Imperial UK Implementation - Week 2


The historical data load process was more complex than originally envisioned.

This was principally because of the sheer volume of entries to be gathered, the format in which they were held versus the format that Tagetik required for upload purposes. 


In our case we were holding plan, actual, financial and statistical data in 4 separate fields which, to cut a long story short, would need to be combined, but not merged.  What made life more difficult was the fact that our statistical codes did not necessarily follow the normal pattern as compared to our financial codes. Furthermore having a year end of June meant that a conversion of months to financial periods would need to take place. With 900000 entries to be categorised normal techniques for manipulating data had to give way to more advanced methods including converting the data to SQL 2012. Once the data for all periods going back to 2007 were held in SQL Server we created a view of the data restricting it to financial periods 2013 and beyond. Finally we had the data we needed but it wasn’t in the correct format and this was where one excels hidden gems came into play in the form of Power Query.
Power Query
Power query is a free addin for excel and is used to extract, transform and load data from multiple sources be it the web, excel, SQL server and so on. It isn’t restricted to the 1m rows currently available in excel and is particularly powerful because it has been constructed in a way that forces as much of the hard lifting to take place in the data source.  In other words if you’re working off SQL server, as I was, you could leverage this computing power. In this case Power Query was invaluable for taking database data, reordering columns, filtering out data, complex pivoting, concatenating fields and manipulating it in a highly auditable fashion. I can’t recommend this tool highly enough to anyone working with volumes of data in excess of 100 lines and the learning curve is quite swift.
This guy does a great job of explaining how to use the tool.
There were a number of false starts when bulk uploading data, but these were mainly in respect of operator error regarding field names (again an easy fix in power query) and excel named ranges and later in the day the techniques were in place to load all history for plan and actual financials. The bulk upload process was interesting in that it didn’t take a lot longer to upload 1 year of data versus 1 month.


Follow me along this journay...


Performance management processes

Share this post!