1 2 3 11 Previous Next

SAP BusinessObjects Web Intelligence

151 Posts

It’s not supported, but that shouldn’t stop every BI4 Administrator from benefiting from the wonderful insight it can provide. Too often customers feel they don’t understand what the Web Intelligence server is doing; some see it as a bit of a black box. No more. The webiadmintool is a sophisticated bit of kit and worth spending a good few hours to understand how it can help you.

 

Here’s a summary:

  1. The Profiling Dashboard will break down a single action, such as ‘opening a document’, into a large number of methods and for each its duration. Around 15 methods are shown for opening a document. This can help you identify which individual method is problematic: CMS communication, document cache, or generation of charts etc.
  2. The Service LifeCycle Information shows which ‘objects’ have been left in memory and yet to be cleaned up, potentially highlighting a memory issue.
  3. The Adaptive Processing Server Relationship shows, by user and document, which Web Intelligence server is using which other dependent service, like the Visualisation engine (for charts) or DSL Bridge (for the new Dimensional Semantic Layer). This means you can see inside the cluster to which user is consuming which bits of your cluster. This could be helpful to resolving a performance issue.
  4. The plugins perspective allows you to “Remove a session” or “Remove all sessions using a particular document” or “Remove all session used a particular user id” should you wish to eliminate processing that is consuming too many resources.
  5. The Profiling Perspective and Analysis enables you to compare two workflows with each other at a very detailed level. For example, you can compare the workflow of one user opening and interacting with a document, with the workflow for a different user. This might be helpful to isolate a performance issue, when one user ID performs slower than another.
  6. The monitoring perspective enables you to visually see load (sessions, users, documents, memory usage) over time in a graph. This is ideal to validate a load test; or help you identity characteristics on the usage of your server.
  7. The scheduling perspective shows you the internal tasks performed by the server, for self-maintenance and clean-up.
  8. The Failover/Recovery status will help you identify the server name, user id, or document name that is frequently failing and causing a recovery to occur. Thus allowing you to take remedial steps

 

I’ve written a detailed wiki on the tool which I hope provides enough information for how to use the tool and the benefits for using it.

Please visit and bookmark the wiki: http://wiki.scn.sap.com/wiki/x/e5hxFw

 

Your feedback is, as always, most welcome. Please comment here or contact me on Twitter.

Matthew Shaw

(Twitter: @MattShaw_on_BI)

Can the collective wisdom of crowds be a successful driver for increasing the overall effectiveness of enterprise BI tools?

The answer appears to be a resounding ‘YES’ based on the successes of SAP’s Idea Place and their BI product offerings. Let’s take a look at a few facts about how SAP is doing compared to their competitors and I'll give you a few highlights about the SAP’s Idea Place as related to my favorite BI platform – SAP BusinessObjects.

 

Did you know…

  • SAP is the worldwide leader in BI and Analytics Enterprise software
  • With revenue of over $3 Billion exclusively from BI and analytics products
  • Over 10,000 ideas submitted on the SAP Idea Place
  • A TON of crowd-sourced ideas delivered to customers around the world and built into product offerings

 

Question: In an industry with an annual revenue of over $14B, how does SAP fare against the competition?

Answer: They dominate all competitors with over a 21% market share.

 

Question: What’s the industry?

Answer: Business Intelligence and Analytics Enterprise software

 

So how do they do it? Two words – SAP BusinessObjects.

 

Let’s drill into those numbers a little and get to the heart of the $14B number. Any idea what the largest subsegment is in the BI and Analytics segment? It’s – BI Platforms with 59% or $8.5B in annual revenue.

 

So, what makes SAP BusinessObjects so great?

  • The BI Platform known as the BI Launch Pad – The perfect BI portal for delivering analytics to users
  • The Information Design Tool (IDT) and Universe Design Tool (UDT) – The Semantic Layer rules. Need I say more?
  • The Central Management Console – Security and administration at your fingertips
  • Web Intelligence – The gold standard of query, analysis & reporting tools


