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

Parameters to DTS

Hello,
Is there a way to pass parameters to the DTS package?
For example, we are using DTS to archive data from database into a flat file
every month. Is there a way to pass the name of the file and the
dateFrom/dateTo values to the DTS?
Thanks in advance.
ArsenA million ways
What version SQL Server
2000 look at the /A parameter to DTSRUN
2000 look at a dynamic properties task
7 I like to DataPump to Global Variables and then use the object model
Use the object model to execute the package and pass values to Global
Variables which sets properties of the object model.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Arsen Vladimirskiy" <arsen@.emergency24.com> wrote in message
news:OST3tdlZDHA.2620@.TK2MSFTNGP09.phx.gbl...
> Hello,
> Is there a way to pass parameters to the DTS package?
> For example, we are using DTS to archive data from database into a flat
file
> every month. Is there a way to pass the name of the file and the
> dateFrom/dateTo values to the DTS?
> Thanks in advance.
> Arsen
>