Dimension Design Question

I have a question about designing a dimension.

I have a fact table with 2 bit columns "IsBalance" & "IsAttribution". Possible values are (0 , 1), (1, 0) and (1,1). Pl note that the fact table cannot be changed.

I use SSAS 2005. I had created 2 dimensions (from named queries) called Balance (Members : 1, 0) and Attribution (Members : 1, 0) to implement the above. Both link to fact table directly.

Obviously this works but it is not the 100% correct business representation. The ideal way is to have a dimension called Type and have following members in it ==> Balance, Attribution, Both. Any suggestion on how to acheive this? Or Is there a better way of doing it?

Thanks,

Arun

It sounds like you just need to create a single dimension from a named query which contains the following rows:

DimKey Balance Attribution Both 0 TRUE FALSE FALSE 1 FALSE TRUE FALSE 2 TRUE TRUE TRUE

You can now build the three attributes you want from this table, plus a key attribute from the DimKey column which you can hide.

If the fact table can't be changed, you've got two options: either create a new named calculation on the fact table in the dsv to derive the DimKey value from the two existing columns or use a composite key for the DimKey attribute rather than the single column described above and join the two columns in this composite key to the two existing columns in your fact table.

HTH,

Chris

|||

Thanks Chris.

Sorry I was not clear in my first post.

Is it possible to have an attribute with following members ==> Balance, Attribution, Both from which the users can select either of them to filter the data across ?

The problem here is that when "Balance" member is selected, it should filter data for combination (Balance = 1, Attribution = 0) and (Balance = 1, Attribution = 1) . Same thing should happen for Attribution too.

|||

Ahh, ok. This sounds like a many-to-many relationship: keep the dimension designed in my previous reply but only build a single attribute on it based on the key, and then hide the whole dimension. Then create a dimension containing the single attribute that you want to see, off the following table:

M2MDimKey M2MDimName 0 Balance 1 Attribution 2 Both

Then create an measure group from a fact table containing the following values:

DimKey M2MDimKey 0 0 1 1 2 2 2 0 2 1

Put in regular relationships between the two new dimensions and this measure group, and last of all you'll be able to give the new dimension based on M2MDimKey/M2MDimName a many-to-many relationship to your main measure group. Now you should get the behaviour you want: selecting the Balance member will give you the aggregated values for all rows where Balance=1, but the Both member will only give you the value for Balance=1 and Attribution=1, and your All Member values will also show correct values.

Chris

|||

Thanks a lot Chris. Tried it out today and works great.

One of the things that I had noted :

I had set the "IsAggregatable" to False (as the "All" member does not make any sense in this case. "Both" member in level 1 will be the quivalent of top level) for this dimension and had set the default value to "Both". When I use the Agg design wizard, it complains that the dimension does not have any agg attribute and hence cannot proceed.

The work around is to set the "IsAggregatable" to True, design and set it back to "False" again. But is this is the right thing to do? (Should I use BIDS helper and delete agg that uses the top level of this dimension)

|||

Hmm, that's strange - I've never had that problem when setting IsAggregatable to false. Had you been setting the AggregationUsage property on the cube dimension or something?

Chris

|||Nope. I haven't changed the defaults on the cube dimension properties. I had set only for the dimension directly.