Saturday, 18 October 2014

Promote a Qlik Sense sheet from Community to Approved

In Qlik Sense, users (with appropriate permissions) can create their own sheets.  These sheets will not be seen by any other users unless the owner chooses to publish them.  Once published, the sheet goes into the Community sheets area.

It may be the case that a sheet created by a user is so good that we would like to promote it to being an Approved sheet.  Right now, there doesn't appear to be a supported method of doing so.

We can, however, use an unsupported method and that is simply to update the entry in the database for the Sheet object.  You will need to refer to my previous post on connecting to the repository and use a Query tool (such as the one that comes with pgAdmin III).

The table that we want to update is AppObjects.  You will need to write a query (or load the data into Qlik Sense!) to find out the correct ID.  The Object will have Published=true and Approved=false so we just need to update the latter:

Update "AppObjects"
Set "Approved"=true
Where "ID"='F3986BDB-1BD7-41D8-8B27-C5040103B827'

Simple as that.  Once is is approved, as well as appearing in the Approved list, if you duplicate the application, the sheet will appear in the available sheets to edit.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Friday, 17 October 2014

Configure a Qlik Sense security rule to reload one task

One of the cool things about Qlik Sense Server is the ability to be far more granular as to what you give particular users access to do in different areas.  In QlikView Server, we can make people Document Administrators and give them access to a folder to be able to perform reload tasks on the documents there.  In Qlik Sense Server, we can give a user, or users, access to just one reload task for one application.

In Qlik Sense QMC, I will go to the Security Rules section and add a new rule.  I will probably only want to add Read and maybe Update but not allow Delete for this rule.  The rule will apply to QMC only.

The Resources that I will list here can be either specific names of objects or they will be wildcards.  In this example, my list of Resources are:

QmcSection_Task - the access to the Task section of QMC
ReloadTask_1899c8cb-3073-4362-9f98-a36dee86dcc8 - Access to a specific task
App_3a1be6fa-99b4-422c-94a8-de031c94a7f3 - Access to a specific application

I could give access to ReloadTask_* and App_* to give the user or group access to all tasks and all apps.

In the access, I can assign to a specific set of users or I can specify a name of a role.  This role does not have to exist anywhere else - this is you creating it!

So, how do I find out the Guid of a particular Task or App?  Well, I could query the database directly for the information (see my previous blog entry - Connect to your Qlik Sense repository).  Alternatively, I can use the web browser's developer tools to help:

If I right-click on a Task or App in their respective lists, I can select "Inspect Element" from the menu (IE11 and Chrome - other browsers may vary).  The Span that contains the row will have an element that contains the Guid:

Just copy and paste!

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Connect to your Qlik Sense Repository

There are many reasons why you might want to connect to your Qlik Sense repository.  For example, you may want to create a Qlik Sense application that lists all the applications and objects, along with current publication status.

It is quite easy to connect as it is hosted, by default, in a PostgreSQL database.

First thing that you will need is the download of the PostgreSQL ODBC drivers from: - there are both 32 bit and 64 bit versions available.

Once you have installed the driver, you can configure the ODBC connection:

The things that you will need to know are:

Database - QSR
Server - wherever the repository database is installed.
Port - 4432 (not the default of 5432)
User Name - postgres
Password - the password that you provided on installation.

That should be good to go.

For a good tool to browse the database, pgAdmin III is a winner:

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Monday, 25 August 2014

Update to Link Table rules

Back in 2011, I wrote a blog post entitled "Rules for creating a Key/Link Table in QlikView".  There were 3 basic rules:

1. Create a primary key in all the tables,
2. Rename the foreign keys to break the links,
3. Use a mixture of Concatenate and Join to generate the Key table using the Resident data.

These rules will still create you a Key/Link table that will work, but it might not always be the most efficient.  As I have had to deal with larger and larger data sets in QlikView, I have learned the hard way that things that work quite well on a table with a few hundred thousand records do not work quite as well when your tables with a few hundred million.

It is really the first "rule" here that can be the problem.  In a dimension table, the key that associates to the Key/Link table does need to be the primary key, however, the key that associates fact tables to the Key/Link table does not have to be a primary key!  This key only needs to be unique for the set of keys being linked to in the Key/Link table, not for the whole record.

For example, if my fact table contains CustomerID and DateID, most of the time that combination will not make a primary key in the fact table, but it is all the key that we need to associate to a Key/Link table containing CustomerID and DateID.  By not using a primary key in the fact table, we are vastly reducing the cardinality of the key being used and hence the amount of memory required to store it.

Another change that I would make is that I no longer use AutoNumberHashxxx to generate a key value.  Now, I always use AutoNumber with an AutoID.  This is because AutoNumber with an AutoID will generate a set of sequential integers which do not, in general, get stored in symbol tables, further reducing the memory requirement.  Using AutoNumberHashxxx to generate multiple different keys will result in non-sequential integer key values which will have to be stored in Symbol tables.

Yet another change that I would require is that the Key/Link table is generated using the Distinct keyword.  Nothing kills performance quite like having duplicate key values in a Key/Link table.

Finally, I no longer require to rename the foreign keys and then rename them back when creating the Key/Link table - I just leave them as is and then use the Drop Field xxx From yyy statement to remove the old foreign keys.

So, the new rules become:

1.  Create a key in each table at the correct granularity to associate to the Key/Link table.  Use AutoNumber with an AutoID.

2.  Use a combination of Concatenate, Join and Distinct to load a Key/Link table of distinct values.

3.  Use Drop Field xxx From yyy to remove the old foreign keys from the fact tables.

For example:

I have a simple structure with Customer, Calendar, Orders.  I have a separate fact table with Customer, Calendar and Delivery information.  I get a synthetic key that I want to resolve by using a Key/Link table:

Step 1 - Customer and Calendar (DateID) already have a primary key.  In Order I will create an ID from CustomerID and DateID.  I will do the same in Delivery:

   AutoNumber(CustomerID & '-' & DateID, 'CD') As LinkID,

Step 2 - Now I load my key table.  I will begin with data from the Order table:

   Load Distinct

Now Join in the Product and OrderDetail keys from the OrderDetail table.

   Load Distinct

Step 3 - Drop the old foreign keys:

   Drop Fields CustomerID, DateID From Order;
   Drop Fields CustomerID, DateID From Delivery;

Now all should be good:

Of course, we don't just create link tables to avoid synthetic keys, we usually create them to overcome association difficulties such as loops.

There is a special prize of a free copy of my forthcoming book, Mastering QlikView, for anyone who can give me a good, technical (i.e. not "because Qlik say so"), reason as to why the original, synthetic key containing, data structure is less desirable than the link table structure.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Wednesday, 30 July 2014

Extensions in Qlik Sense

Qlik Sense is all extensions.  Even the out of box objects are extensions.  It also comes with some samples of additional Extensions.

After installing Qlik Sense Desktop, have a look in %userprofile%\Documents\Qlik\Examples\Extensions.  You will find several sub-folders, each containing one extension.

If you have experience with QlikView 10/11 extension objects (or have read my blog entries on how to create extensions) then you will see immediately that there is a different way of creating extensions in Qlik Sense.

There are two core files that you must have:

- a .QEXT file which contains the JSON description of the extension that will be used within the desktop client.
- a .JS file that contains the javascript to implement the extension.  This JS is build around the requiredjs framework.

Additional files can be added as required.

If you copy one of the extension folders into %userprofile%\Documents\Qlik\Sense\Extensions and restart the Qlik Sense Desktop (F5 may be enough) then the extension should appear.

You can also simply create your own sub-folder and create the .QEXT and .JS files and they will be picked up.  However, it is easier to use the Workbench to create your extension from a template.  To run the Workbench, first make sure that Qlik Sense Desktop is running (it provides the web service for this) and then connect to http://localhost:4848/workbencheditor.

There is no documentation for the extensions in the default help for Qlik Sense, but you may be able to access the beta documentation at:

