1 / 59

# Introduction to Solving Business Problems with MDX - PowerPoint PPT Presentation

Introduction to Solving Business Problems with MDX. Robert Zare and Tom Conlon Program Managers Microsoft. Agenda. MDX basics Time series analysis Multidimensional Navigation Snapshot data analysis. What is MDX?. MDX is M ulti D imensional E X pressions

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about ' Introduction to Solving Business Problems with MDX' - glyn

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Robert Zare and Tom Conlon

Program Managers

Microsoft

• MDX basics

• Time series analysis

• Snapshot data analysis

• MDX is Multi Dimensional EXpressions

• MDX is the syntax for querying an Analysis Services database

• MDX is part of the OLE DB for OLAP spec

• MDX is the key for to utilizing the advanced analytical capabilities of Analysis Services

• MDX allows easy navigation in the multi dimensional space

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

• It is used for

• Queries – full statements (SELECT…FROM)

• Business modeling – definingcalculated members using MDX Expressions – not a full statement

• MDX Queries

• Full statements (SELECT…FROM)

• Most often generated by end-user query tools and applications such as Excel or Data Analyzer

• MDX Sample App deals in queries

• MDX Expressions

• Partial MDX statements

• Define an analytical object such as calculated member, or named set

• Return a single value (which may be a set)

“Only developers need to know MDX”

• Fact: MDX is used everywhere:

• Calculated members

• Security settings

• Custom member formula

• Custom level formula

• Actions

• Named Sets

• Calculated Cells

• Members: an item in a hierarchy

• [John Doe]

• [2001]

• [2001].[Q1].[Jan]

• Tuple: an intersection of 2 or more members

• ([Product].[Drink].[Beverages], [Customers].[USA])

• ([Product].[Non-Consumable], [2001])

• Sets: a group of tuples or members

• {[John Doe], [Jane Doe]}

• { ( [Non-Consumable], USA ), ( Beverages, Mexico ) }

• [2001].Children

• TopCount(Store.[Store Name].Members, 10, Sales)

Groceries

Products

Appliances

Clothing

1997

1998

1999

Time

2000

2001

Measures

Sales

Cost

Units

(Products.Clothing, Measures.Units, Time.[2000])

Groceries

Products

Appliances

Clothing

1997

1998

1999

Time

2000

2001

Measures

Sales

Cost

Units

(Products.Clothing, Measures.Units, Time.[2000])

(Products.Clothing, Measures.Sales, Time.[1999])

Groceries

Products

Appliances

Clothing

1997

1998

1999

Time

2000

2001

Measures

Sales

Cost

Units

(Products.Clothing, Measures.Units, Time.[2000])

(Products.Clothing, Measures.Sales, Time.[1999])

(Products.Groceries, Measures.Cost, Time.Year.[1997])

Groceries

Products

Appliances

Clothing

1997

1998

1999

Time

2000

2001

Measures

Sales

Cost

Units

(Products.Clothing, Measures.Units, Time.[2000]) =

(Products.CurrentMember, Measures.CurrentMember,

Time.CurrentMember)

Groceries

Appliances

Clothing

1997

1998

1999

2000

2001

Sales

Cost

Units

Clothing

?

2000

Sales

(Products.Clothing, Measures.Sales, Time.[2000].PrevMember)

Clothing

?

2000

?

Sales

Naming cells with relative references...

(Products.Clothing, Measures.Sales, Time.[2000].PrevMember)

(Products.Clothing, Measures.Sales, Time.[2000].NextMember)

Clothing

?

2000

?

Sales

Naming cells with relative references...

(Products.Clothing, Measures.Sales, Time.[2000].PrevMember)

(Products.Clothing, Measures.Sales, Time.[2000].NextMember)

(Products.Clothing, Measures.Sales.PrevMember, Time.[2000].Lag(3))

OR

(Products.Clothing,

Measures.Sales.PrevMember,

Clothing

?

2000

?

Sales

• Calculated members add significant power to cubes

• Pre-define complex business logic (so that user does not have to)

• Computed at run-time

• MDX basics

• Time series analysis

• Snapshot data analysis

1. How did Sales this period compare with Sales in the previous period?

(Time.CurrentMember, Measures.CurrentMember) =

1. How did Sales this period compare with Sales in the previous period?

(Time.CurrentMember, Measures.CurrentMember) =

(Time.CurrentMember, Measures.Sales) -

1. How did Sales this period compare with Sales in the previous period?

(Time.CurrentMember, Measures.CurrentMember) =

(Time.CurrentMember, Measures.Sales) -

(Time.CurrentMember.PrevMember, Measures.Sales)

(Time.CurrentMember, Measures.Sales)- the same period last year?

(Time.CurrentMember.Lag(12), Measures.Sales)

2. How did Sales in the current period compare with Sales in the same period last year?

2. How did Sales in the current period compare with Sales in the same period last year?

(Time.CurrentMember, Measures.Sales)-

No!

(Time.CurrentMember.Lag(12), Measures.Sales)

(ParallelPeriod(Year,1,Time.CurrentMember),

Measures.Sales)

Calculated measure: [Sales Growth Y/Y] the same period last year?

+ the same period last year?

Time.Feb,Measures.Sales

3. What have Sales been since the beginning of the year?

+

Time.Jan,Measures.Sales

Time.Mar,Measures.Sales ...

+ the same period last year?

Time.Feb,Measures.Sales

Sum(YTD(Time.CurrentMember), Sales)

3. What have Sales been since the beginning of the year?

+

Time.Jan,Measures.Sales

NO!

=

Time.Mar,Measures.Sales

Calculated measure: [YTD Sales] the same period last year?

Agenda the same period last year?

• MDX basics

• Time series analysis

• Snapshot data analysis

Navigating the hierarchy the same period last year?(The Family Tree)

Parents the same period last year?

Parents the same period last year?

Time.[2000].Parent

Parents the same period last year?

Time.[2001].Parent

Time.[2000].Parent

Children the same period last year?

Time.[2000].FirstChild

Children the same period last year?

Time.[2000].FirstChild

Children the same period last year?

Time.[2000].Children

Descendants the same period last year?

Descendants( Time.[2000], Quarter)

Descendants the same period last year?

Descendants( Time.[2000], Month)

Descendants the same period last year?

Descendants(Time.[2000].[Jan], Month)

Descendants the same period last year?

Descendants(Time.[2000].[Jan], Month)

Agenda the same period last year?

• MDX basics

• Time series analysis

• Snapshot data analysis

The Inventory Problem the same period last year?

• A set of inventory snapshots over time

• Dimensions

• Products: (All), Family, Category, Name

• Warehouses: (All), Warehouse

• Time: Year, Quarter, Month

• Store: (All), Country, City, Store

• Measures

• Quantity [default aggregation=sum]

• The problem:

• Measures are not additive over time

The inventory problem: Semi-additive measures the same period last year?

Problem: Quantity is not additive over time

Business solutions… the same period last year?

• Average quantities in each time period

• Opening and closing balances for each time period

• Minimum and maximum inventory levels in a time period

1. Average over time the same period last year?

• Sum of quantities over all months in the period, divided by the 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]))

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

Calculated measure: [Average balance] the same period last year?

2. Closing period inventory value… the same period last year?

2. Closing balance the same period last year?

• Quantity of the last month in the period

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

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

• (ClosingPeriod([Month], Time.CurrentMember),Measures.Quantity)

Calculated measure: [Closing Balance] the same period last year?

3. Maximum Quantity During the Period the same period last year?

• We want the Maximum monthly inventory value for each period

• Max( Descendants( Time.CurrentMember, Month), Measures.Quantity )

• Why did we calculate using Descendants(…, Month)? Why not use the Children function?

Calculated measure: [Maximum Quantity] the same period last year?

Summary the same period last year?

• MDX is everywhere

• Calculated members are the most common application of MDX in cube modeling

• Understand the fundamental concepts: CurrentMember, .Parent, .Children, Descendants, etc. as these are the building blocks for many expressions

More Information the same period last year?

• Books

• Step by Step Analysis Services

• Jacobsen

• Microsoft OLAP Solutions

• Thomsen, Spofford, Chase

• MDX Solutions

• Spofford

• Online

• Public Newsgroup

• Microsoft.public.sqlserver.olap

• MSN Community

• http://groups.msn.com/MicrosoftOLAPServicesUsersCommunity/_homepage.msnw?pgmarket=en-us

• Microsoft.com

• http://microsoft.com/sql/evaluation/bi/default.asp

Don’t forget to complete the the same period last year?on-line Session Feedback form on the Attendee Web sitehttps://web.mseventseurope.com/teched/