Not Fact-existing members in DMR Dimensions...

[IceTea2]

Hi folks,

i hope i can give you a hint about what i'm thinking about.

Problem as following:

I have a fact table (e.g. "sales_fact") which has some date attributes (e.g. "sales_date" which contains data from - let's say - years 2005 to 2010). This fact table can be joined with our DWH date dimension table, which includes all dates from year 1990 until 2099.

While creating a DMR on this date i think the base common approach is to model a DMR Time dimension based on the Date dimension table as well as a Measure dimension based on measures from the fact table.

If i use this DMR Package in a member based studio (e.g. Analysis Studio), a bad effect is, that all the members from the Date dimension are selectable in the member tree.

It looks like:

Date Dimension
- 1990
- 1991
- 1992
- 1993
...
- 2099

This is confusing for the users, cause real data (from fact table) is available only for years 2005 to 2010.

If i'm doing a real OLAP Transformer cube on this data, only the in-fact-available members of the year dimension are in the member tree. But i like to know if there's a common approach to achieve this goal with DMR. To base the time dimension on the fact table is theoretically possible, but your'e losing any speed advantage, cause every membertree-click does an full table scan.

Hints anyone?

[blom0344]

There are a couple of solutions for this. The first trick would be to index the factable on the year column. The database optimizer can then very efficiently choose to scan the (very) small index for values instead of a full table scan. An alternative would be to add a 'filter' expression to the time dimension - fact join provided it is only used for the fact that you describe.
another option would be to tune the dimension for a specific fact (like by adding a view / indexed view) to the database and import the view for dimensional purposes..

(You might consider adding an index on year for performance sake altogether)