Archive

Archive for the ‘MDX Challenges’ Category

MDX # 40 – CROSS JOIN does not return Cartesian product of two hierarchies from the same dimension

April 15, 2014 Leave a comment

Cartesian product can be illustrated by a Cartesian square

Most SQL developers are familiar with the concept of CROSS JOIN and Cartesian product. Cartesian product is the result that is produced from a CROSS JOIN.

In this Wikipedia article, Cartesian product, a Cartesian product is illustrated by a Cartesian square as shown below.

Cartesian Square

 

 

 

 

 

 

 

In SQL Server, CROSS JOIN returns the Cartesian product of rows from tables in the join

Both of the following articles claimed that the SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.

The above definition of the SQL CROSS JOIN can be illustrated with the following SQL script using the AdventureWorksDW database.

use AdventureWorksDW
go

with color as (
select      distinct Color
from  dbo.DimProduct
),

sub as (
select      distinct EnglishProductSubcategoryName
from  dbo.DimProductSubcategory
whereEnglishProductSubcategoryName = ‘Bib-Shorts’
)

select      *
from  sub CROSS JOIN color
order by
EnglishProductSubcategoryName, Color
–10

To simplify the result set, I am using only one product subcateogry ‘Bib-Shorts’. The above SQL script generates the results shown in the screenshot below. With 1 product category and 10 product colors, the result set is the every possible combination of these two lists, which is 10 (1 * 10) rows.

Multi-Bib-shorts

 

 

 

 

 

 

 

 

 

 

Keep in mind that the above 10 combinations of the colors and the subcategory do not necessarily have any sales. In order to find witch combinations have sales, we will need to do an INNER JOIN on these 3 tables:

dbo.FactResellerSales or dbo.FactInternetSales
dbo.DimProductSubcategory
dbo.DimProduct

The following 2 SQL queries can be used. It turned out that there is no internet sales for multi color bib-shorts, but there are reseller sales for them.

– There is no Internet Sales for Multi color Bib-Shorts
select      *
from  dbo.FactInternetSales fact join dbo.DimProduct prod
on          fact.ProductKey = prod.ProductKey
join  dbo.DimProductSubcategory sub
on          prod.ProductSubcategoryKey = sub.ProductSubcategoryKey
wheresub.EnglishProductSubcategoryName = ‘Bib-Shorts’
and         prod.Color = ‘Multi’
–0

– There is 756 Reseller Sales for Multi color Bib-Shorts
select      *
from  dbo.FactResellerSales fact join dbo.DimProduct prod
on          fact.ProductKey = prod.ProductKey
join  dbo.DimProductSubcategory sub
on          prod.ProductSubcategoryKey = sub.ProductSubcategoryKey
wheresub.EnglishProductSubcategoryName = ‘Bib-Shorts’
and         prod.Color = ‘Multi’
–756

In MDX, CROSS JOIN does not return the Cartesian product members from hierarchies

Enter the world of MDX. CROSS JOIN is an important operation in MDX, with some twists and turns.

The MDX query below is the equivalent of the SQL CROSS JOIN in the previous section. However, the result set contains only one row.

select
{ } on 0,
{ CROSSJOIN(
[Product].[Subcategory].[Subcategory].[Bib-Shorts],
[Product].[Color].[Color].members
)
} on 1
from
[Adventure Works]

Multi-Bib-shorts Crossjoin

 

 

 

 

 

With the CROSSJOIN operation in MDX, we get only the combinations that actually have fact data. We now know that out of the 10 colors of Bib-Shorts only the Multi color Bib-Shorts has sales data (fact data).

Unlike the SQL CROSS JOIN, where we had to join one fact table and two dimension tables to find out which color of bib-shorts have sales, in MDX, the CROSSJOIN function automatically took into the consideration of the fact table. I have to say that the twists and turns are for the best.

Please refer to the section ‘Finding related members in the same dimension“ in Chapter 5 of our book, MDX with SSAS 2012, to gain more knowledge on this topic and how it is related to the concept of auto-exists and the EXISTS and EXISTING function.

Three different ways to do crossjoining

In MDX, you can cross join different hierarchies using three different ways. Chris Webb has a blog here, The rather-too-many ways to crossjoin in MDX, which is better than anything I can write about CROSS JOIN.

 

Categories: MDX Challenges

MDX # 39 – Find Default Measure using MDX Query

April 15, 2014 Leave a comment

In Chapter 1 of our book, MDX with SSAS 2012, we’ve devoted a section “Setting a default member of a hierarchy in the MDX script” to show three places where you can set up Default members for hierarchies.

For more information about how to define a default member for hierarchies and whether  you should have a default member or not for hierarchies, please also refer to the following Microsoft Developer Network article:

Define a Default Member

MDX script is an easy place to maintain the default member definition

As the MDX book pointed out that the MDX script is an easy place to maintain the default member definition.

Below is an example of how to define a default member for the Destination Currency hierarchy by issuing an UPDATE DIMENSION command.

/*– Set default member for the Destination Currency cube dimension —-*/

Alter Cube
CurrentCube
Update Dimension [Destination Currency].[Destination Currency],
Default_Member = [Destination Currency].[Destination Currency].[US Dollar];

MDX query is also an easy place to find what the default members are for each hierarchy

As it turned out that MDX query is also an easy place to find what the default members are for each hierarchy. For every report developer, knowing how to find the default measure is also crucial. You can obviously check the dimension and measure group structures in SQL Server Data Tools, or the cube’s MDX script. But the easiest way to find default members for hierarchies and the cube’s default measure is to write your own MDX query using the DefaultMember function. Please refer to the following MSDN article:

DefaultMember (MDX)

The general syntax is:

Hierarchy_Expression.DefaultMember

Three Sample MDX queries to find default members for hierarchies and cube default measure

The following are three examples to find default members for hierarchies and the cube’s default measure.

  •  Find Default Member of Destination Currency hierarchy

WITH MEMBER Measures.[Default Member] AS
[Destination Currency].[Destination Currency].DefaultMember.Name

SELECT
Measures.[Default Member] ON 0
FROM
[Adventure Works]
– Default member for Destination Currency hierarchy: US Dollar

  • Find Default Member of Geography hierarchy

WITH MEMBER Measures.[Default Member] AS
[Geography].[Geography].DefaultMember.Name

SELECT
Measures.[Default Member] ON 0
FROM
[Adventure Works]
– Default member for Geography hierarchy: All Geographies

  • Find Default Measure of the Adventure Works cube

WITH MEMBER Measures.[Default Measure] AS
[Measures].DefaultMember.Name

SELECT
Measures.[Default Measure] ON 0
FROM
[Adventure Works]
– Default cube measure: Reseller Sales Amount

Showing default measure and the value in one query

By placing the default measure directly on the X axis, you can get the default measure name and the value in one query.

SELECT
–Measures.[Default Measure] ON 0
[Measures].DefaultMember on 0
FROM
[Adventure Works]

Reseller Sales Amount
$80,450,596.98

 

 

MDX+SSAS #37–Ignore unrelated dimension or not

February 7, 2014 1 comment

In Adventure Works cube 2008 or 2012, in the Dimension Usage tab, you will see that many intercepts of measure groups and dimensions are blank. To a specific measure group, the dimensions that have blank intercepts are un-related to the measure group. As a matter of fact you will see blank intercepts more often than not.

As the term un-related dimension suggests, these measure groups have no relationship to these dimensions. SSAS, however, gives us a measure group property IgnoreUnrelatedDimensions (true or false) to control how we want these measures to show for those un-related dimension members.

IgnoreUnrelatedDimensions is a boolean property, so how hard it can be. It turned out that this property is not as straightforward as it seems, and it might not be as effective as you would think either.

When IgnoreUnrelatedDimensions = True, the measure repeats for all members and all levels on the un-related dimensions

IgnoreUnrelatedDimensions = True is a default setting for measure groups. The measure behavior is not the best (actually very confusing to users), but the behavior is consistent and easy to remember.

When IgnoreUnrelatedDimensions = True,

  • the measure will repeat with the same value for all member for all level for any unrelated dimension,
  • regardless if the dimension has default member or not, root [All] member or not
  • this behavior is the same as VALIDMEASURE() function which also repeats the measure with the same value for all member for all level for any unrelated dimension
  • Example 1:

  • Exchange Rates measure group is not related to the Promotion dimension
  • Exchange Rates measure group’s IgnoreUnrelatedDimensions property is set as default True
  • Promotion has no default member

image

    Example 2:
  • Exchange Rates measure group is not related to the Scenario dimension
  • Exchange Rates measure group’s IgnoreUnrelatedDimensions property is set as default True
  • Scenario has a default member of Actual

image

    Example 3:
  • Exchange Rates measure group is not related to the Sales Channel dimension
  • Exchange Rates measure group’s IgnoreUnrelatedDimensions property is set as default True
  • Sales Channel IsAggregatable is set to False; no root [All] member

image

When IgnoreUnrelatedDimensions = False, the measures behave differently depending on if  the un-related dimension ….

…has default member or not, has root level [All] member or not.

Example 4:

  • Sales Target measure group is related to the Date dimension but only at Calendar Quarter level
  • (I’ve disable the calculation in the MDX Script "Sales Quota Allocation" which allocates values down to the Month level)
  • The measure shows at the Quarter level and rolls up to all the ancestor levels.
  • VALIDMEASURE() function overwrites the above behavior and force it to repeat the the same value for all months and days 
  • image

Example 5:

  • Sales Target measure group is not related to the Promotion dimension
  • Promotion dimension has a [All Promotions] root level member
  • The measure shows for the [All Promotions] member only.
  • VALIDMEASURE() function overwrites the above behavior and force it to repeat the the same value for all members

image

Both Example 4 and 5 produce an easy to remember result, that is, the setting forces the measure to the top root member and the member that the measure is linked to and al the ancestor members from that point on.

However, Example 6 and 7 is a bit confusing, because the setting of IgnoreUnrelatedDimensions = False is totally ignored, just because the un-related dimension

  • has a default member, or
  • has no root [All] member

Example 6:

  • Sales Target measure group is not related to the Scenario dimension
  • Scenario dimension has a default member Actual
  • The setting IgnoreUnrelatedDimensions = False is totally ignored as if IgnoreUnrelatedDimensions = True.
  • VALIDMEASURE() function behaviors the same as always and repeats the value for all members.

image

Example 7:

  • Sales Target measure group is not related to the Sales Channel dimension
  • Sales Channel IsAggregatable is set to False; no root [All] member
  • The setting IgnoreUnrelatedDimensions = False is totally ignored as if IgnoreUnrelatedDimensions = True.
  • VALIDMEASURE() function behaviors the same as always and repeats the value for all members.

image

Reporting Driven Decision – relate or un-relate

I recently worked on a request to pull some measures on a dimension that is unrelated to the measure group.

Although the original cube design didn’t link the measure to the dimension, I can see that the design might have missed the reporting requirement. I had a few choices to meet the new reporting requirement:

  1. IgnoreUnrelatedDimensions = False
  2. Creating a MDX calculation to show the measure for the appropriate members on the un-related dimension.
  3. Linking the measure to the dimension in the relational data warehouse, not to the leaf level members but to a middle level, similar to how the example 4 where the Sales Target measure group is related to the Date dimension but only at Calendar Quarter level.
    After some experiment, I’ve abandoned both option 1 and 2, opted for option 3.
    This blog explains why I didn’t choose option 1. The IgnoreUnrelatedDimensions setting for measure groups is not a very reliable way to meet my reporting requirement with so many moving parts.

Option 2 seemed very attractive to me at first. The reason that I didn’t go for it will be topic for another blog.

When SSAS is not enough

After running the above queries with different IgnoreUnrelatedDimensions setting for different scenarios, I recalled a Chapter in our book MDX Cookbook “When MDX Is Not Enough”. Here I’d like to say that sometimes settings in SSAS can seem to provide simple solutions but are actually not enough.

MDX #36 – Many thanks to the reviewers

January 1, 2014 Leave a comment

In this blog, I’d like to give special thanks to the people below who have taken time to write reviews for my book MDX with SSAS 2012 Cookbook.

“It helped me a lot in my projects and helped me advanced my MDX skill in a very short time.”

hxy0135 NJ

“What I like about the cookbook style is the scenario and solution design. ”

Dan English

“The structure of the books is very similar and is again very easy to follow.”

Gerhard Brueckl

"This book is a must have. I have struggled trying to find a book with good illustrations and easy to follow samples UNTIL this book!"

Desert Fun

“I recommend this book to anyone regardless of MDX exposure, really great to have a reference like this book when you are trying to work through tough requirements."

richard mintz

"MDX with SSAS 2012 Cookbook" has a good chapter and topic organization which starts simply, but not too simply, and builds in complexity. "Elementary MDX" sets the stage for the expected level of knowledge with the other chapters building from that knowledge base. I particularly liked the chapters on "Working with Time", and "Business Analytics" because the regression and non-allocated expenses are tough problems for MDX newbies to solve."

PaulG

In the blog below, I’ve given my thanks to Vincent Rainardi for his quick reviewing.

MDX #32 – Vincent Rainardi’s blog

MDX #35 – Properties() function with TYPED flag

December 27, 2013 Leave a comment

 

Reader Query

My publisher recently forwarded me a question from a reader about the “Using the PROPERTIES() function to retrieve data from attribute relationships” section in Chapter 1 of the book “MDX with SSAS 2012 Cookbook”.

“The following code seems to always return true for [Is Numeric], no matter I use the 2nd parameter ‘typed’ or not.

Can you please explain why?”

clip_image001

clip_image002

In the reader’s query, the second parameter TYPED was commented out. Indeed, the [Is Numeric] is returning 1 (true).

I went back and read the section in the book, and I can see why the IsNumeric function used in the query is causing a bit of confusion.

IsNumeric Function Does not Return Data Type

IsNumeric function is one of the Visual Basic for Applications (VBA) library functions that extend the functionality of MDX statements. It returns a Boolean value indicating whether an expression can be evaluated as a numeric value. It does not tell if the property is a numeric data type or a string data type.

This explains why in the proceeding query, the calculated measure [Is Numeric] always returns 1. The value of Total Children can be evaluated as a numeric value.

In this sense, the IsNumeric function behaves consistently with the IsNumeric function in Transact-SQL.

You can try these two simple Transact-SQL queries in SSMS. The second SELECT will always return 1 (true) even when the parameter is a string data type.

clip_image003

Parameter TYPED is useful in sorting, filtering and calculations

If the property value can be evaluated as a number, and you intend to manipulate the property value arithmetically, then adding the TYPED parameter is the safest way to guarantee the accuracy of the sorting, filtering and calculation results.

Let’s use the FILTER function in the following three queries and compare the results.

  • 1. No TYPED parameter, and FILTER by [Total Children] = 3
  • 2. No TYPED parameter, and FILTER by [Total Children] = ‘3’
  • 3. With TYPED parameter, and FILTER by [Total Children] = 3

The first query should produce wrong results (no result set) because [Total Children] is a string value and [Total Children] = 3 cannot be found.

The second and the third queries should procedure correct results because the FILTER function used the correct data type in the search.

Query 1: No TYPED parameter, and FILTER by [Total Children] = 3

Incorrect results: no result set

clip_image005

Query 2: No TYPED parameter, and FILTER by [Total Children] = ‘3’

Correct results

clip_image007

Now try another query with TYPED parameter, and FILTER by [Total Children] = 3. You should see the same results as from the second query.

Properties() function with TYPED flag returns the data type of the property that was defined in the data source

So what exactly is the data type returned when the TYPED parameter is provided? It is the data type that is defined in the data source.

If you open the Data Source View in Adventure Works DW and expand the DimCustomer table, you will see that TotalChildren is defined as System.Byte.

clip_image008

clip_image009

In the relational data warehouse, this filed is defined as tinyint. Another data type you might be interested in is the data type of the Dimension Attribute Total Children. UnsignedTinyInt is an 8-bit unsigned integer with values that range from 0 to 255.

clip_image010

A bit more info

Another reason that you should include the TYPED parameter for the Properties function is for your PivotTable users. Very often, PivotTable users need to manipulate numeric values arithmetically. Data type conversion in PivotTable can create a lot of frustration for users.

Past blogs that explored the Properties function:

MDX #11 – How to get number of cars each customer owned using Properties() function?

MDX #12 – Do not forget the TYPED flag in the Properties() function

MDX #26 – SSN can only be referenced as a member property in MDX

Categories: MDX Challenges Tags: , ,

MDX+SSRS #34 – Query not returning results: there are good reasons

November 10, 2013 Leave a comment

When we are learning MDX, we unavoidably have to deal with the tools we use. Adding confusions about the behaviors of the different tools to our still fragile understanding of the MDX language itself can create huge frustration for learners.

Here is one of them. In developing SSRS reports, we frequently need to create report parameters using members from dimensions as choices for users to pick (they are referred to as pick lists).

Empty Measure (COLUMNS) + NON EMPTY keyword (ROWS) = no results

If you already know how to use the Query Designer in SSRS in graphical mode to create basic MDX queries, you might want to venture further to see if you can create a pick list by yourself. Let’s use Adventure Works cube as our example, and assume that you want to create a product pick list by retrieving all the members from the Product dimension. Very soon, you will realize that your dataset in SSRS will not return any product at all.

Here is what you might have done. In the Query Designer in SSRS, you just simply find the Product dimension and also the Product attribute hierarchy, and drag it to the report area. There is no product returned.

image

As a matter of fact of fact, there would be no data returned at all no matter which dimension you choose.

Now let’s click on the toggle button on the upper right corner and switch to the query editing mode.

image

We see a couple of things in this query.

  • There is an empty set on the COLUMNS axis: { } ON COLUMNS
  • There is NON EMPTY keyword on both the COLUMNS axis and the ROWS axis.

The combination of the above two creates an empty result set. Let’s remove the NON EMPTY keyword on the ROWS axis, and click on the red execution icon on the top. A list of products came up.

image

At this point, you night wonder why SSRS even bothered to use the NON EMPTY keyword by default if it is causing the query not returning any results.

There is a good reason why it does it. Cube space can be very large, and typically it is very sparse. When writing MDX, we spend a lot of effort trying to remove the empty cells from our results to improve both the query time and the data rendering time. SSRS is no exception. By adding NON EMPTY keyword, it assumes that we do not want to include any products that do not have any fact data. Unfortunately in this case, you didn’t get a chance to specify what the fact data (measure) is; SSRS added an empty set on the first axis by default. We get a combination of Empty Measure (COLUMNS) + NON EMPTY keyword (ROWS) which produces no results .

[ALLMEMBERS] = no result

I have seen another good reason why a query would not return the pick list.

you can try to run the following query. It would not return any results.

SELECT  {} on 0,
        [Product].[Product].[ALLMEMBERS] ON 1
FROM    [Adventure Works]

Now remove the brackets around ALLMEMBERS, you will see the result set.

SELECT  {} on 0,
        [Product].[Product].[ALLMEMBERS] ON 1
FROM    [Adventure Works]

In SSMS, by removing the brackets around ALLMEMBERS, ALLMEMBERS’s color turned from black to maroon. 

image

You might have already guessed the reason.

  • ALLMEMBERS is a MDX built-in function: [Product].[Product].ALLMEMBERS will evaluate the product hierarchy in the product dimension and returns a set that contains all members of the product hierarchy, which includes the top level member [All Products], all products, and all calculated members if any. See http://technet.microsoft.com/en-us/library/ms144768.aspx for details on the ALLMEMBERS function.
  • [ALLMEMBERS] is a member with the name of ALLMEMBERS. [Product].[Product].[ALLMEMBERS] represents a member with name ALLMEMBERS that does not exist in the product hierarchy.

More Information on the NonEmpty() function and the Non Empty keyword

In MDX, both the NonEmpty() function and the Non Empty keyword can be used to remove empty rows and columns. The difference between the NonEmpty() function and the Non Empty keyword is well explained in the recipe “Knowing the difference between NONEMPTY() function and the NON EMPTY keyword” in Chapter 1 of the book “MDX with SSAS 2012 Cookbook”.

MDX+SSRS #33 – Retrieve manager’s key and fully-qualified member name

November 8, 2013 Leave a comment

A co-worker recently worked on a SSRS report and wanted to use a specific manager’s team for testing. The Manager is a filter for the SSRS report and can take a fully-qualified member name as the default value.

Using the Employee dimension in Adventure Works cube as an example, a fully-qualified member name can take two forms, key-based and name-based:

  • [Employee].[Employees].&[77]: key-based
  • [Employee].[Employees].[Douglas B. Hite]: name-based

The Manager parameter in the SSRS report is designed to take the key-based fully-qualified member name as input, and the member name as the label.

It is common for us to know only the partial spelling of names. So what would be the quickest way to get a member’s key-based fully-qualified member name (I’ll also refer it as member unique name) when we only know the partial spelling of names?

Retrieve member properties not as data in the result set

Member unique name is one of the many member properties exposed by dimensions in SSAS. I went ahead and showed my co-worker how to write a query using the CurrentMember function to return member properties as data in a result set.

On the second thought, I should have showed him another easier way. That is to use the PROPERTIES keyword to retrieve member properties, including the member unique name. Properties returned this way will not have the property data in the result set.

In SSMS, you can run this query to return each employee’s unique name, i.e. the key-based fully-qualified member name.

SELECT    {} on 0,
        [Employee].[Employees].MEMBERS
        DIMENSION PROPERTIES
             MEMBER_CAPTION,
             UNIQUE_NAME,
             LEVEL_UNIQUE_NAME,
             MEMBER_KEY
             ON 1
FROM    [Adventure Works]

This is part of the results.

image

Now double-click on an employee name Amy E. Alberts. Here is the Member Properties window you will see.

clip_image002

You notice that the unique name appeared twice. As a matter of fact, by default SSMS query result will return the member properties that are shown in the above window, from Caption to ChildCount.

So by simply running the following query without the PROPERTIES keyword you will still be able to get the member’s unique name by double-clicking on the employee name.

SELECT    {} on 0,
        [Employee].[Employees].MEMBERS  ON 1
FROM    [Adventure Works]

The Copy button is also can be very convenient. It will copy the entire Property and Value list to the clipboard.

Query Editor in SSRS will not return UNIQUE_NAME property by default

Let’s just first run this simple query in the Query Editor (in the text mode rather than the graphical mode) in SSRS to see what it will return.

SELECT    {} on 0,
        [Employee].[Employees].MEMBERS  ON 1
FROM    [Adventure Works]

clip_image003

We see employee names returned in the result set. It actually only returns the member property MEMBER_CAPTION by default.

The Query Editor in SSRS behaves quite differently. It will not return member properties other than MEMBER_CAPTION by default. If you want to return more than just the MEMBER_CAPTION, you will need to use the PROPERTIES keyword.

SELECT    {} on 0,
        [Employee].[Employees].MEMBERS
        DIMENSION PROPERTIES
             MEMBER_CAPTION,
             UNIQUE_NAME,
             LEVEL_UNIQUE_NAME,
             MEMBER_KEY
             ON 1
FROM    [Adventure Works]

clip_image005

Interestingly, if you don’t include the property MEMBER_CAPTION, the first column is now empty.

clip_image007

Another different behavior from the query editor in SSMS is that it actually includes the member properties as data in the result set.

Here is a short list of the behaviors of the Query Editor in SSRS when using the PROPERTIES keyword.

  • It only returns MEMBER_CAPTION property by default (default means without using the PROPERTIES keyword).
  • It includes the member properties as data in the result set.
  • When using the PROPERTIES keyword, the MEMBER_CAPTION should be included in the property list (but does not need to be the first one in the list).

Conclusion

Using the PROPERTIES keyword is a simple way to get members’ unique names in the Query Editor in both SSMS and SSRS. We also see that the Query Editor in SSRS behaves quite differently from how the Query Editor behaves in SSMS.

We need to be careful when retrieving the entire members from a very large dimension without filtering. It might cause performance issue.

This might not work very well either when retrieving the entire members from a hierarchy that has multiple levels. It will be very difficult to look for what you want to see without proper sorting.

These member properties, MEMBER_CAPTION, UNIQUE_NAME, LEVEL_UNIQUE_NAME, MEMBER_KEY, are only part of the intrinsic member properties exposed by SSAS. To see all the intrinsic member properties, check out this link on SQL Server Books Online for more details:

Using Member Properties (MDX)

In the query below, I am including quite a long list of intrinsic member properties.

SELECT    {} on 0,
        [Employee].[Employees].MEMBERS
        DIMENSION PROPERTIES
             MEMBER_CAPTION,
             UNIQUE_NAME,
             LEVEL_UNIQUE_NAME,
             MEMBER_KEY,
             CATALOG_NAME,
             CHILDREN_CARDINALITY,
             CUSTOM_ROLLUP,
             CUSTOM_ROLLUP_PROPERTIES,
             DESCRIPTION,
             DIMENSION_UNIQUE_NAME,
             HIERARCHY_UNIQUE_NAME,
             IS_DATAMEMBER,
             IS_PLACEHOLDERMEMBER,
             KEY0,
             LCID,
             LEVEL_NUMBER,
             MEMBER_NAME,
             MEMBER_TYPE,
             MEMBER_UNIQUE_NAME,
             MEMBER_VALUE,
             PARENT_COUNT,
             PARENT_LEVEL,
             PARENT_UNIQUE_NAME,
             SKIPPED_LEVELS,
             UNARY_OPERATOR
             ON 1
FROM    [Adventure Works]

In all the above queries, I used an empty set on the COLUMNS axis. For more details on this technique, please see the recipe “Skipping axis“ in Chapter 1 of the book “MDX with SSAS 2012 Cookbook”).

Notes: all the examples and screenshots are from 2012 version of the SQL Server platform.

MDX #32 – Vincent Rainardi’s blog

October 24, 2013 Leave a comment

I came across Vincent Rainardi’s blog, and thought I’d share it with the readers.

Sherry Li and Tomislav’s 2012 MDX Cook Book

I’d also like to thank Vincent for writing up the review so quickly.

I browsed through Vincent’s blog, and found that he authored this book:

Building a Data Warehouse: with Examples on SQL Server

Many reviews praised that it is an excellent book on data warehousing that combines simple and practical explanations and "how to" for all the key aspects of building a data warehouse on SQL Server.

MDX+SSRS #31– Query Designer in SSRS only allows the Measures dimension in the first axis

September 28, 2013 Leave a comment

In Chapter 1 of the book MDX with SSAS 2012 Cookbook, in the first recipe “Putting data on x and y axes”, I have given a simple example to show how easy it is to use the CROSSJOIN function to "combine" more than one hierarchy into the COLUMNS and ROWS axes.

SQL Server Reporting Services is a report design and information delivery tool, and has been adopted by many companies for their Business Intelligence reporting needs. However, building reports in SSRS accessing OLAP cubes in Analysis Services is not without frustration.

The graphical MDX Query Designer in Reporting Services allows you to retrieve data from any BI semantic model through the technique of drag and drop, without you actually needing to understand and write MDX queries.

If you have some experience building reports in SSRS with Analysis Services data, you have already discovered that the graphical MDX Query Designer serves its purpose very well to  graphically generate well-formed and efficient MDX queries. Very quickly, however, you also will find that the you will need to cross a bridge. This bridge will lead you to the generic MDX query editor where you can edit the MDX query that is built by the graphical designer.

The following is a screenshot of the Query Designer and the toggle button for switching between the graphical designer and the editor.

image

Don’t expect the MDX query editor in SSRS works the same way as the MDX query editor in SSMS.

This MDX query simply puts two measures on the COLUMNS, and the CROSSJOIN (all possible combinations) of the sales territory country and product category on ROWS.

SELECT   
    { [Measures].[Internet Sales Amount],
      [Measures].[Internet Gross Profit]
    } ON 0,
    { [Sales Territory].[Sales Territory Country].[Sales Territory Country] *
      [Product].[Product Categories].[Category]
    } ON 1
FROM   
    [Adventure Works]

In SSMS, the previous query will produce the following result.

image

If we copy the same query to the query editor in SSRS, we do get the same number of rows back, and the measures match perfectly for every combination of the sales territory country and product category. However, we see some noticeable differences, comparing the previous screenshot with the following screenshot.

image

1. The measures are not formatted in the query editor in SSRS.

2. We get four columns in the query editor in SSRS. In addition to the two measures that we put on the X axis, the two hierarchies from our CROSSJOIN function on the Y axis have also appeared as two separate columns.

Now, let’s change the previous MDX query slightly, by switching the measures to the ROWS, and the CROSSJOIN of the sales territory country and product category to COLUMNS.

SELECT   
    { [Sales Territory].[Sales Territory Country].[Sales Territory Country] *
      [Product].[Product Categories].[Category]
    } ON 0,
    { [Measures].[Internet Sales Amount],
      [Measures].[Internet Gross Profit]
    } ON 1
FROM   
    [Adventure Works]

 

In SSMS, we would expect to see the following results.

image

Let’s copy the same query to the query editor in SSRS. This time we would get an error.

image

The message is actually very clear. I’d translate the error message into the following two rules. The query editor (and the graphical Query Designer) in SSRS:

1. does not allow CROSSJOIN in the COLUMNS (or 0-axis)

2. only allows the Measures dimension in the COLUMNS (or 0-axis).

What we have put on the COLUMNS clearly violated both of the rules.

     { [Sales Territory].[Sales Territory Country].[Sales Territory Country] *
      [Product].[Product Categories].[Category]
    } ON 0

 

In my experience, knowing what to expect from the graphical MDX Query Designer and the MDX query editor in SSRS will put you half way through the learning curve.

Follow

Get every new post delivered to your Inbox.

Join 159 other followers