This discussion is archived
6 Replies Latest reply: May 28, 2013 12:14 AM by Shankar S. RSS

OLAP_CONDITION in OBIEE

Swapan Newbie
Currently Being Moderated
I am currently running 11.2.0.3 DB with 11g OLAP cubes and have imported them using OBIEE 11g (11.1.1.6.5) Administrator as a Cube based Subject Area. I want to use an OLAP Condition in one of my reports and hence as per the docs, I am trying to use the EVALUATE_PREDICATE clause. I am not sure how to get the R2C (row2cell) into the RPD. Should I be adding an OLAP column called R2C (what should be its type) in the physical layer.

Any help is appreciated?

Swapan.
  • 1. Re: OLAP_CONDITION in OBIEE
    Nasar Journeyer
    Currently Being Moderated
    You probably have already read the documentation about ROW2CELL clause in OLAP_TABLE's limitmap.
    If not, then read about ROW2CELL here: http://docs.oracle.com/cd/E11882_01/olap.112/e17122/dml_app_sqlfunc001.htm

    When OBIEE generates olap_table queries against Oracle-OLAP data, then it will ALWAYS have these two lines in the LIMITMAP:
    *|| 'loop optimized '*
    *|| 'row2cell r2c ' ))*

    In other words, OBIEE development team has decided that ROW2CELL variable name will always be *'r2c'*, in the queries generated by Oracle BI Server against Oracle-OLAP data.
    So we can use r2c when writing our own OLAP_CONDITION or OLAP_EXPRESSION statements in OBIEE reports.

    BTW... The example given for EVALUATE and EVALUATE_PREDICATE in OBIEE documentation has incorrect syntax here: http://docs.oracle.com/cd/E28280_01/bi.1111/e10540/sqlref.htm#CHDEEIFA


    Some more info:

    _(1)._ With OBIEE's EVALUATE function, we can create calculated-columns using OLAP_EXPRESSION. To do this you will need to know some olap dml language, which is not that hard. You can always get some help on this forum also.

    EVALUATE(OLAP_EXPRESSION(...)) is used in obiee as an alternative to physically creating calculated-measures inside Analytical workspace. For example, if a calculated column (or calculated measure) is only needed in one obiee report, then why create it inside olap AW.

    The syntax is simple:

    For text measure
    CAST(EVALUATE('OLAP_EXPRESSION_TEXT(r2c, %1)', '<olap expression returning text>') AS VARCHAR(100))

    For numeric measure
    CAST(EVALUATE('OLAP_EXPRESSION(r2c, %1)', '<olap expression returning number>') AS DOUBLE)

    .
    On a side NOTE:_ If you are using EVALUATE(OLAP_EXPRESSION(...)) in a Logical query or in an obiee Analysis report then make sure you select atleast one regular measure from "Presentation Layer". Otherwise, if you only select dimension/attribute columns and OLAP_EXPRESSION columns then no data will be returned. This is a very important point.

    .

    *(2).* Oracle-OLAP's LIMIT command is pretty powerful way to filter the data. Using both LIMIT command and LIMIT function, we can do in one line which otherwise would take a lot of relational subqueries. Read more about LIMIT command and LIMIT function at: http://docs.oracle.com/cd/E11882_01/olap.112/e17122/dml_commands_2009.htm#i78802

    We can provide our own LIMIT command in a sql query, using OLAP_CONDITION in WHERE clause.
    OBIEE allows us to do that using EVALUATE_PREDICATE.

    Here is one example where ACCOUNT dimension is limited to bottom descendants (or leaf members). You can see how simple it is through OLAP dml, and imagine if you have to do this through sql sub-queries.

    AND EVALUATE_PREDICATE('OLAP_CONDITION(r2c, %1)=1', 'LIMIT  ACCOUNT TO BOTTOMDESCENDANTS' )     

    You can get help on this forum for more useful LIMIT commands.


    As I have said in the past on this forum, OBIEE with Oracle-OLAP makes reporting very powerful and simple.
  • 2. Re: OLAP_CONDITION in OBIEE
    Swapan Newbie
    Currently Being Moderated
    Thanx Nasar. This fixed my issue. I couldn't agree more on using OBIEE with Oracle OLAP.

    Can't understand why the doc error has not been corrected for some time now.

    Swapan.
  • 3. Re: OLAP_CONDITION in OBIEE
    Shankar S. Journeyer
    Currently Being Moderated
    Swapan, i guess you're using the native connectivity to Oracle OLAP from OBIEE and not reporting on the the SQL VIEW based start schema involving Dimension, Dimension Hierarchy and Cube views.


    Regd
    In other words, OBIEE development team has decided that ROW2CELL variable name will always be 'r2c', in the queries generated by Oracle BI Server against Oracle-OLAP data.
    So we can use r2c when writing our own OLAP_CONDITION or OLAP_EXPRESSION statements in OBIEE reports.
    Nasar, Great catch that. I have been enquiring about the ROW2CELL functionality via Oracle OLAP native connection for quite some time but did not notice this quirk/development hook via r2c.
    Many times, I have reverted back to the older SQL views Based OBIEE reporting of Oracle OLAP due to the lack of this option. I havent used the native connectivity mode of access much as a result.

    It may help the dev community if (say) the Oracle OLAP- OBIEE white paper detailing the native access solution high-lights this particular feature/point so that Developers, partners can trust that this cell will continue to be available in future.


    Another advantage of using this feature is to make the OBIEE report(s) dynamic.
    Example: OBIEE Dashboard can have a Drop down prompt to choose number of days from 1 to 10 (say). This populates an OBIEE Presentation variable post user choice of number of days. We can use this Presentation Variable in the EVALUATE Expression and make the number of days used in MOVINGAVERAGE function or some other OLAP DML function being shown on report.
    E.g: EVALUATE(OLAP_EXPRESSION('MOVINGAVERAGE .....', <obiee col expression>, <obiee Presentaion variable>... )
    If we did not have r2c column available then we would have had to use a fixed value for number of days - either 2 or 5 or 10 .. or... create additional formulae for each value/option. Not very nice compared to using r2c and evaluate + olap_expression.


    HTH
    Shankar
  • 4. Re: OLAP_CONDITION in OBIEE
    Nasar Journeyer
    Currently Being Moderated
    Shankar,

    Having worked with obiee 11.1.1.5 and now with 11.1.1.7, it is much simpler and efficient to use the new obiee's native connectivity to OLAP.

    There is no need to use the old way of using CUBE_TABLE or custom OLAP_TABLE views.

    Few things on the olap side that helped:

    (1). Creating a reporting cube (with only calculated measures in it) on top of stored cubes. This reporting cube was then used in the Presentation Layer of RPD.
    No stored cubes were exposed in Presentation Layer.

    (2). All stored cubes having the same dimensionality, even if some dimensions are not applicable to a stored cube. This way the LOOP OPTIMIZATION worked properly (behind-the-scenes) when a query fetched data from multiple stored cubes.
    If multiple stored cubes with different dimensionality are accessed in one report then sometimes LOOP OPTIMIZATION does not work. You can check that through CUBE_OPERATIONS_LOG.

    (3). This third point is hard to explain and is probably not applicable to other obiee-olap implementations, but I will still mention it here.

    The hierarchies in Source system were parent-child. Originally I created parent-child hierarchies in OLAP and in OBIEE. But because of some reporting requirements and obiee functionality (including data security), we decided to convert those to Ragged hierarchies in OLAP and in OBIEE. But then another issue was faced as "shared members" in multiple hierarchies of a dimension were at different levels. So instead of creating a separate dimension for each hierarchy, I did the following.

    For each source parent-child hierarchy, I created three hierarchies in OLAP:
    (3a). Parent-Child
    (3b). Ragged
    (3c). Skip Level


    - Ragged hierarchies are used by one Subject-Area for OBIEE Dashboards/Analysis reports

    - Parent-child hierarchies are used by another Subject-Area for BI-Publisher reports and custom Logical queries, since those reports did not care about levels, and all members of a dimension were needed in one column.

    - Skip-level hierarchies are used for OLAP cube aggregation. Ragged and Parent-Child are not used for Cube Aggregations.


    All this probably sound complicated, but following some standards made it relatively easier. Exact same logic was used in every hierarchy's source sql-view to convert parent-child to ragged and to skip-level.

    Another benefit is that OBIEE/BI-Publisher developers only have to work with Logical queries, and they do not have to know anything about OLAP_TABLE function.
    And as you know there are no JOINs also.
    Additionally, RPD work is very very minimal.

    I did find few obiee bugs (related to olap_table queries generated by BI server) during this project,
    which have now been fixed by obiee development team.

    .
    The take away is:

    (#1). Use Parent-Child hierarchies or balanced-level-based hierarchies in OLAP and in OBIEE, if possible.

    (#2). Stay away from Skip-level hierarchies in OBIEE, since it generates too many OLAP_TABLE queries behind-the-scenes.

    (#3). If balanced-level-based hierarchy is not possible, then use Ragged hierarchy in OLAP and in OBIEE.

    (#4). Use obiee Data Security feature. Do not implement security in olap - i.e., PERMIT_READ etc - otherwise you have to create obiee cache per user, instead of one global obiee cache.

    (#5). If hierarchies are parent-child, then for obiee data security you will need to use custom OLAP_TABLE views (in obiee init block) which show each dimension's parents in one column and all its descendants in another column. These views are easy to create using OLAP_TABLE function.
    .
  • 5. Re: OLAP_CONDITION in OBIEE
    Nasar Journeyer
    Currently Being Moderated
    About point (#5) that I mentioned above, I will provide the query here which takes a parent-child hierarchy (i.e., DEPTHIER in this case) and return parents and all its descendants.

    This can be used in many places, like security in OBIEE OR Drill-Thru reports from OLAP to transactional data.

    SELECT 'DEPT' "DIMENSION", 'DEPTHIER' "HIER", ancestor, descendant, leaf_descendant
    FROM TABLE(OLAP_TABLE('BAWOLAP.BNSOLAP DURATION QUERY', NULL, 'LIMIT DEPT_HIERLIST TO "DEPTHIER"',
    *'DMNS DESCENDANT AS VARCHAR2(60) FROM DEPT*
    DMNS GID_VAL AS NUMBER FROM ___AW_GID_DIMENSION
    measure leaf_descendant from aw_expr IF INLIST(LIMIT(DEPT TO BOTTOMDESCENDANTS USING RELATION DEPT_PARENTREL QUALIFY DEPT_HIERLIST "DEPTHIER"),DEPT) THEN 1 ELSE 0
    MSR ANCESTOR AS VARCHAR2(60) FROM DEPT_FAMILYRELVAL' ))
    WHERE ancestor IS NOT NULL


    Thanks to David Greenfield for this tip.
  • 6. Re: OLAP_CONDITION in OBIEE
    Shankar S. Journeyer
    Currently Being Moderated
    Thanks for the inputs. I will use OBIEE native access to Oracle OLAP in the future.

Legend

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