I am facing a scenario where I need to implement Slow Changing Dimension ( Time Validity using Start Data and End Date ) for product & customer combination.
A product can be sold to more than one customer. And the price varies over time for each of these product customer combination. I need a module to track the price changes by date (Start Date & End Date).
I have a set product hierarchy and customer hierarchy.
I am unable to understand how to have multiple price values with corresponding start date and end date.
Please advise me how to go about this scenario in Anaplan.
What a great question. Here's one idea you might think about, although this might not fit your use case exactly.
The primary issue is that you need two calendars:
One calendar is the "current perspective". For example, how do the prices look today for a given begin and end time period.
One calendar is for the "historical perspective". For example, how did the prices for a given begin and end date look last week, or last month?
Lastly, there's the time series which allows you to see how the prices have changed over time.
To accomplish this, you will need two calendars. One will use the built-in calendar, the other will be a "fake" calendar that you build using a list. Generally, I always use the built-in calendar for the current perspective and the list for the historical perspective since the historical is not always reliable.
For a retail perspective, historical perspectives are used often with in-transit inventory. Since inventory is a "point in time" snapshot, it's imperative to track how the inventory moves through time, thus the need for an historical perspective.
I can think of two different ways to accomplish this, but it depends on your use case. I am not sure what you described as slowly changing dimensions, the way I am understanding your opportunity is more transactional based than dimensionality. If it is truly slowly changing dimensions, you will have to ask yourself, what is exactly changing? Is it the detail members of the dimension or parents or both? If the answer is the latter two, that is more difficult. If what is changing is the detail members of the hierarchy, then that can be accomplished rather easily by using filters.
Notice in the below, based on the Time, my hierarchy is changing. This is accomplished by using filters and a "Flat List" for the lowest level of the hierarchy. On the right, is the "mapping", basically mapping the detail members to the parents.
On the other hand, from the way I am reading your question, your issue is not exactly this, but more of an ICM issue where based on the Product and Customer intersection, prices are changing. Are all of your prices for a certain product the same for every customer or can the prices vary? When a price changes, is it for every customer or just a select few? The easy answer is for the price to change for all customers at the same time. Based on my understading, I would create the following:
Customer dimension - you likely already have this
Product dimension - you likely already have this
Time dimension - you likely already have this
Price list - this is where it gets tricky, because this will be a numbered list that rolls up to the product dimension (Product is the parent). This list is essentially a placeholder for the price variances.
In the below picture, where I have college football coaches, think of this as the products or SKU's in your solution.
The line items needed then are Start Date, End Date, and Product, assuming the changes are for all customers. If not, then you can add in another line item for the Customer but this will make data entry very tedious because you will have to enter valid combinations for all customers. What is great about this solution is it is not dimenionalized by Time, which will keep it small. Also, let me state, it will be much easier (smaller) if your price changes at the monthly level or the weekly level, basically not the daily level.
In another module, dimensionalized by Time, Price List, I can now pull the above data in using the effective dates of the first module: