In Reporting and Analytics dimensions are seldom expected to change as they define the characteristics of a report and are used to segment the facts data. The changes to these dimensions is something that is not expected in the normal course of a business and their change can result in unanticipated outcomes in reporting and analytics.
However, there are instances where a dimension can get changed due to unavoidable circumstances and this can lead to unexpected results reporting and analytics. The changes to these dimensions are not very frequent like fact data and slow changes can occur in them over period of time they, hence they are referred to as Slowly Changing Dimensions
For example – In Sales Reporting the Revenue is grouped by Opportunity Owner’s Manager. However, there can be a change in the Sales Person’s manager over a period of time and this can lead to a change in the Owner Manager’s revenue realization.
In the above example, On 15th of December Dave was reporting to Michael and on 20th of the month Dave moved to John’s team. What this does is that it moves the revenue Dave generated under Michael on 15th of December to John and reduces Michael’s revenue by $200,000 when the same data is viewed on 20th of December as the reporting has now changed. This gives an inaccurate picture of the revenue earned by a manager on 15th of December!
Historically slowly changing dimensions have been categorized into the following types and different approaches have been considered to address the problem of changing dimensions:
Type 0 – These types of dimensions are fixed and never change.
Type 1 – In this type of dimension no history is maintained and its simply overwritten with the new value.
Type 2 – In this approach a new record is created for every dimension change and the current one marked with an Active Flag.
Type 3 – In this approach a new column is added on the same record to store the previous value.
Type 4 – In this approach the history is maintained in a separate history table with the dimension value and date.
Type 6 – This approach is a combination of 1,2 and 3 where new records are added in the same table with their validity dates, current and historical values. and active flag
The problem of the 'not so' slowly changing dimension
In the current dynamic world of business there are frequent changes to a number of dimensions that used to be fixed or change slowly earlier and this has made it very difficult to get a view of how products or people are performing at any given time.
For example, Sales team members are frequently moving across departments and hence it becomes very difficult to track their and their superior’s revenue contributions in each area.
Also, there are certain dimensions which can change more frequently like an Opportunity Stage and these are more suited for current state reporting. However, there can be situations where there is a need to analyze the movements of opportunities over a period of time to come up with pipeline trend and this may require tracking of the changing dimensions with time.
There is no way in the traditional databases and reporting systems to get a point in time view of the data and hence getting a view of the system data on a given day is becoming a challenge that directly affects the performance monitoring, revenue attribution and compensation of teams.
Snapshots as a solution and Salesforce Snapshot Options
As we saw earlier that the traditional methods of handling slowly changing dimensions are not very apt to address the current dynamic needs. These problems however can be addressed at different levels by creating regular snapshots of data which act as history and can be combined over a period of time to address the challenges posed by the dynamic dimensions.
In Salesforce snapshots can be created at different frequencies based on the business requirements and can be utilized to get a point in time view of the data as well as well to carry out trend analysis.
Let’s have a look at the options that can be used to create snapshots and address the problems using native Salesforce capabilities and some proven market leading tools:
Option 1 – Salesforce Report Snapshots
Salesforce provides a native reporting snapshot feature that can help users to take a snapshot of a report at regular intervals. The report snapshots are stored in a custom object and this can be used to build a history of the changing dimensions over a period of time. While the Salesforce reporting snapshot offers a simple and out of the box solution to the slowly changing dimensions problem, it does require certain considerations to be taken into account. Following are some of the advantages of Salesforce reporting snapshot with some key considerations:
Benefits of Report Snapshots:
- Addresses dynamic dimension changes natively
- Easy access to historical data using custom object
- Easy to build trend analysis and summary level dimensional changes reports
Key Considerations:
- Only 2000 records can be included in a report snapshot
- A logical aggregation is recommended in the reports being snapshot
- The snapshots can consume sizeable Salesforce storage over a period of time
Option 2 – Tableau CRM Snapshots with data flows
Tableau CRM takes the snapshot capabilities to the next level with prebuilt snapshot template apps. The snapshot template apps can be configured to get the required snapshot for trend analysis and capturing history of specific dimensions.
Tableau CRM as a platform can also be extended to create custom snapshots using dataflows that store data in a snapshot dataset. The snapshot data can be easily used in lenses and dashboards to provide the detailed point in time analysis required for dynamic dimension changes. Tableau CRM as an advanced snapshot analytics options comes with the following advantages and considerations of its own:
Benefits of Tableau CRM Snapshots:
- With no limitations on the number of rows in a snapshot, Tableau CRM can serve as strategic snapshot store for summary as well as detailed data.
- Provides ability to detailed point in time reporting at a record level and not just summary level.
- Provides a quick start template app to get the snapshots immediately available through configurations.
- The retention policy for snapshot data can be easily configured with predefined options.
- Can be easily embedded in Salesforce pages for delivering snapshot analytics to Salesforce users
Key Considerations:
- Custom snapshot development requires development of tailored dataflows
- A carefully planned snapshot data retention policy needs to be in place while designing snapshots. Snapshot datasets can consume sizeable amounts of Tableau CRM storage depending upon the objects and frequency of snapshots.
- The snapshots can consume sizeable Salesforce storage over a period of time
- It is not easy to export snapshot data to external source like a strategic data warehouse
Option 3 – External Snapshots using cloud data warehouse
For designing a scalable and strategic snapshot solution, snapshots stored externally to Salesforce platform can be considered. The data can be stored in a scalable data warehouse like Amazon RedShift or Snowflake that can then be connected to analytics tools like Tableau or Tableau CRM for providing a comprehensive snapshot analytics solution.
Using external snapshots helps overcome the issues with Salesforce report snapshots and even Tableau CRM to deliver a scalable and performant point in time analytics solution. The external snapshot data can also be easily integrated with external apps and a larger data fabric for end-to-end organizational analytics.
Conclusion
While owning a time machine to go back in time may still be a distant reality, the snapshot options above can still take you back in time when it comes to your Salesforce data. Choosing the right snapshot solution is a critical strategic decision that needs to be arrived at after careful considerations. If you require any help in defining the Salesforce snapshot strategy for your organization or picking up the best snapshot option based on your requirements then contact us on our website or visit us on Linkedin.
