Nobody said it was easy ... Configuring LDAP

Recently I was in discussions with a client where they wanted to authenticate users against a Netscape LDAP server.  Of course, I told them that this would be no problem because QlikView 10 now comes with a Configurable LDAP directory service connector.  Easy peasy!

The client was satisfied and look like they will go ahead and order the new licenses (yay!) and hardware to implement it on.

Gulp!  I still hadn't played with this LDAP connector so I though I better get some handle on it before the implementation went ahead.  Of course, there is bountiful documentation on it in the server reference manual (well, just under 2 pages).  Luckily I had recently met the genius that is Michael Robertshaw, a Solutions Consultant for QlikView in the Netherlands.  With some guidance from him, I was able to get up and running.

I found a distribution of OpenLDAP for Windows and installed it on my PC.  Once I had an idea of what I was doing, it was actually quite straightforward (Note - choose BDB database, not LDAP during setup and all will be well!)  Once I was up and running, I used a tool called LDAP Admin to add new users and groups.

Now to the QlikView side of things.  I had thought that this should be quite straightforward.  It wasn't initially, but with the help of Michael I got there in the end.  The important thing to know is that LDAP and Active Directory LDAP are not necessarily the same thing.  There are a lot of differences.  Unfortunately, the defaults for the DSP entries are AD ones so these needed to change.  Also, the way that the server is specified is not as easy as I thought it should be, but easy to get right once you know.

I had thought that the correct Path entry for my server should have been:

   LDAP://myserver

This is what you will see for AD.  However, I also found that you need to specify the base search path in this string for me, with my settings of "mydomain.com", this was:

   LDAP://myserver/dc=mydomain,dc=com

Once I added this and the correct username and password (note that the username needs to be the fully qualified name, e.g. cn=Manager,dc=mydomain,dc=com), I was able to think about the DSP settings.  These are accessed via the pencil icon beside the password box.  Now, this is where the Active Directory defaults will need to be changed.  The defaults are:

   Account Name       : sAMAccountName
   Directory Label    : DSP1 *
   Display Name       : name
   Distinguished Name : distinguishedName
   Group member       : 
   Group object class : group
   Id Property name   : sAMAccountName
   LDAP Filter        : (&(!(objectclass=computer))(objectGUID=*))
   User member of     : memberOf
   User object class  : user

* the directory label is how you will identify users to QlikView, e.g. in this case - DSP1\username.

With advice from Michael and some testing, I found the best settings for OpenLDAP (other LDAPs may be different - use the LDIF information to help you work it out) to be:

   Account Name       : cn

   Directory Label    : MyDomain

   Display Name       : displayName

   Distinguished Name : dn

   Group member       : memberUid

   Group object class : posixGroup

   Id Property name   : cn

   LDAP Filter        :        (yes, this is blank!)

   User member of     : memberOf  **

   User object class  : posixAccount

** memberOf (used to check users are in a group) will only work in OpenLDAP if the memberOf overlay is enabled and working.

With these settings, I am now able to use the UserManagement in Enterprise Management Console to query the LDAP directory.

I tested that it was working by creating a new document on my server and used DMS to give access to a user called MyDomain\jdoe (who was a valid user in my LDAP).  I then generated a ticket for the user and connected successfully to the document.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

AND Mode in Set Analysis

Was on QlikCommunity recently and answered a query by Lawrence Chapman - Like for Like

When Lawrence replied, he said that he was looking for something quite reasonable in his chart - only sites that had records for all the 4 years.

I was thinking that this should be quite straightforward and then had to have another think!  The thing about QlikView set analysis is that it is just a set of selections.  The default selection mode in a field in QlikView is "OR".  So, if I select all the 4 years, I will still select all the sites because all of them are in at least one year.  Creating a set based in the year field wouldn't work for what Lawrence wanted.

I thought about how I would select those sites if I was just doing it in the layout (which is a great strategy for designing a set).  The only way that I can think of doing it is to have a selection box using QlikView's AND-mode.

Now, for AND-mode to be enabled, there are a certain number of criteria that need to be fulfilled.  Basically, the field needs to be in a table of only 2 fields and it is the 2nd field (the first is the key field associating the rest of the data set).  Also, the table needs to be loaded with the DISTINCT keyword to remove duplicates (have a look in the help file for more info).

Let me create a quick example to show this in action.  Here is a sales table:


Sales:
LOAD * INLINE [
    Customer, Year, Sales
    Customer A, 2009, 111
    Customer B, 2009, 111
    Customer C, 2009, 111
    Customer A, 2010, 222
    Customer B, 2010, 222
    Customer C, 2010, 222
    Customer A, 2011, 333
    Customer C, 2011, 333
];


You will note that only Customer A and Customer C exist in all years.  However, if I load this data, there is no set that I can create that shows me just the totals for those 2 customers based only on the fact that they have sales in each of the years.

So, I will create an AND-mode field:


SalesYear:
Load Distinct 
Customer,
Year As SalesYear
Resident Sales;


Once loaded, I add this field to the layout and select the "AND-mode" option in the List Box properties (this option will be grayed out unless the field fulfills the criteria above).

N.B.  At this stage, just after setting "AND-mode" in the list box, you may need to just run a re-load to set the internal flag that makes everything else below work.

Now, I can create a set expression like this:

Sum({<SalesYear={2009,2010,2011}>} Sales)

And it will only show me the sales for Customer A and C.

I could make this more generic by limiting the SalesYear to the currently selected Years:

Sum({<SalesYear=P({$} Year)>} Sales)

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner