Combining multiple Database Applications for Congruency in Reporting

I have two database applications. One that terminated in the 3rd week of January 2013 and the new one that picked up that same week. The database tables and fields are not the same and they need to be combined for reporting purposes. What are the best practices in order to combine them with SQL Server.
For instance if I have to combine two different fields from two different applications into one field for reporting purposes how do I do that is it even possible and what affect does the datetime stamps affect the outcome. I understand some SQL just have never combined them before. Any help advice documents video how to's appreciated.
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>The database tables and fields are not the same and they need to be combined for reporting purposes.
Normally this would mean a separate reporting database (datamart, data warehouse) where data is mapped from the source database(s) to this one, and an SSIS package is created and executed daily/weekly/whatever to populate this reporting database.

Good luck.  

>One that terminated in the 3rd week of January 2013
>the new one that picked up that same week.
If this is the same application, maybe the decision you need to make is whether or not the old data can be inserted into the new database, therefore making the new app/database 'backwards compatible', and reporting can be done from just the new database.
0
 
mlmccCommented:
Are these really just separate tables in the same database?

Can you build a query n the database?

If the answer to both is yes, you could write a view/query or stored procedure that does a union of the 2 tables and allows for a date range

mlmcc
0
 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
Are these really just separate tables in the same database? YES
One is a product called Macola....and the other is a product called RAMS. They are both Microsoft SQL Server. From two different network servers.

Can you build a query n the database? YES

So you are saying that this can be done with an Stored Proceedure or Query that is simply a union.

How does it know
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
mlmccConnect With a Mentor Commented:
If they are on 2 different servers they are not in the same database.  They are on the same engine.

If they were in the same database you could use a union query like

SELECT listof fields FROM MacolaTable
UNION ALL
Select samelistoffields FROM RAMSTable

Will a subreport solution work?

Mainreport shows all the data from Macola and the subreport shows RAMS data

mlmcc
0
 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
They are on two seperate databases ..... you are correct.....they are on the same engine...SQL Server 2012.
So is it not possible to use a Stored procedure and create a union between the two databases...?
0
 
mlmccCommented:
Are the 2 database s on the same server or different servers?
You indicated they are on 2 different servers.

I don't know enough about linked servers to know if you can write a SP that pulls from both servers

mlmcc
0
 
Kurt ReinhardtConnect With a Mentor Sr. Business Intelligence Consultant/ArchitectCommented:
Ultimately, the best solution would be the one recommended by Jim Horn - to have a separate reporting database/warehouse in which you've combined data from the various data sources into one set of merged tables, typically with some sort of field that identifies which system generated the data.

Since both databases are hosted by SQL Server, you could easily create queries in one system that pull in data from another--assuming your DBA gives you the ability and permissions to do so--but is this the right long-term solution?  It can be done, but performance may not be great, depending on the size and complexity of the data and the code required to get the two separate chunks of data to appear as one.
0
 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
We are going to go with Stored procedures and that seems to work well for this application. Thank you gentlemen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.