5

I have heard textbook definitions of how to design a star schema regarding what goes in the fact table and what goes in the dimension tables, such as:

The fact table should contain core information about an object and dimensions should contain information about the facts

(paraphrased)

However, practically in business, I have seen a star schema designed where the fact table contains a surrogate key, a business key, and all single-valued fields of an object, and each dimension stores all the multi-valued fields of an object (hence the word dimension). For example, a person may be the object represented in a fact table. A person has one name, one age, etc., which all make viable facts in a fact table. A person may own multiple cars, each with their own attributes, which would represent a person's car dimension, stored as a dimension table with several columns to describe each car's attribute. In this example, this dimension table also includes a foreign key representing the business key of the corresponding row from the fact table.

So, if we can agree that this may be a suitable design, the problem that I am trying to overcome is how to do SCD type 2 (historical) on a multivalued dimension table. For my fact table full of single facts, it is obvious. I include two extra columns, an effective date and expiration date, and I use the business key to link common records where the most recent record has a NULL expiration date, and all other historical records for the same business key have both an effective and expiration date indicating at what point in time they were the most current record.

How do I use this same concept on a dimension which represents a multivalued list? I essentially would like the same concept where I can (1) identify the current list (in this example, the cars a person owns) and (2) identify what the list was at any given moment in history. Can I just put an effective and expiration date on each dimension value? How then do I differentiate between values added after a certain time? Or deleted values?

But, if we do not agree on this design approach, please tell me what industry standard is so I can do this correctly.

Improve this question

1 Answer 1

Reset to default
3

Usually, dimension tables contain a single valid time (start and end date) for all fields and SCD2 would apply to the complete record. It is good practice to use an non-null end value ahead in time to mark currently valid records as this simplifies queries. An end date in the past would signify deletion or any other semantic you define (like person left country or is not employed anymore). Also add surrogate keys to your dimension tables to uniquely identify records.

Fact tables usually contain "measures" like sales or cost or signify events like a placed call or durations of these calls. One would usually use aggregates on these columns in reports.

A star-schema is a way to model a sparsely populated "cube", where each axis of the coordinate system is given by one of the dimension tables. "Slice and dice" operations and "drill up / drill down" operations in reports translate nicely into SQL using this model.

In your cars and people example, I would use two dimension tables, one for cars and one for people, each historized (according to SCD2), and a factless fact table comprising foreign keys to the dimension tables, referencing the respective identifier (entity identifier), and valid time columns (SCD2). You would not add a record according to SCD2 rules in the fact table, if one of the dimension tables changes, in this design.

This way you can model changes in each entity, like name changes in people, color changes in cars and the relationship between cars and people, for example ownership. Each table would use non-overlapping valid times (start and end values) for each business key, recording the history of these entities independently. The fact table would in this model basically be a m:n linkage table, for which a separate history of valid times is kept.

You would identify the current and past lists by using x between start and end on each table for as-of now (or past) queries (answers your (1) and (2) - ignoring if the intervall is right open or left open).

Summary statistics like how many cars do we have in some city with full history (assuming city is part of the people dimension table), can now be answered using temporal joins and "sequenced" queries, which are sometimes also called "coalesce" queries, see Snodgrass, chapter 6.3ff

Improve this answer
7
  • In an SCD2 for a usual flat table (not necessarily a fact table) allows me to historically identify (per business key) what a record set looked like given a specific point in time. This is done by specifying WHERE dateOfInterest BETWEEN effectiveDate AND expirationDate in my query. I am trying to go for the same effect with a multivalued field (my dimension in this example). If I only have a single valid time, and over time I add values to this multivalue dimension without removing any, how can I see that there used to be only 4 values and that now there are 7, for example?
    – Michael Plautz
    Feb 22, 2017 at 4:50
  • You don't have a "single" valid time. Whenever there's a change, start and end date will be adjusted accordingly. That means in your example the three additional records will have a different start time than the others, by which you can tell them apart.
    – Grimaldi
    Feb 22, 2017 at 6:34
  • If in your example one person has 4 cars and you later on discover that this was wrong and he had 7 cars, you would need to use bitemporal tables, using valid time and transaction time. Valid time would model when the record holds (time Intervall in which it is true), transaction time would model when it was entered or changed in the system. The first would be visible to users of the system, the second value would be housekeeping and not usually exposed to users. Hope this clarifies it and does not confuse.
    – Grimaldi
    Feb 22, 2017 at 6:39
  • I have done a bit of research based on what you said, and I have come across a solution that addresses most of your points. I was thinking about it totally wrong. Your comparison of a star schema to a sparsely populated data "cube" was actually very helpful for envisioning what goes where. I have completely redesigned it where I either have a factless table or only the "measures" as facts, and SKs for each dimension in the fact table, where each dimension is either single or multi valued. However, I put my SCD2 on the fact table rather than the dimension table. Is that wrong?
    – Michael Plautz
    Feb 22, 2017 at 17:40
  • (To follow the previous comment,) My fact table has columns PersonSK, CarSK, EffectiveDate, ExpirationDate. At one point in time, A person has 4 cars. In the Car Dimension table, there are 4 rows describing each cars attributes, all with a common SK. Later, this person acquires 3 more cars. In my new model, the fact row would become invalidated with an expiry date and there would be a new fact row. In this row, the PersonSK would be the same as the previous since the person hasn't changed, but there would be a new CarSK (with 7 new car records in the car dimension each sharing the same new SK)
    – Michael Plautz
    Feb 22, 2017 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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