1 2 3 6 Previous Next

SAP Business Explorer (SAP BEx)

80 Posts

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

     In some discussions, I realized that there is a need to define condition according to the value of a characteristic. It is very easy to deal with key figure values to create a condition, but when it comes to the characteristics, we need some work around.  In this blog I will discuss a few alternatives and I will try to explain the procedure for one of them with a small scenario.

 

Scenario:


     Suppose we have a scenario where we have material type, materials and their related prices. We want to apply a condition on material type. When material type is “A” we don’t want to show the list of materials with 0 price. But when the material type is something else, we need to show all materials in the BEx query result.

 

Alternative Ways:


1. Change in design of the model


We can create a new characteristic which will point out the records to be showed in the report. The data type may be char1 and in the transformation rule, we can assign   1 to the records that will be showed and 0 for the records which we don’t want to be in the query result. Then in the query designer we can simply apply a filter on this characteristic in the characteristic restrictions tab.

 

This is a solution where we are flexible with changes in the modelling side. In most of the situations, change to the model may not be feasible due to huge amount of data which cannot be reloaded.  There are also cases where the customers don’t prefer to change the model even without any reason! Then BI experts are restricted to find solution on BEx side.

 

2. Replacement Path Variables in BEx Query Design

 

We can use this approach only if we have the characteristic data type defined as numeric. With this approach, we can create a formula variable with replacement path where we get the key value of the characteristic. When we have this variable, we can use this variable in a formula to write an if-else statement.

 

This solution is also restricted with the data type of characteristic. If it is a char defined characteristic, this solution also becomes useless.

 

3. Creating calculated key figures and using conditions in BEx Query Design

 

With this approach we define a calculated key figure (CFK) to count the records we want to show in the query result. According to the value of this CKF, we define a condition to show the results.

 

When other approaches I mentioned above are inefficient, this solution is what we are left with. Now, I will go into details of this approach.

 

Suppose we have data:

 

Material

Material Type

Unit

Price

ABC129

A

USD

912

ABC128

A

USD

178

ABC127

A

USD

0

ABC126

A

USD

167

ABC125

A

USD

154

ABC124

A

USD

0

ABC123

A

USD

0

ABC135

B

USD

0

ABC134

B

USD

25

ABC133

B

USD

0

ABC132

B

USD

266

ABC131

B

USD

187

ABC130

B

USD

644

 

          We want to show the all records of material type B, but we also want to hide 0 prices for material type A. The final report should look like this way:

 

Material

Material Type

Unit

Price

ABC129

A

USD

912

ABC128

A

USD

178

ABC126

A

USD

167

ABC125

A

USD

154

ABC135

B

USD

0

ABC134

B

USD

25

ABC133

B

USD

0

ABC132

B

USD

266

ABC131

B

USD

187

ABC130

B

USD

644

 

          We create query on the infoprovider adding material to rows and price to columns.

 

1.JPG

     Then we create two calculated key figures to count the number of materials we want to include for each material type. We want to show the materials where price is greater than 0 for material type A and all of the materials type B. The first CKF will count the number of all materials where the price has a value.

 

2.JPG

 

     In the aggregation tab we select counter for all detailed values that are not zero, null or error:

 

3.JPG

     We create another CKF to calculate the number of all materials whatever the price is. The same way we add price to the general tab and select counter for all detailed values in the aggregation tab:



4.JPG


5.JPG


     At the next step we use these CKFs. We can either create a selection or a Restricted Key Figure (RKF) for this purpose. We add the CKF where all materials are counted and restrict the material type to type “A”:


6.JPG


     The same we create another selection (or RKF)


7.JPG


     Now when we run this query we will see that in each of the row we want to show, either type A >0 column is 1 or type B all prices is 1:


8.JPG


     At this point we are successfull to eliminate 0 values in type A. The remaining procedure will include adding a formula to sum up the two columns type –A>0 and Type-B all prices. And then we can add a condition on this new formula:


9.JPG


     Now we have the report shown as:


10.JPG

     When we create the condition:


11.JPG


     And we make sure that we don’t suppress zeros in the query properties:


12.JPG


     We hide the formula and selections:


13.JPG


And when we run the report we get:

14.JPG


We can use this procedure in all cases where we can count the number of detailed records.

 

            Hope it helps

 

Regards

 

Yasemin Uluturk




This post is about geovisualization and spatial analysis of BEx data with SAP BusinessObjects BI Location Intelligence.

SAP BusinessObjects BI Location Intelligence is a geomapping extension for SAP BI (See SAP website).

 

We are going to see how to get the following map:

This post is specific to SAP Webi on BW/BEx.

You can look at this post to get a similar topic specific to geomapping with Design Studio:

Interested in Location Analytics for Design Studio? Take a look at this blog

It is assumed that 80% of enterprise data is geo-located and this will increase in the future with mobility, Internet of Things, and smart grids amongst other factors.


So what is a geolocated data

The most common geolocated data is a point location with Latitude/Longitude coordinates.

Note that coordinates can be defined according to different coordinate systems such as Lat/long in degrees and X/Y in meters just to give a couple examples.

Geolocated data can also be related to geographical areas such as zip code, counties, administrative boundaries or your own business location such as your points of sale, territories, technical assets or networks.

If you have the above type of data in your BW/BEx systems, you should be interested by this post.


Let's see how we can leverage this data through the power of SAP BI 4.x Web Intelligence and its Location Intelligence extension.

 

Step1- Build your Webi document using a BEx query


Here is a broad overview as the purpose of this post is not to go into the complex details. Refer to the following document to get more information about this:

http://help.sap.com/businessobject/product_guides/boexir4/en/xi4sp7_bex_queries_en.pdf

 

1. Build a BEx query with location lat/long coordinates and associated business measures.

This is done in SAP BEx Query Designer.

Check that your lat/long coordinates consist of numerical data.

 

2. Define a BICS connection to your BEx query

This can be done in the SAP BusinessObjects CMC or in the SAP BusinessObjects Information Design Tool.

You can define a connection to a single BEx query or to an InfoProvider containing several BEx queries.

 

 

3. Create a Web Intelligence query on your BEx query

  • Go and connect to the SAP BusinessObjects BI Launchpad
  • Create a new Webi document using the Rich Internet Application client

 

4. Add the appropriate data in your Webi query

  • Open the query panel to access the BEx query data
  • Add the lat/long coordinates and the required business measures
  • Add prompts / filters according business needs
  • Test your query to check that you get one record per location with coordinates and measures
  • Save and close your Webi document

 

And now, let's go for geomaps!

 

Step2- Add a geomap in our Webi report

 

1. Open your Webi report with the HTML Viewer (See user preferences)

 

2. Insert a map container in your report

  • Add a blank cell in your report
  • Define its size and properties according online help

 

Your report should look like this:

 

3. Go to the Cartography menu and choose Map Document:

 

4. Define the different settings in the map wizard below:

  • Choose a cartographic view among the ones made available by the administrator
  • Choose the viewer to use (Flex for advanced use, HTML5 for more intuitive UX)
  • Choose your Query with BEx data in the pick list
  • Choose the fields to use for X/Y coordinates

 

5. Submit your settings and refresh your Webi report to display the map

 

6. It's time to visualize your business data on your map

  • Go to the Menu Layers
  • Open the Thematic Manager
  • Add a Layer
  • Define the settings of the map visualization (Business measure, color, size...) in the following wizard

 

  • Apply to see the map as below:

 

 

In conclusion, you can get more insight on your BW/BEx data with dynamic geovisualization by taking advantage of the power of Webi and its Location Intelligence extension

Have a look on the new map we've got in a few seconds!

Just have refreshed the Webi query and subsequent BEx data and changed the background map and the visualization settings to a heatmap.

 

Bookmarks are saved navigational states of a Web application.In the bookmark functional area of the Reporting Agent,

one can manage the bookmarks created in the system for all BW system users.

Many a times we do not require the bookmarks and it needs to be reorganized or deleted.

 

Creation of Bookmarks in BI system:-

  •    in BEx Web Analyzer
  •    once you personalize a web application
  •    using Bookmark function in the context menu of a web application.

 

Reorganization of BookMark : -

 

we can follow below simple steps to reorganize bookmarks :-

 

1. Go to SA38

 

2. Run the report :-RSWR_BOOKMARK_REORG
1.png

3. you get the below screen to fill the details


General Criteria

 

Created by User: You can enter the user name .

Time of Attachment: Enter the timeframe.

 

Bookmark Points to Following Object

Type: Choose the basis of bookmark e.g a report, a query view etc

Technical ID: technical name of web template


1.png

 

 

4. Once you fill the details and execute it, gives you list of bookmarks , you can choose from to delete:-

 

 

1.png

 

we can delete/reorg the bookmark from Broadcasting tab of RSA1 as well.

 

we need to go to RSA1-->Administration -->Broadcasting --> Reorganization -->Bookmarks

 

 

1.png

then we choose bookmark:-

 

1.png

then we fill the no. of days  for deletion and reorg. and simulate in "Test Call".

 

1.png

 

it displays the simulated results of what it will do :-

 

 

1.png

you can confirm and re-run it by unchecking the "TestCall" for deletion.

Hope this helps you in deleting/reorganizing your bookmarks.

 

cheers,

vinay singh

Hi Folks,

 

This is my first blog post in SDN and I want to share a simple Bex trick with you. Some of you might already know it.

 

Background:

 

