SAP BW Knowledge Sharing: September 2009

Thursday, September 24, 2009

Configuring EP for connecting to BW

The following steps are needed to configure EP for connecting to BW

Creating System Object
Creating Folder, Role, Workset, Page and iview
Attaching iview to page, page to workset, workset to Role
Assigning Role to the User

In the Enterprise portal you have to create the system object for connecting to BW system.
Log on to Enterprise Portal using Fully qualified domain name using system admin id.

Creating System Object

o Go to system Administration --> system configuration --> system landscape.

o Expand the folder portal_content. Right click on the portal_content folder and choose new --> system. Select the template which matches with your BW system and choose save.

o Right click the system object created and choose edit. In the drop down box select connector properties. Enter the BW details here like application host, server port, etc. and choose save.In the system object you have to give the BW system details in the connector property.

o Now select WAS property from the drop down list. Give details of your WAS server and save. The WAS server details should follow the fully qualified domain name

o Now choose user mapping from the drop down list and select the logon method you need and select user,admin from the drop down box and save.

o Again right click the system object and choose system alias. Give your logical system name and set it as default and save. Also create another system alais SAP_BW and set it as default and save

o Right click the system object and choose connection test. Select the check box you need to test and test the connection

2. Creating Folder, Roles, Workset, Pages and iviews

o First create role, workset, page and iviews.

o Go to Content Administration --> Portal content

o Expand the folder Portal_content

o Right click the portal_content folder and choose create folder. Enter the required details and save

o Right click the created folder and choose create Role. Enter the required details and save

o Right click the created folder and choose create Workset. Enter the required details and save

o Right click the created folder and choose create Page. Choose the template required. Select the no of column template and enter the required details and save

o Right click the created folder and choose create iview. Choose the template BW Report ivew or SAP Transaction iview. Enter the required details like query string or BW Transaction code and select the BW system you need to connect to from the drop down list and save. Now iview is created. Right click the iview and choose preview. The iview is executed and the BW query or transaction is displayed.

3. Attaching iview to page, page to workset, workset to Role

o Now right click the page you created and choose edit. With this screen in edit mode, right click the iview you have created and choose add iview to page and save and close the window. Now iview is attached to page.

o Now right click the role and choose edit. With the role in edit mode, now right click the workset you have created and choose add workset to role. Now workset is attached to role

o Now in the right hand side window in the context area, highlight the workset, Then right click the page you have created and choose add page to workset.

o Now you have assigned iview to page, page to workset, workset to role.

o Now right click the role you have created and choose edit properties. In the property "Entry Points" select Yes. Only then the role will be visible in the portal

4. Now assign the role to the user

o For this go to user administration --> role.

o Enter the user and from the drop down box select user and search. Then choose the edit link and next screen is opened. enter the role and search. Select the add role button. The role gets added then click the save button. Now Role is attached to the user

o Now login to portal (using Fully qualified domain name) using the portal user id and the roles will be displayed. Iview is displayed fetching the data from BW system

Tuesday, September 22, 2009

ABAP program to find previous request in cube and delete

There will be cases when we cannot use the SAP built-in settings to delete previous request. The logic to determine previous request may be so customized, a requirement.
In such cases you can write an ABAP program which calculates previous request basing our own defined logic.

Following are the tables used:

RSICCONT ---(list of all requests in any particular cube)
RSSELDONE ----- ( has got Reqnumb, source , target , selection infoobject , selections ..etc)

Following is one example code. Logic is to select request based on selection conditions used in the infopackage:

REPORT ZGET_LAST_REQ_SHORT_RUN.
PARAMETERS: TAR_CUBE(9) TYPE C,
SOUR_ODS(9) TYPE C.
DATA : IT_REQ LIKE TABLE OF RSICCONT.
data : it_REQ_WA type RSICCONT.
DATA : IT_CALMONTH_LOW TYPE /BI0/OICALMONTH.
DATA : IT_CALMONTH_HIGH TYPE /BI0/OICALMONTH.
data : diff type N.
DATA : IT_REQ_CUBE LIKE TABLE OF RSSELDONE.
data : it_REQ_CUBE_WA type RSSELDONE.
data : sour_tar(22) type C.
TABLES : ZBW_PARAM.
CLEAR It_REQ.
CLEAR it_REQ_WA.
CLEAR it_REQ_CUBE.
CLEAR DIFF.
concatenate SOUR_ODS '_TO_' TAR_CUBE INTO SOUR_TAR.
SELECT * FROM RSICCONT INTO TABLE IT_REQ WHERE ICUBE = TAR_CUBE.
LOOP AT IT_REQ INTO IT_REQ_WA.
SELECT * FROM RSSELDONE INTO TABLE IT_REQ_CUBE WHERE RNR = IT_REQ_WA-RNR AND SOURCE = SOUR_ODS AND FIELDNAME = 'CALMONTH'.
ENDLOOP.
LOOP AT IT_req_cube into it_req_cube_wa.
IT_CALMONTH_LOW = IT_REQ_CUBE_wa-LOW.
IT_CALMONTH_HIGH = IT_REQ_CUBE_wa-HIGH.
diff = IT_CALMONTH_HIGH - IT_CALMONTH_LOW.
if diff <= 3.
ZBW_PARAM-ZKEY1 = 'INV_SHORT_REQ'.
ZBW_PARAM-ZKEY2 = SOUR_TAR.
ZBW_PARAM-ZDATA = IT_REQ_CUBE_WA-RNR.
MODIFY ZBW_PARAM.
ENDIF.
endloop.

Once after reqnumb is found out, you can pass
cubename, reqnumb as inputs to FM : RSSM_DELETE_REQUEST and get the job done.

Saturday, September 19, 2009

How to remove duplicate records in the cube?

Scenario: The cube consists of duplicate records of one particular request

The cube is compressed & rolled up into aggregates .

EX : Cube 0IC_C01 is being updated from two ODS i.e 0IC_O01,0IC_O04

This ODS are getting data from data sources 2lis_02_scl,2lis_02_itm respectively

Each ODS has got three data targets. Updating is done by delta method

Sol’s: Goto –Monitor screen of ODS –data target –request- application tool bar –select the icon request reverse posting –monitor the request – until green.

In order to nullify the duplicate records present in the cube compress the request & rollup into aggregates.

The request will be posted if the selection criteria in info package processing tab if we have selected the PSA option i.e first three options

Here the case is that the data is being directly updated into the data target

The selection in processing tab is data target only

So, we don’t have the option request reverse posting.

If we have uploaded the data by using the option PSA the process was as follows

Goto :ODS info package monitor screen

Select the request which you want to post .

And push the button in the app. Toolbar request reverse posting

The request will be posted with the Reverse Image & the records will be nullified .

Thus the incorrect records will be deleted.

If the request is directly uploaded into data target with the selection in the processing tab Data target only

We don’t find the icon in application toolbar (Request Reverse Posting)

What to do?

This is one of the procedures which can be followed:

From the ODS the change log table should be used in creating the data source in BW with the selection Request.

Appl comp DM

Once the data source is created by using the T.Code RSO2—Data base view

Using change log table name./ bic /b followed by 10digit no.

Use my self source system (BW )

The data source is mapped to the data target using PSA.

In doing so the fields that are present in the ODS should be present in the data source

Except like Request ID , data packet ID ,part no ,record.

Where it should meet the original update rules to that of ODS

In doing so the communication structure will match to the ODS

In up date rules we write an Source code to inverse the data in the fields while updating into the Cube.

Source Code:

UPDATE ROUTINE FOR THE INVERSION OF FIELDS:

PROGRAM UPDATE_ROUTINE.

*$*$ begin of global - insert your declaration only below this line *-*

* TABLES: ...

* DATA: ...

*$*$ end of global - insert your declaration only before this line *-*

FORM compute_data_field

TABLES MONITOR STRUCTURE RSMONITOR "user defined monitoring

