BPMonk
asked on
Differences between Essbase and a DataWarehouse
Hi all... I am a SQL/Report Developer a bit of VB and VBA here and their. However I am going to be starting my new role soon which will be that of DBA. The main aspects of the job will be to implement SQL Anlytical Services for OLAP. Now here is my question, the only kind of OLAP tool I have had exposure too is Oracle Essbase and only small exposure at that. As I understand it, data in an OLTP goes into the Warehous and it goes in calculated etc so its geared for heavy reporting. Why then do you need Essbase ? Cant one just use a report write agains the warehouse, what is the difference between the 2. I know Essbase is a MDBMS so why do we need the warehouse, why not just bypass the warehouse and have the OLTP feed into Essbase.
ASKER
Thanks...does give me some insight... However, the main question is, why not just use a report writer to query the DW? Is it because Essbase and other OLAPs store the data in cubes therefore it makes the report much more intuitive for end users to slice and dice? Data in the DW is not stored as Cubes is it ...???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
However, I supsect your real question has to do with "Why OLAP instead of OLTP". And that's an interesting question. The difference has as much to do with philosophy as anything; OLAP supports MultiDimensional reporting, which enables a different (and from an analytical point of view, more powerful) way of looking at your data. OLTP systems are geared towards single bits of data, with good database design enabling you to express more informaiton with less data through normalization, which minimizes the amount of storage, bandwidth and errors. OLAP, on the other hand, is geared towards "sets" of data, largely ignoring the individual data elements. Good OLAP design creates heirarchies of data with the values of the "measures" precalculated at each level so as to minimize the amount of data that needs to be read to get a answer to a question.
Which then begs the question, what is a data warehouse? No clear answer on that one. You could pump the data direclty into essbase (or whatever OLAP tool you choose to use), but the amount of transformation that is usally needed to turn OLTP data into OLAP data makes this risky as a one-step processes. Typically, you don't want the extract from your transactional systems to be impacted for a long period of time, so you want to be able to pull the data out as quickly as possible. Most data warehouses represent this "middle" step, holding the transactional data in a somehwat denormalized and conformed form. This data can then be processed by your OLAP systems into the reporting/analytics database over a longer period of time without impacting the transactional system.