Solving Business Problems Solving Business Problems In OLAP Services Using In OLAP Services Using MDX – Part IMDX – Part I

Amir Netz – Dev Manager & ArchitectAmir Netz – Dev Manager & ArchitectAriel Netz – Program ManagerAriel Netz – Program ManagerSQL Server OLAP ServicesSQL Server OLAP ServicesMicrosoft CorporationMicrosoft Corporation

5-3045-304Global Breakout Session Hardware ProviderGlobal Breakout Session Hardware Provider

Yes,Yes,We Are Brothers.We Are Brothers.

AgendaAgenda

What is MDX?What is MDX? MDX BasicsMDX Basics MDX QueriesMDX Queries Solving Business Problems with MDXSolving Business Problems with MDX

Functionality Functionality

KnowledgeKnowledge RequiredRequired

Knowing MDXKnowing MDX

What Is MDXWhat Is MDX

MDX = Multi Dimensional ExpressionsMDX = Multi Dimensional Expressions A syntax for modeling and querying A syntax for modeling and querying

an OLAP databasean OLAP database Part of the OLE DB for OLAP SpecPart of the OLE DB for OLAP Spec Supported by multiple providers Supported by multiple providers

(OLAP Services, TM1, SAS, (OLAP Services, TM1, SAS, WhiteLight, SAP…)WhiteLight, SAP…)

It is the key for all advanced analytical It is the key for all advanced analytical capabilities of OLAP Servicescapabilities of OLAP Services

MDX BasicsMDX Basics

MDX allows easy navigation in the MDX allows easy navigation in the multi dimensional spacemulti dimensional space

It “understands” the MD concepts of It “understands” the MD concepts of cube, dimension, level, membercube, dimension, level, memberand celland cell

It is used for It is used for Queries – full statements (SELECT…Queries – full statements (SELECT…

FROM)FROM) Business modeling – definingBusiness modeling – defining

calculated memberscalculated members

MDX ConstructsMDX Constructs

Member : Member : Nelson, [1996].[Q1].[Jan]Nelson, [1996].[Q1].[Jan]

Tuple :Tuple : (Nelson, USA), (Computers, [1996])(Nelson, USA), (Computers, [1996])

Sets :Sets : {Nelson, White}{Nelson, White} { (Computers, USA), (Printers, Japan) }{ (Computers, USA), (Printers, Japan) } [1996].Children[1996].Children TopCount(SalesReps.Members,TopCount(SalesReps.Members,

10, Sales)10, Sales)

MDX QueriesMDX Queries

SELECT sets on axes (N axes)SELECT sets on axes (N axes) FROM a source cubeFROM a source cube WHERE the data is sliced by some WHERE the data is sliced by some

coordinatescoordinates

Sample CubeSample Cube

Dimensions:Dimensions: MeasuresMeasures: Sales, Cost, Units: Sales, Cost, Units Time:Time: Year, Quarter, Month Year, Quarter, Month GeographyGeography: All, Region, Country: All, Region, Country ProductsProducts: All, Product Group, Product : All, Product Group, Product

NameName CustomersCustomers: All, Customer Group, : All, Customer Group,

Customer NameCustomer Name

SelectSelect Time.year.members on Time.year.members on RowsRows,,Measures.members on Measures.members on ColumnsColumns,,Product.[Product Department].members Product.[Product Department].members

on on PagesPagesFromFrom Sales Sales

Sample MDXSample MDX

Stage 1: Creating AxesStage 1: Creating Axes

Stage 2: Getting Cells’ ValuesStage 2: Getting Cells’ Values

GroceriesGroceries

ClothingClothingAppliancesAppliances

SalesSalesCostCost UnitsUnits

9797

9898

9999

9696

9595 1515 1313 55

1717 1313 66

2222 1111 77

2929 99 99

3030 1010 88

Query ExecutionQuery Execution

9797

9898

9999

GroceriesGroceries

ClothingClothingAppliancesAppliances

SalesSalesCostCost UnitsUnits

(Products.Clothing , Measures.Units ,Time. 98)(Products.Clothing , Measures.Units ,Time. 98)

(Products.Clothing , Measures.Sales ,Time. 97)(Products.Clothing , Measures.Sales ,Time. 97)

(Products.Groceries , Measures.Cost ,Time.Year. 95)(Products.Groceries , Measures.Cost ,Time.Year. 95)

9696

9595

Every Cell Has A Name...Every Cell Has A Name...

9898

ClothingClothing

UnitsUnits

?

?

(Products.Clothing , Measures.Units ,Time. 98.PrevMember)(Products.Clothing , Measures.Units ,Time. 98.PrevMember)

(Products.Clothing , Measures.Units ,Time. 98.NextMember)(Products.Clothing , Measures.Units ,Time. 98.NextMember)

Every Cell Still Has A Name...Every Cell Still Has A Name...

9898

ClothingClothing

UnitsUnits

?

(Products.Clothing , Measures.Units ,Time. 98.PrevMember)(Products.Clothing , Measures.Units ,Time. 98.PrevMember)

?

(Products.Clothing , Measures.Units ,Time. 98.NextMember)(Products.Clothing , Measures.Units ,Time. 98.NextMember)

?

(Products.Clothing , Measures.Units.PrevMember ,Time. 98.Lag(3))(Products.Clothing , Measures.Units.PrevMember ,Time. 98.Lag(3))

(Products.Clothing , Measures.Units.PrevMember ,Time. 98.Lead(-3))(Products.Clothing , Measures.Units.PrevMember ,Time. 98.Lead(-3))

OROR

Every Cell Still Has A Name...Every Cell Still Has A Name...

Calculated MembersCalculated Members

Calculated members are the real Calculated members are the real power behind OLAPpower behind OLAP

They provide the mean to define They provide the mean to define complex business logiccomplex business logic

MDX is the syntax for calculated MDX is the syntax for calculated membersmembers Statements – tools, query generators, Statements – tools, query generators,

developersdevelopers Calculated members – DBAs and Calculated members – DBAs and

power userspower users

9797

9898

9999

GroceriesGroceries

ClothingClothingAppliancesAppliances

SalesSalesCostCost UnitsUnits

9696

9595

With With member Measures.Growthmember Measures.Growth as as ‘(‘(Time.currentmemberTime.currentmember,Measures.Sales)- ,Measures.Sales)- ((Time.currentmember.prevmemberTime.currentmember.prevmember,Measures.Sales)’,Measures.Sales)’

SelectSelect Time.year.members on Time.year.members on RowsRows,,AddCalculatedMembers(Measures.members) on AddCalculatedMembers(Measures.members) on ColumnsColumns,,Product.[Product Department].members on PagesProduct.[Product Department].members on PagesFromFrom Sales Sales

1515 1313 55

1717 1313 66

2222 1111 77

2929 99 99

3030 1010 88

GrowthGrowth

Time.95Time.95

EmptyEmpty

1515

Time.CurrentMemberTime.CurrentMember

Time.CurrentMember.PrevMemberTime.CurrentMember.PrevMember

Sales GrowthSales Growth

97

98

99

Groceries

ClothingAppliances

SalesCost Units

96

95

With member Measures.Growth as ‘(Time.currentmember,Measures.Sales)- (Time.currentmember.prevmember,Measures.Sales)’

Select Time.year.members on Rows,AddCalculatedMembers(Measures.members) on Columns,Product.[Product Department].members on PagesFrom Sales

Growth

Time.96

Time.95

15

2

Time.CurrentMember

Time.CurrentMember.PrevMember15 13 5

17 13 6

22 11 7

29 9 9

30 10 8

Sales GrowthSales Growth

97

98

99

Groceries

ClothingAppliances

SalesCost Units

96

95

With member Measures.Growth as ‘(Time.currentmember,Measures.Sales)- (Time.currentmember.prevmember,Measures.Sales)’

Select Time.year.members on Rows,AddCalculatedMembers(Measures.members) on Columns,Product.[Product Department].members on PagesFrom Sales

Growth

Time.CurrentMember

Time.CurrentMember.PrevMember

Time.97

Time.96

15

2

5

15 13 5

17 13 6

22 11 7

29 9 9

30 10 8

Sales GrowthSales Growth

97

98

99

Groceries

ClothingAppliances

SalesCost Units

96

95

With member Measures.Growth as ‘(Time.currentmember,Measures.Sales)- (Time.currentmember.prevmember,Measures.Sales)’

Select Time.year.members on Rows,AddCalculatedMembers(Measures.members) on Columns,Product.[Product Department].members on PagesFrom Sales

Growth

Time.98

Time.97

15

2

5

7

Time.CurrentMember

Time.CurrentMember.PrevMember15 13 5

17 13 6

22 11 7

29 9 9

30 10 8

Sales GrowthSales Growth

97

98

99

Groceries

ClothingAppliances

SalesCost Units

96

95

With member Measures.Growth as ‘(Time.currentmember,Measures.Sales)- (Time.currentmember.prevmember,Measures.Sales)’

Select Time.year.members on Rows,AddCalculatedMembers(Measures.members) on Columns,Product.[Product Department].members on PagesFrom Sales

Growth

Time.99

Time.98

15

2

5

7

1

Time.CurrentMember

Time.CurrentMember.PrevMember15 13 5

17 13 6

22 11 7

29 9 9

30 10 8

Sales GrowthSales Growth

GetCellValue(CurrentCell)GetCellValue(CurrentCell)

Push CurrentCellCurrentCellMeasure = SalesL = GetCellValue(CurrentCell)PopPush CurrentCellCurrentCell Measure = SalesCurrentCell Time = CurrentCell Time PrevmemberR = GetCellValue(CurrentCell)Pop CurrentCellResult = L - R

(Growth, 98, Clothing)

(Growth, 98, Clothing)GetCellValue(CurrentCell)

Member Formula?No: Return Cell

Member Calculate(CurrentCell)

(Growth, 98, Clothing)(Sales, 98, Clothing)

(Growth, 98, Clothing)(Sales, 98, Clothing)(Sales, 97, Clothing)

(Growth, 98, Clothing)

R = Sales in 97 of Clothing

L = Sales in 98 of Clothing

= Growth in 98 of Clothing

(Sales, 98, Clothing)(Sales, 97, Clothing)

Growth:(Sales,Time.CurrentMember) –(Sales,Time.CurrentMember.PrevMember)

L=29

R=22

L-R=29-22=7

How did sales in this period compare with sales in the previous period?

How did sales in the current period compare with the same period in the previous year?

What is my total since the beginning of the year?

““It’s Just A Question It’s Just A Question Of Time ….”Of Time ….”

1Year Quarter Month Sales Result790120

Jan 30Feb 40

Q1

Mar 50200

April 65May 45

Q2

Jun 90185

Jul 55Aug 60

Q3

Sep 70285

Oct 80Nov 100

1997

Q4

Dec 105

10

1010010205

-2045-15-455

108015

How Did Sales In This Period Compare How Did Sales In This Period Compare With Sales In The Previous Period?With Sales In The Previous Period?

Year Quarter Month Sales Result200

April 65May 45

Q2

Jun 90-2045

8015

Time.CurrentMember, Measures.CurrentMemberTime.CurrentMember, Measures.CurrentMember

Time.CurrentMember,Measures.SalesTime.CurrentMember,Measures.Sales

Time.CurrentMember.PrevMember, Measures.SalesTime.CurrentMember.PrevMember, Measures.Sales

How Did Sales In This Period How Did Sales In This Period Compare With Sales In The Compare With Sales In The Previous Period?Previous Period?

Year Quarter Month Sales ResultTotal 97Total Q1 120Jan 30Feb 40

Q1

Mar 50

Total Q4 285Oct 80Nov 100

1997

Q4

Dec 105Total 98Total Q1 170Jan 50Feb 55

Q1

Mar 65

Total Q4 275Oct 90Nov 100

1998

Q4

Dec 85

20

10

50

-10

Sales

How Did Sales In The Current How Did Sales In The Current Period Compare With The Period Compare With The Same Period In The Previous Same Period In The Previous Year?Year?

Year Quarter Month Sales ResultTotal 97Total Q1 120Jan 30Feb 40

Q1

Mar 50

Total Q4 285Oct 80Nov 100

1997

Q4

Dec 105Total 98Total Q1 170Jan 50Feb 55

Q1

Mar 65

Total Q4 275Oct 90Nov 100

1998

Q4

Dec 85

20

(Measures,Sales,Time.CurrentMember)-(Measures,Sales,Time.CurrentMember)-(Measures.Sales,ParallelPeriod(Year,1,Time.CurrentMember))(Measures.Sales,ParallelPeriod(Year,1,Time.CurrentMember))

Sales

How Did Sales In The Current Period How Did Sales In The Current Period Compare With The Same Period In Compare With The Same Period In the Previous Year?the Previous Year?

Year Quarter Month Sales Result790120 120

Jan 30 30Feb 40 70

Q1

Mar 50 120200 320

April 65 185May 45 230

Q2

Jun 90 320185 505

Jul 55 375Aug 60 435

Q3

Sep 70 505285 790

Oct 80 585Nov 100 685

1997

Q4

Dec 105 790

What Is My Total Since The What Is My Total Since The Beginning Of The Year?Beginning Of The Year?

Year Quarter Month Sales Result790120 120

Jan 30 30Feb 40 70

1997Q1

Mar 50 120

Sum(YTD(Time.CurrentMember),Sales)

Time.Mar,Measures.Sales

Time.Feb,Measures.Sales

Time.Jan,Measures.Sales

++++

(Time.CurrentMember,Measures.Sales) + (Measures.YTD,Time.CurrentMember.Prevmember)

YTD

What Is My Total Since The What Is My Total Since The Beginning Of The Year?Beginning Of The Year?

Navigating In The Navigating In The HierarchyHierarchy

(The Family Tree)(The Family Tree)

Jan F eb M a r

Q 1 Q 2 Q 3

O c t N o v D e c

Q 4

97

Q 1 Q 2 Q 3 Q 4

98

A llTime.[97].parent Time.[98].parent

ParentsParents

Jan F eb M a r

Q 1 Q 2 Q 3

O c t N o v D e c

Q 4

97

Q 1 Q 2 Q 3 Q 4

98

A llTime.[97].FirstChild

ChildrenChildren

Jan F eb M a r

Q 1 Q 2 Q 3

O c t N o v D e c

Q 4

97

Q 1 Q 2 Q 3 Q 4

98

A llTime.[97].Children

ChildrenChildren

Jan F eb M a r

Q 1 Q 2 Q 3

O c t N o v D e c

Q 4

97

Q 1 Q 2 Q 3 Q 4

98

A llDescendants(Time.[97], Quarter)

Descendants Descendants

Jan F eb M a r

Q 1 Q 2 Q 3

O c t N o v D e c

Q 4

97

Q 1 Q 2 Q 3 Q 4

98

A llDescendants(Time.[97], Month)

Descendants Descendants

Jan F eb M a r

Q 1 Q 2 Q 3

O c t N o v D e c

Q 4

97

Q 1 Q 2 Q 3 Q 4

98

A ll

Descendants(Time.[Q1], Month)

Descendants Descendants

Jan F eb M a r

Q 1 Q 2 Q 3

O c t N o v D e c

Q 4

97

Q 1 Q 2 Q 3 Q 4

98

A ll

Descendants(Time.[Jan], Month)

Descendants Descendants

The Inventory ProblemThe Inventory Problem A set of inventory snapshots over timeA set of inventory snapshots over time

Dimensions:Dimensions:

Products: All Products, Family, Category, Products: All Products, Family, Category, NameName

Warehouses: All, WarehouseWarehouses: All, Warehouse

Time: Year, Quarter, MonthTime: Year, Quarter, Month Measures:Measures:

Quantity (SUM)Quantity (SUM)

Value (SUM)Value (SUM) The problem: measures are not additive over timeThe problem: measures are not additive over time

Year Quarter Month Inventory790120

Jan 30Feb 40

Q1

Mar 50200

April 65May 45

Q2

Jun 90185

Jul 55Aug 60

Q3

Sep 70285

Oct 80Nov 100

1997

Q4

Dec 105

Problem: Inventory is not an additive value .. Problem: Inventory is not an additive value .. Inventory …….Inventory …….

The Business Problems…The Business Problems…

Average quantities and stock values Average quantities and stock values in each time periodin each time period

Opening and closing balances for Opening and closing balances for each time periodeach time period

Minimum and maximum inventory Minimum and maximum inventory levels in a time periodlevels in a time period

The relative contribution of the The relative contribution of the stocked item to the overall stock valuestocked item to the overall stock value

Avg( Descendants([Time].CurrentMember,[Month]), Quantity)

Sum of quantities over all months in the period divided byThe number of months in the period

Sum (months in the period , Quantity) /Count(months in the period)

Sum (Descendants([Time].CurrentMember,[Month]), Quantity) /

Count(Descendants([Time].CurrentMember,[Month]))

Average Over TimeAverage Over Time

Avg Inv: Avg(Descendants( [Time].CurrentMember, [Months]), Quantity)

Average Over TimeAverage Over Time

Push CurrentCellD = Descendents( CurrentCell Time, Months)For Each t in D

Apply(t, CurrentCell)CurrentCellMeasure = QuantityS = S + GetCellValue(CurrentCell)PopPush CurrentCell

NextC = D CountResult = S / C

([Avg Inv], Q398, WH1)

([Avg Inv], Q398, WH1)GetCellValue(CurrentCell)

Member Formula?No: Return Cell

Member Calculate(CurrentCell)

([Avg Inv], Q398, WH1)

([Avg Inv], Jul98, WH1)(Quantity, Jul98, WH1)

([Avg Inv], 98, WH1)

S = S + Jul98 Quantity in WH1

D = Months in Q398

= Sum of monthly inv./#months

(Quantity, Jul98,WH1) = 55(Quantity, Aug98, WH1) = 60

D = {Jul98, Aug 98, Sep98}

S=55

Result = S/C = 185/3 =61.66

(Quantity, Sep98, WH1) = 70

S = S + Aug98 Quantity in WH1S=115S = S + Sep98 Quantity in WH1S=185

C= Number of Q398 monthsC = 3

([Avg Inv], Aug98, WH1)(Quantity, Aug98, WH1)([Avg Inv], Sep98, WH1)(Quantity, Sep98, WH1)

([Avg Inv], 98, WH1)([Avg Inv], 98, WH1)

Closing BalanceClosing Balance

Quantity of the last month in the period

Quantity of the last month in the months contained in the period

Quantity of the last item in (Descendants([Time].CurrentMember, [Month])

(Measures.Quantity, Tail(Descendants([Time].CurrentMember, [Month]),1))

(Measures.Quantity, ClosingPeriod([Month]))

Year Quarter Month Inventory Result790120

Jan 30 30Feb 40 40

Q1

Mar 50 50200

April 65 65May 45 45

Q2

Jun 90 90185

Jul 55 55Aug 60 60

Q3

Sep 70 70285

Oct 80 80Nov 100 100

1997

Q4

Dec 105 105

50

90

70

105

105

(Inventory,ClosingPeriod(Month))

Closing Period Inventory Value…Closing Period Inventory Value…

Maximum Inventory Level Maximum Inventory Level In ATime SpanIn ATime Span

Measures.[Maximum Inventory Measures.[Maximum Inventory Value]:Value]:

Max(Descendants(Max(Descendants(

Time.CurrentMember,Time.Month), Time.CurrentMember,Time.Month), Measures.[Value])Measures.[Value])

Relative ContributionRelative Contribution

Measures.[Value Contribution to Total]:Measures.[Value Contribution to Total]:

Measures.[Value] / Measures.[Value] /

(( Measures.[Value], Measures.[Value],

Products.[All],Products.[All],

Warehouse.[All Warehouse.[All Warehouse])Warehouse])

RankingRanking

Rank each product by sales among the other productsIn its category

Rank(current product, other products on its category ordered by sales)

Rank(Products.CurrentMember , Order(other products on its category , Sales)

Rank(Products.CurrentMember , Order(

Products.CurrentMember.Parent.Children , Sales))