Expose Your Underlying Data

In this blog post I want to suggest that you show your users the raw data in your source systems, without modification.  Not just some of it – but as much of it as your hardware can manage.

But What About Best Practice?

Anyone who has read my previous blog posts will know this is the polar opposite of what I tend to recommend (see post on Perfecting Your Data Model) and accepted best practice.  However, it can be a useful step on the way to the best possible solution.

Often when I first engage with a client they know in broad terms what they want to achieve and also what data they have to support that (sometimes this is only from knowing what they input into the front end of their systems though). This may seem like all that is needed to launch into some kind of build. However I would suggest a step back in order to move forward. The data model design is (in my humble opinion) the most important part of any QlikView implementation – it’s not something that I feel I can guess at as an outsider. So, how do I go about taking my first steps with a new implementation?

Get Under The QlikView Bonnet

Where I tend to start is by pointing a QlikView app at the source database to read the structure and allow it to be searched (I have flavours of this app for SQL Server, Oracle and ODBC). This can be used to identify important looking tables from the data model.  The database schema can then be pulled into a spreadsheet (it will be apparent why in a moment) and the contents of the identified tables can be pulled into QVD’s. Doing a COUNT of rows in all of the tables is a good idea – this means a TOP or a WHERE statement can be used during the raw data extract – if required.

Once created, those QVD’s can be put a simple viewer – for me these QVD viewers tend to consist of a sheet of listboxes of fields that look relevant to me, a sheet with Multi boxes exposing raw data in all fields, a sheet of all data in a table (use a Calculation Condition on this for large tables) and a sheet with a simple data profiler I have created.

Once these things are created for each source table they can be shown to the primary user.

Reduce and Refine the QlikView Data Model

You will recall we put the data model into a spreadsheet – now here’s the why. To each tab of table definition should be added the columns: Keep, New Name, Link To and Notes.  This gives space to capture what should happen to each field.  The key sponsor is the best person to fill these columns. Encourage the user to name fields (in the New Name column) in the way they want to show them on screen and to be ruthless in removing fields that are not needed in the dashboard. In my experience, you often need to leave a week or so for this exercise to be done well.  As they do this they will have access to a searchable copy of the database schema (via the first app created) and a view of the data that resides in every column they are considering – in the QVD viewers.

And Now We Build

Once you have this spreadsheet completed you can build the QVD generators for your apps in the knowledge you are getting it largely right. The spreadsheet can be used to generate the code for your load script with some use of the CONCATENATE statement (see below).

This is where all the good stuff like intelligent use of the ApplyMap statement and build of concatenated keys and creation of derived fields can take place.

In my experience the user will seldom get the spreadsheet filled in completely right first time – but it will be better than my own best guess. Also, this approach moves the responsibility for what fields are included and (more importantly) which are excluded over to the user.

Conclusion

It must be said that this approach is not always the required start point; for example, when migrating existing Crystal or Excel reporting and the data extraction has already been considered, or where there is a flat file data source. However it is an approach that has helped me to start many a QlikView project in a way that has allowed the build to go to to a successful conclusion.

This is the third in a series of blog posts of tips from the coal face of QlikView consultancy. View the entire series here.

Look out for the next post on hitting the quick wins early on in a project.