Seldom used Dimension member

[Rob999]

Hi everyone. I've looked around and haven't seen this, so here goes.

I'm creating a Fact table of customer transactions where approximately 5 - 10% of the transactions belong to a "promotion" - type event, and get an alphanumeric Promotion Code for the transaction. I update all the other records with a '0' in the field because they were not part of a promotion and are not analyzed by that dimension.

I don't know if this is new (I'm using SQL Server 2005), but now the Cube Wizard is forcing me to create a '0' entry record in the Promotion Dimension or the cube won't process (the Promotional Code is the Foreign key in the Fact Table). I know from a relational stand point orphaned records are a bad thing, but they truly do not belong in the Promotion Dimension.

Unfortunately, when the cube DOES process I now have a huge Dimension member for all the '0' records, which needlessly consists of millions of records now aggregated...

Is this a design issue? Is there a way to tell BI Studio I don't want to include the '0' Dimension member in the cube or aggregations? I do NOT want to have two fact tables - a Promotion one which is just a subset of the real one. I could create a Promotion Dimension which is joined by Account number and Transaction ID - technically incorrect, but I think I'd get rid of the '0' dimension by not having a Promotion Code column.

Any insights or comments would be greatly appreciated. Thanks!

 

[MDXer]

Easiest way would probably to build your cube against a view of the fact table that way you can filter out the 0 Key records in the view.

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

 

[johnherman]

Why are you using 0 instead of leaving the field null when not applicable? Not positive, but I think that's the problem.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[Rob999]

Hi guys, thanks a bunch for the replies.

MDXer, I was hoping to just have one comprehensive view of the business - something '05 is supposed to allow. I believe a view would separate the data and I would have to do something to reconnect them at some point. But that may be my only option.

JH, I'll definitely try that and it may be how I've done it in the past (as noted, this is the first time I recall having this problem). Probably updated with '0' out of a natural aversion to nulls.

Thanks again for the ideas. If you or anyone comes up with anything else please let me know, and I'll repost when I have some more information.