Enable or disable caching in the system level and table level

Disable Caching for the System

You can disable caching for the whole system by setting the ENABLE parameter to NO in the

NQSConfig.INI file and restarting the Oracle BI Server. Disabling caching stops all new cache entries

and stops any new queries from using the existing cache. Disabling caching allows you to enable it

at a later time without losing any entries already stored in the cache.

Caching and Cache Persistence Timing for Specified Physical Tables

You can set a cachable attribute for each physical table, allowing you to specify if queries for a that

table will be added to the cache to answer future queries. If you enable caching for a table, any query

involving the table is added to the cache.

[Vikas Arora]:

The NQSConfig.ini – Parameter ENABLE controls the global level setting. If this is set to NO then even if we set “Cache Never Expires” for individual tables then also the query that accesses that table is not cached.

If the ENABLE parameter is set to YES and then “Cache Never Expires” for individual table is checked then query accessing the table is cached. The location of cache is mentioned in NQSConfig.ini in parameter DATA_STORAGE_PATHS.

If the ENABLE parameter is set to YES and then “Cache Never Expires” for individual table is not checked then query accessing the table is not cached.

Implementation of Usage Tracking in OBIEE 11G

Step 1:Create S_NQ_ACCT, S_ETL_DAY,S_ETL_TIME_DAY in bipauth schema of database.

Create table script for S_NQ_ACCT will be available in BI_DEVPLATFORM schema.

Create table script and Data for S_ETL_DAY,S_ETL_TIME_DAY will be available in below path:

C:\Oracle11g_MiddlewareHome\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample\usagetracking\SQL_Server_Time

@Oracle_create_nQ_Calendar.sql

@Oracle_create_nQ_Clock.sql

@Oracle_nQ_Calendar.sql

@Oracle_nQ_Clock.sql

 Create the following view  in bipauth schema of database.

create view NQ_LOGIN_GROUP as

select distinct USER_NAME as LOGIN, USER_NAME as RESP

from S_NQ_ACCT;

Step 2:Take a copy of the usage tracking RPD from the below path and paste into a temporary directory   C:\TEMP

C:\Oracle11g_MiddlewareHome\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample\usagetracking

Step 3:Upgrade the Usage Tracking rpd which is in Temporary directory by following the below steps:

Open the command prompt and run the below steps.

set ORACLE_INSTANCE=C:\Oracle11g_MiddlewareHome\instances\instance1

cd C:\Oracle11g_MiddlewareHome\Oracle_BI1\bifoundation\server\bin

obieerpdmigrateutil.exe -I C:\TEMP\UsageTracking.rpd -O c:\temp\NewUsageTracking.rpd -L c:\temp\ldif -U weblogic

When asked for the encryption password, it means the new repository password which is required by 11g.Just give it a simple password as Admin123.

Step 4:Now  we can observe New Usage Tracking  rpd created in below path.

                       C:\TEMP

Step 5:We now need to add extra fields to the S_NQ_ACCT table in the physical layer in New Usage Tracking  rpd.

Expand the S_NQ_ACCT table in the physical layer and check that all of the following fields exist with the correct data type. 

Rename RUNAS_USER_NAME to IMPERSONATOR_USER_NAME and then add  ID, QUERY_BLOB and QUERY_KEY columns.

Finally change the data type for all of the DOUBLE fields below from INT to DOUBLE. It is important that the Nullable field is correct on every field.

Field Name              Data Type     Length     Nullable?

CACHE_IND_FLG           CHAR            1           No

COMPILE_TIME_SEC        DOUBLE                      Yes

CUM_DB_TIME_SEC         DOUBLE                      Yes

CUM_NUM_DB_ROW          DOUBLE                      Yes

END_DT                  DATETIME                    Yes

END_HOUR_MIN            CHAR            5           Yes

END_TS                  DATETIME                    Yes

ERROR_TEXT              VARCHAR         250         Yes

ID                      VARCHAR         50          No

IMPERSONATOR_USER_NAME  VARCHAR         128         Yes

NODE_ID                 VARCHAR         15          Yes

NUM_CACHE_HITS          DOUBLE                      Yes

NUM_CACHE_INSERTED      DOUBLE                      Yes

NUM_DB_QUERY            DOUBLE                      Yes

PRESENTATION_NAME       VARCHAR         128         Yes

QUERY_BLOB              LONGVARCHAR     4000        Yes

QUERY_KEY               VARCHAR         128         Yes

QUERY_SRC_CD            VARCHAR         30          Yes

QUERY_TEXT              VARCHAR         1024        Yes

REPOSITORY_NAME         VARCHAR         128         Yes

ROW_COUNT               DOUBLE                      Yes

SAW_DASHBOARD           VARCHAR         150         Yes

SAW_DASHBOARD_PG        VARCHAR         150         Yes

SAW_SRC_PATH            VARCHAR         250         Yes

START_DT                DATETIME                    Yes

START_HOUR_MIN          CHAR            5           Yes

START_TS                DATETIME                    Yes

SUBJECT_AREA_NAME       VARCHAR         128         Yes

SUCCESS_FLG             DOUBLE                      Yes

TOTAL_TIME_SEC          DOUBLE                      Yes

USER_NAME               VARCHAR         128           Yes

Step 6: Now right click on S_NQ_ACCT, select Physical Diagram -> Objects and Direct Joins.

The joins to S_ETL_TIME_DAY and S_ETL_DAY are fine, but the join to NQ_LOGIN_GROUP is broken – the line is red and there is no arrow. Click on it and delete it.

