Implement Slow Changing Dimensions in Anaplan

arunabhab

Hello 

 

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.

 

Thanks in advance !

 

 

JaredDolich

@arunabhab 

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.

Hope this helps!

 

rob_marshall

@arunabhab 

 

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:

Applicable?: ITEM(Time) >= 'CRD01: Rep Assignment'.Start Month AND ITEM(Time) <= 'CRD01: Rep Assignment'.End Month

 

Rep or in your case, SKU: IF Applicable? THEN 'CRD01: Rep Assignment'.Rep Name ELSE BLANK

 

 

 

Now, you will have the prices (assuming they don't overlap a the time period level) rolling up to the SKU.

 

Hope this helps,

 

Rob

ChrisAHeathcote

@arunabhab 

I would create a third list where I combine my customer and product lists along with a start and end date property. 

This will enable you to create a sufficient number of valid data points to map all the relevant information to a price point over any valid customer, product, start and end date combination.  

 

To set this up;

1. Create a simple list containing items labelled 1 to 10.
2. Create a new module using this new list. 
3. Add line items to this new module for each dimension required; Customer, Product, Start date and End date.
4. Add line items to calculate the code for customer and product.
5. Add a line item to be used as the combination list code and concatenate the customer code, product code, start data and end date to create a unique ID/Code for each combination of dimensions.
6. Pivot the view and place all items in rows.   
7. Save this as a module view.
8. Create the new combination list and set this to a number list.
9. Create a text formatted property and set the display name property in general lists to this new property.
10. Use the saved view to import into the list mapping the product name to the text property and the combination code to code.
11. Create a new system module dimensioned by this new combined list.
12. Create list formatted line items for the customer and product along with date or time period formatted line items for the start and end dates.
13. Using the saved view import the data from the template module into the system module mapping the combination code to the new list and the properties into their respective line items. 
14. Combine these actions into a process.
15. Create an additional line item in the system module to hold the price relevant to each combination of customer, product, start and end date. 

Use this systems module to map this data into any downstream calculation.