Link to home
Get AccessLog in
Avatar of William Peck
William PeckFlag for United States of America

asked on

Need to understand sample data model of OLAP database

need to to understand the physical structure of an OLAP database. And if I looked into a Oracle database, how can I tell if it's relational or OLAP? Isn't the OLAP data still stored in a table with keys that join to the other tables? I'm having a hard time picturing the structure and then I know querying is different too.

For years I heard these data warehouse jockeys talking about cubes, dimensions, and measures, craftily convincing the IT managers they know what they are talking about, and impressing the users that "this is the thing." But it always confused me. So I watched a few videos today, and the light bulb goes off - "oh, it's a pivot table".

btw, the cube is a nifty visual for a powerpoint presentation, but it's still presented in two-dimensions (piece of paper or a computer screen). Unless you have a 3-D printer I suppose ...

So I got the concept of measures, dimensions, etc., but now need to "get the picture" in what the data actually looks like and how to query it. I know this is bigger than one post here, but that's my objective.

For example, here's my confusion - I was also told today that an OLAP database is NOT relational in essence, it's totally different. In this article, you have
- the time dimension
- the product dimension
- the customer dimension
- the Fact table (total $ sales, quantity, discount)

BUT - don't all the dimension have keys that link it to the fact table? So to me, "in essence" it is relational. And if I know Oracle SQL, how exactly do I query this?

I need some good articles or tutorials on how his works.

And next week will be star schema, another term the data warehouse jockeys love.
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Okay, here's a good starter for you with respect to SSAS.. If you are looking for other OLAP tools, this may or may not be the same..
First understand the types of storage modes in SSAS

More simplified version of this would be ..

If you can see the above 2 articles, you can get a fair idea that in ROLAP or HOLAP, the data storage is in Relational database which means the data is still present in your database as tables..
Hope this answers your basic questions and get back to us for more questions..
I've had to learn the same thing many years ago.  Basically we're looking at the difference between an "OLTP" (OnLine Transaction Processing) database and an "OLAP" (OnLine Analytical Processing).

OLTP databases are what you learn about when you read your first book on database programming.  It talks all about relationships and "normalization" of data from (usually) 3 levels up to 5 levels.  These databases are highly fractured as the tables are designed to contain all the pieces that may go into a business "document" or "object" such as an order or invoice.  They contain tables for customer data, product data, pricing data, incentives data, shipping data, salesperson data, etc., etc., etc.  The business software is designed to build these documents from the many pieces of information that are pulled together from all these tables.

"OLAP" brings another database design into the picture - a database known as a "data mart" - a database where the document records  are kept.  Data marts are much less normalized and have far fewer tables as the information in the documents are already  "written in stone".  Beginners in the world of database design haven't been exposed to data marts until they start dealing with them.  If I had a dollar for every newbie who looked at a data mart database and said "Hey!  Your database isn't build right!  It's not fully normalized", I'd have been able to retire already.

Don't let "reporting" and "analysis" confuse you.  They can be used separately or together.  Some databases are designed to make it easy for users to build their own queries and reports.  Data marts are perfect for that.  Analytical tools are used to analyze the business - sales per region or sales per salesperson, etc.  These tools may use OLTP databases or data marts - it depends on what the needs are and how much trouble and expense you want to put into it.

If you aren't familiar with "data marts", a little research in this area should help a lot.  Hope this helps.
Good questions and good responses.  I especially like the response from Mark Edwards.  Yes, you are correct: the data (at least if you use an Oracle database) is still stored in rows and columns.  So, you can query it just as you always have.  The tables may be organized differently if the database was designed to support OLAP, and the indexes may also be optimized to support reporting instead of transactions.
Avatar of William Peck



This is very helpful,  thank you.

What I'd like to see is a case study of building a MDX database, and then how to query it.

A couple of questions from the articles:
- ROLAP - I don't get what is the big deal here. It sounds like it's just the relational database which is the source of the reporting.
- HOLAP - I don't quite get this either, would have to see an example.

part of my dilemma - all of this is done for performance, no? All reporting COULD be done from the relational database, but it's performance that is the driver to the MDX model. From the OLTP, data could still be presented in "cubes" (I do this all the time with Business Objects), although cubes are a visual to understand the depth of the data, it's still presented in 2-D format.

Professor Hasso Plattner of SAP advocates in-memory computing (zero response time), which eliminates the need for pre-aggregating data. Which means no data warehouse, everything comes back to the OLTP.
Mark Edwards, great explanation, thank you.

Mark Geerlings,

>>you are correct: the data (at least if you use an Oracle database) is still stored in rows and columns.  So, you can query it just as you always have.
- Aha, so I was right!!!! "In essence, it's a relational database"

All - What I'd like to research next is actually designing the tables for an Oracle MDX d.b. I'll poke around for examples but if you have any, please advise.
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access

thank you and helps clarify my thoughts!

>>ROLAP -  the tables would be designed in terms of facts and dimension tables and not like OLTP database tables.
>>These tables are in denormalized form as against the normalized ones in OLTP.
- ok makes sense ... but if there is an OLTP, then wouldn't the ROLAP data have to be moved to the facts and dimension tables? Perhaps that is done in real-time?

>>search for some websites explaining the difference between ROLAP, MOLAP and HOLAP graphically
- great link, also very helpful

>>Small correction, 2 D format is columns and rows and more than 2 D is called as Multidimensional objects and it requires specialized structures called as CUBES to process and store data for faster retrieval..
- good technical point, but my frame of reference is from a user - it's still 2 D and it's still in columns and rows!

>>IMHO, we can do memory computing for OLTP databases which are small in size..
- totally agree! our database is small (30,000 records for the main entity [student])) and it grows at a steady rate.
>>  but if there is an OLTP, then wouldn't the ROLAP data have to be moved to the facts and dimension tables? Perhaps that is done in real-time?

Nope, Since the OLTP data needs to be transformed to Facts and dimension tables(dimension tables less rarely) this will be done on a load basis and not in a real time manner.
But this link says there is "no latency" with ROLAP and HOLAP.
To be more clear it was mentioned as Low latency and not No Latency and the Less latency is due to the processing time..
Lets say you load your Dimension and Fact tables once every 1 hour, then the latency is 1 hour. If every 15/30 minutes then latency is 15/30 minutes..
thanks, and I got that, but the link says "None" ... which was confusing to me. But I'm good (for this question).
>> but the link says "None"

Sorry, may I know which column you are referring to so that I can explain accordingly..
forgot the link, this was your 2nd link in your first response:
Yes, it was either low or none depending upon the load frequency. If the load duration is very less, then it is none and if high, then it is low..