Get Involved

  • How can we as developers, analysts, consultants, users and customers be sure that our favorite (and the WORLD’S favorite) BI Platform is meeting our business needs?
  • How can we become active contributors to the SAP BI ecosystem?
  • How can I influence future Fix Packs, Service Packs and product releases?

Answer: That would be the SAP Idea Placehttps://ideas.sap.com

 

Let your voice be heard and contribute to SAP’s Idea Place by voting on existing ideas, commenting on ideas to add your perspective, or submit new ideas!

 

So, what exactly is on the SAP Idea Place?

Products are presented in 7 different categories with the most sessions found in the Analytics grouping with 36 different sessions.

  • Analytics
  • Business Applications
  • Cloud
  • Database Technology
  • Mobile
  • Other

 

Question: What’s the most popular product session on Idea Place with nearly 20% of all Analytic ideas?

Answer: Web Intelligence

 

Top 5 Analytic Product Sessions

  • Web Intelligence – 19.45% with 1519 ideas
  • BI Platform – 11.36% with 887 ideas
  • Crystal Reports – 8.49% with 663 ideas
  • SAP BPC for SAP NW – 7.64%  ideas
  • SAP Lumira – 4.33% with 338 ideas

 

Note: Disqualified from this list:

  • Xcelsius - 5.11% of ideas. Since SAP recently announced that SAP Dashboards would be retired soon.
  • Access Control - 5.0% of ideas. Interesting. But more governance/risk/compliance than BI.

 

The most popular area on SAP Idea Place is…. (drum roll)… Web Intelligence!

 

BI analysts, consultants, and developers alike have a ton of great ideas about Webi on the Idea Place. I also have a few submitted ideas with “a few” being... two or three… or thirty. (I hope the product developers are looking and will consider a few of them!)

 

What should I know about crowd-sourcing BI ideas

  • It takes time.
    • You’ll have to invest some time to read through all of the ideas. Vote for the ones that interest you and that you think would deliver value to your user base.
  • The ideas with the highest number of votes aren’t always the best.
    • This is simply because there are some excellent ideas that haven’t been seen by many readers yet. Comment on those ideas. Vote for them. Hopefully the product developers will give them full consideration.
  • If the ideas bring value to users, the likelihood is high that they'll be delivered.
    • I have loads of confidence in the Webi (and SAP BusinessObjects) product developers and know that all ideas will receive consideration and many will be delivered.

 

Fact check

Below are the sites I used for financial facts used in this article:

 

 

Thank you for reading. I’ll look for your ideas. Please vote for mine.

- Jim Brogden

http://twitter.com/jhbrogdenjr

This document helps you to understand Dynamic Visibility of blocks on selection of Input controls with Ranking applied....

Hope it helps...

 

Dynamic Visibility – Input Controls with Ranking.

Imagine that, you have only one Dimension and Measure in your report but you need to display measures with different ranking options. Let us consider an example, like I require dynamic selection for Top 5, Top 10, Bottom 5 and Bottom 10 sales of products where Net Value is measure and Product is dimension object. For this to achieve, follow the below mentioned steps:


Step 1: Create a Variable called Select Rank of type Dimension with value of “ ”  and click OK


Step 2: Create Input control on SELECT RANK variable. And while adding input control pass custom values (Top 5, Top 10, Bottom 5, and Bottom 10) into it and click OK.


Step 3: Create the following Variables

Rank: = Rank ([Net Value];[Product];Top)

Top 5: = [Rank] Where ([Rank] <=5)

Top 10: = [Rank] Where ([Rank] <=10)

Bottom 5: = [Rank] Where ([Rank]>=16 And [Rank] <=20)

Bottom 10: = [Rank] Where ([Rank]>=11 And [Rank] <=20)


Step 4: Add a measure variable which will return the appropriate measure, based on user selection.

Rank Selection = If ReportFilter ([Select Rank]) ="Top 5" Then [Top 5]

Else If ReportFilter ([Select Rank]) ="Top 10" Then [Top 10]

Else If ReportFilter ([Select Rank]) ="Bottom 5" Then [Bottom 5]

Else If ReportFilter ([Select Rank]) ="Bottom 10" Then [Bottom 10]

Else [Rank]


Step 5: Consider Five Blocks. Each block holds three objects Product, Net Value and Rank. Apply filter on each block along with using Hide dimension and place them relatively in same position.

Block 1: Add filter – Rank Selection – Inlist – 1,2,3,4,5

             Hide – Hide when -- =Not([Select Rank] InList ("Top 5")).

Block 2: Add filter – Rank Selection – Inlist – 1,2,3,4,5,6,7,8,9,10

             Hide – Hide when -- =Not([Select Rank] InList ("Top 10")).

Block 3: Add filter – Rank Selection – Inlist – 1,2,3,4,5

             Hide – Hide when -- =Not([Select Rank] InList ("Bottom 5")).

Block 4: Add filter – Rank Selection – Inlist – 1,2,3,4,5,6,7,8,9,10

             Hide – Hide when -- =Not([Select Rank] InList ("Bottom 10")).

Block 5: To represent all sales, so no need of having filter

             Hide – Hide when -- =Not([Select Rank] = "")

If you want graphical presentation of data, have copy of each block and turn that copied block to chart.


Step 6:  Now your report is ready to play.

Top 5:

Bottom 5:

All Sales:

Dear All,

 

For all report developers and users that currently are using Desktop Intelligence and especially the well beloved Freehand SQL (FHSQL) function, there is some good news around the corner..

 

Today, the only option to adopt Web Intelligence, while using a Freehand SQL would be to convert your Desktop Intelligence document using a (automatically) generated Universe. This universe would then contain a derived table containing your FHSQL statement from the Desktop Intelligence document. Many users are not preferring this approach as it increases the time to enhance/maintain the SQL Statement as well as increases the complexity of the solution.

 

The great news I can share is that we have received approvals for the development of the FHSQL Function within Web Intelligence. With Freehand SQL in Web Intelligence it would enable you to (finally) convert your Desktop Intelligence documents with a FHSQL Data Provider to Web Intelligence. The implementation of the FHSQL function will be staged (so do not expect all features of DeskI FHSQL straight away)..

 

 

The current planned release of FHSQL for Web Intelligence will be as in the diagram below:

FHSQL in WebI.jpg

For the initial release of the Freehand SQL Data Provider, the following features are planned

  1. Report Conversion Tool, will be enriched to convert FHSQL DeskI documents into Web Intelligence reports using the FHSQL Feature
  2. Web Intelligence Documents containing FHSQL can be refreshed within the WebI Clients
  3. A sample Extension Point will be provided to enable report developers to create New WebI documents containing an FHSQL data provider or remove a Freehand Data Provider (NO EDIT). This sample Extension point needs to be deployed manually after the installation of the BI4.1 Service Pack 05.
  4. Support of @Variables for FHSQL Data Providers

 

The Freehand Extension Point sample embedded in Web Intelligence would go into the direction as shown below

FHSQL Labs Preview.jpg

 

 

After BI4.1 SP05, FHSQL will be further enhanced with additional features to close the gap between the DeskI Freehand SQL option including an integration into the UI.

 

 

Once more details become available, I will update my blog post with the latest changes.

 

Kind Regards

Merlijn

I support Web Intelligence (WebI) for a living.

 

I talk to WebI and it talks to me.  Mind you, it doesn't talk to just anyone - especially when it's encountering issues.  You have to know how to talk to WebI, for it to give you the good stuff - like what's really the problem.

 

At times, the error message WebI gives you isn't all that informative.  For example, just today the WebI document I was working on started to throw a "Failed to connect to the OLAP source. (Error: INF)" error:
Failed_to_connect_to_the_OLAP_source.JPG

 

WebI doesn't tell you why it failed to the OLAP source, just that it couldn't.

 

There's other error messages, just as unhelpful to the end user.  For example, there's a common error where the error message states "csEX".  Just that, nothing else.  Or the dreaded "WIS 30270" error.  You search the SAP Notes/KBase for "WIS 30270" and you'll get hundreds of hits, each describing a different root cause.  So which Note/KBase is the right one, the one that describes your issue?

 

To determine the right one, you need to find the root cause error, the actual error that was thrown that led to the more-generic error message being shown the User.

 

