Tag Archives: Answers

NULL value in multi-select prompt

There is a NULL value in the Multi Select Prompt. When this blank value is selected with other selections from the prompt, the prompt either blanks out or the results of the report returns all values of the field instead of filtering only for the selected criteria.

Cause
The cause of the issue is to have a NULL value (it comes from the datawarehouse) as an option in the multi-select prompt because when is selected, in the textbox this symbol ” appears as the selected option and the filter does not work.

It was tested inhouse adding NULL values in the dimension and fact tables of a business model.
Solution

There are four options to resolve this issue:

1.- Validate if NULL values are relevant for the bussiness analysis (for some reason in the datawarehouse NULL values were stored). If they are not, you have to delete them in Dimensions and Fact tables.

2.- Change the physical table type in your rpd in all tables you have NULL values and use a SELECT like this:

SELECT LOGIN, RESP
FROM NQ_LOGIN_GROUP
WHERE NOT LOGIN IS NULL

Doing this, you only have NON-NULL values in your Multi-select prompt.

3.- Replace the NULL value with a new name like UNSPECIFIED in ALL tables you which are related.

4.- Log an enhancement request to review that multi-select prompts works with NULL values.

Customer decided to apply the third option, change the NULL values by a value like UNSPECIFIED. They couldn’t delete the NULL values so that was the best option for them.

Also I’ve already logged an enhancement request to review that multi-select prompts works with NULL values or display an error or warning message.

OBIEE – state of the market – rates

[ad#banner]

Good afternoon all. In this post, I wanted to give my analysis of the current state of OBIEE market in the US. There’s a regular disclaimer that these opinions of mine – and not of my employers’, clients’, or other 3d party.

First observation: Dice. I’m trying to be cautiously optimistic, but it seems as Business Intelligence market in general is picking up. I use my own Dice job index, where, I enter keyword (OBIEE) – and track it through out time. The results aren’t statistically correct, since it’s been my experience that there’re many similar positions advertised on Dice by different consulting companies – which means that in reality it’s the same position. During fall of 2007, there were very few positions (150+) . In 2008, there were stale (150-200). 2009 starting to grow (on average 250-290).  Now, I see 350+ on a regular basis (again remember -that doesn’t mean there’re actually 350 open positions, as frequently various vendors compete for the same position). Other trend I’ve noticed, most companies demand US Citizens/Green-Card holders, and many companies specifically exclude H1Bs (even through 3d parties).

Second observation:  OBIEE blogging has cooled down. That might indicate that first, people are busy on their current assignments; second, there’s plenty of business to handle, so not much incentive to be involved in self-promotion. Third, warm weather could be attributing to general blogging cool-down.

Third:  it seems as OBIEE has penetrated federal and state organizations throughout the U.S. It’s literally golden time for OBIEE consultants who are US  Citizens and who are able to obtain security clearance (usually that means  no criminal history of any kind, decent credit report/score,  references/education check). I’ve seen full-time salaries on federal projects offered at 150-180k  range + benefits, and I’m sure there’s potential for more. Hourly rates for independent consultants could also be above average. Unfortunately, for H1B consultants, it’s very difficult (if not impossible) to find a federal project which would allow foreigners. Some agencies (such as USCIS, DOD) will not even consider Green Card holders employees/consultants. Some agencies (such DOE) would allow consulting companies to place GC holders, provided they can obtain clearence. I’ve also seen a strong trend in state agencies to utilize OBIEE for reporting purposes. As US government is becoming more and more keen on disclosing various data gathered from federal agencies, OBIEE will be there to stay (foot-in-the-door-principle).

Fourth: even though the amount of positions/jobs have bounced back, the rates haven’t fully bounced back to 2005-2007 levels. However, with proper negotiation skills and market research, one can live a comfortable living. Without getting too much into details, I suggest ignoring ads that advertise their willingness to pay $50 Corp-to-corp to an OBIEE senior architect.  On the other hand, people make mistakes while creating ads, so buy beware.

Fifth: There’re a lot more full-time OBIEE jobs than ever before, mostly in three types of companies: a) large consulting companies growing their OBIEE practice b) companies that have invested heavily in OBIEE and would like to make the best use of their investment c) small consulting vendors bidding on pieces of larger projects with their small-disadvantaged-minority-owned status

I invite you to participate in the discussion. I avoided discussing hourly rates for the reason that there’re many key factors that influence rates, mainly: immigration status, location, form of contract, consulting company’s cut, etc. etc.  So that makes it difficult to weight-in. Do you think that an anonymous rate survey would be in order?

EVALUATE behavior

If you had problems using EVALUATE function –  please look at the following SR that I just found. Apparently, the work-around is to use CAST function.

QL Request: SET VARIABLE QUERY_SRC_CD=’Report’,SAW_SRC_PATH=’/users/administrator/last_working_day test 1′;SELECT “Task Type”.Type saw_0, “Task – Creation Date”.Date saw_1, EVALUATE(‘num_of_working_days(%1,%2)’,”Task – Creation Date”.Date,”Task – Last Updated Date”.Date) saw_2, “Task – Last Updated Date”.Date saw_3 FROM “SPS – Operation” ORDER BY saw_0, saw_1, saw_2, saw_3

The query fails and the following error occurs.

ERROR
———————–
Query Status: Query Failed: [nQSError: 46035] Datatype(DOUBLE PRECISION) nullable(1) unicode(1) external char(0) conversion to datatype(TIMESTAMP) nullable(1) unicode(0) external char(0) is not supported.

These columns are from W_DAY_D. These are type date which the function is expecting. The function returns a number.

BUSINESS IMPACT
———————–
The issue has the following business impact:
Due to this issue, this is holding up resolution of a critical issue on the delivery of the project – which plans to go live next Friday (30th October).

Cause

Comments
——–
The cause of this issue is that EVALUATE expects to return data with a data type that is the same as the data
type of the parameter.
This has been raised in : EVALUATE FUNCTION SHOULD RETURN DATA TYPE OF FUNCTION

The Bug explains that when you use this function:

EVALUATE(‘my_function(%1)’, logical_table.ROWID)

It will set the data type of the column to INT because ROWID is also INT. However my_function does not return an INT but a REAL.

============================
The above is what you are experiencing and why you get the error suggesting converting double precision to timestamp is not supported

[nQSError: 46035] Datatype(DOUBLE PRECISION) nullable(1) unicode(1) external char(0) conversion to datatype(TIMESTAMP) nullable(1) unicode(0) external char(0) is not supported.

Proposed Solution

Customer was asked to do an explicit CAST to set the datatype to the datatype of the value that will be returned by my_function.

So something like

CAST(EVALUATE(‘my_function(%1)’, logical_table.ROWID) AS FLOAT)

The above resolved the issue.

Creating a pivot table view with mixed granularity

Hello everyone.

I just realized that this post is inspired by Venkat.

I’m finally posting a solution to the problem I’ve identified a while ago related to a specific Pivot Table View. I think the final outcome should look something like this image:

Pivot table view

To get this result – I’ve used the “New calculated column” menu on the pivot table (circled in red):

OBIEE pivot table menu
OBIEE pivot table menu

Then, what you really need to have in the Columns is the most granular level column (sounds redundant – in this case it’s a Date column). I used Excel to quickly generate formulas, such as this for Q1.  It’s really easy to generate these formulas below. I hope it’s pretty self-explaining – you add things up in a granular level – this way you can combine things – that you wouldn’t be able to combine otherwise:

