Introduction to Solving Business Problems with MDX
Download
1 / 59

Introduction to Solving Business Problems with MDX - PowerPoint PPT Presentation


  • 66 Views
  • Uploaded on

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

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

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

Introduction to Solving Business Problems with MDX

Robert Zare and Tom Conlon

Program Managers

Microsoft


Agenda
Agenda

  • MDX basics

  • Time series analysis

  • Multidimensional Navigation

  • Snapshot data analysis


What is mdx
What is MDX?

  • 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 basics
MDX basics

  • 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 vs mdx expressions
MDX Queries vs. MDX Expressions

  • 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)


Mdx myth
MDX myth

“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


Mdx constructs
MDX constructs

  • 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)


Every cell has a name
Every cell has a name...

Groceries

Products

Appliances

Clothing

1997

1998

1999

Time

2000

2001

Measures

Sales

Cost

Units


Every cell has a name1
Every cell has a name...

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

Groceries

Products

Appliances

Clothing

1997

1998

1999

Time

2000

2001

Measures

Sales

Cost

Units


Every cell has a name2
Every cell has a name...

(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


Every cell has a name3
Every cell has a name...

(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


The current cell currentmember
The current cell: CurrentMember

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

(Products.CurrentMember, Measures.CurrentMember,

Time.CurrentMember)

Groceries

Appliances

Clothing

1997

1998

1999

2000

2001

Sales

Cost

Units



Naming cells with relative references1
Naming cells with relative references...

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

Clothing

?

2000

?

Sales


Naming cells with relative references2

?

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 references3

?

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,

Time.[2000].Lead(-3))

Clothing

?

2000

?

Sales


Calculated members
Calculated members

  • Calculated members add significant power to cubes

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

  • Computed at run-time


Agenda1
Agenda

  • MDX basics

  • Time series analysis

  • Multidimensional Navigation

  • Snapshot data analysis



1 how did sales this period compare with sales in the previous period1
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 period2
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 period3
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)




2 how did sales in the current period compare with sales in the same period last year1

(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 year2
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?



3 what have sales been since the beginning of the 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 ...


3 what have sales been since the beginning of the year1

+ 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
Calculated measure: [YTD Sales] the same period last year?


Agenda2
Agenda the same period last year?

  • MDX basics

  • Time series analysis

  • Multidimensional Navigation

  • Snapshot data analysis


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


Parents
Parents the same period last year?


Parents1
Parents the same period last year?

Time.[2000].Parent


Parents2
Parents the same period last year?

Time.[2001].Parent

Time.[2000].Parent


Children
Children the same period last year?

Time.[2000].FirstChild


Children1
Children the same period last year?

Time.[2000].FirstChild


Children2
Children the same period last year?

Time.[2000].Children


Descendants
Descendants the same period last year?

Descendants( Time.[2000], Quarter)


Descendants1
Descendants the same period last year?

Descendants( Time.[2000], Month)


Descendants2
Descendants the same period last year?

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


Descendants3
Descendants the same period last year?

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


Agenda3
Agenda the same period last year?

  • MDX basics

  • Time series analysis

  • Multidimensional Navigation

  • Snapshot data analysis


The inventory problem
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 inventory problem: Semi-additive measures the same period last year?

Problem: Quantity is not additive over time


Business solutions
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
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
2. Closing period inventory value… the same period last year?


2 closing balance
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
Calculated measure: [Closing Balance] the same period last year?


3 maximum quantity during the period
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
Calculated measure: [Maximum Quantity] the same period last year?


Summary
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
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/


ad