What's tricky about this is that, with BI 4.x, the root cause error likely didn't originate within the Web Intelligence Processing Server.  With the BI 4.x architecture, for improved scalability and manageability, components of the WebI document processing are done on different processes.  For example, chart generation is done by the Visualization Service housed in the Adaptive Processing Server (APS).  SAP BW BEx query data connections are done by the DSL Bridge Service also housed in the APS.  Multi-source UNX Universe data connections utilize the Data Federator Service.

 

So let's follow an example workflow.  You open a WebI document reporting off of SAP BW BEx query in BI launchpad.  You click the refresh button. The web browser sends this event to the web application server, the AnalyticalReporting web application handles the event, forwarding the refresh event to the Web Intelligence Processing Server.

 

The Web Intelligence Processing Server then determines the WebI Session associated with the open WebI document, sees that it's a refresh request for BEx query data, so sends a 'processDPCommandsEx' remote procedure method call to a DSL Bridge Service in an APS.  The DSL Bridge makes the call to the BEx query, marshals the results and sends it back to the WebI Processing Server, that composits the results in a report page, that it sends back to AnalyticalReporting that translates the XML internal format to HTML and sends it onwards to the web browser.  Phew.

 

But in my example WebI document above, somewhere the process broke down, and an error was thrown.  But where?

 

End-to-End Tracing Saved my Life

 

That's a pretty facetious statement.  But to be frank, the End-to-End trace tool, every day, saves me so much time and effort and makes me look pretty smart with customers. Before there was an End-to-End trace tool, trying to identify root cause within hundreds of MB's of trace log files was always a daunting task.  Much of the contents of the trace log was usually irrelevant to the issue I was investigating, and much of the time spent was manually filtering out the irrelevant.

 

What the End-to-End trace tool does is automatically filter out, from the trace files, all such irrelevant information.  It allows me to trace all the action taken to service a request for a WebI Session, from web browser to WebI PS to APS and back all the way to the web browser, without being bothered by any activity done on the servers not part of the workflow.

 

Most Web Intelligence admins are familiar with this tool - likely when you contact Support, it's the first thing we ask for.  SAP KBase 1861180 is the document that describes how to download and use the End-to-End trace when working with SAP BusinessObjects BI Platform.  I highly recommend you bookmark that KBase and keep it handy.

 

In fact, open up KBase 1861180.  What I'm going to describe in this blog is how I use End-to-End traces to analyze the "Failed to connect to the OLAP source. (Error: INF)" error that I got above.

 

To effectively use this tool, you need to know a bit of how the Web Intelligence processing workflow operates.

 

Preparing the System for End-to-End Tracing

 

The first thing you have to do is go on every machine where BI 4.x is deployed and modify the BO_Trace.ini file.  You'll note that 1861180 says to set append to false and keep_num to 20, except for WebI workflows.  For WebI, set keep_num to 50.  I asked Toby, who wrote that KBase, to put that comment in just for WebI. WebI tends to be very chatty when it comes to trace log output, and I found that for more involved issues, 20 files just weren't enough.

 

So make sure you edit the BO_Trace.ini appropriately:

 

BO_Trace.ini.JPG

 

Launching the End-to-End Tracing Tool

 

From 1861180, download the SAP Client Plug-In tool appropriate for the version of Internet Explorer you have installed on your client machine.  Unfortunately, currently the tool only supports Internet Explorer, and no other brand of web browser.  What the SAP Client Plug-in does is it acts as a proxy for Internet Explorer, and on all outgoing communication, injects a HTTP Header with an unique ID value.  The presence of this ID value is detected and identified by BI launchpad.  BI launchpad will then, for every action it takes to service the request associated with the ID value, increase the Trace level.  Furthermore, it sends this unique ID to any requests it makes to backend servers.  All BusinessObjects servers, including CMS, WebI Processing Servers, Adaptive Processing Servers, the File Repository Servers, etc, will also detect the presence of this ID and raise the Trace level accordingly.

 

So say you have the BI Platform system configured for default (Error) logging.  Any request originating from Internet Explorer launched by the SAP Client Plug-In tool will automatically raise its Trace level to HIGH for any Server servicing this request.

 

