When building planning systems, data is always the first consideration.
When we approach a client to discuss their existing data gathering processes we frequently hear the same thing; descriptions of a combination of data dumps to Excel and a complex matrix of pivot tables, VLOOKUP, INDEX or MATCH functions with various information cleansing activities added for good measure. All of this to arrive at a single data source, taking multiple users several hours each quarter, month, or even week, to produce the basis for crucial analysis required by the business.
We have even seen cases where multiple teams within finance are cleansing the same dataset to achieve similar insight. Inefficiencies like these are not only slowing down the planning process, the repetitive, manual nature of the activities is exposing the whole system to significant risk.
What can we do to improve this? Using Planning Analytics, we can automate these activities to improve efficiency and mitigate against the likelihood of errors in the planning, budgeting and forecasting processes.
Data Collection
IBM Planning Analytics includes Turbo Integrator, an ETL tool (extract, transform, load) including straightforward scripting language which can draw data from various sources: comma delimited files, relational database tables via ODBC, Microsoft Analysis Services and SAP among them.
This scripting language enables us to control the collection from source systems and perform data manipulation to provide a consistent set of data at the required frequency.
There are many benefits of using automation over manual processing; a key advantage is the ability to schedule data extractions to occur at any time, overnight or during the day, as required. Combined with the ability to set up multiple scripts across various sources this allows a fresh sample of data to be retrieved from all applicable systems and prepared ahead of the working day. Analysts can then spend their time providing business insight rather than focusing on manual data preparation in a far more valuable deployment of resource.
Data Aggregation
Collecting information from multiple sources will most likely generate varying levels of granularity: ERP or accounts data may be held at a monthly level and sales data may include daily values.
It is possible to generate multiple cubes within Planning Analytics to represent the different levels of data in their base forms but aggregation can also be achieved. Monthly data can be transformed into a daily value by dividing by the total days in the month; working days in the month; or in relative proportionality to the existing daily figures. Conversely, daily data can be aggregated up to the monthly level, simplifying the analysis process.
Data Validation
Extracting and transforming these data sets gives us a solid platform on which to build our planning applications. Ensuring this information is trustworthy is essential to the success of the system and the confidence users will have in it.
With Turbo Integrator scripting we can perform automated tasks to assist with validating the source data. We can set up staging cubes which can assess data line by line and will output error logs wherever the source does not match the target, or we can run stored procedures against relational databases to verify checksums across the data. These scripting capabilities gives us the power to check each piece of data as it is processed to maintain the integrity of the system.
Data is fundamental to your planning system. Seamless collection, aggregation and validation will both improve efficiency and cut the risk of manual processing errors. Let us help you to leverage the possibilities within the Planning Analytics technology to streamline this crucial function of your business.
Spitfire Analytics:
We are a team of finance professionals who are experts in helping companies bring efficiency, speed and agility to their finance teams and planning processes.
As a team we have over 35 years of experience in licencing, designing, implementing and delivering systems to improve the planning, budgeting, forecasting and analysis processes. We do this using IBM Planning Analytics (powered by TM1).
We currently offer a free health check of your planning system – regardless of the software you use.
Should you already have a project in mind, want to learn more about us and our services, or wish to arrange a health check; please contact us on 0161 464 3687 or email [email protected]
