Parent-Child and use of Surrogate keys gives problems...

After reading Marco Russo post http://sqlblog.com/blogs/marco_russo/archive/2007/07/03/surrogate-key-issues-with-analysis-services.aspx i can see that i'm not the only one with a specific business problem, when using surrogate keys. I hope that someone might be able to post a solution. If not, it seems that there is no other option but to use the business key as the attribute key.

Let's say that you have an account dimension build on a table like this

PK_Account, BK_Account, PK_Account_Parent, BK_Account_Parent, Account_Description

1, 30000, Null, Null, Sum Level A

2, 31000, 1, 30000, Account A

3, 31001, 1, 30000, Account B

4, 31002, 1, 30000, Account C

5, 40000, Null, Null, Sum Level B

So when I create a Parent-Child hierachy i SSAS i'll do the following

Assign the Parent

Usage = Parent

KeyColumn = PK_Account_Parent

Assign the Child which has to be Key

Usage = Key

KeyColumn = PK_Account

NameColumn = Account_Description

Now you have the Parent-Child hierachy using surrogate keys but this gives the problem that Marco describes. Furthermore the problem also includes using MDX. Let's say that you have a calculated member

"CALC A" that is defined by

"Account B + Account C"

In MDX this would be:

CREATE MEMBER CURRENTCUBE.[MEASURES].[CALC A]

AS sum(

{[Account].[Account].&[3],

[Account].[Account].&[4]},

[Measures].[Amount]),

FORMAT_STRING = "Standard",

VISIBLE = 1;

Or by using the ColumName

CREATE MEMBER CURRENTCUBE.[MEASURES].[CALC A]

AS sum(

{[Account].[Account].[Account B],

[Account].[Account].[Account C]},

[Measures].[Amount]),

FORMAT_STRING = "Standard",

VISIBLE = 1;

My Question is here if there is a way to relate to the Business Key ? (Maybe by using a ValueColumn ?)

Alternatively it's maybe be best practise NOT to use Surrogate Keys when the business key is relevant in the Business scenario, Reports and MDX calculations.... ?

Hello! One reason for using surrogate keys is for supporting slowly changing dimensions typ two, by keeping versions of a dimension record. It is hard to see any use for that with an account dimension. In your case , use the source keys.

HTH

Thomas Ivarsson