The Assignment
On a recent client engagement, our statement of work dictated that we build reports and dashboards from a SAP BusinessObjects Budget, Planning and Consolidation (BPC) environment. In SAP BusinessObjects 4, we have two methods of hosting a BPC environment: SAP NetWeaver and Microsoft Analytic Services.
In this engagement, we are building reports against SAP BusinessObjects BPC for the Microsoft Platform 10. The standard workflow that we follow is to build the OLAP connection to the BPC system. Ideally, we would connect to the BPC application via the BPC connector. Next, we would build and publish a semantic layer on the newly created OLAP connection. Finally, we would build our reports and visualizations based on the newly created Semantic Layer.
The Problem
After working with the standard BPC connector for our BPC installation (SAP BusinessObjects Planning and Consolidation for the Microsoft Platform 10), I started encountering very strange issues. First of all, I was able to create the OLAP connection and build the semantic layer on the connection. When I started using the semantic layer in Web Intelligence, I noticed that the performance of the system was abysmal and the data coming from BPC was not correct. Every time I opened the Member Selector for our various objects, it would take approximately 3 – 5 minutes to populate the members.
It also seemed that the Internet Information Services (IIS) application on which the BPC web service was becoming bogged down. Restarting IIS seemed to temporarily fix the issue. The issue continued to happen again and again. I also encountered an issue where I could build a report on the BPC system and the data would refresh the first time. When I tried to change the query or refresh the query, I get a message that GroupSets (SmartMeasures) are not supported. Keep in mind that I did not change the query at all, but simply refreshed the report. If I drop out the Measure, then the report will refresh. If I add the measure back in, the report will refresh the first time but not again. I then get the same GroupSets (SmartMeasures) error.
Finally, I started getting an error that is related to a time hierarchy. Running a Webi Report with this hierarchy started throwing the generic “WIS_30270” error. When I look at the logging files available on the BI4 server, the errors relate to a potential date conversion that Webi is trying to do. I changed the qualification on the universe level to Standard (from Time) and the issue is still happening. The field is showing as a Time object in the Operations cube and the IDT is recognizing it as such, but I think that the values of the field (i.e. 2012.Total) is throwing off the date parsing since 2012.Total isn’t a valid date.
The Work Around
Some of our consultants have long standing experience in connecting SAP BusinessObjects reporting and analytics to BPC systems. I decided to try connecting directly to Microsoft SQL Server Analysis Services, which is where the InfoCubes are stored. To do this, I followed the steps in the following Microsoft article:
Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 7.0: http://msdn.microsoft.com/en-us/library/gg492140.aspx
Once I set up the msmdpump.dll within the confines of a Web Service hosted on IIS within its own application pool, I was able to successfully test the Analysis Services web service using Excel and Management Studio as recommended within the Microsoft Article. Finally, I created a new OLAP connection in the CMC. This time I selected Microsoft Analysis Services 2008.
Figure 1: OLAP Connection Options in the CMC
Figure 2: A Sample OLAP Connection to MSAS
Once the Analysis Services connection was created and the semantic layer was updated to use the new connection, the reporting and analytics processes worked very well and as expected. The biggest disadvantage to this approach is that the security established within the BPC application system is being bypassed. The Analysis Services approach connects directly to the cubes running on the instance of Microsoft SQL Server and does not take into account any security as it was established in BPC.
The Final Product
Once I established a connection directly to Analysis Services, the semantic layer, Web Intelligence reports and Dashboards worked very well. The response time was fast and the data was accurate. It took very little time to build useful reports and dashboards that could be published, scheduled and executed to allow a high level of detail within SAP BusinessObjects 4 while being connected directly to BPC. SAP is aware of the issues related to the native BPC OLAP Connector and is currently researching the issue. If you encounter errors with the BPC OLAP Connector in SAP BusinessObjects 4, consider switching to the MSAS connector until the issue is resolved
Comments