Understanding Oracle BI Application(OBIApps) methodology of deriving AGO Measures

Very often I have members from the IT team at Customer site asking me the question on how to customize OBIApps in order to derive Ago(Year,Quarter,Month,Day) Measures, which are not delivered Out of the Box(OOTB) .Of course there is a Time series function(AGO) in OBIEE which can be leveraged to fulfill these requirements , but in this article I would like to explain the OOTB approach that is taken to derive AGO measures. Getting familiar with OOTB approach and the underlying Data Model that is there to support similar requirements, can help us follow similar methodology for our customization.

In this article I have takenOracle BI Application 7.9.6>Human Resources>Workforce Profile Subject area as an example for understanding the Concepts.

1.Below is the Screnshot of the OOTB Human Resources>Workforce Profile Subject Area .

2. Building a quick report with the Metrics above displays the results as below .

Note that Prior Period Headcount for 2006 is equivalent to Headcount value of 2005.

3. Let us now look into the design . Open the OOTB RPD and expand the Human Resource - Workforce Profile Presentation Catalog. Scroll Down and further expand the Presentation Table Workforce Profile Facts you should notice the two Presentation Columns Headcount and Prior Period Headcount .

4. These two Presentation columns are mapped to different Logical Table Sources as listed below
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Year
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Year_Ago
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Quarter
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Quarter_Ago
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Month
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Month_Ago
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Day
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Day_Ago

5.Further drilling down into the Physical Layer reveals that the above logial table sources are derived from the two Physical Table Aliases of the same table W_WRKFC_EVT_MONTH_F.

6.Below is the Physical Diagragm of the alias Fact_W_WRKFC_EVT_MONTH_F_Snapshot joinining with the Time Dimensions.

7.Below is the expression definition used in the join criteria . Note that ROW_WID of Year,Quarter,Month and Day Dimensions are used to join with the Fact table alias.

8.Below is the Physical diagram of the alias Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Period_Ago joining with the Time Dimensions.

9.Below is the expression definition used in the join criteria . Note that YEAR_AGO_WID,QUARTER_AGO_WID,MONTH_AGO_WID and DAY_AGO_WID are used to join with the Time Dimensions.

10.Let us now analyze the Data of the Time Dimension. As you can notice below the Data Model is in place which can be used to join and derive AGO measures.

11.Similar approach can be followed during Oracle BI Application(OBIApps) Customization for deriving ago measures which are not present Out of the Box(OOTB).

Configuring OBIEE Proxy(Act As) Functionality

As an OBIEE Developer we always want to be able to login as the Business Users in order to validate the security development. This can be done without the need to know the Business user's logon credentials by simply configuring the 'Act As' feature in OBIEE.
Below are the steps involved in enabling this feature.
1.The Script below will create a Proxy table and insert three records so that the developer 'shiv' can login(proxy) as 'business_user1' and 'business_user2' and validate the security requirement is as expected.Also the 'Administrator' can proxy the user 'shiv'.


CREATE TABLE OBIEE_ACT_AS_USERS
(PROXY_ID VARCHAR2(50),
TARGET_ID VARCHAR2(50),
PROXY_LEVEL VARCHAR2(50));
INSERT INTO OBIEE_ACT_AS_USERS(PROXY_ID,TARGET_ID,PROXY_LEVEL) VALUES('Administrator','shiv','full');
INSERT INTO OBIEE_ACT_AS_USERS(PROXY_ID,TARGET_ID,PROXY_LEVEL) VALUES('shiv','business_user1','full');
INSERT INTO OBIEE_ACT_AS_USERS(PROXY_ID,TARGET_ID,PROXY_LEVEL) VALUES('shiv','business_user2','full');
COMMIT;

2.Import the table OBIEE_ACT_AS_USERS into the Physical Layer and Name the Database and Connection Pool as Act_As_Users , CP_Act_As_Users.

3. We now need to create three Session Initialization Blocks Set_RUNAS(Variable:RUNAS),ProxyLevel(Variable:PROXYLEVEL) and ProxyBlock(Variable:PROXY).

4.Initialization Block:Set_RUNAS
Default Initialization String:
Select 'dummy' from dual


5.Initialization Block:ProxyBlock
Default Initialization String:
Seleselect target_id from obiee_act_as_users where 'VALUEOF(NQ_SESSION.RUNAS)'=target_id and ':USER'=proxy_Id


6.Initialization Block:ProxyLevel
Default Initialization String:
select proxy_level from obiee_act_as_users where 'VALUEOF(NQ_SESSION.RUNAS)'=target_id and ':USER'=proxy_Id


7.Navigate to /OracleBIData/web/config/ and modify the file instanceconfig.xml to add the below tag before the </ServerInstance>

<LogonParam>
<TemplateMessageName>Act_As_Logon_Template</TemplateMessageName>
<MaxValues>100</MaxValues>
</LogonParam>


8.Navigate to /OracleBI/web/msgdb/customMessages/ and create a new XML file Act_As_Logon_Template.xml containing the below script.

<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem">
<WebMessageTable system="SecurityTemplates" table="Messages">
<WebMessage name="Act_As_Logon_Template">
<XML>
<logonParam name="RUNAS">
<getValues>EXECUTE PHYSICAL CONNECTION POOL Act_As_Users.CP_Act_As_Users select target_Id from OBIEE_ACT_AS_USERS where proxy_Id='@{USERID}'</getValues>
<verifyValue>EXECUTE PHYSICAL CONNECTION POOL Act_As_Users.CP_Act_As_Users select target_Id from OBIEE_ACT_AS_USERS where proxy_Id ='@{USERID}' and target_Id='@{VALUE}'</verifyValue>
<getDelegateUsers>EXECUTE PHYSICAL CONNECTION POOL
Act_As_Users.CP_Act_As_Users select proxy_Id, proxy_Level from OBIEE_ACT_AS_USERS where target_Id ='@{USERID}'</getDelegateUsers>
</logonParam>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>

9. Within OBIEE Security add three new Users 'shiv','business_user1' and 'business_user2' as shown below.

10.Create a new Web group Act_As_Users_Group and add the user 'shiv' to this group.

11.Cotinue to be logged on as an Administrator and Navigate to Settings>Administration>Manage Privileges and add 'Act_As_Users_Group to Proxy .

12. Restart the Oracle BI Server and Oracle BI Presentation Server.

13. Login in as 'shiv' and then navigate to settings>Act as>business_user1

Ability to Enable/Disable DISTINCT and ORDER BY Clause in OBIEE at Report Level

There was a requirement from my customer to be able to Configure OBIEE at the report level such that the 'DISTINCT' and 'ORDER BY ' clause added by the OBIEE generated query on dimensional attributes can be gotten rid of when desired .

One of the Reason why we wanted to have this feature in our Dashboard was PERFORMANCE . Some of the queries were taking awfully long time to fetch data because of 'DISTINCT' and 'ORDER BY ' clause.

Let us first have a look at what I am trying to achieve and then I will explain the configuration steps.
The screenshot below shows a simple adhoc request sent to OBIEE by selecting COL1

Analyzing the Query generated by OBIEE shows that DISTINCT and ORDER BY clause was added.

Now let us say if we would like to get rid of 'DISTINCT' and 'ORDER BY ' clause in this particular report query all that I am going to do is navigate to the Reports Advanced Tab and change the Value of the Subject Area from 'A' to 'B' as you can see below .

Let us re-run the same report and analyze the query . As you can notice below the 'DISTINCT' and 'ORDER BY ' clause has been removed for this particular query .

Let us now look into the steps to achieve this
1.Within the OBIEE Physical Layer>Database Features by default the value of 'DISTINCT_SUPPORTED ' is checked .

2.Unchecking 'DISTINCT_SUPPORTED' feature will ensure that all the queries that uses the Connection Pool defined within this Database to remove 'DISTINCT' and 'ORDER_BY' clause . But the requirement is to be able to change for any selected report .
3.So I Copied all the three layers of the Metadata within the existing RPD as you can see below.The Only difference between the two are that 'DISTINCT_SUPPORTED' feature in one(B) has been turned off and they are named differently in all the three layers as you can see below.

4.To Summarize the steps . For the Subject area within which you want to be able to Enable/Disable the addition of 'DISTINCT' and 'ORDER BY ' clause .
i.Append all the three layers to the existing RPD with a different name.
ii.Uncheck the 'DISTINCT_SUPPORTED' feature for one of the Physical Datbase connection.
iii.Restart your Oracle BI Server .
iv.Go to the Reports Advanced Tab and Change the Subject Area name to point to 'A' or 'B' in order to Enable/Disable the 'DISTINCT_SUPPORTED' feature for the Report.

Switching Report Data Source Name(DSN) or User Name from OBIEE Dashboards/Answers

One of My Customer had a requirement to come up with a design which would allow the OBIEE Admistrator to switch report Data source Name(DSN) from the Answers . Let us say currently a report is coming from a DSN = ORCL now if the Administrator would like to change the source to a new DSN = ORCL2 the administrator should be able to do so from the OBIEE Answers. To give you a brief background behind this requirement , When we went live all the realtime(near realtime to be precise) reports were pointed to a data source which was planned to be refreshed nightly .Therefore we wanted to be able to come up with a design where in we could easily point a report to the live production instance for selected reports if the Business users could justify the need for it to be realtime.

We will first see what I am trying to achieve and then explain you the steps .
The Below report is being sourced from a Data Source Name(DSN)= 'ORCL' .

Now if you would like to source the same report from a different Data Source let's Say 'ORCL2' we can do so by adding just one Prefix statment by going to the Reports>Advance tab.
SET VARIABLE OLAP_DSN = 'ORCL2'

After applying the above statment the Report now sources from a DSN=ORCL2 as you can see below .

Let us now look into the steps to achieve this
1. I created three Different Data Sources and ran the script to create two tables Department and Employees.You can download the script and the RPD from the link below.
Download
ORCL>DWH: Data Source(Database)=ORCL,Schema(User)=DWH
ORCL>DWH2:Data Source(Database)=ORCL,Schema(User)=DWH2
ORCL2>DWH:Data Source(Database)=ORCL2,Schema(User)=DWH

Make sure that you keep the Same Password for all the three different schemas above.
2. As you can see in the RPD I just imported the two Tables established the Join in the Physical Layer and Dragged/Dropped the Folder all the way to the Presentation Layer.Then I set the Aggregation as Sum For Employees>Salary.
3.We then need to create a Session Initialization Block as below with two variables.
SELECT 'ORCL' OLAP_DSN , 'DWH' OLAP_USER FROM DUAL

4.Variable OLAP_DSN . Make sure that 'Enable any user to set the value' is checked.

5.Variable OLAP_USER.

6.Now let us Modify the Connection Pool to use the Session Variables.
VALUEOF(NQ_SESSION.OLAP_DSN)
VALUEOF(NQ_SESSION.OLAP_USER)

7. Let us now create three Reports.
Report1_ORCL_DWH

Report2_ORCL_DWH2
Go to the Advanced Tab>Prefix and add the below script
SET VARIABLE OLAP_USER = 'DWH2';

Report3_ORCL2_DWH
Go to the Advanced Tab>Prefix and add the below script
SET VARIABLE OLAP_DSN = 'ORCL2';


7.As you can see below the reports are coming from three different Data Sources in a Single Dashboard Page.

8. The Administrator can now Navigate to Administration>Manage Privileges>Answers:Access Advanced Tab and grant privileges to the group who is allowed to add the script in the Reports Advanced Tab.

How to host Multiple RPD and Multiple Presentation Services in OBIEE within the Same Windows Box

Article Summary: Host Multiple RPD/Presentation Services in OBIEE within the Same windows box i.e setup OBIEE Development/QA instance in the same machine .

1.Navigate to Oracle Business Intelligence>System Management

2. Give th 'oc4jadmin' password . This is the password that you give while installing OBIEE , In this case I gave the password as 'oc4jadmin'.


3. Once you are logged in Navigate to Applications Tab and Click on Deploy.



4. Now browse to the arhive location >\OracleBI\Web\analytics.war . Leave other default settings and click on next.


5 . You should see the below screen.


6 .Set the following values
Application Name: analytics2
Context Root: analytics2 and then click on Next


7. Click on Deploy.



8. You should see the deployment starting as below.


9. Once the Deployment is completed the below screen will appear .


10.At this point the new presentation service analytics2 is available.
http://machinename:9704/analytics/
http://machinename:9704/analytics2/
Both the Presentation Services are currently pointing to the same Dashboard/Webcat and instanceconfig file.


11. The below screen shows that I am now connecting to the new Presentation Services 'analytics2'

http://machinename:9704/analytics2/saw.dll?Dashboard


12.Now we will create a new catalog for the analytics2 presentation services.
>Copy the directory OracleBIData\web\catalog to OracleBIData\web\catalog_test. Now the new catalog-directory is ready to use and should be entered in the configuration file ( instanceconfig.xml).


13. Now we have created the new presentation service and the catalog for this service we have to change/create the configuration files. The default configuration file (instanceconfig.xml) tells us which listener-port, catalog and which presentation service will be used.
14. Make a copy of the original instanceconfig.xml and name this file instanceconfig_9710.xml. Also make a second copy of the original file and name this file instanceconfig_9712.xml
Edit the instanceconfig_9710.xml file as below
15.[ServerInstance]
[DSN]AnalyticsWeb1[DSN]
[CatalogPath]C:/OracleBIData/webcatalog/samplesales [CatalogPath]

Edit the instanceconfig_9712.xml file as below
[ServerInstance]
[DSN]AnalyticsWeb2[DSN]
[Listener port="9712"]
[CatalogPath]C: OracleBIDatawebcatalog_testpaint[CatalogPath]

16. Navigate to C:\OracleBI\oc4j_bi\j2ee\home\applications\analytics2\analytics\WEB-INF
And edit the file web.xml and modify the port number to 9712.
[param-name]oracle.bi.presentation.sawserver.Port[param-name]
[param-value]9712[param-value]

17. Create two batch files OBIEE1.bat and OBIEE2.bat
OBIEE1.bat = sawserver.exe -c C:\OBI2\OracleBIData\web\config\instanceconfig_9710.xml

18. OBIEE2.bat = sawserver.exe -c C:\OBI2\OracleBIData\web\config\instanceconfig_9712.xml
19. Modify the Config file to host two host two different RPD C:\OracleBI\server\Config and Edit the file NQSConfig.INI
[ REPOSITORY ]
Star = samplesales.rpd,DEFAULT;
Star2 = paint.rpd ;

20. Create two ODBC connections
AnalyticsWeb1

21.


22.


23.


24. AnalyticsWeb2


25.


26.->Restart the OBIEE Server .
->Stop the OBIEE presentation Services.
->Click on both the batch files . The two OBIEE Presentation services will have their respective Webcatalog and their Respective RPD all hosted in the Same Windows Box .


27.


28.


29.