Webi How to Select a Raw With Where Clause

We love your data…
and it shows.

support

Blogs

Web Intelligence optimizations

12 augustus 2021

Central Management Console

Retry failed schedules

There is an option within the CMC that allows for a retry of the schedule in case of failure. There can be a number of reasons why a report cannot schedule properly. In some cases, this might have something to do with the system having resource or database issues. Once those are solved the report should run fine again. These problems sometimes only last a couple of minutes. That's why a retry with a waiting time in between would be a very convenient solution. Once it failed, the system will wait the specified amount of time and try again.

This option can be implemented in two different places. Underneath Default settings à Recurrence, this will implement the retry for every instance that comes out of this schedule. The option can also be found underneath Schedule –> recurrence. This one will only affect the current schedule.

Limit the number of instances in your BO environment

This feature can drastically improve the performance of the system. With the Limits feature, you can decide the number of instances for a folder or object. If there are any more instances then the set amount, it will automatically delete the oldest instance.

Steps to enable Instance Limits in the CMC

1. Go to the Folders management area of the CMC.
2. Locate and select the folder for which to set limits, and select Actions/Limits.
3. In the Limits dialog box, select the Delete excess instances when there are more than N instances of an object check box, and enter the maximum number of instances per object the folder can contain before instances are deleted in the box. The default value is 100.
4. Click Update.
5. To limit the number of instances per user or group, Click the Add button beside Delete excess instances for the following users/groups option.
6. Select a user or a group, click > to add the user or group to the Selected users/groups list, and click OK.
7. For each user or group you added in step 6, in the Maximum instance count per object per user box, type the maximum number of instances you want to appear in the BI platform. The default value is 100.
8. To limit the age of instances per user or group, click Add beside the Delete instances after N days for the following users/groups option.
9. Select a user or a group, click > to add the user or group to the Selected users/groups list, and click OK.
10. For each user or group you added in step 9, in the Maximum instance age in days box, type the maximum age for instances before they are removed from the BI platform. The default value is 100.
11. Click Update.

Try to use the Instance manager when scheduling reports

Within the instance manager is a lot of information that is useful to take into account when scheduling a new report. All of the reports can be filtered based on when their schedule starts. This can be a very nice tool to spread the workload of the system. Which will aid the system in the more flawless execution of all the different schedules.

There is also a filter that allows you to see all of the failed schedules. This is very useful to check in the morning to make sure that nothing went wrong with the schedules.

Bex Query optimizations

Enable "Use Selection of Structure Elements" in RSRT

Go to the "RSRT" transaction in BW and enable these settings by default for new queries.

Define Local calculations of key figures as summation.

It's best practice to define the local calculations of your key figures as summations. These calculations are being done in the backend, therefore it will cause less workload for your Webi report. It also decreases the amount of data it takes into the report, because the calculation is done before the data enters the report.

This is a good practice for all of the local calculations except the ones with the highest level of detail (lowest granularity).

Create counters with formula variables

This makes sure that the calculations are done in the backend. Because of this there is also no need to add the characteristics that need to be counted in the report.

The following link explains how this can be done: https://blogs.sap.com/2014/09/04/how-to-get-unique-count-of-values-in-one-1-or-more-characteristics/

Only use inclusion restrictions in your filters or restricted key figures.

When using inclusion, there is a database index that is being used to determine the filter. When using exclusion, it will go through each record individually to check if it matches the exclusion. This is a lot slower for the system to process.

Always set the result row to "Always Suppress" on all characteristics

This feature is not being used in Webi. When it is not on "Always Suppress" it will still process the calculation.

Do not activate or even select hierarchy's when they are not going to be used

If you have selected a hierarchy on a characteristic, you can uncheck the activation box. However, the hierarchy will still be taken into account. Make sure that there is also no selected hierarchy.

Restrict the depth of your hierarchy in the query

This will filter out any lower levels of the Hierarchy that might not be necessary. If level 0 is selected, all of the levels will be displayed.

Filter out the top node from your hierarchy in the characteristic restrictions.

All of the values which are not being used in the hierarchy are being placed under "Not Assigned". In order to avoid loading in all of this data, you can put a filter on the top node from the hierarchy. When this is set as a global filter under characteristic restrictions, none of the "Not assigned" data will be loaded.

Avoid the use of exception aggregation key figures and avoid formulas with divisions

Using exception aggregation can cause #TOREFRESH errors in Webi from time to time. In order to get around this issue it is easier to use the IN formula in Webi. This allows for the same aggregations' methods but with zero chance of a #TOREFRESH error.

Also try to avoid building formulas on top of other formulas which use a division. These often causes a key figure which has a "database delegated aggregation". This means that it will only aggregate on the levels the query provides. Once you try to make a custom grouping with a formula, it will give you a #UNAVAILABLE error. This can be avoided by creating the formulas with a division in Webi instead.

Set a filter on 0INFOPROV when you only need data from one cube from a multiprovider.

By setting a filter on the 0INFOPROV you will be able to only load in data from the cubes you need. This will avoid unnecessary runtime for the query.

Try to avoid unnecessary restrictions in Bex query designer

Use calculations instead of restrictions when possible.

For example: If you have 2 key figures with restrictions:

• Restricted key figure 1: restriction on APPLIED
• Restricted key figure 2: restriction on DX

