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.

Extracting Latest Date

I have a db which stores all my sales transactions.
The task is to extract the buy price(price) for each item(ItemCode) where the
date is the latest for each item.
For Example:
Sales Table
ItemCode Qty Price Date
-- -- -- --
XYZ 2 23.50 12/03/05
XYZ 3 23.50 13/03/05
XYZ 2 99.99 14/03/05
ABC 14 88.88 01/01/05
RDB 12 77.30 21/04/05
RDB 23 23.35 02/05/05
I would then require only
ItemCode Qty Price Date
-- -- -- --
XYZ 2 99.99 14/03/05
ABC 14 88.88 01/01/05
RDB 23 23.35 02/05/05
How do i write a query to extract the data?
Need help with a project of mine...really stuck with this one..Thank you
On Thu, 04 Aug 2005 07:31:53 GMT, Jan S via droptable.com wrote:

>I have a db which stores all my sales transactions.
>The task is to extract the buy price(price) for each item(ItemCode) where the
>date is the latest for each item.
(snip)
>How do i write a query to extract the data?
> Need help with a project of mine...really stuck with this one..Thank you
Hi Jan,
Method #1:
SELECT a.ItemCode, a.Qty, a.Price, a.[Date]
FROM Sales AS a
WHERE NOT EXISTS
(SELECT *
FROM Sales AS b
WHERE b.ItemCode = a.ItemCode
AND b.[Date] > a.[Date])
Method #2:
SELECT a.ItemCode, a.Qty, a.Price, a.[Date]
FROM Sales AS a
WHERE a.[Date] =
(SELECT MAX(b.[Date])
FROM Sales AS b
WHERE b.ItemCode = a.ItemCode)
Method #3:
SELECT a.ItemCode, a.Qty, a.Price, a.[Date]
FROM Sales AS a
INNER JOIN (SELECT ItemCode, MAX([Date]) AS MaxDate
FROM Sales
GROUP BY ItemCode) AS b
ON b.ItemCode = a.ItemCode
AND b.MaxDate = a.[Date]
(And there might even be more methods...)
If performance is important, then test each of these queries a few times
and use the one that's the fastest. Otherwise, use the one that you find
the easiest to understand, as you'll have to maintain it later.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo..Much appreciated
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...-mseq/200508/1