OBIEE Online Training

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

Tuesday, January 10, 2012

OBIEE 10g to 11g Upgrade

Hi, Iam an OBIEE developer. Presently iam working in an upgrade project .I searched a lot for articles related to upgrade in the internet.But i couldn't find anyone who could tell me correctly where to find the related articles.So iam using this post to tell you where exactly you can find the documents related to upgrade. I will be happy if this will be of some use.

1.Oracle Upgrade Document:

This document gives a fair idea about how to upgrade a 10g rpd and webcat to 11g.

2.See below the frequently seen differences in the 11g reports compared to its 10g version after upgrade.
These differences have to be fixed manually after upgrade.I will be updating the steps for fixing the bugs in my next post.Also i will be updating the steps for fixing data mismatch in the reports and the bugs which Oracle Support has classified as "Cannot be fixed".

Additional Criteria columns added
After upgrade, additional criteria columns might be added if there are chart interactions defined in 10g charts. You might see a column that is repeated multiple times. Chart interactions are migrated to the Interaction tab of the Column Properties dialog for columns within the Criteria tab. To avoid affecting other views, the upgrade process adds new columns to the criteria for each chart that includes chart-level interactions.
Additional popup menu for analyses
In 11g, you might notice a popup menu for analyses that supports multiple links for navigation. You can turn off the menu in the Interaction tab of the Column Properties dialog if you have only a single link.
Aggregate rule for running aggregates must be in Answers Reports after upgrade
If a column formula includes running aggregates (such as, MAVG(), MSUM(), RSUM(), RCOUNT(), RMAX(), RMIN()) and views include sub-totals, grand totals, or other columns in the Excluded columns section, then the data is displayed differently in the results in 11g. This difference occurs because the aggregation rule is not specified for this column. Ensure that you determine the correct aggregation rules for the columns. If you want to see the same values as in 10g, then include the following aggregation rules in the Edit Column Formula dialog:
  • MAVG(), MSUM() = Aggregation Rule: Average
  • RSUM(), RCOUNT() = Aggregation Rule: Sum
  • RMAX() = Aggregation Rule: Max
  • RMIN() -> Aggregation Rule: Min
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.
Attribute column in measure section might be repeated in a pivot table
In 10g, if you have an attribute column on the row edge and in the measure section, the column is displayed blank. In 11g, the column shows the exact value of the attribute; therefore you might see repeated values.
Calculated items added to all the views
Calculated items are generic in 11g and added to all views. In 10g, only pivot tables have calculated items. If you upgrade 10g analyses that include calculated items, then after upgrade, the calculated items are added to all the views.
Conditional formatting enhancement
In 11g, conditional formatting that is added in criteria applies to both tables and pivot tables. In 10g, conditional formats based on another column did not apply to pivot tables.
Data formatting might change in 11g
In 11g, data formatting in some analyses might be different than the data formatting in 10g. For example, if an analysis for 10g has two decimals, then you might not see those two decimals when the analysis is upgraded to 11g.
In 11g, the system attempts to honor analysis-level or view-level data formats. However, in cases where no data format has been specified, the system relies on the default behavior of the graphing engine. This reliance might create differences in formatting between analyses in 10g and 11g.
Default number of pivot table rows has changed
In 11g, pivot tables have a default of 25 display rows. You can change this number using the DefaultRowsDisplayed setting in the instanceconfig.xml file. See Chapter 18 of Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for information.
Font weight and alignment issues
In 11g, there are changes in font weight and alignment that result from changes in default style sheets and skins.
Grand total is not highlighted with color
The default formatting for grand totals is different in 11g, and this difference might cause missing color highlighting for the grand total row.
Gray cell borders not kept
In 11g, there is a change of borders from bottom/right to top/left for hierarchical columns. You can reset the borders manually using analysis definitions.
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.
Hidden columns used for labels are not displayed
Hidden columns used for labels in 10g are not displayed in 11g. If you have a column that is used as the label for a graph, but the column is hidden from the graph, then in 11g, the labels are not displayed.
Incorrect formatting while using reserved keywords
In 11g, reserved keywords, for example, CASE, WHEN, SELECT, PERCENT, must be double quoted. Otherwise, you might see in incorrect formatting.
Missing view in 11g
In 11g, the query does not run if there are no data views in the analysis. In 10g, the query runs and displays a No Results view if there were no results. This difference might cause a missing view in 11g.
Navigations, drills, or action links might result in additional filters
In 11g, extra filters might be displayed while navigating, drilling, or clicking action links. The value of the item clicked and all the values to the left of the item clicked are passed, including the values for any columns that are set to repeat. This could result in additional filters being applied. However, only the value of the item clicked and values of items to the left are passed in 10g.
No Results message displayed
In 10g, if there is a column selector and the first column in the column selector does not return any results, then the column selector view is displayed, which allows you to select other columns. In 11g, a No Results message is displayed and you cannot see the other columns. See the "Analysis Properties Dialog" topic in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition for information on setting the No Results message.
Oracle BI Server returns all month and day names as three-letter abbreviations.
The BI Server returns all month and day names as three lettered abbreviations. To use full names, modify the NQSConfig.ini file to specify YES for the following values:
After this change, any analysis that uses a CASE statement with month names or week names must match the statements to be either long name or short name according to the setting in the NQSConfig.ini file.
Possible data difference with time-series data
In 11g, time-series functions, such as ToDate and Ago, do not automatically add hidden key columns to the analysis as in 10g. This might cause possible data differences in 11g.
Report_count does not support distinct keyword
In 11g, Report_count does not support distinct keywords. Therefore, analyses must use count (distinct) instead of countdistinct. This requirement affects the data when you select to use TopN or BottomN in the filter for a measure.
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.
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.
Return data from certain column might be different
In some situations, the return data type of certain columns in 11g can be different from 10g. In 10g, some data sources, such as SQL Server and IBM DB2, return an integer for division formulas such as AVG, while other data sources return a double. In 11g, the results of all division formulas are promoted to a double type regardless of the data source, for consistency and for a more correct and precise value.
This difference might impact analysis results because some analyses from previous releases might not be formatted properly for decimal points. If this occurs, then override the default data format for columns that were integer data types in the previous release, but that are now double data types. If you do not perform this step, then the data for affected columns is displayed as a decimal number with two digits to the right of the decimal point.
See the description for the "Integer data types now double data types" change for details on how you might notice this change and on how you can obtain the same results as in 10g.
Rows might be missing in pivot tables
In 10g, rows without any measure data are displayed. In 11g, these blank rows are suppressed. As a result, you might notice missing rows.
Column sort is discarded
If there are two columns with a sort by the first column and then sorted by the second column, and you choose to exclude the first column in the view, in 10g, the first sort is still respected. In 11g, the first sort is discarded and the data is sorted only by the secondary column included in the view.
Sort order might be different
Some 11g reports might have a different sort order than those same reports in 10g. For example, the default sorting for pivot tables in 11g is from left to right. In 10g, pivot tables have no default sort.
Measure-based sorts are not implemented completely for pivot tables and graphs in 11g. Graphs do not provide user-interface elements to emulate measure-based sorts. This is a restriction in 11g, and you can use workarounds to resolve sorting issues.
Axis label ranges changed
The ranges for the numeric axis labels in graphs have changed from 10g to 11g due to a different automatic axis range calculation engine.
Data different in bar graph- services dashboard
For 11g, the data format has been enhanced to show the differences between double and integer data types. You can resolve this issue manually by overriding the default data format for columns that were integer data types in 10g and now double data types in 11g.
Different axis value in 11g
In 10g, graphs do not always honor criteria-level formats or other global data formats for columns. Data labels and numeric axis labels do not consistently follow this formatting. This issue has been addressed in 11g.
Drill-down on a graph might show different results
During upgrade, any existing 10g interactions are placed on the measures and are no longer available in an axis or a legend. To invoke the action, you must click the measures in the graph rather than the axis labels or legend. You can add action links directly to a column placed on an axis or legend by adding an action link to the column within the criteria, after which the added action links are displayed on an axis label or legend.
Graphing engine is not responding
In 11g, the default value of the graph data that is sent by the JavaHost to Presentation Services is 4 MB. If you have a graph with a large size, then you might see a message that the graphing engine is not responding. To work around this issue, increase the graph data size in the instanceconfig.xml file. The following example shows how to increase the graph data size to 6 MB. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for information on editing the configuration file.
Graphs might have missing labels
Some axis labels (on both numeric and category axes) might be skipped as a result of the automatic label layout algorithm in use for 11g.
Graph labels for Y-Axis cannot be rotated
You cannot rotate graph labels for the y-axis other than 0-90 or -90. You cannot perform 45-degree rotations.
Behavior of grid lines in area graphs
In 11g, grid lines are drawn on top of the area markers in an area graph; that is, the grid lines are visible on top of the plotted area. In 10g, the grid lines are not drawn over the area markers.
Line graphs are stacked
Stacked line-bar graphs in which 2 or more measures are shown as lines on the same axis and are not stacked in 10g are stacked in 11g.
Some measures rendered as lines are now bars
In 10g, some measures are randomly selected to be displayed as lines instead of bars for line-bar graphs. In 11g, the plotting of the measures depends upon the graph definition in the analysis and is respected. If the measures are defined to be displayed as bars, then they are displayed as bars.
Missing unknown column in a graph
10g adds an unknown column to a graph whenever the graph definition was not completed by the columns currently in the layout. This is fixed in 11g so you might see a missing column in the graph. No additional columns are included in the layout, and you see a message box instead. During the upgrade from 10g to 11g, all such unknown columns are removed from the analysis because they are considered to be invalid; that is, not present in the query for the analysis or in the XML file for the criteria.
Multiple pie graphs in 11g for single pie graph in 10g
Oracle BI EE 10g does not support multiple pie graphs; however, 11g supports pie graphs for all columns. This enhancement might result in multiple pie graphs after upgrade.
Navigations in graphs have changed
11g graph navigations have changed. If you have navigations on the axis labels or legends in 10g, then they are now moved to the criteria level and are therefore not available.
Negative pie graph values not rendered
In 10g, pie graphs display absolute values, including negative values. Negative values are interpreted as positive values and those slices are displayed. In 11g, slices are not displayed for negative values. When all the values are negative, the graph is not displayed. In 11g, the legend is displayed for negative values.
Pie graph has legend with a "mini" pie graph
When you select to use a graph in a legend that reduces the size of the graph to be too small, 10g does not show the entire graph. However, in 11g, the engine attempts to display graphs in the smallest of spaces. The layout algorithm tries to allocate the maximum area possible to the graph. For legends with too many items, scroll bars are included to avoid compromising the area allocated to the graph.
Right-side scale might be missing from graphs
In 11g, the graphing engine maps the Y2 axis in a line-bar graph to a line. Therefore, even though axes are not synced, the Y2 axis cannot be shown because there is no data for a line.
Possible duplicate navigations
In 11g, action links are generic and upgraded to criteria action links for measures. As a result, there might be duplicate navigations. In addition, a view might be pointing to a non-existent analysis, which results in a "Path Not Found" error.
For example, suppose that you have a 10g report with two hidden graphs, and each has an action link on it. When the report is upgraded, all graph action links are upgraded to criteria action links for measures, which results in additional action links in other views. In this example the action links in the original graph point to non-existent analyses. To work around this situation, manually remove such action links.
Possible mismatch between legends and graphs in 11g
When a stacked bar graph is upgraded from 10g to 11g, the order or position of the series might change. However, the legend view is upgraded without any change. This might cause a mismatch between the legend that is displayed in the legend view and the color that is displayed in the graph. To resolve this, either change the color in the graph or update the legend to match the color in the graph.
In addition, the stacking order in the bar graph changes when you include a column in Vary Color By. For other cases, the order and coloring is maintained. The legend is incorrect or mismatched when you specify conditional formatting on the column in Vary Color By.


  1. Wow!! ...Thanks a lot friend...even i'm working in a similar project ..this could really come in handy for me ...

  2. Hi gives me gr8 pleasure that you found my post useful..thanks for visiting my blog :)

  3. thnaks buddy very informative...

  4. Hi Naveen,

    Please send the information to fix the above listed issues.And Please tell me hoe to fix a data mismatch issue in 11g.

    When i compare a same report in 10g and 11g, i find data mismatch in 11g. Give me the reason and solution for this.

    Mail you solution to this E-mail:

    Thank you !!


  5. Hi Rajesh,
    Can u please send me screenshots of the data mismatch(10g and 11g) so that i can suggest some workarounds.Send it to


  6. Hi Friends..

    Refer the below link for fixing data mismatch issues :

    Best Regards,

  7. Refer the below link for Obiee 10g and 11g difference

  8. Hi,

    Can someone help me please, I need to write a 10 page paper on the key differences of OBIEE 10g and 11g. The benefits of moving from 10g to 11g, I am interested only knowing the key differences in the front end i.e. Reports and Dashboards.

    It would be very much appreciated if someone could help or point me in the right direction to where i can get this information from.


  9. Hi Friend,

    Oracle will be stopping the support on OBIEE 10G from next year(2013). Hence all the companies using OBIEE 10G will surely have to upgrade to OBIEE 11G. If you are planning for a fresh OBIEE project then it is suggested that you go for OBIEE 11G. This is because, the latest version of OBIEE that is OBIEE is very stable and hence all companies will start using this.
    Being the latest version it has lot of newly added features like Map Viewer,KPI and Scorecard etc. Also in OBIEE 11G there are concepts like RCU,preconfigured scheduler and other tables,fusion middleware,weblogic server,ease of clustering,application roles brought into security...
    As far as the dashboards and reports are concerned i agree that there are some look and feel changes. You can search in google for these differences or you can compare by installing both 10 and 11g versions of OBIEE if you have time.


  10. Hi Naveen,

    thx a lot for your advices.
    I recently upgraded from 10g to 11g and I experience a lot of data mismatch, especially the GRAND TOTALs in Tables and Pivot Tables are a real mess.
    Manually changing aggregation rule from DEFAULT to SUM for each report is a way to solve this. But then I have to fix more than 800! reports, so this is not the best way. Do you know any way to fix this by scripting?
    However not each report with aggregation rule DEFAULT has a mismatch compared to report in 10g. Why is this? Version is OBIEE, installed on a Linux machine.

    Thanks for some help

  11. I am glad I share the same skill set with you. You are a great help for others.

  12. Hi Naveen.

    I was getting this error when i am opening a report in OBIEE 11g."has metadata data type of numeric, but the actual result data type is double. This can be resolved by having an administrator refresh the server metadata."

    Could you please provide me a solution on this ??

    1. regarding the error when the report is opened
      You can probably cast the column in the report which is throwing error as double.
      CAST(col name AS DOUBLE)

  13. Hi Naveen, can you provide more details or a link that helps clarify you comment regarding Oralce support for OBIEE 10g "Oracle will be stopping the support on OBIEE 10G from next year(2013)"?

  14. Hi, I'm getting a No Results message after using 2 columns from 2 different folders. This happens after an upgrade, and in 10g is working correct. Do you have any ideea with this is not working in 11g?

  15. can anyone help me with following issues? i am upgrading from obiee 10g to

    1) the pie charts in 11g are looking different from obiee 10g
    2) the values in few dashboard prompts are different in 10g and 11g(like x prompt in 10g having 1500 values and same x prompt in 11g is having 34000 values )

    could anyone help me providing fixes for these issues ??

  16. Hi,

    I'm getting following error while running OTBI report:

    "has metadata data type of numeric, but the actual result data type is double. This can be resolved by having an administrator refresh the server metadata."

    Is there any solution to fix this?



Thank you

- Naveen