1 2 3 16 Previous Next

SAP BusinessObjects Web Intelligence

234 Posts

In my previous blog, I shared my experience on displaying custom message in BI Launchpad login page. In comment, recently I received one request from Pallavi Deshmukh to customize error message returned by OpenDocument URL. In this blog, let me share the way I managed to get it done.

 

 

Requirement

The OpenDocument URL sometimes returns error message like below:

The above error may appear due to unavailability of the particular content in BO repository or restricted access for the user to view the content.

The requirement is to customize the text of above error message.

 

 

Assumption

I have tested the process with SAP BO BI Platform 4.1 SP5 installed in Windows server and configured with Tomcat as web application server.

Product Locale & Preferred Viewing Locale is English (en_US).

 

 

Steps

 

This customization is not supported by SAP. There is no guarantee that this approach will work in future releases of the product.

 

Here are the steps to achieve the requirement:

  1. Navigate to <BO Installation directory>\SAP BusinessObjects Enterprise XI 4.0\warfiles\webapps\BOE\WEB-INF\eclipse\plugins.
  2. Take a backup of the file com.businessobjects.webpath.OpenDocument_lang.en.jar and keep in a safe location.
  3. Extract the content of the file. You will get folders as below:

      \META-INF

          MANIFEST.MF

      \web

          \WEB-INF

                \lib

                    explorer_res_en.jar

                    OpenDocument_en.jar

                    reusable_components_res_en.jar

                    webutil_en.jar

  4. Extract the content of OpenDocument_en.jar from lib folder. You will get folder as below:

                  \META-INF

                MANIFEST.MF

          \com

                \businessobjects

                    \opendoc

                          OpenDocResources_en.properties

  5. Open OpenDocResources_en.properties file in notepad and you can see the default error messages.

  6. Change the value of the property opendoc.doc.not.found as below:

    opendoc.doc.not.found=Either the content does not exist in repository or you do not have access to view it.

  7. Save the file.
  8. Re-create OpenDocument_en.jar with the modified properties file. I copied the com folder and MANIFEST.MF file to <BO Installation Dir>\SAP BusinessObjects Enterprise XI 4.0\win64_x64\sapjvm\bin and then ran the jar.exe with the command below:

                  jar.exe cfm OpenDocument_en.jar MANIFEST.MF com

  9. Replace OpenDocument_en.jar file with the newly created one in lib folder created in step 4.
  10. Re-create com.businessobjects.webpath.OpenDocument_lang.en.jar file including new OpenDocument_en.jar. I used the command below:

                    jar.exe cfm com.businessobjects.webpath.OpenDocument_lang.en.jar MANIFEST.MF web

  11. Replace the com.businessobjects.webpath.OpenDocument_lang.en.jar file in <BO Installation directory>\SAP BusinessObjects Enterprise XI 4.0\warfiles\webapps\BOE\WEB-INF\eclipse\plugins with newly created one. Make sure we have a backup of the original.
  12. Run WDeploy tool to re-deploy BOE web service. This step will take some time.
  13. Once tomcat has finished the deployment of BOE web service and BOE folder is fully re-generated in <Tomcat Installation Dir>\work\Catalina\localhost folder, check the opendocument url again which did show the error mentioned in requirement section.
  14. The customized error text is now displayed as we configured in step 6.

Enable Memory Analysis.png

 

It was always best practice to leave the feature 'Enable Memory Analysis' turned on, as this enabled a number of mechanisms to kick in and reduce memory consumption. I described some of them in a reply to an SCN post

 

However, with Web Intelligence now sitting on a 64 bit architecture the product no longer has a 2 GB limit of memory to run within and so these mechanisms are likely to be unnecessary. This assumes you have enough RAM on your server!

 

Our development team have been busy testing the use of the product with this feature disabled. Rigorous testing identified a defect in a third party 'dll' on the Windows Platform. Non-Windows was ok. With that issue now fixed in BI 4.1 Support Pack 7 it's now time to switch our Best Practice recommendation.

 

 

