How to aggregate across a specific time range?

In this posting we discuss how to aggregate data across a specific time range. In the query below, we define a calculated member ([Time].[MyTime]) that uses the Aggregate function to aggregate data across the specific time range that is specified by the set "[Time].[1997].[Q1].[1]:[Time].[1997].[Q2].[6]". This set uses the ":" operator to specify a contiguous range of values in our time dimension. We then use this newly created calculated member in our where close to force our values to be aggregated across this time period.

Here's the query...

with
member [Time].[MyTime] as
'
Aggregate([Time].[1997].[Q1].[1]:[Time].[1997].[Q2].[6])
'
select
{
[Store].[USA].Children
} on columns,
{
[Measures].[Unit Sales]
} on rows
from Sales
where
(
[Time].[MyTime]
)

Can't Teach An Old Dog New Tricks

While you can't teach an old dog new tricks, it seems this old adage is not necessarily applicable to the humanzee. I learned a few things from the blow posting.

I consider it recommended viewing for all humanzees. Canines on the other hand...dont' waste your time.

TechNet Support WebCast: Common MDX mistakes and solutions in Microsoft SQL Server 2000 Analysis Services

- Monty

How to default you time dimension to show the most recent time period?

A question I often hear asked is "How do I get my time dimension to default to the current time period"? The first problem with the question is what is meant by "current time period". In my experience, 8 of 10 times when folks say "current time period" that means the latest member of time dimension at it's leaf level that happens to have data in the fact table.

For instance, if you use Foodmart 2000 it has time periods for all the months in 1997 and 1998, but the data is only associated with 1997 for the fact table in the Sales cube. So if by "period" one happens to mean "month", then the most recent period with data in the fact table is [Time].[1997].[Q4].[12].

Being that MDX is unnecessarily cryptic...such a simple request becomes masked in the minutia of the language itself. On most of my cubes...I like to setup a default time member on my time dimension. I do so with a statement that looks like this...

Tail(NonEmptyCrossJoin([Time].[Month].Members)).Item(0).Item(0)

You can adjust the "[Time].[Month]" part of the expression to reflect whatever level of your time dimension you want to be used as the default. The NonEmptyCrossJoin part of the expression...which actually is cross joining to nothing...is the part of the expression that limits the list of members to only those that have data in the fact table. The Tail part of the expression is retrieving the last item in the list. Then in classic MDX cryptology we need to use the ".Item(0).Item(0)" to morph this last item in the set from a set into the member it represents.

How do you find the product of a series of numbers using MDX?

This posting shows how to use a recursive calculated member to find the product of a series of numbers. In the particular case illustrated, I'm using the Foodmart 2000 database (as I always do so that the concept is transportable to any reader).

Unfortunately, I didn't see any good reason to do this in Foodmart anywhere (nor did I want to take the time to construct something :) ) so this example is designed to show how to perform the technique...not necessarily highlight it in a real world example where it would be sensible.

I've heard of others that have had this need with a series of rates of returns of stock on a daily basis. To determine the MTD rate of returns, apparently the series of numbers need to be multiplied.

Here's what the MDX might look like...

With member [Measures].[SalesAvgMultiplied] as
'
iif(
IsLeaf([Time].CurrentMember)
,iif(
[Time].CurrentMember IS [Time].CurrentMember.Parent.FirstChild
,[Measures].[Sales Average]
,([Measures].[SalesAvgMultiplied], [Time].PrevMember) * [Measures].[Sales Average]
)
,[Time].CurrentMember.LastChild
)
'
select
{
[Measures].[Sales Average],[Measures].[SalesAvgMultiplied]
} on columns,
{
[Time].Members
} on rows
from Sales

The calculated member first uses the standard non-additive rollup technique to move the last child up to the time aggregate levels (why did I do this?...just felt like it :) ). Next it uses a standard recursion technique by implementing a stop point for the recursion in an IIF function (the [Time].CurrentMember IS [Time].CurrentMember.Parent.FirstChild). This stops the recursion when your leaf level member reaches it's first sibling. If you aren't at the first sibling, however, it then multiplies itself from the previous period against the value in the current period you are multiplying through time.

Recursive Calculated Member Against Member Property

This example shows how to utilize a recursive calculated member to sum a numeric member property up to all it's parent levels. In this specific case, the Foodmart Store dimension has a "Store Sqft" member property which is numeric. That member property only lives at the leaf level (the Store Name level) of the dimension. In this example we create a calculated member that interrogates whether or not we are at the leaf level of the dimension...if we are, it will output the value of the "Store Sqft" member property...if we are not at the leaf level, then the calculated member will sum the value of itself (i.e. the recursive part) for all it's children.

The MDX example is as follows:

with
member [Measures].[StoreSqFt] as
'
iif(
IsLeaf([Store].CurrentMember)
,val([Store].CurrentMember.Properties("Store Sqft"))
,Sum([Store].CurrentMember.Children,[Measures].[StoreSqFt])
)
'
member [Measures].[StoreSalesPerSqFt] as
'
iif(
[Measures].[StoreSqFt] = 0
, NULL
, [Measures].[Store Sales] / [Measures].[StoreSqFt]
)
'
select
{
[Measures].[Store Sales]
,[Measures].[StoreSqFt]
,[Measures].[StoreSalesPerSqFt]
} on columns,
{
Order(
Descendants([Store].[All Stores].[USA])
,[Measures].[StoreSalesPerSqFt]
,BDESC
)
} on rows
from Sales

This query outputs the total Store Sales, the total square footage available in stores, and the store sales per sqft for each member of the store dimension regardless of level. It sorts the output by store sales per sqft breaking the hierarchy so we can quickly see which stores, cities, and states are using their space most efficiently to generate sales.

Conditionally taking an action based upon member existence

Sometimes members might come and go for numerous reasons. Maybe one user has access to a member and the next does not...or maybe the relational data behind your member comes and goes (like time periods) for some reason. In any event, it's sometimes useful in MDX to be able to check whether a member is a valid member prior to performing some operation that uses that member.

In order to do this...you can use the IsError() function combined with the StrToMember() function combined with concatenation of a null string within the IIF() function. Clear as mud right?

At first glance...one might think that a query like this would do the job...

with member [Measures].[Test] as
'
iif(
IsError(StrToMember("[Gender].[All Gender].[Z]"))
,"Not Found"
,"Found"
)
'select
{
[Measures].[Test]
} on columns,
{
[Education Level].Members
} on rows
from Sales

Problem is the MDX parser is smarter than you average humanzee and in its attempt at genius will error out the above statement prior to execution delcaring that it cannot find dimension member [Gender].[All Gender].[Z]. Well since there is no gender Z in our cube that is of course correct...however...the purpose of the calculated member above is to account for this at execution time...but the ingenious MDX parser got in our way. The key to getting around it's genius is to use some good old fashioned humanzee stupidity. You simply concatenate a null string onto the string used in the StrToMember function. This apparently tells the MDX parser that it's dealing with some kind of dynamic string and thus should just go ahead and do what it was asked to instead of trying to be smarter than us. So the new query would look like this...

with member [Measures].[Test] as
'
iif(
IsError(StrToMember("[Gender].[All Gender].[Z]" + ""))
,"Not Found"
,"Found"
)
'select
{
[Measures].[Test]
} on columns,
{
[Education Level].Members
} on rows
from Sales

This query instead of erroring out at parse time, executes as intended and returns the result of "Not Found" for the member [Gender].[All Gender].[Z]. If you change [Gender].[All Gender].[Z] to [Gender].[All Gender].[M] the query will return "Found".