QlikView Access Point allows users to interact with apps in their browser. But what if those users are not on your domain or only require static data? How can we get information to them in a way that is robust and cost effective?

The Problem

The problem that our client faced was that they were producing QlikView apps for their sales team to support them when talking to clients and prospects. Due to geographical factors, a reliable internet connection could not always be guaranteed. What they needed to do was to get subsets of their QlikView documents out to their sales people wherever they were – so they could then open them in QlikView Desktop.

The QlikTech stack allows for this with Publisher, but for smaller sites this is a considerable investment. It can also be achieved with batch script and Windows Scheduler (and the initial implementation for this client did just that) but this solution is not particularly robust or maintainable by someone without scripting experience.

The Solution

An elegant solution to this problem can be created using NPrinting and cloud technology, such as Google Drive.

1. Schedule and format reports with NPrinting

Within NPrinting a Reduce Task can be configured. As with Publisher this creates a QVW file for each recipient with a filter applied. Using the Enterprise version of NPrinting this can then be scheduled to run at a beat. When the reduce task runs NPrinting will create a new folder for each QVW that it creates.

2. Distribute reports with Google Drive

The company runs on Google Apps and therefore there was little resistance to pushing data to Drive. If you have the desktop version of Google Drive installed, and you point your output to within the folder structure of Drive, new folders will be created on your Drive in the Cloud. Drive can then be configured to share these folders with the relevant recipients.

3. Happy sales team

Now each of the users can log into their Drive and download the latest version of their app. Or, if they also have Google Drive Desktop installed, the app will automatically be downloaded for them the next time they have a connection.

Choosing to do this using NPrinting means we also have the option to distribute PDF, Excel, Word or PowerPoint documents in the same way. In this way the sales team could get a daily updated version of their PowerPoint deck with bang up to date information in it – without having to do anything other than open the PPT file.

How to Integrate NPrinting with Google Drive

1. Set up Google Drive

1. Go to http://drive.google.com
2. Click the Download Google Drive for your PC button.
3. Open googledrivesync.exe to automatically install and start Google Drive on your PC.
4. Enter your Google Account username and password in the window that opens. This will be the account associated with Google Drive for your PC.
5. Complete the installation package instructions.
6. Launch Google Drive for your PC from the Start menu. Any files or folders you put into your Google Drive Folder will automatically be synced with your Drive in the cloud. This is where you will output your folders from NPrinting.

2. Set up Sharing

1. Go to http://drive.google.com
2. Check the box next to the file or folder you’d like to share.
3. Click the Share icon.
4. Choose a visibility option. Select “Private” so that the folder is only shared with users you explicitly grant access to.
5. Type the email addresses of the person you want to share with in the text box below “Add people.”
   You can add a single person, a mailing list, or choose from your contacts.
6. Choose the access level from the drop-down menu next to each collaborator: “Can view” will allow your users to view all documents but not edit them.
   
7. Click Share & save.

3. Output NPrinting Reports into your Drive Folder

1. Create a new NPrinting document and set up your Connection
2. From the menu create a new Reduce Task
   
3. Output to your Google Drive folder setting NPrinting to have recipient sub-folders.
   
4. Click the recipients button to add a number of new recipients for reduced QlikViews. Set the folder and subfolder names accordingly.
   
5. For each of the recipients configure the filter based on fields within the connected QlikView document.
   
6. Select the task from under Reduce Tasks and click the Run button to send it out. The sub-set QVW files will be written to the appropriate folder.
   
7. To make this happen at regular intervals create a new Job and then a new Schedule to automate the process. Users will then receive fresh QlikView documents as often as you configure them to be sent.

See it in Action

As QlikTech say, “seeing is believing.” The video below shows the working solution in action:

This is just one example where combining QlikView with other technologies can allow you to produce even more inspired solutions. If you would like more information on NPrinting please see our NPrinting product page.

Quick Intelligence are NPrinting Partners

The post QlikView, NPrinting and Google Drive appeared first on Quick Intelligence.

Don’t Waste Space

