Connecting to SAP BW

[maddysbi]

Hi,

    I'm working on a project where in I need to extract the data from SAP BW which is in the form of cubes.Though,I see that there is SAS Interface to R3,I'm not able to trace as in how to connect to SAP BW.As we have got instructions from the client not to touch the R3 or BW environment internally,I would like to know the possible alternatives of establishing connectivity with SAP BW from SAS.

(FYI,I found information about SAP Open Hub Service and APD which needs to get inside BW environment for set up and hence doesn't serve my purpose completely)

Thanks,

Maddy

[jakarman]

Well the SAP R3 - BW is an application using a database with content.  It could be in your contracts that all users getting content out of that must be licensed as of touching R3 or BW. Exaggerated it could be a very lot of them and many of them just seeing an excel or pdf. You need the data out of SAP using that content with an other tool.

You could use the SAP/BW interface using the metadata structure of SAP.
SAS(R) 9.3 Intelligence Platform: Data Administration Guide, Second Edition

http://support.sas.com/documentation/installcenter/en/ikr3cg/64225/PDF/default/config.pdf

You could use a direct Databases-connection to the one SAP is using.
The interpretation of the DB structure will become your work but may be your questions is simple. 
When you know the DBMS SAP is using then the according SAS/ACCESS connection could be made.

[maddysbi]

Jaap,

        SAS Access 4.4 Interface to R/3 was the same document I was referring to,before posting this question.But was bounced by the condition of not touching R/3 or BW. Now that you are saying that there should license to some of our users to touch them,I will enquire about it.

One clarification that is required in this case is that even If I want to establish a connection to BW,should I be going with the same interface?

Because as per my understanding,SAP ECC or R/3 is OLTP and SAP BW which is OLAP are connected by a RFC connection.Since the data is already being made available in BW in the form of cubes by the client,I do not want to touch R/3.

Please guide me accordingly on how to establish a connection to SAP BW alone using SAS Access 4.4 Interface to R/3 or let me know if there.is any dedicated connector to BW from SAS?

Also,how is Data Surveyor different from this interface and what is its use?

Thanks,

Maddy

[CameronLawson]

Hi,

We have a very large SAP footprint at work and I was involved in setting up SAS connectivity at our site.  Basically you have 4 to 5 options.  I have used each one for both testing and production scenarios.

1. SAS/ACCESS to R/3.  It works for both ECC and SAP BW
2. SAS ODBO Connection to InfoCubes using ODBC / Enterprise Guide Cube Interface
3. Open Hub / Infospoke either to database or flat file
4. Classic Badi / Remote RFC via Proc RFC
5. SOAP Calls via Proc SOAP

SAS/ACCESS to R/3

By far the simplest in terms of usability.  It works like all other access engines in that it will translate SAS code to its source system language equivalent or federate where it can't.  Whether it processes on the SAP or SAS side depends on your query / datastep and procs used.  Generally most SAS/STAT procedures force federation.  You need to install some transports on the BW side which implements a custom package called "SAS/COMMON", the SAP GUI to expose the rfc.dll file to sas, create some RFC connections and also a background user account and your generally ok.  Sometimes you will need to alter your .hosts file also if you use an F5 or something similar to a clustered gateway.  Data Surveyor is awesome for the most part.  It is an add-in to SMC or DI.  It uses the Access engine at its core and it will read BW metadata also.  Using the access engine alone gives the physical db table definition.  This is a problem as most SAP systems have upwards of 70k tables.  Data Surveyor relies on having at least three libnames defined;

1. for SAP BW metadata (Base /v9 etc.)

2. for extracted data (Base/v9 etc.).

3. the SAP libname

Metadata is a separate extract job you run when needed.  It provides the C Struct view in the application.  It will for example combine your BW M and P tables as required and Text and SID tables etc.The tool will translate SAP InfoCubes to a classic Kimball style star schema with;

• A central fact table
• Dimensions which combine SID,Master Data and InfoObjects.
• A view which is a select * of every dimension and fact.

Deltas are supported from BW change logs and you can extract DSO's or InfoCubes.  Masterdata will also export as part of a cube job.  The big gotchas not clear in documentation are that masterdata will be extracted multiple times when extracting from multiple InfoCubes and will not be extracted for DSO's.  This means you have to manually alter cube jobs in DI if you only want to extract once.  E.G: 

• Cube ZBP_C01 uses master data object 0BPARTNER and cube ZBP_C02 also uses it.  The job in DI will create two jobs for extracting data from 0BPARTNER
• DSO ZBP_D01 uses 0COMP_CODE 01.  In BW via SE16 or 11 you will see 01 being ACME.inc.  In SAS it will simply be 01.  I have found that is very confusing for end users as they validate data through the SAP application which provides lookups to the master data.

In DI it will create a separate job for every InfoObject, Dimension, Fact, View and merge performed.  The manual intervention becomes time consuming and confusing if you are extracting from a large number of cubes (I did 80 separate cubes initially and it was hell on earth).

ODBO Connection

Dragons reside here.  Tread carefully.  The engine supporting it does very weird typecasting and performance is horrible on large cubes.  The main advantage is that in E-Guide you can use the awesome OLAP connection functionality.  The only problem is that you will probably force your pc to page memory because it is trying to typecast SAP datatypes on the fly.

Open Hub

Two options here;

• Flat file and then read the file into SAS
• Open hub / bulk load a database which SAS can interface via libname.

We have started using this at work for some use cases.  You need open hub licensing of course.  The nice thing here is that it allows very fine grained control on the SAP side.  It does force replication of data though.  You will force your BASIS staff to have more input with this option as DTP's have to be manually set for Delta / full loads etc.  The basic process is your BW dev team create the open hub in RSA1 and then SAS picks up the output.  Very simple and clean.

Bapi / Remote RFC

Numerous Badi's exist which allow remote communication.  These can be accessed in SAS either by Proc RFC (which is part of the SAS/ACCESS to R/3 product) or x / systask command.  You will end up doing  A LOT of scripting to get this working.  The standard Bapis require multiple calls to get cube or dso data.  Generally you have separate calls for authentication, discovery, selection and then translation.  The two BAPI's to look at are MDDataProviderBW and MDDataSetBW

SOAP Calls

If you activate ICF services in BW you can call OLAP data via Web Service.  This then allows SAS to call the service via Proc SOAP.  help.sas has heaps of info on the service defintions and wsdl addresses.  This is basically the same as using Bapi's as it is a service wrapper over the top of the same procedures.  Methods are normally all the same.

Apologies for the long answer.  It is pretty complex but getting BASIS on board and being able to talk SAP speak will help you greatly.

Cam

[maddysbi]

Hi Cam,

           Though long you have put everything very clear.I could get more clarity only when I saw the entire thing running before me only few days ago and its actually tough dealing with that large of Infoobjects,Dimensions,Facts,Star Schema view for a single cube extraction.Moreover,we have to run load job once the tables are registered.

Please suggest me how is this extraction when compared to the DSO.For example,as quoted by you,consider there is ZBP_C01 cube and also ZBP_O01 DSO.Ideally speaking,is it true to say I will optimize my time on extracting it from DSO though the information is available in granular level when compare to be Cube which is at summarized level and has lot of factors embedded in it.

Also,just wanted to confirm whether is it possible to extract the data from PSA using this SAS SAP BW connector?

And,does it allow me to extract only single infoobject like say 0Material in SAP BW,though the same info object might be reflect in different DSOs or Cubes at the backend.When I tried to do that,it said that NO DSO or CUBE selected.Please reply as it is very useful to me.

[maddysbi]

Hi All,

        Can someone help me with this data surveyor tool for SAP BW. I'm not able to identify how the change data capture mechanism works in this area.I came to know from SAP techies that they have request ids generated daily for delta load as a part of their cdc process and is in the form of default package(infoobject).Now when I'm extracting the cube data,I see the Change data capture option on the screen during the process,but couldn't understand how does it work at the back end,neither I see this request id in the fact table generated in the SAS end.Request someone to please help explaining the process in detail as it is urgent.Any reference with some screenshots will be of great help.

Thanks,

Maddy

[CameronLawson]

Hi,

It uses the BW change logs.  If you view the source code in DI you will see what tables and fields are being used in BW.

Good luck!

Cameron

[CameronLawson]

Hi,

PSA is not available directly through the Data Surveyor tool but you can using the Access Engine.

You need to determine an information management strategy for managing InfoObjects and whether or not you choose to load from DSO or cube.  DSO loading does not include master data texts however.

Cameron

[jakarman]

Maddy I am looking for the possible not being cooperative part  of your SAP contacts. SAS is for them often something as a threat.
The license issue of SAP has been thread, it should be shot by your legal affairs as it your business content not that of SAP.

The SAS R3 interfaces notes:

The add-on is available for SAP BASIS 7.0, SAP ECC 6.0, and SAP BW 7.0. Installing the add-on is an alternative to installing the ABAP objects by using the transport files as described in Chapter 3 — Installing SAS/ACCESS Interface to R/3 Software." You should either install the add-on or you should apply the SAS-provided transport files, but not both

Chaptr 3:

On the SAS Server:

1. Install the Unicode RFC libraries from SAP.

2. Set the environment variables.

On the SAP System:

1. Import the SAP transports into your SAP system.

2. Maintain RFC destinations.

3. Maintain table /SAS/DESTS for destination groups.

4. Activate BAdI implementation for SAP BW/BI authorization checks.

You are needing to get clear what that means and the impact. When it is nothing more as getting authorized for your data is rather trivial.

The used DMBS OLTP OLAP MDDB . it doesn't matter really as it all about cubes. 
Cubes are often stored in a RDBMS when that support the common interfaces (OLE/DB mdx) you can access cubes that way.

As it a RDBMS you could even use SQL to retrieve the data. That is more difficult but must be possible.

The bottom line and that happens often, is file transfers of CSV files.
A lot of work to build that but everyone is happy in his own ivory tower/silo.    

[jakarman]

data surveyor SAS Data Surveyors is the kind of interfaces to other products but watch the version numbers.  
What's New in SAS(R) 9.2 is for - What's New in SAS Data Surveyor 4.3 for SAP It looks like different SAS versions are using other names

At this point is getting beyond my scope/knowledge.

[Casian]

Hello,

I am trying to connect cu SAP AB2 server using SAS Data Integration but I get an error.

Short description of the issue :

I have a connection to SAP AP2 server created in SMC and also a SAS Base library that points to a local path on the DEV server.

I am able to register tables on that library but I am not able to register Infoobjects when I follow the below steps :

1. Go to SAS DI.

2. File -> Register Table -> Enterprise Applications -> SAP BW -> Next (see untitled.png attached)

Then I get the error :

%let error_missing_metatables=%nrstr(ERROR: Metadata tables are missing. You may need to reload BW metadata.);
19         %let error_missing_metatable=%nrstr(ERROR: The Metadata table &parm is missing. You may need to reload the Metadata.);
20         %let error_invalid_object=%nrstr(ERROR: &parm is not defined as basic InfoCube or ODS in Metadata.);
21         %let error_invalid_parameter=%nrstr(ERROR: Invalid parameter passed to macro &parm.);
22         %let warn_no_top_infoarea=%nrstr(WARNING: Could not get top level InfoArea, tree may be incomplete.);
23         %let error_missing_table=%nrstr(ERROR: Table &parm does not exist.);
24         %let error_ods_notintables = %nrstr(ERROR: ODS table name not found in TABLES dataset.);
25         %let error_fact_notintables = %nrstr(ERROR: Fact table name not found in TABLES dataset.);
26         %let error_nodetails=%nrstr(ERROR: No detailed Metadata available for InfoObject &parm.);
27         %let error_incomplete_metadata=%nrstr(ERROR: Metadata are incomplete for &parm, you may need to reload its BW metadata.);

So basically I cannot browse into the AB2 server to search for whatever infoobject needs to be loaded.
The metadata tables containing all metadata actually of AB2 are not available. (that library contains references to infoobjects etc.).

Can you please help?

Thank you!

Casian

[SteveED · Accepted solution]

Casian,

It looks like whomever set up your SAS Data Surveyor for SAP needs to run the Extract SAP BW Metadata process. This is the last step of install/config of the product -- but it also has to be done regularly (i.e. whenever the BW system (data structures) are changed.

Information can be found here> SAS® Data Surveyor 5.3 for SAP

This process is run from SAS Management Console, typically by the administrator who set up your SAS Data Surveyor for SAP.

As an overview, SAS Data Surveyor for SAP reads the SAP (BW in this case) data structures and stores them so the metadata server knows whats there. You can see errors like this if this process was never run, or if the extracted metadata is stale.

Steve