locked
SSRS for Operational Reports RRS feed

  • Question

  • Hello All,

    What is the best approach for deciding the  reporting source,if we want to report   transactional (operational)reports,if we have large number of complex queries with lot of parameters,

    Also if we create the query parameters and filter the data at the source and the users continually execute the reports with different parameters  ,the whole process will become an adhoc approach,and i believe it will affect the database performance.

    If we should use  transactional db as the source should we use the NOLock hint or settting the isolation to read uncomitted.

    If  we cant toch the transactional  database ,what is the alternative approach ,is it good to create snapshot on mirror database and use it as the reporting source or is it ok to create a replicated database for reporting source.

    Many Thanks



    • Edited by Vision2040 Tuesday, July 17, 2012 9:00 PM
    Tuesday, July 17, 2012 8:59 PM

Answers

  • Hi Vision2040,

    You wanted to create your report directly from the operational database. Is your goal to do this is to get the latest values on the report? If yes then there is no better way other than directly hitting the operational database.

    NoLock: Yes, you must use it in this case because operational db may be used by any other front-end application interface and may get issues. But be ready to get the wrong results in your report as far as values are concerned.

    Complext queries lots of joins and lots of parameters: this could slowdown both - your reports performance as well as your ODS.

    Snapshot: Yes, this will be more performant for fetching data for your Reports but does not guarantee to get the latest vaules.

    Idea:
    1. You can create a DataMart where you fill the data from your ODS on hourly basis (its just a hypothetical situation, you can design your own timings).
    2. In Report Project, Create 2 DataSources/Connections - One to DataMart and Second to your ODS.
    3. In Report, create a parameter e.g. "NormalData" or "LiveData". So when you select "NormalData", the Report should display data from DataMart (which could be older data as compared to live) and if you select "LiveData" then hit the ODS and get the latest data.

    There are many ways you can design this system, but if you makeup your mind to connect to ODS then all associated pros-cons are bound to come.

    Thanks, Khilit
    http://www.bigator.com

    • Marked as answer by Vision2040 Wednesday, July 18, 2012 11:07 AM
    Wednesday, July 18, 2012 3:26 AM

All replies

  • Hi Vision2040,

    You wanted to create your report directly from the operational database. Is your goal to do this is to get the latest values on the report? If yes then there is no better way other than directly hitting the operational database.

    NoLock: Yes, you must use it in this case because operational db may be used by any other front-end application interface and may get issues. But be ready to get the wrong results in your report as far as values are concerned.

    Complext queries lots of joins and lots of parameters: this could slowdown both - your reports performance as well as your ODS.

    Snapshot: Yes, this will be more performant for fetching data for your Reports but does not guarantee to get the latest vaules.

    Idea:
    1. You can create a DataMart where you fill the data from your ODS on hourly basis (its just a hypothetical situation, you can design your own timings).
    2. In Report Project, Create 2 DataSources/Connections - One to DataMart and Second to your ODS.
    3. In Report, create a parameter e.g. "NormalData" or "LiveData". So when you select "NormalData", the Report should display data from DataMart (which could be older data as compared to live) and if you select "LiveData" then hit the ODS and get the latest data.

    There are many ways you can design this system, but if you makeup your mind to connect to ODS then all associated pros-cons are bound to come.

    Thanks, Khilit
    http://www.bigator.com

    • Marked as answer by Vision2040 Wednesday, July 18, 2012 11:07 AM
    Wednesday, July 18, 2012 3:26 AM

  • Thank you Khilit,

    In terms of parameters what approach is the best  solution when we are judging performance   ,should we create the parameters in the (query or StoredProcedures) or should we pull the entire dataset and filter  using the report parameters ,how we should decide based on the type and usage  of the reports 

    Many Thanks

     

    Wednesday, July 18, 2012 11:07 AM