[OBIEE 11g] Using External Table To Filter BI

OBIEE 11g: Using external table to Filter BI

When a BI Answers Report data need to be filtered based upon changing data, this is one method to achieve that.

Lets assume you need to report on products that are added or deleted on a daily basis. We can put that data into an Oracle table and use it as a filter to refine our output.

First lets create an SQL file Entries.sql with some sample data as shown below:

Drop Table SAMP_ENTRIES;
CREATE TABLE “SAMP_ENTRIES”
( “ID” NUMBER,
“STATUS” VARCHAR2(30)
) ;
Insert into SAMP_ENTRIES (ID,STATUS) values (1,’Bluetooth Adaptor’);
Insert into SAMP_ENTRIES (ID,STATUS) values (2,’Game Station’);
Insert into SAMP_ENTRIES (ID,STATUS) values (3,’MaxiFun 2000′);
Insert into SAMP_ENTRIES (ID,STATUS) values (4,’PocketFun ES’);
Insert into SAMP_ENTRIES (ID,STATUS) values (5,’Touch-Screen T5′);
Insert into SAMP_ENTRIES (ID,STATUS) values (6,’LCD HD Television’);

1. connect to the database as BISAMPLE/BISAMPLE user and password and execute the sql script as shown below:

2. Open the RPD file in the “BI Administration Tool”.
3. Right click the “Connection Pool” in the “Physical” Layer and select “Import Metadata”
4. Click “Next” button to reach to the screen below.
5. Select the “SAMP_ENTRIES” table and click on the “Import Selected” button then click on the “Finish” button.

6. Double Click on the “SAMP_ENTRIES” table in the “Physical” pane and under the “Keys” tab enter “ID” for the “Key Name” and select the “ID” under Columns.
7. Click “OK” button.
8. Drag the “SAMP_ENTRIES” table from the “Physical” layer to the “Business Model and Mapping” layer under the “Sample Sales” Business Model.
9. Double Click on the “SAMP_ENTRIES” logical table and select the “Lookup table” checkbox and click the “OK” button.

  

10. Drag the “SAMP_ENTRIES” logical table from the “Business Model and Mapping” pane to the “Presentation” pane and rename it to “Filter Vals”.
11. Select File -> Save and click yes when asked for the global consistency check.

12. Now reload your RPD file by going to the URL: http://localhost:7001/em
13. Navigate to the “Farm_bifoundation_domain” -> “Business Intelligence” -> coreapplication.
14. Select the “Deployment” tab then the “Repository” tab.
15. Select the “Lock and Edit Configuration”.
16. Select the “Browse” button and choose the RPD file that you just edited and saved.
17. Then restart your server under the “Overview” tab.

18. Now to create your report with the filter from the newly created SAMP_ENTRIES table.
19. Goto the URL: http://locahost:9704/analytics
20. Select the “New” -> “Analysis” -> “Sample Sales”
21. Under the “Filter Vals” folder drag the “Status” column to the “Selected Columns” area.
22. Save the report as “FilterVals”

23. Now select the “New” -> “Analysis” -> “Sample Sales”
24. Drag the “Product” and “Revenue” columns to the “Selected Columns” area.

25. Select the “Product” options and filter and enter the below values as show in the screenshot.
26. Click the “Browse” button and select the “FilterVals” report we saved above.Click the “OK” button.

27. Now click the Results tab to show the results from the just the entries in the “SAMP_ENTRIES” table.

Summary:

As seen above we have use a filter first by importing the table “SAMP_ENTRIES” to our RPD file, then creating a “FilterVals” table to use as a filter to our final report.
We can automate the process of editing the “SAMP_ENTRIES” table by creating and executing our Entries.sql file when ever our data changes in real time.