Use a ramdrive!

Thanks to Håkan (the inventor of QlikView) for this handy tip.

If you are storing a temporary file to QVD and then reading it instead of using Resident (because it is way faster - something that you may find is improved greatly in version 10), if you configure a RamDisk (there are several options for this) and use that as the temporary location for the QVD, then it will be even faster!

I hadn't come across a RamDisk for many years (I think since MSDOS 6) but this is a very interesting tip if saving seconds is critical to you.

Beta testing QlikView 10

Just back from Qonnections - the annual QlikView Partner conference. This year it was held in the Fontainebleau Hotel, Miami Beach. Good time was had by all.

We got to hear about the new features coming in version 10. I can't say too much about it now, but there are some pretty cool things in there.

They have had a first beta internally for a couple of months now and beta 2 was launched to partners at the conference (and downloaded by me a few minutes later!)

There should be a public beta release in and around June - but that will depend on how beta 2 goes. Release in the autumn.

This is the longest beta cycle that QlikView have ever run. In fact, they are shortly going to upgrade all their own internal systems (including demo.qlikview.com) to version 10 - really eating their own dog food. Expect version 10 to be the most stable new release ever.

How QlikView stores data

To become really good at QlikView, you have to really understand how QlikView stores data. This is an introduction that should help you along that road.

If I load a very simple dataset:

Fruit:
LOAD *
INLINE [
Fruit, Color
Banana, Yellow
Blueberry, Blue
Tomato, Red
Tomato, Green
];

And then add both fields as list boxes, I see that there are only 3 fruit listed - even though I have loaded 4 entries. "Tomato" is only stored once by QlikView. Internally, there are binary keys keeping track of the association between "Tomato" along with "Red" and "Green".

In a large data set with a lot of repeating values (especially text values) the fact that each field value is only stored once means that there is a huge saving in the amount of memory needed to store the information. This is why QlikView can store so much data in memory.

Let's add another table of data:

Colors:
LOAD * INLINE [
Color, Red, Green, Blue
Yellow, 255, 255, 0
Blue, 0, 0, 255
Red, 255, 0, 0
Green, 0, 255, 0
Amber, 255, 121, 0
];

I now might think that I have 2 tables of data - both of which have a field called "Color". But I don't! There is only one field called "Color" in our dataset and this field will have 5 values. Internally, the association between each of them and their associated values are maintained with binary keys. It just so happens that one of the values - "Amber" - is not associated with any of the values in the "Fruit" field.

Note that there is probably no direct association between "Fruit" and "Red". The association is indirect. However, because of the smaller data set in memory and the binary keys, the association is very fast.

Hope this is useful!

Radish Bits

A long-time news group buddy of mine, Walter Shpuntoff, has his own blog that covers QlikView. Well worth the read:

http://radishbits.wordpress.com

Watch your Security Connections

QlikView security is quite simple to implement - you just need to link the security information to the data and you can enable automatic reduction of the information that a user sees. However, you need to watch out for the data that is linking the user to the data - if it is a text field then all of the values in that text field must be uppercase (you know that all the field names in Section Access must be uppercase too, right?)

For example, this straightforward load will enable Tom, Jane or Bill to login:

Section Access;
LOAD * INLINE [
ACCESS, USERID, LNK
ADMIN, TOM, TOM
USER, JANE, JANE
USER, BILL, BILL
];
Section Application;

LOAD * INLINE [
LNK, Name, Month, Sales
TOM, Tom, Jan, 12321
JANE, Jane, Jan, 31232
BILL, Bill, Jan, 32131
TOM, Tom, Feb, 34342
JANE, Jane, Feb, 34545
BILL, Bill, Feb, 76575
TOM, Tom, Mar, 32133
JANE, Jane, Mar, 12123
BILL, Bill, Mar, 21321
];

If you enable the "Initial Data Reduction Based On Section Access" in the "Opening" tab of the Document Properties then they will be restricted to their own data.

However, if I make a slight change:

Section Access;
LOAD * INLINE [
ACCESS, USERID, LNK
ADMIN, TOM, TOM
USER, JANE, Jane
USER, BILL, BILL
];
Section Application;

LOAD * INLINE [
LNK, Name, Month, Sales
TOM, Tom, Jan, 12321
Jane, Jane, Jan, 31232
BILL, Bill, Jan, 32131
TOM, Tom, Feb, 34342
Jane, Jane, Feb, 34545
BILL, Bill, Feb, 76575
TOM, Tom, Mar, 32133
Jane, Jane, Mar, 12123
BILL, Bill, Mar, 21321
];

Now, Jane will not be able to login to this document - because the LNK field has mixed case!

Best advice is to use an integer where possible:

Section Access;
LOAD * INLINE [
ACCESS, USERID, LNK
ADMIN, TOM, 1
USER, JANE, 2
USER, BILL, 3
];
Section Application;

LOAD * INLINE [
LNK, Name, Month, Sales
1, Tom, Jan, 12321
2, Jane, Jan, 31232
3, Bill, Jan, 32131
1, Tom, Feb, 34342
2, Jane, Feb, 34545
3, Bill, Feb, 76575
1, Tom, Mar, 32133
2, Jane, Mar, 12123
3, Bill, Mar, 21321
];

If you need to link on a text field, make sure that you have all capitals - use the UPPER function if necessary.