Microsoft Business Intelligence End to End with SQL Server 2016

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 2016 Integration Services, Analysis Services, Reporting Services and Power BI.

Vaativuus
Arvostelut
5pvä
Kesto
3610,00 
+ alv./VAT
Ajankohta:
Sijainti:
Ilmoittaudu viimeistään
Puhuttu kieli: Englanti

Valitettavasti kyseinen kurssi on jo täynnä, kokeilethan toisella ajankohdalla tai sijainnilla.

Agenda

Overview

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 2016 Integration Services, Analysis Services, Reporting Services and Power BI.

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.

Outline

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

Prerequisites

  • Basic knowledge of Business Intelligence
  • Knowledge of relational database systems
  • Delegates will gain the most from this course if they are experienced with SQL Server databases to the level of Microsoft course www.qa.com/M20764 or www.qa.com/M20462. However, as minimum requirement, delegates should have a good working knowledge of T-SQL to level of www.qa.com/QATSQL or www.qa.com/M20461

Agenda

Paikkoja jäljellä:
Ei paikkarajoitusta
-
3610,00  + alv./VAT