How Do SQL Tools Like APEX Access Oracle OLAP Cubes?
The data in an Oracle OLAP cube is made directly accessible to SQL by a set of relational views. These views represent an OLAP cube as a star schema with the following characteristics: – A cube view plays the role of a fact table. – Dimension views and hierarchy views play the role of dimension tables. The star design exposed by OLAP cubes is very similar to traditional table-based star models. The dimension views form a constellation around one or more cube views. However, there are two key differences: – Fact tables in a star schema store detail data (called leaves), while the cube views reveal many summary levels. – Calculations in a cube are simply exposed as columns in the cube view, and the computation for the equations occurs in the OLAP engine. These differences impact the way you query data. With star queries, you aggregate the data by combining aggregation functions (such as sum) and the GROUP BY clause. With OLAP queries, you simply select the data you want (either stored or