Friday, April 3, 2009

OBIEE Questions

This is a post where you can put all your questions which aren't related to any subject on this blog (Yet). This way we can avoid the clutering of the blog.

But before you ask, if you are in a hurry ask your question on the OTN forum first. (http://forums.oracle.com/forums/forum.jspa?forumID=378) Altough I try, I'm not always able to read the postings from this blog.

Till Next Time.

68 comments:

Dan Gerena said...

1) Can you show an example (a walkthough with screenshots) of how to use the Axis Scaling/Edit Scale Markers/Advanced Options dialog box when you want to create a trend bar graph that compares the last 12 months AVG results to yesterday's results, for instance. Specifically by using the "SQL Query" option.
2) How about some examples of when you would use the "Advanced SQL Clauses" on the Advanced tab in Answers? Specifically, how would we use the "prefix" and "postfix" options?

ss said...

1) I been trying to look into oracle HRMS rpd desing can some one get me some help on this

John Minkjan said...

@ Dan,

1) Have a look here:
http://obiee101.blogspot.com/2009/04/obiee-using-sql-based-scale-makers.html

2) The pre and post fix is used to set variabele before an after you execute a query. Have a look here:
http://obiee101.blogspot.com/2008/10/obiee-developersprompt.html

Regards

John

John Minkjan said...

@ SJ,

Can't help you there, I haven't worked with that RPD yet.

Regards

John

Adeel Javed said...

Hi John,
Thank you for creating a separate post for questions, I have a requirement where we are using Direct Database Request and want to pass a few parameters from Report A to Report B, but Direct SQL does not provide as many functions especially filters are not available. So, right now the only thing that I can imagine doing is manually setting presentation variables from JavaScript but cannot find the solution.

Right now I have added my code in RTNav i.e. when user clicks on a navigation link this gets called, I have access to all the data in the row that I want to pass, and I have somehow found about accessing prompt text fields but even setting them does not update the presentation variables because OBIEE seems to only set it when we click the GO button. So if you have any ideas, please share or point to some good resource of Direct SQL. Thanks.

--
Regards
Adeel Javed

Anonymous said...

Hi

Love the site got a OBIEE question
or request, first can we use native
drivers instead of ODBC and the second is the real question Can we
embed Crystal Reports in the Dashboard and how would you recommend doing this... I saw a section in the DOC about embedding
Microsoft Office is it the same procedure...

Karn Ganeshen said...

Thanks John.

I have OBI set up with a front end so users can access data. The users constitute of management teams & certain senior management people.

This is an out-of-box deployment. The web security assessment for the application revealed that improper query strings result in breaking the report/code & displaying the detailed error messages. Although the error messages do not show physical db details, the presentation layer metadata attributes does get on the screen.

I understand that the underlying info may be accessible using OBIEE Answers. Hence, I need your opinions & suggestions on the following concerns:

1. How could these messages be suppressed completely?
2. What attack vectors do I need to be cover in case there is no solution for error suppression?

Best Regards,
KG

John Minkjan said...

@Anonymous,

Q1: Yes you can use native drivers instead of ODBC, is even recomend.
Q2: Why do want to do that anyway? You could put an HTML link to the report. The mocrosoft integration in the docs is a VBA based connector to the BI-server. See http://obiee101.blogspot.com/search/label/MS-OFFICE

regards

John

John Minkjan said...

@KG,

"that improper query strings result in breaking". Do you allow direct SQL input? If all databases are availble and the reports have been well tested, they shouldn't lead to any of these error messages.

regards
John

Msciwoj Naciagacz said...

I'm not very familiar with HTML/CSS and wondering would it be a lot of work to do some icon customization in OBIEE.
Let's say I have three possible values (1,2,3) in val column and would like to present it as an icon only depending on the value.
Is it possible/How to do embed the appropriate gif image in the place?

Swatiii said...

Hi John,

I am trying to integrate OBIEE and SAP BW. I have imported the cubes to the physical layer. I am facing some issues in the business and presentation model.

