We have a design that handles a customer dimension as follows:

Customer Dimension - One row per customer
Geographic Dimension - One row per CITY/STATE/ZIP
Demographic Dimension - One row per AGE/SEX/ETHNICITY/STUDENTIND

Most facts then use CUSTKEY, GEOKEY, DEMOKEY to track who and where.

We also use a key history table to track CUSTKEY, GEOKEY, DEMOKEY
at a specific point-in-time using effective and end dates.

We chose this approach rather than a more traditional type 2 slowly
changing dimension because we have implementations with more than
20 million customers. Although the ETL is a little more complex,
we felt this approach would provide better query performance.

We are now encountering a problem when building AS cubes from this model.
Since we have separated customer demographics and geographics from customer
name, there is not a straight-foward way to build a dimension that drills
from these dimensions to customer name. For example, SEX=>AGEBAND=>NAME.

I'd appreciate any thoughts or suggestions.

Chris Busch
Blueprint Database Architects

Can you not build a view over the various tables that provides this info?
I'm sure this has occured to you but if you tell us why you CAN'T do this it
might help to better understand your environment and therefore the problem.

Regards
Jamie


"Chris Busch" wrote:

Yes. I could build a view, but it would need to include our customer profile
table, which is a snapshot fact table that links the customer dimension with
it's related customer demographics and geographics dimensions.


"Jamie Thomson" wrote: