Multiple DB's - duplicate Apps or Data Reduction

[#1]

Hi there,

I've been doing a fair bit of research of deployment models when it comes to Qlik Sense, but thought I'd see if anyone had some insight my query direct, rather than scenarios that don't quite fit ours.

In our scenario, we have multiple data sources (SQL DB's 70+) with the same schema but different data.

One of the methods I have been looking at is ti create a 'master' app and duplicate it per DB< providing a dynamic text load for the connection string in which I would specify which database to connect to, or another method to get the same affect (connect to a specific database dependent on a particular setting/file/variable in the app).

The other method is Data Reduction. I don't need to go into much detail here, other than it would take a user attribute such as a Source Database value from the authenticated use and apply that when accessing data in the model. The model itself would have to have multiple connections for every database.

From my point of view, I have a few pros and cons of each:

Duplicating App:

PROS: Completely segregated data, no accidental cross over due to 'missing' data reduction rules, small app sizes

CONS: Additional overhead in duplicating apps (or at least creating a process to do this), not much documentation

Data Reduction:

PROS: Single app, already well documented

CONS: Much larger QVD size could be problematic, accidental cross over due to missing data reduction rules

I was wondering if anyone had any thoughts or insights as to best practice, or past examples or similar scenarios.

Many thanks.

[rohitk1609]

Hi James ,

I have a great curiosity to know , how that part of your all is working as when u publish your app to clientA stream it replaces the string dynamically to clientA Qvd , can u please show me the sample code or anyhow to make me understood

[Anonymous]

What I do in that scenario is create a table [using a database, xls, csv, txt... whatever you fancy] and in that have one row for each data source with its name and data connection.

Then in the script I loop round for each row running the load script and creating a discrete QVD for each data source.  Then for the dashboards I load the QVD's I want, or more often all of them with a wild card load.

[rohitk1609]

Hi bill ,

It will be great help if u show me the sample code or script so I can understand easily , I am confused with how to implement your logic and the other loop logic by scripting.

[#5]

Yeah, that's the first tier - the QVD generator. I actually found a script here (Generic Script for QVD Generator) that I amended for my own needs.

My version first queries our customer table, but still has an inline load for the list of tables. The main bulk of the script is 3 loops:

FOR iServerCount=1 to FieldValueCount('servername')

  LET vServerName = FieldValue('servername',$(iServerCount));

  LIB CONNECT TO '$(vServerName)';

  FOR iDatabaseCount=1 to FieldValueCount('databasename')

    LET vDatabaseName = FieldValue('databasename',$(iDatabaseCount));

    FOR iTableCount=1 to FieldValueCount('TABLENAME')

      LET vTableName = FieldValue('TABLENAME',$(iTableCount));

      $(vTableName):

      SELECT * FROM [$(vDatabaseName)].[dbo].[$(vTableName)];

      STORE $(vTableName) into [lib://$(vQVDPath)/$(vWebaddress).$(vTableName).qvd] (qvd);

    NEXT iTableCount

  NEXT iDatabaseCount

NEXT iServerCount;

I've omitted a few bits that are specific to us, but this covers the logic part. First loop through the servers and connect to it (I created a connection per server with the same name). Then loop through the databases of those clients on that server. Then finally loop through the inline list of tables to generate a QVD per table. As i said, I took the Generic Script for QVD Generator and tweaked it for my needs.

Hope this helps.