Skip to end of metadata
Go to start of metadata

Overview

This document describes the corrections and changes to the calculation engine in Web Intelligence 4.x compared to previous versions of Web Intelligence 4.x or to Web Intelligence XI 3.x and XIR2. It compares the new behavior of the calculation engine to its behavior in previous versions.

This document also suggests migration strategies to accommodate the calculation engine changes.

Finally, it gives a description of the formula rewrite mechanism introduced in 4.1 SP03 to preserve the reports created with an older version, from specific changes.

Originally created by Pierre Saurel & Gregory Botticchio, this document is now maintained by Pascal Gaulin, Web Intelligence Product Expert.

Introduction

The calculation engine in Web Intelligence is regularly updated to include corrections and improvements.

This document only describes the modifications which might unexpectedly impact the calculation results in Web Intelligence documents.

Where() Operator

Where() operator on measures

Prior to XI 3.0, the Where() operator accurately supported conditions on dimensions or detail objects only. Conditions on measures were possible, but did not always return accurate results. Web Intelligence XI 3.0 fully supports the usage of measures in Where() conditions. More details can be found in the documentation.

Where() operator on measures with a condition on a formula based on a dimension

Previously, dimensions were incorrectly added to the dimensional context of the condition. Now dimensions are only used for the conditional evaluation.

Example:

[Revenue] Where (DataProviderType (DataProvider ([Quarter])) = “Universe”; )

When used in a table with [Quarter], the result of the formula with the condition was processed without [Quarter] in the table (same value replicated for each different quarter).

Migration:

Users can aggregate on the related dimension in the context of the measure:

[Revenue] ForAll ([Quarter]) Where (DataProviderType (DataProvider ([Quarter])) = “Universe”; )

From BI 4.1 SP03, to ensure that your results match the previous version, the system automatically rewrites the formula using an ad-hoc parameter with the Where() operator to specify the dimension to take into consideration:

[Revenue] Where (DataProviderType (DataProvider ([Quarter])) = “Universe”; ([Quarter]))

This functionality is available since BI 4.1 SP03, for documents created using the following versions:

  • XIR2 all releases
  • XI3.0 all releases
  • XI3.1 SP01 RTM and All FPs
  • XI3.1 SP02 RTM and All FPs
  • XI3.1 SP03 RTM
  • XI3.1 SP04 RTM
  • XI3.1 SP05 RTM
  • BI4.0 SP01 RTM and All Patches
  • BI4.0 SP02 RTM and All Patches
  • BI4.0 SP03 RTM and All Patches
  • BI4.0 SP04 RTM and All Patches

For more details, see the Automatic Formula Rewrite section, below.

Interaction between a context modifier on a measure aggregation and the Where() operator

Dimensions were incorrectly added as dimensional contexts into the list of dimensions for the context modifiers that have been applied to a measure. This problem happened when the Where() operators that used conditions on dimensions were used on expressions that used measures and context modifiers.

Example:

AggregationFct ([measure] ForAll ([dim1])) Where (condition on [dim2]) was wrongly interpreted as:

AggregationFct ([measure] ForAll ([dim1] ; [dim2])) Where (condition on [dim2])

It is now correctly interpreted.

Interaction between a context modifier on a dimension and the Where() operator

When using a Where() operator with a condition on a dimension, applied to an expression on a dimension with a context modifier, the dimension of that condition was incorrectly added to the context modifier.

Example:

[dim 1] In ([dim 2]) Where (condition on [dim1]) was wrongly interpreted as:

[dim 1] In ([dim 2] , [dim1]) Where (condition on [dim1])

It is now correctly interpreted.

Migration:

To get the previous behavior, swap the Where() operator and the In() context modifier.

Example:

[dim 1] Where (condition on [dim1]) In ([dim 2])

Where() operator is incorrectly applied when outside an aggregation expression

When using a Where() operator with a condition on a dimension, outside an aggregation function, the condition was incorrectly applied before the aggregation calculation. The condition is now applied after the aggregation with respect to the calculation and according to the parenthesis.

Example:

AggregationFct ([measure]) Where ([dim]...)

Before, Where ([dim]...) was applied on [measure] before “AgregationFct”.

Now, “AggregationFct” is applied on [measure] before the “Where” operator.

Migration:

To get the previous behavior, move the Where() expression inside the parenthesis.

Example: AggregationFct ([measure] Where ([dim]…))

Filters

NoFilter() function and “In Break” context modifier

When using the NoFilter() function, filters would still be applied if an “In Break” parameter was used. This problem has been fixed and the filters are now ignored, as expected.

Using filters on object details with multiple values

Details can have multiple values. When displayed in a table together with the dimension which they depend on, they will show #MULTIVALUE when there are multiple detail values for a single dimension value, unless the “Avoid duplicate row aggregation” table setting has been checked.

Filtering on details with multiple values would not select the individual values on rows where they show as #MULTIVALUE. To work around this issue, it was then necessary to check the “Avoid duplicate row aggregation” table setting.

This problem has been fixed: when a filter is applied to an object detail where it shows as #MULTIVALUE, this will correctly select the actual value.

Example:

We have an object [Range] with a detail [Detail] which has multiple values:

We set a filter on [Detail] to select the values “220” (which is part of the #MULTIVALUE) and “350”.

Before the fix (error): the “220” [Detail] value does not show in the table, although it has been selected in the filter:

After the fix: the “220” [Detail] value will correctly show in the table, even when the “Avoid duplicate row aggregation” setting is unchecked:

Versions where this behavior has changed:

  • XI 3.1 since SP7 patch 3
  • 4.1 since SP4 patch 10, SP5 patch 6, SP6 patch 1 and SP7

Filter with a detail on a merged object

It is a common practice to create a variable as a detail of a merged object to combine in the same report element, dimensions from synchronized data providers. When using a detail of a merged object as a filter, it even becomes possible to filter any object from the synchronized data providers.

Example in 4.2 SP08:

A table built with the merged object [City] (merged between Query 1 & 2) and a dimension and a measure from the same query (Query 2). When using a filter built from Query 1: [State] in list “California”, the table is not filtered:

If the filter is defined as a detail of the merged object [City], then the table is filtered:

However, if the detail filter has no relationship with the objects of another query, then the filter should not apply to these objects, as in the table below with objects from Query 3:

A wrong behavior has been observed in older versions of Web Intelligence where, if Query 2 and 3 are synchronized (as in the above table with [Year]), then the filter based on Query 1 and defined as a detail of a merged object between Query 1 & 2 will apply to Query 3 as well.

This wrong behavior has been fixed in the following versions:

  • 4.2 SP07 since Patch 12
  • 4.2 SP08 since Patch 7
  • 4.2 SP09
  • 4.3 SP00 since Patch 1
  • 4.3 SP01 since Patch 3

In case the old behavior would be preferred, it is often possible to redefine the filter as a detail of another dimension or merged object or to define it as a dimension rather than a detail.

Running Calculations

Running calculations will not reset

After 4.1 SP03, running calculations will not automatically reset for each new section value. As a result, the calculation for the first cell of a block for a particular section value instance is based on the last cell value of the block from the previous section instance.

Before 4.1 SP03, the running calculation was reset for each new section value.

In the example below, the running sum for 2005 (cell in bold) is independent from the running sum for 2004:

After 4.1 SP03, the running calculation for the current section value is based on the calculation from the previous section. In the example below the running sum for 2005 (cell in bold) is based on the running sum for 2004:


Migration:

To keep the original behavior, you can specify a list of dimensions as a reset parameter (3rd parameter of the RunningXYZ functions). For instance, with RunningSum():
RunningSum ([Sales Revenue] ; ([State]))

From 4.1 SP03, to ensure that you receive results for this formula that correspond to the previous document version, the system will automatically rewrite this formula accordingly (using the keyword “section” as 2nd operand of the running calculation). This function is available only for documents created before XI 2 SP 05.9 versions. For more details, see the “Automatic formula rewrite” section, below.

Data order in running calculations

Running calculations were not respecting the order of the data but the default order of the result set. The running calculations now take into account the order of the data, as displayed in a table or a chart.

Running calculations in cross tables and reset context

By default RunningSum() is evaluated row after row (Z pattern), in cross-tables.

In XI3.x, when adding a dimension as reset context (3rd parameter), the running sum was improperly evaluated column after column (reversed N pattern) instead of row after row. This has been corrected in 4.x.

Migration:

To get the previous result (column after column) in a version prior to BI 4.1 SP03 Patch 3, you can use the value COL as 2nd parameter.

Starting from BI 4.1 SP03 Patch 3, the system will automatically rewrite RunningSum() formulas with an ad-hoc parameter called FORCE_COL, in the body of cross-tables.

This functionality is available as of BI 4.1 SP03 Patch 3 for documents created using the following versions:

  • All XI 3.x versions,
  • BI 4.0 SP02 patches 20 and 21
  • BI 4.0 SP05, all patches
  • BI 4.0 SP06 patches 1 to 4
  • BI 4.0 SP07
  • BI 4.1
  • BI 4.1 SP01 up to patch 1

For more details, see the section on Automatic Formula Rewrite, below.

Running sums with reset in cross table footers

In cross-table footers, the RunningSum() function will sum up the values of its measure

  • per row if it is in the row footer
  • per column if it is in the column footer

Example:

In the following table, we have a running sum in the column and row footers:

If this running sum has a reset dimension on one of the cross-table axis, then it will reset its value at the end of this axis. On the other axis, the reset dimension will be ignored. For example, in the footer of each row, if the reset dimension is [Year]:

Similarly, with [Quarter], in the footer of each column:

In previous versions, the running sum in the footer of the other axis would give unpredictable results. Typically, with a reset on [Year] in both the row and column footers, the result in the column footers would be meaningless:

Versions where this wrong behavior has been corrected:

  • XI 3.1 since SP6
  • 4.0 since SP4
  • 4.1

Date Functions

LastDayOfWeek() uses Monday as first day of week

To respect the ISO 8601 standard and to be consistent with the DayNumberOfWeek() function, the LastDayOfWeek() function now considers Monday as the first day of the week instead of Sunday.

Example:

In XI R2:  LastDayOfWeek (ToDate (“05/11/2005” ; ”MM/dd/yyyy”)) returns 14 May 2005 (Saturday),

In XI 3.1: LastDayOfWeek (Todate (“05/11/2005” ; ”MM/dd/yyyy”)) returns 15 May 2005 (Sunday).

Migration:

To keep the original behavior, use the RelativeDate() function:

RelativeDate (LastDayOfWeek (ToDate (“05/11/2005” ; ”MM/dd/yyyy”)) , -1) returns 14 May 2005 (i.e. Saturday).

Wrong time zone for formula using CurrentDate() and a date field

The time zone of the server was applied to the CurrentDate() evaluation, instead of UTC, when used with another date field in a formula. It is now evaluated in the UTC time zone.

Week() function

The Week() function was giving an incorrect result when the last day of a leap year is a Monday (this situation occurs every 28 years).

Before the update: Week # of Monday December the 31th of 2012 = 53

After the update: Week # of Monday December the 31th of 2012 = 1

MonthsBetween() function

A set of days over 2 months was considered to be 1 month if the starting day # < ending day # of the ending date. Typically, this was not working for months ending with day 30, 29 or 28, compared to a month ending with day 31.

A set of days over 2 months is now considered to be 1 month if the starting day # <=  ending day # and if ending day # is the end of the month and the starting day # > ending day #.

Before the fix: MonthsBetween (31/03/2008 , 30/04/2008) =  0

After the fix: MonthsBetween (31/03/2008 , 30/04/2008) = 1

Merged Objects

Aggregation functions return correct values for original dimensions inside merged dimensions

Prior to XI R2 SP06, Web Intelligence would not return a correct result in the body of a table, when aggregating a dimension participating in a merged dimension (note that the result is correct when the related dimension is in the table footers or in a free standing cell).

In the example below, depending on the query, the number of resorts is different. When asked for a count of the resorts from query 1 or 2, Web Intelligence returns the total number of resorts for the merged object instead of the individual object:

After 4.1 SP03, the system returns the correct count for the queried objects:

Aggregation functions can process individual objects inside a merged object

An aggregation function (Count(), Min(), Max()...) applied to an object [A] participating in a merged object, was processed on the value set of the merged object instead of the given object [A]. It is now processed on the original object [A] value set.

Migration:

To get the previous behavior, you can replace the original object by the merged object.

From BI 4.1 SP03 (patch 2 or upper required), to ensure that you receive results for this formula that correspond to the previous version, the system will automatically rewrite the formula using an ad-hoc function “UseMerged()” with the aggregation expression as a parameter to force the use of the merged dimension. This is available on request in BI 4.1 SP03 for reports created with earlier version of XI 3.1 SP03.2.

For more details, see the "Automatic formula rewrite" section further below.

Aggregation on a variable based on individual objects inside a merged object

An aggregation on a variable object whose formula is based on an object [A] that is participating in a merged object, was processed based on the merged object instead of the given object [A].

The aggregation is now processed according to the given object [A].

Migration:

To get the previous behavior, replace the original object with the merged object.

Aggregation in free cells of an object participating to a merged object, combined with the Where() operator

In free cells, the aggregation function (Count(), Min(), Max()...) applied to an object [A] participating to a merged object was processed on the value set of the merged object instead of the given object [A], when the context of this aggregation was modified by the Where() operator.

Example:

  1. We have a first query “Query1” giving a single value for the [Year] dimension and a second query giving two other values for the same dimension.
  2. When in a table, the formula Count ([Query1].[Year]) Where ([Query1].[Quarter] = ”Q1”) would return 1, which is the correct result.
  3. When in a free cell, the same formula would return 3, which is the result of the merged [Year] dimension (the single value from Query1 plus the two values from Query2).

This wrong behavior was found in Web Intelligence XI 3.1 SP1 and was corrected in XI 3.1 SP2.

A regression was found in the following versions, when the “Extend merged dimension values” document setting was activated:

  • XI 3.1 SP5 FP5.6
  • XI 3.1 SP6 FP6.3 to FP6.5
  • XI 3.1 SP7

This regression was corrected on the same branches, in later patches.

To get the previous behavior, replace the object with the merged object.

Aggregation of Merged Data from Business Warehouse (BW)

Data fetched from a BW data source have a unique key allowing data with similar values to be treated as different.

In earlier versions of WebI 4.0, this key was wrongly managed when the data was merged, resulting in spurious rows in tables, such as in the example below.

Example with [Region] as the merged dimension:

Since WebI 4.0 SP5 Patch 5, this issue has been corrected. The keys are correctly managed and the above table will show the properly aggregated data with no additional rows:

Versions where this issue has been fixed:

  • 4.0 SP5 patch 5
  • 4.0 since SP6
  • 4.1 since GA

Merged dimensions combined with dimension objects

When using in the same table a merged dimension and an object participating to that merged dimension, Web intelligence 4.0 will perform an intersection of the values coming from the merged dimension and the values coming from the participating object.

Example: We have two queries, each of them returning a year dimension, which are merged together:

When using the merged year with the year from the 1st query, the intersection of the two objects results in the values 2004 and 2005, while with the year from the 2nd query, the intersection of the two objects results in the values 2005 and 2006:

In version 4.1, this behavior has been modified and Web Intelligence will perform a union instead of an intersection of the values. This new behavior has been implemented to comply with the general behavior of Web Intelligence regarding the use of merged dimensions, where the merged dimension always take precedence over any object participating to that merge, thus showing all values from the merged object.

This new behavior results in the same list of values whatever the query where the object comes from. For instance, in the above example, this will result in the values 2004, 2005 and 2006 whether the year object comes from the 1st or the 2nd query:

Versions where this behavior has changed:

  • XI 3.1 since SP4 patch 3, SP5 patch 3 and SP6
  • 4.0 since SP5 patch 15, SP6 patch 10, SP7 patch 6, SP8 patch 1 and SP9
  • 4.1 since SP1 patch 5, SP2 patch 1 and SP3

Custom sorts on merged objects

When defining a custom sort on an object, this custom sort is propagated to all instances of that object in the document. This is not the case with simple ascending or descending sorts, which only apply to the block where they are selected.

When merging custom sorted objects, their custom sorts are disabled. The reason is that objects participating to a merge all share the same list of values, which could therefore result in conflicting custom sorts. The custom sorts are automatically re-enabled when the object is unmerged.

Note that it is still possible to define a custom sort on a merged object. This custom sort will apply to all objects participating to the merge.

Example:

1) Before merge, [Query 1].[City] has a custom sort showing Chicago before Boston:

