MSBI-Project

BI- (Business Intelligent) converting from Data into Information

Below sample project will see how we can create simple SSIS and SSAS project

You can refer this blog https://amitpnk.github.io/MSBI-Project/

Sending feedback

For feedback can drop mail to my email address amit.naik8103@gmail.com or you can create issue

SSIS (Sql Server Integration Service)

Lab 1 - Creating simple SSIS package

Sample SSIS project which will see Data flow and control flow in Lab1.dtsx

Data flow - ETL activities
Control flow - Non-ETL activities

Step 1 - Run SQL scripts which is available in Miscellaneous
Step 2 - Drag DataFlow task
Step 3 - Inside this add Flat file source (to read from CSV file) -> Derived column (string manipulation) -> ADO NET Destination (insert into DB)

Lab 2 - Conditional split, Data conversion & Error handling

In Lab2.dtsx will see how we can create conditional split, data conversion & error handling

Step 1 - Flat file source (to read from CSV file) -> Derivied column (string manipulation) -> Data conversion (to convert into number) -> Conditional split (Based on forumla we can insert into DB or flat file destination)
Step 2 - In case of error, we can redirect into log file (using flat file destination)

Lab 3 - For loop, variables & parameters

In Lab3.dtsx will see how we can extract dynamically with multiple csv files using for loop and variables/parameters

Step 1 - From previous Lab drag For loop container and inside this add Data flow task
Step 2 - Open package variales and add variable FullFilePath
Step 3 - Right click on Foreach loop container and go to Edit -> Collection -> Enumerator
- Modify ForEach file enumator
- Modify Folder path source
- Modify *.txt
- Modify Fully qualified
Step 4 - Go to Variable Mapping
- Add variable User::FullFilePath
Step 5 - Go to properties of Flat file connection manager (csv connection) -> Expression -> add connectionString to @User:FullFilePath
Step 6 - Incase of parameter : go to Parameter tab -> setValue and right click on ForEach loop container -> Expression -> Directory - $Package:ParamFilePath

Lab 4 - Dimension, measures, start schema, snow flake, shared connection managers and package tasks

There is mainly 2 category of tables

Measures/Facts - Is numerical property
Dimensions - Context of Measures

Eg: Sales Amount as per country, year and product here
Sales Amount is Measures and Country is Dimensions

Star schema - Fact is central table with foreign key relationship with dimension Snow flake - Same as Star schema and also dimension table also connected

Step 1 - Create new SSIS package as Country.dtsx, States.dtsx, SalesPerson.dtsx, Product.dtsx and in DataFlow task create FlatFileSource-> ADO NET Destination in each file w.r.t country, states, salesPersion & product csv file
Step 2 - Create Global connection string and map to each file instead of creating individual file
Step 3 - Create Main.dtsx and add control ExectuePackageTask and map to each dtsx file mentioned in step 1