Hi,

we have an OCR software with an underlying sql database. A customer of ours will run several instances of the software, each one with its own database. The different databases have the same schema.
All these databases will stay in the same server.

I have to design for this customer a report application for this set of databases, which will be executed constantly during the day.

The first question is: can the reporting application (let's say it is written in crystal reports) query from the different databases or the performances are too bad (the software constantly reads/writes from/to the db) and so it is better to move all the records from the different databases into a central reporting database, where the reporting application will read locally?

If the records should be moved, I need to distinguish between where they are coming from; so the hypothetical reporting database schema tables will slightly differ from the source tables, since they needs an additional column to identify the source database; can such a transfer be done using existing tools, or a separate application should be developed, since the tables have not exactly the same structure?

Thanks,

Stefano

I would go for copying the data from the individual databases into a central reporting database. If you're reporting from a continually changing data source then you're never going to be sure what you're looking at - rerunning a report 5 minutes later than the first could give you different results.

You could create an SSIS job that will pull all of the data, add an identifier to say where the data came from and then insert it to the reporting database.

But to do this, you need to be able to identify the rows that you have already copied so you don't add duplicates. If you've got a unique identifier (such as an ID or a datetime stamp) then checking to see if they already exist in the reporting database can be done by SSIS.

This is basically the concept of a data Warehouse ETL process.

I have 6 instances of Oracle 10g/ 11g and 3 instances of 2 db sqlserver 2008.
With crystal reports 10, i am to generate a level of reports which some times is a pain . But normally i can remain log-in at all the datasources at once.
The reports i create by taking fileds* from some procedure or table originally denotes to the db-instance it belongs .

the same apply when i cross reporting comparing all or some instances in a single report by taking multiple data fields .

crystal reports takes a lot of "reads/writes" a single datasources ( if u make one single source db) .
to me it "appears" as if it have nothing to do with the database or datasource . it is the software which burdens, its handler for datasource burdens for single source.

however in case of multiple datasource it performs well .

Hi,

thank you for your replies.

I am considering the SSIS solution.
I would like to have the data migration periodically (let's say every 10 min).

I would like to copy only the record not already in the destination report database.
Should I filter the records in a sql query not copying the primary keys already in the destination table, or there is a better way?

furthermore, I would like to reflect the source database records changes in the destination database corresponding record.
I can't think a way to to this rather than adding a timestamps column (let's call it LastModified) for each table in the source database (let).

Is this the right way?

I think you are going down a way-overkill solution without having demonstrated that the simple solution would not satisfy your needs.

I'm much lower tech than most people, so in my mind, to get this project done, you need to write one query, replicate it three times, change some database names in the query, and your basically done.

Now, there is a concern as to whether or not this query will impact operations of the database.

Why not test it out?

How complex could writing one report query be? An hour? Two hours?

Write the query and execute the thing as often as it is likely to be executed in the real world and see how the system performs.

Dollars to doughnuts, no one will ever notice the query running (if it is written properly).

Hi,

thank you for your replies.

I still don't know what is better. Again, the scenario is the following:
in a single sql server I will have several databases (same schema), one for each different customer. A single instance of our software is continuously reading/writing on one of the databases. Let's say we have 10 customers running 10 software instances, each one working on 1 database, for a total of 10 databases.

Now we want to report (let's say using Crystal Reports) against all of the 10 databases and the main decision is whether reporting directly against the 10 dbs at real time, or replicate somehow the information in a dedicated reporting database and report locally on that new dedicated database (the tables have almost the same schema, they just need an extra columns to know where they are coming from).
I was thinking on this solution because I thought it is better (for performances) reporting against a local database rather than against multiple ones, and because the new database can work as an historical records storage as well, independent from what is currently in the databases.

The pros of a direct reporting on the 10 dbs are
- simplicity
- no needs of replicating data
The cons are
- the queries might slow down the software performances in each system
- the report queries might take a lot of time

The pros for the second solution are
- the reports will be on a dedicate local database with better performances
- the report queries won't slow down the software performances
- the report database can be used as an historical record storage
The cons are
- the report database needs to be constantly updated; only the new records should be inserted, but all the existing records that change in the source database must change accordingly in the report database.

This 2 operations seem to be expensive; to modify an existing record changed in the source database I guess I need a new time stamp column in every source table (a LastModified date column), and I don't like this solution. I don't think I can use synchronization mechanisms between the source dbs and the report db, since the schema is slightly different (let me know if I am wrong).
This was the reason why I was considering using SSIS, but the record update is still a problem.

So, should I try reporting directly against the 10 database using Crystal Reports and see what happens, and discarding this option only if performances are degraded?

Thank you in advance,

Stefano