This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,805 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Importance of Global Consistency check

Hello,

I have always checked my rpd for global consistency without knowing the actual meaning behind it. But last night I created a logical column with the following expression:

max(VALUEOF(NQ_SESSION......))

basically i created an aggregation over a logical column that obtains its value from session variable. I know that if we need to use a column as an aggregation column we should use the aggregation tab in the column properties. When we choose an aggregation it disables the editable column formula field.

I put the above formula which violates the rule. The result is perfect so long as I don't check for global consistency. It throws an error that looks something like this:

[38083] The Attribute 'Acceptance Rate Target' defines a measure using an obsolete method.

The question is.. what is the significance of global consistency check.. and what is the consistency criteria.. and is it ok to save the rpd without checking for global consistency (yes this does not cause the BI server to crash when trying to start)


Thanks
Tagged:

Best Answer

  • Stijn Gabriels
    Stijn Gabriels Member Posts: 1,032
    Answer ✓
    First, forget about the variable approach.

    Now you need to do the following steps:
    1) Import the table to the physical layer
    2) Create logical table in existing Business Model in BMM layer with the imported table as logical table source
    3) Create another logical table in existing Business Model in BMM layer with the imported table as logical table source
    4) Create complex join between both table, now you will get one logical dimension table and one logical fact table
    5) In the logical fact table you need to select the column ("Target") and add an aggregation rule, like MAX or MIN
    6) Assuming you have a hierarchy for every dimension in your BMM layer, you need to set the logical levels of the new measure to the Grand Total Level of each dimension hierarchy.
    By doing this, you get a "level based measure", for more info: read this:
    http://oraclebizint.wordpress.com/2007/12/03/oracle-bi-ee-101332-level-based-measures-lbms/

    By setting all logical levels to the grand total level, the measure will be "immune" for all dimensions used in your report.

    So when you have a report like
    Month__Actual___Target

    The BI Server will create two queries:
    select month, sum(sales) from calendar, sales_table where calendar.id = sales_table.calendar_id

    and
    select max(value) from target_table

    The BI Server will then stitch both results together.

    Regards,
    Stijn
«13

Answers

  • 628531
    628531 Member Posts: 1,293
    The "problem" is the MAX part here, a VALUEOF(NQ_SESSION..) can only have one value
    You could switch of the warnings for obsolete method in you consistency checker.

    regards

    John
    http://obiee101.blogspot.com/
  • 646894
    646894 Member Posts: 63
    Hi,

    I know that the Max function should not be put in the column expression. Unfortunately, from my knowledge of OBIEE, the requirements have been met only with this method.

    Let me describe the requirement. Let us have a report like

    Supplier | Sales Accepted | Sales Rejected | Sales Accepted % | Target Acceptance %

    now the column Target Acceptance % is a static value obtained through a session variable that accesses a physical DB table and the query in the initialization block is as follows.

    select target_value
    from (table name)
    where measure_name = '%Accepted'

    Since the above report has agg measures we need to aggregate the target column as well.

    But there is no meaning in making the target column with an agg rule. Besides, I cannot make it an aggregation column as I am obtaining its value through a session variable.

    The table containing the target value is a two column table without any joins to any table, hence I cannot pull it into the rpd and pull it to the presentation layer.

    and its is not a warning, it is an error!! :) Hope i was clear in explaining the situation.. Wish you can help.

    Thanks
  • Stijn Gabriels
    Stijn Gabriels Member Posts: 1,032
    Hi,

    in my opinion it is not a good approach to use a session variable to get the "target acceptance %" in the report, but you should add it to your repository, even when the source table is a two column table with no joins.
    About adding a single table to your repository, you should read this post.
    http://www.rittmanmead.com/2009/11/10/oracle-bi-ee-10-1-3-4-1-single-table-repository-design-part-1/

    Regards,
    Stijn
    Stijn Gabriels
  • 646894
    646894 Member Posts: 63
    Hi,

    Awesome blog, but I can't figure how I can incorporate this into my solution.

    The solution in the blog is great as long as the single table you want to include will not be used for reporting along with columns from other facts.

    The situation I am trying to describe is like this. I need a non-aggregating column from a table that is not connected to any dimension or fact, has no keys but just two columns, name and value. This will be used along with other dimensions and facts. This column displays just one value through out based on the name picked by the user.

    whenever this column is pulled I want it to display a value. Is there any other way of doing this.

    I know one other method of doing this in the presentation services.. pull random column into the report and put the value u want in its column formula.. but this is not acceptable as per the product.. so.. please help!!

    Thanks,
  • Stijn Gabriels
    Stijn Gabriels Member Posts: 1,032
    Let me ask another question first: Is the value you want to display user specific?
  • 646894
    646894 Member Posts: 63
    Hi,

    No its not a user specific value. Its independent of everything. No dimensions, facts etc. Just a display column
  • Stijn Gabriels
    Stijn Gabriels Member Posts: 1,032
    When the value is not user specific, why do you use a session variable? Session variables are always user specific, when you have values which are common for every user, you need to use repostiroy variable.

    Now when you want to use this repository variable in your report, you can just add a column (in answers) and change the formula to VALUEOF(variablename).
    When you want to use this variable in a (repository) column, you can add this variable to a new logical column in a existing dimension.
    Let me know if this works for you.

    Regards,
    Stijn
  • 646894
    646894 Member Posts: 63
    Hi,

    Yeah I had tried repository variables too.. And I tried using it in a logical column as well. The problem is I need to pull this column into a report that contains column from a a dimension and a fact.

    And since this is not an aggregate column the report shows a blank for the repository var based column. This is the reason I was trying to put in an agg function in the logical column formula..

    About the other approach you told me, using the repository variable in a report level filter. Well, they are not very approving of doing this in the report.

    The requirements are such that the user should be able to pull in a column to get this particular fixed value.

    To make it a little more clear. Think of it as a column that contains a global target = 95%. So whenever the user wants a column which needs to show 95% as the target against a measure, he should be able to pull in this column into his report.

    The main problem is it cannot be aggregated!. Hope you know a way out of this one..

    Thanks.
  • Stijn Gabriels
    Stijn Gabriels Member Posts: 1,032
    And what happens if you add the repository value to a logical column of an existing dimension? So without aggregation rule? Do you get the same problem as well.

    Well, actually I still think you need to go for the single table approach.
    So one logical dimension table and one logical fact table, both having the same logical table source.
    Then in your fact table you need to set the aggregation rule of your value to MAX or MIN (doesn't really matter right?).
    Now comes the trick: on the measure you need to set all logical levels of all dimension hierarchies to the Grand Total Level.

    Regards,
    Stijn
  • 646894
    646894 Member Posts: 63
    Hi,

    When i pull in this non aggregating logical column(based on rep var) along with a agg measure from a fact, and a dimension column, no value is displayed in the logical rep var based column since the group by condition will not work on the non agg rep var based col.

    let me give an example.

    if i pull in supplier name from dimension
    total acceptance rate from a fact
    and target (the rep var based logical col)

    the report gives a blank in all the rows for target column. This is because its non-agg column and hence the group by clause will not work.

    Now, I did not understand the trick part of your post.. could you please explain a bit

    Thanks,
This discussion has been closed.