metadata – The Qlik Board!

I’ve been wanting to use Document Attributes since I came across them when first exploring the QVSAdminDataProvider (you can see more info about that in this older post: Collaboration Metadata And More). Hurray – an opportunity finally appeared!

The Problem – too much Qlikin’ goin’ on (a good problem to have!)

I’ve been working consistently at a client and we’ve been growing the QlikView environment — more apps, more users and more revisions to apps. I was struggling to stay on top of the rapid changes: app versions; who has access to which app; which apps distribute to which groups; etc. So I started envisioning an application metadata repository. I’ve done similar things in the past using auxiliary text files that I include in scripts. These text files would hold information like the version #, an app ID, the author, the business owner, etc. This has been effective, but clunky.

But thinking a little more about these metadata, they are simply key-value pairs:

App Name: Sales Analysis
Author: Sven Klick
Version: 3.1.4
Access Group: QV – Sales
Access Group: QV – Executives

Ah ha! What a perfect use case for Document Attributes!

As a reminder, document attributes are assigned in the Publisher task creation process on the “Document Information” tab. By using the plus sign to add rows, an unlimited (?) number of attributes await exploitation. Once the document is reloaded (i.e. the task is run), these attributes are saved with the document. Specifically, they are saved in the .meta file.

For example, I will assign four attributes to my Governance Dashboard app:

After I run the task, the attributes I assigned will be in the file:
C:\ProgramData\QlikTech\Documents\Governance Dashboard.qvw.Meta

If you want to verify the attributes are saved, you can look at the document’s details in AccessPoint, in either list or icon view:

Defining Document Attributes

Before diving in and assigning attributes to all your QVWs, I recommend you start with a short list of essential attributes. Remember, you have to maintain these attributes across all your apps, so only add what you think will be useful.

Here’s a simple list I’m working with:

Version # – I come from a software development background, so the concept of formal versioning is critical for me.
Owner – this is the “functional” or “business” owner of the application. This meta-datum is useful when an access request comes in for an app and I need to track down the owner to grant approval.
Access Group – this is the NT group (or groupS, plural) to which the app is distributed. Also useful when on-boarding new users.
Author – this is useful when you have many people contributing to the QlikView app pool.

Now comes the arduous step — go through all your applications and add these attributes.

TIP: Because the attributes are attached to published documents, the non-published documents, like QVD loaders, will not feed the AccessPoint display or the QVSAdmin data provider. But I still like to save Version # as an attribute on my QVD loader and other staging apps, so I can quickly lookup the most recently deployed version of any QVW document.

Building a meta-app — QV Admin Helper

Now that we have all these great metadata for our published documents, how can we tap into them? As shown above, we can inspect the attributes in AccessPoint. But we can also bring them into data models using the QvsAdminDataProvider.

Specifically, I recommend creating a stand-alone app, I call mine “QV Admin Helper.qvw,” to load data from the QvsAdminDataProvider and related metadata. As the name implies, this app is intended for the QlikView administrators. It is not a “business user” app.

As a reminder, the raw data model will look like this (see: Collaboration Metadata And More):

I find it useful to take the generic table (AttibuteName + AttributeValue) and blow it out into explicitly named fields:

Here’s some sample code to do that, using a FOR..NEXT loop:

Building on …

I’ve augmented my QV Admin Helper with additional metadata that describes which end-user is in which NT group. These data are maintained in an Excel worksheet outside of QlikView. Only the admins have access to it and it is saved on the production QVS server. The “glue” between the DocumentAttribute table and the NT groups & user data is the “AccessGroup” field:

Now I can make some front-end objects to do QlikView-style analysis across my metadata. For example here are the User Documents and Source Documents pivot tables (filtered to Governance Dashboard):

TIP: keeping a separate table for User Documents is helpful when you use Loop & Reduce to spawn many child apps off of one Source.

MastersSummit in Amsterdam – October 1-3, 2014

If you haven’t heard already, our traveling band of QlikView fanatics will be hitting the road (and skies) this Fall and heading to Amsterdam October 1st-3rd. I’ll touch on the QvsAdminDataProvider in my Tips & Tricks class and have some samples from the QV Admin Helper that you can integrate into your own environment. Hope to see you there!

More info at: Masters Summit for QlikView

Keep on Qlikin’

Bill

IMPORTANT NOTE

You may need to edit the CONNECT string to provide the full file path to the DLL. The default location in QlikView 11.2 SR3 is on the C: drive – Program Files\QlikView

An edited CONNECT string would look like this:

CUSTOM CONNECT TO “Provider=C:\Program Files\QlikView\QvsAdminDataProvider.dll;host=corp-qlikview-t;XUserId=****;XPassword=****;”;

Thanks to Ronnie Taborn for this solution!

If you haven’t seen it yet, there’s a nifty feature in the QlikView version 10 to pull metadata from the repository into a QVW.

The metadata are related to applications (QVW documents) in AccessPoint and the server objects that users can create. Specifically:

Documents – names, sizes, AccessPoint properties
Document attributes – if you assign custom attributes in the QVS Management Console
Collaboration (a.k.a. “server”) objects – creator, types, properties

These metadata are not available in the Ops Monitor app (as far as I know), so this new feature is more than just a convenience.

Creating the script

Adding these fields to the load script is super-simple, thanks to a new option in the datasource selection drop-down menu – QvsAdminDataProvider.dll (32)

Choose the QvsAdminDataProvider.dll (32) from the drop-down menu
Click the “Connect” button to add the connect string into your script
Click Select to bring up the Select Wizard
Choose all fields from all tables and include the Preceding Load option

A few tweaks…

I made a couple of small tweaks to the script for convenience:

Rather than expressing the document size (DocumentList.FileSize) in bytes, I converted it to kilobytes using this code:
round(num(replace(FileSize,'.',''), '#') / 1024, 1) AS [File Size (kB)]

And I plucked the username out of the CollaborationMetadata.OwnerName:
subField(CollaborationOwner, '\', 2) AS OwnerName

Now reload the script! Your data model should look like this:

Using the objects

The simplest use case is to create table boxes and a few selectors. The Document List and Collaboration Objects are well-suited for tabular display. Here I’ve done a little renaming and formatting:

What else could we do with this?

Now we can start having fun! With these new metadata available for collaboration objects, we can measure aspects of our QlikView program such as:

Which applications get customized the most? (i.e. the most server objects created)
Which users are creating the most server objects?
What percent of our user base is sharing their server objects?

Here are just a few visualizations to serve as examples:

(this would be more meaningful with more than one user!! 🙂 )

Hope these examples get you thinking about ways to incorporate these metadata into the monitoring and improvement of your QlikView program.

Keep on Qlikin’

Bill

Tag: metadata