Christopher Hickman

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

After working with a client on a dashboarding project in SAP BusinessObjects 4, we encountered an error with SAP BusinessObjects Web Intelligence when we were trying to connect to BI Services.  We could neither connect nor could we publish.  After some investigation, it seems that we needed to redeploy dswsbobje.  Here’s a bit of information that we gathered and the resolution that we discovered.

Symptoms:

Connecting to BI services in Webi in launch pad or rich client will throw random XML parser errors such as XML reader error: javax.xml.stream.XMLStreamException: ParseError at [row,col]:[1089,5]Message: XML document structures must restart and end within the same entity.

You cannot see existing web services nor publish new web services.

Opening the URL http://<servername:port>/dswsbobje/services/Session?wsdl goes into an infinite loop and will crash your browser.  Ensure that wsdl is lowercase.

Cause:

From time to time, BI Service can become corrupt and cause the symptoms that are shown above.

Resolution:

Redeploy the dswsbobje application. To redeploy, follow these steps on the BI4 server:

1. Stop Tomcat and the SIA using the CCM
2. Open a command prompt
3. Go to the directory: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\wdeploy
4. Run the command: wdeploy tomcat6 -DAPP=dswsbobje deploy
5. Look for Build Successful at the bottom of the resulting output.  If you do not see Build Successful, rerun the wdeploy command
6. Start Tomcat and the SIA using the CCM

Go to the URL http:// <servername:port>/dswsbobje/services/Session?wsdl.  The browser should not crash.

Try to connect to BI Services in Webi (launch pad or rich client).  The issue should be resolved.

SAP BusinessObjects Explorer provides the ability to interact with data providing insight that goes beyond standard reports.  However, the question of data security must be addressed when we interact with this data.  One of our recent clients had a requirement to provide users the ability to use the SAP BusinessObjects Experience  iOS and desktop app while restricting access on data to which they have access.  We worked on this demo and provided a very compelling experience for our client.  In this blog, I will share with you what we did to fulfill these requirements.

Database Considerations

First of all, we started with a data source.  The data source that we have contains eFashion data migrated from Microsoft Access to Microsoft SQL Server.  Of course, the data provider doesn’t matter.  Secondly, we added a table that contains a row level mapping of data to individual users.  For instance, we had a restriction that requires a user to see two states: California and Colorado.  A second user should be able to see DC, Florida and New York.  A third user should be able to see all states.  A fourth user should have no access.  Here is the view of the table that we created:

Next, we updated the standard eFashion universe to include this new security table and link it together with the state field of the existing Store table.  That’s it for the data source.  We can now link data with individual users on the database level.

SAP BusinessObjects Explorer

Now that the data source is created and the universe is developed to use the database, we can now create our first Information Space on top of the data source.  Create a new Information Space containing fields for the manager name and the state.  This association represents the data to which each user has access.  Finally, save and index the new Information Space.  This is the Reference.

Next, build a second Information Space that contains all of the facets and measures to be used with Explorer and Experience.  Specify the correct properties, objects and scheduling (if needed).  If you have implemented security on the database or universe level, Explorer effectively strips that security away when you index the Information Space.  Data is brought into Explorer from the data source according to the security of the user performing the index.  When users execute the Information Space in Explorer, they see the data that the indexer had access to see.

The key step in enabling security is to use the Reference Information Space in the final Information Space.  Explorer compares the current User Name with the specified Manager field and performs the filtering on demand.  As users log into SAP BusinessObjects or into the iPad, the Manager field is filtered according to the user’s login id.  If a user’s login id does not exist in the Manager field, no data will be returned and the user will see a message stating this fact.

Personalization

To establish a personalized Information Space, click the Personalization tab of the final Information Space.  Click the check mark beside “Personalize Information Space Exploration” then click the down arrow beside the Exploration View selector to the right.  Browse through the available data providers and select the Reference Information Space.  There are two parts on the bottom of the screen: To Filter and Filtered by.  In the To Filter, map the User Name field to the Manager field in the Filtered by section.  We are filtering States in this example, so select State in the Filtered by section on the same line as the State in To Filter.  Your final Personalization screen should appear like this: 

