Archive

Posts Tagged ‘sub-query’

MDX #28– Time functions will not work with subquery

March 22, 2013 Leave a comment

I had a post last month, MDX #25 – Slicer or Sub-Cube?, verifying that the query context did not change with the subquery.

This can pose problems if you want to use the Time-series functions in MDX. Two functions come into mind, YTD() and PeriodsToDate().

Both functions need to know which member on the Date dimension we are operating on.

PeriodsToDate and YTD function will not work with a subquery

Let’s try this query, which tests the PeriodsToDate function with a subquery. It will not work with the created member Reseller Sales YTD. Created member [x] is here to show what the current member on the Datedimension is.

–PeriodsToDate will not work with the subquery
WITH
MEMBER [Measures].[Reseller Sales YTD] AS
   Aggregate(
        PeriodsToDate(
                [Date].[Calendar].[Calendar Year],
                [Date].[Calendar].CurrentMember
                ),
              [Measures].[Reseller Sales Amount]
            )
MEMBER [Measures].[x] AS
    membertostr([Date].[Calendar].CurrentMember)
SELECT
   { [Measures].[Reseller Sales Amount],
     [Measures].[Reseller Sales YTD],
     [Measures].[x] } ON 0
FROM
   ( select   
        { [Date].[Calendar].[Month].[March 2008]:
          [Date].[Calendar].[Month].[April 2008]
        } on 0
     from    [Adventure Works]
    )

image

PeriodsToDate and YTD function will work with modification

The fault is not the subquery, rather it’s because we did not provide the query context.

We can modify the above query to provide the context by adding a member expression [Date].[Calendar].[Month].MEMBERS to the ROWS axis.

— PeriodsToDate works with [Date].[Calendar].[Month].MEMBERS on the ROWS axis
WITH
MEMBER [Measures].[Reseller Sales YTD] AS
   Aggregate(
        PeriodsToDate(
                [Date].[Calendar].[Calendar Year],
                [Date].[Calendar].CurrentMember
                ),
              [Measures].[Reseller Sales Amount]
            )
MEMBER [Measures].[x] AS
    membertostr([Date].[Calendar].CurrentMember)
SELECT
   { [Measures].[Reseller Sales Amount],
     [Measures].[Reseller Sales YTD],
     [Measures].[x] } ON 0,
   { [Date].[Calendar].[Month].MEMBERS } ON 1
FROM
   ( select   
        { [Date].[Month Name].[Month Name].[March 2008]:
          [Date].[Month Name].[Month Name].[April 2008]
        } on 0
     from    [Adventure Works]
    )

Our result is showing both months on the ROWS axis, and with the correct YTD reseller sales for each month.

image

MDX #25 – Slicer or Sub-Cube?

February 8, 2013 1 comment

Slicer, Axes and Calculations Can All Filter Data in MDX

Every developer with SQL background knows how the WHERE clause works. You put some condition in the WHERE clause, such as TheDate = Yesterday (pseudo code), and it will only return data for yesterday.

Not so straightforward in MDX. We should expect more complex behaviors in MDX because of the multi-dimensional nature of the cubes.

But how different it can be.

There are many topics to explore, including why we prefer to call the WHERE clause slicer in MDX, how tuple is constructed, when default member is used, how slicer (WHERE clause if you insist) can be used to limit data, and how axes can be used, how the MDX engine decides when to use slicer, axes, and calculation formula to limit the data, etc..

Sub-Select Can Filter Data Too

In this blog, I’ll explore just one aspect of how we can use both the slicer and a sub-cube to limit the data, and where they are the same, and where they might give you different results.

Both Slicer and Sub-Select Produces the Same Result

Run these two queries (separating by GO), you get the same Internet Sales Amount from both queries, for July 1, 2008.
So our conclusion so far is that slicer and sub-select should give same results.

image

image

If you make such statement to some MDX experts, they will tell you that results from using a member in the slicer can be different from using the same member in a sub-select.

So what can be different?

While Sub-Select does not change the query context, the slicer does

Run this query pair. We are using the currentmember function to show what date we are currently at. In MDX’s term, we are checking the current member of the Date hierarchy in the query context (quite mouthful).

image

image

It turned out that the “current date” from the two queries is not the same. The first one with slicer says we are currently at just one day, July 1, 2008. The second one with the sub-select says we are actually currently at All Periods (all days in the entire Date dimension; the root member of the Date dimension).

Now we know that the query context (where we currently at) is different, depending on where we are putting our member, in the slicer or in the sub-select. The sub-select does nothing to change the query context, while the slicer changes the context according to the tuple (in the above example we only have one member in the tuple) we put in the slicer.

You might already figure out that we need to be careful now about the context.

When Sub-Select and Slicer Might Give Different Results

Here is an example where your filtered result might not be exactly what you want if you are using the sub-select.

image

image

image

When using the currentmember as a filter, You should expect the sub-select will give you the Internet Sales Amount for the entire date range in your Date dimension, not just from one day, July 1, 2008.

Confusing? Yes, it is. But no complaining?
 
For my other MDX blogs, visit https://bisherryli.wordpress.com/category/mdx-challenges/

Dimension Role-Playing in Employee Comp Reporting

March 17, 2011 Leave a comment

The employee hierarchy table is just one physical table, with a date range to keep employee’s employment history. This same employee hierarchy table can be used sometimes to show different business functional teams, for example, LAS VS. Underwriters on the same fact table. 

I can simply create two SQL views to handle this, or just use two sub-queries if I don’t want to create too many SQL views.

image

image

This is called dimension role-playing. Role-playing in a data warehouse occurs when a single dimension simultaneously appears several times in the same fact table. The underlying dimension exists as a single physical table, but each of the roles should be presented in a separated labeled view.  

Another common example in the employee productivity reporting, is the date dimension. We will just need one single date dimension table, but very often, we need to have a second date, such as application date, besides the primary decision date, on the same fact table. Again we only need to create two different SQL views to handle this, with only one underlying physical date dimension table.

%d bloggers like this: