Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do I obtain detailed reports through SSRS on an OLAP datamart? Expand / Collapse
Author
Message
Posted Thursday, July 31, 2014 9:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2014 10:25 AM
Points: 5, Visits: 85
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.
Post #1598352
Posted Friday, August 1, 2014 2:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:26 AM
Points: 137, Visits: 172
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
Post #1598613
Posted Friday, August 1, 2014 6:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2014 10:25 AM
Points: 5, Visits: 85
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?
Post #1598664
Posted Friday, August 1, 2014 7:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:26 AM
Points: 137, Visits: 172
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
Post #1598676
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse