1

Hi there we have the concept of having department. each department can either be its own head or it can have a head department. (similar to each person in a organisation is a employee but some employee they have head/supervisor or some employee they act as a supervisior for a certain time that can also change over time if employees are leaving)

so we have a dim_department (SC2)

dim_department dep_key department_id department_name valid_from_date, valid_to_date, currenty_yn
1

so it is an SCD2 which can slowly change over time if the name is changing ect

so let's say the dep1 is now becoming the head of the dep2

later there will be another department "dep3" be open and this will then become the head so the head of dep2 is changing

what would be the best approach to model that

I guess there are several options/solutions to solve that problem

so I could

first option

add the head SK in the dim_department so every time a Head is changing it would lead to an entry in dim_customer in our case a new entry for dep3 would be created and a new entry (slowly changing entry) for dep1 with a closing old current_yn and creating new current_yn enter image description here

second option I thought

create a bridge table or factless fact table enter image description here

or am I totaly wrong?

a third option would just be to put the Head SK in the fact tables

so we would than have

key
date_key
HEAD_dep_key
dep_key

another options a snowflake dimension ? hmmm wondering if that would work with an SCD2 dimension?

so in a nutshell it is on this SCD2 table a relation with itself

what I see as a problem with option 1 is that I have first to handle the HEAD entries because the attributes could change (because of changing name etc) and would generate a new SK and than I would need to lookup that key when handling the sub

what do you think? is this total bullshit or totaly on the wrong path?

Thx

1
0

All you need is your one-to-many bridge table, and it really only needs the head_dep_key field and the dep_key field. That can be joined back to your dim_department table when needed and should be able to answer all questions you'd ask of this data.

7
  • what about putting the keys directly in the fact table? won't that solve the problem as well? our BA they always want to be able to just query the fact sometimes without having to join them with dimensiions and as my departement table is an scd2 dont I have to put data fields in the bdrige table as well - as it relations can change over time as well so department 1 is now linked to department0 for a certain time and later to deparment2
    – walpino
    Feb 2 at 15:59
  • @Walpino If your BA wants to query without joining then that's what Views are for, but you should still normalize your Table structure when possible (for performance and maintainability), not denormalize because the BA wants to be lazy. To your second question, if you need to track history on the changes of department relations then you can use a transactional table (as a bridge table usually isn't transactional, it's just point in time real-time), use a built in auditing feature of whatever RDBMS you're using, or add the appropriate field to make your bridge table transactional.
    – J.D.
    Feb 2 at 17:02
  • thx for the explanations -- so we have the data historyical as well who was head of which departement till when - so would have to add a valid_from valid_to column - I'm wondering if thats the sense of having a transactional birdge table sorry for the confusion
    – walpino
    Feb 2 at 19:44
  • @walpino Yes you can add valid_from and valid_to columns and make your bridge table transactional, you'll just have more overhead managing the table and querying it. You'll need to insert a record every time a new department is assigned to report to another department. And if department A manages B and then no longer managed B and then in the future re-manages B you have to ensure the fields are set appropriately. That is there should be two separate records that don't overlap, and never both have a valid_to value for both in the future, etc.
    – J.D.
    Feb 2 at 20:08
  • 1
    I definitely agree the motive behind denormalization should be performance and not ease of query writing.
    – JeromeFr
    Feb 3 at 14:24
0

It depends which analytics tools you use on top of your data mart/warehouse. Some of them don't handle bridge table that well (option 2).

My personal preference would be to take both options 1 and 3 at the same time. It's a tiny bit more work when loading the data :

Option 3 requires an extra join/lookup to add the pk of the head dep in the fact table at load time. But it will be worth it when querying the data as you won't have to join twice with the bridge table.

Option 1 requires to maintain SCD2 on head_pk attribute in your dimension at load time. But it will be useful with Analytics tool that can handle such parent-child hierarchies. It will also give you an accurate view of a department at any point of time.

Denormalization is a common thing to do with a Data Warehouse. It will have a cost when loading the DWH / DM but it will improve performance when querying which is what the end-users experience.

3
  • well anaylsis tool is tableau
    – walpino
    Feb 4 at 13:27
  • well, analysis tool is tableau, so let's say option1: when loading into the dim_department scd2 head department is changing the name --> new SK (Entry) is created for head dep than I have to create a new entry for the sub-department in the dim_customer table as well so it points to the new SK of the head? not just if the head dep itself is changing I have to maintain the "sub dep" entries is that right ? so I would then need to be careful of the order of loading? or do I then insert first and update later the dep_SK for the new sub-SK?
    – walpino
    Feb 4 at 13:44
  • Yes if you want to have the head-dep SK in your sub-dep row, you would need to create a new entry for the sub every time the head is changing. So it's a little bit more complex to handle. What I've seen in the past to avoid that is to use the business/natural key of the head department instead of its surrogate key (e.g. department_id). But they you need to make sure people using it to join dim_department with itself to find the head department are filtering on the date or you could find duplicate parent entries (same BK but different SK because the name of the head dep changed for instance).
    – JeromeFr
    Feb 4 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.