Reporting From Multiple Databases

By February 23, 2011For Developers, Tips

We often encounter customers that need to report on data that comes from multiple applications. This capability can give business an edge their competitors lack by enabling them to respond instantly based on an integrated view of their business. Unfortunately, traditional database configurations and protocols are designed for a bygone world where you buy everything from a single vendor who puts all your data in one place. The good news is that there are several low-cost or free options to get all your data together for easy analysis.

Switching Databases

Izenda supports switching between databases via the AdHocSettings.SqlServerConnectionString property and an Oracle equivalent. The integration involves adding a database selection element to the UI and having it apply the appropriate connection string when the user wants to switch databases. This method does not support actually joining the data sources together into a single report or dashboard.

Combining or Joining Multiple Databases

In some situations, users need to actually join data contained in tables which reside in different databases.  This requires configuring the databases to work together or replicating one of the databases. There are many ways to do this and the right approach depends on your requirements and configuration. Currently ADO.NET is not capable of connecting to two databases with a single connection string. As a consequence, all the data needs to be accessible from a single connection string. There are several ways to do this.

Views

Create a view in one database that pulls records from another. This is accomplished using the double dot notation.  So giving database A access to table T in database B you would create this view in A..

CREATE VIEW T AS SELECT * FROM B..T

OpenQuery and Linked Servers

SQL Server allows for pass-through queries to other databases.

Sql Server Integration Services (SSIS)

Microsoft includes basic ETL capabilities which developer can use to copy specific tables from one table to an other on a scheduled basis.

Sql Server Replication

The latest versions of SQL Server support replicating data in  near real-time manner.  This works very well for situations where yesterday’s data is not timely enough and can be used to migrate data from several systems into a single database using a subscriber model.

Sync Framework

Microsoft’s new sync framework is designed to support data synchronization in a cloud-oriented world.

 

 SqlServerConnectionString