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.

Caching problem?

If I create a view and then a mining structure run it everything works fine BUT if I then alter the view (e.g change a filter setting in a named query) or the actual contents of the database, the OLD contents persist.

Using the view refresh says there are no changes (which is correct as I don't change any column names etc.). If I remove Keep Training Cases, this fixes it, but for Time Series, it then gives an error, so you have to go back and re-select it... is there a direct option to clear caches?

The dependency settings aren't sensitive to non-destructive changes in the DSV. You can simply do a ProcessFull on the mining structure to reprocess from the data.

There is an option ProcessClearStructureOnly (something like that), that clears the training cases from the cache.

TimeSeries requires the cache or the viewer will not work - you can likely drop the cache using the process method above, but you will get errors when trying to view the time series models (at least the chart part)

Caching and Dynamic Security

I am working on a dynamic security implementation where an SSAS stored procedure will obtain the allowed set filter of the permitted customers from an external security authority based on the indentity of the interactive user (Username function). This filter will be applied to all dimensions, e.g. the user can see only accounts that belong to the allowed customers only. The users will use Excel and Report Builder to create reports from the cube in a client-server fashion.

I want to cache the security filter per user so I don't have to access the external security authority when propagating the security filter to all dimensions. In other words, once I obtain the list of allowed customers ({[Customer].[Customer].&[1], [Customer].[Customer].&[2], etc.), I want to "reuse" it across the rest of the dimensions, e.g. EXISTS([Account].[Account].Members, {[Customer].[Customer].&[1], [Customer].[Customer].&[2]}).

What will be the recommended caching solution?

Hi!

You can apply Visual totals option for customer code attribute. In your example users will see, say, product dimension, but they will not see sales for other products then those made by restricted set of customers, which is I hope your intended behavior.

Radim|||Thank you, Radim, but this actually not I was asking for. I was hoping for a recommended approach for caching inside SSAS stored procedures so I don't have to ask the external security authority each time I need to apply the same set across the dimensions that need it. This is a followup question to this thread.