1

MIS 4346/5346 DATA WAREHOUSING

Data Warehouse to BI

2

Agenda

Review Preparing the DW for Analysis Microsoft BI Platform Overview Building a Cube in SSAS

3

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, …

4

Data Warehouse Project Lifecycle

Source: Mundy, Thornthwaite, and Kimball (2006). The Microsoft Data Warehouse Toolkit, Wiley Publishing Inc., Indianapolis, IN.

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

Displaying the Cube, cont…

Pivot table - a multidimensional spreadsheet

8

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

SQL

MDX, DMX

RDL

10

Microsoft BI Platform: Development Interfaces SQL Server Database Engine

Interface: SSMS SQL Server Integration Services

Interface: SSDT SQL Server Analysis Services

Interface: SSDT & Excel 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 Sourceo Data Source Viewo Cube

Deploy/Process Cube Use the Cube

OLAP Analysiso Reportso Data Mining Models

13

Internet Example

http://www.accelebrate.com/sql_training/ssas_2008_tutorial.htm