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.
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.
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
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
https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models-olap-logical-cube-objects/partitions-partition-storage-modes-and-processing?view=sql-server-2017
More simplified version of this would be ..
https://jorgklein.com/2008/03/20/ssas-molap-rolap-and-holap-storage-types/
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..
First understand the types of storage modes in SSAS
https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models-olap-logical-cube-objects/partitions-partition-storage-modes-and-processing?view=sql-server-2017
More simplified version of this would be ..
https://jorgklein.com/2008/03/20/ssas-molap-rolap-and-holap-storage-types/
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..
Chief Technology Officer
CERTIFIED EXPERT
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.
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.
Database Administrator
CERTIFIED EXPERT
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.
IT Specialist
Raja,
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.
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.
IT Specialist
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.
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.
IT Specialist
Raja,
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.
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.
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
>> 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.
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.
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
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..
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..
IT Specialist
thanks, and I got that, but the link says "None" ... which was confusing to me. But I'm good (for this question).
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
>> but the link says "None"
Sorry, may I know which column you are referring to so that I can explain accordingly..
Sorry, may I know which column you are referring to so that I can explain accordingly..
IT Specialist
forgot the link, this was your 2nd link in your first response:
OLAP-data-latency-considerations.PNG
OLAP-data-latency-considerations.PNG
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions