… and points beyond

Download it from http://ftp.qliktech.com/qvwebdownloads/

Wordpress destroys code. Live and learn. You can download the code here.

My apologies for the code that was posted here. Copying and pasting broke the code. Download a ZIP of the code here.

I have expanded on the demo code that was floating around at Qonnections 2008. That code included an ASP page that made the requests using system objects and will not work outside IIS.

What about getting rid of IIS entirely? There are some changes we need to make. We want a ticket for a user other than the one making the request (GetTicketForMe). To do that QVHTTP requires POST instead of GET. The username, contained in XML, is passed as the body of the POST request. The requester must be authenticated as a member of the QlikView Administrators group.

To play with the test page:

• Be sure that the IIS website is stopped and QlikView HTTP Service is started.
• Copy the code below into an HTML file and save it in the QVHTTP base directory. For QV 8.2, this is C:\Program Files\QlikView\Examples\QvsHtmlWebpages\.
• Switch the QV Server to use DMS instead of NTFS authentication. Edit the metadata and add a username to a QV application.
• Load the demo page in IE or Firefox. For example, http://QVServer/qlikview/FileName.html.
• Fill in the name of the app you want to load and the user ID for the ticket request.
• Press the buttons to load the app using a requested ticket.
• The requester must be authenticated as a member of the QlikView Administrators group.
• A ticket will be issued and either displayed or used to request the app.

Each time you press a button, a ticket request is made. The QV Server issues a new ticket even if you request the same user ID again. User IDs are not case sensitive.

Unfortunately, this is not a complete implementation. Behind the scenes, the QV HTTP Server and your browser negotiated an NTLM authentication. This may have been transparent depending in your Windows network, but otherwise you were asked to provide the user & pass of a member of the QlikView Administrators group.

The point is that NTLM authentication is something supported by your browser and that the software issuing the POST request needs to handle NTLM authentication. There are resources that describe NTLM authentication in detail if you want to implement your own. There are tools (such as WGET) that support POST requests and NTLM authentication and can be scripted, though I have yet to try it. Indications from QlikTech are that they will add a more flexible option to authenticate the requester in upcoming versions.

I’ll be trying to do a scripted ticket request using a utility like WGET in the future. I’ll let you know how it goes.

Again, my thanks to Henric Cronstrom, Dan English, Ingemar Carlo, Johan Jeansson and Claus at QlikTech.

My apologies for the code that was posted here. Copying and pasting broke the code. Download a ZIP of the code here.

I don’t know if you use a lot of Sum-If logic like I do. It’s a blessing and a curse. For example: sum(if(Category=’Sales’,Amount)). It just doesn’t scale up to tens of millions of rows. Even with only a million rows, if you have a sheet loaded with these expressions, QlikView will calculate the chart objects one at a time. The user may have to wait half a minute for the sheet to update.

Until set analysis comes out in version 8.5 (and it won’t fix everything), try using numeric comparisons. sum(if(CategoryCode=40,Amount)). In practice you’ll see a 10X speed improvement, but it’s possible to have bigger gains. The reason, as I learned from Nik Bowman, is that numeric comparisons can be done natively on the processor, where the string comparisons have a lot of overhead code.

Starting in Server 8.2, QlikView authentication is much more flexible with tickets. I’m just getting started with them myself, and I’ll be sharing the resources I find along the way.

Big thanks to Dan English, Johan Jeanson and Claes Linsefors for my education about tickets at Qonnections 2008.

If you have a working QV Server implementation including an IIS or QVS HTTP frontend, copy this URL and see the ticket number in the response:
http://localhost/QvAjaxZfc/qvsviewclient.asp?cmd=<Global%20method=”GetTicketForMe”/>
NOTE: The double-quotes don’t copy correctly. Be sure to fix them or you’ll get an “invalid syntax” error.

Copy the ticket value to the end of this request to open a file:
qvp://localhost/YourDocument.qvw?TICKET= (paste ticket number here)

Behind the scenes the QlikView Server (QVS) is passed the username that authenticated to the web server. QVS issues a ticket in an XML response. You apply the ticket to your document request. QVS confirms the ticket, looks up the username that was passed, and then checks if that username can open the document (using NTFS permissions or the QlikView Document Metadata Service [DMS]). You are presented with the document in your client.

Most likely you are running IIS and you were authenticated using the same Integrated Windows Authentication (IWA) or a user/password combination that you use every day. In that case, this demo isn’t very impressive because you didn’t see different behavior. The key difference is that unlike QlikView versions prior to 8.2, your authentication to the server is not tied to your Windows account. QVS did not rely on IWA to pass your credentials, as it normally does. Instead QVS trusts that if you are authenticated to the web server, then you are a real user.

What this all means is that you don’t need IIS to authenticate the user. Combine flexible authentication with flexible authorization using DMS, and you can use two different schemes simultaneously. For example, local users on the Windows network will authenticate transparently using IIS and IWA. Meanwhile, remote users that are not a part of your network can enter a user/pass combo, or be referred through an Apache server, or the ERP system.

QlikView is multi-threaded in many areas but not during the load process. Next time you’re running a load script, open the Windows Task Manager. If you have 2 cores, the processor usage will never go above 50%. You can to do this while QlikView is running and see what actions are single- and multi-threaded.

Load times on large datasets can be dramatically improved by splitting the reload process into pieces and assigning pieces to separate cores. A good place to start is monitoring how your Windows installation allocates processes to cores. I’ve only seen one tool that shows processes on each core. If you know of any others, please post a comment.

• CPU-Control

Now that you have evaluated the automatic allocation, how can you specify the core directly? One tool that ships with Windows is the Task Manager. Unfortunately, it can only alter a running process and it does not save your configuration for the next run. NOTE: Vista allows command-line affinity setting and Server 2008 might as well.

There are several choices of utilities to control core affinity. Some are command-line such as BeyondLogic’s, others have a GUI and store your choice for the future such as Tom’s Hardware Guide’s. The downside with THG’s tool is that you will need to make copies of QV.exe with different names so each gets a unique process name. The upside is that the CPU affinity setting is automatic.

• BeyondLogic Process Utility
• Tom’s Hardware Guide Affinity Tool
• BindCPU Utility
• ProcAff
• SysInternals’ PsExex (written for remote execution)
• ImageCFG.exe from an older Windows Toolkit

Obviously, trying to do this with Publisher would be a challenge. The upcoming Publisher 8.5 will support command-line execution which may work with these utilities, however you will lose visibility in the Publisher console. It’s all tradeoffs, but at least CPU affinity is another tool in your belt.

CPU Control

  Tom’s Hardware Guide Task Assignment Manager

My thanks to Henric Cronström for taking the time to review my post on expression variables. He said the name for this new feature is still being decided. And he had some valuable information for getting started.

Your text is good and everything is correct. But you may want to add the following

1) The “all” qualifier is removed from the documentation and is replaced by “{1} total” which does exactly the same. “all” will still work in formulas in documents, though.

2) The syntax {$<Year=2002>} replaces the list of selected values with “2002″.

3) The syntax {$<Year+=2002>} adds “2002″ to the list of selected values.

4) The syntax {$<Year+=-2002>} removes “2002″ from the list of selected values.

5) The syntax {$<-Year+=2002-2001>} adds “2002″ and removes “2001″ to the list of excluded values. (Forced exclusion – see And-mode or red selection in attached app.)

Cheers

You can download the application here.

One of the more interesting features coming in QlikView 8.5 is “Dollar-Sign Expansion with an Expression”, Section 24.5 in the coming Reference Manual. One place in the manual they are called “on-the-fly variables”, but I prefer “expression variables”.

In many ways expression variables are similar to using the TOTAL qualifier because they are calculated across the set of selections and are not affected by a dimension value in a chart. If you use the ALL qualifier inside an expression variable, it looks at the entire dataset regardless of selections, as ALL normally does. For day-to-day use as part of set-analysis this is all you need to know.

For the most part, you can get the same results by using  the TOTAL or ALL qualifier. What makes expression variables different is that they are optimized for speed by being evaluated only when necessary. This is demonstrated with the expression variable $(=rand()). It is evaluated once and, as far as I can tell, will remain unchanged through reloads, saving and reopening. If you aren’t familiar with rand(), it generates a random decimal number between 0 and 1. Anytime QlikView reevaluates the expression, the value will change and we can clearly see it.

New caching algorithms make expression variables very efficient. Expressions that refer to field names are evaluated for each combination of selections–only as a new combination is queried–and the results are cached. You can see this behavior with the expression $(=Year+rand()). There are additional optimizations so that expressions that do not change are not reevaluated. For example, $(=max(ALL Year)+rand()) is evaluated once because max(ALL Year) will never change.

QlikView organizes the caching scheme by the string value of the expression. $(=rand()) and $(=   rand()) will have different values but both will maintain their values throughout the application. As another example, $(=max({1} Year)+rand()) and $(=max(ALL Year)+rand()) contain an equivalent “max” expression, but yield a different value.

By design, expression variables always give the same result for each row. This is half the reason why set analysis is so much faster than Sum-If logic. The other half is that QlikView compares the expression variable to the whole field contents and not another calculated value. As a result, this reduces the problem of set analysis to making a parallel set of selections, which is what QlikView does best.  On the other hand, an If statement can refer to the current row, and it can be affected by the dimensions for that row, so Sum-If logic must recalculate for each and every row.

This is my own analysis from watching the Beta software. Behavior may change for the final release and I might have misunderstood what’s happening inside the application. I will continue to post what I learn about expression variables. If you can confirm or disprove my analysis, or if you have an interesting use for expression variables, tell me about it.

It is still in development as of the beta. Looks nice.