Wednesday, May 12, 2010

Gathering data from multiple databases

I am working on a Qlikview project for a client who is using an ERP system that maintains a separate database for each site that is using the system. I think there are some regulatory requirements behind this but the reason why is not terribly important, my job is to consolidate their data into a single Qlikview document. As I was thinking about this I was dumbstruck by the possibility of maintaining three versions of my code with just a simple change in the database name. The possibility to have data inconsistencies and errors are high and I'd rather not risk it.
So I started looking at the Qlikview script and how I can use data to drive what data source will be used. I found that you can substitute parts of your SQL select with a Qlikview variable so this is the basis for my solution.

Set vdb = 'BlahBlah';

Employee:

Load *;

Select * from $(vdb).dbo.employee;
If you work with databases very much then the idea that you can easily alter your SQL queries by using a variable should get you at least a little excited. So I hope this next part will send you over the edge.

I am going to start by loading a list of databases I want to load from.

DatabaseList:

Load * INLINE [

DBName

USA

MEX

GB

];

you can use whatever source you want for this, even a query from the system tables but for simplicity I am just going to use an inline list.

So next you might want to define the tables that you want. Notice that I include the owner, this example is using SQL Server I'm sure there are variations needed for other RDBMS.

TableList:

Load * Inline [

TableName, owner

customer, dbo

customerorder, dbo

];


So now I am going to loop through the lists and pull in the data I want.

let vdbCount = NoOfRows('DatabaseList');

for db = 0 to vdbCount - 1

Let vDBName = Peek('DBName',$(db),'DatabaseList');

Let vTableCount = NoOfRows('TableList');

for t = 0 to vTableCount - 1

Let vTable = peek('TableName',$(t),'TableList');

let vOwner = peek('owner',$(t),'TableList');

If db = 0 then

$(vTable):

Load *;

Select * from $(vDBName).$(vOwner).$(vTable);

Else

Concatenate ($(vTable))

Load *;

Select * from $(vDBName).$(vOwner).$(vTable);

end if

next t

Next db



You'll notice that there are some variations in the code to create the new tables and force the concatenation. I did this because invariably you will find that the databases will be out of synch and if the columns in the tables differ then Qlikview will not automatically concatenate them. If it does not concatenate the tables you will generate some HUGE synthetic keys and if you have any significant data volume you'll be crashing your system.

In conclusion I think this code is pretty simple and could save you a ton of time if you have to perform the same operations against data from multiple systems. I think there are a ton of applications for this technique. It is important to note that in my example I am using the same database connection but clearly you could use this technique to define your connection strings too so you can hit databases on multiple servers.