SQL Server End to End Business Intelligence

5 Day Course
Hands On


Collapse all

Course outline (1 topic)

  • 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)

Module 1: Business Intelligence Overview (17 topics)

  • 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)
  • SharePoint
  • BI products
  • Server products and the BI developer's toolset
  • BI Projects
  • High-level plan
  • Scope and phased delivery
  • Evaluating BI requirements
  • Labs:
  • Identify software requirements for solutions
  • Three business requirements that the delegates need to identify which -Microsoft products are needed to fulfill requirement; Group exercise

Module 2: Designing Business Intelligence Solution (22 topics)

  • 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:
  • Ralph Kimball 'Data Warehouse Design'
  • 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 (19 topics)

  • 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
  • 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 (23 topics)

  • Sections:
  • 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: Creating the Analysis Cube using the Multi-Dimensional model (17 topics)

  • 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 6: Enhancing the Multi-Dimensional Analysis cube (13 topics)

  • 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 7: Creating the Analysis Cube using the Tabular model (17 topics)

  • 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 8: Creating reports with Reporting Services (16 topics)

  • 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
  • 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)
  • 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

Module 9: Power BI (12 topics)

  • Sections:
  • Describe purpose of Power BI and its position in the BI solution
  • Power BI Desktop
  • Importing and shaping data
  • Creating and exploring reports
  • Introduction to slicers
  • Saving Power BI documents
  • Demonstrations:
  • Produce a variety of views including charts and grids
  • Demonstrate creating
  • Labs:
  • Import, manipulate and present data using Power BI Desktop

Creating dashboards with PerformancePoint Monitoring and Analytics (2 topics)

  • Dashboard design best practice
  • Data Sources


  • Basic knowledge of Business Intelligence
  • Knowledge of relational database systems
  • Delegates should have a good working knowledge of T-SQL to level of Querying SQL Databases using T-SQL (QATSQL).

Course PDF