Sometimes you have to contact oracle support (https://support.oracle.com/CSP/ui/flash.html) from a machine where no flash player is / can be installed. You probably get stuck in the login screen. Try this address instead: https://supporthtml.oracle.com/ep/faces/index.jspx
Till Next Time
Mark Rittman triggered meto investigate this: If you are Filtering against a date column in OBIEE you will see in the log that a timestamp conversion is added to your query:
select distinct T2826.DAYTIME as c1
from
DAYTIME2 T2826
where ( T2826.DAYTIME = TIMESTAMP '1968-03-14 00:00:00' )
order by c1
This timestamp conversion implies that a index on the DATE column is skipped, resulting in a full tablescan. How can we force the usage of an index? Here is an alternative:
1: Create a FUNCTIONAL INDEX on your date column Fi:
create index dt3 on daytime2(cast ("DAYTIME" as timestamp));
2: Create a view on your table casting the date column as TIMESTAMP:
create view v_daytime2
as
(select cast(daytime as timestamp) as daytime from daytime2)
Map this view to your RPD and you will, check the explain plan, see that Oracle uses your index.
If you have any better / other solutions please let me know!
Till Next Time
If you look in the configuration guide you will find that the CASE_SENSITIVE_CHARACTER_COMPARISON parameter in the NQConfig file controls the case sensitive search within OBIEE. Be aware that you might be fooled by your database settings. If you are on an Oracle database 10G+ you can use a connection script in the repository to allow case insensitive searches. Go to the connection pool, click on the connection script tab.
Press New:
Enter:
alter session set NLS_SORT=BINARY_CI
alter session set NLS_COMP=LINGUISTIC
Save the repository, reboot the BI-Server
Till Next Time
BEGIN
FOR i IN (SELECT 'CREATE INDEX IDX_AGGR' SUBSTR ('0000'
TO_CHAR (ROWNUM), -5) ' ON AGGR.' table_name ' (' column_name ') NOLOGGING NOPARALLEL' AS sql_str
FROM all_tab_cols
WHERE owner = 'AGGR'
AND (table_name LIKE 'AG%' OR table_name LIKE 'SA%'))
LOOP
execute immediate (i.sql_str);
END LOOP;
END;
Till Next Time
This article was original written for the Ciber Knowledge Blog:
http://knowledge.ciber.nl/weblog/?p=127
Up to 9i you had to use Tom Kyte's Stragg function to get the children of the level to a single string. From 10GR1 you can use the collect function.
I wrote an entry on my ORACLE101 blog (http://oracle101.blogspot.com/2008/08/oracle-collect-function.html) which can be used in the same way you use the STRAGG function in OBIEE (see: http://obiee101.blogspot.com/2008/08/obiee-children-of-level-converting-rows.html)
Till next time
create or replace type stragg_type as object
(
string varchar2(4000),
static function ODCIAggregateInitialize
( sctx in out stragg_type )
return number ,
member function ODCIAggregateIterate
( self in
out stragg_type ,
value in varchar2
) return number ,
member
function ODCIAggregateTerminate
( self in stragg_type,
returnvalue out
varchar2,
flags in number
) return number ,
member function
ODCIAggregateMerge
( self in out stragg_type,
ctx2 in stragg_type
)
return number
);
/
create or replace type body stragg_type
is
static
function ODCIAggregateInitialize
( sctx in out stragg_type )
return
number
is
begin
sctx := stragg_type( null ) ;
return
ODCIConst.Success ;
end;
member function ODCIAggregateIterate
( self in out stragg_type ,
value in varchar2
) return number
is
begin
self.string := self.string ',' value ;
return
ODCIConst.Success;
end;
member function ODCIAggregateTerminate
( self in stragg_type ,
returnvalue out varchar2 ,
flags in number
) return number
is
begin
returnValue := ltrim( self.string,
',' );
return ODCIConst.Success;
end;
member function
ODCIAggregateMerge
( self in out stragg_type ,
ctx2 in stragg_type
)
return number
is
begin
self.string := self.string
ctx2.string;
return ODCIConst.Success;
end;
end;
/
create or replace function stragg3 OBIEE Usage
( input varchar2 )
return varchar2
deterministic
parallel_enable
aggregate using stragg_type
;
/