2) After [Query 1].[City] and [Query 2].[City] have been merged, the custom sort on [Query 1].[City] is disabled and does not show either on the merged object:

In some Web Intelligence versions, the custom sorts are not disabled when objects are merged. Instead, the custom sort of the first selected object is applied to the merged object as well as to all participating objects. Because of the impact on migrated documents, this wrong behavior has been corrected.

Versions where the wrong behavior can be found:

  • 4.1 SP5 up to patch 12, SP6 up to patch 6 and SP7 up to patch 2

Versions where the correct behavior can be found:

  • XI 3.1
  • 4.0
  • 4.1 up to SP4, SP5 since patch 13, SP6 since patch 7, SP7 since patch 3 and since SP8
  • 4.2

Data Ranking

“Ranked by” option using a dimension which is not in the table

Up until 4.0 SP07, a dimension used in the “Ranked by” option of the Ranking functionality is always taken into account, even when this dimension is not part of the table where the ranking is applied.

Example:

Ranking the top 2 [Quantity sold] by [Store name]:

Behavior until 4.0 SP07: if [Store name] is not part of the table, this will not modify the ranking:

Starting from 4.0 SP07, if [Store name] is not part of the table, then the “Ranked by” option is ignored and we therefore get a different ranking. Note that, in this particular case, the aggregated measures are not sorted ([Quantity sold]):

This behavior change can be found into the following versions:

  • In BI 4.0:
    • SP07, since Patch 7
    • SP08, since Patch 3
    • SP09, since Patch 1
    • SP10 and all patches
  • In BI 4.1:
    • SP03, up to Patch 6
    • SP04, up to Patch 3
    • SP05

Starting from 4.1 SP03 Patch 7, 4.1 SP04 Patch 4 and 4.1 SP05 Patch 1, we are reverting to the original behavior (prior to version 4.0 SP07), i.e.: whether or not the dimension used in the “Ranked-by” option is part of the table, this will modify the ranking of the table.

Ranking data by a dimension, in sections

In Web Intelligence 4.0 prior to SP11, ranked measures were not properly sorted when the data was within a section and ranked by a dimension.

Example:

Top 3 [Sales revenue] ranked by [State] in the [Year] section:

When a measure is ranked by a dimension, the sort expression is: [M] In ([D]), where [M] is the measure and [D] is the dimension it is ranked by.

If in addition the data is within a (sub-)section, then the sort expression becomes: [M] In ([D], section1; section2, …etc.), where section1, section2, etc. are the expressions of the sections containing the data block. This is the sort expression which has been fixed and which now gives a correct behavior:

The behavior modification can be found into the following versions:

  • In BI 4.0, starting from SP11
  • In BI 4.1, since SP03 Patch 9, SP04 Patch 7, SP05 Patch 2 and later

Note that there is no behavior modification when there is no ranked by dimension defined for the ranking.

Hiding a Report Element when a Formula is True

This section is about the “Hide when following formula is true” setting. This setting can be found in the Format dialog box of any Web Intelligence report element: tables, charts, forms, sections and free cells.

Formula Evaluation when the Data is Null

Null is not 0. It is neither greater nor lower than 0. Indeed, Null is not a numeric value and will not return any result in a numerical expression. As a consequence, hiding a report element on a formula condition will not give any result when the evaluated data is Null.

Example:

Hiding a table when the formula “[Data] >= 0” is true, will not hide this table if [Data] is Null.

To hide this table, the formula should be: “[Data] >= 0 Or IsNull([Data])”.

A defect in previous versions of Web Intelligence would evaluate Null as a numeric value greater than 0. This issue has been corrected, which may explain why some report elements are no longer hidden in migrated documents.

Versions where the wrong behavior can be found:

  • 4.1 SP3 from patch 2 to 6
  • 4.1 SP4 from patch 1 to 3

Versions where the correct behavior can be found:

  • 4.0
  • 4.1 up to SP2
  • 4.1 SP3 and SP4, outside the above patches
  • 4.1 since SP5
  • 4.2

Formula Evaluation with a Filter on the Data

When a filter is applied to an object assigned to a report element and that filter removes all values of the object, then this object no longer exists in the calculation context of the report element (i.e. at the most detailed level). As a consequence, hiding the report element on a formula condition based on the filtered object is no longer possible: the evaluation returns no result and the report element is not hidden.

In previous versions of Web Intelligence, the filtered object would sometimes remain in the calculation context and be evaluated as Null or zero, depending on the evaluated formula. As a consequence, the report element would get hidden.

Example:

We have the following table:

1) Format the table as follows:

“Hide when following formula is true:” = [Year] <> “2016”

Result: the table is hidden as expected

2) Now, filter out [Year], in the table:

For instance, define a filter such as: [Year] equals to “2012”

Result: the table is no longer hidden. Since [Year] is completely filtered out, it is removed from the calculation context of the table. As a consequence, the “hide on formula” condition cannot be evaluated.

In this situation, hiding the report element can be achieved in two ways:

  • Use the “Hide when Empty” format setting on the report element.
  • Alternatively, the object can be evaluated at the report level in the “Hide when following formula is true” condition, with the “In Report” context modifier. For example: “Hide when following formula is true:” = [Year] <> “2016” In Report will hide the above table even when [Year] no longer exists in the context of that table.

Versions where the wrong behavior can be found:

  • 4.1 SP3 from patch 2 to 6
  • 4.1 SP4 from patch 1 to 3

Versions where the correct behavior can be found:

  • 4.0
  • 4.1 up to SP2
  • 4.1 SP3 and SP4, outside the above patches
  • 4.1 from SP5
  • 4.2

Other Functions and Calculation Changes

Previous() in a cross-table no longer returns values for the first column

In prior versions, the Previous() function carried the last value in a row over to the first value of the next row in a cross-table. This behavior was confusing because there was often no link between the last value of one row and the first value of the next row.

In the following example, using XI 3.0, the first value in the second row returns the last value in the first row, even though there is no link between France and US:

In XI 3.1, Web Intelligence no longer returns a previous revenue for US in 2004 (since there is none available for that report):

This change is also applicable when you use Previous() with the COL keyword. In this case the last value in a column is not carried over as the first value of the next column.

Previous() with display-dependent functions

When using nested Previous() functions, there is a high risk of memory overflow, resulting in a “The server memory is full. (WIS 30280)” error message. This is due to the recursive nature of this function. For this reason, the computation of Previous() has been revisited.

As a consequence, there is an impact on the usage of Previous() along with any of the display-dependent functions LineNumber(), ColumnNumber(), Page() and PageInSection(): They can no longer be used together in the same expression and a #RECURSIVE error will be displayed in such scenario.

As a workaround, it is possible to replace LineNumber() or ColumnNumber() with a running sum. There is no known workaround with Page() and PageInSection().

Example in 4.2 SP08

With the expression =Previous(Self)+LineNumber():

With the expression =Previous(Self)+RunningSum(1)+1:

Versions where the behavior has changed

  • 4.2 SP06 since Patch 11
  • 4.2 SP07 since Patch 5
  • 4.2 SP08

Measures will ignore incompatible dimensions

Prior to XI R2 SP03, a measure in a table would return an empty value when the table would contain an invalid dimension in the section header.

In the example below, [Year] and [Country] are incompatible:

After XI R2 SP03, Web Intelligence returns the measure value calculated using the compatible dimensions. In the example below, [Revenue] is calculated by [Country]:

“If” expressions return the same values for formulas and variables referencing formulas

The sum of a formula containing an "If" expression will now return the same result than a variable referencing an identical formula.

As shown in the following table, in XI R2, the sum for the formula If ([Year] = ”2002” ; 1 ; 0) returns the sum of the visible values, whereas the sum of the variable referring to the same formula (MyVarIf) returns the sum of the multiple occurrences of the underlying data (which are hidden):

If you deselect the “Avoid duplicate row aggregation” option, you can see the duplicated data:

In XI R3 and subsequent releases, the system returns the same result for the variable and the formula:

UNV vs. UNX Count projection function

When creating a universe in Information Design Tool (IDT) or Universe Design Tool (UDT), each measure object can have its own projection function. The projection function is the default aggregation used by the Web Intelligence calculation engine when consuming a measure in a block. The projection function can be a sum (by default), a count, a min, a max, or it can be delegated to the data source. The projection function can also be set to “none”, in which case the Web Intelligence calculation engine will process the measure as a dimension (aggregation by identical values).

The “Count” projection function counts the occurrences of each unique value in the list of values of a measure. But it is processed differently in the Web Intelligence calculation engine, depending on whether the measure comes from a UNV or a UNX universe:

  • If the measure comes from a UNV universe, the count aggregation will not take into account the empty values of that measure
  • If the measure comes from a UNX universe, the count aggregation will take into account its empty values

As a result, if a UNV universe is exported as a UNX universe, a Web Intelligence document built with that universe as a data source might show different results before and after the export operation, if one of its measure objects is using a count projection function.

Starting from version 4.2 SP03, it is possible in IDT to choose between the two count projection functions: count with or without empty values.

Versions where this behavior is observed:

  • Since 4.0 (when UNX universes were released for the first time)

#MULTIVALUE when aggregating dimension variables manually qualified as measures

Dimension variables manually qualified as measures in the Variable Editor have no default aggregation function. As a result, they will show a #MULTIVALUE result when aggregated in a table.

In previous versions of Web Intelligence, the calculation engine would attempt to use a default aggregation, based on the variable definition. However, without any guarantee on the result. Now, the users must define the aggregation function in the variable definition.

Example:

The following table shows 2 rows for “France”:

[Measure Variable] has been manually qualified as a measure and is defined as: If ([Type] = ”A”) Then ([Measure] * 10) Else ([Measure] * 20)

When removing [Type], the table will show a #MULTIVALUE:

In order to prevent the #MULTIVALUE, the variable should be explicitly aggregated. For instance, with the Average() function:

Version where the behavior has changed:

-       4.0 SP6 Patch9

Dimension Variables in the Body of a Cross-Table

Since Web Intelligence 4.0, dimension variables which are assigned to the body of a cross-table are automatically added to the vertical axis of that table. This will synchronize the axis of the table with its body content and thus, will prevent #MULTIVALUE errors. In XI 3.x, this was not the case and users had to manually add the variables to the axis of the table to prevent #MULTIVALUE errors.

Now, when dimensions with incompatible values are assigned to the axis of a same table, these values cannot be displayed and result in empty cells, whatever the table type. This situation can be observed when migrating a Web Intelligence XI 3.x document to version 4.x:

If a Web Intelligence XI 3.x cross-table contains a dimension variable in its body and an incompatible dimension on one of its axis, then this table will be completely empty when migrated to version 4.x. This is because the variable dimension in the body will be automatically added to the table axis and since it is incompatible with the other dimension, this will result in empty cells (you need to modify the document to observe the 4.x behavior, after is has been migrated from XI 3.x).

Example:

  1. We have [Year] = “2001” from query 1 and [Year] = “2002” from query 2. The contents of these two dimensions are therefore incompatible.
  2. We create a variable: var2 = [Query 2].[Year].
  3. In Web Intelligence XI 3.1, assigning [Query 1].[Year] to one of the axis of a cross-table and var2 to its body will result in the following:
  4. Once migrated to Web Intelligence 4.x, the same table will result in the following:

    The reason is that, in Web Intelligence 4.x, var2 is automatically added to the vertical axis of the cross-table. And since its content is incompatible with [Query 1].[Year], then the table becomes empty.
  5. Note that if [Year] from query 1 and query 2 are merged, then Web Intelligence XI 3.x will automatically display the content of the merged dimension in the body of the cross-table:
  6. In Web Intelligence 4.x, the result will still be an empty table:

Workarounds:

  1. If possible, the two dimensions should be merged and the merged object should be used in the body of the table. Since the merged object contains the values from both dimensions, then the table will no longer be empty in 4.x:
  2. If merging the two dimensions is not possible, then the dimension variable in the body should be modified into a measure variable. Indeed, measures are not automatically added to the table axis.
    In our example, defining var2 as a measure instead of a dimension will result in the same output than in Web Intelligence XI 3.x:

Versions where the behavior has changed:

  • Since version 4.0

Rounding method for high-precision Decimal numbers

Since version 4.2, a new data format has been introduced in Web Intelligence, with the name of “Decimal”. This format follows the IEEE 754-2008 standard for decimal floating-point numbers and, with 40 digits, offers a much better calculation precision than the traditional 15-digits binary floating-point numbers.

Later, it was found that the rounding method used by the IEEE 754-2008 standard was not the usual “Up” method used in Web Intelligence and other commercial software such as Microsoft Excel. Instead, the IEEE 754-2008 standard recommends the “Half Even” rounding method as default. Typically, with the “Half Even” rounding method, the value 1.125 will give 1.12 instead of the expected 1.13, when rounded or formatted with two decimal digits.

For a better consistency with the calculations based on traditional 15-digits numbers, it was therefore decided to replace the default IEEE 754-2008 “Half Even” rounding method, by the more usual “Up” method.

Since this modification, all calculations in Web Intelligence are using the same rounding method and return consistent results.

Note that you do not need to modify a Web Intelligence document to observe the new behavior.

