How to build Dimensional Table in Data Manager 8

[jpdmdc]

Hi,

I'm working on Cognos Data Manager (Version 8).
I have built a Hierarchy manually with two levels which talk about Boxes (Level 1) and Components (Level 2).
Now I want to build a Dimentional table where I want the Box_Id of Level 1 to have Surrogate Key.
I’ll give you an example of data below.

Box Id Components Qty

101 10010 2
101 10020 4
101 10030 3
102 10010 2
102 20045 5

And so on.

So, a component might be in more than one box and a box would have more than one component.

Could you please give me some suggestions how I should build a Dimensional Table for this?

I want Box Id 101 to carry 1 as surrogate key and repeat thrice in the table and 102 should be given 2 as surrogate key.
I have box_id in my Fact Table and want to replace it with surrogate key of Box Id. Actually the users should be able to see the components of
The boxes if the want.

Hope I explained it well. It would be a great help if anyone can help me with this!

Thank you!

 

[mfgf]

Hi,

Data Manager will allow you to do this quite easily. The hierarchy you describe is a multi-parent hierarchy, and these are supported in Data Manager. Given that you already have your hierarchy defined, you need to:
a) Create a dimension build to deliver it to your dimension table - if you use the wizard, check the surrogates option to generate surrogates for the components level.
b) Modify the output template used by the dimension build to add a second surrogate key based on the Box ID.

When you run the build to deliver the dimension table, you will get two surrogate columns in the resultant table - one based on Component ID and one based on Box ID.

Hope that helps,

MF.