Home /Introduction to SQL Server 2012 Business Intelligence

Introduction to SQL Server 2012 Business Intelligence

Lesson 1:

What Is a Business Intelligence Solution?
Rapid Application Development for BI Solutions

Lesson 2:

A Big Picture Overview
The 10,000-Foot View
Interviewing and Isolating Data
Plan the Solution
Creating the Data Warehouse
Create the ETL Process
Creating a Cube
Creating Reports
Testing the Solution
Approve, Release, and Prepare

Lesson 3:

Planning Solutions
Outline the Steps in the Process
Documenting the Requirements
Locating Data
Defining the Roles
The IT, Security and Licensing Requirements
Documenting the Solution Plan
Implementation

Lesson 4:

Designing Data Warehouses
What Is a Data Warehouse?
What Is a Data Mart?
Competing Definitions
Starting with an OLTP Design
A Typical OLTP Database Design
A Typical Data Warehouse Database Design
Stars and Snowflakes
Dimensional Patterns
Conformed Dimensions
Adding Surrogate Keys
Slowly Changing Dimensions

Lesson 5:

Implementing Data Warehouses with SQL
SQL Server Management Studio
Configuration Manager
Management Studio Windows
Creating Data Warehouse Database
Creating Tables
Creating a Date Dimension Table
Getting Organized

Lesson 6:

ETL Processing with SQL
Performing the ETL Programming
Deciding on Full or Incremental Loading
Isolating the Data to Be Extracted
Identifying the Transformation Logic
Programming Your Transformation Logic
The SQL Query Designer
Updating Your BI Documentation
Building an ETL Script
Working in the Abstract

Lesson 7:

Beginning the ETL Process with SSIS
Starting Your SSIS Project
Adding a Project to an Existing Solution
Renaming Your SSIS Package
The Anatomy of an SSIS Package
Data Connections
Execute SQL Tasks
The Progress/Execution Results Tabs
Resetting Your Destination Database

Lesson 8:

Concluding the ETL Process with SSIS
Data Flows
Outlining a Data Flow Task
Configuring the Data Source
The OLE DB Source Editor
Data Flow Paths
Error Outputs Paths
Configuring the Data Destination
Executing the Entire Package

Lesson 9:

Beginning an SSAS Project
SQL Server vs. Analysis Server Databases
OLAP Cubes vs. Reporting Tables
SQL Server vs. Analysis Server Applications
SSAS Projects
Data Sources
Data Source Views
Dimensions

Lesson 10:

Configuring Dimensions with SSAS
The Dimension Designer
Build, Deploy, and Process
Browsing the Dimension

Lesson 11:

Creating and Configuring SSAS Cubes
Creating Cubes
Processing the Cube
Configuring Cubes
The Browser Tab
The Cube Structure Tab
The Dimension Usage Tab
The Calculations Tab
Making a Test Copy of a Cube
KPIs

Lesson 12:

Additional Cube and Dimension Configurations
Additional Cube Configurations
Additional Dimension Configurations
Managing Your Cubes and Dimension

Lesson 13:

Reporting with SQL Queries
Identifying the Data
Joining Table Data
Ordering Results
Formatting Results Using SQL Functions
Filtering Results
Adding Dynamic Filters with Parameters
Adding Aggregations
Using Subqueries
Creating KPI Queries
Adding Abstraction Layers
Using Your Code in Reporting Applications

Lesson 14:

Reporting with MDX
Key Concepts and Terms
Programming with MDX
The Non Empty Clause
Using Your Code in Reporting Applications
Queries Reporting with Microsoft Excel
Microsoft’s BI Reporting
Excel Reports from the Data Warehouse
Changing Connection Properties
Using Stored Procedures
Working with Excel Reports from a Cube
Testing Your Reports
Creating Charts
Saving to PDF

Lesson 15:

Beginning an SSRS Project
SSRS Architecture
Creating SSRS Objects
Managing the Report
Configuring Reports with SSRS
Creating a Report Template
Adding a Header and Footer
Setting Report Properties
Designing the Header
Renaming Report Items
Using Expressions
Completing the Header
Configuring the Footer
Saving the Report Template

Lesson 16:

Testing and Tuning BI Solutions
Testing the BI Solution
Tuning the BI Solution
Performance Measurements
Creating a Metadata Database