Utbildningar

QA-QASQLBI12

Ladda ner som PDF

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.

Prior knowledge

  • 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/M20466. 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

Objectives:

Course 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)
  • 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... Läs mer

Objectives:

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

Utbildningen levereras i samarbete med

Kurs-ID: QA-QASQLBI12
Längd: 5 dagar
Pris exkl moms: 36 300 kr

Frågor om kursen!?

Har du frågor om kursens innehåll, leveransdatum/ort eller behöver en företagsanpassad variant? Fyll i formuläret nedan!


Avtalsrabatter och kampanjer kan ej nyttjas på denna kurs.


Ort och datum

Cloud Access
i Läs mer

Delta på kursen från ditt hem, jobb eller annan plats.

31 okt – 4 nov
Boka nu!
21 nov – 25 nov
Boka nu!
16 jan – 20 jan
Boka nu!
27 mar – 31 mar
Boka nu!
12 jun – 16 jun
Boka nu!

Tipsa