Cache Purging Techniques

Cache helps BI server to fetch data from the Cache instead of going to datawarehouse whenever a query is rerun which helps to increase performance. Cache stores the query generated with respect to the report/query run. But if the data is updated in database and corresponding cache is not purged, the reports may populate old data. In order to avoid this cache purging techniques are used. There are four techniques available for performing Cache purging.

Cache Purging Techniques

1)      RPD

This is a manual technique performed in RPD. With this technique entire cache is purged.

Open Adminitration (RPD) in online and  goto Manage>Cache>Select All> Purge All.

2)      NQCMD Utility

This also is a manual technique where all the cache gets purged similar to the above technique.

** Update: In 12c or infact in 11g as well this is a very efficient way to purge cache automatically on a periodic basis, below are the steps to achieve this.

Assuming you have a lunix environment below are the steps (similar steps can be followed for Windows)

1) Create a .txt (cache_purge.txt) file where OBIEE is installed and save it to below location and fill in these contents

Call SAPurgeallcache()

MW_Home/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

2) Create a .sh file in any folder where OBIEE is installed with following contents

source  MW_Home/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/

OBIEE_Home/Oracle_BI1/bifoundation/server/bin/nqcmd -d AnalyticsWeb2 -u <Admin User> -p <Password> -s OBIEE_Home/instances/instance2/bifoundation/OracleBIApplication/coreapplication/setup/cache_purge.txt

3) Setup Crontab to schedule the cache purge in linux

 - Crontab -e
- Fill in below details
00 08 * * * MW_Home/Oracle_Home/user_projects/domains/bi/bitools/bin/purgecache.sh 2>&1 /dev/null
-- This setup runs the script at 8AM in the morning everyday


**

3) Event Polling Tables (IMP)

This is the most used technique. This is not a manual technique. In this technique the cache corresponding to the table that got updated in the datawarehouse is purged rather purging entire cache. A table is created in the database with the given script. A trigger is written in datawarehouse such that whenever a update or insert or modify is performed on any warehouse table the corresponding table name is added table (S_NQ_EPT) created. So when a report is created and if a table associated with the query generated exists in the created table the cache corresponding to the table is purged and the data is fetched from the database. Following is the procedure to implement this technique.

1)      Go to C:\OBI\Server\Schema and select .txt file based on the database used. For Oracle it is SAEPT.oracle.

2)      Copy the script given in the file and execute it in the datawarehouse. A table is created with name S_NQ_EPT with colum table_name and other columns.

3)      Then goto RPD and import newly created table. Do not join this table as this is stand alone table.

4)      Goto tools>utilities>OBI Even table > execute. Polling frequency is the interval in which the cache is updated. 

5)      Last step is to create a trigger in database. Trigger is given in such way that whenever a update or insert or modify is performed on a table,  S_NQ_EPT  is updated.

4) Fourth technique is used in RPD which is not much used. Goto table in RPD and double click on it and set option for cache. This technique may not be feasible if there are large number of tables.

Multiple Logical Sources

Mapping multiple physical tables to a single logical source

This is performed if the tables are in a Snow Flake schema, for aggregate navigation for fragmentation etc. The following example shows how a sub dimension table for Product table can be added as a source.

Method 1

1) Create new sub dimension table and import it to the physical layer.

2) Create the alias of new table and create required join to dimension table (product table).

W_prod_d---<w_prod_exp_d

3) Then in BMM(logical) layer create new logical table>name=prod_exp.

4)      Add tables (w_prod_d and w_prod_exp_d) to the source of new table.

5) Drag and drop the columns (uncommon in both) from the two tables to new logical table in BMM layer.

6) Create join between new table and existing fact table in BMM layer.

7) Specify content for the new table in fact table.

8) DC on new table keys>select primary key.

9) Transfer the new table to the presentation layer. Save and Close.

Method 2

Method 2 is simple add the sub dimension to the source of the Product dimension table and then drag and drop the columns of the sub-dimension table from Physical to BMM layer. Transfer new columns to the presentation layer.

Content Tab

The Content tab for any table in BMM layer serves multiple purposes. To access Content tab, DC (Double Click) on source of a table and go to content tab.

1) To provide the Logical level for the Dimension hierarchies in the fact table. For the aggregate tables added logical level is the least level before detail (or depending on the requirement) and for others it is detail.

2) To filter the values of the table. The data getting populated from the table is restricted using 'Where' clause. This can be done in RPD in online mode also. Goto table source> content> use this where clause> expression builder (...)> required condition.

3) Fragmentation Content

This is mainly used for increasing the performance usually done if the records are more than 100,000 in table. By creating partitions for required table at the database level, There sources pointing to the same table are created for the required table in BMM layer.  BI server will be chosing from the three sources of the table based on the query required.

Example: If there are 9 Million records in a table, the table is partitioned for 3Million records each.  Then we create 3 sources for the table all pointing to the same table.

Then in ‘content’, in ‘Fragmentation Content’, give condition for each table source created.

Example: Customer_id between 1 and 3Million

                Customer_id between 4 and 6Million

                Customer_id between 7 and 9Million

Aggregate Navigation

