1 2 3 6 Previous Next

SAP Business Explorer (SAP BEx)

85 Posts

Objective:

 

Every organization use multiple reports like Sales, Shipping, Billing and Profit & Loss to make good business decision. In this all areas we might have developed and used huge BEx reports, in all reports we might restrict (include/exclude) characteristics(like 0MATERIAL, 0PLANT) values 1001, 2002 for company codes, S01, S02 for Sales organization , Doc Type , Plant to meet business report requirement.

 

Every organization will expand their business, as part of business growth organization might merge or establish new plant, company codes, sales organizations etc.

 

Technical Impact due to organization changes.

 

Due to organization changes we might get into technical challenges/issues for example as part of business expand Company ABC new plant has been acquired (Plant 0567) and now I would like to include this new plant wherever already nearby plants (0545) included to same area manager to analysis inbound/outbound business data,  for this I need to search where existing plant 0545 is restricted so that I can include new plant 0567.

 

Approaches to find 0545.

 

  • Search manually respective reports in BEx designer like Filters Area, Default Area, Restricted Key Figure  and Selections, etc....might be restricted anywhere in report like cells, so its very tough job if its complex report
  • Search in table level,  however its required skill what are the tables and link between them
  • Search through FM however its gives unstructured data, need to format and also it works one report at a time.

 

Solution "One Click Away"

 

So, I had written ABAP program which will give list of reports which are restricted by given Infoprovider/InfoObect and characteristics value.

 

 

Here we can search based on Multiprovider/InfoCube/DSO OR Infoset wise, we have to select Radio button based on requirement as like below


1.png

 

Example for Mulitprovider.


Example 1

 

Now, I am searching based on Mulitprovider/InfoCube and selections as like below


Multi/Cube Tech Name: ALL (Means all Mulitproviders and Infocubes)


Infoobject Tech Name: 0PLANT (For Plant) You can give more infoobjects at a time.


Infoobject Value          : ALL (Means for All Plants).



2.png

Output:


Provides list of reports restricted by All 0PLANT values


Example 2

 

Now, I am searching based on Mulitprovider/InfoCube and selections as like below


Multi/Cube Tech Name: ALL (Means all Mulitproviders and Infocubes)


Infoobject Tech Name: 0PLANT (For to Plant) you can give more infoobjects at a time.


Infoobject Value            : 0123 (Where Plant equal to 111).


3.png

 

Output


Provides list of reports where plant restricted by 1111


4.png

Example for InfoSet.

 

Example 1


Now, I am searching based on Infoset and selections as like below


Multi/Cube Tech Name : ALL (Means all Infosets)


Infoobject Tech Name  : 0PLANT (For Plant) You can give more infoobjects at a time.


Infoobject Value            : ALL (Means for All Plants).


5.png

Output:


Provides list of reports restricted by All 0PLANT values


Example 2

 

Now, I am searching based on Infoset and selections as like below


Multi/Cube Tech Name: ALL (Means all Infosets)


Infoobject Tech Name: 0PLANT (For Plant), You can give more infoobjects at a time.


Infoobject Value            : ALL (Where Plant equal to 1111).


6.png

Output


Provides list of reports where plant restricted by 1111

7.png


Attached ABAP Code.



Note:

  • Added code, provided option to  search ODS wise also,
  • And changed selection screen sequence for more convenient


Thanks for reading Blog.



In addition to the "Few important commands in WAD in SAP – Part 1", in this blog I would like to share few more useful commands those usually use in many reporting scenarios.

 

Context Web Item:

The Context Web Item is used to show many of the reporting properties such as Sort, Select Filter value, Remove Filter value, Broadcast, Go to (Report Report Interface), Export to Excel, Export to CSV, Global currency, Variable screen etc when we do right click on the reporting area in the report. The properties will work when there are proper settings maintained for the query in the back end.

 

Report Report Interface Command:


When there is a requirement to have a jump report from the existing query to another query by pressing a button. This is simply called RRI. To achieve this we need to follow the below procedure,

 

  1. Create an RRI functionality using the transaction RSBBS in the SAP BW backend.
  2. Open WAD and create a button.
  3. Add a Command "Command: RRI" to the button by mentioning the RRI functionality created in the backend. Please find the below screenshots for details on the settings and how to add properties,

 

 

