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.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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 ...???
It depends on what you mean by "Cubes". THere are several different "Flavors" of OLAP. When you talk about "Cubes", you're usually referening to MOLAP (Multidimensional OLAP). Essbase is one of the "classic" MOLAP providers. In fact, Essbase was developed as an extension to the standard spreadsheet. Relational database can usualy be represented as "lists" of rows (at the simpliest implementation, a simple CSV could represent a database table). Cubes, on the other hand, store the data in a mulltidemsional format. Different vendorshave their own storage methods, but the end result is that it's easier to go in and get a specific value without needing to scan through the whole dataset.
So chances are, the DW is simply a relational database organized in a star schema, and to use essbase, you have to pull that data out to store it in Essbase's multidimensional format.
Many vendors (like IBM's data warehouse extension or MSSAS) also support "ROLAP" (Relational OLAP), which at a simple level just creates a lot of indexed and materialzied views on your datawarehouse. Increasingly common is HOLAP (Hybrid OLAP), which combines multidimension storage (usually for historical data and dimensional information) with Relational storage (for more current fact information) to allow for both fast historical analysis and more real-time reporting (popular for dashboards).
The difference between Essbase and a data warehouse is quite profound. While some businesses (small) might use Essbase as their "data warehouse" it is not a data warehouse at all. Data warehouse is a term that gets thrown around way too much. So let's say a data warehouse is a relational database, used to store historic snapshots of data to support management reporting that you can not get from disparate legacy transactional systems. Now many would argue with that definition, but it's just a round about one. Data warehouses are usually very large and not easy for non-IT users to go in and find what they are looking for. A subset of a data warehouse would be something called a data mart, which is a subject specific collection of data. Data marts start getting closer to what you might use Essbase for. Data marts are smaller and again subject specific. Essbase is an OLAP engine used to consolidate and calculate data through the use of a meta-data outline that defines the dimesnional hierarchies. It is optimized for speed and intuitive retrieval of data through a common user interface like a spreadsheet. Essbase also supports user write back allowing the end users to manipulate data, submit forecast numbers, change rates, and other drivers while recalculating on the fly to evaluate what-if analysis. Often you will pull data from a data warehouse to load into Essbase for further analysis and manipulation by end users. I could go on and on about this, but I think you start to get the point. Data warehouses are big IT structures that store lots of data about your business. Essbase is tool to help perform analytics against some of the data in your ware house.
"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"
You can consider Essbase as a datamart in your case: It is a "subject oriented" (dedicated to finance, HR for example) database, with powerful calculation capabilities.
Your datawarehouse records a full range of KPI's, store them and historize them.
You could use Essbase as your datawarehouse, but that would mean mixing different "subjects": HR, Finance, etc, therefore having an inapropriate volume. No need to parse HR data when you want to calculate a gross margin for example.
Difficult to define precisely those words without a context. In my opinion, a datawarehouse can even be a collection of Essbase databases, each one taking care of a specific subject, and all archived in a massive historization cube.
Business Accounts
Answer for Membership
by: RWrigleyPosted on 2008-05-29 at 18:59:45ID: 21674741
Essbase is a brand of OLAP, like MS SSAS, DB2 Data Warehouse, SAP, etc. So if your question is "why essbase instead of <insert other OLAP provider>", the answer depends on what you're trying to do, as each type has its own unique strengths and weaknesses.
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.