Data model / dummy dimensions / section access

Luk_B — Mon, 20 Dec 2021 21:08:46 GMT

Hi,

After applying section access I realized I have a problem in the data model.

Let me shortly explain how it looks at the moment.

The phase 1 of the Qlik dashboard is to replicate an excel report - so a lot of tables, pivot tables, which in excel have quite often unrelated dimension rows.

In Qlik model I have a main big table with the information about projects. Projects have start / end / purged / on hold dates so they are connected to canonical calendar.

Also the requirement is not just to show the final values in the pivot table but also after dimensions selections be able to have a second table with all the related deals / details.

So I decided to create a custom dimensions in the script for more complicated pivot tables, i.e

CustomTable1:

ProjKey,

'My 1st row' as CustomDimension1

From a Qvd

where (conditions required for My 1st row);

concatenate(CustomTable1)

ProjKey,

'My 2nd row' as CustomDimension1

From a Qvd

where (conditions required for My 2nd row);

So I have a dimension with 2 rows which have independent where clause conditions, and are connected to the main table on ProjKey. The main table has something like a project counter (1) so in Qlik pivot table I do for example:

Dimension: CustomDimension1

Column: Canonical Month

Expression: Sum(ProjectCounter)

I am not sure whether in general this was a good approach, but I think it does what is also needed - limits projects when any of pivot dimensions is selected and I have the right projects displayed in the details table.

The problem is where I have a few tables requiring a custom row - for example My 3rd row which is division of My 2nd row and My 1st row

So the idea was to create a dummy row without any ProjectKey or where clause, so something like

concatenate(CustomTable1)

// ProjKey, commented out

'My 3rd row' as CustomDimension1

Autogenerate 1;

This doesn't have to be drillable on the front because not necessary will make a sense (maybe in sums, lets say doesn't have to).

On the front in such custom pivot table is solved it by

Dimension: CustomDimension1

Column: Canonical Month

Expression: (simplified - some additional set analysis condition to react to users selections on rows)

pick(match(only({1}CustomDimension1),
'My 1st row',

'My 2st row',

'My 3rd row'

Expr1,

Expr2,

Expr2/Expr1

)

That has worked, but the problem became apparent when I added section access.

Section access limits data so my dummy dimension rows without ProjKey disappeared - obviously.

I don't have any idea at the moment how to solve this problem.
Section access may limit data on 4 different fields - country, region, manager, leading manager.

How would you approach this problem.

Thanks,

Luke

Re: Data model / dummy dimensions / section access

Luk_B — Wed, 04 Nov 2020 17:08:16 GMT

Dummy ProjectKey in Dimension table (for dummy rows generated with autogenerate), main data (concatenate dummy ProjectKey) and section access (Access, UserID, null as your SA fields, 'dummy' as ProjectKey) solved the problem.

topic Data model / dummy dimensions / section access in App Development

Data model / dummy dimensions / section access

Re: Data model / dummy dimensions / section access