Currency Conversion Command:


When there is a requirement to have a currency conversion on pressing a button. To achieve this we need to follow the below procedure,

 

  1. Create a Currency conversion using the transaction RSCUR in the SAP BW back end.
  2. Open WAD and create a button.
  3. Add a Command "Command: SET_CURRENCY_TRANSLATION" to the button by adding the properties for currency conversion in the back end. Please find the below screenshots for details on the settings and how to add properties,

 

 

Ticker Web Item:


Ticker Web Item is used to show the scrolling kind of information on the top of the Report. We can add some custom messages where we can understand few details by reading the scrolling information.

Web Application Designer (WAD) is used to design templates for the report embedded in it. Usually report simply provides an excel based output. In addition to this WAD can provide some extra functionalities or properties such as Links, Buttons, Back screens, Filter panels, Tickers, Drop downs, Radio buttons, List boxes, Hierarchical filter selection, Info Fields etc to the existing report. Here in this blog I would like to share few important commands used,

 

Suppression of Messages:


While executing the Bex Reports, sometimes we may come across some warning messages, which are not much harm for reports, but Users may feel that there is some issue with the reports. So in this case we should suppress those messages, this can be done through WAD using the Web Item "System Messages". We need to suppress the messages by selecting the property Visibility as "Hidden". Please find the below screenshot where the setting needs to be done,

 

 

Filter on Selected Record:


Sometimes users may like to filter a row based on a field (For Example: Organizational Unit). Please find the initial data available in the report below,

 

Organizational Unit

Organizational Manager

Employee

Location

Country

Sales

Karl

Nichol

New York

USA

Sales

John

Martin

New York

USA

Sales

Randy

Micheal

London

England

Finance

Eva

Jana

Munich

Germany

 

When User selects a row (Organizational Unit) on "Sales", User should see only data related to the Organizational Unit "Sales". Please find the data how it should display,

 

Organizational Unit

Organizational Manager

Employee

Location

Country

Sales

Karl

Nichol

New York

USA

Sales

John

Martin

New York

USA

Sales

Randy

Micheal

London

England

 

To achieve this type of functionality we should as below,

 

  1. Create a Button using the Web item "Button Group".
  2. Edit the description of the Button to "Filter Selected" and select a command "SET_SELECTION_STATE_BY_BINDING". Please find the details in the below screenshot,

 

 

3. Add the field for Selection after selecting above command.


 

As per the above screenshot, whenever user clicks on the row based on the field "Organizational Unit", it will filters out that data only and show as output.

 

How to remove filter after selecting a filter on a Row:


  1. Create one more Button and name as Back to Initial.
  2. Select a command "BACK_TO_INITIAL_DP_STATE". Please find the details in the below screenshot,

 

 

After selecting the above command, the screen of the report will move to the initial screen as below,

 

Organizational Unit

Organizational Manager

Employee

Location

Country

Sales

Karl

Nichol

New York

USA

Sales

John

Martin

New York

USA

Sales

Randy

Micheal

London

England

Finance

Eva

Jana

Munich

Germany

Hi BEx community,

 

These days I came across a requirement to find out when was the last used date of a certain Workbook. I could not find an easy way to enter the Workbook title and simply get the Workbook last used date information.

 

 

