dimension design

I have a fact table and dimension. a view is taken as base table for Dimension which will Productkey,LevelId. One product may exist in multiple levels. so we have multiple records for a single product.

And in fact table we have productkey as foriegnKey. When i relate fact n dim to create a cube, cube considering one record for each product key in fact table. its automatically avoiding other records. can we any way stop this happening? OR is this default behavior?

Thanks in adv

Do you have a self-join in your product dimensions table between productid and parentproductid? This sounds to me as if you have a parent-child dimension/structure. In the dimension wizard you will see, in one of the final steps, a question/page where you can tell if you have a parent child structure.

You will also check if you are not having a many-to-many relation between the fact table and the product dimension table.

The third point is that you can add the level to the productid and make a unique technical key for each kombination of product and level. This will have to be matched with the records in the fact table.

HTH

Thomas Ivarsson

|||

Thanks for the response.

3rd point is not possible to implement..let me check with first point.

so does it ignore multiple records for same productKey?

|||

Hello again. In the AdventureWorks sample database you have table, Production.BillOfMaterials that you can have a look at. In the Adventure Works DW sample database you have the account dimension with a more simple parent child relation.

I am not sure what you mean by "ignore multiple records for the same productkey.

With parent-child dimensions you can have data/facts in the fact table that is not only from the leaf level but also from higher levels.

HTH

Thomas Ivarsson

|||

In this case its not possible to implement Parent-Child dimension. and there is also situation that one child product can have multiple products as parents. Its some thing like many-many in Parent-child.

|||

Looks like you have to use many-to-many dimensions then.

Here is a link to some help: (http://www.sqlbi.eu/Default.aspx?tabid=80)

HTH

Thomas Ivarsson

|||

Before coming to forum for the solution, i had tried m2m dimension. When i took the view as dimension where one product can exist in multiple lelvels. table will look some thing like this

leafPKey 5233 5233

L1PKey 9 10

L2PKey 31 15

L3PKey 632 632

L4PKey 2572 2572

L5PKey 5233 5233

When this table is related to fact table only one 5233 is considered. 5233 is only visible under l1pkey= 10.

is this default behavior, or can i get both the ids by changing some thing?

when a single record is only considered m2m relation is existing.

Thanks in adv

sql