author Alejandro Gomez11. November 2019 09:34:26
(6 min read) Today, most companies are inundated with data and often struggleFind comprehensive, reliable and trustworthy datasetsThey can be trusted and used to make data-driven decisions. Need for this reliable, comprehensive data settimely decision This will help rally support across the organization: Time is too precious to waste. By looking back and analyzing whether the numbers are consistent across departments, the data should be used to look ahead and help create and execute the best strategy across the organization. This reliable qualitative data, on which our decisions are based, is partly so-called«the only version of the truth”.There are ways to manage your data to ensure you can trust it. For example in oursprevious blogwe discussed5 facts about the importance of connecting our data and design processes, within the same platform or through tools connected through integration platforms. Integration platforms allow us to transform data sets before importing them into other sources. This is called extract, transform and load or ETL. But there is another, lesser-known, slightly different but equally useful approach: Extract, Load and Transform, or ELT.Export, load and convert for a true single version
Export, load and convertHere the data conversion part moves to the last step of the process where the conversion takes place after loading on the target system.
TheI am usingThe ELT approach differs from the ETL approach in this respectData loading is faster, since there was no previous transformation work. It goes straight from the source to the destination.
SecondlyYes, it may be necessaryRely on computing powerand data management functionstarget systemPerform any necessary data conversions. This is where Anaplan comes in: rules and data validation data can be easily created and maintained in Anaplan's data center (this is the specific "data mart" or data store that ensures that all of our Anaplan models use the same reports quality benefit from the computational speed and modeling flexibility of Anaplan.
case introduction
Employees and SKU are two typical examples of dimensions that often change over time and exist in different data sources. Problems arise when data sources need to be integrated.
Let's see a practical example of how ELT can be used in Anaplan to create a single version of a real data set of SKU combinations in a transparent, fast and repeatable way.
Fall:
- We need to merge two different data sources to get a list of SKUs.
- These data sources provide a list of SKUs with unique names and codes.
You are welcome:
- missing field
- duplicate registration
- There are different codes for the same SKU.
- Different SKUs with the same code.
Danger:
- Incorrect data aggregation due to duplicate or inaccurate data.
- It is becoming increasingly difficult to maintain a single version of the truth.
Solution required:
- Develop a data validation system that gives us instant insight into data quality once we load it into easy-to-use and easy-to-use reports
- Avoid labor-intensive data health checks and automated and repetitive data cleaning.
- It speeds up our process as we can change data on the site and let the source system continue to work as usual. This enables faster implementation with less disruption to corporate systems and teams. (Implementing a platform like Anaplan for programming doesn't mean you have to change the naming and coding conventions in your data sources to get a truly unique version.)
- However, if a change needs to be made to the source, Anaplan can generate a report of bad data and problem fields, allowing the data owner to fix the problem at the source very quickly and easily.
7 quick and easy steps
The data may not match exactly, so we're looking at how you can provide a set of "validation rules" to easily identify and correct records.
1. Load all row data, as it happens, from any source in the data center. A data center is a data store where we store all the relevant data we need. You can read more about individual data centers here:https://www.anaplan.com/blog/4-reasons-invest-single-data-hub/.
2. Once the data is loadedwe can use logic to scan all records and highlight:
- The name or password field is missing
- Unique Name and Unique Code fields.
- A unique name and password combination
2a. Leer:Specific validation rules can be created in Anaplan, allowing us to validate which records have empty fields:
This logic searches for all records and flag fields with missing values within a tenth of a second. In point 4, we use Anaplan's filtering feature to count and display only problem records.
2 B. Unique name and code.Similar logic applies to searching for unique values in fields as explained earlier (to search for empty cells in the name and password fields). Anaplan can highlight the first occurrence of a particular value in a record and scan the record from top to bottom:
By using Boolean values, we make the model easy for business users to interpret and maintain, and require little or no IT support.
2c. Specify the unique combination of name and password.In this step, specify a unique name and password combination.
By combining this unique name and code combination with the results of the previous rule: unique name and unique code, we can identify records that contain unique SKUs.
Entries that do not meet all three criteria can be easily deletedInvalid entries foundAs with "Diatex Paint Red 2L RP1060" above, it is a SKU with the same name but different codes.
3. Based on these validation rules, possibleOverview of creating aggregate datasetsloaded and how many of them are problematic according to the previous logic.
4. Based on these validation rules, possibledoOverview of total recordingsloaded and how many of them are problematic according to the previous logic.
5. Alternatively, a monitor is available,It lists all problematic records and is ready for manual processing. In the example below, we see records with the same name but different codes. Here we can assign a code to all events with the same name.
6. When you change records manuallyThere are a number of guys at Anaplan who analyze proposed new codes or names to ensure this happensThere are no new conflicts with existing data.When you click the Apply Changes button, we can be sure that not only will all existing records be modified, but no new problems will arise.
7. After making sure the data is clean and consistent again, we canDownload a list of final results to share with other interested parties.In this step, we have a solid foundation to carry out the planning and decision-making process in the Anaplan model.
Within seconds we created a process based on Anaplan's ability to analyze large amounts of data. Report any unexpected values that do not meet the validation rules immediately after each new set is loaded.
It enables simple operations such as changing data in-place or generating summary reports to share with data owners.
All this leads to faster processes, more transparent data and better decision-making, all without the help of IT.
Want to learn more about Olivehorse and Anaplan?
You can waste a lot of time manually integrating data into spreadsheets or manipulating views and tables in your database. Anaplan is a highly customizable tool that allows you to create many more solutions than the examples we used in this blog. It is also compatible with all major data integration platforms.
If you're wondering how to get clean and reliable baseline data to run your planning process or what-if scenarios, feel free toContact us.
At Olivehorse we deliverFree wine tasting, we spend half a day with your group at the venue, introduce youThis way you improve your business processes and increase your ROI.Click hereBook a free Anaplan trial session.
Continue reading:plan