Unzip the SAP Client Plug-In download file and you'll see a single executable:
SAPClientPlugin_Start.JPG

 

It's a self-extracting zip that will unzip contents and automatically launch the Plug-In:

 

SAPClientPlugin_Launch_IE.JPG

 

Make sure you've closed all other instances of Internet Explorer on your client desktop, the click the "Launch" button.  This will start up Internet Explorer, and also enable to "Next Step TraceLevel" combobox and the "Start Transaction" button.  Don't click the button just yet!

 

The thing to know is that it'll only inject the HTTP Header with the unique ID only after you click "Start Transaction".  Before then, it won't set the trace log level.  So what you can do is, before starting transaction, do all the preliminary stuff to a point just before the action you know will trigger the error.  For my example, I log onto BI launchpad and open the problematic WebI document:

 

SAPClientPlugin_Start_Transaction.JPG

 

In the above screenshot, I'm at a point just before I click the WebI Refresh button, that I know will trigger the error.  I set the TraceLevel to "High" and click "Start Transaction", then I refresh the document.

 

Important Note:  with the WebI Web Viewer, I can wait to enable transactions till just before I take action in the viewer that triggers the error.  However, if I want to End-to-End trace workflows that involve the WebI Applet Editor/Viewer (Rich Internet Client), then I have to "Start Transaction" before the Applet is launched.  That's just the way Java applets work.  The Applet reads the web browser proxy information only at startup, and not any time later.  So you have to have the transaction started before that point.  You also need to go in Control Panel -> Java -> General -> Network Settings... and ensure the Applet is configured to use browser network proxy settings.

 

Once you "Start Transaction", you need to keep an eye on the SAP Client Plug-In, to make sure it's actually recording:

 

SAPClientPlugin_Recording.JPG

 

If the "Sent Bytes" and "Received Bytes" aren't increasing as you work with the WebI Web Viewer, then the information isn't being recorded in the traces.

 

After I get the "Failed to connect to the OLAP source. (Error: INF)" error, I "Stop Transaction":SAPClientPlugin_Stop_Transaction.JPG

 

You can ignore that "Settings are not valid" dialog box.

 

Follow instructions in KBase 1861180 and go onto all your server machines and collect the updated tracelog *.glf files.  Copy them over to your client machine for analysis.

 

Also, if you look in the SAPClientPlugin folder, you'll see a log folder within which is a timestamped MyBusinessTransaction folder, that contains a BusinessTransaction.xml file.  Save this file as well:

 

BusinessTransaction.xml.JPG

 

Open the BusinessTransaction.xml file using a text editor, such as Notepad++.  What you need from this file is a copy of the unique ID that the Plug-In has been injecting into the HTTP Header, identified by the "id" attribute:BusinessTransaction_id.JPG

 

Copy this id value, because you'll be using it next.

 

 

 

Working with the GLFViewer

 

KBase 1861180 has a link to the download for the GLFViewer.  The GLFViewer is that tool we use at SAP to read and analyze *.glf files.  Download the GLFViewer and start it up.

 

Go to File -> Open -> Add Files... and select all the *.glf files that you've collected.  Don't click "OK" just yet!  If you do, you'll get the whole contents of all the trace files, and that's not what you want.  You want to filter out any trace file entry that's not associated with the session you had with the SAP Client Plug-In.

So in the Open Files dialog box, enable the "Filter and only read matching entries:", then select Column "DSRRootContextID", then enter the unique ID value that you've copied from the BusinessTransaction.xml file:

 

 

DSRRootContextID.JPG

 

and now click "OK".

 

The default configuration of GLFViewer is to not show enough columns.  Go to View -> Choose Columns... and select all columns:

 

GLFViewer_Choose_Columns.JPG

 

Another thing I like to do is select the "ActionID" column and move it up to be second in display order.

 

ActionID

 

I'm highlighting ActionID since it's an important value.  What's an ActionID?

 

In the SAP BusinessObjects BI Platform 4.x system, each and every User action that leads ot a tangible change - clicking the Refresh button, opening a tree view, clicking Export, clicking the navigation button to go to the next page - generates an unique ID that all reactions resulting from the action is associated.

 