Exploration Views

Now that we have two Information Spaces created, we can now build Exploration Views from the final Information Space.  The personalization happens on the Information Space and is extended to the (Exploration) views that are built on the spaces.   To create an Exploration View, open the Information Space.  Click Create View Set and design your Exploration View.  Click Save and provide a name.  Switch over to the Exploration View Sets section and click your new Exploration View.  You should now see your design personalized for the currently logged in user.  Below are screenshots for four users.  The first three have access to certain states.  The fourth has no access to any data.

The user above has access to California and Colorado.

The user above has access to DC, Florida and New York

The user above has access to all states

This user has access to nothing.

Conclusion

SAP BusinessObjects Explorer provides a unique visualization tool that allows users to interact with data in new and different ways.  Data security and Explorer are not mutually exclusive.  In this article, we discussed the standard capabilities of Explorer and Exploration Views.  Administrators have the ability to apply Personalization to restrict data for users that explore the personalized data sources.  Users who have access to the spaces and views but no access to the data will be restricted.  Users with partial or full access to the data will see their data represented accordingly.  Only users who have access to modify the underlying Information Spaces can control the Personalization settings within Explorer.

I’ve been a dashboard developer for a few years now and the biggest fear that I’ve had is building a complex dashboard and encountering a corruption when I try to reopen it.  It’s happened a few times in my career and it’s devastating.  Recovering a corrupt dashboard is nearly impossible unless you’re into poring over lines and lines of XML contained within the XLF file.  Even then there are no guarantees that you will recover your hours of work.

I have found a solution that works very well and that solution is Dropbox.  Dropbox is a cloud storage solution that provides users the ability to save files within their Windows folder structure as we do every day.  However, Dropbox monitors the directory for the addition or updating of files and sends the changed files up to their secure cloud.  This is where the magic happens.  When an update is found, Dropbox will take a backup of the previous version of the file and will add the change as a new file!  Here’s how it can work for you.

Go to the Dropbox (http://db.tt/OREYpVH) website and download the client.  It’s a very quick and easy installation and the app has a small memory footprint.  During the installation, you can specify the default location (on a Windows machine it’s c:\users\<username>) or you can specify a different folder to serve as the root.  You can also selectively determine what folders will be synced with a server.  Once you finish this setup, you are ready to go!

 Once the installation is complete, you will have a folder in the location that you specified in the installation that will contain your synced files.  The folder itself will display with a small checkmark when it is fully synced to the cloud.  If it is being refreshed, it will show the double arrow refresh icon.

Next, simply go about your dashboard development as usual.  In this example, I have created a small dashboard.  The content of the dashboard is not important as we are only concerned with the XLF file itself.  I save this file in a new folder called Xcelsius 2008 within the Dropbox structure.

Open a Windows Explorer window and navigate to the saved dashboard.  Right click on the dashboard and select Dropbox in the context menu.  Click View Previous Versions.  A browser will open and will take you directly to the location of the file stored in the cloud.  Since we have only saved the dashboard once, you will see an initial “Added” Entry with the size and time that it was changed.

Go back to the dashboard and make a change.  Click Save to save the changes to the folder.  If you watch the folder icons, you’ll notice that Dropbox sees that there is a change and will upload the change to the cloud.  Now, navigate to the file again and right click to view the context menu then click View Previous Versions.  The browser refreshes with the current historical listing.

Make another change on the dashboard and click Save again.  Once Dropbox adds the instance to the cloud, right click the file name again and select View Previous Versions.  The third instance displays in the browser.

To roll back to the previous version, simply click the radio button that appears to the left of the desired instance and click the Restore button.  Open the XLF file inside of Xcelsius to see the previous version.  When you restore an instance, a Restored event is recorded in Dropbox, giving you the ability to restore to ANY of the previous instances.

Dropbox maintains versions of your files for 30 days then they are automatically removed.  Work that I performed a year ago becomes finalized after 30 days and cannot be rolled back.  Dropbox does make available an add-on called Pack Rat in their Pro version that allows you to maintain your revisions without the 30 day restriction.

This method has saved me many times.  One time in particular I was working on a complex dashboard for an international corporation.  I opened the dashboard one morning and began working as I typically do.  I saved the dashboard, closed Xcelsius and took a break.  When I came back and tried to open the dashboard, I received an error message saying that the dashboard could not be opened.  My heart sunk.  I was able to use Dropbox to roll back to a version that I saved 20 minutes prior so I lost very little work.

In addition to Dropbox, I always maintain daily versions of my dashboards as extra insurance.  Before beginning work for the day, I make a copy of the XLF and append vXX to the end just before the XLF extension.  On the dashboard that I just described, I was up to v43.  Dropbox then takes over and provides an incremental revision history from the point of creation until the most recent save.

Give this method a try!  Give this a try with not just XLF files, but any of your files.  I presented this idea at the SAPInsider Xcelsius Bootcamp last year and it seemed to be well received.  I hope you find this useful and a time saver.  I hope you never have to suffer corrupted dashboards!

I am working with a client to migrate two instances of SAP BusinessObjects XI 3.1 SP3 to a new SAP BusinessObjects XI 3.1 SP5 server.  The two source servers have a combined 110 ODBC data sources.  This introduces the obvious question: Do I want to recreate these ODBC sources on the destination server?  The obvious answer to the obvious question is NO!  So the question remains: How can I migrate my existing ODBC sources from one server to another?

WARNING!  These steps require manual manipulation of the Windows Registry.  Take a full backup of the registry prior to completing these steps.  Without a full backup, you could corrupt your installation of windows and will be forced to reinstall the OS and all associated applications.  Also, ensure that the middleware that is being used on your current servers are also installed on the new server(s).  Without this middleware, the ODBC Data Source Administrator will not allow you to edit or delete the imported connections.

Now that the warnings are out of the way, let’s get on with the fun!

To migrate ODBC sources across environments:

1. Log into the source server and open regedit.exe.
2. Navigate to 
   1. 32 Bit OS: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC
   2. 64 Bit OS: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC
3. Open ODBC and highlight ODBC.INI
4. Right click ODBC.INI and select Export
5. Ensure that the Export Range at the bottom is set to Selected Branch
6. Enter the file name “ODBC.REG” and click Save.
7. Copy the ODBC.REG to a directory on the destination server.

 Now go to the destination server and do the following:

1. Log into the destination server and open regedit.exe.
2. Navigate to 
   1. 32 Bit OS: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC
   2. 64 Bit OS: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC
3. Right click the ODBC.INI and select Rename
4. Enter ODBC.ORIGINAL.INI in case you need to roll back to the original settings.
5. In Windows Explorer, navigate to the location of the ODBC.REG and double click the file
6. Accept the warnings to continue
7. Test the data sources to ensure proper migration.

When you open the ODBC data source administrator, all of the data sources and their associated credentials will be present.  After I completed this step, I ran the typical content migration process from one SAP BusinessObjects XI 3.1 environment to another.  I was able to immediately run all of the imported reports without the need to recreate ODBC sources on the new server.

This scenario describes the case where you are migrating 32 bit to 32 bit or 64 bit to 64 bit.  If you would like to migrate from a 32 bit operating system to a 64 bit operating system as is the case with the migration from SAP BusinessObjects XI 3.1 to SAP BusinessObjects 4, edit the .reg file prior to importing the contents into the destination server.  You will need to add “Wow6432Node” to the registry key path.  I accomplished this by searching for all instances of SOFTWARE\ODBC in my 32 bit reg file export and replaced it with SOFTWARE\Wow6432Node\ODBC.