1

I have customer dimension table and the location of customer can change.

The customerid filters the sales fact table.

I have 2 options:

  1. Slowly changing dimension type 2 to hold 1 new record for each customer's location changes

Or

  1. Store the location at the time of data load into the sales fact table.

Both ways allow me to see sales by location (although it's a customer location, the etl will place it on fact table).

The later option saves me from implementing SCD on dim table.

What are factors to decide which of the 2 approaches is suitable?

variable
  • 8,262
  • 9
  • 95
  • 215
  • 1
    If you store this in the fact table, You only know where the customer is located _when they buy something_. If stored in a SCD2 dimension, you can find out where the customer is located for any date. When stored in the dimension you can more easily answer questions like "which locations bought something last year but didn't buy something this year". – Nick.Mc Jul 12 '23 at 00:05

3 Answers3

2

If you want to query other facts by customer location or indeed query the customers by location, you will need it in the customer dimension. If you, in all other situations, don’t ever care what a customers location used to be, you could avoid modelling the customer as an SCD Type 2, and if you care about customer location at the time of a sale, store it on the fact. You can also do both…

Most of the time you’d have other facts in play so in the end making a SCD customer dimension would be the best route.

Rich
  • 2,207
  • 1
  • 23
  • 27
0

How you model location depends on what it relates to. If it is an attribute of a sale then it belongs as its own dim related to the sale. If it is an attribute of a customer (such as their home address) then it belongs in the customer dim. If the location is an attribute of both a sale and a customer then it belongs in both

NickW
  • 8,430
  • 2
  • 6
  • 19
  • It is an attribute of customer but what is the problem if I put it in sales table given that my end goal is to find all sales by customer's location? Putting it in fact table saves me from having to implement SCD on dim table. – variable Mar 24 '22 at 12:40
  • So put it in the fact table then – NickW Mar 24 '22 at 13:53
0

Your fact table should contain things that we measure, count, total. Your dimensions should be descriptive elements that allow users to slice their data along an axis - basically answer the "by" part of their request

I want to see total sales by year and month across this customer based regional hierarchy

Don't take my word for it, grab a data warehousing book or go read the freely available information from the Kimball Group

Storing the customer data on the fact is a bad idea regardless of your database engine. To satisfy a query like the above, the storage engine needs to read in the entirety of your fact table and the supporting dimensions. It could read (Date, RegionId, CustomerId, SalesAmount) which likely costs something like 16 bytes per row times however many rows you have. Or, it can read (Date, RegionId, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode, SalesAmount) at a cost of what, 70 bytes per row? That's an inflation to

  • store your data (disk is cheap but that's not the point)
  • read your data (basic physics, the more data you wrote to disk, the longer it takes to read it back out)
  • less available memory for other queries (you're in a multi-user/query environment, when you hog resources, there's less for others)
  • write data (ETL processing is going to take longer because you have to write more pages to disk than you should have)
  • inability to optimize (What if the business just wants to see "Total Sales by Year and Month" - no customer hierarchy. The database engine will still have to read all the pages with all that useless customer data just to get at the things the user actually wanted)

Finally, the most important takeaway from the Data Warehouse Toolkit is on like page 1. The biggest reason that Data Warehouse projects fails is that IT drives the requirements and it sounds like you're thinking of doing that to avoid creating a SCD type 2 dimension. If the business problem you're attempting to solve is that they need to be able to see sales data associated to the customer data at the point of time it happened, you have a Type 2 customer dimension.

Yes, technologies like Columnstore Compression can reduce the amount of storage required but it's not free because now you're adding workload to the cpu. Maybe you have it, maybe you don't. Or, you model it correctly and then do the compression as well and you still come out ahead in a proper dimensional model.

billinkc
  • 59,250
  • 9
  • 102
  • 159