Being faced with a new data source can be daunting. QlikView has a number of features to help you though. Here I take a look at Structure Files.
Creating Structure Files
Previously I have blogged on getting your QlikView project right from the outset. In this post I am focusing on just one aspect of that; Using structure files.
As a consultant I frequently arrive on new customer sites and am directed towards a database with no prior knowledge of what is in there. Sometimes a DBA, developer or analyst will be on hand to give pointers, other times however the database is a black box to the customer and I have to go in blind.
As you might imagine my tool of choice for dealing with this is QlikView. Firstly, if no ERD is available, I will load a list of tables and columns into QlikView in order to locate likely candidates for useful tables. I will cover this process in detail in a future blog post. After this I will then set about loading raw data from each of the identified tables into their own QlikView apps. Once loaded the required structure files can be created.
To create these files simply go to the Settings menu, select Document Properties and then the Tables tab.
You will find a host of interesting information here, but rather than just viewing it here we are going to send it to a set of files. To do this we simply click the Export Structure button.
Save the resultant files to a sensible location and then come back out of the settings screen. We will come back to this app later, but for now simply repeat the above for other tables.
Viewing Structure Files
The files you will have just created are simply text files, and can be viewed in your text editor of choice. To get a better handle on them I load them into QlikView though.
The app that I tend to do this with has been shared on Qlik Community; here:
https://community.qlik.com/t5/Member-Articles/QlikView-App-Structure-File-Viewer/ta-p/1488356
Once in here it is easy to identify fields with only nulls of just one value, these are unlikely to be of use to you and can be struck off the list of fields you need to load into your QlikView apps. Uniqueness can also be identified, if there are as many distinct values as rows in the table.
One of the other advantages of the structures app is that you can export the structure and delegate some of the work of defining which fields are important. What I tend to do is produce a spreadsheet that allows the end user to annotate the fields, flagging the one they want to keep and providing the names they want for the fields. The later part being essential for a successful implementation.
In order that the user can make informed decisions when updating the spreadsheet you can return to the apps that the structure file were created from. Onto each of these I tend to place my Data Profiler objects (see this previous blog post), a set of multi boxes and sometimes a set of list boxes of fields that are likely to be useful. These are then made available to the users, so they have the ability to look up the contents of fields as well as having the distinct counts they have in the spreadsheet they have been given.
From Structure File To Load Script
The other advantage of having this information in a spreadsheet is that the same spreadsheet can be used to get feedback from users can build your list script for you.
With a spreadsheet that looks like this:
You can then add this expression:
This will build the bulk of the load statement that you need to run to extract the data, for example:
Day AS [Day Of Week],
Once this expression is applied to all rows simply copy and paste the resultant column into your load script. Watch out for the extra comma in the last line though!
Further Usage
As well as the table structure export available on the Tables tab of Document Properties, similar files can be created for Sheets and Objects. This is very useful for documenting or getting your head around a QlikView document that has already been built. These files can be created from the Sheets tab of Document Properties, and can again be loaded into QlikView.
There is plenty of documentation about making sense of the stats contained in these files on-line.
I can’t mention looking at the contents of your QlikView app, without also mentioning the excellent Document Analyser, created by Rob Wunderlich. This gives you a whole heap of information about apps, this can help you find which fields in an app are never used and can be dropped from your data model.
There are many files that can be created by QlikView, Desktop and Server, that can in turn be loaded back into QlikView. Doing this can take a lot of effort out of some of the tasks a QlikView developer needs to do.
I hope this information is useful, and allows you to get up and productive quicker when presented with a new data set.
Excellent as usual Steve
Thanks Alan.
Outstanding, thanks for taking the time to share.
Thanks Paul. I’m pleased you found it useful. I will aim to share some similar tips soon.
Thanks Steve :)
Is there a way to get the same data in the application itself but without extracting those files?
You can view the data in QlikView, without exporting it, but you can not analyse it there. You can get much of the same information within any app by using the system tables. If you look at this blog post ( https://www.quickintelligence.co.uk/qlikview-data-profiler/ ) and associated download it will show you how.
is it possible to automate the structure exports? For example by a trigger or Load Script?
Hi Brenda, it’s not something I’ve ever tried, but it feels like it should be possible. I would start by looking in the API to see if it can be done with a macro. This can then be called by a trigger if required.
[…] they ultimately want in their data model and also makes you look like a professional. You can see the full write up at Quick […]