Database stress by having different Qlik Apps using same database?

[RogerPenna]

I am new to Qlik and Bi and databases in general. 

But I know many BI systems will need the so called Data Warehouses, where as I understand, you will do an transform and loading of several different databases from your company into a 2nd database, designed specially to be analyzed by the BI system.

That way, you only do the loading of each of your systems database a single time, and the BI will only connect to that Data Warehouse, not stressing anymore the main database of each system. 

Is that correct?

I also hear Qlik works a little different, connecting directly to each database, and it's "data warehouse" is the App file (.QVF), right?

But let's say my company has 4 QLik Sense Desktops installed. Each of them has the same "shared" (sent through email) Qlik App, plus different apps in each desktop. But all of them connecting to the same ERP and BPM databases.

Won´t the load on the databases be TOO big? Every app connecting and requesting "selects" from those 2 databases?

[dplr-rn]

Read up on 3 tier architecture and qvds.
Basically there are 3 type of Qlik apps
Extract - direct simple query to do and save as qvds
Transform -use qvds to transform and combine data as you need. Store results into other qvds
load - load the transformed qvds into your main app which will have visualization s

Basically the concept is hit database only in the extract layer and reuse it.
There s a lot more benefits to such an approach and typically recommended approach for most big Qlik deployment s

Forgive typos - using my phone

[RogerPenna]

I am not sure I follow you.

If there are 3 type of Qlik apps and the advised approach is to only hit the database with tier 1 apps (the ones which do extract), does that mean other apps (tier 2 and 3), spread around other QLik Sense Desktops, should connect at that single TIER 1 app?

[ganapati_h]

See the attached diagram on the different data architectures.

Ideally you should use Qlik Sense Enterprise.  With Qlik Sense Enterprise you can run task for extraction, transformation and qvd creation. The apps(qvf) will then reload itself on qvd data.

If you use QS Desktop then all desktop should have access to qvd and will have to manually refresh the dashboard.

In Qlik the data is in RAM so there is no querying every time  to back end data sources.

Hope this helps.

[RogerPenna]

Ok, I guess I am starting to get it. I did not realize there were QVD and QVF apps.

Ok, so let's say I load data from two different databases (several tables) and from an Excel file.

If I go to save it right away, it will save as a QVF, which is a THIRD LAYER app.

So instead I should create a QVD. ( https://community.qlik.com/t5/Qlik-Sense-App-Development/How-to-create-the-QVD-files-in-QlikSense/td... )

Creating a library from the right panel in the data load editor...

then I add a command to store the data as a QVD file.

STORE file INTO [lib://QVDFolder/file.qvd] (qvd);

is that right?


Ok, it was saved into the Library... and I can use that QVD as a source for QVF apps. (same type of connection procedure as when selecting Excel files)

Er... just... the QVD was saved in the LIBRARY, which is not a real file path.

How can apps in other desktops use the same QVD?

I mean, if I can´t use a single QVD as source,  what's the purpose? I will still have to make multiple loads from the ERP database, one for each desktop, right?

[RogerPenna]

Btw, is it possible to program the QVD to connect to a database and extract data only at a specific time, or time interval? So it doesn´t harm the ERP usability for example.

[ganapati_h]

Hi Roger,

You can writer extractor(qvf) files which will have script to connect to database , bring data and store into qvd. You can also write incremental script which will only fetch changed records from database and add that to qvd. This approach further reduces the load on database.

If you are using Qlik Sense Desktop you will have to reload extractor manually. If you have Qlik Sense Enterprise you can setup task for running the extractor.

Regarding  your earlier question if you qvd are store in a shared folder which can be accessed by other desktop(these could be mapped as a drive e.g  T:\sales\sales.qvd) . So the QVF on all desktop when refreshed will use the qvd from shared location and connection to erp/excel/etc is not needed.