Some of you may have seen a post of mine where I had proposed a new chart type called a Pie-Gauge.  I had already created a QlikView v11 extension to generate these gauges using RaphaĆ«ljs to draw the pies so I wanted to have a go at doing this in Qlik Sense.  After a bit of trial and error, and looking at other extensions (including Ralf Becher's implementation of the d3 dependency wheel), I was able to get what I was looking for:

I am happy for anyone to grab the code for this from my github repository.  Just grab the whole RedmondPieGauge folder and drop it into your %userprofile%\Documents\Qlik\Sense\Extensions folder and restart the Qlik Sense Desktop.

You need to specify one dimension and then two expressions - one for Actuals and one for Target.

Each of the objects - including the text labels - are clickable to make selections exactly as if they were a Qlik Sense object.  This also implements a slider in the properties to allow you set a doughnut size.  Anyone who knows me will know that this was definitely an academic exercise - I hate doughnut charts!


Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Friday, 25 July 2014

Qlik Sense

Yay!  It is finally here.  Qlik Sense, the product that has previously been called, has been released to the public as a free download.

This is the desktop version of the product and is licensed similarly to QlikView Personal Edition.  You can use it "...solely for the User’s personal or internal business use...".

Right now, there is no server version but this will follow.  Qlik have also promised a public server service - Qlik Cloud -  to allow users to create and share content.  This will be awesome when it happens.

So, now Qlik have two products - QlikView and Qlik Sense.  Qlik Sense is an evolution of QlikView, but QlikView will still be around for several years to come.

So, what is in this new Desktop client?

When you first open it, you arrive at the "Hub".  This lists all your applications.  You simply click on an application to open it.

When an application opens, it will allow you to select sheets (just like current QlikView).  The navigation between sheets is different, so will need some getting used to, but it is pretty straightforward.

Opening the Sales Management demo app invites you to look at the new Story feature, where you can find out more about Qlik Sense.  The sheets in this application allow you to try out creating new objects, and learn how easy it is.

If you do feel brave enough to create your own applications, there is a very useful feature that allows you to drop a file of structured data into the application and then it will generate the data model and allow you to get up and running.

If you feel really brave, you can go into the script editor!  As it turns out, not really a lot of bravery required as any experienced QlikView developer will be able to get up and running in here - the script syntax is all the same.  In fact, all your old scripts will run and generate a data model.

Your Qlik Sense apps, in the new QVF format, are stored in %userprofile%\Documents\Qlik\Sense\Apps.  If you drop an older QVW file into this folder and re-open (F5 works) the Hub, the app will appear and can be loaded into Qlik Sense.  When you save it then, it will be saved in the new format.  Right now, only the data and script will get converted into the new format, all UI will be lost.  There are also limitations around applications with hidden scripts and those with section access.

I am really happy to see this new version finally released.  It marks the start of a whole new Qlik journey and I, for one, am happy to be along for the ride.  I can't wait to see where it takes me.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Saturday, 26 April 2014

Is QlikView v11 vs. QlikView.Next the right question?

I have seen and heard a lot of discussion about what is missing from the initial beta release of as compared with QlikView v11.  I am not sure that this is the right discussion to have.

The way QlikView has evolved, it has become much more than a data visualization tool.  It has become almost an application development platform.  All of us have used QlikView to do application things - show/hide objects, run macros, actions, etc. - and have built some excellent applications for our customers.  But was this the right thing to be doing?  Did we do it in QlikView because it was the right place to do it, or because we could and QlikView made it easy for us to do?

Let's think for a minute about the strengths of QlikView 11. For me, they are:

  • A really good ETL script to allow us take data from lots of different places and combine them in different ways - including using best practice dimensional modelling.
  • A best of breed analytics engine that calculates fantastically well across large data sets.
  • Associative logic - the green, white and grey, awesome functionality for data discovery.
  • A good (but not awesome) data visualization tool.

The first 3 are, for me, the magic that makes QlikView the fantastic tool that it is, the one that really differentiates it from its competitors.

You will notice that I don't mention "application building" as a strength in QlikView - that's because it isn't a strength.  If I want to build an application, I will use Visual Studio, JDeveloper, or even Notepad++, but I am not going to use QlikView.  If I want something that is going to have application functionality - like writing to a database for example - I am not going to think of using QlikView for that, even if I could hack something together.

I can include QlikView data in another application, and I have built several such applications.  I can also create an application inside of QlikView using an extension.  I can do both of these even easier in, but still using my usual application building tools.

So, from my point of view, still has all of the great things that QlikView 11 has - except it is even better as it now is an AWESOME data visualization tool, one that business users will love using.

I think that for new customers, this will be fantastic. I think that they will start out building QlikViews that are designed around data discovery and their users will love it.

For existing customers, there is a different perspective.  Many will have invested a lot of money in building what they have.  My thinking is that it is an excellent opportunity for them to evaluate what they have done and revisit the design decisions that were made to see that they can do it better - while knowing that their existing platform will be supported for several years to come.

I think that if you look at the beta (note *beta* - not shipping product) and dismiss it because it can't do this or can't do that, then you might be missing a trick.  Think instead of what you can do - there is a wonderful world of opportunity opening.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond