Fact and Dimension linking

Hi,

Am a bit of a beginner in this area, so this may sound a little dumb.

I am trying to develop my first cube based on consolidated sales data from 10 different companies, and have a problem wrt linking a dimension table to this fact table.

I have a dimension table which contains the products which can be manufactured by these 10 companies. There are scenarios where one product can be made in several of these companies.

I therefore have a table with all the sales line items (my fact table) which are uniquely identified by invoice no, invoice line num, and originating site.

The product table (dimension table) is uniquely identified by item code and site.

When I create my data source view I create a join between the two tables with sales.site = products.site and sales.itemcode = products.itemcode

If I then create a dimension from my products table and browse them in the cube, the sales data just gets repeated for each product and is not correctly split.

I think that the link I have between the dimension and the fact table is wrong, as when I set the relationship I can only choose one column to link on.

I'm kinda getting lost on this one, and could really do with some pointers. Restructuring the tables are not too much of a problem (if they are wrong) as the project has not been officially released yet.

Any help would be greatly appreciated.

Hello! In the cube editor you have a dimension usage tab where you configure the relation between each dimension and measure group(fact table). If you have created som dimension after the you have created the cube this relation will not be configured automatically.

It seems to me as you relations are correct in the data source view. You can have a problem with that the product key is defined without the site key in the dimension editor. You will have to make a collection of product key and site key. Check the dimension key for this property.

HTH

Thomas Ivarsson

|||

You might check the KeyColumns property for the key attribute of your Product dimension - a composite key {item code, site} should be defined. Then you should be able to join the fact table using this composite key.

http://msdn2.microsoft.com/en-us/library/ms175461.aspx

>>

SQL Server 2005 Books Online

How to: Modify the KeyColumn Property of an Attribute

This procedure describes how to modify the KeyColumns property of an attribute. For example, you may want to specify a composite key rather than a single key as the key for the attribute.

...

>>

|||The item code and site also have to be present in the fact table for this to work. Your message says that the invoice line num is present in the fact table - you have to be able to derive the product item code from this.|||

Thx everyone... after posting I sat down and read the book again to make sure I hadn't missed anything out. And you are right, the tables are OK in my DSV, but the dimension had not been configured correctly w.r.t. its attributes.

As soon as I had that done correctly, of course it all fell into place..

Thx again.