Category Archives: MDX

This week I’m teaching the Pragmatic Works Intro to MDX virtual training class. A student in the class asked how they could find the current day sales amount using MDX (no SSAS functionality) and I thought this was a worthy blog topic. This solution assumes that the cube is processed at least once a day as the query you’re about to see returns the last day in the cube that we data for.

Like this:

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!

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. 🙂

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:

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. 🙂

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!

One of the great strengths of SQL Server Analysis Services is the ability to create hierarchies by defining relationships between attribute fields. In this webinar, we’ll take a look at how we can fully leverage our SSAS hierarchies in our MDX queries and calculated measures using navigational functions such as PARENT, DESCENDANTS, PARALLELPERIOD, and many more! So if you’ve ever wanted to learn more about some of the cool, navigational functions built into MDX or have had questions about creating really powerful MDX calculations, this webinar is for you!

Share this:

Like this:

If you read my blog regularly, you may have seen this post from last week where a reader asked how to create set to get the top 5 members while lumping every member outside of the top 5 into an “Others” member. As a follow up question, another reader asked how we can rank the members being displayed.

Share this:

Like this:

This week I got an email from a reader named Brad asking a specific question regarding MDX. Here is his question:

I need an MDX query to return me the top 5 values and then default all others to an “other” group and ranks based on the resulting measure value. For instance, if we are talking about a measure by month for a full year, I need the output to look like this:

Dim Measure Rank

Nov $500 1Mar $400 2Feb $300 3Jan $200 4Dec $100 5Other $350 6

Any input will be most appreciated!

Thanks,

Brad

I thought this was a good question which is why I thought I’d take the time to blog this out. In this example I’ll be using the Adventure Works cube and the Adventure Works Customer dimension.

The first step is to determine what are our Top 5 Customers. To do this, we can use a simple TopCount function to build a set of the Top 5 Customers.

Once we have our Top 5 Customers, then we can easily determine everyone else. To create our custom “Others” group, we need to create a Calculated Member (Custom Member). To create the Calculated Member, you need to specify the dimension, attribute hierarchy, and member name for your custom member (ie [Customer].[Customer].[Others]).

To create our Others custom member, we need to use the Except function to basically specify that we want our custom member to include all our customers except the Top 5 Customers set we previously created. Also, don’t forget to wrap the set in the Aggregate function.

Lastly, we create our set that will actually be used in our query. This set will include our first set containing our Top 5 Customers as well as our Others custom member. We can use the Top 5 Customers set with our custom member because all members in the set are from the same Customers hierarchy.

I hope that was pretty straight forward and not too confusing. If you thought this was helpful leave me a comment and let me know! And if you have any questions or comments, feel free to leave a comment.