From SAP BusinessObjects Business Intelligence 4.1 Support Pack 7 onward and BI 4.2 onward, we are now saying de-select 'Enable Memory Analysis'

 

If you are using an earlier version, on the Windows Platform, then you should leave this feature enabled to prevent a possible crash of the Web Intelligence Processing Server.

 

 

What's the benefit? Quite simply, performance. You should experience an improved performance as the product will not need to garbage collect as often, this sometimes 'freezes' the product.

 

Enjoy to the new power of Web Intelligence, but please just make sure you have enough RAM and monitor the memory usage of your server. If your server runs of RAM it will start to use swap and that will slow things down.

 

If you have limited RAM and you find your server is using swap, you may need to keep this enabled. Feel free to increase the Lower/Upper and Maximum Thresholds, but my advice would be to keep the 'ratios' between these values the same. Don't set them all to the same value in other words. You'll want to allow the memory recovery mechanisms to kick in one at a time.

 

Feel free to comment to this blog with your experiences of disabling this feature and provide SAP and other customers feedback.

 

Regards, Matthew (Twitter: @MattShaw_on_BI)

Maps are a hot button when it comes to Web Intelligence, which is one of the most requested features as long as I can remember. BI4.2 delivers a double whammy for BI administrators to deliver amazing geographic visualization and analysis features.


GeoMaps


New out of the box geomaps are long over-due but finally here for basic map requirements. GeoMaps transform report tables into interactive maps using common geographies. The components are the same found in Explorer and Lumira. Here is a fantastic article on SCN that explains how to use new 4.2 GeoMaps

SE10.png

 

Custom Elements

 

New to BI4.2 is custom elements, which allows BI administrators to enable a new wave of visualization options for Web Intelligence. Naturally, we immediately plugged in our CMaps Analytics JS API (which inherits Google Maps for Work), and with a few hours of experimenting we had a new custom element working.

 

web4.2cmaps.png

 

It is an exciting time as SAP modernizes it's best of breed reporting solution to take advantage of innovate "visualization as a service" offerings for BI4.2. If you want to see the above examples in action or how we built our custom element, live feel free to send me a message here or on social media and I am happy to share online.

 

We have just scratched the surface of what's possible, allowing customers to display multiple layers of information like custom regions, drive distance and radius bands, ESRI ArcGIS, and others to be officially announced shortly from CMaps Analytics.

I work on the SAP Web Intelligence (Webi) Support team.  I want to highlight one of the new features that will be introduced for WebI in SAP BusinessObjects Business Intelligence Platform 4.2 (BI4.2) called Parallel Queries. This allows for parallel query execution to be performed when refreshing WebI documents with multiple data providers.


Prior to BI4.2: Each data provider (query) in a Webi report was run sequentially (when the first query finished executing, the second would run, and so on).

 

Starting in BI4.2: Parallel query execution is enabled by default for WebI documents that have multiple data providers.

  • There is no parallel activity at the refresh preparation (i.e. query script generation, resolution of contexts, and prompts)
  • Parallel activities occur on the database at the execution and data fetch part of the refresh process.
  • Parallel queries will not be run for dependent data providers.

 

Parallel queries are supported for the following data provider types:

  • Data providers that are based on unv and unx universes.
  • Data providers based on SAP HANA Direct Access.
  • Data providers that are based on Free-hand SQL and Text Files.

 

Supported Web Intelligence clients:

  • WebI REST SDK
  • Scheduler
  • WebI Rich Client
  • WebI HTML and Applet interfaces.

 

The following screen shot shows what happens before BI4.2 and in BI4.2:

 

Query_Times_Before_And_After_42.png

 

Parallel Query Settings at the WebI level:

  • The maximum number of concurrent refresh jobs is set in the WebIContainer_ClientDescriptor.xml file for the Webi Rich Client.
  • The maximum number of concurrent refresh jobs is set in the WebIContainer_ServerDescriptor.xml file for WebI running on the server (through BI Launchpad)
  • For a default installation location on Windows, the location of the above XML files is as follows:
    • C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\config
  • The property that is set is called WebiParamMaxConcurrentRefreshJobs.
  • By default, the Maximum Concurrent Refresh Jobs is set to 64.  This can be set to a value that you find works well for your system. If the value is set to 0, then the parallel data refresh is disabled all together.

 

The following shows the WebiParamMaxConcurrentRefreshJobs parameter in the Descriptor XML file:

 

Entry_In_XML_File.png

 

The following describes the behavior at the database connection level relating to parallel queries:

  • There can be no more than four concurrent queries running on the same relational connection.
  • There can be no more than one concurrent query on the same OLAP connection or text file.
  • As of BI4.2 SP01, parallel query setting cannot be changed for each database connection. The setting currently applies to every connection.

 

A summary of the limitations in SAP BI4.2 are as follows:

  • It is not possible to run parallel queries on data providers based on “Direct Access to BEx queries” (BICS).
  • It is not possible to run parallel queries on data providers based on “Analysis Views”.
  • It is not possible to run parallel queries on data providers based on Excel files.
  • It is not possible to change parallel query setting for each database connection.  I believe that this will be looked at being incorporated into a support pack later than BI4.2 SP01.

 

Example of the log file analysis of a parallel query example:

  • In this example, a Webi report was refreshed in the Webi Rich Client.
  • The WebiParamMaxConcurrentRefreshJobs parameter was set to 64 in the WebIContainer_ClientDescriptor.xml file.
  • The webi report is refreshed with 4 data providers that all point to the same relational connection.
  • Since there are 4 queries that are all pointing to the same connection which is less than or equal to the limit of 4 parallel queries per connection and since the WebiParamMaxConcurrentRefreshJobs parameter is set to a number of 64 and no other queries are running at the same time as my test, the queries should all be run in parallel.
  • Webi Rich Client logging was enabled with “sap_trace_level = trace_debug;” when refreshing the report with the 4 queries running in parallel.
  • The .glf log file was saved.

 

Log File Analysis:


To analyze the log files, I used the FlexiLogReader.  This log reader can be used for analysis of BI .glf log files.  The FlexiLogReader can be retrieved from the following link:

Flexible Log Reader

 

Analysis Steps:


1. Open the FlexiLogReader by launching the FlexiLogReader64.exe file.

 

LaunchFlexiLogReader.png

 

2. Select the Webi Rich Client .glf log file that was created when logging was turned on when refreshing the report with 4 queries running in parallel.

 

Open_WRC_Log_File.png

 

3. Specify the default configuration (PLUGIN: BI 4.x GLF Log Parsing and End to End Analysis (v1.2.0).

 

Choose_Config.png

 

4. Specify to load all files that are in the directory of the file that you just selected. In this case, we only have one .glf log file, but if there were more, by selecting this option, all of the files in the current directory would be chosen.

 

Load_All_Files.png

 

5. When the log files are loaded, notice that one of the ways that the log files are displayed is by “ServerName/PID/TID”.  This means that the logs can be broken out by “Server Name = WebRichClient”, Process ID (In this analysis there is only one process ID), and Thread ID (there are many threads).  The advantage of this for analyzing refreshes of parallel queries is that there will be one thread that “manages” the creation of all of the refreshes, and a further thread will be spawned for each of the individual refreshes.

 

Log_Files_Opened.png

 

6. To find the lines of code that are related to the parallel refresh process, find all of the lines containing the tag [//R].  To do this in the FlexiLogReader, select “Search” – “Search Window” to bring up the search window.

 

Show_Search_Window.png

 

The search term should be added as a tag so that we can filter the logs to only see the entries that include the lines that have the [//R] entry.

 

Search_Add_To_Tags.png

 

Now ensure that you only check the Search Terms [//R] tag and don’t check any of the other ones. After that, select “Show Only Log Entries Linked To Selected Tags”.

 

Search_R_Show_Only_Selected.png

 

Now, show an extra column that refers to the “Thread ID”.

First off, find out what thread ID is associated with the first row that was returned in the [//R] search.

 

First_Row_Find_ThreadID.png

 

From the above, we can see that the thread ID that is associated with that first row is 9028.

 

Now, add the “Thread ID” column to the columns that are shown in the trace summary.  To do that, select the first row, select “View”, and select “Choose Columns To Show”.

 

View_Choose_Columns_To_Show.png

 

In the “Define Visible Columns” screen, check the “9028” column which stands for the thread ID column and then select “OK”.

 

Define_Visible_Columns.png

 

The result is that we now have “Column 3” showing which is the “Timestamp”, “Column 12” showing which is the “Thread ID”, and “Column 36” showing which is the “Text”.

 

Summary_Show_3_Columns.png


7.  By looking at the logs that are filtered by [//R], we can see that there are the following 5 different threads:

 

     9028 – This is the thread ID of the “main” thread that manages the refreshes of the 4 different data providers that are refreshing in parallel.

 

     11212 – This is the thread for the refresh of the first data provider (DP0).

 

     11208 – This is the thread for the refresh of the second data provider (DP1).

 

     11776 – This is the thread for the refresh of the third data provider (DP2).

 

     12028 – This is the thread for the refresh of the fourth data provider (DP3).

 

Summary_Highlight_All_Threads.png

 

8. In the main thread (thread ID = 9028 in this case), the following shows the start and finish of each of the queries that are being run in parallel as separate threads.

 

Main_Thread_Highlights.png

 

9. In FlexiLogReader, we can take the thread ID of one of the threads for one of the query refreshes and filter based on that thread ID.  So, for example, if we take the thread ID of the first query that is refreshed (thread ID = 11212) and filter based on that thread ID, we see the following (as shown in the below screen shot).  We will leave the [//R] search term selected so that in the summary screen, we will see the logs for thread ID = 11212 as well as the logs for the [//R] search term.  By filtering on the thread ID, you can see all of the activities that went on during the refresh process.  You can see when the thread starts, which query it is referring to, and when the thread end (when the refresh is finished for that query).

 

Thread1_Screen1.png

 

Thread1_Screen2.png

 

You can analyze the other query refreshes in the other threads using the same process as shown above.

 

Summary:

 

I hope that this blog has highlighted the Parallel Queries feature that will be available in BI4.2 including the data providers that it is supported on, how to configure it, how it should lead to quicker refresh times, and how the log files can be examined effectively using the FlexiLogReader.  It looks like this will be a great feature!

HI,

 

As per requirement we need to show alternate color in cross table rows as per below screen shot.

 

 

We can do the same by below solutions.


Solution 1: With Inbuilt option


Step 1: Write Click on Table, select Format Table then Appearance.

Step 2: In Alternate Color Option, set Frequency and Color as per requirement.



Solution 2: By Creating Conditional Formatting Rule

  1. Create Local Variable with formula “=Even(LineNumber())”.
  2. Create “Conditional Formatting Rule” on Local Variable refer below screen shot.

 

 

  3. At last apply Formatting Rule to Quantity and Value Column and get desire output.



Thanks,

Kashif Ali Khan

Hi Team ,

 

Purpose : Get cascading filters in webi report created on BICS connection  and make use of element linking to give dashboard kind of appeal for filters

 

1.Cascading filters for BEx BICS

 

We know cascading filters with BEx BICS connection is not supported in BI 4.x .

 

We can do you a work around to get this done by including .UNX into our report .

 

* Create a universe in IDT on top of the Multiprovider / Cube used for the BEx .

 

* Create navigational paths as per requirement .Use this universe in report .

 

* Merge the objects from BEx with Objects(used in Navigational path) in report and use the merged objects as filters .


Since we will be using only few dimensions from universe , it must not hamper report performance .

 

Cons:

 

Overhead of using extra data source (Universe)  .

 

Pros :

 

we can leverage Drill functionality also by using Universe .


Note : This method must be executed precisely . Don't choose this option when you have so many characteristics used in report body because it may lead to create many detail objects against Merged objects .


To get text , we need to add the masters table in UNX and join with the Cube tables.

 

2 .To get the filters in report body

 

A) Use Horizontal tables and element linking to get cascading filters in report body to get cascading kind of usage .

 

Capture.JPG

In this example , 2 horizontal tables are used . 1 for District and other for Store . Apply element linking on District , dependency should be Store table and main table .Similarly apply element linking on Store table it should affect main table .


Cons :

* If you have more items then horizontal table expands to next other pages . Use this approach when you have only few values .

* Must be careful with clearing filters applied by using element linking on tables


Or


B) If you have more items ,Use Heat map and element linking to get cascading filters in report body to get cascading kind of usage .

 

Heat map generates next row (gives kind of feel ) .It depends on height and width of table .

Capture.JPG

Create a dummy variable with value 1 to use in heat maps.


Cons :

The size of heat map is static . So it may doesn't look good after applying filters . Design Heat maps carefully .


Hope this trick make sense .


Thank you for reading

Hello All,

 

I'm going to explain how to implement the Alerts in WebI charts in the blog. This is a work around.

 

I'm going to use eFashion UNV to demonstrate this with an example.

 

1) Add a simple query with two result object Year and Sales Revenue to your data set.

2) Create Three variables like below
    Target: =10000000
    Above Target
:=If [Sales revenue]>=[Target] Then [Sales revenue]
    Below Target : If [Sales revenue]<[Target] Then [Sales revenue]

3) Insert a stacked column chart and assign data as shown below

    Category axis: [Year]

    Value Axis1 : add three variables [Target] ,[Above Target] and [Below Target]

   Then apply and click on Ok.

   AssignData.png

4) Now go to Format Chart -> Global -> Region type -> change Target Measure region type to Line (see below Image).


     Target.PNG

     You need to change column colors under palette and style -> Color Palette to Palette Hue 2 to show it in the required column colors.

 

5) Finally the Chart looks like below(Alerts are applied)

    If the sales revenue is below 10 millions it shows Red else Green and we can also see the Target line.

  

   FinalChart.png

  

That's it you're done.

If you like this post please give 5 rating.

 

Thanking You All

 

Regards,

Thiru

 


The RelativeDate function has been around a while.  It's core function is to return a date that is relative to another date.  This was historically done by using a parameter that specified the number of days you wanted to add or subtract from a date.

 

For example:

 

 

=RelativeDate(CurrentDate(); -100)

 

 

 

Would return Today's date minus 100 days.

 

Starting with BI 4.1 Support Pack 6, new functionality was added that allows you to do the same but with other date dimensions such as month, year, quarter, etc...

 

This was added to the product, but the documentation wasn't updated with all, or in some case any, of the relevant details.  This has been corrected in the BI 4.2 Formulas & Calculations guide:
http://help.sap.com/businessobject/product_guides/sbo42/en/sbo42_ffc_user_guide_en.pdf

 

But for those of you still on BI 4.1 SP6 and above, I have documented some useful information below that can get you started:

 

Syntax

The proper syntax for this function is as follows:

 

 

date RelativeDate(start_date;num;period)

 

 

In some documentation, the separator between num and period was incorrectly shown as a comma.  It is indeed a semicolon so be sure to use a semicolon for this to work:

 

example

 

 

=RelativeDate(CurrentDate()); 1; MonthPeriod)

 

 

The above formula would return the current date minus 1 month.  (Nov 11 2015) at the time of this writing.

 

Valid Values for Period

 

The documentation in BI 4.1 also omitted the values that were valid for this parameter.  Below is a list of the ones that you can use:

 

ValueDescription
MillisecondPeriodused to return a relative date based on adding/subtracting milliseconds
SecondPeriodused to return a relative date based on adding/subtracting seconds
MinutePeriodused to return a relative date based on adding/subtracting minutes
HourPeriodused to return a relative date based on adding/subtracting hours
DayPeriodused to return a relative date based on adding/subtracting days
WeekPeriodused to return a relative date based on adding/subtracting weeks
MonthPeriodused to return a relative date based on adding/subtracting months
QuarterPeriodused to return a relative date based on adding/subtracting quarters
SemesterPeriodused to return a relative date based on adding/subtracting semesters
YearPeriodused to return a relative date based on adding/subtracting years

 

Notes on using this functionality

 

These are pulled from the BI 4.2 documentation:

 

  • The num parameter can be a constant, the numerical result of a function, a measure value or a numerical dimension value, and has to be an integer.
  • The num parameter can be negative to return a date earlier than start_date.
  • If omitted, the period parameter works with days (DayPeriod).
  • When adding or subtracting months (for SemesterPeriod, QuarterPeriod and MonthPeriod), if the day does not exist in the returned month, then the last day of the returned month must be used.
  • Possible values for the period parameter are: MillisecondPeriod, SecondPeriod, MinutePeriod, HourPeriod, DayPeriod, WeekPeriod, MonthPeriod, QuarterPeriod, SemesterPeriod, YearPeriod

 

I should also mention this post: RelativeDate function enhancement, not yet by Noel Scheaffer

It covers his testing and results (down in the comments) which shows the output that he tested for each period type.

 

I have asked our documentation team to make the updates to the documentation for BI 4.1.  In the meantime, I hope this info saves you some time.

Thanks

Jb

<< I thought this was working - but NOT YET !! >> The results are very inconsistent, to the point where a space in the connection name can cause a problem.

I have older connections which resolve OK, but later one's don't. If you have correct results, please let me know.

 

Listing all Webi reports used by an OLAP connection as there is no Relationship query, is something I have been putting off for a while and I am still trying to work out / finalise. There are still a lot of "LIKE" clauses and not the actual full string.

 

The tool to use is the query builder:

http://<servername>:8080/AdminTools/querybuilder/ie.jsp

 

 

Use the query:

 

Select

    SI_ID, SI_NAME, SI_KIND, SI_OWNER

From

    CI_APPOBJECTS, CI_INFOOBJECTS

Where

SI_KIND='Webi' AND

    PARENTS("SI_NAME LIKE '%Connection%'",

            "CHILDREN('SI_NAME LIKE ''CommonConnection-%'' ','SI_NAME=''Vendor Balances'' ')")

 

The SI_NAME marked in bold is the name of your OLAP connection  (case sensitive). In my case that is "Vendor Balances".

 

If you have any further information to replace the LIKE clauses with exact strings, please post below this blog.

 

Other references:

BusinessObjects Enterprise SDK - Relationship Queries

 

BusinessObjects Query builder - Basics

Sometime we may get requirement from User to select Free text for column where we don't have 'LOV's available.

Something like in attached screen shot

e.g: Running report for "Last Day" Or "Last Week" on specific Date column

 

Prompt.JPG

 

We can't hard code for single selection as report will run only for that selection and re-usability can't be achieved.

 

Here I am going to explain how we can create custom filter condition at Universe (IDT or UDT) so that custom LOV's can be used to run report with multiple options. This filter condition can be used in report so user will get option to select period for which report should run. Below are steps.

 

I have used Oracle Database for this example, but same can be replicate with little modification with any Database.

 

Step1 : Login to IDT/UDT

Step 2: Create Filter With Name "Period Selection" (or give any Name)

 

NewFilter.jpg

 

 

Step 3: Write below formula in Filter Definition.

               In this formula you can add as many option you want based on requirement and modify formula. For this example i have used 4 options.

 

Filter Condition.JPG

 

Step 4: Validate the formula and you are ready to use in reports. You can create same Filter for all Date columns and use in report based on requirement.

            Once you refresh the report it will ask you for prompt where you can select any value from LOV's and report will run accordingly.  

 

Selection.JPG

 

You can make use this of feature to Schedule same report with different option and deliver to end user based on period.

 

E.g: You can use "Last Week" to send report on Every Monday with Last week Data.

        OR

        You can use "Last Month" to send report on Every 1st of Month with Last Month Data.  etc.

 

What you have to do is just select according option in Scheduling Parameter option and schedule report.


Regards,

Sushil Padhye

We recently upgraded our BusinessObjects environment from 4.1 SP02 Patch 2  to 4.1 SP06 Patch 2.

 

One enhancement I was looking forward to is listed on Page 56 of http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41_whats_new_en.pdf. The content at that link seems to get updated with each support pack so here is the information related to the improved RelativeDate function...

You can now have more date options with the RelativeDate() function in your calculations, including Year, Semester, Quarter, Month, Week, Day and Hour, thanks to an additional period parameter to specify the period to add.

 

Previously, you could only calculate in days and had to convert every units (week, month, year etc) in days before using the RelativeDate() function.

Unfortunately, it doesn't work. The description of the function does not match the documentation.

 

 

I thought it was very odd that the "num" and "period" parameters were separated by a comma (,) rather than a semi-colon (;). I have tried all sorts of variations using the comma as shown, using a semi-colon like everywhere else and guessing what some possible period values might be all with no luck.

 

This sort of thing troubles me and calls into question SAP's quality and testing standards and practices. There is an SAP note on this stating that documentation on this function is not correct...

 

http://service.sap.com/sap/support/notes/2237360

 

However, in my opinion this is more than just a documentation issue. I want to point out that the RelativeDate function still works if you use the RelativeDate(start_date;num_days) syntax. I hope they are able to fix this soon.

 

Noel

UPDATE - Attached is a .wid file , renamed to .txt so I coulkd attach it, with the proposed solution. Is's on BO 4.1 SP5. In order to work, rename it back to .wid

 

Hi,

There was a particularly tough question (at least for me) posted by Michal Radomski in WEBI Running Count Distinct with tooke me some time to figure out how to solve it.

 

The question was how to implement a RunningCount of distinct elements The OP had a list of IDs and dates and he needs to count the distinc elements over time (a date), his data is somethong like this :

 

dateID
01/01/20091
01/01/20092
01/01/20093
01/01/20094
01/01/20091
01/01/20095
01/01/20092
01/01/20096
02/01/20093
02/01/20097
02/01/20098
02/01/20099
02/01/20092

 

 

resulting in this :

 

 

date
Running Count Distinct
01/01/2009
6
02/01/2009
9

 

 

 

In order to do so,I proposed a solution using the following formula , which I´ll try to explain

 

[Running Count Distinct]  =RunningSum(Sum(If ( ([Id] ForEach([Id];[date])  In ([Id];[date]) = Previous([Id] ForEach ([Id];[date]) )) In ([Id];[date]);0;1) ) )

 

The idea behind this is compare , row by row, The [id] element with its previous [id] object  AS IF IT WAS ORDERED BY ID INSTEAD OF DATE , in

case it´s equal  I mark the row with 0 otherwise 1. In this manner, when I sum up the values aggregated by date I get the number of different IDs over time.

 

As the data is, in fact ordered by [date], I force the comparison to be evaluated as if was ordered by [id] and redefine the context to include [id].

This is accomplished by setting the correct order in the redefinition context ([ID];[date]).

 

In fact, when redefining context, the order in which the objects are set matters in terms of the final result.

 

Have ypu all a nice WEEK END.

 

Regards,

Rogerio

If you were fortunate enough to attend this years Mastering Business Analytics event (Melbourne, Australia), you may have seen the Web Intelligence demo either at our Icon Integration booth or during my session on Taming the BI Tools Jungle!

 

The goal was to build an interactive report that demonstrates many of Web Intelligence's key features (Input Controls, Element Linking, Quick Filters, Render as Image/HTML, Hide When.. etc). Since this was a community event, I thought it would be fitting to base our report on community data specifically, SAP's Community Network metadata.

 

Below is a snapshot and video of the end result.

WebIntelligence_SCN.png

Earlier this year, we  were involved in an SAPinsider Q & A event with the title "Will Your Web Intelligence Journey  Lead You into Lumira?" The session featured numerous SAP subject  matter experts providing live answers to questions from an online audience. This  format proved to be an excellent means of getting input and insights on key  topics from both the Web Intelligence and Lumira teams.


