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.