Please guide me. How should i go ahead with the building of the presentation and business model.

John Minkjan said...

@Msciwoj Naciagacz,

Have a look at conditional formating option of the column, that should do the trick.

Regards

John

John Minkjan said...

@ Swatiii,

I have very little experiences with that. Have a look at the blog/wike of Nicolas Gerard:
http://gerardnico.com/wiki/dat/obiee/start, I know he has some good experience with that

regards

john

Unknown said...

Dear John,

Can you please tell me if there are any tools which help test OBIEE components, Data Warehouse?

Regards,
Megha

eejimkos said...

hi,
is it possible to "print" narative to pdf.
Tnks

John Minkjan said...

@eejimkos

That depends on the version of OBIEE you are using, there used to be a problem before 10.3.4.x ---

regards

John

eejimkos said...

That depends on the version of OBIEE you are using, there used to be a problem before 10.3.4.x ---

regards

John
////

tnks for the reply,John...
I use 10.3.4.1(i beleive),i searched at the oracle forum but nothing.(in the case we have a html in narative)..


tnks one more time

John Minkjan said...

@eejimkos

Why don't you log an SR on metalink? (please let us konw the answer!)

Regards

Unknown said...

Hi,

I have a question about the 'new calculated columns' in a pivot table. Apparently I am not able to make more than one calculated column because we run on an aix machine. Every column gets the same exact name, metadata said it is an AIX issue. I was wondering if there is another way to create calculated columns I want to do something like the following:
dimension column/fact
a/1
b/2
(calculated columns)
c = a+b(3)
d = a/b(1/2)(this is the main one I want. I want the percentage of one dimension column value based on another dimension's value.)

Thanks

Phil DeGuzman said...

I am struggling to understand when OBIEE will excute SQL functions on the database versus on OBIEE server. For example, I have a logical table and when I create a column and use the AVG aggregation rule, OBIEE does the avg function on SQL Server- however if I create a column with StdDev aggregation rule, then OBIE pulls all the data from SQL server and does the aggregation on the OBIEE server.

Any pointers with how to "force" OBIEE to use SQL functions on database?

Anonymous said...

Hi,
Is there any way to store the output of all the columns in the report in separate variables in OBIEE?
We want to access the output value of a column in 2 reports and use them in a 3rd report to calculate the difference between the two. Any help on this regard will be really appreciated.
Regards,
Niket

John Minkjan said...

@Patrick,

I don't have any experience on AIX.
sorry

regards

John Minkjan said...

@Phil DeGuzman

It firstly depends on the settings in the connection pool, but still then you can't always control it, OBIEE is kinda random sometimes.

regards John

John Minkjan said...

@Niket,

You can use the result of one report as a filter for the next. Maybe you can work from that.

regards

John

lax said...

Hi John,

Thanks for your postings its really helpful for developers.
could you pls give any hint ..i am running into one issue with grand totals . how to show grand totals for direct data base request queries. i am getting all the columns from simple sql and at the end of the report pages i have to disply all columns totls .like adding a new Grand Total Row at the bottom.

John Minkjan said...

@lax

Why don't you add a total row using an UNION ALL query?

regards

John

John Minkjan said...

@lax

Why don't you add a total row using an UNION ALL query?

regards

John

Luntu said...

Hi

can anyone please tell me if its possible to backup a Repository (.RPD)automatically, if can please tell me.

Regards,
Luntu

John Minkjan said...

@luntu,

have a look here:
http://obiee101.blogspot.com/2008/01/obiee-backing-up-repository.html

regards

John

Swatiii said...

Hi John,

I have a requirement where in,

A user will be logged off after some idle time and forced to log in again. This can be achieved with LogonExpireMinutes. But how do i display an alert message saying "You have been Logged Off. Kindly Log In again"?

Thanks and Regards,
Swati

Eric said...

Hi,

Can anyone help me with a metric calculation in RPD where the expression is:

$Amount/(Grand Total $Amount)

Here $Amount is per each store; and (Grand Total $Amount) is the total based on whatever set of stores the user select on Answers. I am not sure how to grab the (Grand Total $Amount) which is dynamic based on users interest.


Thanks,
Eric

John Minkjan said...

@Eric,

Play aruound with the BY statement:
SELECT "D2 Market"."M04 Region" saw_0, "D2 Market"."M01 Market" saw_1, "F1 Revenue"."1-01 Revenue (Sum All)" saw_2, SUM("F1 Revenue"."1-01 Revenue (Sum All)" by "D2 Market"."M04 Region") saw_3 FROM "Sample Sales" ORDER BY saw_0, saw_1

Regards

Jun said...

Hi, I have a question on the OBIEE Mapviewer issue we've encounter.

Have you tried to collapsed a section in a dashboard with a map with it? It seems that there's a bug or issue when we expand again the section. The bounding box was set to minimum resulting top incorrect map display... Hope you can help me. Thanks...

Unknown said...

Hi,

simple question, write back, I have read the documentation and see - "The user interface does only minimal validation of input" - i.e. numeric in a numeric field, cannot exceed field size etc... but no pick list / LOV functionality for foreign key values....

Which is my question, is there anyway to 'hang' a LOV on a write back column, to limit the user to good data - and require selection (no nulls)?

And yes, I know I could use database triggers, but error messages are not the mopst friendly of ways to deal with end users...

Thanks for your input - very impressive blog - how do you find time??!

Rich said...

Hi John, first off, thanks for all the answers I've gotten from your blog. I heard of it from another developer whose also a subscriber.

I was wondering if you or your readers have run into an accounting/financial phenomena called Trailing Twelve Months calculations (TTM) where measures (in our case: hours) are displayed as the sum of the period plus the previous 11 months. So, shown in a pivot, the period of Jan 2010, is actually a sum of the periods between Jan 2010 and Feb 2009.

With that said, I was wondering if you have done something like that in OBIEE or more specifically in the BMM?

Thank you.

Richard

John Minkjan said...

@Jun,

You could hardcode the size of the box in the init function of the map.

Regards

John Minkjan said...

@Robert,

Try filling a presentation variable with a prompt and use the variable as input for you write back.

regards

John Minkjan said...

@FakeBradAnderson / Richard,

You could do that with the ToDate function in the BMM.

Regards

John

kart said...

Hi john,

I want a table report with dynamic column names based on the prompt selection. I successfully did it using venkat's blog by creating three separate requests and joind it using "Union" operator. But my problem is the column headers appear at the first row(as expected) but when the user navigates to next page the header is not coming since it is there in the first row. Is it possible to display the headers in all the pages by any means?

Thanks,
Karthick

CHAITANYA REDDY said...

Hi All I am new to OBIEE,

I am working on Trend Analysis of ETL Mappings,
I am using AGO Function to get the Previous day Elapse Time of a mapping but while generating the report its behaving in a strange way.
like
AGO("BI Performance"."Map Val Fact"."Elapse Time Minutes","BI Performance".ETLStartDim."Day",1) -- Day Ago Elapse time
AGO("BI Performance"."Map Val Fact"."Elapse Time Minutes","BI Performance".ETLStartDim."Day",2) -- Two Day Ago Elapse time

For example, in database there is date like
Date Elapse Time
2nd June 10 min
1st June 11 min
30th may 10 min

My date in Report should be like

Date Elapse Time Day Ago Elapse Time Two Day Ago Elapse Time
2nd June 10 11 10

But the date coming over here is

Date Elapse Time Day Ago Elapse Time Two Day Ago Elapse Time
2nd June 10
3rd June 10
4th June 10

So if any one could help me out of this, it would of great help.

With Regards
Chaitanya

Unknown said...

I am trying to leverage the Web Services Interface in OBIEE to load about 1200 users into our system. (At this point I could have typed them in manually but that is no fun).
I found a few undocumented services that seem like they would work just fine.
SecurityService.createAccount() Works just fine
SecurityService.joinGroup() does not work and does not throw an exception just does nothing.

If I can get the joinGroup to work I can finish my script but it seems like literal no-one knows about this stuff.

I have the PDF "Oracle® Business Intelligence Web Services Guide
Version 10.1.3.2"

RG said...

Is there anyway to link the Data Models directly to answers? I have created new models and exported them as .html files. I just need a way to link them into answers so i could click on a link or something and the .html file will pop up with the ERD. any ideas?

Janne04 said...

Hi,

I need your help, please read below.

Dashboard will have 2 reports (Table 1 & Table 2)

When I drill into Table 1, i will be redirected to a report which is X (fact) by Y (dimension)
I have a column selector to dynamically change the dimension (Y ; Z)...
i.e. X by Y table or X by Z table

When I drill into Table 2, i will be redirected to a report which is X (fact) by Z (dimension)
I have a column selector to dynamically change the dimension (Y ; Z)...
i.e. X by Z table or X by Y table

I'm thinking of creating only 1 report for both.

How can I set a default to a column selector, such that when I drill into Table 1, column selector of the report will default to Y
and when I drill into Table 1, column selector of the same report will default to Z

Any idea?

Thanks
Janne

Rich said...

John, Im running into an addition/sub total issue that is kind of stumping me, and I want to see if you have any ideas to steer me. I created a line item report for sales in Answers. On it I placed item, price, quantity, and line total. Price and quantity are aggregates and line total is a calculation (price * quantity). The issue occurs when we subtotal by store, line total does as it should and multiplies the subtotal of price times the subtotal of quantity and I end up with a multiplication. I placed a screenshot here to illustrate this (http://bit.ly/9wW5tq). If you look at it, store 1074T shows $35 ($17.5 * $2) versus $17.5 (the sum of $15 + $2.50). Any ideas on how this is done correctly in OBIEE? Thanks.

eejimkos said...

Hello,Rich 2 things.
1,in the new column have you set any set of aggregation??
Put in in the instanceconfig.xml file and restart all the OBIEE services the tag,
true.
Does it help??

eejimkos said...

*ReportAggregateEnabled**
true
*/ReportAggregateEnabled**
where
* = <
and
** = >

Rich said...

@eejimkos: Yes, the line total has a agreggation. Its is aggregate of the sum(price) * sum(quantity). I had already tried report aggregate rule, but I still get the same result.

eejimkos said...

@rich
hi,your final view is pivot table???
1.you hava make in the creteria tab a calculated column price * quantity.ok.(NOT sum(price) * sum(quantity))
2.put in the calulated column in creteria tab (in the edit formula),aggregation rule->sum.
It does not show the sub-total correct?I have tested in a test,it works fine.

Zel said...

Hi John,

We have this issue on reports giving different set of results if the attribute used in formula is included or removed as part of request criteria.
What is causing this issue?

Regards,
Zel

John Minkjan said...

@Zel,

Did you check your log file to see where the sql might differ?

regards

Bitloo said...

John:

My question is not about this post but in general around Union, Union All. I am running into sum calculation issues.

I have a union between two subject areas. The Result is:

Month
Count 1
Count 2

Until this point everything looks good. However when I perform Count 2 / Count 3 it does not work. The result I get is from 1st set. Union does not work. Any thoughts.

Bitloo said...

John Responded.....

@bitloo

please post off subject question on OTN
http://forums.oracle.com/forums/forum.jspa?forumID=378
or
http://obiee101.blogspot.com/2009/04/obiee-questions.html

As to your question, did you add the formula on the highest level of your combined request ("Result Columns")?

regards

John

Bitloo said...

@John:

Yes, tried that it works however it aggregates the result after it divides. I want it to aggregate first and then divide.

John Minkjan said...

@bitloo

First do the aggregate:

sum (saw_1 by saw_0) / sum(saw_2 by saw_0)

regards

John

Zel said...

Hi John,

I looked at the log and the SQL issued is using partition by clause causing the wrong result. When I disabled the DB feature ROWNUM_SUPPORTED and RANK_SUPPORTED, then the SQL generated is correct but partitioning is then being done by BI so the result is still wrong.

Thanks

Zel

eejimkos said...

Hello John,
One question.
With the new release,11G,is it possible if we have -let's say- a table with 2 collumns,one is year(dimension) and other is measurement(qty),when we drill down to year,our table still shows the other years in our report?
meaning....
2009--15
2010--20
when i drill to 2010,i will show my half-years...but still the year 2009?
--->
2009 --15
2010--2010_H1--10
2010--2010_H2--10

Tnks in advance.

Dimitris.

John Minkjan said...

@Dimitris

This works great if you drag a hiearchy in a pivot table!

regards

John

eejimkos said...

hello,
tnks for your quick reply.
I have a hiearachy in time...
year-->semester...and so on.
When i drill down everything is ok.
My issue is here.
----------------------------------
2009--15
2010--20
--->
2009 --15
2010--2010_H1--10
2010--2010_H2--10
----------------------------------
when i try to dril to 2010,bi filter on that...and in the report
it returns
2010--2010_H1--10
2010--2010_H2--10
and not this
-->
2009 --15
2010--2010_H1--10
2010--2010_H2--10
,which is the desired.
It makes a kind of filter,is it possible to avoid it?and to drill in a value,but still retain the others??

tnks in adnance

eejimkos said...

John,ok..
I find it.My rpd was updated from 10.4 to 11G and it does not have the hieracly.I duplicate some of my business area and make new presentation catalog.And i saw.it works nice.

Tnks again.

Unknown said...

HI,
need one help to resolve the follwoing problem.
1> How we can add both grand total and avg of grand total column in a pivot table ?

raghavendra said...

HI John

I want to eliminate the white space between two reports which are placed vertically in a dashboard section.

Is there any way to eliminate the white space by using html code or modifying the XML...

Please help!

Bitloo said...

Hello John:

Is there a way to create vertical scale marker in OBIEE 10G. I have a graph which shows 2 years worth of data by months. I wanted to add a verticle marker to separate the years.

Is this possible.

John Minkjan said...

@Bitloo

Create a dummy column with only a value ont 1/1

Unknown said...

Hi John,

I have a requirement to display the "Total By" values and "Grand Total" in bar charts of OBIEE.

I did not find any inbuilt way to do this. I feel some workarounds needs to be carried out.

I created the calculated item in pivot table view and now I am able to view the Totals in charts.

But the totals always appear in last position of the chart. How can I bring it to the 3rd or 4th position?

Could you please help?

Thanks,
Janani

Swati said...

Hi John,

I am trying to replace the white background of the dashboard with a jazzy image. So that no whote space can be seen between two views. I have tried modifying the portalcontent.css file but no results. Could you please suggest what changes need to be done.

I am using OBIEE 11.1.1.3.

Regards,
Swati

mihai said...

Hi John,

I have an OBIEE 11 analysis which uses a filter based on results of another analysis. The problem is that in the other analysis I'm using some variables in the Prefix field from the Advanced tab and when I try to run the "master" analysis I get the error:

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27004] Unresolved table: "SET". (HY000)

So OBIEE doesn't strip the variables from the other analysis and the keyword SET is interpreted as the table name: "...IN (SELECT saw_2 FROM (SET VARIABLE...".

Do you know how to fix this?

Thanks!

PS:
I've also posted this issue here

John Minkjan said...

@mihai,

did you add a ";" after the variable?

regards

ankit dubey said...

Hello John,

I have to make a report having 2 measure values(for example Till date sales, days sales). The report is to be run for a date duration(From date and to date). The report should give us the till date sales values for the two dates(to date and from date), and show sum of the days sale for the date duration excluding the from date.
Can this be done in OBIEE other than the use of direct database request?

Awaiting reply. Thanks in advance.