Aggregate tables are important in OBIEE (also interview point of view) which are used to increase the performance thereby decreasing the time required for the data to get populated in the reports. Aggregate tables are used making use of most economical source for the BI server to fetch data when a report is run. BI server choses the source for the data based on the granularity provided. If base table has state and country and if agg table has country the server loads country data from agg table when column country (only) is requested in a report. Following is the procedure to make use of aggregate table.

1) Create Aggregate table in the database for fact and dim and import them to the physical layer. Create join between there Aliases in physical layer.


2) Add aggregate tables as source to all the base tables which have their agg tables (Not necessary to import agg tables to BMM if there source is added to base tables that is fine). When a + sign is clicked under the base table a new source can be added to it.

3) Give aggregation=sum for all the measures in fact tables.

4) Then go to the source of fact table and give content the lowest level present in aggregate table before detail corresponding to the hierarchy created for base table with aggregates present. The server then would be able be choose between the sources of a table.

5) Add content as detail for the for other tables.

For every request/report run in Answers or Dashboards log is created with the details of sql query generated. To access the log in the front end go to settings>administration>manage sessions>view log.

By viewing sql generated in the log aggregate navigation can be checked by verifying the table from which the BI server is pulling the data.

Level Based Measures

Level based measures are built with the help of the hierarchies in BMM layer. If we need to have aggregated values in reports for example Country Sales, Monthly Sales etc we need to have level based measures. Share can also be calculated with the help of level based measures. Following is the procedure to create a level based measure with the example of sales of a Country.

1) There should be hierarchy built for the level based measures.


2) Duplicate the column Sales in the fact and rename it as country sales.
3) Add new column to hierarchy (country>state>city) under the level country.

4) Transfer the new column to the associated table in the presentation layer.

In the front end whenever country sales is selected in a request the data for this column would be aggregated value of sales for country.

Dimensional Hierarchy

Dimensional Hierarchy is used to have an option for end users to drill down the columns. Dimensional Hierarchy is built in BMM layer. It is the best practice to have hierarchy built for every dimension table. A time dimension hierarchy helps to to build time series functions (to date, ago). As per my knowledge a hierarchy can be built on columns from single table. OBIEE 10.x does not support ragged hierarchies. Following is the procedure to create country>state>city hierarchy.

Procedure: RC>BMM name>New object>dimension>name-dim_dept.

RC on Dim_dept>new object>logical level

First level in a hierarchy is named ALL and grant total is checked.

RC on ALL(first level)>new object>child level>name-country

RC on country> new object> child level> name- state.

RC on state > new object

Last level is named detail(recommended).

Then drag and drop the related columns from the table to respective levels. Ex: dept_country>country

RULE OF THUMB> last level should have a primary key of the table.

RC on dept_country> new logical key> check, similar for all other levels.

Then double click on each level(country) > go to key tab> check respective field and check drill down.

For last level uncheck drill down. >> Procedure end.

For all the hierarchies build it is important to mention each in the content of respective Fact table for granularity. Go to fact table > RC on source>content>select last level(detail) for all hierarchies unless we are using aggregate table and most economical concept.

RPD Offline/Online Mode

Online

A RPD file can be opened either in offline or online. If there are some minor changes to be done like renaming a column, services (BI Server) need not be restarted and can be performed by opening .rpd in online mode. Changes are slow in online and reload server metadata must be clicked for new updated data in Answers page. Renaming of a column can be done in presentation layer (uncheck use logical name).



Offline



For performing major changes to data .rpd file must be opened in offline. For offline changes to be made close RPD stop the BI server then open the RPD. Offline changes are quick but interrupt the services. File>open>offline/online.

Creating Basic Report.

The following is the procedure to create a basic report on local machine using OBIEE.

In the physical layer join the tables using Foreign Key>Drag them to the BMM layer. Join the tables using Complex join which helps to join logical tables with multiple sources and specifies cardinality relation between the tables. Fact tables appear in yellow while the dim tables appear in white. Then drag the same in to presentation layer or create a new catalogue in presentation layer. Then check consistency and global consistency before saving and close RPD.




In order to access Answers, both BI server and Presentation server should be up and running. To switch on  servers following is the procedure

Control panel> system & security> administrative tools> services> RC on BI server and start. Make sure presentation server is started. OC4J should always be started before running anything (Programs>OBI>start OC4J).

NQSconfig.ini is one important file which determines many settings of this tool. Only one RPD can be run in Answers which needs to be set up in this file.
Go to Mycomputer>oracle BI> server> config> NQS config file open it> come down and in repository section comment(#) on default and change the new rpd file that we wish to see in answers.



Now programs>OBI>presentation services> password is empty while user id is Administrator> Answers> To the right is the catalogs created in RPD click on anyone.



Then at the left we can see the tables we have put in catalog in RPD. Select the columns we need and click display results to create a request and save it in shared folder.

Presentation Layer

Presentation Layer contains Presentation Catalogs which are called as Subject areas in Answers. Each catalog would be having its own set of tables which are imported from BMM layer. Catalogs are created based on the business requirements each having its own set of tables and security implementations. Normally columns from two tables from two different subject areas cannot be used in the query/request.

A new catalog can be created by RC in the presentation layer and importing associated tables from BMM layer. It is necessary to have atleast one fact table in every catalog. The tables and columns present in the catalog are only visible in Answers for creating reports/requests.