WIS 10901: ORA-01830 date format picture ends before converting entire input string

ORA-01830 date format picture ends before converting entire input string. (WIS 10901)

Scenarios when I could expect this error:

Convert Date field to other format.

I want to calculate a filter for rolling 12 months. TBL_DIMN_CALN_POST.D_PERD is my date field and I want to filter is for last 12 months.
In BOBJ universe, I am creating a pre-defined filter as
TBL_DIMN_CALN_POST.D_PERD between  to_date(trunc(add_months(sysdate,-12),'MM'),'dd-mm-yy') and  to_date(trunc(sysdate,'MM')-1,'dd-mm-yy')
I am parsing it is everything looks fine.

When I pull the pre-defined object to create a Webi report, it throws error
ORA-01830 date format picture ends before converting entire input string. (WIS 10901)
Possible Reason
Date format has extra data (dd-mm-yyyy HH:mi:ss). We try to convert to a valid date where the first part of the date (dd-mm-yyyy) is required not the remaining data (HH:mi:ss).

In above example, SYSDATE has a format of dd-mm-yyyy HH:mi:ss and we are trying to change it to 'dd-mm-yy'.

Possible Solution
Include the missing Date parts in conversion.

TBL_DIMN_CALN_SERV.D_PERD between  to_date(trunc(add_months(sysdate,-12),'MM'),'dd-mm-yy hh24:mi:ss') and   to_date(trunc(add_months(sysdate,-6),'MM')-1,'dd-mm-yy hh24:mi:ss')

If again, we want to truncate it to “dd-mm-yy” format, we can use to_char function for that.

**Please check List of Error Codes for a detailed list of common errors. **

WIS 30567: Error occurred while attempting to reconnect to CMS : Not a valid logon token. (FWB 00003)

Could not save the document to the repository for the following reason: [repo_proxy 30] DocumentFacade::uploadBlob - Query execute has failed : Error occurred while attempting to reconnect to CMS : Not a valid logon token. (FWB 00003) (hr=#0x80042a70) (WIS 30567)

Scenarios when I could expect this error:

    Saving the report- I am creating a webi report. Creation is fine but when I try to save the report, it throws the error.
During Schedule- When webi report server tried to save schedule output, it returns the error.

Possible Reason

Webi Server fails to connect CMS Server.  During Server Polling Interval, when webi server tried to connect CMS, it fails and CMS does not have current meta data for respective webi report server.

Possible Solution

1.       Recycle Webi server. It will reconnect WRS to CMS and then saving a report or schedule output should work fine. (Tested on BO 3.1 SP5)
2.       If step 1 does not work, you ca try to increase Request time (–requesttimeout in command line) out for CMS, Adaptive job server and Webi processing servers.

**Please check List of Error Codes for a detailed list of common errors.**

Practical usage of SELF operator in Web Intelligence

Let’s check some scenarios where we can use SELF operator with Previous function.

  • Example 1. If we want to populate a specific sequence or series
We require to start with 3 and continue the series with multiples of 3.

=3 + Previous(Self)
We can use any calculation such as =3 +1.5* Previous(Self).

  •       Example 2. Showing concatenated values
Suppose we have Country and City names (check below) and we want to show all City names concatenated.

 =[City]+", "+Previous(Self)

A slight change in arrangement- reset on Country name.

     =[City]+", "+Previous(Self;([Country]))

  • Example 3. Now, consider a business requisite
We have Sales data for USA and UK for few dates along with sales target values for some dates and we are trying to show color-codes (Alerts) if Sales is greater or less than target.

Issue here is absence of Target values for each date. Since for few dates, target value is not present, we could not compare Sales value and suggest a color-code for those dates.

Business informs that when we do not have Target for any Date, pick the last available target.

So, Target for 9/17/15 will be 75 for USA.

Here SELF operator becomes helpful to pull targets from previous values.
We are adding a updated target column with formula as:

=If IsNull([Target]) Then Previous(Self;([Country])) Else [Target]

If target is null for a specific date, it will go to previous dates and pull last available target value.
(In this case, we have sorted Date in ascending order.)

Now we we compare Sales with updated target, everything looks good as expected.

(for this example to work fine, first row should have a target value that will be pulled down.)

Let us know how you are using SELF operator.

When to use Database Ranking in Web Intelligence Report

We will discuss what is Database Ranking and how and when we can use it in Web Intelligence Report. We will also compare its advantages over report level ranking.

SAP Business Intelligence and Analytic Market Share Trend

While working as an SAP BusinessObjects developer, sometimes it strikes me to think – how the SAP BI is working in current market and what is the future scope of this tool. I know that for the enterprise environment, where I am working, it is a perfect fit for my customer requirements. But I wonder- how it is functioning in comparison with its competitors, what’s the market share, whether dominance of BOBJ is increasing further or it is on its decline. I did some research with data available from Gartner and here I am sharing my findings.

Overall performance of SAP business intelligence in terms of market share is quite consistent and encouraging for few couple of years. It has a stake of around 21%-23% from year 2008 to 2013. Please check the yearly market share graph:

 The segment has slightly declined in 2013 but with the introduction of HANA and data discovery tool-LUMIRA- and with emphatic reviews of both tools, we should expect a growth in 2014 and 2015.
(Waiting to get 2014 data to add in the trend. Stay tuned.)

If we check how SAP is doing compared to Overall BI market and other top vendors, Business Intelligence and Analytics Software Market Grew 8 Percent in 2013 whereas SAP grew by 5.3%. Even the difference was wider in 2012.
If we talk about business of BI mega vendors in 2013, the difference in market share between #1 and #2 is huge.

SAP business intelligence has wider presence and vast customer base. With the inclusion of new tools (analytics and self-service), it is trying to align with changing trend and is expected to expand customer bandwidth along with market share. With a prediction of better BI domain growth till 2018, I feel sticking with SAP BOBJ would be a wise decision. 

What do you think?

Trick | How to show Pyramid chart in Web Intelligence Reports (SAP BusinessObjects)

How beautiful it looks like if we add these Pyramid charts in Web Intelligence reporting and get a response “WOW!!!”. Let’s check how we can make the webi report more colorful and lively.

Which certification for Data Warehousing/Business Intelligence is worth and most widely accepted?

Data warehousing is an emerging field with great potential of further growth. Scope and penetration of its usage is tremendous. Almost every business domain is using and implementing business intelligence in one way or other. With increasing rate of BI deployments, Business intelligence (BI) certification has attracted the attention of associated professionals, be it a DWH developer, Business analyst, BI architect or program manager.
(Disclaimer- We are NOT debating the importance and benefits of doing certification or whether certifications are required or not for career growth.)

We are skipping WHY (certifications) part and focusing on HOW. Article talks about different available certifications in the field of Data Warehousing and Business Intelligence.
Available Data Warehousing certifications fall into two broad categories:
Vendor Neutral Vendor Specific
Vendor-agnostic certification courses tend to offer more of a focus on general procedure and best practices, rather than concentrating on the details of a certain suite of hardware and software. Objective is to equip with general knowledge of process, methodology, concepts of business metrics, data visualization, deployment skills, and techniques of data warehousing.
Few prominent certifications are:
  • Certified Business Intelligence Professional (CBIP)
  • Certification of Competency in Business Analysis (CCBA)
  • Certified Analytics Professional (CAP)
Vendor-dependent certification courses focus on respective vendor product details.
Major ones are:
  • Oracle Database 11g Data Warehousing Essentials 1Z0-515
  • IBM Certified Developer - Cognos 10 BI Data Warehouses (C2020-625)
  • MCSE: Microsoft Certified Solutions Expert (Business Intelligence)
  • SAP Certified Application Professional - Enterprise Data Warehousing with SAP NetWeaver Business Warehouse (BW 7.0)

To choose the right certification for oneself depends on many factor such as:
Job profile and role- This will be a key element in deciding the test. If I am working as an architect or program manager, in that case, will it be a good idea to go for product based certification. Professionals playing at mid-level management or higher prefer to go for general BI exams such as CBIP.
Developers or experts who want to stick with particular product line select for respective product certification that they keep current with re-certifications with latest releases. It also gives a competitive advantage during job change.
Location- Perception of certification type is different in different countries. In Europe, the vendor certification seems to be the key whereas in US BI market, it is a mixed experience.
Employer type- Stack vendor's certifications hold more weight for consultancy companies to show employees with vendor certificates. Internal employees tend to go with personal growth consideration.
My recommendation would be to analyze my current job profile and nature of responsibilities. If I am more on designing and planning, I would go for vendor neutral certification. If my job and career goal revolves more around particular product, I would apt for exams for that product. Certifications does not prove expertise but it gives a fair understanding of subject knowledge. Besides, there is no harm in doing certification.
I would like to hear your views on certification. Please share your thoughts.
The key to success is to focus our conscious mind on things we desire not things we fear.
Brian Tracy