‘1/1/2007’+’1/2/2007’+’1/3/2007’+’1/4/2007’+’1/5/2007’+’1/6/2007’+’1/7/2007’+’1/8/2007’+’1/9/2007’+’1/10/2007’+’1/11/2007’+’1/12/2007’+’1/13/2007’+’1/14/2007’+’1/15/2007’+’1/16/2007’+’1/17/2007’+’1/18/2007’+’1/19/2007’+’1/20/2007’+’1/21/2007’+’1/22/2007’+’1/23/2007’+’1/24/2007’+’1/25/2007’+’1/26/2007’+’1/27/2007’+’1/28/2007’+’1/29/2007’+’1/30/2007’+’1/31/2007’+’2/1/2007’+’2/2/2007’+’2/3/2007’+’2/4/2007’+’2/5/2007’+’2/6/2007’+’2/7/2007’+’2/8/2007’+’2/9/2007’+’2/10/2007’+’2/11/2007’+’2/12/2007’+’2/13/2007’+’2/14/2007’+’2/15/2007’+’2/16/2007’+’2/17/2007’+’2/18/2007’+’2/19/2007’+’2/20/2007’+’2/21/2007’+’2/22/2007’+’2/23/2007’+’2/24/2007’+’2/25/2007’+’2/26/2007’+’2/27/2007’+’2/28/2007’+’3/1/2007’+’3/2/2007’+’3/3/2007’+’3/4/2007’+’3/5/2007’+’3/6/2007’+’3/7/2007’+’3/8/2007’+’3/9/2007’+’3/10/2007’+’3/11/2007’+’3/12/2007’+’3/13/2007’+’3/14/2007’+’3/15/2007’+’3/16/2007’+’3/17/2007’+’3/18/2007’+’3/19/2007’+’3/20/2007’+’3/21/2007’+’3/22/2007’+’3/23/2007’+’3/24/2007’+’3/25/2007’+’3/26/2007’+’3/27/2007’+’3/28/2007’+’3/29/2007’+’3/30/2007’+’3/31/2007′

Calculated item OBIEE
Calculated item OBIEE

The same logic works for Q2,3,4. For individual dates, it would be the same idea:

screen5

And for months, it’s the same logic:

screen6

To sum up  – by using the “calculated item” feature of pivot table view – you can create reports with mixed granularity on them. The pros are that you’ll be able to support legacy views (in some instances it’s absolutely critical to mimic existing view). The cons are that it involves some manual entry / data manipulation and that it’s not very flexible (I couldn’t find out how to use variables in this example).

Last thing – it seems as first column gets added on the left and the consequent ones to the right – keep this in mind while designing this view – since I’ve noticed you can’t really manipulate with the locations of the calculated columns.

Oracle’s support

I saw this post on OTN and decided to take a look:

“I need to create a prototype for the following Pivot Table. I’m putting a view as I would see in Answers.
————-|9/1/2009|9/2/2009|9/3/2009|MTD|Q1|Q2|Q3|Q4|YTD
________ |_______________________________________________
Customer |Some metric (for example Income $)

I’ve been unable to come up with a way to do this in Answers without creating additional columns (such as Income YTD, Income MTD, Income Today, Income Yesterday, etc.). Even with that, I can’t figure out what’s the best way of arranging pivot columns in Answers to create this report. Does Answers allow for mixed granularity like this in pivot?”

[ad#ad]

“The question was how to transpose various hierarchiecal levels on x-axis? Not homgenous, but different ones. For example, if I dragged columns “Year” and “Qtr” to the Columns section in Pivot I get the following:
2009
—-
q1|q2|q3|q4

What I want is to have it liks this:
2009|q1|q2|q3|q4

It seems as it’s not possible right now. But I’ll be glad if you can correct me”

Someone suggested that this wasn’t possible. Original author suggested using only tab “Measure labels” in Columns. Call appropriate metrics as Q1,Q2,Q3, etc. (use filter for them) – and use them in measures section.”

However, I thought there should be another solution. I’ve contacted metalink and was told that this wasn’t a technical, but implementation issue and as such services of Oracle Services’ were necessary.

So far, i wasn’t able to come up with anything better than using custom columns. This method isn’t ideal – since it’s very cumbersome to implement and would fail relatively easy.

Main challenge is hiding some values in Pivotal columns without affecting aggregate’s total.

I’d welcome any suggestions, since I can’t throw this out of my mind.