USING COMM_STRUCTURE LIKE /BIC/CSRIC_O04_CL_DATA

RECORD_NO LIKE SY-TABIX

RECORD_ALL LIKE SY-TABIX

SOURCE_SYSTEM LIKE RSUPDSIMULH-LOGSYS

CHANGING RESULT LIKE /BIC/VRIC_C01T-/BIC/RRECVSVAL

RETURNCODE LIKE SY-SUBRC

ABORT LIKE SY-SUBRC. "set ABORT <> 0 to cancel update

*$*$ begin of routine - insert your code only below this line *-*

* fill the internal table "MONITOR", to make monitor entries

IF ( COMM_STRUCTURE-PROCESSKEY = '050' "Sonstiger Zugang / Umbewertung

OR COMM_STRUCTURE-PROCESSKEY = '051' "Preisänderung + / Umbewertung

OR COMM_STRUCTURE-PROCESSKEY = '052' ) "Rechnungsprüf. + / Umbewertung

AND COMM_STRUCTURE-BWAPPLNM EQ 'MM'.

RESULT = ( -1 ) * ABS( COMM_STRUCTURE-CPPVLC ).

* if the returncode is not equal zero, the result will not be updated

RETURNCODE = 0.

ELSE.

RESULT = ABS( COMM_STRUCTURE-CPPVLC ).

* if the returncode is not equal zero, the result will not be updated

RETURNCODE = 0.

ENDIF.

* if abort is not equal zero, the update process will be canceled

ABORT = 0.

*$*$ end of routine - insert your code only before this line *-*

ENDFORM.

The data will be posted with Reverse Image into the cube.thus the duplicate records will be nullified.

Friday, September 11, 2009

SAP BI 7.0 Authorization - Part 1: InfoObjects level authorization

New SAP BI 7.0 Authorization concept (analysis authorization) change a lot in accessing, analyzing and displaying BI information. The approach allow to restrict data access on Key figure, Characteristic, Characteristic value, Hierarchy node, and InfoCube levels. It enables more flexible data access management.

Analysis authorization is active by default in SAP BI 7.0 systems and I think it is worth to spend some time to look closer at the new concepts and the features. In part one of this two-article series, I will show you how you can restrict access to SAP BW reports on InfoObjects level.

Initial settings

At the beginning activate business content objects (TCode RSORBCT) related to authorizations:

InfoObjects 0TCA*
InfoCubes 0TCA*

and set the following InfoObjects as Authorization-Relevant:

0TCAACTVT (activity such as Display)
0TCAIPROV (InfoProvider authorization)
0TCAVALID (validity period of authorization)
0TCAKYFNM (if you want to restrict access to key figure)

Characteristics authorization

Use TCode RSA1, go to Modelling -> InfoObjects. Display properties of the characteristic to which you want to restrict access and set it as Authorization-Relevant.

Characteristics values authorization

To authorize characteristics values you need to create new authorization object through TCode RSECADMIN. The following pictures show how allow users to access to specific sale organization (e.g., New York, San Francisco, Dallas).
1. Create new authorization object (e.g., Z_SORG_B).

2. Choose characteristic and press Details button.

3. Select sales organization (e.g., 1612 - New York, 1614 - San Francisco, 1615 - Dallas). Available operators: EQ - single value, BT - range of values, CP - pattern ending with (*) (e.g., abc*). You have also option to Include (I) or Exclude (E) values.

Attributes authorization

To authorize navigational attributes, set them as Authorization-Relevant.

Hierarchies authorization

To grant authorization on hierarchy level edit or create authorization object (e.g., Z_SORG_B), add hierarchy and nodes, and choose type of authorization.

Key figure authorization

To grant authorization to particular key figure, add special object 0TCAKYFNM to authorization object (e.g., Z_SORG_B), and choose the key figure to be authorized.

Summary

InfoObject level authorization gives you a great flexibility, but keep in mind system limitations. Avoid setting too many characteristics as authorization relevant (more than 10 in a query). All marked characteristics are checked for existing authorization if they are in a query or in an InfoProvider that is being used. Too much authorization objects may slow query execution. Exception are characteristics with all (*) authorization. If you want to check which InfoObjects are authorization relevant in your BI system, use TCode RSECADMIN -> Authorization Maintenance and display 0BI_ALL authorization. More about 0BI_ALL you will find in the article on creating and assigning authorization.

Remember that authorization do not work as a filters do. It means that the user who is executing the query, where characteristics are authorization relevant, must have sufficient authorization to the characteristics ("all-or-nothing" rule). Exceptions are hierarchies in the drill down and variables which are dependent on authorization.

In part two,- how to create and assign SAP BI authorization .

Query Monitor - helpful tool for testing BI queries

Query Monitor is one of the BEx monitor tools that enable to display and verify query correctness, test variants and additional query properties. To use it start RSRT transaction (you do not need BW front end).

I usually use Execute and Execute + Debug options. Execute + Debug option enable for example to check aggregates, set authorization breakpoints, run query with BI Accelerator, display SQL statement for the query and show BW statistics.

You may choose different query display format. BEX Analyser display format is by far the fastest and the simplest, List gives you additional query options and using HTML format you receive portal-like view.

You can also use the tool to check users' authorization to a query. To execute a query as another user start RSUDO transaction and choose a user and a query.

Transporting SAP BI queries

In this post I present basic steps to transport SAP BI queries from development to quality server. The steps are performed in source and target system, so you need authorizations to release and import objects.

Source system
Start with transaction RSOR (Transport Connection), insert initial and target source system names using Conversion button (2) and choose grouping type (3). Select queries you would like to transport to target system and press Execute and than Transport objects (truck) button.

Release the change request to transport using SE10 transaction. Press Display, choose tasks and requests you would like to release and press the button with single truck (or F9). When both task and request have been released successfully, start transport in target system.

Target system
To import queries to quality system start STMS transaction > Import Overview (F5) > Display Import Queue. On the Import Queue screen select the request and press Import (truck with a small loading). Choose target client's number and press enter. The queries will be written to the target system.

Standard BEx Transport Request
When the request, you have released, was set as Standard BEx Transport Request, you need created a new standard request. If there is no standard request, nobody is able to process queries or workbooks on the system. When you try to do so, you will receive the error: The query could not be saved due to a problem in transport. BEx transport request is not available or not suitable.

To create a new request you need to press BEx and than Assign / Delete button, add the request and save the choice.

Now all new objects and modifications will be written to the chosen BEx transport request. For more information on the standard transport request see this note:194051.

Additional resource:
Transporting: role and objects.
Authorizations for change and transport: S_TRANSPRT and S_CTS_ADMI.

SAP BW Process Chains - automation and job scheduling

Would you like to automate repetitive BW tasks like loading transaction data? Create Process Chains!

Let's begin with basic definitions:

Process chain (PC) is a sequence of processes linked together.
Each Process have a type (BW activity, e.g., activate ODS) and a variant (contains parameters).
Start process describes when the process will start (immediately, scheduled job, metaprocess, API).
Connector is linking processes; you can choose 1 of 3 options of starting next process: when previous finishes with success (green arrow), failure (red) or always start the next process (black).
Variant is a set of parameters passed to the process, such as the name of the InfoPackage to use for loading, the name of the InfoObject to perform a change run for.

Selected icon bar buttons:

Planning view enables to create and modify processes.
Checking view checks consistency of a process chain selected in plan view.
Log view shows log of the execution of a process chain selected in plan view.

Selected left panel buttons:

Process Chains displays process chains.
Process Types displays process types, enable to drag and drop process into main panel.

Loading Hierarchy using Process Chain

Now, we will describe how to load a hierarchy into an InfoObject. The process will start every day at 1PM. The process chain will have the following processes:

Start > Load Hierarchy > Save Hierarchy > Attr. Change Run

1. Start transaction RSPC and choose Process Chain > Create. Enter a name of the chain and description. You will be asked to insert a name of start process - choose New, enter a variant name and description.

