Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How do I obtain detailed reports through SSRS on an OLAP datamart?


How do I obtain detailed reports through SSRS on an OLAP datamart?

Author
Message
rick.servant
rick.servant
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 157
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.
andrew_dale
andrew_dale
SSC-Addicted
SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)

Group: General Forum Members
Points: 468 Visits: 687
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
rick.servant
rick.servant
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 157
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?
andrew_dale
andrew_dale
SSC-Addicted
SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)

Group: General Forum Members
Points: 468 Visits: 687
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search