Versions where the behavior has changed:

  • 4.2 SP3 patch 13
  • 4.2 SP4 patch 6
  • 4.2 SP5 patch 1
  • All versions since 4.2 SP6

Automatic formula rewrite mechanism

Web Intelligence provides an Automatic Formula Rewrite mechanism that automatically modifies a selection of formulas (see list below) in a document. The formulas that follow a certain pattern are modified when you open a document migrated from a previous version (see above for a list of the applicable versions). After modification the formula returns the same result than before the calculation change.

We then recommend that you save the document so that the modifications are stored in the document, thus completing the formula rewrite mechanism.

The Automatic Formula Rewrite mechanism is available by default for documents migrated to BI 4.1 SP03 for the following formula pattern:

  • “where with dim as parameter in condition”
  • “running calculation reset on section”

BI 4.1 SP03 (Patch 2 required)

  • “merged object in aggregation function”

BI 4.1SP03 Patch 3:

  • “running calculation in column”

The releases that apply for this solution are specified above in the sections.

Automatic formula rewrite mechanism rules

The rules to automatically modify the formulas are stored in an XML file called “Formula_migration_rules.xml”, located in the [installation directory]\[SAP BusinessObjects Version]\[OS]_[PLATEFORM]\config folder.

For example, on Microsoft Windows:

  • Web Intelligence server: (64bits): C:\Program Files (x86)\SAP  BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\config
  • Web Intelligence Rich Client (32 bits): C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI4.0\win32_x86\config

BEWARE!!!

Modifying this file may have an unexpected impact on all of your Web Intelligence documents. In particular if you enable the “force” attribute, the formulas in your documents may be rewritten and introduce behaviors and results that you did not expect.

You should never use the “force” attribute for all of your documents.  Use it only for specific documents. In order to do this you should enable the “force” attribute, open the document, save it and then disable the “force” attribute immediately afterwards.

Note: If you modify the XML file, then you need to restart the server or the application to apply the changes.

The XML file has the following content:

<Rules>
<Rule name=”ExtractPlainDimFromWhereCond” enable=”true” force=”false”>
  <!–List of version where the behavior changed–>
  <Version value=”12.3.6.1006″/>  <!– Titan XI3.1 SP3 FP06 –>
  <Version value=”12.4.1.1188″/>  <!– Titan XI3.1 SP4 FP01 –>
  <Version value=”12.5.1.1357″/>  <!– Titan XI3.1 SP5 FP01 –>
  <Version value=”14.0.5.882″/>   <!– 4.0 SP5 RTM –>
</Rule>
<Rule name=”ResetOnSectionForCumulative” enable=”true” force=”false”>
  <!–List of version where the behavior changed–>
  <Version value=”11.5.10.0″/>
</Rule>
<Rule name=”UseMergeDimInAgg” enable=”true” force=”false”>
  <!–List of version where the behavior changed–>
  <Version value=”12.3.2.0″/>
</Rule>
<Rule name=”UseColForCumulativeOnXTabBody” enable=”true” force=”false”>
  <!–List of version where the behavior changed–>
  <Version value=”12.x.x.x”/> <!– All XI3.x versions –>
  <Version minvalue=”14.0.2.798″ maxvalue=”14.0.2.846″/>
  <Version minvalue=”14.0.5.882″ maxvalue=”14.0.5.1249″/>
  <Version minvalue=”14.0.6.1036″ maxvalue=”14.0.6.1145″/>
  <Version minvalue=”14.0.7.1147″ maxvalue=”14.0.7.1147″/>
  <Version minvalue=”14.1.0.896″ maxvalue=”14.1.0.896″/>
  <Version minvalue=”14.1.1.1036″ maxvalue=”14.1.1.1072″/>
</Rule>
</Rules>

Where:

  • enable=“true” means that the rule is applied, depending on the document version.
  • force=”true” means that the rule is applied, regardless of the document version.

5 Comments

  1. Added a new paragraph on "Dimension Variables in the Body of a Cross-Table"

  2. Added a new paragraph on "Rounding method for high-precision Decimal numbers".

  3. Added a paragraph on "Previous() with display-dependent functions".

  4. Hello,

    It has been a long time since we found a behavior change in the WebI calculation engine. Following a bug fix, we recently found a behavior change when filtering with a detail of a merged object. This is documented in the above paragraph on "Filter with a detail of a merged object".

    Best regards,

        Pascal.