2. Select Direct Scheduling option and click Change Selection. Click Date/Time, enter schedule start date and hour (current date, 01:00:00). Click Period values > Daily > Save > Save > Save > Back.

3. Click Process Types button on the left panel. Expand Load process and click twice Execute InfoPackage. Choose the InfoPackage for the Hierarchy you would like to load and confirm the choice. To connect the Start process with the load process: right click on the Start, choose process > Connect with > Load Data > created process.

4. Add processes to save hierarchy and attributes change run (commits changes in the InfoObject).

5. Save the process. Click Checking View button. If the chain is OK, activate and schedule the chain by clicking Activate and then Schedule buttons.

Additional information

To work with PC, you need authorization for authorization object S_RS_PC.
To monitor selected processes create a list of PCs using TCode RSPCM. This tool will enable you to see statuses of selected PCs and provide a link to process chains' log.
To have a PC that can be scheduled and maintained only in specified client - press Process Chain > Attributes > Editing Client, and enter the name of the selected client.
To transport client dependant PC with complete starting options, enter required background user data in the target system using TCode RSTPRFC.
If you transport PC with scheduling option: immediately the PC will start immediately after the transport.
To "stop" scheduled PC click Execution > Remove from Schedule.
To see overall PCs status - start BWCCMS tool.
PC can send e-mail message when process failed. To create e-mail alert right click on the process and choose Maintain Message option.
To see technical names and additional information about processes: click View > Detail View.

Examples of BW PC
Example of processes sequence when deleting overlapping requests form a InfoCube:
Start > Del. indexes > Load InfoCube > Delete Overlapping request > Gen. Indexes

Loading hierarchy, attributes and texts into InfoObjects:
Start > Load Attr. > Load Hierarchy > Save Hierarchy > Attr. Change Run > Load Texts

Additional resources:
Creating process chains (SAP Help)
Decision Makers in PC (SDN Article)
Create process chain - step by step tutorial (SDN Blog)
Presentation on Process Chains (SAP Library)

Remote ABAP Debugging and more

You have possibility to set User Breakpoint (formerly External Breakpoints) in ABAP backend to get into debugging mode while you invoking this code outside (e.g. via RFC or HTTP requests) of ABAP backend. Such a breakpoint is set on application server on which are you currently logged in. You will not get into debugger if your request is send to other application server in which you haven’t set break points. To obey this behavior SAP is providing transaction SRDEBUG where you can activate your User Breakpoint in all applications servers.

There is one more useful transaction called RSBREAKPOINTS via which you can manage your breakpoints. Basically this TA deals with tables: ABDBG_BPS (ABAP Debugger: Breakpoints), ABDBG_INFO (ABAP Debugger: Information on Breakpoints) and ICFATTRIB (Description of ICF Attributes (Trace/Debugging)).

The biggest tables in SAP BW

In one of my April 2008’s posts I noticed a standard SAP R/3 / ECC application tables which may potentially grow rapidly and then they may cause problems. I mentioned in there some tables from other SAP solutions like CRM, SCM, MDM, APO, PI, etc. In today’s post I would like to focus on BW big tables.

• BW statistics data: RSDDSTATAGGR, RSDDSTATAGGRDEF, RSDDSTATCOND, RSDDSTATDELE, RSDDSTATDM, RSDDSTATEVDATA, RSDDSTATHEADER, RSDDSTATINFO, RSDDSTATLOGGING

• PSA error logs: RSERRORHEAD, RSERRORLOG

• BW staging engine tables: RSMONMESS, RSSELDONE

• BW workbook tables: RSRWBSTORE

• Temporary BW tables: /BI0/0*

• RSBMNODES – “Hierarchical Log: Nodes”; If e.g. it takes a long time to set a DTP request to 'green' after it has been processed it is caused by large volume of data in this table. Since BI system in production a quite long time you got a lot of logs in it then you have a huge amount of entries in this table.

• RSBERRORLOG (Logs for Incorrect Records) – stores error handling logs due to following and other reasons:

