Data Warehousing Community Forum
October 24, 2019, 01:22:35 am

Pages: [1]   Go Down
Author Topic: hierarchies in dimensions  (Read 119 times)
DW Fresher

Reputation: +0/-0
Offline Offline

Posts: 2

View Profile
« on: August 06, 2019, 10:16:45 pm »

I have a series of dimensions and all are related hierarchically, related in the source system that is.  what is the best way to model this?  for example, i have these dimensions

sales agent dim
store dim
client dim
master client dim

these dimensions are all related: sales agent belongs to store, store to client, client to master client

some facts connect at the agent level, some at the store level. 

my reporting tool uses hierarchies to allow user to aggregate at various levels.  so one could roll up sales at the store level, client level, or master client level.   the tool likes the hierarchy data to come from a single table.   the table would include agent, store, client, and master client.

so my first thought is to just put all the data in the agent dimension.  connect agent dim to my facts and expose all the levels of the hierarchy from the agent dim.   

but there are also some facts that connect at the store level, so i would also have a store dim that includes the hierarchy above it.  that would be store, client, master client.  so store dim has one level less than agent dim.

now am thinking that there would be a bunch of replicated data as agent will contain store and above, store dim will contain client and above. 

all of this i am ok with, but some reporting tools only like to source an attribute from one place.  for example, the client name will exist in the agent dim as well as the store dim.  so when someone drags the client name to the reporting pallet, the tool has to know where to get it from.  for us sql writers, its pretty easy.  not so easy for a reporting tool.

so my question is whether to keep the whole hierarchy in the lowest level dimension, or simply join dimensions together.  ie have fk of one dim in the other.  i dont like this idea, but is certainly cleaner.

also, all of these dimensions are type 2.  so one added complexity is if we store the whole hierarchy in the agent dimension, each time the higher dimensions get modified, then every child dimension record will have to change.   ie if we change the master client description, then every agent record that contains that master client will get updated.  new record created for every agent.

Pages: [1]   Go Up
Jump to: