Surrogate Key causing incorrect results

Hi

Sorry for the newbie question, i am just starting out with SSAS and I am trying to figure out how to do the following:-

I have a applications fact table with a surrogate key (auto number) and business key, I am trying to calculate applications submission over time, but because the business key may appear more than once (a new row is inserted when the application status changes, and the Surrogate key is the logical key) this causes my application numbers to be higher than they actually are. The only way i can see to alter this is to use a query in my dataset view to group and use my business key as a logical key.

I am sure this is a common problem and i am just missing something obvious. If anyone can point me in the direction of some online learning materials for SSAS for a complete beginer that woud d be great.

Thanks

Its a common misconception, primarily due to the notion of primary keys as used in a typical 3NF OLTP application where the primary key is usually the business key.

In a dimensional model using surrogate keys, the purpose of the key is to simply associate rows, they are not part of the BI 'view'. Queries integrate and report data based on attributes, not keys. The natural key is one such attribute.

Thanks for your reply, so are you saying i am right to replace my fact table with a query that removes the surrogate key? or is there something i am missing in the ssas cube designer because it i can't seem to unset the surrogate key as the logical key.

Thanks Paul

PugMaster wrote:Thanks for your reply, so are you saying i am right to replace my fact table with a query that removes the surrogate key? or is there something i am missing in the ssas cube designer because it i can't seem to unset the surrogate key as the logical key.

Thanks Paul

The query should be grouping on the natural key, not the surrogate key... if that is what you mean by 'removing the surrogate key'. The surrogate key is used solely to join the fact rows to its dimension rows.

Excellent, yes that is what i meant, it would seem not using the cube wizard would be a wise idea!!!

Many thanks for your prompt response.