SQL Server End to End Business Intelligence
5 Day Course
Hands On
Code QASQLBI08
This course has been superceded by Microsoft Business Intelligence End to End with SQL Server 2016.
Modules
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
Prerequisites
- 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).