Creating SQL Server Integration Services (SSIS) Packages

This is continuous From My previous Post to create SQL Server Integration Services to get the updated data to MultiDimensionalAnalysisProject (MDAP) Database.
These are the following steps

1. Create SQL Server Integration Services (SSIS) projects.
2. Define Control flows and Data flows.
3. Deploy SSIS projects.
4. Schedule SSIS packages.

1. Create SQL Server Integration Services (SSIS) projects.
First we need to set up the framework for an SSIS project in our current MDAP Solution.This is done by adding a new project to the solution that is currently opened in Microsoft Visual Studio.
(Select integration services project  in the new project)
This will result in the SSIS project being created, and the new project should be immediately visible in the Solution Explorer.
2. Define Control flows and Data flows.
In the SSIS Toolbox  in the left hand side of the screen and drag the Data Flow Task item into the design area.Double click it to go to the Data Flow tab of the Design window.
To work with data flow we should define source and destination connections.For this we should create OLEDB Connections for the MDAP database, by Right clicking in the connection Manager which is the lower part of the Designer window.After completion of Connection setting Drag and drop the OLEDB source and Destination Controls.Then Define the Source table(from Live Database ) for Source and Destination Table(Database what we Create in initial step) in Destination Control.( if You get any time stamp error after mapping edit mapping relation ship and delete the time stamp mapping link).
Start Debugging we can see successful package run.
But each time if run it will create a new set of records in the destination tables, adding to the already existing records.
So, before we transfer the latest set of data to the destination tables, we must ensure that they are emptied first.
Let us move back to the Control Flow page of the SSIS package, where we will add an Execute SQL Task item.it to define it. The connection must point to the Database what we Create in initial step because it is the tables in there we would like to empty.Next, click the button in the SQL Statement field to enter the SQL Query that we would like to execute. The truncate statement removes the content of the table without removing the table itself.
Connect the two Control Flow items so that the Execute SQL Task  item is executed before the Transfer Data Flow item.
3. Deploy SSIS projects.
We need to process the Multidimensional database every single time the data have been updated.
For that  go to the toolbox and select an Analysis Service Processing Task item.
This Processing Task also requires a Connection Manager to be able to know which multidimensional database needs to be processed. So we will need to add an Analysis Services Connection to our list of
Connection Managers.
Debug it again for successful creation.
4. Schedule SSIS packages.
Assigning this job for SQL Agent. Already we know how to create a job and schedule it .
Open the SQL server Management studio:
Right click the Jobs folder to add a new job.Give name and other details,Go to the Steps page and click the New button. At the New Job Step page we must enter information about Name, Type and Source.
• Step name: Run the name of your SSIS Package.
• Type: SQL Server Integration Services Package.
• Run as: SQL Server Agent Service Account.
• Package Source: File system.
in next step shedule in when to run. ok
Finally right click the job and select "Start job at step" option.Make sure that the job can execute and complete without errors.(close all window related to this task before start).

That's it we completed the SQL Server Integration Services process ready to use in any BI Clients.

Business Intelligence with Dynamics NAV 2013

HI,

Actually from NAV 2013 we don't have any Business Intelligence setup. So we have to develop all Dimensions and Measures from Dot net Environment.
If we use SQL Server 2012 then we have to select "SQL Server Data Tools"  which is SQL Server 2012 folder from all programs.
If we use SQL Server 2008 R2 then we have to select "SQL Server Business Intelligence Development Studio"  which is SQL Server 2008 R2 folder from all programs.

****** IMP ****
(Before starting  we should create a Database in Database engine and import the required tables from live database for Analysis. This database information will update dynamically from live databse by creating SSIS Package because we should not disturb the Live database)

Steps to Create MultiDimesional Project for SQL Analysis:
After Opening and select path for the Project,

Step :1
The first thing we need to do is to tell upon which Data Source this project should be defined.
Right-click the Data Sources folder to start defining a Data Source for the Multidimensional project.
Clicking the New button will open the Connection Manager dialogue box.
The new connection must point to the your database on our SQL server. To access databases on our SQL server, you should select Native OLE DB\SQL Server Native Client 11.0.

Step : 2
The Data Source View is where we define the relationship between the tables that we want to access via the Data Source.
We will also be setting logical keys for the tables as we define their relationship.
Start out by right-clicking the Data Source Views folder to create a new Data Source View.

In the next step we will select from a list of available objects (tables in our case) to be included in the Data Source View.

And click next after that finish it.
Now we want to establish the relationship between the tables.
Already we know about Fact tables and Dimension tables
Some tables contain the actual data or the facts that we would like to analyze such as Amount, Quantity etc. These tables are often referred to as Fact tables.
Each post in the fact tables also contains information about e.g. which item has been sold, but the item information in the fact table may be kept short, often limited to the item number only.
Further information about an item number such as name and description may then be found in another table, e.g. the Item table. Such a table is then called a Dimension table.
Just Right click on the table and establish relation ship by selecting the tables and the fields which are primary and Foreign keys.

Step :3 
Creating Dimensions for our Analysis:
Dimensions to categorize and to filter your data.
we can go to the Solution Explorer on the right hand side to add a New Dimension to the Dimensions folder.
Select appropriate Data source view and select Main table(from where you want to select the dimension fields) and Key column.
Next select the remaining field which you want to use as dimensions. and finish it.
All you can see in Dimension Structure tab.
Finally Process the Dimension.(If you get any impersonation problem,
To correct the Impersonation setting, you will have to go back to the Data Source definition of your project in the Solution Explorer on the right hand side. Double click the  DBName.ds Data Source or right click to open the Data Source settings dialogue box.Select the Service Account.)

Step : 4
Creating Cubes for our Analysis :
To create a new cube you should right click the Cubes folder in the Solution Explorer on the right hand side of Visual Studio.
the Cube Wizard is actually just showing all the fields from the table that has a numeric data type.So Select the Tables and in the next select the required measures for Analysis.Next you will get the Dimension sets what you created, select as per requirement.
Finally Process The Cube.(If you get any impersonation problem,
Before clicking the Run button above there is one button change settings select it   Dimension Key Errors
Select "Use Custom error configuration"  and select "Ignore errors count")

Its Completed now you can check data by selecting dimensions and measures in Browser tab in visual studio.
But Database day by day increse so we should create SSIS package to get data Dynamically.
We will see how to create SSIS package in my next post.