Planning Your ETL in Qlik

Qlik has a powerful script engine which allows you to bring information in from a variety of sources. It’s included within Qlik and because you load all the data into memory, once saved it is effectively a portable business intelligence (BI) solution. Of course in the real world the file would remain on a server where all the decision makers can access the information held within.

Naturally, one would assume each dashboard would bring in the data needed from its sources and this can be the case. However, when you’re planning a larger scale strategic implementation this becomes less efficient.

Qlik has a simple yet effective option to allow you to develop these large suites of BI dashboards. As mentioned, Qlik stores the information into memory and also has the ability to export data tables in a special format which is already optimised in the standard Qlik file format (QVD). This optimisation reduces the data size without the need for compression. A Qlik dashboard file can now be seen as something different; it becomes a script which moves a set of data from one location to another and perhaps transforming it as well.

For large deployments we would recommend creating three layers (three Qlik dashboard files):

  1. Extract
  2. Transform
  3. Dashboard Load

The Extract layer would take information from the source systems and place it straight into a QVD file. Here, the latest information from the source system can be added to the data retrieved previously – also known as incremental loading. This method is far more efficient than taking the whole history each time you re-load. It’s important at the extract stage that no data is changed, combined or additional fields added. This way you can easily test that your saved data matches the source file.

The next step is Transformation.  Here we read the previously saved data and get it ready for consumption for a dashboard. As before we would do this incrementally for large tables that are added to over time. Transformation would include; joining tables together, adding flags and calculations.

Finally the Dashboard Loads the transformed data it requires. As the data is fully prepared in the transformation stage, it can be loaded as ‘optimised’ which means it happens very quickly.

When storing QVDs in the Extract and Transform layers it’s good practice to split large tables into manageable chunks. These tables typically contain your transactional data which grows over time (commonly referred to as FACT tables). These bite sized chucks depend on the data volume although we would typically recommend monthly files – this makes archiving data in the future easier. Also, if your dashboard only requires information from the last twelve months, you don’t need to load a QVD file which potentially could contain many years of data, which of course would slow down your process.

The benefits of this type of data load structure include:

  • Your source systems are only accessed once, reducing the demand on their resources
  • You can validate the accuracy of each stage of the process
  • QVD files are built up over time and are optimised
  • Several dashboards can re-use the same data which is both efficient and consistent

If you have any questions or you’d like more information on how Qlik business intelligence can help your organisation, please don’t hesitate to contact us on +44 (0)330 363 9900 today.

Thanks for reading!

Richard Pearce, Senior Consultant.

What makes a great Qlik Dashboard

What makes one Qlik Dashboard stand out from the rest?

It’s probably best to start at the beginning and ask the question: what is a dashboard? There are lots of ideas out there: “a single view, like the dashboard of your car, giving you the important facts you need in a clear, concise way” is probably the most popular definition. When it comes to business however, a single page dashboard often asks more questions than it answers. A simple set of dials doesn’t give you the full story, just the ending. So perhaps a single view is just one element of the bigger picture. This would take us neatly onto the “Dial to Detail” style of reporting, suggesting a top down approach to analysis. If the “dial” gives you the ending then it would make sense that the “detail” is the story, but is that the best approach to managing performance?

Qlik Dashboard Ometis 2

Effective Performance Management

Is performance management about investigating why something has happened (or possibly gone wrong)? Or should the focus be more on prevention and a continuous journey towards more efficient operational processes and increasing your organisation’s skills? Having the relevant data and being able to supply this information to the right person to analyse is key to success, although I’m always curious when organisations employ a team of analysts. In my experience these teams usually began life as report creators who churned out information by request. These teams are often asked to shift focus and spend more time analysing the information, they are analysts after all. The reason I find this shift somewhat curious is because I’m not sure how exactly that’s supposed to work. I believe that it is those who are managing the front line people and processes who are best placed to complete the analytical task and make effective changes based on their findings. I believe a front line manager knows their own area and information should act as an additional tool in this process.

Qlik Dashboard Ometis 3

Making It Personal

Mobile phone apps have been popular for a while but why is that? One reason is because apps allow every person to customise their phone to their own needs. Not too long ago when you bought the latest phone you were stuck with the manufacturer’s own calendar, calculator, task list etc. Now that’s changed with the advent of smart phones and what seems like a limitless supply of apps. My phone has become very personal to me. If I were to swap my phone with someone else’s, even though the phone may be the same make and model, the apps I inherit will probably be of little use to me in my daily life because they don’t meet my needs.

This makes me think that reporting performance information has to be personal to succeed. For example, let’s say we have three people as our internal information customers; the CEO, Sales Director and Operations Director. We could safely assume the Sales Director and Operations Director will want very different information in their regular reporting so two reports are created, one for each, and both reports also go to the CEO who has to then take the bits of information from both reports which interests them. People are asked to gather information far too often. Organisations I’ve been involved in have anywhere up to 100 regular reports being produced and few are aimed at an individual or role. Most cater to a service or department.

Qlik Sense Dashboard Ometis

Have you ever been on the internet looking for something slightly out of the norm? You cast your hook, see if anything bites then you reel back in and try again. That process can be very wearisome and it doesn’t take too long before you feel frustrated that the information you need isn’t at your fingertips. Information gathering can be very frustrating for an individual if they’re having to, in effect, create their own dashboard by collecting information from a variety of sources.

The takes me to what I believe is the answer. What makes a dashboard useful, usable and used is that it’s designed around the person or role, providing only required information in a format that is effective The fewer roles each dashboard is targeting at the better. with our Qlik Dashboards you can do just that. For more information on how we can help your business achieve effective data analysis, get in touch today.

What do you think makes an effective dashboard? Tweet us @ometis_ltd.

Richard Pearce, Senior Consultant