Our Sales Hieratchy looks like this:

-Sales Group
--Account Manager
---Customer

Sales Group is a parent-child. However, the grain of the fact table is
Customer which is the key of the dimension.

Do I need two separate dimensions? One for Sales Group and the other for
Account Manager and Customer? Or can I use all three in the same dimension
(preferred)?

Thanks in advance.

IMHO you couldn't mix it in a single dimension. you should create 2
dimensions.

Vladimir Chtepa

"Jesse O" <jesperzz (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:u2gSytRIGHA.964 (AT) tk2msftngp13 (DOT) phx.gbl...

Logically I would have though you would have split Sales Group/Account
Manager into one hierarchy and Customer into another. But the logical
data structures are not always the only consideration.

I actually built a dimension similar to the one you have proposed a
couple of years ago in an AS2k database. We had 345,000 customers, so
there was no easy way to browse customers without superimposing some
sort of navigation hierarchy over the top. These cubes were for monthly
sales reporting so the sales force hierarchy was an obvious choice.

The performance of such a large p-c dimension (10,000 sales people,
345,000 customers) in AS2k was REALLY bad. I ended up writing SQL code
to flatten the p-c hierarchy into a levelled hierarchy and then wrote
some DSO code to synchronize the dimension in AS.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <u2gSytRIGHA.964 (AT) tk2msftngp13 (DOT) phx.gbl>, jesperzz (AT) hotmail (DOT) com
says...