Create a new join from S_NQ_ACCT to NQ_LOGIN_GROUP and change the join condition to:"OBI Usage Tracking"."Catalog"."dbo"."NQ_LOGIN_GROUP"."LOGIN" = "OBI Usage Tracking"."Catalog"."dbo"."S_NQ_ACCT"."USER_NAME"

Then click ok to save and close the diagram.

Now right click on S_NQ_ACCT and select properties. If there is no Key defined, enter ID in Key name and select the ID field from the drop down in the Columns field. This defines the key on the table. Click ok.

Step 7: In the Physical layer right click on ‘OBI Usage Tracking’ database, select properties. In the General tab change the Database to Oracle 11g.

Step 8: Then click on the Features tab and click the ‘Reset to defaults’ button.

Step 9:Right click on the ‘Connection Pool’ connection pool, select Properties.

Give the DSN name as <database>.WORLD.

User Name: bipauth

Password:*********

Now edit the ‘Usage Tracking Writer Connection Pool’ connection pool and make the same changes as above.

Step 10:Check Consistency.

Step 11: Take a back up of DevRpd to merge this rpd with Usage Tracking rpd.

Step 12:create a blank repository, select File -> New Repository.  Enter a name (Test.rpd), a location (the temp directory), click the No option against import metadata then enter the password ********. Finally click Finish.

Step 13:Open the backup rpd of Dev in offline mode.

select File -> Merge

In the Merge Wizard screen select Merge Type ‘Full Repository Merge’

Then select the Blank RPD as the Original Master Repository and enter its password ********

Then select NewUsageTracking Repository as the Modified Repository and enter its password Admin123.

Leave the Save Merged Repository as the default name (usually the same name with (1) at the end) –  rename it later once the merge is complete. But  give the password for this rpd as *********.

Leave Equalize during merge unchecked. Click Next.

From drop down in the Decision column choose ‘Current’. Then click Finish.

Then  we can see the merged rpd and check the consistency.

Step 14:Update the NQSConfig.INI file, for  locate Usage Tracking section and update following highlighted information.

[USAGE_TRACKING]

ENABLE = YES; 

#  Parameters used for inserting data into a table (i.e. DIRECT_INSERT = YES).

#

PHYSICAL_TABLE_NAME = "OBI Usage Tracking"."Catalog"."dbo"."S_NQ_ACCT";

CONNECTION_POOL = "OBI Usage Tracking"."Usage Tracking Writer Connection Pool"; 

BUFFER_SIZE = 250 MB; 

BUFFER_TIME_LIMIT_SECONDS = 5; 

NUM_INSERT_THREADS = 5; 

MAX_INSERTS_PER_TRANSACTION = 1; 

Step 15:Make the merged rpd as online and Restart the services.

Step 16:Now copy and paste UsageTracking.catalog on the desktop

Step 17: Log into Analytics and go to Shared Folders in Catalog.

Step 18:  Click on Shared Folder and then click on Unarchive option  which is in the left bottom corner.

Step 19:  Then new window will open to unarchive the catalog. Select Usage Tracking.webcatalog in browse and Select ACL as  'Inherit' and then click on 'OK'.

Step 20: Now Usage Tracking dashboard will appear under Dashboards.

OBIEE 11g repository on READ ONLY mode

If  there is a requirement to make OBI repository as read only—

To give READ ONLY access to users please navigate to following and check to disable online updates on repository. Bounce the server to activate changes.

Login to Oracle Enterprise Manager Goto -> Business Intelligence -> coreapplication

Click - Capicity Managment Tab in Right side -> Performance -> Check Disable Disallow Online RPD Updates

In order to make any development work then enable the option and bounce the server to activate the changes and repository will be opened for modifications.

Check to disable the option once development work is done else this will allow users to modify the repository knowingly or unknowingly.

Type of Dimensions (Conformed/Junk/Degenerate)

                                     Type of Dimensions


Conformed Dimension:

When a dimension can be used with multiple Facts, more precisely with multiple facts from multiple Data Marts. Conformed dimension provide the ability to perform the analytics across multiple subject ares of the Enterprise.

Most common example is Date Dimension.

Others are Product  Dim, Customer Dim.
Junk Dimension:

Its a way to store flags and indicators that are associated to transnational data. By creating Junk dimension these flags and indicators can be removed from the corresponding Fact table and placed in Junk dimension. 

The transaction table (in OLTP) may have several yes/no, true/false, indicator or  comments fields or columns with low carnality values.

If we leave these in Fact table then the row length would  be unnecessary large (and so the I/O).  


While creating a respective Fact table in Data Warehouse, one way is to those columns in the Fact table as it is, other way is use a junk dimension that holds all the unique combinations of those indicator fields into a single dimension and assigns a unique key.  This key is what is stored in the fact table.  So you will have only one additional dimension table and will reduce the number of fields in the fact table. 


Lets say there is Order Transaction Table that contains flags like Created (yes/no), Shipped(Yes/no), Returned ( Yes/No), Cancelled (Yes/No). These flags are related to status of the Order (or Order Line). There would be Order Key in the Fact Table that is linked to Order Dimension. There is no need to store Flags in the Fact table. In this case there is no need for Junk Dimension.

But at times there could be flags that are not associated to any dimension, and if there are plenty then keeping them in a Junk dimension would be more appropriate rather than storing them in the Fact table.  

(Let me find out some real life example of such flags and indicators and I will update here)