Anyway, I just found a three steps way to find out this useful information (let's say you want to clean-up some unused Workbooks and want to make sure it was not used for the last couple of years, for instance). Below I describe how I found the Workbook last used date.

 

 

1- Make sure the system is collecting statistics for the given Workbook:

 

Enter transaction RSDDSTAT on BW backend, go to tab 'Workbook' and make sure the statistics are turned ON for this specific Workbook, otherwise you might no rely on this guide, since the Workbook might have being used but no statistics are being generated.

You can find more information about Configuring Statisics Properties on this link.

 

27-07-2015 15-51-52.jpg

 

2- Get the workbook_id information:

 

The Workbook statistics are saved under the workbook_id information and not it's title, so we need to get this information before filtering the statistic table. Enter table RSRWBINDEXT on se16 transaction, filter attribute TITLE by the Workbook name and copy the value on WORKBOOKID attribute.

 

27-07-2015 15-52-19.jpg

 

3- Filter statistic table RSDDSTAT_OLAP to get last used date and other information:

 

With the workbook_id, enter table RSDDSTAT_OLAP on se16 transaction and filter attribute OBJNAME = workbook_id.

Make sure the values returned are NOT being truncate by the Maximum No. of Hits (You might need to use the CALDAY attribute to restrict the result-set if this is the case).

Once you have the results, you can sort it by CALDAY attribute in descending way. This same CALDAY will show you the last day the Workbook was used.

You can find other information such as UNAME: user that executed it; UTIME: time it was executed; etc.

 

27-07-2015 15-53-08.jpg

 

Hope this little Blog Post can help you to easily find out the Workbooks last used date for whatever reason you might need it.

 

 

Cheers,

Eduardo

In BW Reporting scenarios, business or application manager who deals with the BW reporting requires a report, how many times does the report executed by which user, on what timing, DB time at the time of query execution, frond end time etc. can be determined in a single report. Below are the custom report details,

 

  1. On initial, the BW Administration cockpit is installed as a pre-requisite.

 

    2.  Create a custom query based on the Multiprovider (0TCT_MC01) which provides data form the Info provider Front-End and OLAP Statistics (Aggregated).    This Multiprovider provide proper information with satisfying the requirement.

 

Rows:


Calendar Month (0CALMONTH)

Report Name (0TCTBISBOBJ)

User (0TCTUSERNM)

 

Note: Please chose property Display Result Rows with "Always Suppress".

 

Columns:


Number of Executions (0TCTQUCOUNT)

Duration (OLAP) (0TCTTMEOLAP)

Average Duration (OLAP) – Calculated Key Figure (Time OLAP (0TCTTMEOLAP) / Count for BI ApplObj (0TCTQUCOUNT)

 

Free Characteristics:

 

Calendar Day (0CALDAY)

 

Note: If you require at what time the report is executed, please include the filed Time Stamp of Report execution.

         Please chose property Display Result Rows with "Always Suppress".

 

Filter:


BI Appl. Obj. Type (0TCTBISOTYP) – Include ELEM (Which Selects Reports only)

 

Calendar Month (0CALMONTH) – User Entry Variable, which provides selection to the User to select which Month data required

Info Provider (0INFOPROV) – Include Only Info Provider (0TCT_C01)

 

User (0TCTUSERNM) - User Entry Variable, which provides selection to the User to select which User data required

 

Report Name (0TCTBISBOBJ) - User Entry Variable, which provides selection to the User to select which Report data required

 

Object Version (0TCTOBJVERS) – Include Only Active Version (A)

 

BI System (0TCTSYSID) – Add SAP Exit Variable (0P_SYSID)

 

3. On successful creation of the query, execute the report and it will displays the below output,

 

Variable Screen:


 

Report Output based on the Variable entry selections:


Blog Overview

 

Issue

In Past few years SAP BEx designer changed/improved and added few options, however never added an option like deriving characters/strings/flags or status based on business logic ( like Completed, Open, In-Progress, True and False etc) to derive such values in BEx designer is very difficult and in most of the times it’s not possible also. Apart from this we have few alternatives to bring characters/strings in BEx Analyzer like VB Macros, HTML Script, Java Script, BEx Virtual characteristics or adjusting data targets by adding infoobjects and writing ABAP program to populate desired values as per the business requirement.


Alternatives

  1. Virtual Character
  2. Adjusting Datamodel (modifying data target by adding extra infoobjets)
  3. VB Macros

 

Pros and Cons:

To implement above three alternatives we  require extra maintenance/time and may lead to performance issues while ET&L and Analyzing data and also in few scenarios full data drop & reload is required.

Workaround

Let’s take below example

 

Table

 

 

   Sales Region

   Status

   Sales in %

   AUSTRALIA

A = Best

  1. 24.90 %

   CANADA

C = Bad

  1. 1.33 %

   CHINA

B = Better

  1. 17.34 %

   GERMANY

C = Bad

  1. 1.33 %

  INDIA

B = Better

  1. 16.20 %

   JAPAN

C = Bad

  1. 1.34 %

  N KOREA

C = Bad

  1. 1.36 %

  S KOREA

B = Better

  1. 15.96 %

  SINGAPORE

B = Better

  1. 17.34 %

  UK

C = Bad

  1. 2.50 %

 

In above table to derive “Status” column it may require virtual characteristics or add infoobject to data target and write ABAP program.

 

Solution to bring “Status” column in BEx designer without writing ABAP code/Virtucal char and modifying data model.

 

ROWS

Add Sales Region Infoobject and below this infoobject create new structure

As like below.

 

1.png

Right click on Structure and create 3 selections (Best, Better and Bad)

 

A = Best

B = Better

C = Bad

 

Keep the all selections (A, B and C) empty (do not drag & drop any char or key figure) as like below

 

2.png

 

COLUMNS:

 

Add key figure into columns and copy same key figure two times as like below and hide one key figure

 

3.png

Now, go to “Cell” Editor TAB

 

4.png

As shown below screenshot

5.png

Repeat same steps remaining B = Better and C = Bad “Cells” as above screen

 

How to derive “Sales in %”

 

Go to “Cell” TAB and write logic in “New Formula”

6.png

My logic as like below

7.png

 

Repeat above step for B = Better and C = Bad , as per your requirement

 

Report Output

 

8.png

Changes for Better Look & Feel of Report output

 

  1. Now in report output CANADA having “Better” sales, so I do not want display remaining status like A = Best and C = Bad

 

Settings

 

Select “Structure” and Right side you able to see structure Properties

Select  “Display” TAB and un-check “Structure as Group: Only apply suppression if all elements are 0” as like below

 

Screenshot:

9.png


  Select Report Properties and select options as like below screenshot

 

10.png

Now, you can see only “Results” which are required for us.

 

11.png

 

This Solution will work for similar scenarios as like below

 

12.png

 

Thanks for reading this Blog.

 

Regards,

Nanda

Blog Overview

In SCN community, noticed few people asking how to display unit as separate column instead of behind the value. Initial I thought its quite complex as we have only two options.

 

  1. Modeling level option: Create new Infoobject as characteristic and write routine/direct map(currency/unit object) in transformation to get currency/unit value and use in report, this option is good, if we know the requirement in initial phase like realization phase (while designing data model) after that it bit complex like once system Go-Live.
  2. Virtual Characteristics: Create new Infoobject as characteristic and write ABAP code to get currency/unit while BEx query execution however this will be less complexity if we compare with option 1. For virtual characteristics reference go to link SAP BW BEx Virtual Characters

 

However after thinking, got one more instant solution, we can derive currency/unit as separate column in query output by adding one extra FORMULA in BEx designer level.

 

Let See steps involved deriving currency/unit as separate column

 

Report output before implementing logic.

1.png

Logic :

 

  1. Add basic key figure "Amount"  to COLUMN and Rename "Amount BK" and Hide
  2. Create new formula Amount --> NODIM ( Amount ).
  3. Create new formula called "Currency" -- > ( ( Amount BK >=0 ) AND ( Amount BK <= 0 ) ) * Amount BK
  4. As like below screenshot.


2.png

 

Report output after implementing logic.


3.png

 

Note:

As I said its not permanent solution, because in above screenshot currency column act as key figure even though it has alpha values, it just tricky way of separating currency as in different column just for looking not for anything else and it does not support filter and other options, if business users required to provide selections/filters , I recommend select any one of two options which are discussed in this blog above.

 

Thanks for reading this blog.

 

Regards,

Nanda

Dear Community,

 

8 years after the release of SAP Netweaver 7.0 and the introduction of BI Java Runtime as the official frontend for the 7.0 BEx Tools (WAD, Query Designer) we still have questions about the basics of its integration, configuration, which SPS levels are compatible between ABAP and Java, which BI Java SCAs must be updated, patch level 0 and so on and so forth...

 

Based on this I have put together as a "guided procedure" all the most important and relevant KBAs and Notes with this information. Please access that at the link below:

 

BI Java Guided Procedure

 

Should you have any comment or question, just let me know. Enjoy!

 

Thanks and Kind Regards,
Marcio

As BW developers, we expect users to show some patience when they are executing BEx queries. Even when the window turns to 'Not Respondin' for a while. If its a rare occurance then we disregard it attributing system performance or connection.  However, sometimes BEx query execution frequently turns our browsers to 'Not responding' and that can do tricks on your patience and nerves . In this blog post we will look at some of the possible causes behind it and try to think about the solutions for same.

 

 

Background:

BEx query executing runs for a long time and then turns into 'Not Responding' window.

 

Possible Causes:

 

 

OLAP calculations: Sometimes due complex design of RKF's and CKF's or presence of Cell references the query execution result into not responding.

           Solution:

·         Reduce the strain on OLAP calculation.

·         Try to get as many calculation as you can at data load level itself.

·         If possible limit usage of ‘Cell references’.

·         Characteristics having high volume can be moved into free characteristics area. Initial display of query result will be with small set of data.

·         Try to breakdown the query in simpler queries using Report-to-Report interface

·         Instead executing a complex query, consider saving the result if that query in a DSO using APD and then in a simpler query reading those results directly.

·         try to eliminate obvious performance hindering objects e.g. virtual characteristics/KFs

·         guide users to apply the drill downs in such a way that they happen only after certain filters are applied. This will increase the responsibility of users but atleast they will be able to see the result set.

·         Try the other tools/techniques that will help saving resources at query runtime.

    Huge Data set in background: Sometimes due to heavy dataset in the infoprovider may result into query execution result into not responding.

Solution:

·         Try to provide the complete selection criteria for the query. This will limit the data getting selected for query processing.

·         Make sure you have created Indices after data loads

·         Try to check the need to setting Cube partitioning, compression or introduction of Aggregates.

·         If BWA already exists for your server, then consider setting your cube for BWA

·         If possible, consider moving the selection criteria applied at RKF/CKF or local level to move at global default filter tab

C.SID Generation at Reporting.

Solution:

·         If your report is based on a DSO set to SID generation at reporting then try to change this setting to SID generation at load time

·         If possible, move your report on a infocube or Multiprovider.

D.System Performance: Sometime unavailability of system resources result into query execution result into not responding.

Solution:

·         If possible, increase the system processes available for query processing.

·         Run a SQL trace using ST05 and check the objects causing delay (http://scn.sap.com/docs/DOC-51233)

·         Make use of BW statistics to find a possible cause/solution

·         Wait for system resources to become available and try running query again.

·         Check for any Patch/Updates or Notes required to be applied to the system

Apart from these, generally running the 'Generate Report' option in RSRT works for our benefit too, to understand/resolve the issue.

   These are the one's that are curruntly top of my head. I will keep updating this post as and when newer causes and solutions make their apearance.

    -Swati Gawade.

 

Here I present you a recommended flow to analyze queries that return unexpected data. It can guide you finding out whether it happens due to a bug in the software or show you that your expectation is not correct.

 

Start testing the query at T-Code RSRT (HTML mode). This flow assumes that you can see the unexpected values even when executing the query there. The numbers between parenthesis refer to an SAP Note where you can find details on how to execute that step. The direct link to those notes can be found in the end of this post.

 

flow.jpg

 

Step 1: Execute the query at RSRT (HTML mode) using the button Execute + Debug and selecting Safe Mode. See SAP KBA 2125357.

 

Step 2a: In case you get the expected/correct results when running the query in Safe Mode the issue most likely lies on Aggregates, BIA/HANA indexes, Cache or DB Optimizations. Use the instructions in the SAP KBA 2142771 to narrow down which one is causing the error. Search for notes in that area. In case you cannot find any relevant, open an incident under the component shown in the picture above.

 

Step 2b: In case you still get unexpected/wrong results even in Safe Mode, you will need to simplify your query. You can find how to do that in the SAP Note 1125883.

 

Step 3: Now that you got a simpler query it's possible to compare the query results against the values in the InfoProvider. To make it easier to investigate, create a LISTCUBE report as explained in the SAP KBA 2055174.

 

Step 3a: In many cases, after doing all the above steps one can find out that the results in the query are correct and some mistake was made when designing the query. Other times there is really a failure in the software doing the calculations. In this last case, log an incident under BW-BEX-OT-OLAP component.

 

Step 3b: If the values of the Basic Key Figures in the query don't match their values in the cube there's likely a bug in the application. Log an incident under BW-BEX-OT-OLAP component.

 

In case you need to create an incident, whatever the component is, provide the technical name of your simplified query and the LISTCUBE report.

I work on many incidents a day and whenever I get a new one I have a big challenge: to understand the customer’s issue. The customer may have clear in mind what his/her issue is. But normally it’s not easy for me to understand it. So, some time is taken until I completely understand the issue.

 

Here I will present some tricks you can use to help us helping you and get faster solutions.

 

1. Search first, log an incident later


My first tip is to use the SAP xSearch to search for existent SAP Notes, Knowledge Base Articles (KBAs), SCN blogs, discussions and wikis. Mainly if you get an error message: it’s easier to elaborate a search when you have an error message.

 

A good search will maximize the chances of finding a solution yourself. But you may think: “I pay SAP for support. They must provide a solution”. Well, you are right. But as much faster you get a solution, the less the issue will impact your company, right? As I said, we (support engineers) take some time to understand your issue before being able to start our investigation. And nobody knows more about your issue than yourself.

 

The SAP KBA 1540080 is very basic and shows how to perform searches on the SAP Service Marketplace. If you’re already familiar with that process jump to the next: the SAP KBA 2081285. This one explains in more details how to perform the most efficient searches. Believe me: if you don’t know it yet, it worth a try.


2. Create an incident


You combined many search terms but could not find an existent correction (SAP Note, KBA, etc.) to your issue so you need to log an incident. In this case, the more quality has the initial information you provide, the less time it will take to the support engineer to start the actual troubleshooting of your issue.

 

Sometimes we see incidents going back and forth requesting the connections to be opened, the customers claiming that the connection is opened but the support engineer reports that it still fails, the support engineer requesting more detailed information about the actual issue, how to reproduce it, traces, etc.

 

So, here’s a checklist for OLAP incidents (some of them may apply to other areas):

 

requisites.jpg

 

a) Select the correct system ID and installation number

Opening the incident for the correct system ID and installation number will ensure that no confusion is made by the support engineer when accessing your systems. In case the incident needs to be forwarded to other component or to the Development Support no time is wasted finding the correct system to connect to.

 

b) Open the connections

Usually, for troubleshooting OLAP issues, the support engineer needs R/3, BW RFC and BW GUI connections. The instructions on how to set those connections up are contained in the SAP Notes 812732 and 195715, respectively. He/she also may need HTTP connection for testing whether the issue you reported lies on either the backend or frontend layer. The SAP KBA 592085 shows how to configure the HTTP connection.

In some companies the process of opening a system connection is very bureaucratic and/or takes a considerable time. You wonder if there would be a way to do this just once. In fact, there is: the Line Opener Program (LOP). Read the SAP Note 797124 for details.

 

c) Provide the logon information

If you don’t provide the logon information in the secure area the support engineer will not be able to connect to your system. The SAP KBA 508140 explains how to provide user name and password in the secure area.
IMPORTANT: Never write down a password in the body of the incident. Use the secure area instead.

 

d) Ensure that the user you provided has proper authorizations

The user account to be provided to SAP must have some specific authorizations. In the ideal case you provide a user with SAP_ALL authorization. In case that is not possible, at least the authorizations in the SAP Note 177875 must be given.

 

e) Simplify your report/query

Your business scenario sometimes require very complex reports to be designed. Some issues then show up (like after an upgrade, for example) and the customers log an incident for that. However, in nearly all cases the report/query can be simplified and the issue is still reproducible.

Simplifying the query allows the support engineer to focus on the real potential causes of the issues and can save much time investigating the issue. The SAP Note 1125883 shows some recommendations to be followed in that matter.

IMPORTANT: For performance issues it is very important that you apply filters to your report/query so its execution time does not exceed 15 minutes (notice that the support engineer will execute it repeated times). Also inform by what factor the applied filters will reduce the amount of data returned by the report/query (e.g. “my filters reduce the query results in 80%”).

 

f) Explain clearly how to reproduce the issue

Provide a document with screenshots showing each step taken until the issue is reproduced: what transaction (T-Code) you open, what is the name of the report you are accessing, what is the technical name of the BEx query associated to that report (if applicable), what input values are required (if any), where to click, what options to select, etc.

