Limiting dimensional data that is only relative to the fact data?

[lochew]

Hey Everyone,

I wanted to know if there is a way to limit the dimensional level data that is only relative to the fact data. Example: Within a dimension call store I have a listing of all my stores Store1, Store2, Store3. If I only have Fact data for Store1 how can I make only this store visible within the Store dimension then as fact data comes available for Store2 have MSAS make available this information. Currently it displays all stores but has no values. When displaying this cube within Cognos Power Play I see all the Stores but have zeros for the measures. If I suppress zeros within Power Play it hides the stores with no data. So my question is as follows: Is there a way to have MSAS hide these members within the level prior to the end users view the cube? I would rather not use zero suppression within powerplay for obvious reason (dimension nesting).

Any thoughts on this would be great.

Cheers

LC

 

[MDXer]

Been a while since I have looked but I believe in the dimension editor there is a visibility property.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Steri]

The only way I know is to build a local dimension directly from the fact table (where necessary include dimension info with the fact info).
This way if the dimension level exists in the facts then it will be included

Andrew

 

[gwilson67]

LC,

What I would do to limit dimension data to ONLY data that is in the fact table is to create a partition filter. In your example, you used Store 1 -3 in dimension and just store 1 in fact table. In the Partition filter for the cube, you would type in where database.dbo.store.storename = 'Store 1'

You get to this filter by editing a partition for a cube and going to the very last step and then you select advanced.

g

 

[SydneyUser]

I would have thought the easiest way, would be to create your dimension via a view along the lines of:

Create view StoreDimension as
Select *
From DimStore s
Where exists
(Select 1 from FactTable f
Where s.store_id = f.store_id)

Only downside is that the dimension isn't necessarily reusable across cubes.