MDX help

Hi All,

I have a calculated measure in my OLAP cube which is a distinct count of one of the keys in the Fact table. This is a semi-additive measure so I need Distinct count of the last child of this measure. How can I do this? Is there a built-in function to handle this.

I am using AS 2005.

Any help would be greatly appreciated.

Rohit

Hi Rohit. More detail here is helpful. You ask for LastChild of a measure. Unfortunately, measures don't have a hierarchy so they don't have children. This means you're probably asking for one of the following:

(1) The LastChild within a hierarchy (such as Product), and the corresponding value of your semi-additive measure (what's the name of the measure?)

OR

(2) The 'closing' value of your semi-additive measure across time.

Are you asking for one of the above options, or something else. An example would be very useful.

PGoldy

|||

Paul,

The calculated measure is MarketCount (distinct count of MarketKey). I am looking for the closing value of the measure across time.

The MarketCount is a monthly snapshot and should not be added across time.

For example, if the MarketCount is 110 in July and 120 in Aug, when the user browses the data in the cube for the months of July and August, the MarketCount should display as 120 and not as 230.

Thanks.

Rohit

|||

SSAS 2005 does have built-in support for semi-additive measures like last child, but only in the Enterprise Edition

http://msdn2.microsoft.com/en-us/library/ms189734.aspx

If you have the standard edition you could achieve the same results through calculations in the MDX script (although the performance would not be as good as the built-in feature)

|||

I am aware of the built in function for Last Child but I need to find the Distinct Count of the Last Child of one of the Fact keys. Please refer to my previous post for further details.

Paul, any ideas?

Rohit

|||

Hi Rohit:

I'm operating on a couple of assumptions here: (1) Calendar hirrearchy has structure: AllCalendar->Year->Quarter->Month (2) The lowest level in Calendar where MarketCount is valid is Month. (3) You want to see the MarketCount for the last month in the higher selected time period (Quarter, or Year). As an example, the MarketCount value for March 2006 is 130, then the Ending_MarketCount for Q1 2006 is also 130. Here's a calculated member definition for Ending_MarketCount based on my assumptions:

(TAIL(DESCENDANTS(Calendars.Calendars.CurrentMember, [Calendars].[Calendars].[Month]), 1).Item(0), Measures.MarketCount))

The use of TAIL(DESCENDANTS() finds the last month in the quarter if quarter is the current time level. It also finds the last month in the year if Year is the current time level, and defaults to the current month if a month is the current calendar selection.

Hope this helps.

PGoldy

|||

Sorry, I left an extra parentheses on the end of the calculated member definition. Here is the corrected definition:

(TAIL(DESCENDANTS(Calendars.Calendars.CurrentMember, [Calendars].[Calendars].[Month]), 1).Item(0), Measures.MarketCount)

PGoldy

|||

Have you thought about what you want the users to see when the data is incomplete?

For example, if we currently only have data up to August, but the users want to see the value as at calendar quarter Q3, should they see 0, because September currently has no value? Or should the calculation display the last non empty child (August)?

Wrapping the descendants function with NonEmpty() would give you "Last Non Empty Child" behaviour, but whether you need this or not depends what your business requirements are.

eg.

(TAIL(NONEMPTY(DESCENDANTS(Calendars.Calendars.CurrentMember, [Calendars].[Calendars].[Month])), 1).Item(0), Measures.MarketCount)

...just a thought

|||

Hi Paul,

I tried your suggestion but it did not work. I might be missing something. Let me explain my scenario in more detail.

I have the following dimensions -

DimTime - Hierarchy is Year - Quarter - Month (This dimension table has a granularity of date but I have set the granularity for this dimension in the cube to month level. Would this be a problem? I need the date for other Facts which are not part of the cube in question.)

DimMarket

DimInstrumentModel

DimTerritory (Geography)

I have an aggregate Fact Table 'FactInstallBase' which has a monthly snapshot of the instruments installed and active at various customer sites. Each customer can have one or more instruments. A few sample records in the Fact table look as follows

MarketKey InstrumentModelKey TimeKey TerritoryKey InstrumentCount

123 111 222 333 2

123 112 222 333 1

125 111 222 444 2

There is a requirement to display a count of the markets that own instruments belonging to a certain Instrument model. The users browse the cube using Excel pivot table.

I have created a calculated measure 'MarketCount' and set the aggregation for this measure to DIstinctCount of MarketKey. This works fine when the users browse only one month's data. But when the user's select multiple months like Jan-2006 and Feb-2006, this mesaure gets added for both the months. If the Distinct Market Count for Jan '06 is 110 and for Feb '06 is 120 the MarketCount is displayed as 230 (110+120). This measure is semi-additive and should not be added over time. So even if the user selects multiple months, the MarketCount measure should display as the last month's MarketCount (In this case 120). Am I doing something wrong?

Any help would be greatly appreciated.

Rohit

|||

Paul,

I changed the granularity in my Time Dimension table to Month. The hierarchy for this dimension is Year - Quarter - Month but the MDX code you sent for the calculated measure still does not work.

Rohit