Avoid Surrogate Key in Transformer Model Cube

[cognos_dev]

Hello All,
I have a requirement where I have to build a cube with multiple source. I have huge set of dimensions and a fact table. These dimension tables have surrogate keys and these are linked to the fact table.

For e.g., Product Dimension has 'Product_SK' which is a combination of Prod_Id, Prod_Line_Id, Prod_Type_Id, Prod_Grp_Id and Region Dimension has 'Region_SK' which is a combination of Reg_Id, Country_Id, State_Id, Cty_Id.

The Fact table 'Sales' has columns - Region_SK, Product_SK, Sales_Amount. If I am bringing them into my transformer for cube model, then my last level of diemnsion in dimension map will be Prod_SK for Products and Region_SK for Region.

Since they dont really have any meaningful detail, how do I avoid this SK's in my dimension. How do I go about constructing this cube. Please guide me with some useful tips.

Regards,
S

[rockytopmark]

There are many properties of each level. 

The Product_SK will be the Source, the Label will be the Prod_ID, for instance, in the Product Dimension.  There is also Long Name and Short Name.  Long name might be the Product Description and Short Name some other piece of information.

The _SK is the source and will be what ties the Dimension to the Facts.  The Label will be what the Users see.

You are very lucky to have the Surragate Keys.  Good Design!!!

[COGNOiSe administrator]

Very good design indeed. Some many people forget about the additional properties of each Transformer categoy. In my case, use of surrogate keys or generated unique keys is an absolute must have for category codes. That's the only way you can prevent your reports based on cubes from braking in the future.