Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question:

The answers and explanations we received were a lot more varied this time around. Some people even accused us of posting a bitmetrickquestion. A clever pun, but as we’ll see that’s certainly not the case here! In the spirit of providing a realistic Qlik certification experience we may include a red herring or nonsensical answer, but we won’t trick you. Not in the question at least 😉

The correct answer is D

Let’s see why.

Section Access lets you manage the (row and column level) security of a Qlik application. You do this by including a table in your load script that defines which data is accessible to the user. When a user opens an application, Qlik uses this information to dynamically reduce the data set to only the information that the user is allowed to see.

Let’s take a look at the example from the question to see how it works. We’ll start with a short explanation of the default Section Access system fields:

• The ACCESS column determines what access the user should have. The access levels are either USER or ADMIN. Users with ADMIN access can see all data in the application, unless it is limited by the security table. Users with USER access can only see the data that’s assigned in the security table. Users that are not listed in the security table will not be able to open the app. As some have commented, if you are reloading the application on your Qlik Sense Enterprise server then it is required to include the INTERNAL\SA_SCHEDULER with ADMIN access. Otherwise you won’t be able to reload the app.
  
  
• The USERID column contains the username of the user.
  
  
• The OMIT column contains the name of a field that needs to be omitted (removed) for the specific user. It enables you to set up column level security.
  
  

The _Reduction_Country column is included in the security table but it’s not part of the default Section Access system fields. This is where the magic happens! This column links the security table to the main data model in the Section Application and is used to only show the corresponding value(s) for the listed user.

For example, for OFFICE\USER1, the column _Reduction_Country lists the value SE. This links to the Countries table in the main data model, and limits the data to Sweden.

But _Reduction_Country and _REDUCTION_COUNTRY have different capitalization and Qlik is case-sensitive! They shouldn’t link!

You’re right, except when it comes to Section Access. Any column and field value within Section Access automatically gets a ‘free upgrade’ to uppercase. So, even though the column is named _Reduction_Country in the script, once loaded this will be converted to _REDUCTION_COUNTRY and the fields will link.

Note that the opposite does not work. The CUSTOMER_ID value listed in the OMIT column will not match the Customer_ID field in the Countries table, as it’s case will not be automatically adjusted. In this example the Customer_ID field won’t be excluded from the data. Remember to always use uppercase for all fields and values that link to Section Access, and to carefully test that it works.

NL is listed in the OMIT column. Why does the user still see The Netherlands?

Because OMIT only removes entire columns, not individual field values. The NL value does not refer to the name of an existing column, so it is ignored.

What’s up with the asterisk (*)? Shouldn’t the useralso see Denmark?

The asterisk is a wildcard character that will match all values listed within Section Access. The value Denmark only appears in Section Application, so is not included.

To summarize:

• Answer A is wrong because the * in Section Access does not include DK, so the value Denmark will be excluded for the user.
  
  
• Answer B is wrong because the _Reduction_Country column in Section Access is automatically converted to uppercase by Qlik.
  
  
• Answer C is wrong because the NL value in the OMIT column doesn’t actually do anything.
  
  
• Answer D is right because the values NL, SE and UK listed in the _Reduction_Country column are matched by the asterisk (*) assigned to OFFICE\USER5 and there isn’t anything else preventing a link to the _REDUCTION_COUNTRY in Section Application.
  
  

That’s it for this question. See you again next Friday?