RG Ruhrgebiet

1

• RG Ruhrgebiet
• 10.04.2007
• MDX Basics

2
Lecture 7 Topics

• MDX
• Comparison SQL vs. MDX
• Terminology
• Query Parts
• MDX Language

3
SQL vs. MDX

• Select, From, Where
• Two dimensional result
• Select columns
• Result is a relation
• Select from relations

• Select, From, Where
• Multi-dimensional result
• Select sets of cells
• Result is a cube
• Select from a cube

4
Comparison To SQL
5
MDX Basic Terminology

• Cube Conceptual container of values from one
  fact table and all possible aggregations for one
  or more of its dimension hierarchies
• Cell One or more facts (measures) and dimension
  attributes that represent one record in a cube
• Dimension business factor used to group a fact
  record
• Hierarchy Levels of aggregation in a dimension
• Level Aggregation layer in a dimension
  hierarchy
• Member value of a dimension attribute or a
  derived value from a dimension attribute
• Measure Fact. Numerical value that measures a
  business activity and can be summarized
  (usually).
• Tuple collection of members from different
  dimensions

6
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 (SELECTFROM)
• Business modeling definingcalculated members
  using MDX Expressions not a full statement

7
MDX Schema Items

• Cube
• Measure
• Dimension
• Member
• Calculated Member
• Hierarchy
• Level
• Member Property

8
An MDX Statement is either a MDX Query or a MDX
Expression

• Query
• Extract values from an OLAP cube
• Selected values from selected levels of selected
  dimensions of a cube
• Expression Language
• Formula that calculates a single value
• Constant
• Example Use
• Create calculated members in a cube
• Create a dynamic default member

9
MDX Model

• Data model based on dimensions and cubes
• MDX query selects cells from one cube
• Result of an MDX query is a subset of the
  original cube
• Output cube
• Assign any dimension (or combination of
  dimensions) on a result cube dimension (called an
  axis)

10
MDX Queries vs. MDX Expressions

• MDX Queries
• Full statements (SELECTFROM)
• 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)

11
MDX Myth
Only developers need to know MDX

• Fact MDX is used everywhere
• Calculated members
• Security settings
• Custom member rollup
• Custom level rollup
• Actions
• Define named Sets
• Calculated cells

12
Cube Characteristics

• Dimensions of a cube are a subset of the
  dimensions of a fact table in a star schema.
• A cube contains all possible values for all
  members of all levels of all dimensions defined
  in that cube.
• Regular Cube
• Not a linked, virtual, or local cube
• Most common
• Write-enable, real-time, and distributed cubes
  are regular cubes with special storage support
• Linked Cube cube alias for a cube on a remote
  server
• Virtual Cube
• Cube based on one or more regular cubes that
  shares at least one common dimension
• Similar to a view in a relational database
• Local Cube cube on a client

13
Dimension Characteristics

• Regular Dimension
• Shared dimension usable by multiple cubes
• Private dimension that belongs to one cube only
• Virtual Dimension
• Dimension that uses member properties of another
  regular dimension
• Parent-Child Dimension
• Changing Dimension

14
Hierarchy Characteristics

• Balanced
• No empty levels
• Unbalanced
• Skipped or empty levels
• Different number of ancestors
• Ragged
• Unbalanced hierarchy where branches descend
  through all levels (even if no data)
• Uses a placeholder for missing data
• Multiple hierarchies possible in a dimension

15
Calculated Members

• Perform calculations on aggregated values
• Store formula (as MDX expression) in the cube
• Formula values calculated at query time
• Formulas more complex than aggregations that are
  stored in a cube
• Ex. average, weighted average
• Limitations
• Calculate for all members of a hierarchy level of
  dimension
• Not recursive calculate in one pass
• Calculated cells calculations on a sub-cube

16
Member Properties

• Purpose
• Add secondary business information to members in
  a dimension
• MDX Retrieval
• Use DIMENSION PROPERTIES MDX expression to define
  an axis, or
• Create a calculated member from the member
  property
• Examples
• SELECT
• Measures.Units Ordered., Measures.Unit
  Shipped ON COLUMNS,
• NON EMPTY Store.Store Name.MEMBERS
• DIMENSION PROPERTIES Store.Store Name.Store
  Sqft ON ROWS
• FROM Warehouse
• WITH
• MEMBER Measures.Store Sqft AS
  Store.CURRENTMEMBER.PROPERTIES(Store SQFT)
• SELECT
• Measures.Store Sqft, Measures.Units
  Ordered., Measures.Unit Shipped ON
  COLUMNS,
• NON EMPTY Store.Store Name.MEMBERS ON ROWS
• FROM Warehouse

17
MDX Model Tuple

• Tuple collection of members from some number of
  dimensions in a cube
• Can have only one member from each dimension
• Example Minnesota, 2003
• A tuple with a single member is a simple tuple or
  degenerate tuple
• A tuple that contains one member from each
  dimension of a cube identifies a unique value in
  the cube
• OR
• For each value you see in an output cell, you can
  determine a tuple that includes one member from
  each dimension in the cube
• OR
• Every value in an output cube requires a single
  member from each dimension

18
MDX Model Set

• Multiple members on a single axis
• An ordered collection of tuples
• Position
• MDX set may contain the same tuple more than one
  (unlike a mathematical set)
