1 2 3 10 Previous Next

SAP BusinessObjects Web Intelligence

138 Posts


In my previous blog I described how we can consume HANA Views using UDT(.unv) in web intelligence.

 

Consuming HANA views in Web Intelligence report

 

Here we will see how we can consume HANA views(variables and input parameters) in webi through IDT(.unx).

 

Keep in mind only JDBC middleware is supported for consuming variables and input parameters

 

At first make sure you have View created in HANA Studio and variables or input parameter over it.

 

HANA11.png

 

Once this is executed we can create a Variable over this analytical view

 

HANA12.png

This was the HANA part and after this View is executed in HANA Studio and ready to consume in BO we can now move to Information Design Tool and see how we can create connection over this HANA view and use variables and Input Parameters

 

 

HANA13.png

 

So now we are creating a relational connection using JDBC as middleware for HANA

 

You need to specify HANA server name,port number and redentials remember in order to consume variables/input parameters only JDBC is supported with webi and not ODBC.

 

Refer https://websmp207.sap-ag.de/~sapidb/011000358700000171062014E/sbo41sp3_rel_restric_en.pdf for this.

 

Once local connection is created we will now reate HANA Business Layer as below

 

HANA14.png

This will allow you to create data foundation and business layer in one go,once you have given names below will be the pane to select HANA View

 

 

HANA15.png

Once you click on OK here it will create both data foundation and business layer.In the data foudation once we click on table we can see the variable dependant on it.

 

HANA16.png

The next steps is very crucial we have created data foundation and business layer over a local connection hence inorder to publish the HANA business layer we need to first publish the connection and then point the data foundation to the published connection shortcut(.cns)

 

HANA17.png

Now the HANA Business Layer is ready to be published to repository and for reporting.

 

Now you can create webi over this HANA universe and run it successfully.

 

HANA18.png

 

Hope this blogs helps all

 

I am open for any comments or queries on this.

Hana Views can be consumed in Web Intelligence reports through a universe created from Universe Design Tool using JDBC/ODBC connectivity.

 

Once we have View created in HANA Studio in the Content section under project

 

HANA1.png

 

We can see them under _sys_BIC schema under Catalog part

HANA2.png

 

Remember this is purely a relational component of HANA and HANA Studio and it is a tabular form of View(data foundation).

 

After this is activated in HANA system and executed we can create a relational connection using Universe Design Tool through odbc/jdbc middleware.

 

HANA3.png

 

The above figure depicts the tables under _sys_BIC and all the entried are HANA View in relational form.

 

After this it is a similar procedure like we create a relational universe in UDT.

 

Always remember Web Intelligence does not support Variables and Input Parameters refer below link

 

http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp2_webi_user_guide_en.pdf

 

When the universe is created you can create a Web Intelligence report over it.

 

Hope this blog helps users who wish to consume HANA Views in Web Intelligence

Hi Team,

 

We’ll be asking for dynamic aging buckets many times at customer places. Each user want to see different aging intervals ,so forced to do multiple copies of  same BEx query with different interval for buckets so do Webi report.

 

I have got Order Number ,Invoice Number ,Aging Days (Difference between 2 dates Due date and current date)  and Outstanding Amount objects from backend (BEx query).

 

Logic behind : We’ll be having 6 buckets (Number of buckets are fixed) and will change range  of buckets dynamically with 5 input controls entered by user.

 

Process:

 

Create 5 Variables with default values which will act as Input controls.

Bucket1 : Measure variable :=15(default value , so at first we’ll get 0-15 range of bucket)

Bucket2 : Measure variable :=15(default value , so at first we’ll get 16-30 range of bucket)

Bucket3 : Measure variable :=15(default value , so at first we’ll get 31-45 range of bucket)

Bucket4 : Measure variable :=45(default value , so at first we’ll get 46-90 range of bucket)

Bucket5 : Measure variable :=90(default value , so at first we’ll get 91-180 range of bucket)

