Data Quality Orchestration : a simple example with ADO & Workflow - Part 2/2
In this article, you'll find a simple but hopefully inspiring illustration of the power of Anaplan Data Orchestrator (ADO) combined with Workflow.
This is a 2 parts article.
The Part 1 focuses on the Workflow and the steps to achieve Data Quality tasks.
This Part 2 is a deep dive on some details.
Above is the final Object Map generated by ADO for my example. But let's have a step back and start with…
Explore the Data
Snowflake's Trial sample Regions/Nations hierarchy (see here for more on ADO Snowflake Connector) has some differences with the Region/Country used into SYS08 Employee Details.
Regions are using different names.
Countries are sometimes using different names or doesn't exist in Snowflake's samples.
Explore the New Model
The Geo hierarchy will be provided by Snowflake and the Department/Employee hierarchy will come from the SYS08 Employee Details.
The Data Mapping requires the M1 & M2 Lists to hold the Regions/Countries with no match in the Snowflake's hierarchy. The values to map. It is the subtraction of Snowflake's values from SYS08's values. We will see how to do that with ADO here after.
We are now ready to build the Modules our UX Page will use to manually do the Data Mapping.
Finally, the goal of this work is to populate the SYS01 Employee Details with a translated version of the initial SYS08 Employee Details.
Explore the ADO Objects
A lot of the magic happens in ADO.
Let's start to explore the data used to populate the Department/Employee hierarchy.
Using SYS08 as the Data source, a Transformation View is used to build the list of Department. Note the Remove duplicates, the Root Total Company value for the parent column and the HASH() function used in formula of the code's column.
The Employee Transformation View is a JOIN LOOKUP of SYS08 and the newly created Department Transformation View.
Let's now quickly explore the Region/Country hierarchy.
A similar approach is used for both entities: a Transformation View is created from the Data sources to add a new code column which is concatenating, using the & operator, the snowflake's ID with a REGION_ or COUNTRY_ string.
You can note the use of the CAST() function to transform a number into a string.
The following is presenting the objects used to find the Regions and Countries from SYS08 Employee Details with no corresponding value in the Snowflake's hierarchy
Step 1 - is to transform the Regions & Countries data to create an upper version of their names using the UPPER() function. It will be useful to do a LOOKUP with Snowflake's values.
Step 2 - is to create a Transform View from the previous one to create the lists of Anaplan SYS08 Region/Country values.
Step 3 - is to keep only the values with a NULL value as the result of the JOIN with the SnowRegions/Nations Data sources.
The Final Employee Details is built using a multi- JOIN with Snowflake's data and the MAP01 and MAP02 data mapping modules from the new Model
At this stage, we have all the data we need to calculate the final values for Regions and Countries which will come from Anaplan SYS08 or Snowflake or the manual Data Mapping.
Conclusion
With the Part 1 and the Part 2 of this article, I hope you can now better deeply understand how much powerful is the combination of these two Anaplan capabilities. With ADO and Workflow you can dramatically transform the Planners' Data experience based on the simplicity and the robustness of these powerful technologies.
Feel free to visit the Anaplan Data Orchestrator and Workflow documentation.
Be trained in Academy with the ADO Training and the Workflow Training.