• Every tuple must have the same dimensionality
• To create a set
• , or
• Use MDX function that returns a set
• Ex. Generate()

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

20
Every Cell Has A Name...
Groceries
Products
Appliances
Clothing
1997
1998
1999
Time
2000
2001
Measures
Sales
Cost
Units
21
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
22
Every Cell Has A Name...
(Products.Clothing, Measures.Units, Time.2000)
(Products.Clothing, Measures.Sales, Time.1999)
Groceries
Appliances
Products
Clothing
1997
1998
1999
Time
2000
2001
Measures
Sales
Cost
Units
23
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
Appliances
Products
Clothing
1997
1998
1999
Time
2000
2001
Measures
Sales
Cost
Units
24
The Current Cell Currentmember
(Products.Clothing, Measures.Units, Time.2000)
(Products.CurrentMember, Measures.CurrentMembe
r, Time.CurrentMember)
Groceries
Appliances
Clothing
1997
1998
1999
2000
2001
Sales
Cost
Units
25
Naming Cells With Relative References...
Clothing
?
2000
Sales
26
Naming Cells With Relative References...
(Products.Clothing, Measures.Sales,
Time.2000.PrevMember)
Clothing
?
2000
?
Sales
27
Naming Cells With Relative References...
(Products.Clothing, Measures.Sales,
Time.2000.PrevMember)
(Products.Clothing, Measures.Sales,
Time.2000.NextMember)
Clothing
?
2000
?
Sales
28
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
29
Examples

• Examples

30
Zero axes Returns a single-cell value
31
(No Transcript)
32
(No Transcript)
33
Basic MDX Query

• WITH
• MEMBER
• SET
• SELECT ltaxis_specificationgt ,
  ltaxis_specificationgt...
• FROM ltcube_specificationgt
• WHERE ltslicer_specificationgt

34
Sections of a Basic MDX Query

• Cube source cube for query
• Axes
• Collection of members from different dimensions
  organized as a set of tuples
• For example, an axis that includes Time and
  Product dimensions will result in aggregation
  along these dimensions (cell will include members
  of these two dimensions)
• Members selected dimension attributes that are
  included in output cube
• Measures (facts) of a fact table are treated as
  members of a Measures dimension
• Slice filter that selects cells in output cube

35
Example MDX Query

• SELECT
• Measures.StoreSales on columns,
• Time.2002.Q3, Time.2002.Q4 on rows
• FROM SalesCube
• WHERE (Store.States.USA)

36
(No Transcript)
37
Syntax Conventions

• OLEDB for OLAP Specification
• Functions
• Some use VB-style functional notation
• Ex. Order() function
• Some use object format
• Ex. hierarchy.Name
• Axis Order
• columns, rows, pages, chapters, sections
• Each axis is numbered
• Ex. Time.Members on Axis (0)

38
MDX Notation for schema

• Periods separate schema parts
• Ex. Products.Food.Dairy
• No spaces in names unless square brackets are
  used
• Examples
• Dimension Time
• Hierarchy Time.Fiscal
• Level Time.Fiscal.2000.Q3
• Member Time.Fiscal.2000.Q2.May

39
WITH Operator

• Use to
• Specify a calculated member
• Define named sets
• Example
• WITH
• MEMBER Measures.DaysWorked AS
• Measures.EndDate Measures.StartDate
• SELECT
• Measures.StartDate, Measures.EndDate,
  Measures.DaysWorked ON COLUMNS,
• Employee.Department ON ROWS
• FROM workCube

40
WHERE (slice)

• IMPORTANT NOTE
• If a dimension does not appear on a filter axis,
  the default member of that dimension is used
• Usually, the default is the ALL member

41
Common Operators and Functions

• Comma construct a set by enumerating tuples
• Ex. Time.2001.Jan, Time.2001.Feb.
• Colon construct a set by specifying a range
• Ex. Time.2001.Jan Time.2001.Nov
• .Members returns set of all members
• Ex. Customers.Members
• Comments
• / / - can be multi-line
• // - to end of line
• -- - to end of line

42
Common Operators and Functions (contd)

• CrossJoin() cross-product of members or tuples
  in two different sets
• Product all possible combinations
• Ex. Construct a set
• Ex. CrossJoin (
• Time.2000.Jan Time.2000.Dec
  , Product.Brand.Members )

. . .
43
Common Operators and Functions (contd)

• Filter() reduce a set by including only those
  elements that satisfy some criteria
• Arguments
• Boolean Expression
• Set
• Returns subset
• Ex. Filter (
• Product.Brand.Members ,
• Measures.Sales gt 500
• )

44
Common Operators and Functions (contd)

• Order() order a set
• Arguments
• Ordering criterion
• Set
• Flag option (ex. Ascending, descending)
• Hierarchical order can be complex
• Ex. Order (
• Product.Brand.Members ,
• (Measures.Sales, Time.2000),
• BDESC
• )

45
Boolean Operators

• Equal
• lt Less than
• lt Less than or equal
• gt Greater than
• gt Greater than or equal
• ltgt Not equal
• IsEmpty(Expression)
• If expression is an empty set, return true
• AND
• OR
• NOT

46
Example

• SELECT
• Time.2001.Aug,
• Product.Bandaids ON COLUMNS
• FROM medSupplyCube
• WHERE Measures.Costs