The first thing to say here is that a Clear Current Selections button is totally redundant. That button is built right into the QlikView UI in Desktop and both Access Point versions. The same is true of Back, Bookmarks and (unless you have hidden your tabs) Next and Previous Tab buttons. Save yourself some space for some more analysis and don’t waste space with these buttons.

In my opinion shiny lozenges take far more space up than they need to and are visually distracting. You should not use these unless you really have to (i.e. your client or user insists).

Many objects can be Buttons

Anything that supports Actions can be a button. The lowly Text Area serves this purpose admirably, and if you have to use a gauge in your app, at least make it useful by making it clickable. A text box with no borders or shading provides a clickable line of text – like the ones that have served web pages so well for many years now.

With the use of dynamic colours you can have traffic light dashboard components that can also be useful for triggering actions (such as a dive to detail). Simple shading behind your text can make a useful toggle switch in your app.

Using Actions For Hiding Sheets

Here are some examples of text boxes performing the actions of buttons. These examples place values into variables, which are then used to do something dynamic in the app.

In most of the QlikView apps I produce I have some tabs for system data that is hidden from most users. To implement this I set a variable in the load script:

let vShowDev = 0;

On the front sheet there is then a text box that has dynamic text:

=if (vShowDev = 0, 'Show', 'Hide') & ' Development Tabs'

And finally there is an Action to set the value of the variable:

=if(vShowDev = 0, 1, 0)

The toggle on the screen looks inconspicuously like this:

All that is left then is to put the Conditional Show criteria on the correct sheets. The code for this is simply:

vShowDev = 1

Show Different Views Based On Variable Values

Another example of a show/hide toggle is where you want to swap between different charts or tables. Natively QlikView has the Auto-Minimise tick box and more recently the Container, but I am not a fan of using these. With a Conditional Show one object can be hidden and be replaced with a number of other objects.

To do a multi-way toggle I will set color variables up in the load script:

let vColHighlight = rgb(210, 255, 210);
let vColNoHighlight = rgb(255, 255, 255);

And default a display variable:

let vViewMode = 'Dashboard';

Text Areas are then required for each of the values you wish to set. Each text box simply has the legend of the view mode, and an Action to set the variable to the correct value:

The final step is to highlight which button is presently active. This is done by setting the Background Colour on each Text Object  to a dynamic colour with the following code:

if(vViewMode = 'Analysis', $(vColHighlight), $(vColNoHighlight))

With a bit of sizing and alignment the row of toggle buttons should look a bit like this:

Again, a simple Show Conditional expression is required on each chart to dictate under which heading it displays.   The code for this will be:

vViewMode = 'Analysis'

Other QlikView Button Examples

There are many, many more examples of where using Actions on Text Objects or Gauges can be useful. Selecting a field to analyse, changing some UI feature, linking it to an external site or anything else you can think of.

I have created an app with a few examples that you may want to use in your own applications. You can download this from the Shared QlikViews section of QlikCommunity, here: http://community.qlikview.com/qlikviews/1426

And you can see the app in this YouTube video here:

If you can think of any use cases that I may not have considered please add them to the comments section below.

The post QlikView Buttons – When, Why and How appeared first on Quick Intelligence.

Back at the Business Discovery World Tour in 2011 QlikTech were talking of how they were encouraging third party developers to enrich the QlikView offering, rather than trying to build everything into the product themselves. The main thrust of this, at the time, was the introduction of QlikView Extensions. Spin on a couple of years though and more importantly than that, a thriving ecosystem of third party tools has matured around QlikView.  The best place to discover some of these tools is on the QlikMarket.

In this blog post I want to bring to your attention some of those tools that I use to improve my QlikView life.

QVSource

You have no doubt already heard that QlikView will connect to just about any data source. That is great, but what about when you want to connect to one of the others? This is where QVSource steps in. Running as a service on your machine it allows you to connect to other data sources – most notably on-line databases and API’s, such as those used for sentiment analysis and text analytics.

I use it frequently to pull down data from Google Analytics and monitor the reach of this site. Connectors to services like Twitter and Facebook are also good for demos, and indeed you can find a number of QVSource powered demos on-line.

If the particular source you need to connect to is not already covered by QVSource you can contact the developers – who I have always found to be most helpful and willing to add new features.

Visit QVSource

QViewer

A relative newcomer to my toolkit; QViewer has quick become one of the apps that I wonder how I got by without. Once it is installed you can double click on any QVD and it will bring the contents up in a tabular view. Profiling of the data can be done simply and intuitively without any coding. Searching for values within the QVD is also supported.

There are many occasions when I want to dive deeper into looking at the contents of QVDs (and I have built QlikView apps for doing just this) but as a quick and easy route to do this; QViewer is hard to fault.  Given the low cost of QViewer, if you have ever created a QVD, you should buy it.

Visit QViewer

QlikView Document Analyzer

Rob Wunderlich has produced many useful little tools and some very helpful example files in the shape of his QlikView Cookbook. The tool that I frequently turn to, and want to draw your attention to here is the QlikView Document Analyzer. It is a QlikView document itself, that you point towards one of your own QlikView documents. The Document Analyzer then reads the metadata from your document and profiles this for you. It gives a view of many different aspects of the profiled document – but where I find it most useful is showing a client just how many fields they have pulled into their apps that they have not actually used anywhere.  This enables you to prune your apps and make them much more efficient easing the load on your server.

Visit QlikView Document Analyzer

NPrinting

Printed reports in QlikView (as I have mentioned previously) are not one of it’s strongest features.  Scheduling output and sharing it via email (with Publisher) can be cost prohibitive to some organisations. NPrinting steps in to address both of these issues.

Sitting alongside QlikView it allows users to pull data straight out of their QlikView documents into applications such as Excel and Word. The scheduler can then produce versions of these documents for a number of different recipients (with different subsets of data) and distribute them via email. This is great when users only need a read only view (removing the need for a QlikView licence for that user) or when data needs to be pushed to users pro-actively.

For many, this application fills in some of the crucial gaps in QlikView.

Learn more about NPrinting

TortoiseSVN / Subversion

It was long considered a major omission in the QlikView product set that there was no way of doing proper source code control. The introduction of the Project folder addressed that to an extent – but actually doing the Check In and Merge of code was very manual.  As of QlikView 11 hooks have been added into QlikView to allow the use of third party source code control tools.

TortoiseSVN is in use at a couple of my clients and manages the creation of projects and check out/check in process well. It may not be for everyone, as the time overhead of creating the project folder adds to development time, but if you have more than one developer amending the same Qlikview document; using TortoiseSVN saves a lot of headaches. You may chose to use it if you are a sole developer if you want the ability to do roll backs of select changes in your QlikView document to a prior state.

Visit TortoiseSVN

Notepad++

Whilst not strictly a QlikView tool, this developers text editor deserves mention here thanks to the QlikView language syntax highlighting definition produced by Matt Fryer. Notepad++ has all the features you would expect in a text editor of this kind and quite a few you might not. If you use include files in your applications (as I have discussed on this blog) then you should consider this Notepad++ for editing them.

Visit Notepad++ | Notepad++ Syntax File

SnagIt

This next tool is not a QlikView tool at all, but it deserves a mention as I now can’t imagine my life without it. SnagIt is a screen capture tool with every whistle and bell you could possibly want thrown in.

Capture any area or window on the screen, manipulate it and then send it out to any number of locations other services. Where it has a real application in a QlikView environment is when building a quick QlikView theme based on a company’s website.  It can also be very helpful when distributing screenshots from QlikView apps – thanks to the ability to blur areas of your screen-grab, to hide any sensitive information.  A great tool, and one I highly recommend.

Visit SnagIt

Mapping Tools

Going slightly off the topic of tools I feel exposed without, these are a couple of third party apps that are established and well used – but I personally have yet to implement in a live environment.

Most people are aware that you can integrate the Google Maps API into QlikView to plot points on a map. This is relatively inexpensive and covers a lot of mapping requirements. However, when you need to do something that goes beyond the native capabilities of the Google Maps API and QlikView – you may want to turn to either GeoQlik or Idevio Maps.

