Duplicate Dimension Members

Hi everyone,

In AS 2000, some of the dimensions I was using had both the key & the name of the dimension as the same text field (dimTable.name). This grouped duplicate dimension members together. Browsing the dimension only showed unique members.

In SSAS, my understanding is you need a key attribute that links to the fact table, or another reference dimension. Since the dimension I was using had an intermediate key on another dimension table, I created a key in the dimension, and used a reference dimension to link indirectly to the fact table to another dimension.

Now I'm getting duplicate dimension members, since the keys are unique in the dimension table. Is there anyway I can consolidate these?

I'll try and diagram for a better explanation.

status dimension table --> dimension table --> fact table

status dimension table has key, name, foreignkey fields that links to a dimension. There can be many statuses for the dimension, based on the foreign key. There are duplicate statuses.

Any help is much appreciated!

...perhaps it's easier to understand if you post some sample data for your scenario... I get a little confused what's duplicate and what's not... Maybe you're talking about a n:m dimension...|||

It seems to be happening not just with the dimension with 2 reference tables, but a standard 1-M dimension-fact relationship.

1 example.

DIM table.

STAT_ID - STAT_NAME

1 Andrew
2 Thomas
3 Thomas
4 Andrew
5 Andrew

Fact Table

STAT_ID
1
1
3
4

AS 2000 Dimension

Key - STAT_NAME
Name - STAT_NAME

Relationship in Cube - STAT_ID - STAT_ID

The result is 1 name in dimensino but keys are related.

AS 2005 Dimension
Key - Stat_ID
Name - Stat Name

A new attribute is created for Stat_ID
This attribute is used in cube Dimensions tab.
The tables are linked in DSV to STAT_ID

The result is duplicate names, due to different keys.

One suggestion is to create a Named query & join on the names in the DSV. I don't want to do that for every dimension we have KEY & Name with the same field in AS 2000.

If I change the Key in the dimension back to STAT_NAME, I can no longer link the granularity settings in the cube.

|||

Andrew,

what's the reason for the key in the dimension table not being unique? Perhaps you should setup some ETL to make this "clean"...

|||

I was thinking about doing that, however I didn't want to change the 2000 data model. Can I do that in the DSV, by just creating a derived query on the name column?

The reason they're not unique is because the data isn't totally denormalized. The actual table looks more like...

key -- status name -- child name

where status name is not unique, but the child is.

So do you think we should split group name off into it's own table, or is there a way to set a property on the dimension so that it will ignore duplicates? It seemed to work in AS 2000.

thanks for all your help,

Andrew

Dummy dimension for passing parameters

Hi,

I'm just having a little problem... I have a calculation, which is a non-trivial formula. This needs input from some dimensions (no problem with that...) and some information, which is independant from the data itself. The idea is to have something like a dimension but which is not connected to the data itself. You can't slice anything using it, it's just used to provide some input for the formula. Just imagine you have some amounts and you want to display the interests for them. The interests are nothing from the cube, they just are needed as input for the formula to calculate the interests. In the cube you only have the amount. Sure, you can add different measures for each interest rate you need, but this might be quite a pain...

Any idea?

Thomas,

You might be able to get what you want by using the same method as was outlined in an article on building a dimension for "cell annotations". Here is the link:

http://www.sqlserveranalysisservices.com/OLAPPapers/Cell%20Annotations%20in%20AS2005v1.htm

The idea is that you have a dimension table that has a compound key made up of the key values that identify a cell in the cube and then the additional attributes can be comments or a formula or whatever you want. You can then retrieve information from the dimension using the script as outlined in the article. The annotations dimension does not have to be related to any measure group.

HTH,

- Steve

|||

Hi Thomas,

AS2005 supports dimensions which aren't connected to any measure group in your cube - just go to the Dimensions tab in the Cube Editor, click on the box at the intersection of the dimension and the measure group, and set the 'Relationship Type' property in the dropdown at the top of the dialog to 'No Relationship'. I've used this method several times to include dimensions in cubes which don't affect any of the regular measures but which are used in calculations to control how they behave.

Regards,

Chris

|||

Chris,

thanks, works perfectly... It's just too easy to find out ;-)

Hope you had a good trip back from PASS... Your session helped me very much to do the custom rollups I asked you about... Seams to work, but some crasy things going on with the scopes... But that's something for later...