11 Replies Latest reply: Apr 8, 2013 12:34 PM by user9134685 RSS

    HOLAP - using OBIEE and Oracle OLAP

    user9134685
      Hi

      How to implement HOLAP in OBIEE (with Oracle OLAP cubes) as below –

      1. For an aggregate table, there are 3 dimensions each one having 4 levels within its hierarchy.
      2. We create an aggregate table at the lowest level of each dimension.
      3. We create a cube for rest of the levels and its combination for the same measure.

      Can we implement this and how?


      Thanks
        • 1. Re: HOLAP - using OBIEE and Oracle OLAP
          Nasar-Oracle
          You are on the right track. This is how it should be done.

          Detailed transactional data in relational table, and high-level aggregated data (plus calculations) inside Oracle-OLAP cubes.

          You may not need Aggregate tables, since all aggregations are stored in Oracle-OLAP cubes automatically at all levels of all dimensions.

          Anyway, you should look at the OBIEE SampleApp V207 demo image. This Linux image contains lot of good dashboards, including Sample OLAP dashboards. There are samples for vertical and horizontal federation between OLAP and relational data.

          You can download the linux image from:
          http://www.oracle.com/technetwork/middleware/bi-foundation/obiee-samples-167534.html

          I have taken some screenshots and put it here:
          http://www.nasar.net/obiee_SampleApp_V207_OLAP_dashboards.htm


          Here is a summary of benefits when using Oracle-OLAP with OBIEE:

          (1). Simple cube creation using OLAP technology.

          (2). One Oracle-OLAP cube replaces dozens (or even hundreds) of Materialized-Views and Summary tables.

          (3). Simplified RPD work, since BI 11.1.1.5 can import OLAP metadata. There is very mininal work in rpd, as almost all calculations exist inside database and not in reporting layer.

          (4). No additional servers, databases, or DBA was required. If client has Oracle Enterprise-Edition, then they already have it.

          (5). All work through SQL and PL/SQL. No other language skills needed.

          (6). Fast Cost-based aggregation of olap cubes, as well as compressed cubes.

          (7). Simple SELECT...FROM..WHERE queries generated by BI server against olap data. No complications in the physical queries.

          (8). Very Scalable, since it uses Oracle database resources.

          (9). Complex financial, time-series and statistical calculations can be created with simple OLAP Expression syntax, which automatically works at all levels of all dimensions. This syntax is very much similar to relational ROLAP analytic syntax.

          (10). All types of hierarchies are supported. No need to make hierarchies, level-based if levels do not make any sense. Ragged and skip-level are easy also.

          .
          • 2. Re: HOLAP - using OBIEE and Oracle OLAP
            user9134685
            Thanks Nazar. That's an elaborate answer. I will load the sample app and see the implementations and would get a better idea from there.

            The only doubt I had was that you mentioned that transactional level data in ROLAP and aggregates in MOLAP whereas i was more for lowest level AGGREGATE in tables and higher level in MOLAP cubes.

            One apprehension i have and I read somewhere that for higher volumes ROLAP DB tables is always suitable over MOLAP cubes. I will be working on avg 10Million daily volumes with certain hierarchies upto 7 levels. So the aggregattion of all combinations can be very high volumes.

            1. Would the cube support these high volumes and I will risk the system by going against the norm (i.e. high volumes use ROLAP) suggested ?
            2. If I implement this split of ROLAP (lowest level agg) and MOLAP (other higher level agg) , then drill downs won't work because dimensionkeys of MOLAP cube dimension is "<<LEVEL>>_<<PK of DIMENSION>>" and on ROLAP it is just <<PK of DIMENSION>>.

            Thanks
            • 3. Re: HOLAP - using OBIEE and Oracle OLAP
              Nasar-Oracle
              Some side questions:


              (1). What was the main reason that prompted you to use Oracle OLAP option?

              (2). Which reporting tool will be used for OLAP reporting: OBIEE / BOBJ / CONGNOS etc ?

              (3). Was the level concatenation to dimension keys done because the members are not unique across levels?

              (4). Were the hierarchies parent-child in the source transactional system, and were then converted to level-based in your DW ?
              • 4. Re: HOLAP - using OBIEE and Oracle OLAP
                user9134685
                1. Requirement is for aggregates and adhoc reporting + time series measures with SLAs meant that a number of MVs would need to built, hence wanted to use cubes.
                2. OBIEE
                3. The source dimension table has hierarchy in form of columns.SK is same across since it is the same row. Once we ask cube to use its own dim keys, it generates the <<level_PK>> for each one. Because it is unpivoting the hierarchy columns, it would result in PK violation if level_ prefix is not attached.
                4. The hierarchy is in same table in form of different columns. Cube hierarchy is modeled as level based.

                Thanks
                • 5. Re: HOLAP - using OBIEE and Oracle OLAP
                  Nasar-Oracle
                  Do you know that starting with OBIEE 11.1.1.5, it can directly query from Oracle OLAP cubes, as it understands OLAP cubes metadata in Oracle database dictionary?

                  Oracle-OLAP is a new data source in obiee.

                  You can just "import" olap cubes/dimensions/hierarchies/attributes from your Analytical workspace in your obiee's RPD. After that when obiee reports are done, the BI Server first generates logical-query and then convert that logical query to physical OLAP_TABLE query automatically.
                  In this case, Cube-organized MVs are not used/needed.

                  Take a look at this tutorial: http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi11115/olap/olap.htm

                  I just wanted to make sure that you are aware of all this.


                  .

                  Regarding the hierarchies, it seems that those have been converted to balanced-level-based hierarchies (maybe for MVs) and that is why same member shows up at multiple levels.

                  If OBIEE is going to be used, then you can convert the hierarchies to parent-child or ragged when loading/mapping those hierarchies into olap. Is that something that you can do?
                  You will not have to do any level concatenation in olap, and keys in olap will match dimension keys in your relational detailed data.


                  I am still not clear, why you need MVs, especially with new adhoc reporting features of OBIEE - that is why I am asking all these questions.
                  • 6. Re: HOLAP - using OBIEE and Oracle OLAP
                    user9134685
                    Thanks Nazar for your reply.

                    On Cube based MV usage - The idea is to have lowest level summaries in relational. Higheer level in cube based on MV. So in case there are queries that can get rewritten , they will retrieve data from the cube itself.

                    On hierarchies -

                    If we take the example of oracle provided GLOBAL application and check the product dimension -

                    ========================================================================

                    CREATE TABLE "GLOBAL"."PRODUCT_DIM"
                    (     "ITEM_ID" VARCHAR2(12 BYTE) NOT NULL ENABLE,
                         "ITEM_DSC" VARCHAR2(31 BYTE) NOT NULL ENABLE,
                         "ITEM_DSC_FRENCH" VARCHAR2(60 BYTE),
                         "ITEM_DSC_DUTCH" VARCHAR2(60 BYTE),
                         "ITEM_PACKAGE" VARCHAR2(20 BYTE),
                         "ITEM_MARKETING_MANAGER" VARCHAR2(20 BYTE),
                         "ITEM_BUYER" VARCHAR2(20 BYTE),
                         "FAMILY_ID" VARCHAR2(4 BYTE) NOT NULL ENABLE,
                         "FAMILY_DSC" VARCHAR2(20 BYTE) NOT NULL ENABLE,
                         "FAMILY_DSC_DUTCH" VARCHAR2(60 BYTE),
                         "CLASS_ID" VARCHAR2(4 BYTE) NOT NULL ENABLE,
                         "CLASS_DSC" VARCHAR2(15 BYTE) NOT NULL ENABLE,
                         "CLASS_DSC_FRENCH" VARCHAR2(60 BYTE),
                         "CLASS_DSC_DUTCH" VARCHAR2(60 BYTE),
                         "TOTAL_ID" VARCHAR2(15 BYTE) NOT NULL ENABLE,
                         "TOTAL_DSC" VARCHAR2(15 BYTE) NOT NULL ENABLE,
                         "TOTAL_DSC_FRENCH" VARCHAR2(30 BYTE),
                         "TOTAL_DSC_DUTCH" VARCHAR2(30 BYTE),
                         CONSTRAINT "PRODUCT_DIM_PK" PRIMARY KEY ("ITEM_ID")
                    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                    TABLESPACE "USERS" ENABLE
                    ) SEGMENT CREATION IMMEDIATE
                    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                    TABLESPACE "USERS" ;
                    ========================================================================
                    Now , if this was TYPE 2 dimension and if you had possibility of late arriving sales data where you need to pick up "point in time" attributes of the product. How can I model dimension hierarchy in that case in OLAP?

                    In the Oracle example, they have kept it simple TYPE 1 so ITEM_ID can be kept as the key. However, if I had the PRODUCT_DIM as a TYPE 2 dimension table and i was using sequence generated surrogate key as the PK of the dimension table, how do i model it in OLAP?

                    Thanks
                    • 7. Re: HOLAP - using OBIEE and Oracle OLAP
                      Shankar S.-Oracle
                      The typical use case for dimensions is SCD1 but it does not mean that SCD2 dimension is not do-able or not supported. You just need to do most of the useful work upfront via ETL in the relational source layer before loading into OLAP.

                      Check out older posts regd OLAP and SCD2:
                      Re: Question re. loading OLAP from relational
                      SCD Type-2 Mapping from ROLAP to MOLAP

                      Model SCD2 dimension by defining unique keys for the PK column for each version of the "same" record. Record with Natural Key = ITEM1 with name "ABCD" may have Surrogate Key (pk col) =100, when name for ITEM1 changes to "PQRS" then the SK should become something else like 101. Then both versions of ITEM1 will be loaded as distinct members of Item hierarchy with keys=100,101. You can/should define attributes like EFF_FROM_DT, EFF_T)_DT also to make sense of the members in final query/report. You can choose to show the SK (100, 101) or just show Natural Key ITEM1 with EFF_FROM_DT columns in the report. The facts (summable) will get attached to either record with Key=100 or Key=101 and should line up nicely along time as per usage/event/transaction timelines.

                      This will give you point in time attribute values (as they occured).. sales records with key=100 upto 2008 = 4,000$ and sales records with key=101 from 2009 onwards till 2012 for about 10,000$ will give you 4,000$ for name=ABCD and 10,000$ for name=PQRS. However if you want something else (gimmicky) like show report with all sales of 14,000$ for name, = ABCD as of date: 2007 to be chosen by user at runtime, then that wont be possible via olap. Its better to attempt that via Relational as you can add dynamic filters to the report via your reporting tool of choice.

                      Even for relational reports, this .. super-imposing attribute values at a specific point in time onto the whole timeline of the dimension or sales lifecylce, is not easy/typical.

                      HTH
                      Shankar
                      • 8. Re: HOLAP - using OBIEE and Oracle OLAP
                        user9134685
                        Thanks Shankar.

                        There are 2 issues -

                        1) If i use the option to "use source key" as the KEY for the dimension, it will not work because it will be the same across levels and the way cube stores the dimensions, this won't work.
                        2) If i use the option "generate KEY" and ask cube to generate the KEY , it generates the key as "<<LEVEL>>_<<SK VALUE>> , so it will be non-unique combination across level but then because the KEY of the dimension and the relatational tables is different, I cannot have a cube and table join for ad hoc analysis purposes.

                        So, please guide me how to manage this.
                        • 9. Re: HOLAP - using OBIEE and Oracle OLAP
                          DavidGreenfield
                          Suppose you have a dimension with three levels, LEAF, MIDDLE, and TOP, which is mapped to a dimension table like this:
                          CREATE TABLE dim_table 
                          (
                            leaf_key NUMBER,
                            middle_key NUMBER,
                            top_key NUMBER
                          )
                          Suppose also that the key "100" appears at in all three "key" columns in the dimension table. As you said, you cannot select "use source keys" because "100" would then belong to three levels, which is invalid. If, instead, you ask the system to generate keys, then you will get three dimension members "LEAF_100", "MIDDLE_100", and "TOP_100". This is valid from an OLAP perspective, but doesn't fit your other requirements.

                          A compromise is to select "use source keys" but to map the levels as follows:

                          <li> Level LEAF is mapped to the expression DIM_TABLE.LEAF_KEY
                          <li> Level MIDDLE is mapped to the expression 'MIDDLE_' || DIM_TABLE.MIDDLE_KEY
                          <li> Level TOP is mapped to the expression 'TOP_' || DIM_TABLE.TOP_KEY

                          This will give you three dimension members "100", "MIDDLE_100", and "TOP_100".
                          • 10. Re: HOLAP - using OBIEE and Oracle OLAP
                            Shankar S.-Oracle
                            Neat trick suggested by David to convert non-unique keys into unique ids for members on the OLAP side.

                            i did not understand the objection you raised regd:
                            2) If i use the option "generate KEY" and ask cube to generate the KEY , it generates the key as "<<LEVEL>>_<<SK VALUE>> , so it will be non-unique combination across level but then because the KEY of the dimension and the relatational tables is different, I cannot have a cube and table join for ad hoc analysis purposes.
                            If your cube is loaded fine as truly representative of the underlying star schema (relational), then all reporting, standard as well as ad-hoc, can be based off the cube via the olap based SQL cubeview. I dont understand why you need to join Reln table with olap output (assume you mean olap sql view) as a matter of ad-hoc report execution/fulfillment. Joining olap views with relational Fact tables is not a recommended reporting practice.

                            Side Note: You can join olap views with relational dimension entities to get some attributes, NOT modeled in olap layer, ... That's possible but also atypical. There was an old olap newsletter which highlighted this approach. If relational dimension has many (30-40 attributes) then you can afford to map only the important 5-10 attributes in olap and get the rest via olap view joined to relational dimension table at runtime. OLAP provides the aggregates and reln provides the attribute values as tags to the aggregated data.
                            From Link: http://www.oracle.com/technetwork/database/enterprise-edition/polk-olap-newsletter-088552.html
                            - Where necessary, cube based materialized views were joined to relational tables during query execution. This allowed the team to integrate cubes into an existing snowflake schema without the need to consume all attributes of the dimension members inside of the cubes.
                            In case your reporting is all relational and ad-hoc and the cube is supposed to help with the cube based MV query rewrite, even there too, i dont think the dimension level prefix will cause a problem. I have got query rewrite to work against a star schema based olap cube whcih was loaded using the dimension level prefixes. The data integrity relating to 2 members like DAY_100 and MONTH_100 at DAY and MONTH level with key=100 will be maintained by the SQL Query Rewrite system. I dont think it (having level prefixes for member ids) rules out Query Rewrite capability for the cube.

                            There are a lot of other Cube MV related QR limitations/obstacles which you would have to overcome (multiple hierarchies, lossy joins b/w snowflakes etc.).


                            HTH
                            • 11. Re: HOLAP - using OBIEE and Oracle OLAP
                              user9134685
                              Shankar - There are certain subject areas I believe will need a lot of adhoc reporting and hence I am modelling it into OLAP cube (Now there is another question around usage of OLAP where i need your opinions, this is just one of the criterias i mentioned here). Others I will leave as relational tables. Now, when a superuser wants to create reports with relational measures and OLAP measure side by side, I will need to do this.