Data Warehouse to Cube
Download
Report
Transcript Data Warehouse to Cube
Data Warehouse to BI
1
Agenda
Review
Preparing the DW for Analysis
Microsoft BI Platform Overview
Building a Cube in SSAS
2
DW Development Approach: Kimball
Methodology
DW Project Lifecycle
Business requirements
Business Requirements Documentation
Bus Matrix
Design, build and deliver in increments
DW Architecture
DW Design
ETL system
Cube, Reports, query tools, …
3
Data Warehouse Project Lifecycle
Source: Mundy, Thornthwaite, and Kimball (2006). The Microsoft Data Warehouse Toolkit, Wiley Publishing
Inc., Indianapolis, IN.
4
DW Development Approach: Kimball
Methodology
DW Project Lifecycle
Business requirements
Business Requirements Documentation
Bus Matrix
Design, build and deliver in increments
DW Architecture
DW Design
ETL system
Cube, Reports, query tools, …
5
BI Application Specification,
Development
Specification
Standard Reports
Layout-led discovery
Enrollments by course and timeframe
Average GPA by course and timeframe
Analytic Applications
Data and Model-led discovery
Year-to-year changes in average GPAs by college
Students most likely to have good overall GPAs
Development
(SSAS, Excel) Multidimensional Cube, Data-led discovery
(SSRS, Excel) Standard Reports
(SSAS, Excel) Mining Model-led discovery
6
Displaying the Cube
Cube browser—resembles spreadsheet
7
Displaying the Cube, cont…
Pivot table - a multidimensional spreadsheet
OLAP Models
Model
Description
Comparison
MOLAP
Data and aggregates in
multidimensional database
Best query performance;
proprietary
ROLAP
Data and aggregates stored in
relational database
Worst query performance;
leverage existing infrastructure
HOLAP
Hybrid approach; detail data stored
in relational database, aggregates
stored in multidimensional
database
Best processing performance;
query performance better than
ROLAP, not as good as MOLAP
9
Microsoft BI Platform
RDL
MDX, DMX
SQL
10
Microsoft BI Platform: Development
Interfaces
SQL Server Database Engine
Interface: SSMS
SQL Server Integration Services
Interface: SSDT
SQL Server Analysis Services
Interface: SSDT
SQL Server Reporting Services
Interface: SSDT
MS Office Excel Data Mining Add-In
11
Preparing the Data Warehouse for
Analysis
Build a multidimensional cube
Refine
Create KPIs…
Design reports
Design mining models
Deploy cube, reports, models to the server
12
Building an Analysis Services Cube
Create:
o Data Source
o Data Source View
o Cube
Deploy/Process Cube
Use the Cube
OLAP Analysis
o Reports
o Data Mining Models
13
Example
http://www.accelebrate.com/sql_training/ssas_
2008_tutorial.htm
14