Recently I worked on a project where we upgraded our BEx components from 3.x to 7x an encountered numerous issues, In this technical blog I have listed one of the interesting issue related to BEx Analyzer limitation


Problem Statement


Part of BEx Upgrade project BW workbooks when upgraded from BEx 3.x to 7x started experiencing issues for having huge data set (1 Million data cells and beyond). Business users were executing workbooks which were either stored on local machine OR from the server having 10,000+ Rows & 90+ Columns, upon execution they got a popup "Client Out of Memory" .


This phenomenon has been described in SAP note 1411545 & 1040454. Understood from SAP that there is a design limitation for 7x analyzer (0.75 Million data cells) because BEX 7.X leverages MS .NET framework having this memory limits.


As per note number of cells that can be displayed as defined by SAP is 750,000 OR 1.2GB but the our workbooks had 1+million data cells. Calculation of the data cells is as per allocated memory MB = 100 + ( (# Rows * # Columns) * 0.0016)


Only the result set in the workbook should be considered for Number of Rows and Columns in above calculation

 

Alternative Approach/Workarounds


1. Cut down data set size by including additional filters to display the data, so that number of rows and columns gets reduced. SAP says "Anything extracted beyond ~.5M cells is considered as ETL requirement. BEx is a reporting and analytical tool and this should not be used for data extraction as an ETL tool."

 

2. If your result set has more number of columns due to Drill Across fields for all key figures/measures then change the way how reports are executed by rearranging the drills down/across so that the number of columns get reduced as we have no control on the number of rows.


3.  Every workbooks will have corresponding query associated with it. If in your case workbooks have only one query in the back end then as k the users to access corresponding report from either 3.x OR 7x analyzer instead of workbooks as queries have separate memory restriction which can be extended in RSADMIN.


With this workaround you can instantly run in excel and you also have refresh functions as in workbooks but the look and feel might be slightly different

 

4. If in your case the workbooks can be accessed both 3.x and 7x then you can ask the users to open 3x analyzer and access the migrated workbooks as 3.x workbooks dont have .Net limitation


5. Execute the report in Web and download to CSV as query can handle huge data set, presuming you maintain appropriate settings in RSADMIN


6.  Execute the report in smaller chunks using a custom ABAP program and then merge the files together

 

All the above workarounds will be limited until SAP BW/BI version 7.30 and presuming you enable both 3.x and 7x front end tools.


I also performed research on couple of other BI tools (Hadoop, Qlikview, Tableau..) Some of them dont have the restriction as in Analyzer but most of them are meant for small data packets and not for huge data volumes.


One that can be a potential alternative is SAP Business Objects Analysis for Office Edition, A premium alternative to Bex Analyzer. When I googled found combination of OS 64 Bit + Excel 64 Bit  + Analysis for office 64 Bit can handle huge data set but there are mixed reviews about this tool.


Relevant SAP Notes

1411545 & 1040454

1973478 - BEx Analyzer: Safety Belt option for large Query Results

1411545 - BExAnalyzer: safety belt for large resultsets

1860872 - Report not executable in BEx Analyzer 7x / Client out of Memory

2061104 - BEx Analyzer: Throws out of memory exception when there are many data providers and hence the size of the XML is very large.

2041337 - BExAnalyzer: Performance in Serialization of Structure Members and Cells

1958613 - Optimizing the BEx Analyzer Performance - Known Corrections and Best Practice Guidelines

1466118 - Hardware & Software requirements for Analysis, edition for MS Office


Thanks

Abhishek Shanbhogue

Whenever our Bex query execution turns into an error or users report error in query execution, one should follow some basic steps before turning into a paniced maniac.


Sometimes we have worked hard on a query developement but the query gives error instead of the desired output. This can be very frustrating as we have already spent a lot of time on the developement and the error causes us further distress.


One can follow some basic steps below to make sure that there are no obvious mistakes.

 

1. Use Generate Report option from RSRT tcode. This will either resolve the issue or give you a better description of the error.

 

2. Test all the infoprovider objects in RSRV. This will let you know if there is any issue with the inforproviders, their indices or their structures.

 

3. Run RSRV test on all the master data objects involved in the info-providers. Sometimes the issue lies in the SID values or the indexes built on the master data. Often, you can find garbage master data causing issues as well.

 

4. check if the report is giving problem only to your ID or only your system? try checking on other systems and ask you collegues to try with their ID.

 

5. Check the patch level of BEx software and GUI. Is it old? does it need to be upgraded?

 

6. If the error is occuring only in a partiular system then try to retransport all the objects in the Query. (structures of the multiproviders and their respective Cubes) If it's not production system then I guess you can drop data and reload from underlying DSO/data providers.

 

7. Check if there were any warnings displayed when you transported the Query or any other object that is involved in the query. Sometimes these mild harmless warning messages turn into real nightmare.

 

8. Check if you have any customer exit code in your query. any code that needs to be executed before selection screen is displayed . Check if CMOD code was moved to appropriate system.


9. Check if any notes that need to be applied to your system which are causing issues.

 

for Bex query execution turning into 'Not Responding' see my blog:

http://scn.sap.com/community/business-explorer/blog/2015/06/12/when-your-bex-report-execution-turns-into-not-responding

 

 

Hope this helps.

 

-Swati Gawade.

I have seen many questions in SDN Business explorer space regarding the display settings of Decimal points. This blog will explain these settings in detail.

 

Background:

In the BEx query designer, we can set the number of decimal places till which a Key figure will be displayed as output. These settings can be set for each key figure individually by selecting the keyfigure and going to ‘Display’ tab in ‘Properties’ section in the query designer.

 

Pic1.jpg

 

If the ‘Use Default Settings’ Checkbox is ticked then the no. of decimal places displayed will be determined from the Key figure info-object properties.

 

To view the default properties of the key-figure, go to the display of the info-object and in the ‘Additional Properties’ tab, you will able to see the default properties set for that key figure.

 

pic2.jpg

Problem Description:

As per business requirement, the no. of decimal places displayed should be 2 whereas the output of query displays 3 decimal points.

 

Solution:

Step 1: Go to the decimal settings in the Query designer as explained in background.

 

Step 2: Check if the ‘Number of Decimal Places’ dropdown has been set to 0.00 (as the requirement is set to display 2 decimal places)

 

Step 3: If not, then set it to display 2 decimal places as shown below.

 

pic3.jpg

Step 4: if yes, then check if the ‘Use Default Settings’ Checkbox is ticked as shown below.

 

pic4.jpg

Step 5: If yes, then uncheck the checkbox.

 

Now your BEx Designer settings are accurate to display the correct decimal places as set in the ‘Number of Decimal Places’ dropdown. It must have been displaying numbers upto 3 decimal places as per the default info-object settings for this key fogure as explained in background section.

 

For confirming, you can simply check the settings trough transaction RSA1 or RSD1.

 

Note:

Sometimes after an upgrade, the decimal settings are changed. For this issue refer to SAP Note.

Hello BW People!

 

Recently, here at Product Support, we have been informed by our development team that they had detected some BICS issues mainly in BW 7.40 SP 8 and SP 9.

 

They affect all the reporting tools which make BICS usage, such as WebI, Design Studio, Analysis for Office, BEx Web Java Runtime, etc.

 

Those issues are related to filters, hierarchy usage and variables. So, in case you have BW 7.40 and see any issues with filters not being applied, hierarchies not correctly displayed/validated in prompts/variable screen, variables not being filled or executed in case of customer exits, please take a look at the notes below.

 

 

NoteApplicable for BW 7.40 SP
2101236 - Missing data in query with artificial characteristic Currency/Unit789
2050425 - BICS: Dynamic filter missing and inconsistent state (N_SX_META_DATA)5678
2101188 - BICS: Filter on structure element is reset to initial selection789
2092810 - Filter variable on structure becomes ignored89
2064630 - BICS: Compoundment on Hierarchy Node Variable does not work5678
2096560 - BEx Web 7.40: Termination when using exceptions or conditions678910
2068075 - BICS: Values of exit variables remain empty after refresh6789
2052141 - Variable values not correctly resolved5678
2109550 - Bookmark deletes filter selection8910
2107060 - RSBOLAP: Correction for SAP Note 2065089 (reader reset)78910
2094538 - BICS: struc. member change after submit incomplete89

 

As clearly stated in SAP Note 2000326, please do apply these notes in any case, as they solve known issues and have no reported side effects.

 

With this you will avoid many known BICS issues that many times are not that easy to detect, trace and have a quick resolution, even with SAP Support help.

 

Kind Regards,
Marcio

Actions

Filter Blog

By author:
By date:
By tag: