Monday, November 4, 2013

OBIEE11g - Custom BI Time Dimension Populate Database Script.


1) Create database function to populate the time table in the database.

CREATE OR REPLACE PROCEDURE update_day_dim (start_date IN DATE, no_of_days IN NUMBER,Delete_data IN varchar2)
AS
   mcount   NUMBER;
   mdate    DATE;
BEGIN
if delete_data = 'Y' then
   delete from xx_bi_time_day_d;
   commit;
end if;
   BEGIN
      mcount := 1;
      mdate := start_date;

      FOR mcount IN 1 .. no_of_days
      LOOP
   
         INSERT INTO xx_bi_time_day_d
              VALUES (TO_CHAR (mdate, 'MM/DD/YYYY'), mdate
                    , TO_CHAR (mdate, 'DD'), TO_CHAR (mdate, 'Day')
                    , TO_CHAR (mdate, 'MM-YYYY'), TO_CHAR (mdate, 'MM')
                    , TO_CHAR (mdate, 'W'), 'W' || TO_CHAR (mdate, 'W')
                    , TO_CHAR (mdate, 'YYYY'), TO_CHAR (mdate, 'Q')
                    , 'Q' || TO_CHAR (mdate, 'Q'), TO_CHAR (mdate, 'Month')
                    , TO_CHAR (mdate, 'MM')
                    , DECODE (TO_CHAR (mdate, 'Q')
                            , '1', 'H1'
                            , '2', 'H1'
                            , 'H2'
                             )
                    , DECODE (TO_CHAR (mdate, 'Q'), '1', '1', '2', '1', '2'));

         mdate := mdate + 1;
      END LOOP;

commit;

   END;
END;*/


2) Create below table in the data base.

create table xx_bi_time_day_d
(current_date  varchar2(15),
current_date_date  date,
day_num   number(15),
day_name   varchar2(25),
period_name  varchar2(15),
period_num   number(15),
week_num     number(15),
week_name      varchar2(15),
period_year varchar2(4),
quater_num    number(15),
quater_name    varchar2(3),
month_name   varchar2(25),
month_num  number(15),
half_year_name varchar2(15),
half_year_num number(15)
);

3) To Populate the data in the time table, Execute the below function.

execute update_day_dim('01-JAN-1980',50000,'Y');



OBIEE 11g - Enable Report Performance Improvement in the Analysis

OBIEE 11g analysis has query performance option to "Share query with multiple users"

To enable this option,
1. Analysis' Advanced tab
2. In the bottom of the page select "Share query with multiple users (may improve performance after initial
run)
3. Click "Apply" and save your analysis.

OBIEE11g - Enable Log-Level from Advanced Tab

In the Advanced tab, Add below tag in the prefix and click Apply SQL.

SET VARIABLE LOG_LEVEL = 2;


Log from Administration > Manage Sessions.

OBIEE11g - Calculating First Day of Year, Quarter, Month in Answers


First Day of  Year: TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFYEAR(CURRENT_DATE )-1) , CURRENT_DATE )

First Day of Quarter: TIMESTAMPADD(SQL_TSI_DAY, -1*(DAY_OF_QUARTER(CURRENT_DATE )-1) , CURRENT_DATE )

First Day of Month: TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH(CURRENT_DATE )-1) , CURRENT_DATE )

Query For Current Year and Quarter and Month  

SELECT 
TRUNC(cast(TO_CHAR(SYSDATE,'YYYY') as VARCHAR(20))),
TRUNC(CAST(TO_CHAR(SYSDATE,'Q') AS VARCHAR(20))),
TRUNC(CAST(TO_CHAR(SYSDATE,'MM') AS VARCHAR(20)))
FROM DUAL;

Sunday, November 3, 2013

OBIEE11g - Changing Default Chart Colors

Change default chart colors in the OBIEE11g

OBIEE 11.1.1.7.x
<Middleware>/Oracle_BI1/bifoundation/web/msgdb/s_FusionFX/viewui/chart/dvt-graph-skin.xml
Older versions:
<Middleware>\Oracle_BI1\bifoundation\web\msgdb\s_blafp\viewui\chart\dvt-graph-skin.xml

Add the following <SeriesItems> tag before </Graph>
Note: You can add as many <Series id="n" color=.... /> as you wish.
<SeriesItems>
<Series id="0″ color="#ff0000″ borderColor="#ff0000″/>
<Series id="1″ color="#00ff00″ borderColor="#00ff00″/>
<Series id="2″ color="#0000ff" borderColor="#0000ff"/>
</SeriesItems>


</Graph>

Error : [nQSError: 13015] You do not have the permission to set the value of the variable


View Display Error

Error getting drill information: SET VARIABLE LEDGER-ID='02' SELECT ledger.ledger_id saw_0, valueof(nq_session.LOB) saw_1 FROM OLEM

 Error Details
Error Codes: YQCO4T56:OPR4ONWY:U9IM8TAC:OI2DL65P
Odbc driver returned an error (SQLExecDirectW).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 13015] You do not have the permission to set the value of the variable 'LEDGER_ID'. (HY000)
SQL Issued: {call NQSGetLevelDrillability('SET VARIABLE LOB=''LEDGER'' SELECT ledger.ledger_id saw_0, valueof(nq_session.LEDGER_ID) saw_1 FROM LEDGER')}


Solution :
 Go to properties of session variable all check the option "Enable everyone to set value"

Follow the below steps :

1. Open Administration Tool. Go to Manager > Variables.

2. Select System or Non-System variable (depends on type of your variable) under the Session  from left pane. Then the corresponding variables will be listed on right pane.

3. Right click on the variable(for which your getting error in presentation service when you tried to change its value) and select properties.

4. check the option "Enable everyone to set value" (by default this option is unchecked) and click OK.
 Log out from that session and again login to presentation services and the same report.

OBIEE 11g - Query Limit

Query limit and number of minutes a query can run per physical layer database connection, follow the below steps.

> Login to Repository using OBIEE Admin Tool
> Go to Manage > Identity
> Go to Application Role tab, choose the role and double click on it to open.



> Click on Permissions tab



 > Set the Query Limits. You can limit queries by the number of rows received, by maximum run time, and by restricting to particular time periods. You can also allow or disallow direct database requests or the Populate privilege.



OBIEE 11g - Query for Yesterday Date

Using in Analysis:
"Date" IN (TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE))


Using sql query in the Dashboard Prompt:
SELECT TIMESTAMPADD (SQL_TSI_DAY,-1,CURRENT_DATE) 
FROM "Time"