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/

For feedback can drop mail to my email address [email protected] or you can create issue

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)

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)

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

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