Section Access limitation of 1M rows?

[#1]

Section Sccess has started truncating my data set now that my app has expanded beyond 1 million rows.

The "hierarchy" of my data looks like this

Client Group

     Client

          Case

               Case Attribute A

               Case Attribute B

               ...

where Section Access is implemented on the level of the bolded underlined text above which is a 1:n relationship. A Client has many Cases but a Case belongs explicitly to a Client. Users are assigned access per Client + Case combination.

A few weeks ago the app expanded beyond 1 million Cases and that's when this issue started occurring. Here is the symptom.

> I reload the app (1,002,500 unique Cases)

> Save the qvw (still 1,002,500 unique Cases)

> Close QlikView and reopen the qvw (now exactly 1,000,000 unique Cases)

If I were to reload again without closing QlikView I would be brought back to 1,002,500 Cases.

Anyone encountered a similar issue?

QVW

[rubenmarin]

Yes, I founded before, 1M max, my workaround was to load the table outside the section access, something like:

section application;

Table

LOAD USER, FilterFields

section access;

LOAD NTNAME, USER

So NTNAME filters the USER and from there it applies security.

Hope i explained myself.

[#3]

Interesting indeed Ruben.

I'll investigate your proposed setup and see if that works in my environment. I'll keep you posted.

Are you aware that this is a known limitation of Section Access, meaning if Qlik has acknowledged this limitation?

[rubenmarin]

Maybe is documented somewhere but I searched and didn't found it. (It was more than one year ago)

[#5]

DO you have the Section Application executed BEFORE your Section Access part for a reason?

[rubenmarin]

No, it was a document which suffers very upgrades with drastically changes in data, security and usage and to apply one of those changes the simpliest way was to put section access at the end.

[#7]

Ok. To clarify, it seems that although Section Access supports >1M rows there is a limitation that reduction field(s) in in the Section Access table cannot handle more than 1M unique values. This is where a setup like Ruben Marin suggests above comes into play.

I've got a temp fix in place for now to limit the table to less than 1M unique values and will work on the clean solution in a couple weeks and post the results here.

[Brett_Bleess]

Philip,

Do not know if you have seen the following info, but this might help.  You can also leave a post on the blog posts and Henric will likely reply.  I know there is a 2 billion unique data point limitation as per the QlikView Reference Manual in the Appendices, but I am not aware of a Section Access limit, so this could be a defect, and if you are a licensed user, I would suggest you open a support case with us, so we can look into this.  There are some other blog posts here as well that may be helpful to you in understanding things at a deeper level and rather than me trying to find them all, I will let you look at them yourself to determine which ones may help in addition to the ones below:

Primary Design Blog link:

http://community.qlik.com/blogs/qlikviewdesignblog

Section Access/Data Reduction links:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/06/09/methods-for-data-reduction

http://community.qlik.com/blogs/qlikviewdesignblog/2014/06/02/data-reduction-using-multiple-fields

http://community.qlik.com/blogs/qlikviewdesignblog/2014/05/26/section-access

I hope this might help, sorry I do not have exact info regarding the limitation you appear to have hit.

Regards,

Brett

[briangaines]

Wondering if anyone can confirm whether a defect was ever found related to Section Access limitations in either QlikView or QlikSense?

[rubenmarin]

I don't know if it can be called a defect but there is a limitation of 1M different values in section access, you can test yourself:

SECTION Access;

LOAD 'ADMIN' as ACCESS,

'YourUserName' as USER,

RowNo() as REDUCE

AutoGenerate 1e6+1;

SECTION Application;

LOAD RowNo() as REDUCE

AutoGenerate 1e6+1;

After reload, without data reduction Max(REDUCE) will return 1000001, after opening with data reduction returns 1000000.