How to find the documents impacted by calculation engine changes
Calculation engine changes: What’s the impact on me?
As you might have seen on SCN, there are quite a few Calculation Engine changes in BI 4.0 and BI 4.1 in different SPs. The summary on SCN is here:
The most interesting part for me were the latest changes in 4.1, namely with Merged Dimensions, the Where operator and Running Calculations. My work mate Raphael Branger wote an excellent blog entry about the Merged Dimension weirdness (a bug-fix that is technically correct but kind of unexpected). He calls it: The bug paradox: When fixing the bug leads to wrong reports
The usual question is then: What’s the impact on our environment? In this specific case it’s pretty difficult to say, it depends how often the functionality has been used in reports. You would need some tool that cannot just analyze the CMS database (which is hard enough alone), you would also need a tool that can look into a report, extract the formulas used in the variables and then store this information somewhere.
A tool that analyzes your documents: 360eyes
I found a tool that did this job very nicely, it’s named 360eyes (part of the 360suite) 360eyes | GB & SMITH
It can extract information on each and every WEBI document in your environment. It will tell you exactly which variables you have in your document, what their definition is and where they are used. The latest version (I used v1.47) also extracts information about Merged Dimensions. The initial simple setup (from knowing it zero to my first extract for a single folder) took me 1h, the more complex setup in command line mode with remote scanning 4h. The longest time is really the scanning. In my environment the WEBI collector scanned approx. 750 documents per hour. But then again, 750 docs per hour… That’s roughly 5s per document on average… That’s actually pretty quick.
Pic1: Different collectors in the CMC
How does 360eyes work?
I found 360eyes relatively easy to configure and use. It’s basically a set of data collectors (basically some jar Files) which write to a common database (which you have to provide, any empty schema will do). There is a collector for CMS data, there is one for WEBI reports and a few others. I’ve only used the CMS and the WEBI collectors so far but found them working really well. I was especially surprised by the robustness of the code, considering the amount of dodgy documents we have in our environment (some have survived 3 or more migrations, some can’t even be opened properly anymore). The collectors coped well with error messages of all kinds and just took notes in their DB and went on to the next document. I guess I got used to some SAP tools that just fall into pieces if you present them some older content. So, nice surprise here.
Option 1: Simple, within BO Enterprise
You can use the collectors in different modes. The original idea (and the most simple mode) is to unzip the 360eyes directory somewhere on your server, than upload a BIAR to your server and run the job from within CMC or Launchpad/InfoView. The BIAR contains the data collector .jars as program objects, a Universe for every collector and few sample Webi reports.On your disk you have a few helper jars, a log directory and also a parameter file. In this simple mode, you basically define what you want to scan in the parameter file and to which database you want to write the results. Make sure your Path on the Program Object and on your disk match (especially the 360eyes folder you unzipped and the path to the BI 4.x Java SDK library). And the just schedule the CMS job first to create the set of tables and some basic information, afterwards you can immediately run the WEBI job. They can run in parallel, but it’s the CMS jobs that creates the initial tables.
Option 2: GUI, standalone, outside of BO Enterprise
The second mode is the GUI mode. Instead of uploading the jobs to the repository and run them from there, you can also use a little GUI to run the collection job outside of BO. I didn’t use that very much but it was helpful to understand the concept.
Option 3: Commandline, standalone, ideal for recurring scans on remote BO systems
The third mode was very interesting for me, the command-line mode. I was not allowed to install something on the BO production machines, so I was looking for a way to scan these machines remotely. 360eyes does this perfectly fine, as it uses the standard BO SDK to collect the information. It’s similar to GUI mode but instead you configure your job parameters on the command line. You basically call a java.exe with a jar and pass some parameters like CMS host, CMS user, CMS pass, the JDBC connection where you want to put the extracted data and so on. I used this mode very much and scanned 4 production environments around the globe with 80.000 users and around 100.000 documents. The tool was really stable, no issues whatsoever.
Pic2: WEBI Collector in action, one document is faulty (logfile is reduced (see snip) to show only the interesting bit)
The 360eyes database
The data in the 360eyes database is organized in snapshots. That’s a very clever idea, I think. It allows you to scan your system at a certain point in time and create another scan a bit later. You can then compare the data. The data is completely open in the database in easy-to-understand tables with meaningful names. I had no issues understanding the schema. One cool feature is also the ability to combine the extracted data with Audit data. You tell the tool where your Audit database is and then it extracts this information as well and combines it with the extracted document information.
Pic3: Database structure of 360eyes database
Finally: A report, based on the extracted data
Once you have your data collected, you can go straight to the 360eyes database and collect the data from there. Or, being a BO admin, you could also use one of the Universes that come with 360eyes and try out a sample report or create your own. I used the 360_reports Universe which focuses on the extracted information about the reports. This is where I finally found the information which reports were using Merged dimensions and Where variables. I decided to create one Excel per Business project by using BO Publications with Dynamic Recipients and help the projects with some numbers about their documents. They now know which documents to focus on during their testing phase.
Pic4: A dataprovider in a Webi document, based on a WEBI extract
Pic5: A finished Excel export, showing which documents use Merged Dimensions
All in all, a very nice tool. I was also very much impressed by the support. They were very responsive and helped me to get up to speed quickly. Much appreciated!