Microsoft Business Intelligence End to End with SQL Server 2016

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

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

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

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

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

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

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

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

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

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

Please note: for Attend from Anywhere customers an additional screen is required. The additional screen must have a minimum screen size of 19 inch and minimum resolution of 1280x1024, with the vertical resolution (1024) being the most critical.