Dimensions at multiple levels (one to many) in the same cube?

[RickJ]

Hello all,

I've been scouring the web for an answer to this problem but haven't come across one (or at the very least I didn't understand it when I did).

I'm currently working on a cube in Transformer 10.2 for Cognos 10.2.

The issue...
I have two sets of data: Subscriber Data and Features Data

The Subscriber Data is the subscriber information such as Name and Total Subscriber Cost.
The Features Data describes the features of each subscriber. A subscriber can have one to many features and each feature has it's own cost.

The Features Data fact table contains a subscriber key that allows me to link between features and subscribers. This join is done in framework manager as well.

I am using a report for my data source and if I bring in the query items for both subscribers and features, it will duplicate all the subscriber data on each row for each feature. This is standard for joining data of this type, however if I was to build the cube off this all the subscriber facts (ie Subscriber Cost) would be duplicated for each feature they have and return an incorrect amount.

What I want to do is show the feature names in the cube as a dimension but not duplicate any of the higher level facts do to so.

I am quite new when it comes to Cognos Transformer so maybe this is easy but I haven't found a way. I did try using the subscriber key in the feature name dimension to allow transformer to relate the two data sources (I tried bringing in the features data separately) but I am unable to set it to unique so I do not believe it will work.

Any thoughts or suggestions?

Thank you!!

[bdbits]

It seems to me like this is a design issue. I would have a single fact table with a name like Features Ordered Fact. Each row would be a feature ordered by a subscriber, with a single measure of Cost. There would be a dimension called Features Dim, and Subscribers Dim. You don't need the aggregate Total Cost, that is a roll up from the fact table.

Maybe I have misunderstood, but I think you have modeled it as two fact tables. If the underlying model is changed as I suggest, it should be straightforward to make a cube out of it without any worries about duplicates.

[RickJ]

Thank you for your reply bdbits!

I think I wasn't entirely clear on Total Subscriber Cost. There are many facts at the subscriber level that cannot be rolled up to from the Feature level. As such I am not able to roll up the subscriber level facts from lower level tables.

The features data is simply an association to the subscriber data, but provides further insight into one very specific instance. This is why I'm unsure how to show the data in a cube (or if it can even be done).