Post reply

Reporting on data from three databases

  • August 7, 2007 at 7:05 am

    #175817

    I am trying to report on real time data from three different systems that all contain a subset of out asset managment system. Can anyone suggest a way of being able to pull data from all three systems and place into one report that will be able to exported to excel as one data set?

    I have looked into using temp tables and views but have hit some performance issues. If anyone can point me in the right direction it would be appreciated, otherwise I am thinking that I will have to go back to my users and tell them that we will have to use data that is refreshed at a regular interval into a reporting database.

    Cheers

  • August 7, 2007 at 7:08 am

    #725122

    John,

    If the three databases are all on one server then there should be no serious overhead of doing a cross database query.

    How much data are you trying to report on? Is your reporting query as optimised as it could be?

    James

    --
    James Moore
    Red Gate Software Ltd

  • August 7, 2007 at 7:21 am

    #725128

    If you're users want real time data when they open the spreadsheet, don't export the data as an "Excel file"... create a spreadsheet that uses "External Data" from a view.  If you're having performance problems with the view, post it so we can have a look see.  Don't forget to post the structure of the underlying tables, as well.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 8, 2007 at 4:48 pm

    #725553

    What kind of performance issues? Can you get the individual queries to the differents systems to work ok? If on different servers, add them as liked servers onto 1 of the boxes, then create a view that unions the different queries together.

  • August 10, 2007 at 7:46 am

    #726013

    SSIS has Merge and Merge Join data transformations that might also come in handy here.

  • September 4, 2007 at 5:12 am

    #731158

    Many thanks for the replys guys, sorry I did not get back in here sooner but things have been a bit hectic. I have gone down a route where I am pulling into a reporting db every two hours or so and reporting from there.

  • September 5, 2007 at 2:02 am

    #731417

    John

    what about a crystal report using sub-reports?? I use these as I have four databases that I have to run reports over, and using variables I can use the data from each sub-report to make relevant calculations

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply