Qlik Sense Server and MS-SQL Setup

[Johnny]

Hi all,

I'm currently implementing a small scale Qlik Sense Server setup for my office (Of around 20 people). My plan is to have Qlik on one server, and have it connect to a Microsoft SQL Server, which resides in another server.

After doing some testing with Qlik Sense Desktop and a test instance of MS-SQL, I have some questions about how things are going to work:

1. Where does Qlik cache the data? I broke the link between my desktop and the SQL server, but apps didn't have any issues loading the data, so this data must be cached somewhere.
2. If I add a LOAD statement into my ODBC connection to MS-SQL, when will Qlik perform a data reload? Quite concerned about this as the initial data loading from MS-SQL takes a very very long time.
3. Would running this setup result in smaller qvf files? Since the source data doesn't reside locally on the client. (This sorta ties in with point 1.)
4. If an app is accessed by 2 concurrent users, or if 2 apps are accessed concurrently, can they all use the same database account credentials?

If anyone else has ever run this kind of setup before, any extra tips would be greatly appreciated!

Thank you!

[fosuzuki · Accepted solution]

I assume you already know the difference between Qlik Sense Desktop and Qlik Sense Enterprise (the server edition).

1. Qlik Sense loads the data and stores it in two places: in disk, for data persistance in case you reboot your machine, and in memory, using a patented technology, for faster response times. So in fact it is working unplugged from the data source, it doesn't keep sending real time queries.

2. In Qlik Sense Desktop, the data refresh must be done manually in the "Load data" button. But QSE has a task schedule function.

3. Since QS loads the data and stores internally, when you use QSE the client will use a web interface with nothing stored in the local client. The mermory and CPU consumption will be server-side.

4. When multiple users access QSE to navigate in the dashboards, they access the in-memory version, which is not plugged live to the database. In QSE you can create a single Data Connection to your SQL Server instance, and all the apps can use this same data connection.

Hope this helps.

[Levi_Turner · Accepted solution]

@fosuzuki is pretty spot on, but to give my take:

> Where does Qlik cache the data? I broke the link between my desktop and the SQL server, but apps didn't have any issues loading the data, so this data must be cached somewhere.

Qlik apps live on disk. These apps contain both the data layer of applications as well as the visualization layer. When a user accesses a Qlik app they are accessing a file on disk through a web client.

> If I add a LOAD statement into my ODBC connection to MS-SQL, when will Qlik perform a data reload? Quite concerned about this as the initial data loading from MS-SQL takes a very very long time.

Whenever you reload the data. Either manually (Desktop or Server), or through a scheduled task (Server). If load times from source are a concern, then approaches like incremental reloads are attractive, so that only the diff from what the app currently has are pulled from source.

> Would running this setup result in smaller qvf files? Since the source data doesn't reside locally on the client. (This sorta ties in with point 1.)

Not sure what you're getting at. See my answer above.

> If an app is accessed by 2 concurrent users, or if 2 apps are accessed concurrently, can they all use the same database account credentials?

Qlik apps only talk to source on reload. So the to concurrent users would be accessing a file on disk which is served through a web client.

[Johnny]

@fosuzuki @Levi_Turner 

Thanks for the reply! Regarding point 2, I was actual referring to this . Since you're running a sql load statement everytime you open the app, would that cause the app to reload every single time it is opened?

[jonathandienst]

The LOAD statement does not run every time you open the the app. It is only executed when the app is reloaded, as already explained above. Typically this would be an overnight scheduled task on the server and it will be run under the credentials of the QlikSense server service account.