Tag Archives: Descendants

#MDXMonday, MDX, SSAS

#MDXMonday: Calculate Daily Average Sales

2 Comments

Creating an SSAS MDX calculations for daily average sales is a pretty common requirement I’ve come across as a consultant for Pragmatic Works and as the instructor for Pragmatic Works Introduction to MDX class. Because of this and the fact that many people often come to me with their MDX challenges, I’ve decided to start a series of blog posts covering these challenges and experiences both as a reference for myself and other and also as a way to give back to the community. These examples will be done using the Adventure Works cube unless otherwise stated. Some of these will be simple and some will be more complicated but I hope you will enjoy learning a little bit about MDX as much I do so lets begin!

There are two basic parts to creating this calculation. The first step is Continue reading #MDXMonday: Calculate Daily Average Sales →

Free Training, MDX, Pragmatic Works, SSAS

Navigating Hierarchies with MDX webinar recording is now available!

image Thanks to everyone that attended my webinar on Navigating Hierarchies with MDX! We looked at a bunch of different ways we can navigate up, down, and side to side in our hierarchies in order to do some really neat things with calculations. If you would like to view the recording, you can do that here completely for free! Also, if you’d like to view my PowerPoint slide deck and scripts I used for the webinar, you can download those from here. Just download the Navigating Hierarchies with MDX .zip file. 🙂

Now on to the questions!

Q: Is there anything like storeproc / pre stroed mdx query in SSAS which can be called in .net application.
A: There is a concept of SSAS stored procedures, which you can read more about here: http://msdn.microsoft.com/en-us/library/ms176113.aspx and here: http://asstoredprocedures.codeplex.com/

Q: How would ParallelPeriod handle a leap year?
A: ParallelPeriod returns the member at the same position in the specified period. So if the 29th day of February does not exist in the previous year, then no value will be returned:
image

Q: Can you use PeriodsToDate() on a ‘custom’ period like an Academic Term?
A: PeriodsToDate can be used on any user defined hierarchy.

Q: Setting date property for MTD, QTD seems straightforward.  What about WTD (week-to-date)?  It seems it might take some careful work each year.
A: If you have a Week attribute in your Date dimension, that should be set to Week, as well. That’s all that is required. 🙂
image

Q: What is the name of the zoom tool and highlighter used, just curious.
A: Zoomit. It’s free, too! I get asked that question every time I present.

Thanks for all the great questions, everyone! If you have any further questions, please feel free to post it here or to send me a tweet!

MDX, SSAS

Using the Descendants Function

1 Comment

The MDX function, Descendants, is a very versatile and commonly used function that returns the members at, below, or even above a specified level.

The first argument of the descendants function is a member or set expression. The second argument can either be a specific level expression or a number indicating the number of levels to travel. The following queries return the same results:

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    2) 
    on 1
From [Adventure Works]

GO

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    [Date].[Calendar].[Calendar Quarter]
    ) 
    on 1
From [Adventure Works]

 

Results:
image

The Descendants also accepts a third optional argument: a string expression to distinguish between possible descendants to return in the results. This query uses the SELF_AND_AFTER flag to returns members at the specified level as well as members subordinate to the specified level.

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    [Date].[Calendar].[Calendar Quarter],
    SELF_AND_AFTER
    ) 
    on 1
From [Adventure Works]

Results:
image 

And this query uses the BEFORE_AND_AFTER flag to return all members except for the members of the specified level.

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    [Date].[Calendar].[Calendar Quarter],
    BEFORE_AND_AFTER
    ) 
    on 1
From [Adventure Works]

Results:
image

Notice that the specified level is excluded.

Also, if I use the LEAVES flag, I can return all the leaf members between the specified member and the specified level. Alternatively, if you don’t specify a level, the members at the lowest level (the leaf members) will be returned.

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    ,
    LEAVES
    ) 
    on 1
From [Adventure Works]

Results:
image

Check out MSDN for more information on the Descendants function.

MDX, Pragmatic Works, SSAS

Ten MDX Calculations For Your Cube

8 Comments

I get lots of questions about how to implement various types of calculations in a cube so I figured I’d cover some of the more commonly used calculations I run into on a regular basis. All of these example calculations I’ll be providing were written against the Adventure Works 2008 R2 example cube but these should still work against later versions of the AW cube.

10. YTD, QTD, and MTD Calculations

These kinds of calculations are pretty common and I see these in a lot of cubes. They’re pretty easy to wire up since there are only a couple simple MDX functions necessary to make this work.

CREATE
MEMBER CurrentCube.[Measures].[YTD Internet Sales Amount] AS
Aggregate
(
PeriodsToDate
(
[Date].[Calendar].[Calendar Year]
,[Date].[Calendar].CurrentMember
)
,[Measures].[Internet Sales Amount]
)
,FORMAT_STRING = “Currency”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

Let’s start with the PeriodsToDate function. The PeriodsToDate function is going to return all the sibling members at the specified level up to the current member. The outer function, the Aggregate function, calculates the value based on the aggregation type specified in the cube for the measure. In our case, the Internet Sales Amount aggregation type is Sum, so the Aggregate function could be exchanged for the Sum function.

If I wanted to calculate the Month to Date for Internet Sales, I would simply exchange the reference for the Calendar Year level of the Calendar hierarchy with the Month level, as seen here:

CREATE
MEMBER CurrentCube.[Measures].[MTD Internet Sales Amount] AS
Aggregate
(
PeriodsToDate
(
[Date].[Calendar].[Month]
,[Date].[Calendar].CurrentMember
)
,[Measures].[Internet Sales Amount]
)
,FORMAT_STRING =
Currency
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

9. Period over Period Growth

This is also a pretty common calculation I find and implement for my clients. It’s also pretty straight forward. In this example, we’re comparing this year’s Internet Sales Amount to the previous year’s Internet Sales Amount.

Create Member CurrentCube.[Measures].[Yearly Growth Internet Sales Amount]

As
([Date].[Calendar Year].CurrentMember,[Measures].[Internet Sales Amount])-
([Date].[Calendar Year].PrevMember,[Measures].[Internet Sales Amount]),

FORMAT_STRING = “Currency”,
VISIBLE = 1 ,  DISPLAY_FOLDER = ’10 MDX Calculations’ ,  ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’  ;

The function here to pay attention to is the PrevMember function. The PrevMember function returns the previous member at a given level based on the supplied member. For example, if the supplied member was 2011, the previous member would be 2010.

Create Member CurrentCube.[Measures].[Monthly Growth Internet Sales Amount]

As
([Date].[Month of Year].CurrentMember,[Measures].[Internet Sales Amount])-
([Date].[Month of Year].PrevMember,[Measures].[Internet Sales Amount]),

FORMAT_STRING = “Currency”,
VISIBLE = 1 , DISPLAY_FOLDER = ’10 MDX Calculations’ , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;

Similar to our last calculation, if we wish to calculate the growth from month to month, we should reference the Month attribute of our Date dimension.

8. Profit Margin Calculation

Another popular calculation for obvious reasons is the calculation for profit margin. Basically all you need to understand for this calculation is the logic of a Case statement. We use the Case statement to check for a zero value in the denominator.

CREATE
MEMBER CurrentCube.[Measures].[Internet Profit Margin] AS
CASE
WHEN
IsEmpty([Measures].[Internet Sales Amount])
THEN NULL
ELSE
([Measures].[Internet Sales Amount]
–
[Measures].[Internet Total Product Cost])
/
[Measures].[Internet Sales Amount]
END
,FORMAT_STRING = “Percent”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

7. Percent of Total

More often then not, my clients want to be able to calculate the percent of a total amount for a hierarchy. In this example, I’m once again leveraging the Case statement.

CREATE
MEMBER CurrentCube.[Measures].[Percent of Internet Product Sales] AS
CASE
WHEN
IsEmpty([Measures].[Internet Sales Amount])
THEN NULL
ELSE
(
[Product].[Product Categories]
,[Measures].[Internet Sales Amount]
)
/
(
[Product].[Product Categories].[All]
,[Measures].[Internet Sales Amount]
)
END
,FORMAT_STRING = “Percent”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

To adapt this calculation to your cube, just replace [Product].[Product Categories] with your dimension and hierarchy.

6. Count Leaf Members of a Hierarchy

I’ve seen this kind of calculation used a lot in combinations with other measures and/or calculation. This calculation counts the Leaves, which are products, of the Product Categories hierarchy.

CREATE
MEMBER CurrentCube.[Measures].[Product Count] AS
Count
(
Descendants
(
[Product].[Product Categories].CurrentMember,
,LEAVES
)
)
,FORMAT_STRING = “#,##0.00;-#,##0.00”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

There’s not much to this calculation. First we use the Descendants function with the Leaves function as the third argument to get all the Leaf members of the Product Categories hierarchy. Then we use the Count function to count those members.

While these calculations are not extremely complex, MDX can be a bit overwhelming. A very helpful tool that I’ve found useful, even as someone experienced with MDX, is the MDX Calculation Builder included with BI xPress. Adding calculations, like those seen above, is very easy and only take a few click with the wizard.

To open the MDX Calculation Builder, click the icon seen on the Calculations tab of your Cube Designer in BIDS.

10-30-2011 10-22-07 PM

After you click the Calculation Builder icon, the Calculation Builder wizard will open. The first thing you’ll need to do is select a calculation. At these step, we can also choose to create a named set, which the wizard will help us do.

10-30-2011 10-29-37 PM

After selecting the calculation, you’ll be taken through a couple more steps, such as selecting the Measure you wish to perform the calculation with or specifying which attribute to use as the Year attribute for the calculation.

You’ll also have the opportunity to apply conditional formatting to your measure values, which is a nice touch the end users like to see.

10-30-2011 10-46-34 PM

Finally, we’ll give our measure a name, select the measure group we’d like the measure to be a part of, select the formatting, and preview the calculation that was written by the tool.

Then we’ll click Finish. The calculation has been added to our calculation script within our cube.

10-30-2011 10-50-58 PM

BI xPress has made adding calculations to your cube simply and fast and I take advantage of the tool anytime I’m implementing calculations into my client’s cube. Head to PragmaticWorks.com to check out BI xPress and to check out some of the great training videos hosted there covering the in’s and out’s of MDX.

Stay tuned for my next blog post where we will cover the remaining 5 MDX calculations to add to your cube!

MDX, SSAS, SSRS

Showing an SSAS Hierarchy as Delimited String

I recently encountered a requirement for a client to display a list of all the individual stores of a ragged hierarchy (the leaf members) on an SSRS report with a bunch of different measures. But they also wanted to be able to quickly see the the ancestor members of each level above each store quickly by using a tool tip to display the different ancestors.

Turns out there are a couple nifty, little MDX functions that can allow us to do this very easily. The Ancestors and Generate functions make this possible. Here an example I put together with the Adventure Works 2008 R2 cube so you can play along.

WITH MEMBER [Measures].[Management Structure] AS
            GENERATE(
            EXCEPT(({Ancestors([Employee].[Employees].CURRENTMEMBER,6),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,5),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,4),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,3),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,2),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,1)}),[Employee].[Employees].[All]),
            [Employee].[Employees].CURRENTMEMBER.Name, "  >  ")

Select {[Measures].[Management Structure],
    [Measures].[Reseller Sales Amount],
    [Measures].[Reseller Order Quantity]} on 0,

NON EMPTY(DESCENDANTS([Employee].[Employees],10,LEAVES)) ON 1

From [Adventure Works]

Where [Employee].[Title].&[Sales Representative]

Pay attention to the calculate member [Measures].[Management Structure]. The way the Generate function works is that it takes one set and applies that set to each Member in another set. In this case, our first set is the ancestor from each level in our hierarchy of the current member, excluding the All member.

So this our first set:

EXCEPT(({Ancestors([Employee].[Employees].CURRENTMEMBER,6),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,5),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,4),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,3),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,2),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,1)}),[Employee].[Employees].[All])

Our second set is the Name property of the CurrentMember of the Employee hierarchy being displayed:
 
[Employee].[Employees].CURRENTMEMBER.Name
 
The Generate function actually also excepts a third argument: A delimiter. We can specify a delimiter. In my example, I use “  >  “ to make the string a bit easier to read. If we execute the query we can see the results of our calculated member.

10-10-2011 9-47-39 PM

Pretty cool, huh? So now that we have each employees management hierarchy in sweet, little string, we can easily display this in a report as a tool tip.

I added the tool tip to the cell in my report’s tablix that contains the employee name. Here’s the expression I used:

=REPLACE(Fields!Management_Structure.Value,">",VBCRLF)

This expression replaces the “>” with a carriage return, line feed. Here’s what our tool tip looks like:

10-10-2011 10-02-24 PM

Using this method, we can easily display the managers for each employee in a tooltip without having to waste any real estate with additional columns.

I hope somebody finds this useful. I’m out!