July 31, 2014 at 9:56 am
I am accustomed to writing detailed reports from my OLTP databases. Piece of cake and I have no problems with this.
Now we are putting in our first datamart and I am at a loss.
1. Will I be able to use the datamart dimensions and facts to produce the same 'detailed reports' as in my OLTP environment?
2. Will I be able to use the cube dimensions and facts to produce the same 'detailed reports' as in my OLTP environment?
If 'no' to the answers above please point me into a direction which will assist with adding the needed clarity in my thinking.
August 1, 2014 at 2:07 am
the simple answer is that the datamart/datawarehouse must contain raw detailed records, otherwise you can't create the OLAP cube
the OLAP cube should only contain summarised data, otherwise they become slow to build
the whole point of an OLAP cube is that it is pre-summarised data created during the daily build from the datamart/datawarehouse
the ETL process then becomes
OLTP --> staging --> datamart/datawarehouse --> OLAP cube
when writing reports you use the datamart/datawarehouse for detailed ones, e.g. show me all the line items ordered by a particular customer
and you use the OLAP cube for summaries, e.g. total sales by city for the last six months
see http://www.kimballgroup.com/ for advice on datawarehousing
regards
Andrew
August 1, 2014 at 6:57 am
Thank you for your response it has been helpful.
For additional clarity, if I wanted to create detailed reports(operational reports) I would have to create an ODS or use staging tables to do that?
August 1, 2014 at 7:19 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply