Business Intelligence – Oracle

Oracle BI EE 10.1.3.2/3 – Dual Table Equivalence

Posted by Venkatakrishnan J on December 31, 2007

I had received a couple of emails on how to have a table equivalent to DUAL(of Oracle Database) in BI EE. One of the major advantages of the Dual table in an Oracle Database is that you can supply some values in a select statement and the dual table will print it out for you(of course i am stating a very basic use here. There can be a variety of uses depending on how you use the dual table). Now why do we want a similar one in BI EE. Both the emails stated that in BI EE it is not possible to create a report that will print out some dummy values without including a dummy column and hiding the column. Say for example, i need a report that will just print a presentation variable or a simple dummy value like ‘Test’. If you just enter ‘Test’ or the ‘@{variable}’ in the only column that you have in the report you would get the following error while viewing the results.

      

How do we avoid this error. There are 2 approaches. One is to create a dummy column containing a presentation layer column and hide that column. This will bypass your “Query not referencing any table error”.

      

      

But both the emails suggested not wanting to use this approach. Then in that case use the 2nd approach which is to use a single column formula like the one below

CASE WHEN 1=0 THEN PRODUCTS.TOTAL_NAME ELSE ‘Test’ END

where PRODUCTS.TOTAL_NAME will be a column from the presentation layer. The trick is to reference a column within the query but which will never be used. In the above case, 1=0 will never be true and hence ‘Test’ will always be printed. In this case you dont have to include another column and hide that column. But again this is not an exact substitute to the dual table since the query never hits the dual table. Instead the same query (using the above case statement) will be fired on the PRODUCTS table since we have used one of its columns. But till we get a similar dual functionality(i dont think we ever will :-)) use any of the above 2 mentioned methods.

      

P.S: Advanced New Year Wishes to Everyone!!!

Advertisements

5 Responses to “Oracle BI EE 10.1.3.2/3 – Dual Table Equivalence”

  1. raj said

    I am new to Bi,

    We have a problem of changing the date format of current_date please can any body help us.We created a dashboard board prompt and using calendar and made sql result as default and in the sql we wrote query select current_date from table we need to change the format womething like select to_date(current_date,’dd-MM-yy’) from table when we tried this its throwing error.Please help us.
    Thank You,
    Raj

  2. eejimkos said

    i post to the oracle forum the:
    hi,

    i’ve read a lot of threads about calendar(in dashboard prompt) and timestampdiff.
    i have the following question.
    I want to have the periods of days for 2 periods of time-select by calendar in dashboard
    —>
    i created 4 prompts in dashboard prompts using in formula
    CASE WHEN 1=0 THEN “Calendar”.”date” ELSE TIMESTAMP ‘2200-01-01 00:00:00′ end(this column is timedate in oracle 10gr2)
    i changed for each the ..01-02 00:…. , an i set 4 presentations variables (dstart1,dstart2,dend1,dend2)
    Then i want to have 2 columns with the period of the days selected before.
    —>>>
    So,i created 2 columns with the following code :
    timestampdiff(sql_tsi_day,’@{dstart1}’,’@{dend1}’)
    timestampdiff(sql_tsi_day,’@{dstart2}’,’@{dend2}’)
    and i added the quantity in a pivot table to see the specified quantity for these 2 periods.

    The question…
    The function timestamp does not recognize the ‘@{dsrat1}’ as timestamp , how can i achieve it?
    I tried timestamp’@{dstart1}’ , with no result.
    I have to change the type for my column in adminstrator as ‘timestamp”?
    i have to make a new logical column with timestamp??

    Any ideas?
    tnks,for ur time…
    ////////
    can you help me??
    tnks for your time…

  3. Anders said

    Hi, I have the exact same problem. Have you found any solution to the problem?

    Thanks,
    Anders

  4. Mohan said

    Hi,

    I am also have the same problem.. If any one found a solution then post it..

  5. eejimkos said

    hi,
    if you search the oracle forum there is a solution……
    1.if you modify the javascripts of the calendar…..( i avoid it)
    or
    2.set in administrator,in connection pool–a “set nls language in the form you want”

    serch and other blogs too…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: