Attributes (non-additive) based on specific combinations of dimension keys -- how best to model?

View previous topic View next topic Go down

Attributes (non-additive) based on specific combinations of dimension keys -- how best to model?

Post  breidbe on Mon Oct 15, 2012 2:05 pm

I've just inherited a data model that hasn't had any oversight for a while. I should be able to figure this question out but my brain keeps circling.... help?

Question:
The business is asking for filtering (non-additive) attributes that tie to various combinations of the dimensions. For example:
a) location, account, ServiceType -- for example: whether a given servicetype is primary for the particular location/account combination.
b) location, vendor, and customer -- for example, a text attribute associated with that particular combination of location/vendor/customer.

I'm struggling with how to represent non-additive attributes specific to combinations of dimension keys.... Help?

[ additional detail, if it helps ]
Existing Dimensions:
Client,
Location,
Vendor,
Account,
ServiceType

Existing Bridge Tables: (I didn't create 'em)
ClientLocation
LocationAccount
AccountVendor

We have a couple fact tables (BillDetail for example) using the keys from all the above dimensions.

breidbe

Posts : 2
Join date : 2012-10-15

View user profile

Back to top Go down

Re: Attributes (non-additive) based on specific combinations of dimension keys -- how best to model?

Post  ngalemmo on Mon Oct 15, 2012 4:40 pm

for B, the text should be its own dimension. The text itself is the natural key. You can use a small hash (32 bit) of the text value as a non-unique index for NK lookup purposes (i.e. don't index the text, index the hash). Or you can use a large hash (SHA-1 or better) as the natural key. Assign surrogate PK's as normal, and attach to the facts. You can later build keyword bridges for quick searches. (This approach will vary depending on the type of platform you are on. This works for traditional databases, you may handle it differently on an MPP platform.)

for A, it depends on wither the primary designation may change and how you want to deal with it historically. The simplest is to have a lookup table that ETL uses with location, account, and primary service type. When you load facts, you use this to determine if the service type is the primary and set a flag (degenerate dimension) in the fact. If the designation changes, the older facts will be tagged with the historical designation.

If you want them to always carry the most current designation, you need to create a dimension with the natural key of location, account, and service type and carry the flag on that dimension. Add the key to this dimension on the facts. Changing the flag in the dimension will affect all fact rows. You can also treat it as a type 2 if you wish.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Attributes (non-additive) based on specific combinations of dimension keys -- how best to model?

Post  breidbe on Mon Oct 15, 2012 6:40 pm

This helps greatly, thanks!

breidbe

Posts : 2
Join date : 2012-10-15

View user profile

Back to top Go down

Re: Attributes (non-additive) based on specific combinations of dimension keys -- how best to model?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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