Business intelligence solutions provide the infrastructure that enables users at all levels of a business to make better decisions based on more accurate and up-to-date information. This workshop focuses on teaching IT professionals the best practices and skills required to successfully design, build and operate a business intelligence solution using SQL Server 2012 Integration Services, Analysis Services, Reporting Services (including Power View), PowerPivot for Excel and PerformancePoint Services for Sharepoint.
Target Audience:
This workshop-style course is designed for IT professionals who are interested in learning how to implement Business Intelligence solutions on the Microsoft BI Stack. Basic knowledge of common business requirements for a BI solution is assumed and some experience with SQL Server is required.
Module 1: Business Intelligence Overview
This module will introduce the course concepts and the Microsoft Business Intelligence stack (covering line-of-business systems, Integration Services, the Data Warehouse, Analysis Services, Reporting Services, Power View, SharePoint, PerformancePoint and PowerPivot for Excel).
Sections:
Define terms and products
- Business intelligence (BI)
- Extract Transform and Load (ETL)
- SQL Server Integration Services (SSIS)
- SQL Server Analysis Services (SSAS)
- SQL Server Reporting Services (SSRS)
- Power View
- SharePoint
- PerformancePoint Services for SharePoint
- PowerPivot for Excel
BI products
BI Projects
- High-level plan
- Scope and phased delivery
- Evaluating BI requirements
Demonstrations:
-
Examining a sample requirement; Identify source data systems; Identity collisions / overlap from source data systems; Discuss ways of joining data; Discuss ways of cleaning data (e.g. Data Quality Services); Discuss ways to log / record activity; Identify destination requirements i.e. reports, scorecards and charts; Discuss planning BI projects
Labs:
Identify software requirements for solutions
Module 2: Designing Business Intelligence Solution
Sections:
- Introduce dimensions and fact tables
- Discuss dimensions, attributes and hierarchies
- Discuss star and snowflake schemas, and identify advantages and disadvantages
- Discuss conformed dimensions
- Discuss facts, fact tables and granularity
- Discuss whether to use a staging database
- Discuss timely delivery of data from source to destination (daily, hourly, real-time)
- Discuss challenges of changing data
- Discuss slowly changing dimensions (SCD) and late-arriving dimensions
- Discuss use of surrogate keys
- Discuss data warehousing techniques
- Discuss designing a time dimension
References:
Demonstrations:
- Design dimensions and facts, introducing different techniques
- Design a time dimension
Labs:
- Design a simple data warehouse: several dimension tables and fact table
- Identify keys, attributes, hierarchies, requirements for SCDs and facts, stating assumptions made
- Identify the flow of data from line of business systems to the data warehouse tables
Module 3: Creating the Data Warehouse
Sections:
- Table design -choosing appropriate columns and data types
- Referential integrity with primary and foreign keys
- Calculations and views
- Partitioned tables
- Indexes: clustered and non-clustered
- Columnstore indexes
- Maintaining indexes
References:
Demonstrations:
- Create partitioned tables (using multiple file groups for the fact table)
- Create referential integrity
- Create and maintain indexes
Labs:
- Create data warehouse database
- Create the dimension tables
- Populate the time dimension
- Create the fact tables with partitioning
- Create referential integrity with foreign keys
- Create indexes on the fact and dimension tables
Module 4: Loading the Data Warehouse
Sections:
- ETL requirements: Considerations for Master Data Management (MDM), Data Quality Services (DQS) and Cloud DataMarket
- SSIS basics: Projects; Packages; Control Flow; Data Flow; Transformations; Data Sources; Data Destinations
- Project design: Implementing a package hierarchy
- Creating dynamic packages: Variables, Expressions; Parameters
- Control flow tasks
- Data flow tasks
- Troubleshooting and Error handling
- Fast-load and table partitioning, using switch and merge
- Late arriving data
- Advanced options: Event Handlers; Logging; Checkpoint; Transactions
- Handling changing data: Change Data Capture (CDC); Slowly changing dimensions
- Deploying: Project vs. Package deployment; SSIS Catalog, Environments
Demonstrations:
- SSIS package basics; Control flow; Data flow; Transformations; Logging; Variables; Expressions; Parameters
- Error handling, logging, transactions and checkpoints
- Change data capture (CDC); Slowly changing dimensions (SCDs)
- Deploying packages to the SSIS catalog
Labs:
- Design and create a SSIS package structure
- Add control and data flow items
- Create data flow for each dimension table
- Create data flow for each fact table
- Manage changing data using slowly changing dimension transformations (SCDs) and Change data capture (CDC)
Module 5: Analyzing and modeling data with Analysis Services
Sections:
- The Multi-Dimensional model vs. the Tabular model
- Client-side vs. Server-side models: The role of Analysis Services, PowerPivot, Excel and Sharepoint
- Discussion: Comparing the models and thinking ahead: pros and cons; choosing the appropriate one
Module 6: Creating the Analysis Cube using the Multi-Dimensional model
Sections:
- Analysis Services Multi-Dimensional object basics: data sources; data source views, dimensions, measure groups, cubes
- Data sources and Data source views: Friendly names; Relationships; Calculated columns; Named queries;
- Dimensions: Time; Parent-Child; Multiple table (snowflake); Unary Operator
- Dimension Attributes: Key values and name values; Attribute relationships
- Measures and Measure Groups; Data type design; Aggregation functions (Additive / Semi-Additive / Non-Additive)
- Cubes: Producing the base cube; Dimensions usage and relationships
- Best practice warnings
- Processing and testing
- Demonstrations
- Create a data source (DS)
- Create a data source view (DSV); Add named query; Add calculated column (full name, quarter with year, month with year)
- Create dimensions; Time; Product-> Product Subcategory -> Product Category; Geography; Customers; Resellers
Labs:
- Designing dimensions for usability to required design
- Design cube using dimensions
- Set properties for measures
Module 7: Enhancing the Multi-Dimensional Analysis cube
Sections:
- Enhancing the cube using MDX: introduction to MDX, members, tuples and sets; common MDX functions
- Methods to enhance the cube: calculated measures; Calculated members; Named sets; Key Performance Indicators (KPIs); Perspectives; Actions;
Demonstrations:
- Introduction to MDX
- Creating calculated measures, calculated members, named sets, key performance indicators (KPIs), perspectives and actions
Labs:
- Create calculated measures (totals and averages)
- Create calculated members
- Create named sets (top 10 products)
- Create KPI (sales targets)
- Create actions (drillthrough)
- Design perspectives for Internet and Reseller sales departments
Module 8: Creating the Analysis Cube using the Tabular model
Sections:
- Analysis Services Tabular model basics: importing and filtering data, managing and visualizing relationships
- Introduction to DAX
- Creating the model using calculated columns and measures
- Enhancing the model: Creating hierarchies, Using Time Intelligence, Key Performance Indicators, Perspectives and Partitions
- Optimizing the model: In-memory vs. DirectQuery;
- Testing and deploying
Demonstrations:
- Importing and filtering data
- Using DAX to create calculated columns and measures
- Creating hierarchies, KPIs and perspectives
- Testing and deploying
Labs:
- Create and configure a tabular data model from external data
- Use DAX expressions to create calculated columns and measures
- Create hierarchies, KPIs and perspectives according to end-user requirements
- Testing and deploying the model
Module 9: Producing the user interface for visualizing, analyzing and reporting
Sections:
- Best Tool for the job
- Discussion: When and where to use Reporting Services, Power View, PerformancePoint Services, PowerPivot or Excel
Module 10: Creating reports with Reporting Services and Power View
Sections:
- Introduction to reporting services: Pre-canned vs. Adhoc Reporting; The SSRS platform and its components and tools
- Reporting Services basics: data sources, data sets and report design
- Creating reports: tablix data regions; formatting, expressions, grouping, document maps; drilldown, sorting
- Visualizing data: charts; gauges; maps; sparklines; data bars; indicators; images
- Linking reports to each other: parameters; actions; subreports
- Deploying: Standalone SSRS; SharePoint Integrated
- Visualizing and presenting data with Power View: creating a view; creating a data region, filtering; using charts; interactivity; exporting to PowerPoint
References:
Demonstrations:
- Create a report using a tablix that shows sales by product category for a particular region (parameterized).
- Create a report that holds a bar chart showing sales by regions
- Link bar chart report to tablix report passing the selected region as a parameter.
- Creating a report to display key performance indicators (KPIs)
- Power View report visualizing sales performance
Labs:
- Create a report to compare sales over time
- Use gauges to show performance against targets
- Create a linked reports for sales with drillthroughs to add interactivity
- Create a Power View report to view and interact with in PowerPoint
Module 11: Creating dashboards with PowerPivot for Excel
Sections:
- Describe purpose of PowerPivot for Excel and its position in the BI solution
- Designing pages
- Introduction to slicers
- Saving and publishing PowerPivot views
- PowerPivot as a source for Power View and Analysis Services Tabular Mode projects
References:
Demonstrations:
- Produce a variety of views including charts and grids
- Demonstrate creating a slicer using the background
Labs:
- PowerPivot for Excel used to import, manipulate and present data from a single source
- PowerPivot for Excel working with multiple sources (Analysis Services, text file and Access)
Module 12: Creating dashboards with PerformancePoint Services
Sections:
- Dashboard design best practice
- Data Sources
- Reports; Analytic Chart; Analytic Grid; SSRS Report; Scorecard;
- Dashboard; Filters; Relating published elements; Deploying to SharePoint Services (WSS or MOSS)
References:
Demonstrations:
- Create each of the elements: charts; grids; indicators; KPI; scorecard; dashboard
- Deployment to SharePoint to view all the elements including additional representations such as decomposition trees
Labs:
- Show dashboard design to best practice (ref. Stephen Few 'Information Dashboard Design')
- Create a data source from SSAS cube created earlier
- Create an analytic chart and grid, include parameters, for sales over time
- Create a KPI for sales against last year
- Create a scorecard using the KPI and include multiple target columns for target, score and indicator
- Create a summary dashboard holding overview reports and scorecard
- Introduce filters into the dashboards and relate published reports to filters
- Publish dashboards to SharePoint