The last bucket we’ll be the >([Bucket1]+...+[Bucket5]) bucket ,which is >180 .

 

Add Bucket1..Bucket5 variables input controls with Entry Field selection .

 

Then create 4 variables to get sum of buckets to add up dynamically.

Bucket 1+2                         =[Bucket 1]+[Bucket 2]

Bucket 1+2+3                     =[Bucket 1]+[Bucket 2]+[Bucket 3]

Bucket 1+2+3+4                 =[Bucket 1]+[Bucket 2]+[Bucket 3]+[Bucket 4]

Bucket 1+2+3+4+5             =[Bucket 1]+[Bucket 2]+[Bucket 3]+[Bucket 4]+[Bucket 5]

 

Now derive values for bucket ranges .Since we are not showing/using Invoice number and Order number in the Webi table .I have included Invoice number and deal number in calculation context. You can remove them when you are working on with your requirement.


Bucket1 value  

=Sum([Outstanding Amount] Where ([Ageing Days] Between(0;[Bucket 1])) ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 2 Value

=If([Bucket 1+2+3+4+5]=[Bucket 1]) Then  Sum([Outstanding Amount] Where ([Ageing Days]>[Bucket 1])ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days] Between([Bucket 1]+1;[Bucket 1+2]))ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 3 Value

=If([Bucket 1+2+3+4+5]=[Bucket 1+2])  Then Sum([Outstanding Amount] Where ([Ageing Days]  >[Bucket 1+2]) ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days]  Between([Bucket 1+2]+1;[Bucket 1+2+3])) ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 4 Value

=If([Bucket 1+2+3+4+5]=[Bucket 1+2+3]) Then Sum([Outstanding Amount] Where ([Ageing Days] >[Bucket 1+2+3]) ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days] Between([Bucket 1+2+3]+1;[Bucket 1+2+3+4])) ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 5 Value

= If([Bucket 1+2+3+4+5]=[Bucket 1+2+3+4]) Then Sum([Outstanding Amount] Where ([Ageing Days] >[Bucket 1+2+3+4])ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days] Between([Bucket 1+2+3+4]+1;[Bucket 1+2+3+4+5]))ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 6 Value

=Sum([Outstanding Amount] Where ([Ageing Days] >([Bucket 1+2+3+4+5]))ForEach([L01 Order Number];[L01 Invoice Number]))

 

Now ,derive Buckets headers without this the report is not meaningful.


Bucket 1 Header

=0+"-"+[Bucket 1] +" Days"

Bucket 2 Header

=If([Bucket 1+2+3+4+5]=[Bucket 1]) Then "> "+[Bucket 1] +" Days" Else [Bucket 1]+1+"-"+[Bucket 1+2]+" Days"

Bucket 3 Header

=If([Bucket 1+2+3+4+5]=[Bucket 1+2]) Then  "> "+[Bucket 1+2]+" Days" Else [Bucket 1+2]+1+"-"+[Bucket 1+2+3]+" Days"

Bucket 4 Header

=If([Bucket 1+2+3+4+5]=[Bucket 1+2+3]) Then  "> "+[Bucket 1+2+3]+" Days" Else [Bucket 1+2+3]+1+"-"+[Bucket 1+2+3+4]+" Days"

Bucket 5 Header

= If([Bucket 1+2+3+4+5]=[Bucket 1+2+3+4]) Then  "> "+[Bucket 1+2+3+4]+" Days" Else  [Bucket 1+2+3+4]+1+"-"+[Bucket 1+2+3+4+5]+" Days"

Bucket 6 Header

= "> "+[Bucket 1+2+3+4+5]+" Days"

 

(This part is optional , we can leave blank columns also.)

The last part is to make the columns shrink/hide when there is 0 value specified as bucket value.

Since anyhow we’ll get Bucket1 and bucket2 value (If at least Bucket1 value is specified ) write formula to hide remaining columns and make them auto width with 0 inch .

3rd bucket column Value

=If([Bucket 3 Header]<>[Bucket 2 Header];[Bucket 3 Value])

3rd bucket column Header

=If([Bucket 3 Header]<>[Bucket 2 Header];[Bucket 3 Header])

4th  bucket column Value

=If([Bucket 4 Header]<>[Bucket 3 Header];[Bucket 4 Value])

4th bucket column Header

=If([Bucket 4 Header]<>[Bucket 3 Header];[Bucket 4 Header])

5th  bucket column Value

=If([Bucket 5 Header]<>[Bucket 4 Header];[Bucket 5 Value])

5th bucket column Header

=If([Bucket 5 Header]<>[Bucket 4 Header];[Bucket 5 Header])

6th  bucket column Value

=If([Bucket 6 Header]<>[Bucket 5 Header];[Bucket 6 Value])

6th bucket column Header

=If([Bucket 6 Header]<>[Bucket 5 Header];[Bucket 6 Header])

 

 

Caution : Don’t implement this logic if report brings more than 10k rows at view time .This can kill performance.

We can follow similar logic to give bucket range at @prompt level too. But the burden on Webi is same.

 

Thanks to Sabari Vasan. S for seed of this tip.

 

Hope somebody will find it helpful. Feel free to suggest changes in logic /other way of doing this.

In China and English.for  the people with poor reading English like me in mainland China

中英文对照,方便中国大陆英语和我一样差的人阅读。

 

 

1. Create a Variable named <input options> =""

1. 新建一个维度变量 input options=""

define1.jpg

 

 

2.Create a Input Control, on variable <input options>, and input A B C

2.新建一个控件(Input Control),关联到变量《input options》,输入 值 A B C

define2.jpg

 

 

3.Create a Variable named < Rank USER >

3.新建一个维度变量 <Rank USER>

=If [input optins]="A" Then ([凭证中的过帐日期])

ElseIf [input optins]="B" Then ([总分类账账目])

ElseIf [input optins]="C" Then ([会计凭证编号])

 

define3.jpg

 

 

4.In the Table , insert a column on the table left, input  Variable  < Rank USER >,

and  Apply an ascending(descending) Sort to the  column of Rank USER(you can hide the column )

4.在报表左边插入一列,输入数据是变量 《Rank USER》

并且根据需要做升序,降序的排序(可以隐藏此列)。

 

5.Using the input control, you can dynamically change which column is sorted.

5.点击控件选项即可实现用户自定义 的列排序

define4.jpg

 

That's it. Thanks

Hope this works for you

The goal of this blog is to update you on the enhancements delivered since SAP BusinessObjects BI4.1 to leverage the SAP HANA platform. Please refer to the SAP 4.1 documentation to have more details.

 

This blog is covering several features introduced in SAP BI 4.1 Web Intelligence and Information Design Tool that will help you leverage your HANA investment. Please note that previous recommendations with BI 4.0 on top of SAP HANA still apply.

 

  • SAP BI 4.1 Universes on top of HANA: HANA Business Layer authoring enhancement, HANA Multi-view universes
  • HANA variables and input parameters with BI 4.1
  • HANA Database Ranking with BI 4.1
  • Query stripping Relational with HANA + Auto-refresh use case.

 

 

Automatic HANA universe generation (available since BI4.1 SP2)

 

From an authoring standpoint, the Information Design Tool (IDT) has been enhanced to facilitate Universes development on top of SAP HANA. IDT is now able to generate a HANA Business Layer automatically based on HANA Views.

The default process for creating a business layer on a data foundation containing SAP HANA views takes into account the metadata as defined in the SAP HANA information model. The New Business Layer wizard automatically creates the dimensions and attributes in each SAP HANA view in a business layer folder, and creates measures with the appropriate aggregation function.

You could do this manually prior to BI4.1 SP2. Please refer to this document for more details on the manual steps: Business Case for the BI4 Semantic layer and Web Intelligence on SAP HANA

 

The SAP HANA Business Layer option is available when you create a new IDT project

HANA BL 1.png

HANA BL 2.png

HANA BL3.png

 

The New SAP HANA Business Layer wizard automatically creates a data foundation and business layer based on selected SAP HANA views. When multiple SAP HANA views are present in the data foundation, any dimensions and attributes that are common to different views are created as a single business layer object, and special aggregate-aware objects are generated to make queries on multiple views possible.


In the following screenshot, we are selecting 2 distinct Analytic Views

HANA BL4.png

The HANA Business Layer is generated with the appropriate @Aggregate_aware function. By doing this, the Universe information engine will be able to generate the most optimal query to HANA. In this scenario, the Universe is leveraging aggregate awareness to access the HANA view in the most optimal manner.

 

HANA BL5.png

In the Data Foundation, a self-join is generated randomly for each Analytic view. The reason behind is to avoid joining HANA views with each other as that could impact performances. As you can see in the below screenshot, a “dummy” filter will be generated in the SQL statement and the 2 HANA views are not joined.

HANA BL6.png

The HANA Business Layer automatic generation will also configure the aggregate navigation’s incompatible objects for you. This will make sure the end user does not query incompatible objects from the query panel.

In the below screenshot, we can see that the incompatible objects were set automatically for each table.

 

  • Table foodmart/SALES_2006 is incompatible with SALES_2007 objects

HANA BL7.png

  • Table foodmart/SALES_2007 is incompatible with SALES_2006 objects

HANA BL8.png

 

We can test the results by creating a query with a dimension “Product” and 2 measures (“Store Sales” from the SALES_2006 HANA View and “Store Sales” from the SALES_2007 HANA View.

In the below screenshot, the Universe will generate two separate queries hitting the 2 HANA Views with a full outer join performed on the client side.

 

HANA BL9.png

 

HANA BL10.png

As a result, the query will generate a result set from both SQL flows in the same table whenever possible.

HANA BL11.png

 

The goal of this functionality is to enable ad-hoc WebIntelligence reporting on top of HANA. The end user doesn’t have to know which or how many HANA views are being accessed and the Universe will generate the most optimal and performing query transparently.

 

 

HANA variables and input parameters (available since BI 4.1 SP3)

Variables and input columns defined in the SAP HANA information model are now included in the data foundation. When refreshing the data foundation, new, deleted, and updated variables in the data source are taken into account.

HANA IP1.png

 

In Web Intelligence, SAP HANA universes behave like any other relational UNX universe; HANA variables and input parameters in SAP HANA information models are associated with the corresponding tables in the data foundation.

HANA IP2.png

 

When you run a query that includes HANA variables and input parameters in the Query Panel or when a document is refreshed, prompts appear that require you to specify values for those variables and parameters. The values available in the prompts come directly from the HANA source

 

HANA IP3.png

HANA Database Ranking (available since BI4.1 SP3)

 

You can now set the ranking of data in an SAP HANA universe. Queries based on objects with universe-level ranking will take less time to fetch data. You can use HANA Analytic functions such as RANK() and PERCENT_RANK for ranking filters

 

HANA Rank1.png

 

  • SQL generation for RANK()

 

HANA rank 3.png

  • SQL generation for PERCENT_RANK()

HANA Rank 4.png

 

Query Stripping Relational (available since BI4.1 SP2)

 

Query stripping is now available for relational universes (including universes on SAP HANA) as well as OLAP universes. Query stripping is a reporting feature that can be used to optimize performance by automatically rewriting the query to retrieve only objects included in the report. It is used only by SAP BusinessObjects Web Intelligence.

The following steps will show you how to configure and enable query stripping on relational Universe (in this example a HANA Universe)

In the Business Layer Query options, check the Allow query stripping option and publish the HANA Business Layer

 

QS1.png

In the Web Intelligence query panel> Query properties, check “Enable query stripping”

QS2.png

 

In the Web Intelligence document property, check “Enable query stripping”

QS3.png

 

Create a report to test the query stripping. In the screenshot below, the dimension in bold are stripped and are not included in the SQL statement anymore unless you add them in the report canvas.

QS4.png

 

If you drag and drop a stripped dimension into the report canvas, you’ll get a #REFRESH message instead of the dimension’s data. This is normal as the dimension is not in the SQL anymore, you have to refresh the dataprovider manually by clicking “Refresh”

QS5.png

If you want to perform this action more transparently for the end user, you can enable the Automatic Refresh feature available since BI 4.1 :

In the CMC go to Application > Web Intelligence > Properties > Automatic Refresh

autoR1.png

 

In the document property, enable the “Automatic Refresh”

autoR2.png

 

Auto-refresh  works only when delegated measures are involved in the report

autoR4.png

 

If you now drag and drop a stripped dimension into the report canvas, a refresh will automatically run so you don’t have to manually click on the refresh button.

Hi Team,

 

Purpose : How to show Top N and Bottom N records controlled by a single Input control , and show Top and Bottom records in single table ordered as per their functionality  and format them with alerts .

Desired Output

In above table top 2 records are colored with green ,ordered by descending and bottom 2 records are colored with red,ordered by ascending.

 

How to do :

* Create a variable that acts as Input control for ranking.

Top =2 (Default value )

* Create another variable which distinguish Top and Bottom and filter the records in table for Top and Bottom records.

Top/Bottom =If(Rank([Prod])<=[Top] ) Then "Top" Else If( Rank([Prod];Bottom)<=[Top]) Then "Bottom"


Filter the table with Top/Bottom variable inlist Top and Bottom . By default we get only Top as value for Filter , we need to add Bottom value manually to Filter.

 

Now we get below table . Rank column is added to table.Here values are not in order.

Next to do is to order values .(If you sort on measure /Rank we get only ascending /descending , but our purpose is to have Top values in descending and bottom values in ascending).

* Create another variable for sort

Sort =If([Top/Bottom]="Bottom";Max(Rank([Prod]))In Block-Rank([Prod]) ; Rank([Prod]))

 

Add Top/Bottom and Sort tables to table .

Do descending sort on Top/Bottom  and ascending sort on Sort column.

 

Hide Sort and Top/Bottom columns ( don't delete)

Create alert to distinguish Top and Bottom records.

 

..Hope you'll enjoy this trick.

Would like to share the behavior of objects in Merged Universe when we upgraded Linked Universe from BO 5.1.5 to BI 4.1 and convert to UNX

 

 

When the linked universes in 5.1.5 is upgraded and converted to UNX, they result in a merged universe in 4.1.

 

Issue:

 

The state of the objects that were from Core Universe was not as expected in 4.1 merged UNX Universe.

 

Issue Description:

 

Noticed objects that were in hidden state in the core universe in 5.1.5 are visible in the merged universe in 4.1 UNX

which might cause issues by exposing the hidden objects when released to users without proper testing.

 

Resolution:

 

Manually need to change the state of the object in business layer so that
the object state would be the same in both 5.1.5 Universe and 4.1 UNX Universe

 

Note: Linked universes are no longer supported in Business Objects 4.x UNX.

However, linked universes can be converted as merged UNX universes i.e. conversion of derived
universe to UNX automatically includes Core Universe objects also.

 

Thanks,

Archana

Welcome !!

 

This is a simple & quick how to procedure which explains about how to make an Excel as a source to WebI. Thanks if you found this interesting and utile.

 

Note: This is a special feature available from BO Version 4.1 (SP2) onwards.

 

I've tried to post this maximum with the screenshots hope we all love more of a screenshots rather composing big paragraphs. This process is not a big deal still will hopefully help fresh guys.

 

Here is how we can upload an excel file and make it as a source for WebI report.

Untitled.jpg

Choose Local Document, and choose the destination folder to upload an excel file.

Untitled1.jpg


Choose file from your computer as highlighted.

Untitled2.jpg


Here you can choose the file you want to upload and make your WebI Report

Untitled3.jpg


Click Add to upload the file.

Untitled4.jpg


It will be uploaded as highlighted here.

Untitled5.jpg


Now, it is time to create a WebI Report based on uploaded file.

Untitled6.jpg


Select new report.

Untitled7.jpg

Choose Excel as a Data Source.

Untitled8.jpg


Choose the destination folder on which the excel was uploaded and saved.

Untitled9.jpg


Here you can do settings for the excel file.

Untitled10.jpg

Now the WebI will show all the dimensions and measure as default.

Untitled11.jpg


You can choose and change a dimension into a measure as per requirement.

Untitled12.jpg


Here you can even select type of the object(String/Date/Number).

Untitled13.jpg


After doing all necessary steps you can run query to get result as intended.

Untitled14.jpg


You can also save this as WebI query as highlighted.

Untitled15.jpg


That's it. Thanks

Hi

 

This blog will help you to remove the underline from hyperlink and Document and change the visited and unvisited Hyperlink color.

 

 

1st we will learn how to remove the underline from the Hyperlink or document link 

 

Step 1: Create a hyperlink on a column.

 

 

 

Step 2:  The below image I had created a hyperlink on a NAME column.

 

Step 3: After creating the hyperlink we will see a above image.(i.e hyperlink with underline)

 

Step 4:

 
 

 

 

We can see the formula bar; The new formula has been display.

For  eg we had created a hyperlink for a www.google.com

The formula bar will contain the formula as

 

="<a href=\"www.goggle.com\" title=\"\" target=\"_blank\" nav=\"web\" >"+[Name]+"</a>"

Step 5:

After that we have to edit the above formula

="<a href=\"www.goggle.com\" title=\"\" target=\"_blank\" nav=\"web\" style=\"text-decoration:none!important\">"+[Name]+"</a>"

We have to add the below text to the hyperlink

=style=\"text-decoration:none!important\"

Step6 :

 

After that right click on the name column and select the format cell

 

 

 

Select the “READ CONTENT AS HTML”.

 

 

Step 7: After selecting the Display as Read Content as HTML.

 

 

Note

After this step we have to save our  Webi  to BI LAUNCH PAD

 

 

Step 8:After saving the webi in bi launch pad we will see the below output ie hyperlink without under line

 

 

 

After that we will change the hyperlink visited and unvisited color of hyperlink

 

Step 1: Right click on a report name

 

 

 

And select the Format Report.

Step  2:

 

Select the Appearance we can see the hyperlink color.

 

 

Finally save the webi in BI launch pad and see the changes.

Summary

WHAT: Pin the latest instance of a Web Intelligence document.

 

WHY: Users may have a Web Intelligence document that they navigate to on a recurring basis as they consume the latest scheduled output. Pinning the latest instance of the document saves the user from having to navigate as the instance will appear upon logon.

 

 

Instructions

1. Right click on the Web Intelligence document and click on "Document Link".

hypA1.png

 

2. Copy the hyperlink.

hypA2.png

 

3. Create a "Hyperlink" object by clicking New > Hyperlink. Note: You will need write access to a folder (e.g. My Documents).

hyp01.png


4. Populate properties Title and URL and click "OK".

    Important! URL = Paste document link AND append &sInstance=Last

hyp02.png

hyp03.png

 

5. Double click the object (or right click and select "View").

hyp04.png

6. Click the pin icon to pin the tab.

hyp5.png

 

That's it! You have successfully pinned the latest instance of a Web Intelligence document.

Scenario

Visualise [Sales Revenue] by [Quarter] for the [Last 3 Years].

 

Example

The data visualisation below is an example of what is produced by Web Intelligence out of the box (i.e. default).

DataViz01.jpg

What is wrong with this visualisation?

  • Misuse of Colour - When viewed at a glance, a user may believe Blue is good and Orange is bad? Our eyes need to constantly flick between the legend and the bars to understand which colour relates to which year. The default colour choice does not provide much value.

 

  • X & Y Axis Titles - Do we need them? Would a heading (e.g. Sales Revenue by Quarter: 2004 - 2006) not provide sufficient context? When we see values such as Q1, Q2, Q3 the user understands these are Quarters.


  • Gridlines & Value Axis - Are gridlines and the Y-axis adding any value? We can't tell the actual value of each bar regardless of their presence.

 

  • Whitespace - There is a large amount of whitespace above the bars and around the legend. Could we make better use of that space to emphasise the shapes of the bars?

 

Here is an example using the same data but adhering to some data visualisation best practices using standard Web Intelligence functionality.

DataViz02.jpeg

  1. Remove titles from the X and Y axis. The combination of a descriptive heading (e.g. Sales Revenue by Quarter: 2004 - 2006) and actual values (e.g. 2004, 2005 & Q1, Q2, etc) provide sufficient context.
  2. Remove background grid lines, this will increase our Data-Ink ratio.
  3. Change the colour palette to monochrome (available in Web Intelligence 4.0), this fading of blue from light to dark enables the user to draw a relationship between lighter being further in the past and darker being closer to the present.
  4. Change the position of the legend to the top to minimise white space.
  5. Change the x-axis line to a less jarring colour to emphasise the bars (e.g. Grey - 25%).
  6. Change the font colour to a less jarring colour to further emphasise the bars (e.g. Grey - 40%)
  7. Force a custom MAX for the value axis so that the highest bar is equal to the MAX Y-axis value (reducing white space further).

 

Note: If the values of the individual bars are deemed critical to the story, we can turn them on but rather than leaving the default format, scale the number to a level of precision that enables the user to consume the information as quickly as possible without losing relevancy.

DataViz03.png

 

Colour Palette - Web Intelligence 4.0 vs. 4.1

Something to be mindful of, Web Intelligence 4.0 is restricted to a fixed number of palettes.

webi40.jpg

 

Web Intelligence 4.1 introduces a Custom colour palette which can provide users more flexibility. Highly recommend reading Stephen Few's - Uses and Misuses of Color for further learning and understanding on how to use colour appropriately and when it could be misused.

webi41.png

Issue Description

 

The following error message (or very similar) appears when you attempt to refresh a SAP BusinessObjects report built off a SAP BW BEx query using BICS.

 

Failed to execute query: ‘java.lang.NumberFormatException: For input string: “2000,0000”’.


The input string will differ based on the environment, but the key to this is that the format is incorrect.  In the above example, the input string value is 2000,0000.  The thousands separator is in the incorrect location, thus throwing the java.lang.NumberFormatException.  I’ve included screenshots of what the error looks like in both Crystal Reports and Web Intelligence.


Crystal Reports

ForInputString Crystal.png


Web Intelligence

ForInputString WebI.png

Web Intelligence throws an error code of WIS 00000, which is pretty generic.

 

What is Causing This?

This has nothing to do with default values or formulas in the report, so you can stop looking there.  The input string (in our example 2000,0000) is coming from the SAP BW Safety Belt setup. If you are not familiar with the SAP BW Safety Belt, read about it here.  The value 2000,0000 is read from the SAP BW Safety Belt objects stored in the table RSADMIN.  Below is a screenshot from RSADMIN showing the objects and the values.

 

 

BW Table RSADMIN

BW RSADMIN.png

 

Resolution

To resolve this issue, the Safety Belt objects should be corrected to specify the correct, intended values.  KBA1773823 explains how to make the change.

 

More Information

1773823 - How to resolve error message 'For input string: "500,0000" (WIS 00000)' in BI 4.0 Web Intelligence reporting off of SAP BEx query?

1127156 - Safety belt: Result set is too large

SAP BusinessObjects Increasing Stability by Setting Limits on Max. Retrievable Cells from SAP BW into Web Intelligence using BICS

Hi All,

 

Environment : BI 4.1 .

Reporting Database : BW 7.4 SP3.


Recently we migrated to BI 4.1 SP3 from BI 4.0 SP6.

From then reports in BI 4.1 launchpad was taking so much time compared to BI 4.0 and BI 4.1 Rich Client.

There was drastic change in performance .A report which is running in 30 sec in BI 4.1 Rich client and BI 4.0 SP6 was taking >10 minutes in BI 4.1 launchpad.

Below tasks helped us to achieve the performance again.
1.Disabling logs:

-Go to CMC,navigate to the servers and then webi processing server.

-Right click and go to properties.

-Set log level as "None"

-Do the same for all webi processing server.

-Same step you have to follow for rest of the servers.

-For only CMS keep log level as "Unspecified".


2.Delete logs:

-Go to Installing Directory of BusinessObjetcs\SAP BusinessObjects\SAPBusinessObjects Enterprise XI 4.0\logging directory.

-Delete all unwanted logs(.glf files).


3.Clear Cache:

-Go to Installing Directory of BusinessObjetcs\SAP BusinessObjects\SAPBusinessObjects Enterprise XI 4.0\Data directory.

-Take back up of all the content inside it and delete everything.


4.Clear Tomcat cache:

-Stop Tomcat.

-Go to Installing Directory of BusinessObjetcs\SAPBusinessObjects\Tomcat\work\Catalina\localhost.

-Take back up of all the content inside it and delete everything.

-Start Tomcat.


After performing all these steps, Restart SIA.

In Addition to above , we can consider below points also for better performance.

A. If source to webi is .UNV

- Change the connection pool mode to 'Disconnect after each transaction'.

- Increase the Array fetch size to 1000.

- In Universe Parameters, click on the Parameters tab and change the setting DISABLE_ARRAY_FETCH_SIZE_OPTIMIZATION from the default of "No", to "Yes" and click the "Replace" button, then Ok to save the

changes.

 

B.Disable Connection Server Trace

Modify cs.cfg In Windows this is located in the following

directory C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer Set the

following parameter to this value <Traces Active="No">

 

C.Follow the SAP NOTE:1930558 -

How to utilize the 64-bit SAP BAPI driver with UNV universes in BI 4.x (Windows) .


Thanks to Sabari Vasan.S for his valuable sharing

Please post if you have any other points to consider for better performance.


 

 



This document describes about how to create Publication with Dynamic Recipients in brief.


Hi I recently worked out how to Join Data from two queries in a single report

 

Create a WEBI document  with 2 queries where 1 reads data from the other
in my example I have 2 queries 1 is Rels and the other is Related Acc the Related Acc query is reading the account name for accounts that are the Rel Business Partner ID from the Rels Query
join1.png
The result is two tables 1 of the accounts with the related accounts and the other of the related accounts IDs with their names. I want to join the second table to the first to provide name details fro the Rel Business Partner ID in the First table
Join2.png
The first step is to define the Merge criteria which is the Rel-Business Partner ID from the Rels query and the Business Partner ID for Filtering from the Related Acc query use Ctrl click to select these two dimensions then right click and select Merge
join3.png
Next right click on the Merged dimension and select Edit Properties
join4.png
Then change the name of the Merged dimension to make it obvious which is which
join5.png
result is shown below
join6.png
Next create a variable for the Organization name from the Related Acc query.
The important elements are to make the Qualification of the variable a detail not a  dimension. When this detail variable type is used it allows you to specify what the associated dimension is in this case it is the MErged dimension Business Partner ID M.  The Formula just reads the value of the field from the Related Acc query
join7.png
finally include the new variable in the first table and the names will be displayed correctly
join8.png

 

 

I hope this helps other people I have been wondering how to do this for ages. So simple in SQL but quite complex in WEBI.

 

Regards

 

Louis

Actions

Filter Blog

By author:
By date:
By tag: