By using our website you agree to our use of cookies.

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

Business Intelligence Overview (9 topics)

  • Business intelligence (BI)
  • Extract Transform and Load (ETL)
  • SQL Server Integration Services (SSIS)
  • SQL Server Analysis Services (SSAS)
  • SQL Server Reporting Services (SSRS)
  • PerformancePoint Services for SharePoint
  • ProClarity
  • SharePoint
  • Master Data Management (MDM)

Designing Business Intelligence Solution (9 topics)

  • Reviewing requirements: Identify each products role in the BI solution
  • Discuss whether to use a staging database
  • Discuss use of slowly changing dimensions (SCD)
  • Introduce temporal / rapidly changing dimensions (RCD)
  • Discuss use of surrogate keys
  • Discuss data warehousing techniques
  • Discuss timely delivery of data from source to destination (daily, hourly, real-time)
  • Discuss data warehouse schema design with advantages and disadvantages: Star; Snowflake; Normalised; Combination
  • Discuss designing a time dimension: Surrogate key

Creating the Data Warehouse (6 topics)

  • Table design
  • Partitioned tables
  • Primary and foreign keys
  • Data types
  • Logging and error tables
  • Index considerations

Loading the Data Warehouse (11 topics)

  • SSIS basics
  • Configurations
  • Logging
  • Lookups
  • Fast-load and table partitioning
  • Using switch and merge
  • Late arriving data
  • Change Data Capture (CDC - SQL 2008 sources only)
  • Package design
  • Transactions
  • Checkpoints

Creating the Analysis Cube (6 topics)

  • Data source views
  • Dimensions
  • Dimension Attributes
  • Measures
  • Cubes
  • Best practice warnings

Enhancing the Analysis cube (13 topics)

  • Calculated measures
  • Calculated members
  • Names sets (Dynamic / Static)
  • Key Performance Indicators (KPI)
  • Perspectives
  • Actions: Drillthrough; Report; URL
  • Partitions
  • Storage design
  • Designing aggregations
  • User-based optimisation (UBO)
  • Partition slice alignment
  • Security Roles
  • Processing design

Producing the User Interface (3 topics)

  • Best Tool for the job
  • Infrastructure design
  • Where to use SSRS , PAS, PerformancePoint

Creating reports with Reporting Services (6 topics)

  • Show differences between BIDS and Report Builder 2.
  • Creating a report
  • Tools
  • Related reports
  • Parameters
  • Deploying: Standalone SSRS; SharePoint Integrated

Analyzing data with ProClarity (8 topics)

  • Describe positioning of ProClarity Desktop / PAS Server
  • Designing pages
  • Saving and publishing briefing books
  • Unique views
  • Decomposition trees
  • Perspectives
  • Performance Maps
  • Introduce slicers

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 # Experience with SQL Server database to the level of Microsoft course M2779 or M6232

Microsoft SQL Server Training Courses

Course PDF

Print

Sections