Shulou(Shulou.com)06/01 Report--

Last time we understood the dimension table, what is a slowly changing dimension? for example, we have a scenario in which customer A belongs to Department X in April and to Department Y in May. If customer A's sales need to reflect the changing relationship of the department. At this point, you need to use the slow change dimension. The figure below is a slow change to the customer table.

The above design is to generate a piece of data for customers every month, but if we have 1 million customers, one per month, there will be 12 million a year later, which will result in a large amount of data, which will seriously affect performance when associated. Then we need to use the design scheme of slowly changing dimension, using the surrogate key scheme to achieve the minimum amount of data. As shown below:

We frame the current version of each customer by framing the start date and end date of the customer table, and then bring the customer's agent key with the landing data, so that if the customer dimension information in the result table is incomplete, we can directly use the agent key to associate the customer dimension table to bring out the required dimensions. if there is no agent key to do the association at this time, using the customer code to do the association, there will be data Cartesian product As shown in the figure below, when we need to add the customer's "home department name" to the result table, when we have the "agent key" information, we directly press the agent key to do the association condition to bring out the "home department name" information in the customer dimension table. if we associate by "customer code", it will cause the data to double, see the red data record, from 100 to 200 in April and 200 to 400 in May. Otherwise, the monthly data will need to be recalculated to achieve the purpose of adding "attribution department name" information.

To generate SQL code for slowly changing dimensions, see:

Https://github.com/blt328/dw_blt

For more technical articles, please follow the official account (long press the back point to identify the QR code in the figure):

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.