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:
- Data Dimensions & Cubes
- SSAS Perspectives
