1 Reply Latest reply on Jan 29, 2013 2:33 PM by Shankar Somayajula-Oracle

    Default STATUS and QDR

      Trying to understand what is the default status or state when I am querying from SQL -> into the Cube. Specfically what is the status of my dimensions by default to the DML program that is running?

      Will the status of the dimensions be limited to what I am selecting?

      Eg. Suppose I have a dimension "Symbol"

      and I run a query like

      select c.mydmlcalcmeasure from cube c, dim a where c.sym = a.dim_key and a.level_name = 'PRODUCT' and a.short_description = 'SOME PRODUCT'

      What is the status of of the symbol dimension in the call to the DML program mydmlcalcmeasure?

      If I am referencing cube data from this DML program, do I need to worry about having the Symbol dimension as an argument to this program so that I can use this for any QDR's or is the default status already set?

      What is the best practice here?
        • 1. Re: Default STATUS and QDR
          Shankar Somayajula-Oracle
          I think that the best practice is to pass the cell specific dimension value (ie define input parameter for all dimensions) as input to the program (olap dml) and use it within a tempstat cmd (if you wish tp, pls look up olap dml reference for tempstat) to ensure that all calculations/formula used within the olap dml program is specific to that cell.

          By default the formula from front end should be using the QDRs to pass on all dimension members (even those not visible in the report ... not visible => a) dimension are on page edge and restricted to single value or b) missing dimension could be set to full status and comprise multiple values).

          More specifically if your symbol dimension contains 3 symbols - A,B and C and also Total. If this dimension is not used... then it can be any of the following (depending on what olap dml cmds have been executed/enforced on the aw for that session):
          * SYMBOL dimension status could ALL... all 4 values in status and passed/used implicitly for formula. So a formula in olap dml using total() would sum up values for A, B, C and also Total ... possibly getting you wrong results .. double of total.
          * SYMBOL dimension status could be set to "TOTAL"... say if obiee or some other reporting tool enforces some security filters... then TOTAL symbol is in status and applicable for the program.
          * If some other reporting prompt or report executed earlier in the browsing/reporting session had selected symbol C (say) and such settings were made persistent (olap filters can be persistent ... valid after the query is executed or the filters could be transient.... status is reset back to ALL or default status after query is executed).. in this case, the program would use SYMBOL C for the duration of the olap dml program execution.

          As you may expect, all the above scenarios leave too much to chance and so best practice is to pass missing dimensions and begin work within program only after all relevant dimensions have set to a specific value. NOTE: you can pass some dimension value as input, restrict status to that cell dimension value and then use some limits in olap dml program to calc values for the cell's parent/children/descendants/bottom descendants (leaves) etc. All this should occur within the tempstat so that the program temporarily changes status (as necessary) and calculates the results and goes back to re-do the same work/calculation for the next cell.

          OLAP DML work for all values in status. so an assignment var1(time time1) = var2(time time2) assigns values from time1 to time2 member but if multiple symbols are in place/status then this assignment is done for all symbols in status. If you use a function like total then all values in status can be in play.

          OLAP DML programs which calculate values should ideally not modify data ... becomes a bit unpredictable.

          you query should probably be something like this ...

          select c.mydmlcalcmeasure(a.dim_key, b.dim_key, d.dim_key) from cube c, dim a, time_dim b, prod_dim d where c.sym = a.dim_key and c.time = b.dim_key and and c.prod = d.dim_key and a.level_name = 'PRODUCT' and a.short_description = 'SOME PRODUCT' and b.level_name = 'MONTH' and c.level_name = 'ITEM'