This discussion is archived
2 Replies Latest reply: Nov 7, 2012 5:41 PM by Nasar RSS

Cube Performance and Data Explosion

Royal Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points