Time Series Functions

These functions help to obtain the data analyzed based on the time. There are two types of Time Series in OBIEE viz..

1)     Ago

2)     To Date

  Following are the prerequisites and procedure to create time series function in OBIEE RPD.

a)     Create a dimensional Hierarchy (ex: YearàQTRàMonth) and convert it to the time dimension. DC on hierarchy and check the box time dimension.

b)     Define the Chronological Key. A chronological key should always be the lowest level in the table and the data in it should be in the format of 20090102 or 200109 (year, month format). DC on level (preferred/last—detail) go to KEYS tab and check chronological key.

c)     Duplicate the existing column, rename and specify the formula for the column.

1)     Ago: This function when specified in any column displays data that is month ago, 2 months ago depending on the specified formula. DC on new column check use logical columns and click on exp. Builder. In the left column select functions and then time series and then AGO. Then in metric go to logical tables and select metric and in level go to time dimension and select month (1 month ago) and give the period as 1(since it is 1 month ago) then transfer two new columns to presentation layer

.

Ex: sales: 100,200,300 à -, 100, 200 (1 month AGO).

2)     To Date: This functions allows us to display the aggregated measures based on the formula specified. DC on new column check use logical columns and click on exp. Builder. In the left column select functions and then time series then select TO DATE. Select metric from logical tables (revenue) and as level go to time dimension and select year(YTD) and OK.

YTD- year to date – Displays aggregated values that is with respect to month since it is YTD. Similarly for QTD and MTD.

Ex: sales: 100,200,300 à YTD Sales: 100,300,600

Please let me know for any comments/questions.

Variables in OBIEE

There are 3 types of variables in OBIEE viz.

1) Repository - a) Static
                         b) Dynamic

2) Session - a) System
                    b) Nonsystem

3) Presentation


Repository and Session variables are created and configured in rpd and can be called in reports. Presentation variable can only be created and called in reports/dashboards.

1) Repository:

      Changes made to repository variables reflect only after restarting BI server.

a) Static- As the name suggests these variables represent the static values that are default and do not change unless the user changes.

   Examples : Currency Code –‘USD’, DSN=’DSN_0314’, ‘US_English’

In order to create a static variable goto> Manage à Variables

  Action>new>Repository>Variable> give name, select static and give default intializer.

b) Dynamic-  These variables are dynamic and do change with time. They are scheduled variables which run according to the scheduled time/whenever BI server is on. Initialization block is used to populate these variables.

Ex: Current_Date, Current_month, last refresh date (predefined).

2) Session:

Session variables are initialized at log in and are cleared at logout.

a) System- These variables are system defined reserved variables that cannot be changed by the user. User and Group variables are used for Authentication and Authorization purposes. I would be posting on EBS-OBIEE integration step by step procedure very soon.

Ex: User, Group, log level, password, web language.

b) Nonsystem- These variables are populated by Init blocks and are valid for that respective session only.

Ex: Primary position Id, Primary owner Id, Primary Organization Id.

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.

Business Model & Mapping Layer

This layer helps to implement business logic on the tables. The tables in this layer are called as logical tables whereas tables in physical layer are physical tables. Each logical table have one or more source of the physical table/view or in some cases logical table itself. Fetching data from physical column will be always faster than fetching from logical column, but with physical tables we do not have enough feasibility to implement the logic hence the logical tables.

It is best practice to join tables in BMM layer using Complex join. Since we may have multiple sources for a single logical table it would be easier for the BI server to fetch data.

To create new BMM RC(right click) anywhere in the layer and select New Business Model. Give the appropriate name and drag/copy the tables to new bmm from physical layer. After the tables are imported, the dimension tables should appear in white and facts in yellow by default. To create joins between two tables RC on two tables selected go to business model diagram > selected objects > select complex join at top on new window > drag from dim (dimension) table to fact.

It is the best practice to have hierarchies created for all the dimension tables although not required. Hierarchy created in bmm layer helps users to drill down in the reports, example: country>state>city. Drill down is the option in reports to make state appear in a report when country is clicked.

Physical Layer

It is recommended that Foreign key join is to be used to join tables in Physical layer also to create alias tables for every base table.

Foreign Key join is used to connect two tables using primary key and foreign key present in the tables. Alias acts as a pointer. Alias is the copy of the base table. New columns cannot be created in alias tables. Any changes made to base tables are automatically reflected in Alias tables. It is recommended that joins are created among the alias tables.

To open Admin tool and create new rpd file --- Programs> OBI>Administration
 
Go to File>Import  from database> select DSN created >select tables needed.
 
After the tables are imported, we can see the database tree structure with connection pool and schema. Schema consists all the tables imported. Connection Pool contains the information for Call interface (ODBC, OCI etc), schema in database etc. OCI is recommended for Oracle database.
 
Then create alias tables for the tables imported. RC (right click) on table in Physical layer > new object> alias. Create join between two tables (ofcourse fact and dimension) by selecting two tables and RC and go to physical diagram> select objects only, which pops up a new window. Select Foreign key join at the top and select dimension table and then drag to fact table. Specify the primary and foreign keys and click OK.
 
Fact is the table which stores the metric.Dimension table stores the attributes related to facts.Fact table contain measures, foreign keys of other tables (dimensions). A fact surrounded by the dimension tables and if the fact is directly connected to all dimension tables then it is Star schema. Similarly if we have any sub dimension connected to dimension but not directly connected to fact, that can be refered as Snow flake schema.

View:
A View can also be created in Physical Layer. A view is created to combine dimension and mini dimension to a new dimension. A view is used to display different columns from two or more tables.
RC on Schema > new physical table and in type select 'select' and write required query. Then go to tab columns and enter all the columns mentioned in query.

Ex: select city, country, row_id, sales from customer, sales where customer.row_id=sales.customer_id;

OBIEE Admin Tool

OBIEE Administration tool is where all the back end process of reporting is done. This is the place where business logic, security etc is implemented on physical data. RPD is a file containing all the metadata of the logic/security etc is stored. It basically has 3 layers.

1) Physical Layer: This is where connection pools are created and physical tables are imported in to rpd from datawarehouse.

2) BMM layer: Here actual business logic is implemented on logical tables and it is the best practice to join tables using complex join in BMM layer.

3) Presentation layer: This layer is the visible to the user in the front end to create reports/requests. Security is implemented in this layer.

The flow is Physical>BMM>Presentation. We will discuss each layer in the coming posts.

DSN Creation

After installing OBIEE on your local machine, a DSN has to be created which helps in creating connection pool in OBIEE Admin tool to connect to the database. Following are the steps to create System DSN.

Creating DSN

Go to Control Panel>administrative tools> ODBC

Go to System DSN(tab)>add>oracle xe(whichever database)

Enter name for your DSN, tns anything, User id (Schema) and test connections>ok

Note: If OBIEE is installed on 64 bit OS following link would be helpful

http://forums.oracle.com/forums/thread.jspa?threadID=869482

First Post

This blog is my notes of OBIEE tool. The content posted in this blog expresses the views of the author only  and is for knowledge purpose only.