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.
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..