View the SAPinsider Questions and Answers here.

 

The unfortunate side to this very positive  SAPinsider event was that, due to time constraints, many good questions were  necessarily given short answers. We decided to follow up with some of the Q  & A participants and explore some of the questions in greater depth.

 

WebI? Lumira? Both!

 

Naturally, given the  title of the event, one of threads that ran through the Q & A was the  relationship between Web Intelligence and Lumira, and most particularly whether  Lumira would replace Web Intelligence in the long run. Ty and Frank were most  definitely in agreement here:

Question:
    There is a notion with some of our  clients that Lumira will replace WebI in future? How true is that?
   
    Frank Prabel (from the Web Intelligence Team: Not true. :-) Web Intelligence and Lumira serve different needs.  Web Intelligence addresses the reporting market while Lumira covers the agile  visualization and data discovery markets. We expect both products to continue  to play a leading role on the SAP BI roadmap.
   
    Ty Miller (from the Lumira Team): Absolutely right on, Frank. Fully agree.

The SAP BI roadmap  lays out a convergence strategy for the SAP BI platform, and as the slide below  makes clear, the key to that convergence is interoperability:

 

Jayne-Landry-BI-convergence-strategy.jpg

Where Lumira and Web  Intelligence Fit in the SAP BI Convergence Strategy

Source: "Run Simple: Convergence of the SAP  BusinessObjects BI Product Portfolio"

 

When we followed up  with Gregory Botticchio (from the Web Intelligence team), he made it clear that  Web Intelligence would remain a key part of the convergence strategy:

We no longer talk as  much about Web Intelligence, simply because we are bringing new tools to  market, and they naturally get more marketing attention, but the fact is that  Web Intelligence remains by far our leading BI tool in terms of adoption by  companies and by end users. We continue to invest in the development of Web  Intelligence.

When we followed up  with Adrian Westmoreland (from the Lumira team), he made it clear that the  future would see Web Intelligence and Lumira complementing each other more and  more:

Web Intelligence  being the Swiss army knife of BI tools, it does everything reasonably well.  Lumira will not replace Web Intelligence, but it will evolve to assume some of  the use cases that are not in the Web Intelligence reporting sweet spot. It  will simply be easier for users to adopt Lumira for these use cases than to try  to force them into Web Intelligence workflows not designed to handle them.  Lumira opens up so many possibilities for user interaction that are just not  there for Web Intelligence reports.

You can do many things  with Web Intelligence, and if all you are going to do with Lumira is to create  graphs and charts, you might just as easily use Web Intelligence to build them.  However, Lumira has data preparation, composition and layout capabilities that  go beyond what are available in Web Intelligence, allowing you to accomplish  some of the tasks you would normally perform in Web Intelligence more quickly,  more easily, and with more flexibility.

 

Web Intelligence remains  the means of presenting your data discovery, exploration and visualizations to  targeted information consumers, while Lumira increases your ability to explore  data and create compelling visualizations. So, to answer the title question  from the SAPinsider Q & A, your Web Intelligence journey may indeed lead  you into Lumira, but your Lumira journey won't lead you out of Web  Intelligence. Moving forward, some of the "sweet spots" may shift, and  the interoperability between the two solutions will be one of the primary  factors in the evolution of your data discovery, exploration, visualization and  presentation workflows.

 

For those wishing to  explore these topics on their own, the online Lumira tutorials are a good place to start.

 

In our next post: The Future of Web Intelligence and Lumira  Interoperability.

Purpose

Extract timezone from the result of built-in function CurrentTime().


Issue

Here below formula syntax doesn't work:

Right(CurrentTime();10)


Error returned is the following: "The expression or sub-expression at position N in the 'Right' function uses an invalid data type. (IES 10037)"


Solution

Work around found consists in encapsulating the CurrentTime() function in an user-defined variable and update syntax as:

Right([MyTime];10)

where:

MyTime = CurrentTime()

Actions

Filter Blog

By author:
By date:
By tag: