SQL Server Performance

Merging of OLAP and OLTP data

Discussion in 'SQL Server 2005 Reporting Services' started by SQL2000DBA, Oct 11, 2006.

  1. SQL2000DBA New Member

    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
    SQL2000DBA, Oct 11, 2006
    #2

  2. Madhivanan Moderator

    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
    Madhivanan, Dec 28, 2006
    #2

  3. madhuottapalam New Member

    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
    madhuottapalam, Dec 28, 2006
    #2

  4. satya Moderator

    satya, Dec 29, 2006
    #2

  5. MohammedU New Member

    MohammedU, Dec 29, 2006
    #2

  6. Madhivanan Moderator

    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
    Madhivanan, Jan 1, 2007
    #2

Share This Page