Data from more databases (connect to databases or datawarhouse)

[francisvandergr]

I have a question. We have a qlikview customer who has more databases and the next years they will grow fast. I want now make a good decision before build in qlikview. The reports we gonna make in qlikview are mostly data combined from more databases. For example we have the following databases :

001 Netherlands, 002 Belgium, 003 Germany, 004 United States. For the reports we sometimes want data from 1 administration, and often we want data combined from all administrations or some administrations.

CONNECT

TO

[Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=002;Data Source=NB-FRANCIS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=NB-FRANCIS;Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False];

Of course i can get all tables from database 002 now (initial catalog). I can get the tables:

Customers,Countrys, Orders, etc.

But when i want this for all administrations. So i can repat the script for all administrations.

Is there an easier way to do this for example to build a datawarehouse with data from all administrations.

[pover]

I suppose it depends on the ODBC, but the initial catalog is usually optional and you can always refer to a different database directly in the SQL statement. You only have create a new connection string to connect to a different server and you can concatenate similar tables from different servers without any problem in the same script.

Regards.

[francisvandergr]

Hi Karl,

I have the solution : We build in a loop. Thanx for your time!

FOR

EACH admin IN

$(Administrations)

Orders:

LOAD

Boekjr as Orders\Boekjaar

,

periode

as Orders\Periode

,

debnr

as Relatie\ID

,

// "cmp_name" as Verkooporder/Naam,

// ClassificationId as Verkooporder/ClassificationID

// "cmp_fctry" as,

artcode as Artikelen\Artikelcode

,

Description

as Orders\ArtikelOmschrijving

,

Code

as Orders\ProductgroepCode

,

"u_productgr"

as Orders\ProductgroepOmschrijving

,

omzet

as Orders\Omzet

,

aantal

as Orders\Aantal

,

marge

as Orders\Marge

,

"vvp_s"

as Orders\VVP

,

admin

as Orders\Administratie

,

naam

as Orders\RelatieNaam

,

PurchaseCurrency

as Orders\Valuta

,

ordernr

as Orders\Ordernr

,

faknr

as Orders\FactuurNummer

,

"orginele regel kostpr"

as Orders\Originele_Regel_Kostprijs

,

costprice

as Orders\Kostprijs

,

"Huidig jaar"

as Orders\HuidigJaar

,

fakdat

as

Orders\Factuurdatum;

// artcode from 1 for 1 as BudgetUnit;

SQL

SELECT

*

FROM

[$(admin)]

.dbo.VwhvOmzAlg;

NEXT