As most of you are probably aware, SAP has released an excellent resource detailing calculation engine changes introduced in BI 4.1 with respect to various releases along the way (e.g., XI 3.0, XI 3.1, BI 4.0). The goal with this post is to demonstrate how Sherlock® can help you to quickly identify all of your Web Intelligence documents that may be impacted by these changes. I will walk through a few of the calculation engine changes identified in the SCN article that are representative samples for how Sherlock® can assist. The idea is that this will allow you to infer from these samples how to apply the same logic to finding impacted reports for the other issues.
If Expressions on Formulas and Variables
The issue was visible in XIr2 where an aggregation on a report level variable that had an IF calculated inflated results. This was resolved in XI 3.1 versions. The same aggregation was not a problem if done via a report level formula. Using Sherlock®, you can quickly identify all of the Web Intelligence documents that have formulas and variables that contain an aggregation calculation against if conditions. In addition, you can useful details about the location of the documents, the owner, when it was created, the last time it was viewed, etc….
Running Calculations with No Reset
For Web Intelligence documents that contain a running aggregation, there was a problem with running calculations not resetting before a new block started. As such, the calculation for the first cell in a new block was based on the value from the last calculation in the previous block – which is incorrect. Again, Sherlock® allows you to quickly identify those documents that may be impacted by a running calculation. In the screenshots below, you can see the list and location of all reports that contain a RunningSum().
As this particular issue deals with those running calculations that do not leverage a reset, you can also easily identify those reports that are using a running calculation and are already using a reset dimension. This allows you to see the list of reports that are using a running calculation and are correctly using the workaround suggested by SAP.
LastDayOfWeek() Now Uses Monday as Start of Week
To conform with ISO 8601 standards, date calculations such as LastDayOfWeek() now use Monday as the first day of the week. Instead of showing the report results for this one, I’ll show the query behind the report. As you can see from the screenshot, it is easy to quickly change the pattern for which you want to search through variables and formulas within the body of a Web Intelligence document.
Previous() Modification for First Columns in Cross-Tab
Using the same logic above, you could search for all Web Intelligence documents that are using the Previous() function. At this time, it is not possible to limit these results to only those documents containing cross-tabs.
Aggregation Functions Applied to Original Dimension from Merged Dimension
The identified issue is that any aggregations applied to dimensions that are members of a merged dimension would not return correct values. Instead of aggregating the individual dimension, it would aggregated the merged dimension instead. Using Sherlock®, you can identify a couple of factors that would be relevant to making your investigation of this issue easier. You can identify reports that contain an object that is used inside of a merged dimension. From there you can then determine all of the variables or formulas within those reports that contain an aggregation (e.g., sum, count, etc…). You could do this manually with a single query or you could use a query based on the results of another query. In the latter scenario, you would need a query that returns all of the report IDs that contain merged dimensions and then a second query that uses that list of report IDs to return the report names, paths, owners, etc…..for all reports that contain a variable or formula with an aggregation.
Again, here I’m showing the query panel as this is most relevant for this scenario.
Where Operator on a Measure with a Condition on a Formula Based on a Dimension
As you can probably guess, this one is a bit complex. You need to look for all documents that contain a calculation against a measure object and the calculation using a where operator with a condition based on a dimension. Sherlock® can give you the report details (e.g., name, location, owner, etc…). It can give you the calculations used behind variables and formulas. It can give you a list of reports that contain a measure object. Combining all of these together, you could get a list of reports that contain a measure and a variable or formula with a where operator in a calculation. While this doesn’t give you the quick hit list of all reports that may have this particular problem, it does give you a shorter list of reports to review.
For the other issues affecting documents using where operators, it just becomes a matter of tweaking your search pattern for the [Report Variable Text] and [Report Formula Text] objects from the Sherlock® Universe. Then combining the results with the correct details from either the Universe behind the report or details about the report itself.
Merged Dimensions
The SCN article contains a detailed list of particular calculations against merged dimensions that may have issues. Using the same methods we’ve already discussed you can see potentially impacted reports. The key is to use the [Object Used in Merged Dimension] indicator from Sherlock® combined with a proper search pattern for variables and formulas. For example, one of issues relates to the use of CurrentDate() along with merged dimensions. The screenshot below shows the results of a query returning details about reports that contain merged dimensions and also leverages CurrentDate() in a calculation.
Using the same logic, you can search for reports containing merged dimensions that use Week(), MonthsBetween(), or NoFilter() functions as well.
I hope that this post helps you identify where Sherlock® can assist you with determining the true impact of these calculation engine changes as you start investigating applying BI 4.1. If you have any questions or comments, let us know.
Thanks for reading and thanks to Gregory for his excellent article on SCN.