Home Forums Data Warehousing Analysis Services How do I obtain detailed reports through SSRS on an OLAP datamart? RE: How do I obtain detailed reports through SSRS on an OLAP datamart?

  • not sure what you mean by ODS (operational data store?), if you mean it to be the same as a datawarehouse, i.e. bringing together data from various systems, e.g. finance, stock, transactions, then your detailed (operational) reports, that need to report the situation as at now, would contiue to be against the various OLTP e.g. how many of this item are in stock, in the warehouse or shops, now

    the reason is you normally only run the ETL process once a day, normally just after the close of business, so the datawarehouse is a snapshot at the time you ran the ETL process so does not get updated throughout the day so does not show the situation now

    one of the purposes of a datwarehouse is to optimise it for reporting so summary reports are not run against the OLTP system thus reducing the load on that system, also with OLAP cubes, because they are summaries you reporting can be lightning fast

    you would not normally run any operational reports against the staging tables, but you might run some data integrety reports if you suspect the ETL process did not work correctly e.g. what did the ETL process do last night, but you can only do that if it does not get cleared down at the end of the run

    other reports you would write against the datawarehouse would be comparing individual items over time, e.g. how did a particular item sell on the same weekday last year

    once you have the datawarehouse/datamarts and OLAP cubes setup then you can migrate reports form using the OLTP to using these