OBIEE Online Training

OBIEE Online training by ex-Rittman Mead and Oracle trainer.
Contact : naveen@bipracticals.com
Watsapp : +65-82830121
Training will be real time and more hands on.
Number of slots available : 2/5

Saturday, July 28, 2012

Fixing Data Mismatch issues after upgrade from OBIEE 10g to 11g

Hey friends...i know you guys will be really struggling to fix the data mismatch issues.Unlike other issues it takes a lot of time to fix data mismatch issues.The method followed by us was a trial and error method.There are no clear cut explanation for the workarounds but it worked for us.I will be giving some tips which might help you make your bug fixing easier.

Scenario: Data Mismatch

TIP 1:

Most of the data mismatch issues can be resolved by doing the following:

Solution:Apply the aggregation rule in the report level.ie,Instead of the default aggregation given, apply aggregation rule based on the column.
eg: For the Revenue metric you can apply Sum aggregation rule instead of Default.















Reason:The default in 11g is not same as 10g.


TIP 2: 

Almost 80% of the data mismatch issues will get fixed doing this.


IMPORTANT TIP:
Solution: Try unchecking Report Based Total and checking Server Complex Aggregate in certain cases.




 

 

 

 



Reason: Server Complex aggregate will take the aggregation rule applied
in the rpd. Report based total should be unchecked on a level based measure
column to get the correct total.
If report based total is checked then it will show the aggregate
result(ie total,count…) after applying all the filters and aggregation rule).
If it is not checked then it will show the results before applying the filters
and aggregation rule (eg:there is no meaning in taking the total for a
level based measure.The total should be taken before applying aggregation
rule.hence uncheck report based total here)

For more see:
http://pagelock.blogspot.in/2012/07/report-based-total-in-obiee.html

http://pagelock.blogspot.in/2012/07/aggregation-rules-and-functions.html

NOTE1:In 11g we can apply Report based total for each column in the
report whereas in 10g we can apply it only for the complete report.

NOTE2:While migrating 10 to 11g ,report based total and default is
checked for all columns in the report level if any of the view was using
the report based total.

EXPLANATION:


Report-based Aggregate
When using report-based aggregates, you might obtain different results in 10g and 11g. You might see the different results in 11g when the following are true:
  • When a column has an aggregation rule of "Server Complex Aggregate".
  • When a pivot table includes sub-totals, grand totals, or other columns in the Excluded columns section.
  • When a column has Report-based Total (when applicable) checked.
The difference occurs because the data for this column for sub-totals was produced, for example, by the aggregate() function in 10g. In 11g, this issue is resolved by using the report_aggregate() function to accurately reflect the Report-based Total (when applicable) setting.

TIP 3:

Possible cause: Basically now all aggregation needs to be applied at criteria level and not at report level.Because in 11g what is set in criteria is not overridden even though we change it in report level.
Solution: Apply aggregation in criteria.

































TIP4:


IMPORTANT TIP:
Check whether any column is hidden in criteria.This may be causing a data mismatch. Unhide that column in criteria.




















 EXPLANATION:

Hidden but included data is not displayed
In 10g, if a column is hidden but included in a pivot table, the data is displayed in the pivot table. In 11g, if the column is hidden at the criteria level, then the data is not displayed.


TIP 5:

Scenario: Number of rows is less in 11g compared to 10g

Possible cause: In 11g when we apply filter on a hidden column it will be filtered but in 10g it won't be filtered if that column is hidden.
Solution: 11g is correct.


TIP 6:

Scenario : Decimal places mismatch or very slight mismatch.

Possible cause: (integer value) divided by (integer value) will give an integer value.

Solution: Convert the integer value to float and then divide.
Cast( “int x” as float) / “int y” = “float z”

In some cases rounding will work out. 


Integer division returns integer in 11g
Integer division that returns double point data in 10g returns an integer in 11g. You might notice these return values for a column in the following cases:
  • The results of the analysis lose the decimal points.
  • Data order is slightly different for the column. Because data is sorted without decimal points, some rows might contain the same values.
  • Formatting of the column is changed because the default formatting for integer (if configured) is used. (In 10g, the default format is used for double data types.)
To force integer division to return double point results, cast the numerator to a double point data type before the division. For example:
“int x” / “int y” = “int z”
Cast( “int x” as float) / “int y” = “float z”
Cast( “int x” as double) / “int y” = “double z”
If this is different behavior than you saw in 10g, then it is because a known issue caused integer division to incorrectly produce a double point result when certain physical data sources where used.




TIP 7:

Scenario: Decimal places missing.

Solution: Set decimal places to the desired value.
































EXPLANATION:

Integer data types now double data types
You can override the default data format for columns that were integer data types in 10g, and are now double data types in 11g. Without this workaround, the data for this column displays as a decimal number with two digits to the right of the decimal point.
You might notice this change for a column in the following cases:
  • The results of the analysis show decimal points where integers were shown in 10g.
  • Formatting of this column is changed because the default formatting for double (if configured) is used in 11g.
You can using the following options to maintain the same result as 10g:
  • Use the Cast function to cast the values to the appropriate data type in the metadata repository.
  • Change the formatting for the column to display only integers and save that specification as the default format.

Above mentioned tips will help you in fixing 90% of the data mismatch issues.If you are not able to resolve the issues even after trying out the above methods then feel free to post the issues here so that i can help you out.

I will be discussing other issues in another post. Please let me know whether this post was helpful by putting in your valuable comments. I will be posting further based on this feedback.

Till next time .... :)



5 comments:

  1. Hi Naveen,

    Thanks for your reply. And im trying out to sort the issues. Will let you know if any concerns. Thank you so much for you response..!!

    Regards,

    Rajesh

    ReplyDelete
  2. Thanks very much for all tips! It was very usuful to solve missatch on total column but I still have problem with total on rank(mesure) column ¿any idea to solve that?

    Regards

    Laura

    ReplyDelete
  3. Hi Laura,

    Can u please elaborate the issue. If possible can you send a screenshot of the issue to naveenupendran@gmail.com

    Regards,
    Naveen

    ReplyDelete
  4. i did the following and the values are showing as integers in the report and filters now but the prompt is still showing double. Should I do the CAST in the RPD to fix this? thank you. Hermilo.

    "Change the formatting for the column to display only integers and save that specification as the default format. "

    ReplyDelete
  5. hi ,
    in 11g the total data is mismatch compare to 10g even I tried above methods it is not working, after adding grand total row the data is matching .how to resolve this issue without adding grandtotal row

    ReplyDelete

Thank you

- Naveen