Attributes (non-additive) based on specific combinations of dimension keys -- how best to model?
2 posters
Page 1 of 1
Attributes (non-additive) based on specific combinations of dimension keys -- how best to model?
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.
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
Re: Attributes (non-additive) based on specific combinations of dimension keys -- how best to model?
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.
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.
Re: Attributes (non-additive) based on specific combinations of dimension keys -- how best to model?
This helps greatly, thanks!
breidbe- Posts : 2
Join date : 2012-10-15
Similar topics
» How to Model Store-specific Product attributes
» Model with Attributes Based on Two Separate Dimensions
» Case-specific extended attributes for employee dimension
» Calculated dimension attributes based on fact
» Data model for Reporting needs - Event based or fact based
» Model with Attributes Based on Two Separate Dimensions
» Case-specific extended attributes for employee dimension
» Calculated dimension attributes based on fact
» Data model for Reporting needs - Event based or fact based
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum