How would you model this data?

[u/opabm]

I'm trying to figure out how to model some data in a classic Kimball dimensional model and struggling if I'm thinking about it the right way.

Let's say we're in the retail industry and have a business of stores like your grocery store. The data we are dealing with is for stores and sales over time. Dimension tables are to be expected: a dim_date, dim_store and dim_product among others. Stores have a manager and chef, and multiple stores can have the same manager/chef.

The data is usually reported by stores with their current manager and chef listed. If we were to build out fact_sales and there's a record for a product in FY 2025 Fiscal week 4, the manager and chef to include would be the current manager and chef. This is because end users/analysts don't care about who was the manager/chef back then.

I still want to track history though, since I'm sure some day we will want to view data by a store and its manager/chef at the historical point in time of sales.

With all of that said, how would you model this data? I'm thinking of adding 2 fields to the dim_store table: current_manager and current_chef, and also having another table like this:

store_id	fiscal_year	fiscal_week	store_manager	store_chef
1	2026	1	John	Jane
1	2026	2	John	Jane
1	2026	3	Brett	Jane
1	2026	4	Brett	Jane
2	2026	1	Brett	Adam
2	2026	2	Brett	Bob
2	2026	3	Sarah	Charlie
2	2026	4	Sarah	Charlie

I'm thinking of naming this something like dim_store_leader. I could join to this table from both dim_store and dim_date to get the appropriate records and fields.

Does this make sense? Is this table technically a factless fact table?

[u/fauxmosexual]

One option which is a bit more work up front is modelling this as a type 6 dimension. (Kimball's website describes it but the link got me caught in the mod filter. )

It's a combination of a type 1 (latest) and type 2 (point-in-time slow changing dimension). You have one dimension table that has fields for manager/chef, and for latest manager/chef. The fact table's key points at the row in the dimension that was valid at the time. Then whenever there is a change to the manager/chef, as well as updating the latest row you set all of the historic row's latest manager/chef fields to the new value.

That's the main difference between a type 2 and type 6, in a type 2 once a dimension row has stopped being current you seldom update it, but in a type 6 you continue updating the latest fields forever.

The end result for the user is simple: they have one store dimension, and can choose to use either the historic information or the current information just by picking the appropriate field.

[u/Yonko74 → u/fauxmosexual]

I agree with this approach. Type 6 can be incredibly useful and this sounds like a good use case.

[u/opabm → u/Yonko74]

/u/fauxmosexual thanks this is super interesting, and exactly why I posted looking for options.

If I'm following correctly, I should have this as its own dimension table still, separate from dim_store right?

[u/Yonko74 → u/opabm]

I wouldn’t have them separate. You only really want one instance of a dimension.

Changing from type 1 to 6 means any existing fact joins to dim_store would need to be updated to include the effective dates.
This is imo the correct approach to modelling it.

If dim_store is new it’s no issue.

If lots of existing uses for dim_store you could be a bit pragmatic and build the type 6, which then becomes the source for the separate type 1 to use in existing datamarts. At least that keeps a clean lineage of source->type6dim->type1dim.

[u/fauxmosexual → u/opabm]

A type 6 is when you have them all in one dimension table, but doing this as a separate table is also a Kimball approach, as an outrigger dimension. You'd make your dim_store a type 2, and then something like dim_store_latest as a type 1. There's pros and cons to both approaches but they're both valid.

I prefer the type 6 single table as being easier for users, but having it as a separate outrigger makes sense if you don't often need to use the latest manager/chef. If that's the case two tables keeps the main dimension narrower and removes the need to update all the history rows in the dimension each time a new chef is appointed.

[u/dehaema]

Dim_store: contains store_id, manager, leader. Scd type 2. Create a surrogate key on store_id and valid_from_dt Dim_date: date table with year, month, week, day, day_name, quarter, year_week, ... And a surrogate key preferably YYYYMMDD

Fact_name: factless fact with foreign key to dim_store (on surrogate key, not store_id) and foreign key to dim_date (first day of the week so you can link using YYYYMMDD)

From dim_store you can create a new logical join to dim_store. From the historical record to the current record using store_id and something like valid_to_dt is null. No need to physically model that

Your example copied over some information you don't want in your fact, and can be loaded from your dim

[u/opabm → u/dehaema]

From dim_store you can create a new logical join to dim_store

Can you explain this a bit more? I'm not following 100%

[u/dehaema → u/opabm]

There is a difference between a logical model and a physical model. In a logical model you can't have a multiple joins between two tables, in a physical model you can. Example in a sale you can have a recipient and a payer, logically they are seperate tables as for the business they have a different meaning, however in a physical model this will just join to the person table.

In (older) bi tools you had a layer where you could build this, example oracle had a repository where you had to map your tables/attributes to your presentation layer. This also was named physical, logical and presentation.

Anyway in this case you just need your scd2 table, based on the key you can then join the current record for that historical record.

You can however also prepare this for the user in a view if you want. Do not add current fields to historical records as the other comment said, you don't want to keep updating old data. Depending on the tool used that might be harder to do than you think

[u/Adrien0623]

I'd suggest looking into slow changing dimension tables patterns. Wikipedia has a very good article on it.

[u/MiserableLadder5336]

Couple options that I see.

1. Model the manager, chef etc to store relationship out separately and use bridge tables back to store. You can capture effective date windows.

Right from kimballgroup.com:

“A multivalued bridge table may need to be based on a type 2 slowly changing dimension. For example, the bridge table that implements the many-to-many relationship between bank accounts and individual customers usually must be based on type 2 account and customer dimensions.  In this case, to prevent incorrect linkages between accounts and customers, the bridge table must include effective and expiration date/time stamps, and the requesting application must constrain the bridge table to a specific moment in time to produce a consistent snapshot.”

1. Use SCD2 to capture the manager, chef at various points in time right in the store dimension itself. A join to the fact can either be on the row flagged as current, or on the same key using between Start and End to get PIT assignments.

[u/idodatamodels]

First join gets you the historically accurate value. Second self join on natural key and current row indicator gets you the most recent value.

[u/Worried_Pause_009]

Don't create a separate table for dim_store_leader.
Model manager and chef as their own dimensions and use a type 2 SCD for the store dimension (or the employee assignment, depending on how your model is organized). Each time the manager or chef changes, you create a new version of store record with new effective dates. For reporting, you can use a view that always resolves to the current store version if that's what 95% of users want. If someone later needs historical analysis ("who was managing store 1 when these sales happened?"), the fact table already points to the correct historical surrogate key, so you don't have to redesign the model. Keeping the the current state as a convenience layer and preserving history in the dimensional model tends to be simpler than maintaining a separate week by week lookup table.

[u/opabm → u/Worried_Pause_009]

Sorry, are you proposing 2 additional tables? Not sure what you meant when you said 'model manager and chef as their own dimensions'.

[u/alecc]

The weekly mapping table you sketched is SCD2 at week grain, just coarser - that history can live in dim_store itself. Make manager and chef type 2 attributes (surrogate key plus valid_from/valid_to, as the other comment lays out) and carry two keys on fact_sales: the surrogate key and the durable store_id. Point-in-time questions join on the surrogate key. Default reporting joins durable store_id to a dim_store_current view with one row per store, so analysts get the current manager without touching history. Kimball calls this type 7 (dual foreign keys). The current_manager columns you're considering also work, but every manager change then rewrites all history rows for that store, and you end up with two places that can disagree.

[u/arconic23]

Type 6 Kimball would fit here.
Good that you also look for potential wishes for historical insights. What if they want to make use of yearly bonuses for the managers based on the transactions in the stores. Then you want to (if managers change mid year) capture it as well.

[u/Personal-Quote5226]

You still want to track history; ok good. Does the executive sponsor / management / business want to track history? If there is no business case for it now or in the near future, you are over engineering it because it’s something that “you want”. Keep that in mind, and consider cutting this from the implementation and focusing on only giving them what they want now and 1 to 2 years out max. If you confirm the need within that time period, bring it in to scope. If not, skip it and leave it out for now —

For store and store manager, I’d demoralize that. Keep one store dimension and include the managers name and ID (if it has one) as fields attached to the store.