QlikView is a tool that is enterprise ready and able to step up to those really big BI projects. One of the pitfalls though is to miss the tactical wins you can have with it and get bogged down working towards a management information utopia.
In this next post in my tales from the front line series I want to look at a situation where an ‘under the radar’ quick win was exactly what was needed to progress towards the global enterprise roll out that was the goal. First though, some background.
Becoming What It Was The Alternative To
Back when I first discovered QlikView it was sold as an alternative to dinosaur BI solutions. Products that require an 18 month project before the first report can be delivered. Delivery in weeks and not months was the mantra. Happy days! However, as the product has matured and QlikTech has grown they are now gunning for (and winning) sales in massive clients where long projects are the norm and lucrative consultancy is up for grabs. Project scopes have grown and time-scales have stretched – but QlikView is better than ever at delivering those quick wins. Don’t miss out on these opportunities by only looking at the end game!
QlikView Saves The Day(s)
So, on to a real life scenario. I was on site at a client where a big global roll out was the goal. The tender process was over, the requirements had been written and agreed and I had arrived on site to begin the first phase of the build. A problem had arisen though – the data hadn’t arrived yet. The data that was to be feeding into this app was being collected by sending out template spreadsheets, having them filled in by each office and receiving them back in via email. This was proving to take longer than expected. The person that was copying and pasting the data from each of the received spreadsheets was finding that some of the offices had decided to add columns of ‘helpful’ annotations – or deleted columns that they felt were not applicable. They were not even sure who hadn’t yet returned their spreadsheets. The estimate for getting this task completed was two weeks , full time, by one very dejected looking staff member.
Fortunately, I got talking to the guy doing the copy and paste exercise whilst I was waiting for someone to set me up with access to a database. I told him what I was doing for the company, introduced him to QlikView and suggested I should look at pulling the Excel files in for them. It was then agreed that I should do this.
Given the inconsistent source files (and some information in header rows rather than the columns) a load with a wildcard in the filename was not possible. However I created a load that enumerated around the files and pulled each of them in. Each row was stamped with the source file name and some metadata about the file. The first pass of this took a little over an hour to build.
Once the data was in things did not look pretty. There were supposedly boolean fields with values of yes, no (as expected) and a pile of other values. But, by selecting the other values a list of corrupted filenames was given. By performing a substring on the filename I was able to get a code for each office which could then be linked to a file of details for each office. Now along with the dirty data values and the filename containing the duff values we could see where to return the file to get it fixed.
After a bit more time doing battle with the header values (which were naturally not all in consistent cell positions) all of the data was loaded in and could be written to a CSV file during the load process (using STORE) – ready for import to the database.
A process that was due to take weeks took only a couple of days. The profile of QlikView was raised in the business and the guy who was going to be spending days in copy paste misery was able to learn a bit of QlikView and assist in the next phase of the project.
Seek Out The Low Hanging Fruit
Something I always try to do when arriving on a new client site is to work out where there is low hanging fruit. If QlikView is already in use there will invariably be load times that can be shortened or memory that can be saved. If the client is new to QlikView then there will usually be someone who is spending far too long manipulating spreadsheets, perhaps repetitively on a weekly or monthly basis. Spotting those tasks that can be replaced by an automated QlikView solution can often bring the biggest and certainly quickest return on investment – regardless of what QlikView was actually brought in to do.
This is the fourth in a series of blog posts of tips from the coal face of QlikView consultancy. View the entire series here.