Do not make a third key figure which has a restriction on both. Instead, make a calculated key figure which summates the other 2 key figures.  The same principle applies for formulas.

Webi Optimizations

Enable query stripping in the query and in the report

Make sure to enable this feature in both the query and the report. This will make sure that only the data that is being used will be loaded into the report. The fields which are not being used will be shown in a bold font.

Avoid retrieving large amounts of data

Use filters in the data source selection screen in Webi. When a report becomes too heavy to run, try to split up the report in multiple reports. The OpenDocument formula can be used In ideal circumstances, try to avoid:

• more than 10 tabs (20 max)
• more than 50 000 rows (500 000 max)
• more than 5 queries (15 max)

Do not select anything in the hierarchy member selection screen whenever possible

By selecting certain parts of the hierarchy, it will start loading in everything line by line. Which may cause a loading time up to 20 times slower. When the selection has been left empty, it will load in everything at once. However if there is a need for a selection, try to do this in the Bex query by setting the level.

Bex Query selection:

Webi selection:

Use If clauses instead of where clauses whenever possible

This works more efficiently in large formulas then where clauses. Try to use Boolean formulas with your if clauses.

Use filters on Keys instead of text

The keys will never change whilst the text might change from time to time. The text can also change depending on the locale settings of the document and the locale settings of Webi itself. Getting the key for an object if very straight forward, just use the following formula: [Object].Key

When trying to force a different aggregation level with IN/ForAll or ForEach, use IN whenever possible

The in Formula is more efficient for the BO calculation engine. ForAll or ForEach adds characteristics in the background whilst IN only uses the necessary ones.

Sometimes you will not be able to get the desired result by only using the IN formula. In that case feel free to experiment with the other two variants. Just try to use the IN formula before switching over to ForAll or ForEach.

Remove unused BO variables

Whenever a figure gets removed from the query, this will result in an error code in the formulas which used the figure. It will display something along the lines of "??DP12.DO134??" instead of the figure.

Although this formula will not work anymore, the calculation engine will still try to calculate them. Please make sure to remove or repair these formulas, do not leave them in the variables or report.

Do not use the IsError function for checking if something is dividing by zero

Using the IsError function on a formula will result in doing the calculation twice. Whilst this could be useful in some cases, it is not useful when dealing with a division by zero error.

It is better to check if the denominator is different from zero.

For example: If(A <> 0) Then (B/A)

Try to use the IF clause instead of the grouping functionality

If clauses are more efficient because they take everything into account that you define with the formula. Whilst the grouping option only takes the values of the single that have to be added manually to a group. The formula is a lot easier to maintain when values are added in the future as well.

Only merge dimensions that are absolutely necessary

Merging dimensions in Webi creates a lot of additional calculations in the background. Avoid merging when possible, also don't forget to remove merges that might not be needed anymore.

Reuse variables

Try to reuse variables in other variables. By doing this you will reduce the number of calculations Webi needs to perform.

For example:

SalesAB = [Sales A] + [Sales B]

Sales ABC= [SalesAB] + [Sales C]

This is a better option than

Sales ABC = [Sales A] + [Sales B] + [Sales C]

Do not use the function TotalNumberOfPages

Do not use this function when you have a lot of pages in your report. This function will cause the report to render all of the pages. Only use this function when your report has a small amount of pages.

Don't accidentally disable report caching

If you use one of the following functions, you disable the report caching:

• Comment()
• CurrentDate()
• CurrentTime()
• CurrentUser()
• DocumentCreationDate()
• DocumentCreationTime()
• GetDominantPreferredViewingLocale()
• GetPreferredViewingLocale()
• GetLocale()
• GetContentLocale()

Webi uses disk and memory caching to improve the performance of loading, opening and processing documents and universes. If any of the formulas from above are used, then this will disable the caching for the whole document. Please keep this in mind when these functions are being implemented.

• Blogs

  Apache Log4j Security Vulnerability impact on Java (CVE-2021-44228)

  Last week, a serious security breach was found with a Common Vulnerability Scoring rating of 10/10.
  This is something that does not happen often, so let's have a closer look on what it is and what can be done about it.

  Read more >

• Blogs

  Working From Home: Tips & Tricks (Part II)

  Due to the prevalent pandemic and subsequent measures, many employees are forced to work from home for an extended period. This situation will probably stay around for a little longer, so I wanted to share a few tips and tricks to improve your WFH situation. In the first part of this blog, I introduced a few tips to help you structure your day and your endless to-do lists in times where we are all working from home. This second part deals with some aspects of setting up your home office, virtual communications and, arguably the most important one, relaxation in these stressful times.

  Read more >

• Blogs

  Working From Home: Tips & Tricks (Part I)

  When COVID-19 came knocking onto our doors, most companies had to switch to work-from-home mode. While some of us might have already been accommodated to WFH several days a week, it does not compare to a prolonged period of full-time home office. Even though a large part of the population is vaccinated by now, WFH remains a prevalent measure in fighting the spread of Covid. It might even become a standard post-COVID-19 at your company. While many articles have already focused on all the possible benefits of working from home (better work-life balance, higher spirits, higher productivity etc), it is very likely you are becoming a bit tiresome of always staying inside your house.

  Read more >

Webi How to Select a Raw With Where Clause

Source: https://cubis.be/web-intelligence-optimizations/

Share this post