multidimensional-expression-part-4

How do I group dimension members dynamically in MDX?

You can create calculated members for dimension and then use them in the query. Example below will create 3 calculated members based on filter condition:


With MEMBER [Product].[Category].[Result1] AS 'Aggregate(Filter([Product].[Category].[All].Children, [Product].[Category].currentmember.Properties("Key") > "3"))'
MEMBER [Product].[Category].[Result2] AS 'Aggregate(Filter([Product].[Category].[All].Children, [Product].[Category].currentmember.Properties("Key") = "3"))'
MEMBER [Product].[Category].[Result3] AS 'Aggregate(Filter([Product].[Category].[All].Children, [Product].[Category].currentmember.Properties("Key") > "3"))'
SELECT NON EMPTY {[MEasures].[Order Count]} ON COLUMNS
,{[Product].[Category].[Result1],[Product].[Category].[Result2],[Product].[Category].[Result3]} ON ROWS
FROM [Adventure Works]


Result will be:
Order Count
Result1 19,523
Result2 9,871
Result3 19,523

How you can add another dimension as a row on your sample code?
Just add new members for new dimension and add same members in you row details.

How do you write MDX query that returns measure ratio to parent value?
Below is example on how is ratio calculated for measure [Order Count] using Date dimension. Using parent function, your MDX is in dependent on level that you are querying data on. In example below, if you query data at year level, ratio will be calculated to level [All]:


WITH MEMBER [Measures].[Order Count Ratio To Parent] AS
IIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0
, NULL
, [Measures].[Order Count]
/
([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent)
)
, FORMAT_STRING = "Percent"

SELECT {[Measures].[Order Count], [Measures].[Order Count Ratio To Parent]} ON 0
, {DESCENDANTS([Date].[Calendar].[All Periods], 1), [Date].[Calendar].[All Periods]
} ON 1
FROM [Adventure Works]

Result will be like:

Order Count Order Count Ratio To Parent
CY 2001 1,379 4.38%
CY 2002 3,692 11.74%
CY 2003 12,440 39.55%
CY 2004 13,944 44.33%
CY 2006 (null) (null)
All Periods 31,455 (null)

If you query data at month level, ratio will be calculated comparing to level quarter:

WITH MEMBER [Measures].[Order Count Ratio To Parent] AS
IIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0
, NULL
, [Measures].[Order Count]
/
([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent)
)
, FORMAT_STRING = "Percent"

SELECT {[Measures].[Order Count]
, [Measures].[Order Count Ratio To Parent]} ON 0
, {DESCENDANTS([Date].[Calendar].[Calendar Quarter].&[2003]&[4], 1)
, [Date].[Calendar].[Calendar Quarter].&[2003]&[4]
} ON 1
FROM [Adventure Works]


Result will be like:

Order Count Order Count Ratio To Parent
October 2003 1,779 29.95%
November 2003 1,888 31.79%
December 2003 2,272 38.26%
Q4 CY 2003 5,939 58.87%

Multidimensional Expression Part 3

MDX query to get sales by product line for specific period plus number of months with nonempty sales?

Solution:
You can use COUNT () function with Exclude Empty option. For count function you specify set that is cross join of Date members at the month level and measure that you are interested in.

WITH Member [Measures].[Months With Above Zero Sales] AS
COUNT(
DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Month]) * [Measures].[Sales Amount]
, ExcludeEmpty
)
SELECT {[Measures].[Sales Amount], [Measures].[Months With Above Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])

Result will be:

Sales Amount Months With Above Zero Sales
Accessory $1,987,396.37 19
Components $454,644.34 18
Mountain $24,430,307.51 19
Road $24,919,506.74 19
Touring $16,010,837.10 13

Same you can apply for Years:

WITH Member [Measures].[Years With Above Zero Sales] AS
COUNT(
DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Years]) * [Measures].[Sales Amount]
, ExcludeEmpty
)
SELECT {[Measures].[Sales Amount], [Measures].[Years With Above Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])


Result will be:
Sales Amount Years With Above Zero Sales
Accessory $1,987,396.37 2
Components $454,644.34 2
Mountain $24,430,307.51 2
Road $24,919,506.74 2
Touring $16,010,837.10 2

You can change Measures:

WITH Member [Measures].[Months With Above Zero Sales] AS
COUNT(
DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Month]) * [Measures].[Internet Sales Amount]
, ExcludeEmpty
)
SELECT {[Measures].[Internet Sales Amount], [Measures].[Months With Above Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])

Multidimensional Expression (MDX) – part 2

Many developer start MDX query learning with MDX tutorial, learning tuples, namedsets, axis, select query syntax etc….. But still after that, many queries remain to resolve and need hands-on experience of some real time project when one really starts applying functions, tweaking or nesting queries and parameters.
In this session will start with some complex query with adventure works database.
Hope this helps all to resolve last movement MDX requirements when developers don’t have time to learn all the syntax, structure and theory:

How to find bottom 10 customers with lowest sales in 2003 that were not null?
Simple using BOTTOMCOUNT will return customers with null sales and we can use NONEMPTY or FIlTER to remove null data.

SYNTAX:

SELECT {[Measures].[Internet Sales Amount] } ON COLUMNS,
BOTTOMCOUNT(NONEMPTY(DESCENDANTS([Customer].[Customer Geography].[All Customers]
, [Customer].[Customer Geography].[Customer])
, ([Measures].[Internet Sales Amount]))
, 10
,( [Measures].[Internet Sales Amount])
) ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Calendar].Calendar Year].&[2003])

Result will be:
Internet Sales Amount
Ariana Peterson $2.29
Olivia Brown $2.29
Abigail L. Bennett $2.29
Natalie L. Bryant $2.29
Madison D. Lee $2.29
Lauren Miller $2.29
Stephanie B. Murphy $2.29
Cameron L. Rodriguez $2.29
Melanie Peterson $2.29
Alfredo Romero $2.29

Multidimensional Expressions (MDX): PART 1

Multidimensional Expressions (MDX) lets you query multidimensional objects, such as cubes, and return multidimensional cell sets that contain the cube's data.

Microsoft SQL Server OLAP Services provides architecture for access to multidimensional data. This data is summarized, organized, and stored in multidimensional structures for rapid response to user queries. Through OLE DB for OLAP, a PivotTable Service provides client access to this multidimensional online analytical processing (OLAP) data. For expressing queries to this data, OLE DB for OLAP employs full-fledged, highly functional expression syntax: multidimensional expressions (MDX).

We are assuming the reader is familiar with multidimensional data warehousing and OLAP terms. Before talking about MDX and how it queries data, it is worthwhile to give a brief description of the structure of a cube.

Cubes:

Cubes are key elements in online analytic processing. They are subsets of data from the OLAP store, organized and summarized into multidimensional structures. These data summaries provide the mechanism that allows rapid and uniform response times to complex queries.

Cubes are the main objects in online analytic processing (OLAP), a technology that provides fast access to data in a data warehouse. A cube is a set of data that is usually constructed from a subset of a data warehouse and is organized and summarized into a multidimensional structure defined by a set of dimensions and measures.

The fundamental cube concepts to understand are dimensions and measures.

 Dimensions provide the categorized descriptions by which the measures are separated for analysis such as Customers, geographical information and Products etc.

 Measures identify the numerical values that are summarized for analysis, such as price, cost, or quantity sold.

A dimension can be created for use in an individual cube or in multiple cubes. A dimension created for an individual cube is called a private dimension, whereas a dimension that can be used by multiple cubes is called a shared dimension.

Pre-calculated summary data called aggregations provides the mechanism for rapid and uniform response times to queries. Aggregations are created for a cube before end users access it. The results of a query are retrieved from the aggregations, the cube's source data in the data warehouse, a copy of this data on the Analysis server, the client cache, or a combination of these sources. An Analysis server can support many different cubes, such as a cube for sales, a cube for inventory, a cube for customers, and so on.

Every cube has a schema, which is the set of joined tables in the data warehouse from which the cube draws its source data. The central table in the schema is the fact table, the source of the cube's measures. The other tables are dimension tables, the sources of the cube's dimensions.

Each cube dimension can contain a hierarchy of levels to specify the categorical breakdown available to users. For example, a Store dimension might include the following level hierarchy: Country, State, City, and Store Name. Each level in a dimension is of a finer grain than its parent. Similarly, the hierarchy of a time dimension might include levels for year, quarter, and month. Multiple hierarchies can exist for a single dimension.

For Example: The fiscal period hierarchy levels Fiscal Year, Fiscal Quarter, and Month. The calendar hierarchy levels Calendar Year, Calendar Quarter, and Month.

One final important item of note is the concept of a member. A member is nothing more than an item in a dimension or measure. A calculated member is a dimension member whose value is calculated at run time using a specified expression. Calculated members can also be defined as measures. Only the definitions for calculated members are stored; values are calculated in memory when needed to answer a query.

Getting Starters with MDX:

Let's start by outlining one of the simplest forms of an MDX expression, bearing in mind this is for an outline of an expression returning two cube dimensions:

SELECT (query) axis specification ON COLUMNS,
(query) axis specification ON ROWS
FROM cube_name
WHERE (slicer) axis specification

A SELECT clause that determines the query axes of an MDX SELECT statement. A FROM clause that determines which cube will be queried, An Optional WHERE clause that determines which members or tuples to use on the slicer axis to restrict the data returned.

Comparing the Syntax of the MDX SELECT Statement to SQL

The syntax format for the MDX SELECT statement is similar to that of SQL syntax. However, there are several fundamental differences:

1. MDX syntax distinguishes sets by surrounding tuples or members with braces (the { and } characters.)
2. MDX queries can have 0, 1, 2 or up to 128 query axes in the SELECT statement. Each axis behaves in exactly the same way, unlike SQL where there are significant differences between how the rows and the columns of a query behave.
3. As with an SQL query, the FROM clause names the source of the data for the MDX query. However, the MDX FROM clause is restricted to a single cube. Information from other cubes can be retrieved on a value-by-value basis by using the LookupCube function.
4. The WHERE clause describes the slicer axis in an MDX query. It acts as something like an invisible, extra axis in the query, slicing the values that appear in the cells in the result set; unlike the SQL WHERE clause it does not directly affect what appears on the rows axis of the query. The functionality of the SQL WHERE clause is available through other MDX functions such as the FILTER function.

SELECT Statement Example

The following example shows a basic MDX query that uses the SELECT statement. This query returns a result set that contains the 2002 and 2003 sales and tax amounts for the Southwest sales territories.

SELECT
{ [Measures].[Sales Amount],
[Measures].[Tax Amount] } ON COLUMNS,
{ [Date].[Fiscal].[Fiscal Year].&[2002],
[Date].[Fiscal].[Fiscal Year].&[2003] } ON ROWS
FROM [Adventure Works]
WHERE ( [Sales Territory].[Southwest] )

In this example, the query defines the following result set information:

1. The SELECT clause sets the query axes as the Sales Amount and Tax Amount members of the Measures dimension, and the 2002 and 2003 members of the Date dimension.
2. The FROM clause indicates that the data source is the Adventure Works cube.
3. The WHERE clause defines the slicer axis as the Southwest member of the Sales Territory dimension.

For more learning on MDX kindly wait for next article………….. Multidimensional Expressions (MDX): PART 2