This unique ID is separate and different from the DSRRootContextID.  For example, when I clicked the WebI refresh button, that generated an unique ActionID.  This ActionID is then sent from the web application to the backend Web Intelligence Processing Server.  Any request that the WebI Processing Server makes to any other Service - the CMS, the FRS, the APS - also is associated with this ActionID.  All responses sent back to the web app is also associated with this ActionID.

 

The ActionID is the way in which we can follow the workflow associated with the User event End-to-End, from the initial request sent from the web browser, and the final response sent to the web browser.

 

So let's say a User request results in an error response.  To trace back the error to the root cause for that error, you would trace back, in the trace files, following the trail of the ActionID back to the first error that was thrown.

 

Let's do this for my example.

 

Web Intelligence Processing Server **Error

 

So let's go to the point in the workflow where we get the error message shown to the end user.

 

Whenever the Web Intelligence Processing Server sends an user-facing error message back to the client, it does so in XML format.  In the WebI Processing Server traces, this XML message is logged prefixed by the string "**Error".

 

So to find all error messages shown to the end user that passed through the Web Intelligence Processing Server, search the "Text" column for lines that contain "**Error":

 

GLFViewer_Find.JPG

 

It's easier if you scroll down to the very last entry in the GLFViewer, and search backwards:

GLFViewer_WebI_Error.JPG

 

Now that you have this error, read the "ActionID" associated with this log entry by going to View -> Filter:

 

GLFViewer_Filter.JPG

What you've done is filtered out everything not associated with the workflow resulting in the "Failed to connect..." error.

 

Root Cause Error Identification

 

You now want to walk backwards from the **Error line thrown by the WebI Processing Server, to the actual root error.

 

It's easier to see this if you enable text indent, by going ot View -> Indent Text According to Scope. When you enable this, you'll see that the Text column becomes very colorful:

 

Root_Cause_Error.JPG

 

What the color lines define is scope.   Scope defines the depth of calls that are made internally to service a request.  For example, the WebI Processing Server invokes a request to the DSL Bridge Service.  This request is logged by a "START OUTGOING CALL" line in the WebI Processing Server trace file, that indicates that a call was made to the APS.  The APS trace file will log this request by a "START INCOMING CALL" line identifying the requester as a WebI Processing Server. The call scope has gone one deep.  When the APS returns a response to the WebI Processing Servier, it logs "END INCOMING CALL", and the WebI Processing Server logs "END OUTGOING CALL".

 

So from the "**Error" line identified earlier, follow the depth of call upwards until one encounters the first line in the trace with Trace column equal to Error.  You can see in the above screenshot that the root cause error actually originated in the APS, in the DSL Bridge Service.  The actual roor error is:

 

"Password logon no longer possible - too many failed attempts on <host>"

 

Someone had tried to log onto the SAP BW system using my account, but with the wrong password.

 

That someone was me, of course.  I had an old WebI document recurring schedule that was running, and I forgot to stop it before updating my password on my BW system.   Instant lock-up!

 

Stopping the schedule, logging onto the BW under another admin account, and unlocking the User resolved this issue.

 

Final Words

 

So you can see how End-to-End traces came in handy here.

 

You can ask yourself, "Well gee, why didn't WebI just tell the end User that the Password was invalid?"

 

That touches on one of the security considerations that went into the design of BI Platform.  If the system provides too much information concerning a logon failure, then a nefarious intruder can use that knowledge to try and hack into the system.  And that's not what anyone wants.

 

Even though this design makes troubleshooting a bit difficult, the availability of the End-to-End tool greatly mitigates the pain in getting to the root cause.

Amusing! For the last two years I have used the same plain background on the monthly report charts. However, today that all changes!

 

cgraph.png

The end results was:

 

ss.png

 

Sometimes the simple things in life can be fun!


The starting situation is as follows. I've used multiple columns (universe -> odbc -> excel) for my organizational units (main department, department, group, cost center).

For Filtering the database I've set up some command prompts as you can see in the following picture.


prompts.png


 

