Calculated measure using Tail/Filter on time not always working

We created the following time based measure to get the distinct count of propertyId - but we needed to get the distinct count from the last fact table load in a time period so we used the following

Code Snippet

IIF([Time].Currentmember.level.ordinal = 4, [Measures].[Property Id],

IIF([Time].Currentmember.level.ordinal = 3,

(tail(filter([Time].Currentmember.children, NOTISEMPTY([Measures].[Number of Buildings])), 1).item(0), [Measures].[Number of Buildings]),

IIF([Time].Currentmember.level.ordinal = 2,

IIF([Time].Currentmember.properties("Is Quarter") = "1",

(tail(filter([Time].Currentmember.children, NOTISEMPTY([Measures].[Number of Buildings])), 1).item(0), [Measures].[Number of Buildings]), ([Time].Currentmember.lastchild, [Measures].[Number of Buildings])),

IIF([Time].Currentmember.level.ordinal = 1,

IIF([Time].Currentmember.properties("Is Year") = "1",

(tail(filter([Time].Currentmember.children, NOTISEMPTY([Measures].[Number of Buildings])), 1).item(0), [Measures].[Number of Buildings]), ([Time].Currentmember.lastchild, [Measures].[Number of Buildings])),

IIF([Time].Currentmember.level.ordinal = 0,

(tail(filter([Time].Currentmember.children, NOTISEMPTY([Measures].[Number of Buildings])), 1).item(0), [Measures].[Number of Buildings]),

NULL

)))))

In most cases, the calculation seems to work fine, but sometimes, when browsing the cube using Proclarity (6.1), no values are returned when we know there are values...

Can anyone see why this measure would not work in some circumstances?

I think that basically, what I'm asking for is a method of determining a distinct count of the last recordset from a fact table for any time dimension level (which is what the above is trying to do).

The above method seems only to work rather haphazardly, especially when viewing via Proclarity (6.1) When you move certain dimensions to the background, the calculation returns zero when we knbow data exists...

Is there a more efficient way of finding this sort of distinct count (using SQL 2000) ?

|||Anyone fancy a stab at this ?|||

Hi,

have not read the whole mdx,

do not know proclarity

This does not work if you do a multiselect on the time dimension beause of CurrentMember (there is no current member if you have a subquery)

Does it return null or #error and do not show up.

It really depends on how your client does write his queries. I would recomend you trace your queries with profiler and then analyse the query which is returning wrong results. Check where and how the measure is used and check if there are any subqueries based on time.

Best Regards, HANNES

|||

Thanks for having a look at this

The following query will not return any values for No of Buildings

Code Snippet

SELECT { [Measures].[Number of Units], [Measures].[Number of Buildings], [Measures].[Property Id] } ON COLUMNS ,

{ DESCENDANTS( [Building Status].[All Building Status], [Building Status].[Building Status] ) } ON ROWS

FROM [Property]

WHERE ( [Time].[Year].&[2007] )

I can't really see why not, as it should pick up the latest data for 2007 should it not, given the calculations in my first post ?

|||

Hi,

I am a little bit confused about the complexity...

A few more questions.

Do you have only one Hierarchy in Time? (All Attribute hierarchies are hidden - I guess yes because you do not specify an hierarchy name)

Is it a 2005 cube or 2000?

Are the properties populated correct? Is Quarter, Is Year

Why you need this properties - if its one hierarchy then level 2 could only be quarters? and level 1 only years and level 0 only ALL if I am right

Why do you write this complex form - why not only decendants(time.currentmember),time.levels.count, leaves) which your tail logic

maybe one of this is a hint to you.

HANNES

PS: I would alos recommend moshas blog with design time level checking instad of runtime level checking. Scope instead of member with iif (only for design purpose - have nothing to do with functionality)

|||

Hi,

1. SQL 2000 cubes - and yes, the properties are all fine and we can't change time dimension at this stage as it works fine itself.

It's this complex because we have daily loading in certain parts of DW but weekly in others - in terms of a measure using distinct count

this presents issues when querying measures from weekly loads in terms of needing to hit the last load in a period, be it month, quarter, year or All Time...

In any case we fixed the issue by creating a completely new view for building count as we read that distinct count runs in to known issues when AGGREGATE used in MDX queries, so in developing the new view we populated a 1 for propertyid as opposed to attempting to do a distinct count - also we created calculated cells to deal with the time hierarchy levels issue - we then based a new cube containing just building count but shared same dimensions with the old cube, containing other aggregateable valid measures that worked.

Megred the two cubes into a new virtual cube & now building count works fine So problem solved. I think the tail/filter issue was a red herring in any case - the data that got was fine - AS 2000 just does not like distinct counts (in the way we were trying to use them...)

Thanks for getting involved with this though, but i'll mark it as solved for now.