Useful ways to format Bex results in Webi

Useful ways to format Bex results in Webi

Via Content in SCN

Running webi reports off of Bex queries can present interesting results due to the nature of using a relational tool to consume OLAP results. Here are some useful formulas/tips to help present Bex results in a cleaner manner.

Remove Values using SUBSTR():

A SUBSTR() formula in Webi is used to strip out unnecessary details from a particular field. A classic example is when dealing with compounded infoobjects. For example when there are reports created for financial and accou nting information a report user is interested in seeing the G/L Account number. However G/L Account Number is compounded with the company code that is configured in the backend. The account number is shows up in the following format CODE/number.

Lets say the G/L Account Number is COMP/13550563 but the user is only interested in seeing 13550563. We can use the substr formula.

=Substr([FIELD];6;8)

Substitute [FIELD] with your object. This formula says we will begin our field at the 6th character by ignoring t he first 5 which is COMP/ and the 8 in the formula says G/L account numbers are always 8 characters long and we need to display all 8. It is recommended you look at the actual Infoobject data length to determine the length of the object.

Remove/Replace Values using REPLACE():

We could have removed the company code from the above example using a REPLACE() formula as well if we are sure that our company code will always be the same. If the G/L account number shows up as COMP/13550563 we can write the replace formula as follows:

=Replace([FIELD];" COMP/";"")

This formula now strips out the unwanted COMP/ value from our G/L account number. This formula is also useful to remove additional details from timestamps and any details from an address field. This can also be used to replace existing data with data that might make more semantic sense to the user. For example changing a meaningless 'X' flag value to either YES or NO.

Create Custom Number Formats:

Different clients have different needs on how they view numbers and dollars on their reports. Custom number formats can easily be created in Webi. When yo u go into format for a number you can define your 'custom' format. Here is a sample for a standard accounting format where thousands are separated by a comma, null values are treated as zeroes and negative values are treated in RED and contained in parenthesis. All numbers follow a 2 decimal notation.

POSITIVE = 0,.00
NEGATIVE = (0,.00)[RED]
EQUAL TO ZERO = 0.00
UNDEFINED = 0.00

The comma allows us to seprate the thousands indicators. To apply to integers remove the decimal.

Use system variables to provide additional meta-data

There are a variety of formulas that are available that call the system specific fields. For example you may want to display the last date the report was executed. You can achieve it using  LastExecutionDate(). CurrentUser() gives you the username of the person accessing the report currently. These become more powerful when you use it along with CurrentDate() and CurrentTime()

Calculate Days between two dates:

It takes a significant amount of work to calculate the number of days between two date ranges in Bex but Webi makes it very easy for us to acco mplish the same. Suppose we want to find the number of days between our Transaction Date and our Posting date simply create a new variable of type Detail and use the DaysBetween() formula to calculate the number of days between the range.

=DaysBetween([Activity_Date];[Posting_Date])

Counting Members:

Webi also makes it very easy to create different counters. Suppose we want to compare number of business partners and the number of contract accounts it is very easy to accomplish by using the Count() formula. Create a variable of type measure and use the formulas as such:

=Count([Business Partner])

You can count different dimensions for a good comparative analysis.

Nest Concatenation() to present free text:

Webi contains a Concatenation() formula to join two strings however it is limited to only joining two strings at a time. This formula becomes really useful if you can nest them inside each other. For example lets say we want to display a free text from CRM on the Webi report but the free text is split into 4 fields due to BW's limit of 60 characters per infoobject of type CHAR. The free text is split into FIELD1, FIELD2, FIELD3, and FIELD4. In order to proper nest the concatenations remember this quick formula. If you wish to join 'N' number of fields then you need to use a total of 'N-1' Concatenation() formulas. In this case beause we want to join 4 fields we will need to use the Concatenation() 3 times.

=Concatenation(Concatenation(FIELD1;FIELD2);(Concatenation("FIELD3";"FIELD4")))

Authorization in BI 7 - Part 1

Authorization in BI 7 - Part 1

Via Content in SCN

This blog will give the information related to Authorization concept available in SAP BW. Also, steps are given to maintain report related authorization.

Why is Authorization required in SAP BW?

• To avoid the unwanted access to various objects as well as data in SAP BW system.
• To assign restricted access of data to report users.

Authorization in SAP BW 3.x 

• BW 3.x required Infoprovider Check to activate or deactivate authorization on the given reports.

What's new in SAP BI  7 for Authorization?

• Analysis authorization
• New standard authorization objects have been added.
  < li>http://help.sap.com/saphelp_nw04s/helpdata/en/be/076f3b6c980c3be10000000a11402f/frameset.htm
  

Prerequisites for authorization

• Decide for which all reports an authorization is required.
• Make list of infoobjects which will act as authorization specific infoobjects in reports.
• Check whether all the required infoobjects are Authorization Relevant or not, if not marked then activate the flag.
• 
• Now check the Authorization Mode in SPRO
  • For SAP BW 3.x - Obsolete concept with RSR authorization objects.
  • For SAP BI 7 or above - Current procedure with analysis authorizations.
  • 
  • SPRO path
    • SAP Customizing Implementation Guide
      • SAP NetWeaver
        • Business Intelligence
          • Settings for Reporting and Analysis
            • General Settings for Reporting and Analysis
              • Analysis Authorizations: Select Concept
• If you want to use any navigational attribute in authorization then it has to be authorization relevant in master data infoobject as shown in following screen.
• 

Steps to create authorization object for SAP BI 7

• Go to t-code RSECADMIN
• Select Maintenance button 

• Maintain Authorization : Initial Screen will appear.
• Enter the technical name of authorization object to be created.
• Click on Create Authorization (F5)
• 
• Maintain Authorizations : Create screen will appear with message in status bar 'Special Characteristics Missing'.
• 
• Why these special char s are required, see what SAP has to say about it
• 
• Fill up short, medium & long text fields.
• Click on the button 'Insert Special Charact.' as shown below.
• Following objects with default selection will get added.
• Further, get the list of all the 'Infoproviders' you want to add in this authorization object.
• Each infoprovider has set of 'Authorization Relevant Infoobjects', all of them have to be added.
• Check one by one infoprovider for the 'Authorization Relevant Infoobjects' as shown below.
• 
• New window will pop-up for Infocube Authorizations.
• You can specify required infoprovider's name directly or can use F4 help to get the name of infoprovider from the list.
• As per the reporting requirement user either
  • Insert Aggregation Authorizations: For the Totals in report, aggregation authorization ':' is used.
  • Insert Full Authorizations: It will assign '*'.
• Once you specify the name of infoprovider and hit enter, list of all authorization relevant infoobjects, from the given cube/DSO will appear.
• 
• Select all the infoobjects from the list using Select All (F7) and hit enter.
• All the selected objects will get added as shown below.
• 
• Repeat the procedure for all the infoproviders.
• As shown in the above screen shot, all infoobjects have value (*) in the intervals field. If you want to change value for any of the infoobjects, use following steps.
  • Use Details button to go to the maintenance screen OR
  • Double click the infoobject to reach the maintenance screen.
• In the maintenance screen, 2 options are given.
  • Value Authorizations: You can specify single, multiple single or range of values.
  • Hie rarchy Authorizations: Hierarchy node values can be assigned.
• 
• Value Authrozations.
  • 1st column - Including/Excluding : Specify either I or E to include of exclude the assigned value.
  • 2nd column - Operator : Specify value from the given list as per your requirement.
• 
  • 3rd & 4th column - Technical Character : Here you have to mention values which are required for the authorizati on.
  • Value (*) can be mentioned to assign Full Authorization.
  • Use Add/ Delete Rows option to add more single values.
  • Whenever single, multiple single or range values are maintained add ( : ) as 1 more row to avoid the summary calculation related errors at runtime. 

• Once you are done by assigning all the required values, click on Save button and exit.
• If you make changes in the existing authorization object, on saving it pops up following message
• 
• Select Yes and new changes with old definition will be saved.

• Hierarchy Based Authorization
  • To assign hierarchy node go to Hierarchy Authorizations in details of particular infoobject.
  • Click on Create button to assign hierarchy node.
  • Following window will pop up where you can select the required hierarchy and particular node.
  • 
  • Click on Select Hierarchy button to open the hierarchy variant screen.
  • New window will show the available variants of hierarchy.
  • 
  • Double click on the required variant and Hierarchy variant name will get populated.
  • Now, detail node needs to be selected. Click on Select Node option.
  • 
  • Select the required node from the list given
  • Once the values for Hierarchy & Nodes are selected, you have to set Type of Authorization.
  • For Type of Authorization following 4 options are given
    • 0 - Only the selected nodes
    • 1 - Subtree below nodes
    • 2 - Subtree below nodes to Level ( Incl. )

3 - Complete hierarchy

4 - Subtree below nodes to ( and Incl. ) Level ( Relative )

Now hit enter and main screen will appear where you can see Hierarchy Node value with type of authorization maintained.

When you are maintaining hierarchy node value then in the Value Authorizations only ( : ) value needs to be maintained to avoid the error at runtime.

Save the authorization object.

How to manage Error Free Transports

How to manage Error Free Transports

Via Content in SCN

Introduction

                  This blog is going to explain you about "Managing Transports by using common sense". I am not going to talk about standard Steps to be carried out.

Common Mistakes :

1. We tend to collect multiple(too many) objects in a single TR
2. We collect our objects in other's transport requests via separate tasks without our/their knowledge
3. We collect different types of objects in a single TR. Eg: Transformations and DTPs together
4. We overlook dependent objects while collecting our objects
5. We do not know whether RSLOGSYSMAP table has got right entries in Target systems or not.
6. We don't bother about dependent objects are active in Target Systems or not

How to use our common sense while managing transports

Scenario : In our projects, we use some standard infoobjects like 0plant, 0Division, 0material etc., Please observe all these standard infoobjects belongs to their respective standard Infoareas and Application Components.

How to handle above scenario?

We need not to collect their respective Infoareas and Application Components every time. Because these standard Infoareas and Application Comps will be already available in target systems.

How to transport DSOs/Infocubes first time to the target systems?

As we might have already moved our infoobjects, we just need to collect only DSOs/Infocubes and move to target systems. We need not to collect any underlying infoobjects it is pointing  to, like below. The infoobjects which has Expand icon can be collected with "Do not Transport Any below". This will reduce the burden on the TR. Note : It's better to use "Only Necessary Objects" and Collect Automatically".

What to do when DSOs/Infocubes becomes inactive target systems?

Just collect the active version of DSOs/Infocubes and move to target systems. You need not to collect anything below.

How to transport Transformations and DTPs first time to the target systems?

We all are aware that Transformations are between Source and Target. That's exactly is the reason to collect Source and Target along with Transformation like below. We need not to collect all infoobjects of our Cubes and DSOs, because those objects have already been moved. Same explanation applies to DTPs also.

How to transport Transformations and DTPs after the first time to the target systems?

We just need to collect the active version of Transformation and DTP. It's not required to collect Source and Target as they are already actively available in Target systems. This procedure can avoid running RDG_TRFN_ACTIVATE program directly in Target systems. You can run this program if you want to activate Transformations/DTPs in mass.

Tips to consider while managing Transports

We tend to collect multiple(too many) objects in a single TR

We should not collect too many objects under one TR. Eg: 1) 30+ Queries 2) 10+ Process Chains etc., We will not be able to have a control on all objects. If you collect few and make them successful in Target systems, it gives you a great idea on our transports.

We collect our objects in other's transport requests via separate tasks without our/their knowledge

This is not really a big mistake. But there is a dependency here. Unless all the tasks of different users gets finished, you cannot release the main Request No. This will delay your work. To avoid all this mess, you should create your own TR and collect all your objects under it and release it.

Sometimes the other developer would have developed some objects and saved in his task. When you take up that work and start working on them, all your changes will get merged with the existing request and task. You will be confused finally, what is your changes and their's ? That's why you have to collect freshly just before you are ready to transport all your objects. Never trans port the older requests, as you don't know exactly what are all changes have been done.

We collect different types of objects in a single TR. Eg: Transformations and DTPs together

This is a biggest mistake we generally do. When we start our developments in our BW Dev system, we might create one TR in the beginning and start saving all our work in that. Finally we land up in multiple errors while importing in target systems. Eg : (1 Infopackage, 1 DTP, 2 Transformations, 3 DSOs ) All in one TR.

My tip here is, you can still work on single TR, but finally unlock all your objects with the help of my other blog http://scn.sap.com /docs/DOC-31394. Collect again freshly object wise from RSA1-->Object types in individual requests. Be clear on object wise here. Eg: Cubes- 1 TR, DSOs- 1 TR, Chains- 1 TR, Transformations- 1 TR, DTPs- 1 TR

We overlook dependent objects while collecting our objects

We should always cross check in target systems (before transporting our objects), whether dependent objects are active in Target systems or not. Eg : Datasource Replicas, Transformations, DSO/Cubes etc..

We do not know whether RSLOGSYSMAP table has got right entries in Target systems or not

Logical system conversions are must to be maintained in target systems , while transporting Transfer rules, Infosources, D atasource Replicas,Transformations etc. They basically check "Source System Reference" in target systems.

You can maintain them with my other blog http://scn.sap.com/community/data-warehousing/netweaver-bw/blog/2013/09/29/clients-and-logical-systems

How to collect Data flow Migration work

This is a very sensitive task while collecting. All your sequence of steps have to be collected on the spot. Treat these TRs with special care. Don't allow any other changes to get saved under it other than migration activities. It is not as easy as unlo cking from the old request and collect again in new Request from RSA1-->Object Types.

How to collect a BEx Query

You have to select the tick boxes like below and cross check whether all the query elements have been collected or not. You need to select the Infoproviders, as they will be already available in Target systems.

How to set your system Transport Request settings

Goto RSA1-->Transports Tab-->Follow below image

If you switch on standard, then every activity will ask you for transport request through a pop up instantly.

If youSwitch -off standard, then all your activities will get saved in to local objects. Finally you will have to collect from RSA1-->Transprt tab-->Object types

Object Changeability

You can observe this functionality in above image. This can be used to provide a privilege for us to edit the objects directly in Target Systems. Eg: Queries, Web Templates, DTPs, Infopackages etc. This setting has to be done in Target systems

Conclusion

Transporting is an easy task if you organize things properly in a systematic manner. Maintain your own excel sheet with all your Transports. This will help you any time if you want to cross check.

Extraction of Material Classification Data from R/3 to BW

Extraction of Material Classification Data from R/3 to BW

Via Content in SCN

This document would help to extract Material Classification data for the newly added classification characteristics from R/3 to BW system.

First I would explain what is classification, class, objects and characteristics.

Classification:

R/3 supports many ways for those customising it to store additional data fields. For example, many tables support customer defined

additions, but there is also the Cross Application Classification System. The Classification System allows R/3 to hold many varied

additional fields against many different sorts of R/3 "object", for example it might allow you to store a colour against a material. Here

is some terminology:

 
Object - a predefined business entity such as vendor, material, equipment or customer.

Class -  a grouping of Characteristics. To view class definition use t-code: CL03
Characteristics - a property of the object, this is the new field being added. For example a new characteristic might be the color of the material.
Characteristic Value - the actual value of the characteristic, for example a material can be colored blue.

Steps to be followed in SAP ECC side

1)  Goto TCode SBIW->Settings for Application-Specific DataSources->Cross-Application Components->Maintain DataSources for Classification Data

    

2)      In this below screen select DS: 0MATERIAL_ATTR, Class Type: 001(Material Class)
           
          

           

In the above screen shot we can see for each data source generated its corresponding Classification Data Source name starting with 1CL_0MAT***(for MATERIAL_ATTR) gets activated.

3)  Then double click on characteristics on left side. Here we need to maintain the characteristics that we require in BW from the material master.
        Manually enter the characteristics in the screen popping out
  
         

In the above screen shot "N" value indicates datasource has been created
              

4) Then Click on Generate Data Source         

     R/3 side Configuration is now complete, test the extractor using RSA3.

Steps in BW side

Replicate the datasource in BW using t-code: RSDS.