SAP BW

Running SAP BusinessObjects Explorer on top of SAP BW

Running SAP Explorer on top of BW This blog describes the only successfully tested way to run SAP Explorer on top of SAP Netweaver Business Warehouse (SAP BW) info-providers. This is not a straight forward configuration and it requires knowledge of Information Design Tool, Data Federator Administration Tool and SAP Logon application for SAP BW.

The supported connection by SAP Explorer to explore data on top of SAP BW info-providers is through a Relational Universe using a JCo (Java Connector). But this connection does not work with just configuring the right parameters for the JCo connector in IDT, it requires some additional adjustments to be done on the SAP BusinessObjects - SAP BW environment.

Here is a quick brief on some topics to be considered (how-to not explained in this blog post):

  1. It is a recommendation from SAP to split (size) the Adaptive Processing Server in up to 6 different APSs and isolate this way some of the services (SAP KBA 1694041)
  2. Make sure Data Federation Service in included as a service in your current APS
  3. Make sure the user you are using for creating the relational connection is propertly granted in SAP BW (SAP KBA 1604227)
  4. Make sure with your IT team that there are no firewall activities or blocked ports that can make the connection fail

Step 1: Creating a Relational Connection in IDT:

Open IDT, create a new Relational Connection - SAP - SAP NetWeaver BW - SAP Java Connector (SAP JCo) and configure all the connection details required. As soon as you finish the configuration and test the connection, you will get the error message #1:

Error message
Error message

There is another connectivity issue (error #2) you might face, after completing the steps, in this blog article you will learn how fix both of them:

com.businessobjects.datafederator.sourcedefiner.connection.ConnectionExce ption: com.sap.connectivity.cs.java.drivers.DBDError: [Data Federator Driver] [Server] [Connector 'SAP Testing'] No RFC destination was defined for the server 'SIA.AdaptiveProcessingServer2'

Step 2: Creating a RFC destination in SAP Logon App:

By using the transaction SE37 and using the function module "RSDRI_DF_CONFIGURE" you will create the RFC destination.

Function builder
Function builder

This is the crucial part of this configuration, the RFC destination must be created specifically in the same way SAP recommends in this link:

http://wiki.scn.sap.com/wiki/display/BOBJ/BW+With+RFC+JCO+Connections+In+Information+Design+Tool

Take a look at the section "Steps for Manual Configuration" and complete your RFC destination as follows:

RFC destination
RFC destination
  • Make sure you set an empty value for the I_ONLY_CHECK parameter
  • Make sure you set an empty value for the I_REMOVE_CONFIGURATION
  • Complete I_RFC_DESTINATION as requested. To give an example if your SAP BusinessObjects server is called MYBOSRV the value for I_RFC_DESTINATION must be DF_JCO_MYBOSRV_1

After doing this, error #2 will be gone.

Step 3: Configuring Data Federation Administration tool properly

In Data Federation Administration Tool, under "Connector Configuration" you will find the Relational Connection created in IDT. Create a configuration for the connection mentioned accordingly with the content in the SAP Logon transaction SM59 - TCP/IP Connections (where you will find the DF_JCO_MYBOSRV_1 connection created in Step2).

You must make sure that these three parameters have the correct values as per your DF_JCO_MYBOSRV_1 in SM59:

  • gatewayServiceName: same value as in SM59
  • gatewayHostname: same value as in SM59
  • programIDMapping: concatenate your SIA name + Adaptive Processing Server name = DF_JCO_MYBOSRV_1
Uncheck "Use default values
Uncheck "Use default values

Step 4: Modifying "reginfo.dat" file in BW system:

By using SAP Logon transaction SMGW you will have access to the "reginfo.dat" file in which we will perform a quick change. Navigate to Goto - Expert Functions - External Security - Display (Reg. Info).

Display (Reg. Info)
Display (Reg. Info)

As per SAP note 1806694, you will add one line in this file. The easiest way to perform that for me was to search in the SAP BW server for the file "reginfo.dat" and edit it manually. After this, go back to SMGW transaction in SAP Logon and go to Goto - Expert Functions - External Security - Reread (global). You should see the line added in SAP Logon when you display the file again. The line to be added is:       P TP=* HOST=<server as per ProgramIDMapping> CANCEL=* ACCESS=*

Where <server as per ProgramIDMapping> means your BO server where SAP Explorer is installed.

Summary

If you have completed all the steps in this blog and you try to test your relational connection in IDT, it will work!

So now, you are able to test the relational connection in IDT as well as able to create a Data Foundation and Business Layer on top of it. After publishing the Relational Universe into your repository, you will find the universe avaiable in SAP BusinessObjects Explorer and you will be able to create an Information Space and index it.

SAP BW on SAP HANA: when and why?

In this article we will analyse the advantages of the SAP Business Warehouse (SAP BW) on SAP HANA solution in comparison with a Data Warehouse built directly on SAP HANA. We will also benchmark the performance of a SAP BW vs. SAP BW on HANA in a big data scenario. Most of us are aware of the last trends in BI/EPM market and where it is heading. SAP's mid and long term strategy shows that they are committed to convert SAP HANA in the core of many of their solutions including SAP ERP and SAP BW. SAP HANA can be used either as a database for other Custom Applications or as an Enterprise Corporate Datawarehouse (EDW) to take advantage of its big data and in-memory capabilities.

What options do we have when implementing an Enterprise Data Warehouse in SAP HANA?

When creating an Enterprise Data Warehouse (EDW) in SAP HANA, SAP customers have 2 options

  1. EDW in SAP HANA: Implementing a Data Model inside SAP using a combination of custom data models implemented by BI consultants, models created using SAP RapidMarts and SAP Data Services as a modelling tool.
  2. EDW in SAP BW on HANA: Implementing the SAP Business Warehouse on HANA by taking advantage of its complete data warehousing workbench and its rich business content, which provides Data Models and ETL for almost all modules in a SAP ERP system.

EDW in SAP HANA or EDW in SAP BW on SAP HANA?

Many SAP customers are now facing a dilemma. They want to go for SAP HANA, however, they don’t know yet whether to go for option 1 or 2.  My suggestion is that the following customers go for SAP BW on HANA:

  • Those who already have a SAP BW system. They can quickly migrate that system into a BW on HANA (and Clariba can help on that) and take advantage of in-memory and big data capabilities of SAP HANA by protecting previous investments on that BI system.
  • Companies with SAP ERP that currently don't have an EDW. SAP BW is a very good solution to create a data warehouse on top of other SAP Systems as integration between them is tighter and it comes with all pre-developed data models.

What are the weak points of SAP BW on SAP HANA?

Companies with no other SAP systems in the landscape can theoretically go for SAP BW to implement their custom data models (no business content), however, that has some disadvantages:

  • ETL Implementation effort is not so quick as compared to ETL implementation with SAP Data Services
  • Data Models implemented within SAP BW will be a bit slower than data models implemented directly into a SAP HANA database. That’s because we are adding a layer with the NetWeaver  application server on top of the SAP HANA Database.
Data Warehouse SAP HANA diagram
Data Warehouse SAP HANA diagram

What are the advantages of SAP BW on SAP HANA?

SAP BW on SAP HANA has some advantages in front of a plain data warehouse in SAP HANA:

  • SAP BW is a data warehouse framework. You have several capabilities you may expect from a data warehouse. Plain SAP HANA is not so mature and complete.
  • Plain SAP HANA lacks Master Data Management capabilities
  • Data warehousing development workbench
  • It comes with and engine that automatically manages dimensions, Master Data ID’s, hierarchies. In comparison, implementing a Data Warehouse directly on SAP HANA seems to be handcraft and highly depends on the BI Consultant skills
  • Better capability to automatically manage data loads and solve issues/ ie: if a load fails, you can easily delete the load an repeat it
  • BW is a more business focused data warehouse  than a data warehouse directly in SAP HANA, which is more technical. You can carry out large SAP BW projects without scripting SQL queries. It’s not the same with a large project involving a plain SAP HANA data warehouse, in which you will need to script SQL

For SAP BW customers, what are the improvements of migrating to SAP BW on SAP HANA?

SAP BW customers need to assess whether the investment on a new SAP HANA appliance makes sense.  Cleary the investment is valid for customers with SAP BW systems containing huge amount of data and suffering poor query reporting performance. Also to consider that with SAP BW on SAP HANA less database space is used as fewer indexes are needed and aggregates are no longer necessary.

These customers must consider that the SAP HANA database in which the SAP BW will be running can be used for other enterprise applications, so your investment on the SAP HANA technology is not just for the SAP BW, but also for other applications.

What if we compare the SAP BW vs. SAP BW on SAP HANA?

We have a pool of sample data with millions of records coming from a telco scenario: prepaid recharges carried out by millions of customer during a frame of time. We want to compare the same scenario deployed in normal SAP BW vs. a SAP BW on HANA.

  1. Extraction of 2.5 million records from source system. That source system is another SAP HANA database. Yes, we should see the scores in a non SAP HANA database, but in another article.
  2. Load of these 2.5 million records into an Infocube. Transformation rules with 4 ABAP routines are used.
  3. Execution of consolidated (without displaying millions of records) BEx queries on that InfoCube
  4. Execution of a Web Intelligence report from that query.
SAP HANA Database
SAP HANA Database

Architecture

The scenario has been implemented on a SAP NetWeaver 7.3 SP9. SAP BusinessObjects BI Platform 4.1 used for the Web Intelligence reports.

Both servers have same RAM memory, hard disk, network speed, etc.

The following picture shows the architecture of both scenarios

  1. SAP NetWeaver BW on a MS SQL Server Database installed in the same box (no network latency)
  2. SAP NetWeaver BW on SAP HANA. The SAP HANA box is in a separate box and there is network latency.
Architecture SAP BW on SAP HANA
Architecture SAP BW on SAP HANA

Server 1 System Status

Server 1 System Status
Server 1 System Status

Server 2 System Status

Server 2 System Status
Server 2 System Status

Benchmark scores

Server 1

SAP BW 7.3

Server 2

SAP BW on HANA 7.3

vs.

Extraction of 2.5 million records  time from source system

34 minutes 36 seconds

26 Minutes 6 seconds

1.32x faster

Load of these 2.5 million records into a Infocube

1h and 3 minutes

3 minutes 27 seconds

18.5x faster

Execution of consolidated BEx queries on that InfoCube

27.8 seconds

0.414 seconds

67.1x faster

Execution of a Web Intelligence report from that query.

138 seconds

11 seconds

12.5x faster

Conclusions

In this article we tried to compare the performance of SAP BW on SAP HANA vs. a non-SAP HANA BW. We used the same data model and amount of data in both cases just to quantify the difference in performance of both scenarios with real numbers. These are my conclusions

  • SAP BW on SAP HANA in much faster than BW on non HANA database. That improvement can range from 10 to 67 times, depending on what we execute.
  • SAP BW on ASP HANA performance on extraction depends on the speed of the source system. It can be a bit faster as records, once read, are inserted faster in the SAP HANA Database.
  • Loading records in Infoproviders of SAP BW on SAP HANA can be 10x or 20x times faster compared to a non-SAP HANA BW. That is highly variable depending on the database we compare with (in our case, a MS SQL Server)
  • Query and Analysis on SAP BW on SAP HANA is much faster. However, it depends on the type of query. If we just want to calculate figures, it will be faster than if we try to retrieve large number of records.
  • Web Inteligence reports on top of SAP BW on SAP HANA are faster, however the performance improvement is not so great when compared to directly executing the BEx query. This is due to the fact that SAP BusinessObjects needs to move data from sAP BW server to the SAP BusinessObjects server and load it into the Web Intelligence report. Will SAP release a SAP BusinesObjects system on SAP HANA? Let's wait and see

SAP Data Warehousing Solutions: an October 2011 Review

Regardless of software or hardware vendors, the Data Warehousing market is an area of growth, expected to increase about 10.1% in CAGR (compound annual growth rate) by 2015 according to the latest study carried out by IDC. SAP, thanks to the acquisition of Sybase, it is seen as a leader in the Data Warehouse by Gartner & Forrester. This article is focused on the solutions provided by SAP for organizations looking for enterprise-oriented data warehouses or more agile high-performance data foundations and to catch a glimpse of their future road map.

Enterprise Data Warehouse

  • SAP NetWeaver Business Warehouse (BW): is nowadays a very stable product, with a large installed base and in constant growth (more than 12000 customers, more than 15000 productive systems), and what more it has not been affected by the economic downturn in 2009. If we look at its future evolution, it is intended to be running on HANA in 2012 which will be used as the underlying In-Memory database Platform (BW 7.3x SP5), this means to be based on an  already built-in Business Warehouse Accelerator.

High Performance Analytic Data Foundation

  • SAP HANA: is a flexible, data source agnostic, in-memory appliance that analyzes high volumes of transactional data in real-time. It includes tools for data modeling, data and lifecycle management, security and operations. It combines SAP software components which are optimized with the hardware provided by partners. The benefits are the following: make smarter business decisions supported by an increased visibility  of very large volumes of business information, react to business events more quickly through real-time analysis and reporting of operational data, a new application foundation for a new category of applications, streamline IT landscapes and finally reducetotal cost of ownership (TCO). If we look further  ahead in time, there will be a transition going from having SAP Business Objects BI 4.0 running on SAP HANA 1.0 to having the whole SAP Business Suite running on SAP HANA 2.0 in 2013.

 

  • Sybase IQ: is a market leading, high-performance, columnar analytics server and data warehouse specifically designed for high speed data analytics, enabling high speed performance of complex queries against large datasets, with the advantage of being a low cost  maintenance when compared to row-based systems (by reducing the need for aggregates and indexes). It is a mature and proven solution with about 1900 customers and more than 3300 unique deployments. SAP will continue to support and invest on this acceleration technology, especially for non-SAP applications and data (including the possibility of placing SAP BusinessObjects BI on top).

 

  • SAP BusinessObjects RapidMarts are preconfigured jumpstart data marts that are designed to accelerate BI. And they all come pre-packaged by subject areas and sub-areas specific to SAP modules (Finance, Manufacturing, Operations, HR, etc.) as well as for non-SAP applications (JD Edwards, PeopleSoft, Lawson, Oracle EBS, etc.). The key elements included are the following:
  1. ETL Mappings: Source-to-target mappings and data transformation for relevant source tables (initial and incremental data movements).
  2. Data Marts: set of target RDBMS objects and schemas based on best practices for dimensional data modeling (Oracle, DB2, SQL Server, Teradata).
  3. BI Content: preconfigured universes based on best practices and samples of reports displaying  the wealth of data available.

If you need any further information on the solutions presented here, don’t hesitate to post a reply or contact Clariba.

SAP BusinessObjects: Through the Eyes of Critics and Clients

In our day-to-day life we are exposed to many purchasing decisions. Going to the supermarket to get some food or buying widgets in an internet shop is no longer a quick and easy task. The diversification of vendors forces us to go through a conscious or unconscious decision making process. It is always interesting to study the reasoning that leads to an action, especially for marketing people, and it is a social phenomenon in constant evolution, ranging from the traditional word of mouth to the most modern surfing of internet forums.

The same happens when a company has the opportunity to make a BI investment and has to choose a vendor that fulfills their particular needs. There are some specialized consulting companies that carry out complex market studies, but the feedback received from clients or contacts is just as influential to the decision making process.

This blog article is focused on the latest studies from Gartner and Forrester regarding SAP BusinessObjects.

The Forrester Wave: Enterprise Business Intelligence Platforms, released during the last quarter of 2010, considers SAP BusinessObjects to be leading the market together with IBM Cognos, Oracle and Information Builders, followed by SAS, Microsoft and MicroStrategy close behind. The 145 criteria used correspond to vendor surveys, product demos and customer reference surveys.  The end result is a graph that situates BI vendors in relation to their current offering and strategy.

SAP BusinessObjects is recognized as having very good BI tools for each job:

  • Crystal Reports as top report writer embedded in thousands of applications;
  • Explorer as a combination of the power of OLAP and the simplicity of search, exploration and analysis;
  • SAP BusinessObjects Dashboards is very popular amongst executives that can visualize the generated flash in disconnected mode and no SAP software installed;
  • BEx being still the most widely used and popular query and analysis tool for SAP BW users;
  • Business Warehouse Accelerator with the speed of in-memory database to provide a powerful DBMS optimized for BI;
  • SAP is also leading in self-service BI software as a service (SaaS) offerings.

According to Forrester, the downside of having all these tools is a poor product integration and a lack of object reuse from product to product, which is supposed to be improved with the new release BI 4.0 in Q1 2011.

Gartner’s Magic Quadrant for Business Intelligence Platforms, released in January 2011, considers the same leader vendors as Forrester and also adds QlikTech thanks to its recent continued growth. It uses standard criteria divided into two categories: completeness of vision and ability to execute. According to the study, a good strength is to have the largest share of the BI platform market (combination of SAP NetWeaver BW and SAP BusinessObjects) with an installed base over 46,000 customers and one of the largest channel and services ecosystems. Reporting and ad-hoc querying capabilities were rated high by customers, whereas other SAP NetWeaver BW tools were below the market average. However, in regards to OLAP, BEx Analyzer seems to be more widely used than Voyager. The complements to the BI platform are also seen as positive: collaboration and decision support (StreamWork), Text Analysis, search-based data exploration with its Explorer product, and the enterprise information management products with data integration, lineage & impact analysis, and data quality. The vision for an in-memory appliance, HANA, promises to solve many of the perennial performance issues of large complex BI deployments in general (given its SQL and MDX access for third-party BI tools) and SAP BW in particular. As also reported by Forrester, SAP has been one of the first of the leading BI vendors to introduce a SaaS offering, BusinessObjects OnDemand BI. In addition, SAP has also made investments in mobile BI with its Sybase acquisition and the SAP BusinessObjects Explorer iPhone/iPad application.

In the cautions part of the report, Gartner mentions some concerns that customers have about the roadmap after product changes to support optimizations with the SAP Business Applications and NetWeaver BW products, and also a perceived higher cost of ownership. Migration, implementation and integration choices are reported to be confusing by some customers. Finally, ratings for support, software quality and sales experience are unfortunately low compared to other vendors in the survey. This is currently being address by SAP; they are giving more and more importance to Channel Partners like Clariba, which can provide high quality uninterrupted support, much more aligned to the customer needs.

If you have any questions or comments about the Gartner or Forrester summaries mentioned in this article, or would like more information about Clariba’s SAP BusinessObjects services, please leave a comment below or contact us at info@clariba.com.

Connecting SAP BW and BusinessObjects Data Integrator for data extraction

In follow up to my blog article on July 7, I would like to share some insight for connecting SAP BW and SAP BusinessObjects Data Integrator for the purposes of data extraction.   

 The problem that I encountered was that I could not connect my BODS to SAP BW. The connection was correctly created into the Management console of Data Integrator but the start up was always failing. 

After what seemed like hundreds of tests and commands from the cmd, I found the solution: the services file contained in the same path as the host file (windows/system32/drivers/etc) requires a small change:

  1. You need to add the following string SAPGWxx  33xx/tcp where xx is the system number of your SAP connection.
  2. Then here I also configured the sapjco3.jar that is stored in tomcat (you will find it with an easy search in the folder) in the Tomcat CLASSPATH as per the previous topic posted on July 7.

To start the service I used a command from the cmd in the location: Drive:Business ObjectsBusiness Objects Data Servicesbin: RfcSvr -aRFC_ProgramID -g/H/ip or name of the SAP Application Server name/S/33xx -xsapgwxx   RfcSvr is the .exe file that starts the DI processes. If you want to know more details regarding this command, the best way is to do a quick search in Google.

After following the steps above, everything should work fine. At this point, you can use BW cubes as your data source in Data Integrator.

If you have any questions or feedback to add to this quick solution, please feel free to leave a comment below.

Connection between SAP BW and Universe Designer / BusinessObjects InfoView

In this blog post I will explain some tips that I learned while integrating SAP BusinessObjects Universe Designer/Infoview with SAP BW.

For the most part, the steps should be simple and quite standard (unless you face some unexpected issues).   First of all you need to install the platform and the integration kit for SAP. At the end of this process you will see that you can create universes on top of BW cubes or BW queries. You can easily publish the universe and retrieve your data in a report.

Now in theory, after configuring the user in the CMC (BO CMC --> Authentications --> SAP) a user should be able to log in to SAP BusinessObjects InfoView using his/her SAP credentials…

But in reality BusinessObjects will fail while importing the roles of the SAP user. Why? Because you will need a java connector, which doesn't come with the “out of the box” integration kit.

All you need to do is download the files from SAP (or from the bottom of this blog post), and make them available for your system.   Here a step by step guide:

  1. Create the folders in your tomcat55 parent folder called Sharedlib
  2. Copy there the sapjco.jar and the .dll files
  3. Copy the .dll files into Windows/system32
  4. Go to Tomcat configuration and add the complete path of the file sapjco.jar to the CLASSPATH string - restart tomcat

Notes: Do not confuse the sapjco.jar with other versions in the folder. Download the 32bit version even if your machine is a 64bit.   Now try to import your user's role in BO CMC --> Authentications --> SAP again and you will see that everything will work as expected.

If you have any questions or feedback about this solution, please let me know by leaving a comment below.

> Download this Shared file (contains sapjco.jar and the .dll files)