Surrogate Key causing incorrect results

Go down

Surrogate Key causing incorrect results Empty 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

Back to top Go down

Surrogate Key causing incorrect results Empty 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Surrogate Key causing incorrect results Empty 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

Back to top Go down

Surrogate Key causing incorrect results Empty 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Surrogate Key causing incorrect results Empty 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

Back to top Go down

Surrogate Key causing incorrect results Empty Re: Surrogate Key causing incorrect results

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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