Atlanta MDF MDX Overview What Is MDX MDX

Atlanta. MDF MDX Overview

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 all advanced analytical capabilities of Analysis Services

Comparison To SQL Construct OLAP construct SELECT… (MDX) CREATE… DSO object model DROP… INSERT… DELETE…

MDX Basics • • • MDX allows easy navigation in the multi dimensional space It “understands” the MD concepts of cube, dimension, level, member and cell It is used for – Queries – full statements (SELECT…FROM) – Business modeling – defining calculated members using MDX Expressions – not a full statement

MDX Queries vs. MDX Expressions • MDX Queries • MDX Expressions – Full statements (SELECT…FROM) – Usually generated by a query tools and applications such as Excel – MDX Sample App deals in queries – Partial MDX statements – Define a calculated member, or a set, or member properties, etc. – Returns a single value (which may be a set)

MDX Myth “Only developers need to know MDX” Fact: MDX is used everywhere: l Calculated members l Security settings l Custom member rollup l Custom level rollup l Actions l Define named “Sets” l Calculated cells

MDX Constructs • Members: an item in a hierarchy – [John Doe] – [2001]. [Q 1]. [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 Top. Count(Store. [Store Name]. Members, 10, Sales)

Pr od uc ts Every Cell Has A Name. . . Groceries Appliances Clothing 1997 Time 1998 1999 2000 2001 t s s Sale Cos Unit Measures

Every Cell Has A Name. . . Pr od uc ts (Products. Clothing, Measures. Units, Time. [2000]) Groceries Appliances Clothing 1997 Time 1998 1999 2000 2001 t s s Sale Cos Unit Measures

Every Cell Has A Name. . . (Products. Clothing, Measures. Units, Time. [2000]) (Products. Clothing, Measures. Sales, Time. [1999]) Pr od uc ts Groceries Appliances Clothing 1997 Time 1998 1999 2000 2001 t s s Sale Cos Unit Measures

Every Cell Has A Name. . . (Products. Clothing, Measures. Units, Time. [2000]) (Products. Clothing, Measures. Sales, Time. [1999]) (Products. Groceries, Measures. Cost, Time. Year. [1997]) Pr od uc ts Groceries Appliances Clothing 1997 Time 1998 1999 2000 2001 t s s Sale Cos Unit Measures

The Current Cell: Currentmember (Products. Clothing, Measures. Units, Time. [2000]) = (Products. Current. Member, Measures. Current. Member, Time. Current. Member) Groceries Appliances Clothing 1997 1998 1999 2000 2001 Sales Cost Units

Naming Cells With Relative References. . . Clothing ? 2000 Sales

Naming Cells With Relative References. . . (Products. Clothing, Measures. Sales, Time. [2000]. Prev. Member) Clothing ? 2000 ? Sales

Naming Cells With Relative References. . . (Products. Clothing, Measures. Sales, Time. [2000]. Prev. Member) (Products. Clothing, Measures. Sales, Time. [2000]. Next. Member) Clothing ? ? 2000 ? Sales

Naming Cells With Relative References. . . (Products. Clothing, Measures. Sales, Time. [2000]. Prev. Member) (Products. Clothing, Measures. Sales, Time. [2000]. Next. Member) (Products. Clothing, Measures. Sales. Prev. Member, Time. [2000]. Lag(3)) OR Clothing ? (Products. Clothing, Measures. Sales. Prev. Member, Time. [2000]. Lead(-3)) ? 2000 ? Sales

Calculated Members • n n n Calculated members add significant power to Analysis cubes Pre-define complex business logic Usually create calculated measures Computed at runtime

1. How Did Sales This Period Compare With Sales In The Previous Period? Year Qtr Mon 2000 Q 1 Jan Feb Mar Q 2 Apr May Jun Q 3 Jul Aug Sep Q 4 Oct Nov Dec Sales 790 120 30 40 50 200 65 45 90 185 55 60 70 285 80 105

1. How Did Sales This Period Compare With Sales In The Previous Period? Qtr Mon Q 2 Sales Delta 200 80 Apr 65 15 May 45 -20 Jun 90 45 (Time. Current. Member, Measures. Current. Member) =

1. How Did Sales This Period Compare With Sales In The Previous Period? Qtr Mon Q 2 Sales Delta 200 80 Apr 65 15 May 45 -20 Jun 90 45 (Time. Current. Member, Measures. Current. Member) = (Time. Current. Member, Measures. Sales) -

1. How Did Sales This Period Compare With Sales In The Previous Period? Qtr Mon Q 2 Sales Delta 200 80 Apr 65 15 May 45 -20 Jun 90 45 (Time. Current. Member, Measures. Current. Member) = (Time. Current. Member, Measures. Sales) (Time. Current. Member. Prev. Member, Measures. Sales)

Results For Question 1: Year Qtr Mon 2000 Q 1 Jan Feb Mar Q 2 Apr May Jun Q 3 Jul Aug Sep Q 4 Oct Nov Dec Sales 790 120 30 40 50 200 65 45 90 185 55 60 70 285 80 105 Delta 10 10 80 15 -20 45 -15 -35 15 10 10 20 5

2. How Did Sales In The Current Period Compare With Sales In The Same Period Last Year? Year Qtr Mon 2000 Q 1 Jan Feb Mar Q 4 Oct Nov Dec 2001 Q 1 Jan Feb Mar Q 4 Oct Nov Dec Sales 790 120 30 40 50 285 80 105 850 170 50 55 65 275 90 100 85

2. How Did Sales In The Current Period Compare With Sales In The Same Period Last Year? Year Qtr Mon 2000 Q 1 Jan Feb Mar 2001 Q 1 Jan Feb Mar Sales Annual Delta 790 120 30 40 50 850 170 50 55 65 20 (Time. Current. Member, Measures. Sales)(Time. Current. Member. Lag(12), Measures. Sales)

2. How Did Sales In The Current Period Compare With Sales In The Same Period Last Year? Year Qtr Mon 2000 Q 1 Jan Feb Mar 2001 Q 1 Jan Feb Mar Sales Annual Delta 790 120 30 40 50 850 170 50 55 65 20 (Time. Current. Member, Measures. Sales)(Time. Current. Member, Lag(12), Measures. Sales) (Parallel. Period(Year, 1, Time. Current. Member), Measures. Sales)

Results For Question 2: Year Qtr Mon 2000 Q 1 Jan Feb Mar Q 4 Oct Nov Dec 2001 Q 1 Jan Feb Mar Q 4 Oct Nov Dec Sales 790 120 30 40 50 285 80 105 850 170 50 55 65 275 90 100 85 Ann. Delta 60 50 20 15 15 -10 10 0 -20

3. What Have My Sales Been Since The Beginning Of The Year? Year Qtr Mon 2000 Q 1 Jan Feb Mar Q 2 Apr May Jun Q 3 Jul Aug Sep Q 4 Oct Nov Dec Sales 790 120 30 40 50 200 65 45 90 185 55 60 70 285 80 105

3. What Have Sales Been Since The Beginning Of The Year? Year Qtr Mon Sales 2000 YTD 790 Q 1 120 Jan 30 30 Feb 40 70 Mar 50 120 Time. Jan, Measures. Sales Time. Feb, Measures. Sales + + Time. Mar, Measures. Sales. . .

3. What Have Sales Been Since The Beginning Of The Year? Year Qtr Mon Sales 2000 YTD 790 Q 1 120 Jan 30 30 Feb 40 70 Mar 50 120 + + Time. Mar, Measures. Sales = Time. Jan, Measures. Sales Time. Feb, Measures. Sales Sum(YTD(Time. Current. Member), Sales)

Results For Question 3: Year Qtr Mon 2000 Q 1 Jan Feb Mar Q 2 Apr May Jun Q 3 Jul Aug Sep Q 4 Oct Nov Dec Sales 790 120 30 40 50 200 65 45 90 185 55 60 70 285 80 105 YTD 120 30 70 120 320 185 230 320 505 375 435 505 790 585 685 790

Questions