Organizing a fact table with a composite primary key

Question

In my SSAS 2005 cube I have a fact table related to sales invoices.
I haven't defined the primary key in the underlying dwh; I think to the invoice number + invoice date
(and perhaps I will add also the line number).
When I will query the cube I will aggregate in respect to these two fields.
Do I include my fact table into the dimensions from the dsv?
Do I create a dimension that includes the invoice number and the invoice date?
Any suggests for me, please? Thanks

Answer 1

It depends on your requirement. For example, if you only want to analyze the invoice and the lowest granularity is invoice number, you can only create a dimension with key attribute invoice number. But if you want to analyze the details, for example (invoice number + invoice date), then you’d better generate a numeric surrogate key in dimension table base on invoice number and invoice date, set the key attribute with composite key columns (invoice number + invoice date), and then join the fact table with invoice number and invoice date in Usage tab. You can also generate this numeric surrogate key in fact table with named calculation, then you don’t need the composite key columns.

 

A good document about design may helpful in the future:

http://technet.microsoft.com/en-us/library/cc966399.aspx

 

Regards,

Raymond