In this MSBI project, you will be creating data flow tasks in SSIS, creating SSAS Cubes, creating an SSRS Report.

Project 1: SSIS

Problem Statement: Create a data flow task to extract data from the XLS format and store it into the SQL database, store the subcategory and category-wise sales in a table of the database. Once you get the output, split the dataset into two other tables. Table 1 should contain three columns (Sales < 100,000), Category, and Subcategory. Table 2 should contain (Sales > 100,000), Subcategory, and Category columns. Also, the Sales column should be sorted in both tables. Divide the whole dataset into a ratio of 70:30 percent and store the results in two different tables in the database

Topics: Data Flow, ODBC Set up and Connection Manager, Flat File Connection, Transformation, Import Export Transformation, Split and Join Transformation, Merge and Union All Transformation

Highlights:

Creating a Data Flow Task

ODBC Set up and Connection Manager

Transformations

Project 2: SSRS:

Problem Statement: In the United States, there are many stores in which a survey was conducted based on students. By using data set (Student Survey), try to extract meaningful Insights by creating an SSRS Report to show Tabular Visualization, Matrix Visualization, Funnel chart, Pie chart, Scatter plot, Drill-down.

Topics: Report Creation, Deployment, Concepts of Reporting Services, Tablix and Matrix, Expression and Parameters, Charts & Reports

Highlights:

Tabular Visualization

Matrix Visualization

Transformations

Funnel chart

Pie chart

Scatter plot

Drill down

Project 3: SSAS:

Problem Statement: Using Adventure Works DW 2014 database, Build a Cube to show the number of products there for each color, total Sales Amount for each currency, Count of products there for each product name. Create a reference relationship between the Product category table and subcategory table hence show how many subcategories are there for each category. Build the partitions for sales amount <= 700 and sales amount > 700. Make the aggregations and stop when performance gain reaches 40%. Build a perspective (Subset of the above cube) with limited measures and Dimension fields.

Topics: Dimensions, Data Dimensions & Cubes, Aggregations, Measures & features of Cube, SSAS Perspectives

Highlights:

Data Dimensions & Cubes

SSAS Perspectives

Case Study 1: SSIS

Problem Statement: Create the connection of OLDB & load the data in SQL Server from excel; Create transformation where you have to split the people’s age group; How to create constants and events in package; Create a project level and package parameter at the package level; How to extract the data in an Incremental Order;

Topics: Data Flow, ODBC Set up and Connection Manager, Transformation, Split & Join Transformation, Term Extraction and Lookup

Highlights:

ODBC Set up and Connection Manager

Transformations

Case Study 2: SSRS

Problem Statement: Steps to add a correlated column chart; how to create a report server project; Use of Data Connections, Performance point Content library; Steps to create drill-down charts; How to pass the parameter from the main chart to detail chart (pie); Functions of Data bars and Sparklines; Usage of KPI box in SSRS Dashboard;

Topics: Concepts of Reporting Services, Report Creation, Expression and Parameters, Report and Authentication, Deployment

Highlights:

Expression and parameters

Report and Authentication

Deployment

Case Study 3: SSAS

Problem Statement: Knowledge check on data mart, measures, dimension, cube, KPI’s, perspectives in SSIS

Topics: Dimensions, Data Dimensions & Cubes, Measures & features of Cube, SSAS Perspectives

Highlights: