Identify Dimension elements not Used

[CiskoWalt]

Hello,

Is there an easy way to identify the elements in a dimesninon that are not used (no data in the cubes) ?
Some of our dimensions are shared by more than one cube.

A few years ago our chart of accounts was changed and data was migrated from say entity XXXX to new entity YYY. Some of the old entities were not mapped to new entities so it is possible that some of the old XXXX entities have data.

Looking for a better solution than summing data by entiy for each cube and then manually identifying the entities that are not used.

Thanks,

W

[Steve Rowe]

I think that a manual approach is proabably easier.

It could be possible to do something with TI along the lines of
Create an attribute called Populated in the dimension concerned.
Create level 0 views of the cubes concerned (you might have to manage the size of these), maybe a view with C levels on everything else but level 0 on the dimension of interest.
Point a TI at the view created above, process the view and populate the attribute with a 1 if the element is encountered.

Hope thsi gives yous ome ideas.
Cheers,

[Steve Vincent]

or you could use Excel as well. Put the full list of elements down the side and write a DBRW to grab data from each cube its used in, using the top level consolidation for each fo the other dims in the cube. Then just sum across each account and then filter that to find any zero values across all cubes.

[nhavis]

Instead of summing the elements and looking for 0 -

Use something like {=AND(A1:C1=0)} which will avoid the case when cells are negating each other eg. -2, 0 and 2 will sum to 0.

[Alan Kirk]

Instead of summing the elements and looking for 0 -

Use something like {=AND(A1:C1=0)} which will avoid the case when cells are negating each other eg. -2, 0 and 2 will sum to 0.

I find the SumSq function useful for that purpose.