One of the parterns developed a data warehouse application. And the DW application has some performance issue:
when the report get the query of the high level dimensions, the performance is okey, and when the query get the very detail data in the cube, the performance gets bad.
The aggregations and the detail data are all stored in the cube, and the cube data gets Explosion quite quickly since some detailed transaction data need to be queried and stored in the cube too.
So, experts, do you have any good suggestion on this issue? or if the may be a better design for the cube? e.g. in DW, the cube only stores the aggregations or summary on coarse grained data and measure, for fine grained data , it can be got in ODS.
another question, I google the architecture solution for the above issue, and someone said that if the DW is designed in a hypercube, there maybe data explosion issue, but instead of desinging the hypercube, multicube should be used, so I wonder if multicube can solve the data explosion issue, and how to solve it. And if the multicube has better performance than the hypercube or can also solve detail data query.
Last question, do you have any experience on DW implementation on TB level Data, and any good suggestion for architecture design using Oracle OLAP or Essbase for good performance.
You have not asked any specific technical question. In my opinion all Oracle Datawarehouses should use Oracle OLAP option for the Aggregation strategy. Significant improvements in 220.127.116.11 (and later versions) have been made. It has become much easier now to create and maintain dimensions/cubes. On the reporting side, OBIEE 11g now understands OLAP metadata. Other reporting tools can use the CUBE_TABLE views.
Here are some links that you may find useful.
Comparing MVs and OLAP... Oracle White paper
Oracle OLAP Support page
Three demos done by OLAP Development which explains how OLAP can help in a DW.
Main OLAP page at Oracle OTN site
Recommended Releases for Oracle OLAP
Accelerating Data Warehouses using OLAP option
What's new in 18.104.22.168 database OLAP option
Oracle 11.2 OLAP Documentation (scroll down to OLAP section)
Excel reporting from OLAP using Simba tool. This was developed in partnership with Oracle.
There is a good demo for Simba Excel tool at: