Dimension hierarchy design

Hi,

I have a question regarding the design of a dim hierarchy. the following tables exists in the operational system

customer, account, premise, meter. it is a fixed width M:1 hierarchy. In my DW I like to flatten(dinormalized) these tables and build
a dim_customer hierarchy that will enconpass (account info, premise info and meter info). Is this the right approach?


thanks in advance.

It depends, in your case, I would say no.

The structure needs to be at the lowest level, in this case meter. Does it really make sense to have a table, one row per meter, to contain attributes of those other dimensions?

When you build a fact table, do you not include all those dimension references anyway? If it is a meter level fact, such as a reading or a billing line, would you not also capture the customer and account as dimensions of the fact? It may be reasonable to include premise attributes in the meter dimension, but it is more likely premise is its own dimension and would be referenced from the fact. So, your source may just have the meter and you need to look at this other structure in the operational system to identify the premise, account and customer, but on the DW side, you don't replicate that structure, you use that information to attribute dimensions to the fact.

ok. I see your point. yes it is reading information in the fact table.

wouldn’t it make sense to flatten customer and account? And premise and meter?.
Otherwise, my design will look exactly like the operational system.

DIM_CUSTOMER, DIM_ACCOUNT,DIM_PREMISE,DIM_METER

Thanks

I would not combine customer and account as it just makes for a really big dimension rather than adding any useful information. It also hurts performance on many systems. Premise and meter is most likely ok, but you lose some opportunity to provide aggregate fact tables should you decide to do so in the future. While you can aggregate in a query any way you want, creating an aggregate fact table is based on existing dimensions in the sourced detailed fact table. You could not build a premise based aggregate (if asked to do so) because you do not have a premise dimension, only a meter dimension that contains premise info. Combining them is ok because creating such a fact table really doesn't help much since, at best, is would only cut the size of the fact table in half, which isn't much of a reduction (depending on your DBMS). It is more likely to create account level aggregates and/or customer level aggregates, so you need to keep those dimensions clean and well defined.

If source system does have these entities, it does not mean you cannot have them in dimensional modeling. It is matter of how you should structure the relationship to serve the purpose of better reporting performance and ease of use on the model. What you proposed is a simplistic approach, by just denormaling the customer dimension with attribute hierarchies in one dimension. However, dimensional modeling is more than that.

In many business, customer dimension could be very big, or sometimes in the category of monster dimension (millions), in which case, you should slim down and slow down the dimension, leveraging mini-dimensions and outriggers. If customer account is just an account number and not a multivalued attribute to customer, I would not mind having it in the customer dimension. If it has other attributes, say type of account or other descriptive attributes, I would treat it as outrigger to the customer dimension. Similar thinking applies to premise and meter.

Having FKs to the respective dimension in fact table is one way of reflecting the hierarchy. However it only covers the relationship that have appeared in the fact table. To have a complete picture of the hierarchy, and sometimes you do need that, you either resolve it within dimensions/bridges or have coverage factless fact tables if the changing history needs to be tracked.

With aggregate dimension, there is one distinction between relational and dimensional modeling. In relational thinking, if you need an aggregate dimension, you would remove the repeating groups from the base dimension and replace them with single FK (snowflake). Whereas in dimensional thinking, you create aggregate dimension without having to remove repeating groups from base dimension, unless you are dealing with monster dimensions.

Good Morning,

Thank you both for the thoughtful responses. The account table is used for billing purposes.
The customer one has all the info about the customer who owns the account.
Please see bellow table structure from source system. The meter dimension is the bigest dim
in my schema so far. the premisa dimension has only a few attributes, premise_type being the most
important one from reporting perspective.

CUSTOMER ACCOUNT
Customer_id Account_id
Customer_name Account_status
Customer_type Mailing_address
Customer_address
Customer_city Mailing_city
Customer_zip Mailling_zip_code