Merging of OLAP and OLTP data | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Merging of OLAP and OLTP data

Hi, Please provide me your inputs on whether we configure one reports to get data from OLTP and OLAP(Cubes) Database.For example, a weekly sales report contains data in two section i.e. Current Week Sales and Last Weeks sales. My Last Weeks Sales are existing in OLAP database and my current week is present in OLTP. Can i display both the data in one report. If yes then how ? Thansk and Regards
Ravi K

I think you can create a procedure that combines data from two databases and use that procedure as datasource Create procedure showData
as
Select columns from OLAP.dbo.table
Union all
Select columns from OLTP.dbo.table
Madhivanan Failing to plan is Planning to fail
I don’t think the above said procedure will work. The problem is u can not get the Cube data from OLTP procedure. Acutally u need One portion of the report should populate with Aggregated data(ie from Cube) and the other with Current Transactional Data(OLTP). What u can do is make two report one connect to OLTP and the other to OLAP and use one as sub report. Madhu

I think there is a section in BOlhttp://msdn2.microsoft.com/en-us/library/aa933151(sql.80).aspx for more information. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
What about creating the linked server to SSAS AND run the openrowset or openquery commands to get the data from OLAP and union the data with from db… http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx
Mohammed U.
quote:Originally posted by madhuottapalam I don’t think the above said procedure will work. The problem is u can not get the Cube data from OLTP procedure. Acutally need One portion of the report should populate with Aggregated data(ie from Cube) and the other with Current Transactional Data(OLTP). What u can do is make two report one connect to OLTP and the other to OLAP and use one as sub report. Madhu
I have just given as idea of how to do it assuming that data are available in the single server Madhivanan Failing to plan is Planning to fail
]]>