Xcelsius Connectivity with SAP BPC

SAP Businessobjects Planning and Consolidation is a well known budgeting and consolidation tool earlier known as outlooksoft. With the enhance isoft web the reporting has exceedingly moved towards something called userability. With the launch of SAP BPC 7.5 with Microsoft which has additional compatibility with another well known dashboarding tool name Xcelsius, the data representation in form of dashboard has been pretty feasible.

Being said that when it boils down to using BPC and creating custom dashboards on top of the data it is not that simple. It has restrictions and still in development phase. Below are the restrictions as per SAP

1. 2 Dimensions are required

2. Only 2 Dimensions can be used (1 on row, and 1 on column)

3. Dynamic ranges are not supported

4. The number of values being returned from the web service is in a static format

This also means we need to use web services to extract the data out of SAP BPC which in turn means developing web services for each and individual dashboard, results in longer overturn.

Advantage implementing BPC dashboard includes security inheritance. Since the provided BPC Services for Xcelsius Integration (SXI) components live within the BPC environment, all of the standard BPC security is inherited. If a user adds a component that attempts to access data values which the user is not privileged to see, those results are filtered out within BPC before the data is returned to the component. This maintains the same high level of security which is present throughout the BPC system.

Here I would like to discuss a different approach of extracting BPC data and representing it in form of an Xcelsius dashboard. Advantages include simplistic approach of creating reports and dashboards. Drilling down using extended hyper linking to Business objects web intelligence reports.

Disadvantages include BPC security model is not extended to dashboards. Also the data is extracted from a cube hence standalone and can only be compared to other source at report level. Other way is to extract the data to an EDW which leads to duplication of data.

SAP BPC with Microsoft uses Microsoft SQL Server Analytical services. For every shell defined there are multiple cubes generated in analytical services. From the BPC frontend when you login you choose one Appshell. When one uses live reporting it is in turn using MS SQL server reporting service. When you click on Insight within ISoft Web it shows you KPI metrics and dashboards however if you would like to have customized dashboard there is no direct way to design them. SAP Xcelsius has currently connectivity with SAP BPC though as mentioned before it is through web services and hence restrictions. Well the other way is to design a universe on top of the cube itself and create Xcelsius dashboards using QaaWS. Today we are going to investigate more on that.

Creating an OLEDB connection to the cube and create a universe on top of the cube. Create QaaWS or reports using the universe and use that as a data feeder to Xcelsius Dashboards

Start a universe session. Click on new universe. In the parameter create a new connection.

Give a name and then choose Microsoft Analytical services

OLEDB Provider. Though if you are using some other database i.e. MS SQL Server 2008 which is not mentioned in the list you may would like to check SAP for compatible drivers. (Most of the time OLEDB for MS Analysis Service 2005 would work for 2008 but it might be your bad day if it doesn’t)

Keep the authentication to use specified until and unless you have implemented Single Sign on and also you MS Server also connected to the LDAP or AD directory. Also you need rights on the cube you would like to access so ask you database administrator for rights.

What you see in the screenshot above are Appshells in form of databases, cubes namely finance and all, also you would see Dimensions which has been designed for slice and dice.

Now back to the universe , click next to continue and check the cube you would like you dashboard should extract the data from.

Test the connection and click Next to continue

Click finish to end the designing process

What you see in the universe panel is an OLAP cube universe.

Format the universe objects using naming conventions as per your requirement , save it and export it to the repository

Now open a QaaWS session

Use an enterprise account or an alias account in order to login.

You may further use the QaaWS as source for the dashboard

You may also extract data from the underline Database services rather than the analytical services using ETL tools like Data Services. Though this brings us to another topic of data duplication and further leads to maintenance and usability. Another disadvantage is redesigning the metrics and dimensions, including business rule as per the requirement. Well we will discuss about it some other day…

I hope you liked the topic.

Take care

-Phyxius

P.S http://freebusinessobjectsknowledge.blogspot.com/

SAP Business Objects Data Integrator connectivity with SAP BW

Today I would like to take BODS connectivity with SAP BW services. The idea is to extract cube data from SAP BW and put it in a relational OLAP database (SQL Server 2008) and later on the course would take SFDC connectivity and at the end try to combine the data between the two source systems. We would also try to investigate Master Data Management without using much third party tools (Combining SAP BW master data with SFDC) though in next set of blogs

Software used

SAP Data Services XI 3.2

SAP BW

SAP Business Objects Enterprise Edition XI 3.1

SAP Data Services XI 3.2 comes with additional features including compatibility with SQL Server 2008 and also if you have data quality added to your package more of the data cleansing components. In order to start the connectivity you have to first of all configure the SAP BW system.

Prerequisites

1. User with privileges.

2. Data Services transports in SAP system for functions and rights

3. SAP Front end to be installed in the server where data services would be running(P.S IF you have BO Administrators with extended knowledge in Logon.ini, SAPRFCJCO.jar files you may not need to install the frontend , all you have to do is to deploy the requisite jar files in Business Objects WIN32 folder or windows x32 system folder).

It all starts with installing SAP data services XI 3.2 You may first would like to go through the prerequisites and also compatibility especially if you are using a newer version of database i.e. in this case SQL Server 2008.While the installation there is a step to install Tomcat 5.5 or use a already installed on. Though I am sure most of you would choose to click on using the existing Tomcat if you are doing an upgrade I would still insist to use the latest Tomcat which is with Data Services as this installation would include some jar files required for SQL Server 2008 driver which else to be downloaded from the Microsoft site and most of the time it would not work (Personal experience)

After installing or configuring the SAP data services the next step is to configure the BW system. In order to do that as I mentioned you need to the transports in BW system. You may find these transports in [Install Folder]\ BusinessObjects Data Services\Admin\R3_Functions\transport\ folder

The instructions are also added to the same folder.

After running the transports in SAP system, the next step is to create a user (system user) and Role in order to load and extract from/to BW system.

Create a service account user name BODI_CONN (You can give any name)

Create a role name DS_ROLE

In Authorization add the following

Add this role to the user BODI_CONN

Now Login to Data Services for further configuration

Add a new datastore

Choose Datastore as SAP BW Source. Add the username and password, click on advance

In the SAP menu add the following

ABAP Execution Option - Generate and Execute

Client Number - SAP BW client number

System Number - SAP BW system number

Data Transfer Method - Shared Directory

Now go back to the SAP BW system. Create a new folder. Name it “bodi”. Create a sub folder and name it “ds_development”. Share the folder “bodi” to the SAP DS system admin user. Log in back to the SAP Data Service machine and created a new Map Network Drive to the folder \\[SAP BW system]\bodi and name it “Z”. Create another new folder in Data Service Machine and “DS_SAPBW” and create a sub folder name it “ABAP_PROGRAM”.

Now come back to the Datastore configuration

Working Directory in SAP Server - \\[SAP System]\bodi\ds_development

Application Path to the shared directory - z:\ds_development

Generated ABAP Directory - [Root Folder]\DS_SAPBW\ABAP_PROGRAM.

Hit save and close

Now double click on the datastore and it would show you all the Nodes

Hope this would help you with your DS connectivity.

Next part we would talk about SFDC connectivity. Also we would talk about extracting SAP BPC data directly and connecting to BO universe. This way we could work on the dimensions and metrics created in SAP BPC and also it would allow us to create easy adhoc reports and even better Dashboards using Xcelsius.

Till then take care

-Arpan Baruah