For a dynamic chart label showing you on which layer you are at the moment depending on what you've chosen at the prompts:

 

Right Click on the chart you want to format -> Format Chart… -> Title -> Title Label -> fill in the statement below!



="YOUR_TEXT " + UserResponse("Main Department:") + (If UserResponse("Main Department:") = "" Then "" Else (If UserResponse("Department:") <> "" Or UserResponse("Group:") <> "" Or UserResponse("CostCenter:") <> "" Then ", " Else "")) + UserResponse("Department:") + (If UserResponse("Department:") = "" Then "" Else (If UserResponse("Group:") <> "" Or UserResponse("CostCenter:") <> "" Then ", " Else "")) + UserResponse("Group:") + (If UserResponse("Group:") = "" Then "" Else (If UserResponse("CostCenter:") <> "" Then ", " Else "")) + UserResponse("CostCenter:")

 

 

You can easily port this method for every dimension you want to be shown in the chart label by using prompts for other dimensions and adapt the statement for example like this: dimension KPI with prompt text "choose kpi:" -> UserResponse("choose kpi:")

 

That's it. You now have a dynamically changing chart label depending on what you've chosen at the prompts.

Hari Dutt

Dynamic Headers in WebI

Posted by Hari Dutt Sep 15, 2014

Let me know i can show column values as Headers Names With multiple columns.

 

For example

 

 

Item    Module_Name   Qty      CalculatedQty

1         P1                     1          1

2         P2                     1          1

3         P3                     1          1

4         P4                     1          1

 

 

Actually Required

 

Item                    P1                P2             P3         P4   CalculatedQty

111
211
311
411

In my project, I did face a requirement of sorting month in charts.

Let's create a simple report to demonstrate this. For this example we will be using two objects. in report

 

1. Month Name

2. Number of incident

 

When we create the column chart  using above objects as shown below :

 

 

Month.PNG

 

Requirement : Sorting Month Name in Chart.

 

Create a variable in report.

 

Month_Number:-   If[Month]=April Then "4" ElseIf[Month]=May Then "5" ElseIf[Month]=June Then "6" ElseIf[Month]=July Then "7" Else [Month]

 

Step 1: Assign Data in chart section

               Select below values

Month.PNG

Step 2:

 

Hide 'Month Number'

Month.PNG

 

Step 3: Right click on the chart and select 'Sort' option

 

Sort -> Manage Sorts ->Add

 

Month.PNG

Add Month Number and select order  as Ascending

 

 

You can then see the chart output as shown below :

 

 

Month.PNG

 

Hope this helps

 

Regards,

Pranay

Hi Team ,

This is a trick to use BW multi language in Webi reports .

Environment : ECC6,SAP BW ,SAP BI 4.X UDT/IDT .

 

In BW Language pack deployed and Master data of objects maintained in different languages(From ECC).In BW they have option to choose language when log on , data and screen will get change as per language selected.

 

In BO , we have option to pass only one language in universe connection .By default we'll passing EN .So, though we have data maintained in multiple languages in BW ,we'll not be leveraging it for reporting in BO.

 

Possible Solution :

 

Scenario 1: Use Universe with SSO as connection type .Login to Launchpad with user and change Preferred Viewing Locale to see metadata and data in different languages .


In BI 4.1 SP3 ,we will get Codepage error .It is a bug in BI 4.1 .Please find SAP Note 2037630. It is not yet fixed either in  SAP BI 4.1 SP4 patch2(Latest Verion)  or SAP BI 4.1 SP3 Patch2 (our current version)

So we wanted to look for another solution which will work with universe.


Scenario 2 : With Manage Access Restrictions feature we can switch toggle between the connections based on user login.

Assume you have a ES_SUPPORT Group/User , to whom Spanish data required when logged in.

 

Scenario 3: Use BICS connection with SSO as connection type.Login to Launchpad with user and change Preferred Viewing Locale to see metadata and data in different languages .

Since we use Unv only in our project . we are not using this solution.


Procedure for Scenario 2:

1.Create a connection Cnn_MATERIAL_TEST on top of BEx query MATERIAL_TEST with default language EN.

