Load data from excel file without seeing the data rows!

[apoorvasd]

Hello Everyone,

I have a question and also need your suggestion or ideas on how can I accomplish this requirement.

There is an excel file using which I need to create a QlikView dashboard. The concern is, the data in it is highly confidential that the owner doesn't want our team to see it. Is it somehow possible to restrict data in excel file such that, while extracting data from excel into QlikView, I should be able to see only the headers in the file and not the data row in QlikView file wizard? So far, the requirement looks simple, where I need to call a field as a dimension and sum(another field) as expression in a chart. Major concern is the data confidentiality! Is there any functionality in QlikView or any other idea on how I can handle this?

Thank you. 

[marcus_sommer]

AFAIK is Excel not the most suitable tool for highly confidential data - at least in the previous releases there was some vulnerable issues ...

Nevertheless there might be some approaches which could ensure some control of the data-access. For example you could create the load-statement just with the headers or some scrampled or dummy-data and this load-statement is then stored within an include-variable and maybe called within a hidden script.

The path to the include-variable and also to the excel-file could be controlled with the windows active directory maybe to the user which runs the qlikview-services. Thinkable is also to create a specialized user for it and the qlikview-services runs just a windows task which execute this load with this specialized user (maybe per RUNAS) - it won't be quite easy especially if you want to protect the whole process against a capable and bored qlikview-admin ...

Of course after loading the data you will need to control the access within the qlikview application and this could be realized with Section Access.

- Marcus

[apoorvasd]

Hi Marcus,

Thank you so much for your response!

I will try the include-variable approach. But could you please explain a bit more on what you meant by - "to create a specialized user for it and the qlikview-services runs just a windows task which execute this load with this specialized user"?

Thank you.

[marcus_sommer]

I meant using a special non-personal domain-user who has no other purpose and rights as to access those highly confidential data. This could be controlled with some kind o group policies from another admin-level which has nothing to do with the QlikView environment - just to ensure that nobody else has access to these data respectively could gain them in any way. This meant the most important part is to ensure the confidentiality of the data with the measures of the windows active directory.

- Marcus

[Peter_Cammaert]

IMHO Marcus' first suggestion (about "...dummy data...") is your only option to make absolutely sure that no prying eyes can see the original - highly confidential - data. And that one is probably the only one that can guarantee strict adherence to data governance rules (and auditing).

Ask your sponsor to create a new Excel data file with a limited set of rows containing representative test data, and develop your dashboard on that data source. If the data is chosen well, you can be sure that the dashboard will work in the end on the secret data file. And if not, it's up to the owner to expand the cases in the test file.

[apoorvasd]

Hi Peter,

Thanks for your response too!

Yes, my initial idea was to create or build QlikView application using a dummy file. Once I find that the dashboard is working as expected, thought of replacing this dummy file with the original one. The only concern here is, an error might appear later in the dashboard where I might be required to check the original file. So, just wanted to know if there are any other ideas! If I don't find anything suitable, I might go with this approach only!

Thank you.

[apoorvasd]

Hi Marcus,

Okay, let me think about this. Thank you so much for your suggestion!

[Peter_Cammaert]

Well, your situation is - how should I say - paradoxical. You should be able to read the data and use it to detect & solve bugs, but not "see" it? Compare this to a doctor who is told to cure you but to not known what is actually affecting you.

The only way you can guarantee your sponsor 100% that you won't have access in any way to his data, is to not have access to it in any way. AFAIK there is no "middle of the road". To illustrate my point, consider this: most delicate information to which noone ever had a look, can be deduced by a smart person by simply glancing at the stuff presented in a high level dashboard (example case: wages and bonusses in an HR document)

Compromises cannot be made in software (it will stop working in a predictable reliable way), but they can be made between people. For example, if your sponsor decides to trust you, you can agree on a procedure that gives access to the raw data to you only (and supported by all technical means listed before), and you promise to never divulge any aspect of the raw data or the end-result. Then you get a working relation that enables the exchange of information that a developer needs, as well as the creation of an operational and reliable dashboard and the containment of the confidential data.

If there are more developers in your group, you can separate responsibilities: one or more developers create a dashboard based on test data, but only a single one can test it on actual data and process information about all anomalies so that it becomes usable for the actual developers but doesn't contain anything about the real data. This is how it is done in a lot of environments where information should be closely guarded and leaks are simply not allowed.

YMMV.