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.
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:
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.
TIP 7:
Scenario: Decimal places missing.
Solution: Set decimal places to the desired value.
EXPLANATION:
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.
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:
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:
|
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:
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:
You can using the following options to maintain the same result as 10g:
|
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.
No comments:
Post a Comment