Transcript Building OLAP Applications with SQL Server and Visual Basic

Override the title
Chris Harrington
[email protected]
Session Overview




OLAP Review
MDX Explained
SDK/API Options
Demos
The Multidimensional
Data Model
“Show me my sales by product by region by
time”
Dimensions: Product, Region,Time
Hierarchical summarization paths
Product
W
S
N
Juice
Cola
Milk
Cream
Toothpaste
Soap
1 2 34 5 6 7
Product
Industry
Region
Country
Category
Region
Product
City
Time
Year
Quarter
Month
Month
Office
Day
Week
Why Use OLAP?

Relational database


Data Warehouse




Transactional processing
Tactical information
“What was the total revenue from soft drinks for
Sussex in January?”
“What would be the effect on soft drink costs to
distributors if syrup prices went up by 10p/gallon and
shipping costs went down by 5p/mile?”
OnLine Analytical Processing (OLAP)

‘Slice and dice’ for Data Warehouses
Terminology
Cube –
A multi-dimensional
container for information
and pre-calculated query
results.
Terminology
A discrete name or
identifier used to identify a
data item's position and
description within a
dimension.
Coffee
Apples
Tea
Time
April
March
February
Onions
January
Products
Members –
Terminology
Parent-child relationships
within a dimension.
Coffee
A detail member of a
dimension is the lowest level
number in its hierarchy.
Apples
Tea
Time
April
March
February
Week3
Am
April
pm
Monday
March
Tuesday
Week2
Week1
February
Onions
January
Products
Hierarchy –
Terminology
Level –
Coffee
e.g. Jan, Feb are of the level
Month within Time
Apples
Tea
Monday,Tuesday would be of
the level Day
Time
April
March
Week2
Week1
February
Week2
Week1
Onions
January
Products
Position within a hierarchy.