2.Create universe with the connection and Create Webi report and save.You'll get only English data.

3.Create another connection Cnn_MATERIAL_TEST_ES on top of BEx query MATERIAL_TEST with language key ES for Spanish language .

4.Universe ->Tools ->Manage Security ->Manage Access Restrictions

5.Click New ,Enter Name: ES Language .From Connections drop down , select Cnn_MATERIAL_TEST_ES

6.From right hand side ,click Add user or group . Select ES_SUPPORT (user/group , for which different language data required)

7.from left side click Access Restriction ES Language , from right side click ES_SUPPORT . click on Apply.


Now log on to to Launchpad with ES_SUPPORT and refresh the report .You'll see data maintained in Spanish .

we can do same in IDT on top of cube in Data security .

 

Limitation /Considerations:

 

* Not possible with OLAP connections , universe is mandatory.

* Creating multiple connections for universe(per BEx query).

* Need to maintain user groups based on language in BO.

* Need to create Access Restrictions in Universe.

 

Hope someone find this helpful .Please share if there is another way of doing it.

Using the drillfilters() function, we can see where we are in a hierarchy when drilling is enabled. However, sometimes you may want to format report elements or data based on your whereabouts in the hierarchy. This is very easy to do and can be an effective mechanism.

 

For this example we will be using E-Fashion and the State>City>Store name hierarchy. We create a query returning just these objects:

Efashion Hierarchy Query.JPG

 

Without any arguments or parameters, the drillfilters() function will show the selections you have made to drill into your hierarchy (if you haven't yet drilled into your data, this will return a blank cell). However, we can add a Dimension parameter to the function to indicate the value clicked when drilling to the next level in the hierarchy.

 

In the E-Fashion hierarchy we are using as an example, if we drill down from the State level to the City level by clicking California, then the drillfilters([State]) function will show the value California.

 

Let's create a simple report to demonstrate this.

Efashion Hierarchy Report Design.JPG

Efashion Hierarchy Report.JPG

 

 

 

When we are at the top of our Hierarchy, we can see that no drilling has yet taken place; the drillfilters() functions are all blank. However, if we drill down a level we get:

Efashion Hierarchy Report D1.JPG

..the drillfilters([State]) function now returns a result - the state name we drilled down on.

 

Using this logic we can craft an alerter:

Efashion Hierarchy Alerter.JPG

We're going to change the colour of the text in the header cell depending upon where we are in the hierarchy. In the above example, when the drillfilters([State]) function returns a null - which is the default before we drill into the data - the text will be red. However, if we drill down to City, drillfilters([State]) is no longer blank but drillfilters([City]) is - hence the text becomes green:

Efashion Hierarchy Report D3.JPG

..and finally, if we drill down again, the text becomes blue:

Efashion Hierarchy Report D4.JPG

This is the bottom of the Hierarchy; we can't click on the Store name to drill any further, hence drillfilters([Store name]) will always return a null.

 

If you wanted, you could expand on this mechanism to create a very visual indication of where you are in the drill hierarchy:

Efashion Hierarchy Report D5.JPG

Efashion Hierarchy Report D6.JPG

Efashion Hierarchy Report D7.JPG

This last example involves a little bit more work - one alerter for each hierarchy level, to achieve the above result. See if you can figure it out for yourself!

 

I hope you find useful

The SAP Enterprise Support Academy is running a Meet the Expert Session (a webinar) entitled “BI 4.1 Web Intelligence & Java Runtime Best Practices” on 10th September 2014 at:  9.30am Eastern Time (US), 3.30pm CEST (Europe), 2.30 pm BST (UK)

 

Please join our SAP Active Global Support experts in a session designed to improve your BI 4.1 ROI and best manage your Web Intelligence environment.

 

Topics covered are the following:

  • Know how to maintain your Web Intelligence environment in regards to potential Java Run-time Issues
  • Validation of BI 4.1 Administration best practices
  • Known issues & resolutions for your Web Intelligence with Java
  • Sources of information to best plan and maintain your landscape

 

Duration 1 hour

Register at the Support Academy Web Site


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.

Actions

Filter Blog

By author:
By date:
By tag: