Mdx overview
Download
1 / 29

MDX Overview - PowerPoint PPT Presentation


  • 115 Views
  • Uploaded on

MDX Overview. What Is MDX?. MDX is M ulti D imensional E X pressions 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 all advanced analytical capabilities of Analysis Services. Comparison To SQL. MDX Basics.

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 'MDX Overview' - arich


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

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 forOLAP spec

  • MDX is the key for all advanced analytical capabilities ofAnalysis 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. MDXExpressions

  • MDX Queries

    • Full statements (SELECT…FROM)

    • Usually generated by a query tools and applications such as Excel

    • MDX Sample App deals in queries

  • MDX Expressions

    • Partial MDX statements

    • Define a calculated member, or a set, or member properties, etc.

    • Returns a single value (which maybe a set)


Mdx myth
MDX Myth

  • Fact: MDX is used everywhere:

  • Calculated members

  • Security settings

  • Custom member rollup

  • Custom level rollup

  • Actions

  • Define 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...

Groceries

Products

Appliances

Clothing

1997

1998

1999

Time

2000

2001

Measures

Sales

Cost

Units

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


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



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)


Mdx overview

Results For Question 1: Previous Period?



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)


Mdx overview

Results For Question 2: The Same Period Last Year?



Mdx overview

Results For Question 3: The Same Period Last Year?


Other useful family function
Other useful The Same Period Last Year?family function

  • .Parent

  • .Children

  • Ancestor()

  • Cousin()

  • .FirstChild

  • .LastChild

  • .FirstSibling

  • .LastSibling


Questions
Questions The Same Period Last Year?