Category Archives: MDX

#MDXMonday: Finding the Current Day

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.

View previous posts in the #MDXMonday series

The first part is where the most work takes place. I created a named set to identify

Continue reading #MDXMonday: Finding the Current Day

Advertisements

#MDXMonday: Previous Year Month to Date Calculation

I recently had a client give us a call asking for helping creating a calculation in her cube to show the month to date value for the previous year. Here’s the basics of the calculation I created.

View last week’s #MDXMonday –> Calculating Daily Average Sales

This calculation involve two parts. First we must create a calculation that figures the month to date value of the measure in question. To do this I used the MTD function along with Continue reading #MDXMonday: Previous Year Month to Date Calculation

#MDXMonday: Calculate Daily Average Sales

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

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!

Navigating Hierarchies With MDX Webinar 9/23/14 11 am EST

Heads up, everyone! I’ve got a free training event coming up on Tuesday, September 23 at 11 a.m. EST! Next Tuesday I’ll be discussing Navigating Hierarchies With MDX.

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!

But the best part is it’s all free training! Just get signed up and register for the event, completely free of charge. I look forward to seeing you there!

The MDX Rank Function

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.

We can create a numeric ranking of the Customers by leveraging the MDX Rank function. The Rank function Continue reading The MDX Rank Function

Creating Calculated Members with MDX

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        1
Mar      $400        2
Feb      $300         3
Jan      $200         4
Dec      $100         5
Other  $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.

with dynamic set [Top 5 Customers] as 

Topcount(
[Customer].[Customer].[Customer].members,
5,
[Measures].[Internet Sales Amount]
)

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]).

member [Customer].[Customer].[Others] as 

Aggregate(
Except(
[Customer].[Customer].[Customer].members,
[Top 5 Customers]
)
)

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.

dynamic set [Top 5 & Others] as 
{
[Top 5 Customers],
[Customer].[Customer].[Others]
}
 
Now all that’s left to do is to use our set in a query. Here’s the entire query so you can see the total syntax.
 
with dynamic set [Top 5 Customers] as 

Topcount(
[Customer].[Customer].[Customer].members,
5,
[Measures].[Internet Sales Amount]
)



member [Customer].[Customer].[Others] as

Aggregate(
Except(
[Customer].[Customer].[Customer].members,
[Top 5 Customers]
)
)

dynamic set [Top 5 & Others] as

{
[Top 5 Customers],
[Customer].[Customer].[Others]
}

Select [Measures].[Internet Sales Amount] on 0,


[Top 5 & Others] on 1


From [Adventure Works]

And here’s the results in the SSMS query results windows.

image

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.

Thanks again, Brad, for the great question!

Check out my next blog post to see how to return the numeric rank of the customers!