Surrogate Key causing incorrect results

View previous topic View next topic Go down

Surrogate Key causing incorrect results

Post  PugMaster on Fri Sep 10, 2010 3:10 am

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

PugMaster

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: Surrogate Key causing incorrect results

Post  ngalemmo on Fri Sep 10, 2010 11:38 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Surrogate Key causing incorrect results

Post  PugMaster on Fri Sep 10, 2010 2:39 pm

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

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: Surrogate Key causing incorrect results

Post  ngalemmo on Fri Sep 10, 2010 3:07 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Surrogate Key causing incorrect results

Post  PugMaster on Fri Sep 10, 2010 3:18 pm

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.

PugMaster

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: Surrogate Key causing incorrect results

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum