2 Replies Latest reply: Nov 7, 2012 7:41 PM by Nasar-Oracle RSS

    Cube Performance and Data Explosion

    Royal
      Hi Experts,

      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.

      Thanks,
      Royal.

      Edited by: Royal on 2012-11-4 上午4:01
        • 1. Re: Cube Performance and Data Explosion
          Royal
          Hello Experts,

          The above questions are the real issues that raised by our partners who are developing data warehouse application for our customers in medical treatment industry.

          it will be greately appreciate that you suggest any resolution.

          Thanks,
          Royal.
          • 2. Re: Cube Performance and Data Explosion
            Nasar-Oracle
            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 11.2.0.2 (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
            http://www.oracle.com/technetwork/database/bi-datawarehousing/comparison-aw-mv-11g-twp-130903.pdf



            Oracle OLAP Support page
            https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1107593.1


            Three demos done by OLAP Development which explains how OLAP can help in a DW.
            http://download.oracle.com/otndocs/products/warehouse/olap/videos/intro_part_1/OLAP_Features_and_Use_Cases_1.html
            http://download.oracle.com/otndocs/products/warehouse/olap/videos/intro_part_2/OLAP_Features_and_Use_Cases_2.html
            http://download.oracle.com/otndocs/products/warehouse/olap/videos/intro_part_3/OLAP_Features_and_Use_Cases_3.html


            Main OLAP page at Oracle OTN site
            http://www.oracle.com/technetwork/database/options/olap/index.html


            Recommended Releases for Oracle OLAP
            http://www.oracle.com/technetwork/database/options/olap/olap-certification-092987.html


            Accelerating Data Warehouses using OLAP option
            http://www.oracle.com/technetwork/issue-archive/2008/08-may/o38olap-085800.html


            What's new in 11.2.0.2 database OLAP option
            http://docs.oracle.com/cd/E11882_01/olap.112/e17123/whatsnew.htm


            Oracle 11.2 OLAP Documentation (scroll down to OLAP section)
            http://www.oracle.com/pls/db112/portal.portal_db?selected=6&frame=#online_analytical_processing_%28olap%29


            Excel reporting from OLAP using Simba tool. This was developed in partnership with Oracle.
            http://www.simba.com/MDX-Provider-for-Oracle-OLAP.htm



            There is a good demo for Simba Excel tool at:
            http://www.simba.com/demos/MDX-Provider-for-Oracle-OLAP-web-demo.html