Management Reporting from various dbs - what is the best approach?

[ikel]

Hi,

I am working in a company that utilises many systems with their own databases. For example, the cafe has its own CAFE system with its own database (it could be postgresql). The Health Care system has its own database system (it could be MS SQL). The Transport booking has its own database system. And another system uses No-SQL database.
I am faced with a task of producing web-based management reports to allow managers to see key performance indicators on screen.

What would be the best way of accomplishing this?
I would perform cross database queries of the key performance indicators of the company. But I feel that there are things that I haven't taken into an account.

Has anyone done something similar and able to give me suggestions?

[rch1231]

My suggestion is to set up a server with a database you are familiar with and then regularly export the data from the various databases and import it to your master system. I have done something like this and I normally use PERL to generate the export files (normally a CSV since every db will create a CVS file) and then import them into a MySQL database. Once you have all of the data in one place then you can start generating reports. This way your reports will have a similar format and you only deal with coding for one database and can use some of the same code. Another advantage is that you are not dealing with the actual data in their database and don't have to worry about accidentally changing something.
Hope that helps.

[ikel]

@rch1231,

Thanks for sharing your approach. It sounds like setting up a data warehouse, but simpler. *"You are not dealing with the actual data in their databases and don't have to worry about accidentally changing something." *Well, yes!

[pritaeas]

This was something demonstrated to me by a coworker last year. Have to say that it was pretty easy to work with, although importing your data is a bit more work.

[ikel]

@pritaeas,

I had a look at Qlik. It says that it does not require a DW as it aggregates from various dbs. I'll have a look in more detail later. (Although it may be a bit difficult to aggregate in the first place as you will be dealing with 100s of tables that you don't want to see, which was a necessary steps prior generating reports from certain tables only).