while creating a BEx query, we often want to use the variables that are already available and ready for use. It is always recommened to re-use the objects instead of creating new ones. These re-usable objects may or may not be created by you or your collegues and you may want to confirm there behavior before you use them in your query. Sometimes its tricky to find which variable suits your requirements best, as you wouldn't know the purpose of all variables available for a certain characteristic. Here, you must note that for all the standard variables, SAP has mentioned their properties in description itself. However, if that not how your custom variable's description is mainatined as per given naming convetion then one needs to look at the properties of the variable to determine its behaviour.

 

The general way to check the Re-usability of a variable is to check the property of the variable and mostly its done by getting the required characteristic in the filter area, right click -> Restrict -> Variables. when the list of all variables on that characteristic is displayed then we select it and click on option 'Edit Variable' to see the properties.

 

Bex_Variable_edit.JPG

 

Problem Definition:

 

Here, theres a chance of altering the Variable values unknowningly, which will lead to change the behaviour of the variable across all queries.

 

Sometimes, we might not even have the authorization to go in the Edit variable mode. Finding out the variable properties becoms difficult in this case.

 

Solution:

 

so simpler way to check the variable properties without going in change mode is as follows.

 

in the infoprovider pane, expand on dimensions to the characteristic that you need.

Expand the characteristic further and you will see below 3 options, which can be further expanded.

 

1. Attributes

2. Characteristic Value variables

3. Characteristic values

 

Expand node 'Characteristic Value variables' and you will be able to see list of all the variables for that characteristic.

 

click on any variable and you will be able to see it's properties in the properties pane on the right had side of your Bex Query designer.

 

variable Properties.JPG

 

Here, you will be able to see all the properties of your desired Variable with ease and also they not in edit mode.

 

One more ease is in terms of checking out multiple variables.

 

 

Hope this helps you.

 

 

-Swati Gawade.

Hi All,

 

Here is my first blog post ..


As SAP suggested few safety belt s-notes to avoid server(JAVA stack) crashes and improve the fastness of server.

 

Many end users will run the BEx reports without filter or will run entire year data, in this cases, server will go slower or will create issues(slowness) for  other end users when they try to execute reports in portal. For avoid those issues - go below

 

Set Bex report execution limitation in Portal:

By default Bex report execution cell  limit is 10,00,000. This has to set lower(based on client requirement. Example 650000 cells

Check default settings in below table.

 

Se16>> table “ RSADMIN “ execute.  (If don’t have any setting’s for

BICS_DA_RESULT_SET_LIMIT_DEF or BICS_DA_RESULT_SET_LIMIT_MAX  please set )

 

Diff b/w DEF and MAX:   DEF – when the user ran the report,  limitation is DEF number. If the user wants to extend the cell limit, end users can increase MAX limit by clicking SETTINGS option.

 

Set values:  se38 >> programe SAP_RSADMIN_MAINTAIN >> execute (F8) then provide the parameters

BICS_DA_RESULT_SET_LIMIT_DEF = 500000 cells

BICS_DA_RESULT_SET_LIMIT_MAX  = 650000 cells then OK. We can modify also my clicking modify if wants to change the cell limit.

 

Portal execution limit msg.jpg

 

entend limit in web.jpg

 

 

For more details see s-note: 1127156

 

Set BEx report download to Excel/PDF limitation in Portal:


These limitations enhance the above settings.

The memory consumption may be larger for the export to Excel or to PDF than for the execution in the Web; therefore, a lower value can be specified as the safety belt for Excel and PDF.

BICS_DA_RESULT_SET_LIMIT_XLS =300000

BICS_DA_RESULT_SET_LIMIT_PDF = 300000

Execution default limit is 500000 and Max limit is 650000 in portal but download to excel/pdf is 300000 cells only if we set XLS and PDF setting in RSADMIN table.

 

Message when Excel limit cross.

 

excel.jpg

After Open Excel:

 

excel mesg.jpg

 

 

Message when PDF limit cross.


pdf.jpg



pdf msg.jpg


For more details see s-note: 1622134

 

 

Set report execution limitation in BEx Analyzer:


We know BEx reports can execute in Web and Analyzer.

We discussed web Safety belt setting in above. Below Analyzer settings.

 

ANALYZER_LIMIT_DEF: The default number of maximum cells if no further setting is specified. The default number can be overwritten locally in BEx Analyzer for each data provider.

ANALYZER_LIMIT_MAX: the absolute maximum number of cells (see below). The maximum number cannot be overwritten in BEx Analyzer.


ANALYZER_LIMIT_DEF=500000

ANALYZER_LIMIT_MAX = 750000


Message when cross limit:

Analyzer msg.jpg

For more details see s-note: 0001411545


How to select Max limit in Analyzer


After executing the report in Analyzer click the workbook settings as shown below for increasing the MAX limit. Increasing MAX limit is report specific.

 

Analyzer prop.jpg

Data Provider >> Properties

propertes.jpg

 


cell limit inc.jpg


Thanks

Nageswara Polaka


Actions

Filter Blog

By author:
By date:
By tag: