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.
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
RUA Volunteer2?Tableau 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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

mlmccCommented:
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
RUA Volunteer2?Tableau 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 ReinhardtSr. 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
RUA Volunteer2?Tableau 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.