8 Replies Latest reply: Feb 5, 2015 12:21 PM by Jonathan Poole RSS

    Different DBConnection based on QVUser

      I like to use different DBConnection (dbname, username, password) based on the QVUser.

      So I don't have to duplicate the app for every additional user, which will have his own db and can only mange one single app.

       

      Is this possible in Qlik Sense?

        • Re: Different DBConnection based on QVUser
          Michael Tarallo

          Hello Sarah,

           

          Are you using Qlik Sense Desktop (free) or Qlik Sense Server?

           

          With Qlik Sense Desktop (Free) - each app does need it's own data connection, so each user would connect using their own credentials and manage their own apps locally on their individual / personal machines.

           

          With Qlik Sense Server - data connections and apps can be centralized and shared amongst users, apps etc. with security rules, however the connection parameters are not dynamic based on the logged in user id, being Qlik Sense (be default)  does not directly connected to the data during App run-time like a traditional SQL Query based BI tool.

           

          Can you explain a little bit more about your use case? - Are these separate databases per user connected? or is it the same database - same tables - but configured with row and column level security - and their ID limits what data is viewable?

           

          If so - Qlik Apps - connect to the data - bring the data into its in-memory index engine - and then can use the logged in IDs - with the same app - to secure the data by row and column using what we call Dynamic Data Reduction and Section Access Security - this is a feature that is only available in Qlik Sense Server.

           

          Let me know more some more detail and we can see what the best solution is for you.

           

          Regards,

           

          Mike Tarallo

          Qlik

            • Re: Different DBConnection based on QVUser

              Hey Mike

               

              We use Qlik Sense Server.

              We have one app and several users which have different username, password and as well separate database. Every user will have a token allocated per username.

               

              I was wondering if it's somehow possible that every user can use the same app, but different data will be used.

              Because when we have some changes in the app we'll have to alter every app. If we have e.g. 30 users we have to alter it 30 times, otherwise it would be only once..

               

              Thanks & Regards

              Sarah

                • Re: Different DBConnection based on QVUser
                  Michael Tarallo

                  Hi Sarah,

                   

                  Hmmmm....Let me double-check with our services team on this and see what they come back with. I assume that might say the same thing as I have previously stated, but maybe there is some tip or technique we can leverage.

                   

                  I assume the DB schema is the same per database, just different data per user. That would make sense as the visualizations would be configured with the same fields, expressions etc.

                   

                  The ideal approach is to concatenate all the datasources for each user and put them into a QVD (Qlik's file based representation of the in-memory data model) - then this single app would load the QVD and you can use Qlik section access security to reduce the data for the appropriate user. You would use Qlik Load Script to do this, and then schedule a reload when appropriate to refresh the QVD. Then the App would pull the data in from a QVD file instead of a live data connection and each user would only see their data. There are many advantages to this approach and it is quite common with Qlik solutions.

                   

                  I'll shoot this over to one of our services members to see what they say.

                   

                  Regards,

                   

                  Mike T

                    • Re: Different DBConnection based on QVUser

                      Hey Mike

                       

                      In the data load editor there will be as well one variable, which will be different accoring the logged in user. If this is not possible then there could be added a new dimension.

                       

                      Therefore the database structure would be allways the same but with different data.


                      An other requirement would be to have up to date data, this means there have to be a reload taskevery hour (or at least when the user logs in).


                      T&R

                      Sarah

                      • Re: Different DBConnection based on QVUser
                        Jonathan Poole

                        You can load all 30 distinct data sets into one Qlik Sense application and employ section access to filter the results based on the user logged in.  Section Access allows a developer to create 'row and column' security on a Qlik Sense data model and you can use all the Qlik Sense data modelling techniques to customize this correctly. Section Access only works with Qlik Sense Server, do not attempt to use it with the desktop..you can easily get locked out.  I suggest duplication the app first in Qlik Sense server before adding it in (as a backup).

                         

                        https://help.qlik.com/sense/en-US/online/#../Subsystems/WorkingWith/Content/Scripting/Security/Sections in the script.htm?Highlight=section access

                         

                        To do this you would create a script in the load editor that loads data from the 30 locations into one application. You may need to use all 30 data connections in the script, but that is the beauty of the script... its sequential and allows you to sequentially connect/load all the different pieces together in one routine. Autoring the script is a one time setup. Then its automated.

                         

                        QVDs will help if the data volume is high and you need to do an incremental or parallel load to shorten the refresh time.

                         

                        Use only the fields that will be employed in exrpessions and in the UI, no need to load in 'dead weight' fields that will not be used.

                         

                        If the data volumes are extreme ... billions of records, the alternative is to use separate app for each user with 1 conneciton per app. In this scenario, externalizing the script that would be reused could work well by using the INCLUDE statement in the script. The INCLUDE allows you to inject a snippet of script from a TXT file sitting externally to the repostiory on the file system.  You can include the script wherever you want in the script.  The scripts of each app would look identical except for the CONNECTION at the top...  CONNECT / INCLUDE

                         

                        https://help.qlik.com/sense/en-US/online/#../Subsystems/WorkingWith/Content/Scripting/SystemVariables/Include.htm?Highli…

                         

                        Let us know what you decide.

                          • Re: Different DBConnection based on QVUser

                            Hey Jonathan

                             

                            The first approach should be fine for us. But I've some troubles to implement it.

                             

                            Let's say I've appMaster, which has all my diagrams. I assume, that this will get an appMater.qvf file like in Qlik Sense Desktop.

                             

                            On the other hand I need this QVD file as you mentioned to create my database connections. I found the articel about creating an QVD file.

                             

                            Does this mean, I've to create a 2nd app, and use this special synthax to create the qvd file?

                            But how has this file to look, and how can I access it from one file to the other, depending on the loged in user?

                             

                            T&R

                            Sarah

                            • Re: Different DBConnection based on QVUser

                              Hey Jonathan

                               

                              Could you give me please some more assistance?

                               

                              My Load scripts looks like below:

                              Main:

                              SET ThousandSep="'";

                              SET DecimalSep='.';

                              SET MoneyThousandSep="'";

                              SET MoneyDecimalSep='.';

                              SET MoneyFormat='CHF#''##0.00;-CHF#''##0.00';

                              SET TimeFormat='hh:mm:ss';

                              SET DateFormat='D.MM.YY';

                              SET TimestampFormat='D.MM.YY hh:mm:ss[.fff]';

                              SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                              SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                              SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

                              SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

                              SET FirstWeekDay=0;

                              SET BrokenWeeks=0;

                              SET ReferenceDay=4;

                              SET FirstMonthOfYear=1;

                              SET CollationLocale='en-GB';

                              LET homeCountry='Switzerland';

                               

                               

                              /* customised settings */

                              SET range_red = 0.75;

                              SET color_red = '220, 0, 0';

                               

                              SET range_orange = 0.95;

                              SET color_orange = '255, 129, 0';

                               

                              SET color_green = '0, 230, 0';

                              SET color_gray = '150, 150, 150';

                               

                              SET success_lup = count({<lupsuccess = {'OK'}>}lupsuccess)/count({<lupsuccess ={'OK','NOK'}>}lupsuccess);

                              SET success_voice = count({<tcvoicesuccess = {'OK'}>}tcvoicesuccess)/count({<tcvoicesuccess ={'OK','NOK'}>}tcvoicesuccess);

                              SET success_sms = count({<tcsmssuccess = {'OK'}>}tcsmssuccess)/count({<tcsmssuccess ={'OK','NOK'}>}tcsmssuccess);

                              SET success_data = count({<tcdatasuccess = {'OK'}>}tcdatasuccess)/count({<tcdatasuccess ={'OK','NOK'}>}tcdatasuccess);

                               

                              countries:

                              LOAD

                                  iso as iso3166a,

                                  name,

                                  longitude,

                                  latitude

                              FROM [lib://CommonData (nexus-ag_blappur)/countries.csv]

                              (txt, codepage is 1252, embedded labels, delimiter is '$', msq);

                               

                              World:

                              LOAD

                                  world.Name as iso3166a,

                                  world.Area

                              FROM [lib://CommonData (nexus-ag_blappur)/world.kml]

                              (kml, Table is [World.shp/Features]);

                               

                              Database:

                              LIB CONNECT TO 'PostgreSQL35W';

                               

                              LOAD tcsuccess,

                                  lupsuccess,

                                  callid,

                                  cycle,

                                  faultid,

                                  faulttext,

                                  Timestamp("time") as "time",

                                  simoperator,

                                  country,

                                  shortcountry as iso3166a,

                                  nwoperator,

                                  lupReq,

                                  lupAcc,

                                  testCaseName,

                                  campaignname,

                                  msisdn,

                                  imsi,

                                  callednr,

                                  LocUpDur,

                                  'LUP' as testtype;

                              SQL SELECT tcsuccess,

                                  lupsuccess,

                                  callid,

                                  cycle,

                                  faultid,

                                  faulttext,

                                  "time",

                                  simoperator,

                                  country,

                                  shortcountry,

                                  nwoperator,

                                  "lupReq",

                                  "lupAcc",

                                  "testCaseName",

                                  campaignname,

                                  msisdn,

                                  imsi,

                                  callednr,

                                  "LocUpDur",

                                  'LUP' as testtype

                              FROM mobile."public".lupkpi;

                               

                               

                              JOIN

                              LOAD tcdatasuccess,

                                  callid,

                                  cycle,

                                  faultid,

                                  faulttext,

                                  Timestamp("time") as "time",

                                  simoperator,

                                  country,

                                  shortcountry as iso3166a,

                                  nwoperator,

                                  testCaseName,

                                  campaignname,

                                  msisdn,

                                  imsi,

                                  callednr,

                                  actpdpreq,

                                  actpdpacc,

                                  UpTp,

                                  DnTp,

                                  PdpCtxActDur,

                                  servername,

                                  'Data' as testtype;

                              SQL SELECT tcdatasuccess,

                                  callid,

                                  cycle,

                                  faultid,

                                  faulttext,

                                  "time",

                                  simoperator,

                                  country,

                                  shortcountry,

                                  nwoperator,

                                  "testCaseName",

                                  campaignname,

                                  msisdn,

                                  imsi,

                                  callednr,

                                  actpdpreq,

                                  actpdpacc,

                                  "UpTp",

                                  "DnTp",

                                  "PdpCtxActDur",

                                  servername,

                                  'Data' as testtype

                              FROM mobile."public".datakpi;

                               

                               

                              LOAD tcsmssuccess,

                                  callid,

                                  cycle,

                                  faultida,

                                  faulttexta,

                                  faultidb,

                                  faulttextb,

                                  Timestamp("time") as "time",

                                  simoperatora,

                                  simoperatorb,

                                  countrya,

                                  shortcountrya,

                                  nwoperatora,

                                  countryb,

                                  shortcountryb,

                                  nwoperatorb,

                                  testCaseName,

                                  msisdna,

                                  imsia,

                                  callednr,

                                  smssend,

                                  smsrec,

                                  smstextb,

                                  SmsDelivery,

                                  If(countrya='$(homeCountry)',countryb,countrya) as country,

                                  If(countrya='$(homeCountry)',nwoperatorb,nwoperatora) as nwoperator,

                                  If(countrya='$(homeCountry)',shortcountryb,shortcountrya) as iso3166a,

                                  'SMS' as testtype;

                              SQL SELECT tcsmssuccess,

                                  callid,

                                  cycle,

                                  faultida,

                                  faulttexta,

                                  faultidb,

                                  faulttextb,

                                  "time",

                                  simoperatora,

                                  simoperatorb,

                                  countrya,

                                  shortcountrya,

                                  nwoperatora,

                                  countryb,

                                  shortcountryb,

                                  nwoperatorb,

                                  "testCaseName",

                                  msisdna,

                                  imsia,

                                  callednr,

                                  smssend,

                                  smsrec,

                                  smstextb,

                                  "SmsDelivery",

                                  'SMS' as testtype

                              FROM mobile."public".smskpi;

                               

                               

                              JOIN

                              LOAD tcvoicesuccess,

                                  callid,

                                  cycle,

                                  faultida,

                                  faulttexta,

                                  faultidb,

                                  faulttextb,

                                  Timestamp("time") as "time",

                                  simoperatora,

                                  simoperatorb,

                                  countrya,

                                  shortcountrya,

                                  nwoperatora,

                                  countryb,

                                  shortcountryb,

                                  nwoperatorb,

                                  testCaseName,

                                  msisdna,

                                  imsia,

                                  callednr,

                                  mosa,

                                  mosb,

                                  setupa,

                                  setupb,

                                  connectack,

                                  disconnecta,

                                  "CallSetupDur",

                                  If(countrya='$(homeCountry)',countryb,countrya) As country,

                                  If(countrya='$(homeCountry)',nwoperatorb,nwoperatora) as nwoperator,

                                  If(countrya='$(homeCountry)',shortcountryb,shortcountrya) as iso3166a,

                                  'Voice' as testtype;

                              SQL SELECT tcvoicesuccess,

                                  callid,

                                  cycle,

                                  faultida,

                                  faulttexta,

                                  faultidb,

                                  faulttextb,

                                  "time",

                                  simoperatora,

                                  simoperatorb,

                                  countrya,

                                  shortcountrya,

                                  nwoperatora,

                                  countryb,

                                  shortcountryb,

                                  nwoperatorb,

                                  "testCaseName",

                                  msisdna,

                                  imsia,

                                  callednr,

                                  mosa,

                                  mosb,

                                  setupa,

                                  setupb,

                                  connectack,

                                  disconnecta,

                                  "CallSetupDur",

                                  'Voice' as testtype

                              FROM mobile."public".voicekpi;

                               

                              As far as I understood you. I've to extend the line below

                              LIB CONNECT TO 'PostgreSQL35W';

                               

                              to something like:

                              LIB CONNECT TO 'PostgreSQL35W' UID=userid PWD=password;


                              and put this into the QVD file?

                                • Re: Different DBConnection based on QVUser
                                  Jonathan Poole

                                  hmm... the data connection line  'LIB CONNECT...'  should be auto generated from the data connection you create that you see on the right side of the screen while you are in the load editor. Its autogenerated when you click 'insert connection string' or 'select data' just below the connection on the right side.

                                   

                                  So you'll first need to create the connections and when you do that put in the credentials so that the connection is saved with the credential within it.  Repeat for the other credentials.

                                   

                                  Then you can insert each conncetion (which will use credentials embedded in the connection when it was created) at different points in the load script.

                                   

                                  This has the added benefit of not showing/exposing the actual credentials in plain text in the script.