MSBI_SSIS_Aggregate Transfermation

Aggregate Transformation :

Generally "Transformations" are used to transform the data to a desired format as data moves from one step to another step.

Aggregate Transformation is used to perform aggregate functions(Group By, Count, SUM, MAX, MIN and Count Distinct) on source data later pushed to destination.

For example there is a source table Employee and the destination(Output on DW) requirement is Total employees on each department, Total Salary on Department wise, Maximum and Minimum Salaries on each department.

We can write a query in SQL Server as:

SELECT DEPTNO   AS   Department,
       COUNT(*) AS  'No.Of Employees',
       SUM(SAL) AS   Toatal_Salary,
       MAX(SAL) AS   Max_Sal,
       MIN(SAL) AS   Min_Sal
FROM Employee
GROUP BY DEPTNO


We can implement it in a SSIS package as below,

1. Take a Data Flow task to control flow,
2. Edit the Data Flow as drag & drop OLEDB Source, Aggregate Transformation and OLEDB Destination to Data Flow for design,
3. Edit the OLEDB Source, provide the connection and authentication details and select the Employee table. 
4. Make a connection from OLEDB Source to Aggregate Transformation.
5. Edit the aggregate transformation as,
6. Now make a conncetion from Aggregate Transformation to OLEDB Destination,
7. Edit the OLEDB Destination and provide the connection, authentication details ,create a  new table by click on "New.." and rename it (or) select the existed one if the destination has already.
8. Run the package and check the destination data.

Sample Tables

Sample "Employee" and "Department" tables script

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))
DROP TABLE [dbo].[Employee]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]') AND type in (N'U'))
DROP TABLE [dbo].[Department]
GO

CREATE TABLE [dbo].[Department]
(DEPTNO INT PRIMARY KEY ,
DNAME VARCHAR(20),
LOC VARCHAR(20) );
GO

INSERT INTO [dbo].[Department] VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO [dbo].[Department] VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO [dbo].[Department] VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO [dbo].[Department] VALUES (40, 'OPERATIONS', 'BOSTON');


CREATE TABLE [dbo].[Employee]
(EMPNO INT PRIMARY KEY,
ENAME VARCHAR(20),
JOB VARCHAR(20),
MGR INT,
HIREDATE DATE,
SAL MONEY,
COMM MONEY,
DEPTNO INT FOREIGN KEY REFERENCES Department(DEPTNO) );
GO

INSERT INTO [dbo].[Employee] VALUES
(7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, NULL, 20);
INSERT INTO [dbo].[Employee] VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30);
INSERT INTO [dbo].[Employee] VALUES
(7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30);
INSERT INTO [dbo].[Employee] VALUES
(7566, 'JONES', 'MANAGER', 7839, '2-APR-1981', 2975, NULL, 20);
INSERT INTO [dbo].[Employee] VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30);
INSERT INTO [dbo].[Employee] VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981', 2850, NULL, 30);
INSERT INTO [dbo].[Employee] VALUES
(7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10);
INSERT INTO [dbo].[Employee] VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20);
INSERT INTO [dbo].[Employee] VALUES
(7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10);
INSERT INTO [dbo].[Employee] VALUES
(7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981', 1500, 0, 30);
INSERT INTO [dbo].[Employee] VALUES
(7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20);
INSERT INTO [dbo].[Employee] VALUES
(7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981', 950, NULL, 30);
INSERT INTO [dbo].[Employee] VALUES
(7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981', 3000, NULL, 20);
INSERT INTO [dbo].[Employee] VALUES
(7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10);

BOOKS TABLE

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Books]') AND type in (N'U'))
DROP TABLE [dbo].[Books]
GO
CREATE TABLE [dbo].[Books](
[BookID] [int] IDENTITY(1001,1) NOT NULL,
[BookName] [varchar](50) NULL,
[Category] [varchar](50) NULL,
[Price] [numeric](18, 2) NULL,
[Price_Range] [varchar](20) NULL,
PRIMARY KEY CLUSTERED ( [BookID] ASC )
) ON [PRIMARY]
GO

INSERT INTO dbo.Books
(BookName, Category, Price, Price_Range)
VALUES
('Computer Architecture', 'Computers', 125.6, '100-150'),
('Advanced Composite Materials', 'Science', 172.56, '150-200'),
('Asp.Net 4 Blue Book', 'Programming', 56.00, '50-100'),
('Stategies Unplugged', 'Science', 99.99, '50-100'),
('Teaching Science', 'Science', 164.10, '150-200'),
('Challenging Times', 'Business', 150.70, '150-200'),
('Circuit Bending', 'Science', 112.00, '100-150'),
('Popular Science', 'Science', 210.40, '200-250'),
('ADOBE Premiere', 'Computers', 62.20, '50-100')

Why a DBA

After long time I found the best answer from Toad World blog.

Developers and other technicians will bounded for some area. But the DBAs manage hundreds of SQL Server instances, making sure that servers are always online, backed up, and corruption-free. They manage clustering, SAN storage and security. 

Windows admins are responsible for making sure the hardware, storage and operating system works correctly, and the SQL Server DBAs only start troubleshooting at the SQL Server level. If the SQL Server can't access its drives, or if the operating system throws errors before SQL Server starts, then the DBA team calls in the Windows administrators and makes them do the troubleshooting first. The production DBAs still have to understand the basics of Windows and storage, but they're less involved in the day-to-day hardware and OS maintenance.

Production DBAs make sure that SQL Servers are installed correctly, secured, backed up, and when necessary, restored. Production DBAs spend a lot of time in meetings because they frequently help to implement third party applications. Project managers or business managers will purchase applications from vendors, and these apps store data in SQL Server. The production DBA helps define what kind of database server is needed, or what existing SQL Server will be used. The application has its own database schema that can't be modified, and the production DBA may not have to learn that schema, but the DBA is still accountable for making sure that the server is always running, reliable, and fast. When questions about performance arise, the production DBA has to determine whether it's a hardware problem, a SQL Server configuration problem, an application design problem or a misbehaving user.

MSBI - II

Create a package in SSIS :

Go to Start --> Programs --> Microsoft SQL Server 2008 (o)r 2012 --> 

SQL Server Business Intelligence Development Studio (or) SQL Server Data Tools as shown in the below figure:

It will open a BIDS as shown in the below figure.Once the BID studio is open, we need to create a solution based on our requirement. Go to File -> New Project (or) Ctrl + Shift + N. It will opens a wizard where we need to select Integration Services Project .

Select the Integration services project and provide the project name, solution name and location, where we want to store the SSIS files as shown in the below screen:

After creating new project, we can see that by default a new SSIS package is added (Package.dtsx), you can right click on it and rename.

Sample Example :

The below example explains a simple SSIS package that can fetch the data from "Notepad" to a "SQL Server Table",
From the below figure

1. A Source server contains some data in a Notepad and a Destination server contains an empty table in a database. 
2. Source connection manager contains Source server Name, Notepad name and location,
3. Validation process will checks the sources for meta data which you mentioned in query is valid or not,(Meta data means : Objects, columns, data on the Source)
4. Destination connection manager contains Destination server Name, Database name and Table name.

I have taken a "Data Flow" task from control flow Toolbox to "Control Flow" editor,

Right click on "Data Flow Task" and click on "Edit...",

The controle will go to the "Data Flow",

I have taken a "Flat file Source"  and a "SQL Server Destination" to the Data flow, First I defined the Source with Source server Name, Notepad name and location as,right click on "Flat file Source" and click on "Edit..",

There will be a "Flat file source Editor" window is opens for "Flat file Connection manager", this is our first package, no connection managers have been defined earlier. So click on "New.."

There will be a "Flat file Connection manager Editor" is opens. In "General" tab provide the name for connection manager, location of the notepad (Browse..),Format as Delimited(Either the data in the notepad separated by comma{,} or tabs or fixed length) and select the check box for display first row data as header in the output .

Now go to the Columns tab, set the column delimeter as "Comma{,}" and find the data as preview

Make a connection between source and destination by drag the arrow from source to destination. Now defined the Destination connection by right clicking on "SQL Server Destination" and click on "Edit..". It will displays a "Destination editor" window, click on "New.." for Connection, again it will displays OLEDB Connection Managers window again click on "New..". 

Provide the Destination Server name, Username, Password and Select the database(In which database you want to store). Click on OK. You will returns to previous window.

If your destination table is already existed, just click on drop down list, else click on "New.." for create a new table, here the system provides predefined table structure, if you want just rename it,

Go to Mappings Tab, Map the Input and Outpu columns,

Now come back to Controle flow, right click on "Data Flow Task" and click on "Execute Task". Debugging will starts  and if your Task executed successfully you get a right mark in green color. Stop the Debugging. Go to the SQL Server see the destination table has the data or not.

MSBI -I

MSBI - Introduction 

Normalization: It is the process of storing the same data into multiple tables (eliminating redundant data) and each table contains only related data(ensuring dependencies make sense).

Benefits:

Eliminate data redundancy,

Improve performance,

Query optimization,

Faster update due to less number of columns in one table,

Index improvement.

OLTP Systems (Online Transaction Processing Systems): In DBA’s view these systems are directly connected to application systems and the data is inserting into these systems, modifying the data and extracting the data when it is needed.  Means it maintains daily transactional summarized data (Dynamic Data). Normalized data is available.

ODS / DW (Operational Data Store / Data Warehouse): It integrates the data from multiple sources and stores in a database. And it maintains the detailed data / historical data (Static Data). Denormalized data is available.

Generally to stores the data into a Data Warehouse/ODS we are using ETL tools (Extraction, Transformation and loading). These are of 2 types…. GUI based (Informatica, Data Stage, Oracle Warehouse Builder, SSIS etc) and CUI based tools (SAS ETL, TeraData Utility, BCP etc) available.

Reporting tools are used to fetch the reports from DW, as time based (Daily, weekly, monthly), business factors based (E.g. Total sales on particular product, Maximum reselling products etc). So that we can analyse how much progress is being made in the business for certain period of time.

MSBI (MicroSoft Business intelligence)

MSBI tools are the set of technologies (SSIS, SSRS, SSAS..), used to collect the raw data from multiple data sources, Transform it into meaningful information and finally show data to users with a presentation.


BIDS (Business Intelligence Development Studio)/SQL Server Data tools:  It’s an integrated development environment (IDE), by using this we can develop the projects for “SQL server Integration Services”, “SQL server Analysis services” and “SQL server reporting services”.


SSIS: Is an ETL tool of Microsoft, introduced in SQL Server 2005(Before this DTS – Data Transformation Services are available). By SSIS we can Extract the data from different sources like Text files, Excel files, RDBMS Tables…etc and do some Transformations on that data like aggregations, Joins, Conditions … etc and load the data to Destination (DW).

All the SSIS files are organized into Packages, Projects and Solutions. 

  

Package is basic element in SSIS design flow. In general package means collection of items, in the same way SSIS package is a collection of Connections, Control Flow items, Data Flow items, variables, Parameters and Configurations. Each package is saved with a extension “.dtsx” in a project. You can create one or more packages in a Project. And a Project is a part of Solution, you can develop one or more Projects within a Solution.

(**Collection of packages stored in a project, Collection of Projects stored in Solution)

Connection Managers : This section will stores the meta data of sources and destinition connection. For example Server name, database name, user name and password etc.

Control flow Elements : It is a collection of Tasks and Containers(in another way Collection of executables). Optionally collection of Data Flow tasks. A Container represents the structure and Task represent the functionality.  

Example: 

(i)A Data Flow Task can represents the flow of the data from source to destination.  

(ii)A Execute SQL Task Represents to execute user defined queries,

(iii)A  Send Mail Task represent to send the mails to different recipients,

(iv)A File System Task represents copy or move the files/folders to different destinations.... etc. 

Data Flow Task: Already we know that a Data Flow Task can represents the flow of the data from source to destination with required changes in the data using by different types of Transformations like, SORT,MERGE,DERIVED COLUMN,CONDITIONAL SPLIT, UNION ALL.... etc. 

Event Handlers : At the time of executing the Packages or Tasks or Containers it will raise an event like OnError,OnWarning, OnPreExecute, OnPostExecute, OnProgress … etc. 

Variables : A Variable is a named object/temporary place, that store one or more values which will be used/referenced by various components through out the package. You can assign a value to the variable when it creates, can updates values at run time. Two types of variables in SSIS ,  System and User Defined. 

Configurations : Which allows to configure properties of a package externally and can run in different environments. For example you have created a package and migrated to another server. That you need to set the source and destination information externally by config files. Which can available in XML Config file, SQL Server, Environment variable ... etc.