• Warnings that are created during master data uploads for duplicate records

• Single record error messages in customer-specific transformation routines

Table is accumulated a numerous error messages records for a DTP requests. In most cases this is due to many errors while BW is processing data and InfoPackage (IP) or Data Transfer Package (DTP) is setup in way that every data duplicity needs to be recorded.

So here it is, yes really it has 151 milions of records. I took me a half an hour to get this pop up window.

Since the fact tables of the InfoCubes are usually one of the biggest tables in a SAP NetWeaver BW you can observe their volume as well. Table names have following naming conventions:

/BIC/F* - for fact table before cube compression
/BIC/E* - for fact table after cube compression

SAP NetWeaver BI Quick Links

BI Quick Links: BI Service market place ; BI ABAP and NW SPs ; BI Best Practices
Learning: BI Developer Area ; BI E-Learning
Discuss: BI Forums ; BI Blogs ; BI Articles
How-To's: BI 7.0 ; BI 2004
SAP Help: BI NW 7.0 (2004s) ; BW NW 2004
BI Content: BI 7.0 ; BW 2004

What is the Consulting Outlook in SAP BI/BO/BW?

I recently received a question that asked me about the future of SAP BI/BW, as well as which SAP modules or products might work well with BI going forward.

Despite the economic circumstances of the present, I feel very confident in the future of BI for a number of reasons. First, we know that the current demand for BI on SAP job sites is strong. A recent search on “SAP BI” on Dice.com yielded more than 700 job openings. That’s not even counting variations like SAP BW or even Business Objects related positions.

The second reason BI looks good is the level of emphasis SAP is placing on the product. John Schwarz, CEO of Business Objects, gave the keynote address at TechEd in Las Vegas this year. SAP’s Business Objects acquisition, the largest acquisition in SAP’s history, is yet another indication of how strongly SAP feels about this market going forward. The third reason I like BI? Because it fits in well with industry trends concerning what ERP customers are looking for in their products. ERP customers want better value out of their transactional ERP systems. They want to turn all that data into actionable, intelligent information that can be used for smart decision making at every level of the enterprise.

So, we can feel optimistic about SAP BI. I like BI both as a skills focus and as a great “skills tool” for a consultant with a broader SAP focus. However, BI will come with its challenges. Obviously, the BO acquisition is going to create a number of changes in the ultimate SAP BI product line. For the consultants, this will mean mastering new applications and not just resting on their “Business Warehouse” laurels. Going through all the possible BO skills combinations with SAP would take a book length article, and much of it is speculation at this point anyhow. But one thing we can be confident in is that BO’s user-friendly reporting solutions will be very attractive to SAP customers as they are tied ever more closely into NetWeaver.

That gives you some idea of what to look for in the market. Now, about your question about which modules really work well with BI. I’m going to assume you are referring to what we think of as the classic “functional modules” of the SAP suite. In theory, all of these modules go well with BI. In practice, however, I would suggest a focus on the areas in SAP that have the strongest needs for reporting data. SAP Financials goes well with BI, in particular the BEx Analyzer, because many financials users like this kind of Excel format. This is traditionally where SAP has done the best on the reporting side. Two other areas where reporting seems very important: SAP HR/HCM and SAP CRM. Technically a Business Suite component, I’m throwing it into this answer as well for consideration. We also see some interesting BW-APO integration roles from time to time.

As BO’s tools are integrated more fully into SAP, I expect we will see SAP having more success spreading its reporting tools farther into the enterprise. For now, financial reporting remains a stronghold, with a few other areas also looking promising. The trick for the consultant is striking the right supply and demand balance. One way of doing this would be to have some good experience in BI along with Financials, and then also bring in an emerging area like EPM or GRC (Enterprise Performance Management and Governance, Risk and Compliance). This way, you are building skills that less consultants have while also keeping your core strong.

by Jonathan Reed

Monday, September 7, 2009

usefull BW/BI Links

Head Lines

SAP BW

Search This Blog

Subscribe via email

Labels