GeoQlik | Idevio Maps

Other Quick Mentions

Finally a couple of quick mentions, and again I am veering slightly off the QlikView track here, of a couple of small apps that I use quite frequently. A quick Search and Replace tool, known simply as SR32, which allows me to quickly interrogate the contents of text files – be they CSV data sources, XML files (such as QlikView themes or config files) or QlikView log files.  The other quick mention is a tool for grabbing RGB colour codes from the screen, ColorPix, which is a great time saver when creating a QlikView theme that mirrors the look and feel of a company web-site.

Neither of these tools will change your life, but they may well save you a few minutes each time you use them.

SR32 | ColorPix

Conclusion

There you have it! My list of tools that work for me alongside QlikView to deliver inspired solutions to my clients. There may be some on there you use already, and some that you can’t live without that I am not aware of. Please let me know of any other great add-ons in the comment box.

Tick the box below to subscribe to comments, if you want to be notified of what others think on this topic.

The post 9 Essential QlikView Development Tools appeared first on Quick Intelligence.

The function itself is not revolutionary, but it is going to cause me to have to go back and modify many QlikView apps to use it.  As I described in my previous blog, you can include code from an external script file simply by referencing it like this:

$(Include=..\Scripts\ExternalScript.qvs);

This is all well and good, provided that ExternalScript.qvs exists.  However, if you have a typo in your file name then QlikView will happily just ignore your include statement and get on with the load script.  That is until it references that table you thought you loaded in your include file.  Debugging this could take some time – as the error will not necessarily occur anywhere near where you include failed and unless you are aware that QlikView doesn’t raise errors on missing includes you may not suspect that is the fault anyway.

This seemed to me like a bit of a flaw in how QlikView handles includes.

However, someone has obviously noticed this at QlikTech and with just a small change to your code:

$(Must_Include=..\Scripts\ExternalScript.qvs);

You will get an error message raised on any missing scripts.  This will alert you in Desktop mode, or send abort your load and send a notification in batch mode.  To my mind a much more useful thing to do than to keep the missing file quiet.

Many thanks to Toni Kuatto for posting about this feature on QlikCommunity.

There have been other occasions where there is a seemingly un-documented feature in QlikView that really helps makes things better, the AllowDataLineage flag being a recent case in point.  I’m sure there are many more of these that I am yet to uncover.

If you are able to tip me off to any more of these, please leave a comment below.

The post Hidden QlikView Features appeared first on Quick Intelligence.

When you first create your chart, setting only the bare essentials up first, your caption may look a bit like this one here.

The actual coding behind the expression is shown in the header (which is meaningless to anyone who is not a developer) and there is some chart junk in the form of maximize and minimize icons that you probably don’t need.

First of all, you should always provide a label for any expressions, whether that label is visible or not, that is meaningful to the user.  Any icons that are not required can be turned off.  The XL icon is a matter of taste – I tend to leave it on.

But why stop there?  You have space taken up by a caption, so why not use it?  In the caption you can type plain text, or (more usefully) you can provide an expression.  Expressions always start with an equals sign.  If you are showing numbers then ensure that you use the num function to format them correctly.  In this example total sales are shown.

Your chart or table will no doubt have a dimension on it, so you can also use the space to give details of what the dimension is that the chart is over.  If your chart or table uses a Cycle group then you can use the GetCurrentField function to retrieve the current value.

If you still have some room on your caption bar you could provide even more information here.  It may be useful to show the date range of the data that you are displaying or, as I have here, a count of the rows of data that make up the values in the chart. You can do all kinds of expressions in a caption so give some thought as to what is going to be most useful to your users.

Here is a completed chart.  The caption is showing additional data in the form of the total sales (which you would have to add up otherwise) and the number of sales – which is not represented at all.  If the dimension wasn’t a Cycle group then you could do away with the dimension legend on the chart and only have it in the caption.  In the image above the caption text is restating the dimension in the caption, so that all the headline information is in one place.

The code required for the caption bar in the chart above is as follows:

=’Sales by ‘ & GetCurrentField([SalesCycle]) &
‘ (£’ & num(sum([Sales Amount]), ‘#,##0′) & ‘ – ‘ &
num(sum(RowCount), ‘#,##0′) & ‘ Sales)’

As well as captions; expressions built up in this way can be shown in Text Objects.  A ticker bar of information along the top of a page is a good way of getting lots of information in to a small amount of space.

So, what can you fit into the captions in your apps?

The post QlikView Caption Contest appeared first on Quick Intelligence.

Land And Expand

One of the mantras of QlikTech sales strategy over the years has been to Land And Expand. This phrase nicely sums up the way that QlikView can find its way into an organisation with a single user and then usage grows to more users and eventually Enterprise Server licences are required and the world is good. The phrase also ties in nicely with the way that an incredibly quick time to live can be achieved with a simple app that can then be expanded into something much bigger and better.

Both of these scenarios are ones I have seen happen with great success in a number of organisations. I have also seen it go quite badly wrong.

We Need A Bit Of Help With Our QlikView

I was approached by the company involved in this tale after they had realised the consultancy firm they had been using were not working out for them and their endeavors to ‘go it alone’ were hitting too many roadblocks. The company in question have sanctioned this article – but will obviously not be named. “We need a bit of help with our QlikView” was the phrase they approached us with. Since then we have helped them patch up and improve many of their QlikView apps. Progress there is always a bit like wading through treacle though. Here’s why.

Mark Twice, Cut Once

If I had to assign a single blame to why things are so challenging at this client it would be the practice of cloning one document to use as the basis for another. Sure, this can often be a way of reducing development time and bringing consistency. However when the cloned document retains most of the same content, just with perhaps a new chart or two, it is probably not a good idea. The reasoning was often because different users wanted a subtly different view of things. This can usually be achieved by a mix of parameters, section access and show/hide conditions. If a cloned document is ever seeming like a good idea to you then there is probably a more elegant solution available that you have not yet considered. Give this some thought before you copy and paste!

Get Your Data Model Right

The issues with cloned documents would be easier to deal with if the data model was correct and a decent QVD layer was in place. Frequent readers of this blog will know this is one of my pet subjects. At this client however there were no QVD’s being generated when I arrived and each of the multitude of different apps each made their own connection to the source database – running similar, but different, queries. Part of the reason for having multiple loads was that different group companies had different database instances – but a well thought out strategy of QVD use would allow each of these data sources to be merged together into a single app. This is something that has now been retrofitted for some applications.

The Ultimate Sin?

The thing that has probably caused me the most stress at this client is something else I have warned against on previous occasions. The use of the QUALIFY statement. This statement, whilst it can be useful for rapid profiling, should never find its way into a production environment. Any time it can save you at the start of a project will almost certainly be lost many times over as fields come through with names you are not expecting – particularly when you try and join or concatenate. Use of this statement is almost always an indication that not enough time has been spent on data design and that can only ever be a bad thing.

The Happy Ending?

In this series of posts I tend to end with the happy conclusion that through a combination of QlikView’s excellent software and some ingenious use of it the customer us now in a much better place. Sadly I can not honestly report the same here. Whilst many in-roads have been made to cleaning things up and consolidating the QlikView reporting; each days consultancy has been a battle against the proliferation of apps that share some common code. Many improvements have been made – but always with that nagging doubt in the back of my mind that it would be better to delete it all and start again. I enjoy a challenge when doing QlikView work – but it always more rewarding when breaking new ground rather than propping up something that has not been properly executed first time around.

You have been warned.

Click here to see all posts in this series. Look out for the next post, coming soon.

The post A Cautionary Tale of QlikView appeared first on Quick Intelligence.

QlikView Include files are snippets of code that are stored as text files outside of the QlikView app. Use of includes across multiple apps allows for effective code reuse.

Perhaps the most common use for include files is to store database connection details. By this simple route, when database credentials require changing they only need to be updated in the include file and all documents that use that include file would then automatically use the new credentials. An other potential advantage for this approach is to toggle easily between production and test database environments by having different include files on different servers.

Database connections however are just one example of the sort of code you can place in an include file.  Things that I have used them for in the past have included setting default variable values (such as storing RGB values) and calling a routine to log the performance of load scripts.

The point is that anything that can go into a load script can be placed into an include file.

The thing to watch though is, as with using dollar sign expanded variables, that the script executes as if all code is contained in one script. So if, for example, you commence a comment block within your include file (with a /* ), but neglect to close it, all subsequent code in your load script will effectively be commented out. As QlikView is not aware of the contents of your include file as the script is being edited; the syntax highlighting will not show the subsequent code as commented. This can be quite confusing – so be careful of this when using include files.

[Please note: see this subsequent blog post on resolving this]

Syntax highlighting is worth mentioning here, as due to your include file code being stored in a text file; QlikView is not able to do syntax highlighting for you. One way around this is to edit the code in QlikView first and then copy and paste this code into an include file afterwards. Alternatively, Matt Fryer has provided a syntax highlighting definition for Notepad++, which he has made freely available for download here.

So, after discussing the why’s of QlikView include files, here’s the how. You simply have to reference your text file in a dollar sign expansion, with the prefix “Include”.  Like this:

$(Include =..\includes\DBConnect.txt);

Note the use of the relative path (the ..\ ) which means that as long add the folder structure of the .txt file and .qvw remain together the files can be moved to a new location without anything breaking. Drive letters and UNC paths are both valid here also would you want to reference a fixed location.

Something to watch here is that QlikView will not raise an error if your include file is not found – it will simply continue without doing anything.  This can be confusing at best when the code you thought would run does not.

The use of the Debug functionality in QlikView is particularly useful when using include files. Each include file opens on a separate tab in the debugger so it is quite easy to spot what code had come from where and ensure it is indeed getting executed.

Hopefully that has given you what you need to start using include files. I’m sure if you do use them you will be able to save yourself some time and bring a greater degree of consistency to your QlikView apps.

The post QlikView Include Files appeared first on Quick Intelligence.

The headline grabbing fact here is that optimised QVD loads are up to 100 times quicker than non-optimised ones.  That makes a lot of difference if you are watching a reload dialog, it even makes a lot of difference to your server performance if your reload is running on a schedule.

The reason for the vast difference is related to the much publicised compression algorithm that QlikView uses when storing data for in memory analysis. QVD files are stored in a format that mirrors the compression used in memory (which is why QVD files are so small on disk) and during an optimised load the data is sent directly from disk to memory directly in the same compressed format. When a non-optimised load is performed this is not the case.

So why not make all loads from QVD optimised? The simple fact is that some operations require the data to be unpacked, modified and then re-packed.   This significantly slows the process.  Just about any change to the data on the way out of the file and into memory will cause a load to be non-optimised.

Some examples of things that will cause a non-optimised load are:

- Adding new fields to the table
- Deriving new values from a field in the QVD
- Retrieving a field twice
- Most WHERE conditions
- Joining to an existing in memory table
- Loading data into a mapping table

In contrast the things you are allowed to do are:

- Rename fields
- Omit fields
- Do a simple one field WHERE EXISTS on a field returned in the record set

This sounds hugely restrictive, but then most things you would want to achieve can be coded for. For example, if you need to add fields – do this in the QVD generate routine rather than when reading the QVD. Similarly, if you need to derive a value do this when you generate the QVD also. Even complex WHERE statements can be done by deriving flags or composite keys in the QVD generate routine and then doing a simple WHERE EXISTS on a temporary table (even if that temporary table is just a single row from an in-line table).

In fact, optimised QVD loads with a WHERE EXISTS clause on each subsequent load statement is a simple but effective way of quickly building documents which contain related subsets of data – but that is something for another post.

So, how do you know if your load is optimised?  Well, the first way is by noticing it is still running when you return to your desk with a fresh cup of coffee.  The other is by checking the load progress dialog.  Optimised loads show the text qvd optimized as the data is being pulled from the QVD – in contrast no message is shown when the load is non-optimised.

Always look out for that text and if it is not there when loading from a QVD then there will be merit in reviewing your load script to make the load optimised.

When the incredible speed of an optimised load is really essential is when you look to build an incremental load strategy and fresh data from source databases are combined with data previously stored in QVD’s.  If the retrieval of the old data is not quick and efficient then the whole point of the incremental load is eroded.

It is worth noting here however, that that a non-optimised load from a local QVD file will still typically be much much faster than from any other data source.  Sometimes non-optimised loads can not be avoided (or the development required to avoid them is not worth the time saving).

Hopefully this article has given you the information you need to make sure you loads are optimised, or make an informed decision to allow non-optimised loads.

As I have said in previous articles the back end of your QlikView document is at least as important as the front end – and ensuring optimised loads is an important part of getting the back end correct.

The post QlikView Optimised QVD Loads appeared first on Quick Intelligence.

I Want My QVD

It is always pushed as the ‘correct’ way of doing things to have a QVD layer in any QlikView implementation.  In practice there are very few scenario’s where it does not make things easier to have a QVD layer.  Even if you are not in a situation where your are pre-processing the data once before using these in multiple QlikView apps; the ability to logically segregate data extraction and manipulation from data presentation can make development easier and less prone to errors.  The incredible speed that data can be loaded from QVD means that unit development and test of just part of the load process before refreshing presentation apps can be done without lots of waiting around.  I’ve blogged on getting data design right previously (Perfect Your Data Model) and on the advantages of Optimised QVD loads – so I won’t repeat myself here.

Suffice to say though, whilst QVD’s are a ‘good idea’ generally, when it comes to Incremental Loads they are essential.

Why Do Incremental Loads?

As BI apps are expected to deal with larger and larger amounts of data the amount of time that it takes to retrieve that data becomes a serious issue.  This could be due to shear volume of data or the need for frequent refreshes.  Either way, you do not want to be pulling all of the data all of the time.  What you want to be able to do is just pull the data that has changed, append to that the data that you stored away previously and then get back to the business of analysing.  This will reduce load on the source database, the network infrastructure and your QlikView server.

There are many different ways of implementing incremental loads in QlikView, and the choice of approach should depend entirely on the data that you are extracting.

Here are just a few examples of the approaches you can use.

Date Stamped Append Only Table

The easiest type of data to build an incremental load over is a table where new rows can only be appended to the end and old rows will never change.  One scenario with these tables is that a date stamp is written to each row.  Examples of these types of table are system logs or records of transactions that can’t be amended – only reversed with subsequent transactions.

If the data volumes here are not massive, a kind of pseudo incremental load can be done, using the fact that QlikView can load multiple files with a wildcard (as I’ve shown in this Video posting).  Each time a data extract is done a WHERE statement can be done on the SQL select to only bring back the current day (or month, depending on volumes).  When the QVD is written the date can be encoded into the filename, eg. TransactionHistory_201301225.qvd.

When data is then extracted from the QVD it can be loaded with a wildcard, eg. TransactionHistory_*.qvd.  This approach is very simple, and avoids having to concatenate QVDs.  You do however need to careful to ensure that no data at the end of one period is not missed out as you have moved on to the next period before it is pulled.  The best way to ensure this is to loop through multiple periods, for example with each load refresh both the previous days file and the current day (or perhaps just do this until 2am if loading hourly).  Data volumes and refresh intervals will naturally inform decisions as to what is the most sensible approach here.

With anything involving dates make sure that you give proper consideration to timezones and daylight saving times – as these can cause problems if you are not careful.

Append Only Table With A Unique Sequential ID

If the table again can only have rows appended and there is a unique sequential ID then a different approach can be used.  The sequential ID could be a system generated number added by the database engine, or it could again be a date – if dates are recorded to the millisecond and it is not possible to have more than one transaction in a split second.

The first approach was straight forward, but it did potentially mean data was being pulled many times during the period each QVD was for and it also meant that many QVD files were being created.  The multiple files would take more space on disk and load slightly slower than if all data was included in a single file.  Both of those negative aspects can be avoided by always pulling data from a specific point in the table.

To use this approach the SQL WHERE statement will only pull records where an ID is greater than the last record that was pulled the previous time.  This value can be read from a config file created at the end of the previous run.  When all data is read in from the defined point forward a peek statement can be done to find the latest ID (ensure rows are read in ID order when using this approach), this can be put into a variable then a table and finally written to a CSV file with a STORE statement.

A similar approach to the first example could be used when the new data was pulled, so it was written into its own QVD with a suffix on – perhaps the last ID in that batch.  A better approach may however be to append the previously saved data to the new data – resulting in a single, growing, QVD.  As the previously created QVD will be in exactly the same structure a simple load from QVD will concatenate to the new data creating a single table in memory.  This in memory table can then be stored back over the top of the existing QVD.  As we are here being very precise and careful with the ID we are loading from and up to we can be confident that the resulting QVD will have each row only once and not be missing any rows.

Again, with this approach you need to ensure your assumptions about the data and the ID are correct and take care with the appending.  If the size of the dataset allows it is always good to have a fallback routine that does actually pull all data from source – perhaps just run over the weekend to ensure data consistency.

Free Form Data With Modification of Existing Rows

Unfortunately we often are not living in a simple world where ID’s are sequential and any row once created can not be changed.  Often changes to rows can be made at any time and the data we have in our QVD’s can become stale and need to be replaced.  This can still be dealt with using a QlikView incremental load, it just needs a bit more work.  Two things are however required to allow us to do this, firstly a timestamp or audit table to say something has been changed, and secondly a unique key.  If the database provides a unique key then this can be used, if not one may need to be created by concatenating fields until uniqueness can be guaranteed (perhaps combining Operator Code and DateTime for example – if you know an operator can only make one change per second).  This key will be used later.

As with all the other incremental loads described the SQL statement used to extract the data will have a WHERE statement on it.  This time it will be to return rows where the timestamp is since a specific point in time.  Unlike with the previous load where we had to be very precise with the point we were loading from; we can allow ourselves some slack here – perhaps on a daily load you may chose to always pull four days of data, so if the load failed for a couple of days it would still backfill the data that had been missed when it next ran successfully.

When the data has been loaded we will have some brand new rows and some that exist in the QVD we have already built and been adding to previously.  We still want to do an append from the QVD to concatenate to the new data – but this time we will explicitly exclude any rows that we have just pulled from the database with a WHERE statement in the QlikView load.  The syntax is simply WHERE NOT EXISTS (IDField).

Only rows where the ID is not already found will be loaded from the existing QVD.  This is why uniqueness of ID is critical, as even if the ID exists twice in the source it will only be kept once in QlikView.  Again, after the data has been combined it can be stored back over the existing QVD.

This approach is perhaps the most robust of the incremental load methods I have described here – and it is certainly the way I have implemented incremental loads most frequently out on client site.

Final Considerations

As with any system build, due consideration has to be given to incremental loads.  A significant amount of bullet proofing may be required around the solution.  For instance – you should code a check to see if the existing QVD is there before attempting to append it, so that the code runs first time before a historical file exists.  Make sure there is a plan in place for dealing with database or network failures.  A flag at the start of the load that changes it from an incremental load to a full one is a good ‘get out of jail free’ trick, so if there is any concern about the integrity of the data it can be pulled in its entirety from source.  If data volumes, or even the existence of historical data beyond a certain point (eg. where logs get purged from the source system but are required in QlikView) mean that a full refresh is impossible it is absolutely critical that QVD files are backed up.  The creation of a backup can even be written into the QlikView load script – so there is not reliance on an external process.

Two final points that are important anywhere, but have specific relevance to incremental loads; ensure that loads from QVD’s are Optimised wherever possible (Google this if you are not aware of Optimised vs. Non Optimised loads) and make sure that if you are loading from multiple sources into a single table you ensure that the columns from all sources are identical (if they are similar but not identical a massive synthetic key can be created and things can fall over very quickly).

Hopefully this has given food for thought as you go on and design your own incremental data loads.

The post QlikView Incremental Loads appeared first on Quick Intelligence.

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 The 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.

The post Ignore The Big Picture appeared first on Quick Intelligence.