Microsoft Business Intelligence End to End with SQL Server 2012 and SharePoint 2013

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.

Nánari lýsing

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

  • Server products and the BI developer's toolset

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

  • Three business requirements that the delegates need to identify which Microsoft products are needed to fulfill requirement; Group exercise (2-3 per team); Notes about assumptions and decisions to be made during exercise; Show and tell (if appropriate to class)

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

References:

  • Ralph Kimball

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:

  • Stephen Few

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:

  • Stephen Few

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:

  • Stephen Few 'Dashboard Design'

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

Forkröfur

  • 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/M6232 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