mdx overview
Download
Skip this Video
Download Presentation
MDX Overview

Loading in 2 Seconds...

play fullscreen
1 / 29

MDX Overview - PowerPoint PPT Presentation


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

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

(Time.CurrentMember, Measures.Sales)-

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

other useful family function
Other useful family function
  • .Parent
  • .Children
  • Ancestor()
  • Cousin()
  • .FirstChild
  • .LastChild
  • .FirstSibling
  • .LastSibling
ad