Leverage Data Export Service to Visualize Data Change Log

[William_Yu]

One frequently heard requirement from SAC Planning customer is how to create reports and dashboards on data change logs when a planning model has data audit enabled. Today, SAC provides you a flat list of data change log which is NOT easy to consume and lack of security control if planner is only allowed see the change history he is entitled to.

In this blog, I will introduce you a way to achieve this by leveraging the Data Export Service and OData Import Connection without external tool or platform.  You may already learned how to replicate data between SAC tenants via Amanda’s blog, we can use the same methodology to load audit data in the same SAC tenant.

Prerequisite:

• Your SAC tenant is a Cloud Foundry Tenant deployed either in AWS, Azure, Ali Cloud or Google Cloud.

Steps:

1. Setup OAuth Client and OData Connection

Please follow the exact step 1, 2, 3 and 4 of Amanda’s blog on how to configure the OAuth Client and OData Connection in your SAC tenant.

The only difference is, the OData Connection is to be created in the SAME tenant and pointing to itself.

2. Create an Audit model with new model type

a. Go Modeler -> Create New Model -> Start with Data -> Select Data Source ‘OData Services’

b. Select the OData connection you created in step 1, in my case with name ‘SELF-SALES_PLAN’ -> Next

c. In new query screen select ‘AuditData’ -> Next

d. Select all fields of ‘AuditData’, and it’s optional to set a filter, for example on version -> Next

e. Click Create in the next screen.

f. Now you will see a model with structure like this, the dimensions in orange box are same from your planning model, while dimensions and measures in green box are for data audit information.

You of course can save your audit model directly like this, but it won’t allow you to leverage your existing master data, hierarchy and data access control maintained in your public dimensions(if you have). 

Instead, I would recommend to create a new model without data by using public dimensions as much as possible. Just keep this screen untouched(as a reference of audit data structure) and create new browser tab.

g. In the new browser tab go Modeler-> Create New Model -> Start with an empty model

h. Create new dimension by adding Existing Dimension Table(public dimension), you can also create private dimensions if needed.

i. Add all dimensions same as your planning model, in my case as below:

j. Now by referring to audit structure you see in step f, create all audit relevant measures and dimensions as below.  Please note that for dimension AUDIT_USER  choose ‘Don’t add a dimension table’ and data type ‘Text’, for dimension AUDIT_TIME choose ‘Don’t add a dimension table’ and data type ‘Timestamp’.

k. Audit Action is stored as ‘AUDIT_FLAG’ for each base measure of your planning model with integer(1 = Delete, 2 = New, 3 = Update). To simplify the reporting on audit changes later on, I created an audit action field for each measure with data type ‘Text’.  This is my final Audit model:

l. Save the model.

3. Create an import job and load audit data

a. In your audit model, go Data Management -> Create Import Job -> Select Data Source ‘OData Servicres’

b. Repeat step 2 sub step b, c, d and e. 

c. Click ‘Set Up Import’

d. Now click ‘Toggle Custom Expression Editor’ , create a new column with expression to convert integer of action into readable text. Do the same for other base measures if you have. 

[Action_Lcvalue] = if([AUDIT_FLAG_LCVALUE] == 1, "Delete", if([AUDIT_FLAG_LCVALUE] == 2, "New", if([AUDIT_FLAG_LCVALUE] == 3, "Update", "#")))

e. In the ‘Map to Facts’ UI, most of the dimensions and measures should already be mapped, just map the additional audit action fields you created in previous steps.

f. Run through the steps to the end and run import. Now you should have audit data imported.     

4. Use the audit data in a story

Just create a story on this audit model with all SAC capabilities as you want.

Quick Summary:

• With Data Export Service and OData Import Connection, I can quickly setup a SAC acquired model and fill it with Data Change Log from my planning model.
• Any type of report and dashboard can be created on this audit model.
• In my case, I have setup data access control on dimension Region. With the reuse of this public dimension, same data access control can be applied to my audit model.
• This approach not only offers an option for reporting on data change log, but also offers an option to backup data change log in the same tenant.