Security for hierarchical dimensions

Using IndexCol Function to Handle hierarchy Level security

IndexCol can use external information to return the appropriate column for the logged-in user to see. The Oracle BI Server handles this function in the following ways:

• ODBC Procedures. NQSGetLevelDrillability and NQSGenerateDrillDownQuery return the context-specific drill-down information based on the expression translated from IndexCol. This applies to both IndexCol expressions specified in the logical SQL query and IndexCol expressions specified in a derived logical column.
• Query Log and cache. The logical SQL with IndexCol function appears in the SQL string in the query log. But the logical request will not show the IndexCol function because Oracle BI Server will translate IndexCol to one of the expressions in its expression list in the logical request generator.

  NOTE: The query cache will use the resulting translated expression for cache hit detection.

• Usage Tracking. Usage tracking will insert the logical SQL query string with the IndexCol function.
• Security. As long as the user has the privileges to access the column(s) in the expression translated from IndexCol, then the query will execute.

  When the first argument to IndexCol is a session variable and if a default expression is expected to be returned even if the init block fails, then the Oracle BI Administrator should set a default value for the session variable. Otherwise, the query will fail because the session variable has no value definition.

Syntax

IndexCol( INTEGER literal, expr1, expr2, ... )

The IndexCol function takes in an integer literal value as its first argument, followed by a variable length expression list and translates to a single expression from the expression list. The literal value is the 0-based index of the expression in the expression list to translate to.

Consider the following expression:

IndexCol( INTEGER literal, expr1, expr2, ... )

If the literal value is 0, the above expression is the same as expr1. If the literal value is 1, then the value is the same as expr2, and so on.

NOTE: The primary use case for IndexCol is for the first argument to contain a session variable. Specifying a constant literal would result in IndexCol always choosing the same expression.

Example With Hierarchy Levels

Company ABC has a geography dimension with the hierarchy Country of State, City. The CEO can access the Country level down to the City level, and the sales manager can access the State and City levels, and the sales people can only access the City level. Create the table

CREATE TABLE HIERARCHY_LEVEL
(
  USER_NAME  VARCHAR2(30 BYTE),
  TITLE  VARCHAR2(30 BYTE),
  GEO_LEVEL  NUMBER(1)   DEFAULT 5
)

GEO LEVEL on Oracle must be NUMBER(1) as data type to become a INT in OBIEE. Samples values are given below.

Table Example With Hierarchy Level

USER_NAME	TITLE	GEO LEVEL
Bill	CEO	0
Bruce	VP	1
Mike	Manager	2
Kale	Manager	2

The following steps illustrate one way to create a single query where each user sees the top level to which they have access:

Create the OBIEE user

Create the OBIEE users with the same user names.

The Oracle BI Administrator creates a new session variable GEOGRAPHY_LEVEL that is populated by the initialization block: SELECT GEO_LEVEL from T where USER_NAME = ':USER'.

Create the session variable

SELECT GEO_LEVEL FROM HIERARCHY_LEVEL WHERE USER_NAME = ':USER'

This assume that the Oracle BI Server instance has the same user names.

Create a column formula

IndexCol( VALUEOF( NQ_SESSION.GEOGRAPHY_LEVEL ), Country, State, City )

With the amount sold.

Using SELECT IndexCol( VALUEOF( NQ_SESSION.GEOGRAPHY_LEVEL ), Country, State, City ), Revenue from Sales, the following occurs:

• Bill logs in and IndexCol translates to the Country column because the GEOGRAPHY_LEVEL session variable is 0. He will get the same result and be able to drill down on Country to State as if he had used SELECT Country, Revenue from Sale.
• Bruce logs in and IndexCol translates to the State column because the GEOGRAPHY_LEVEL session variable for Jackson is 1. He will get the same result and be able to drill down on State to City as if he had used SELECT State, Revenue from Sales.
• Mike logs in and IndexCol translates to the City column because the GEOGRAPHY_LEVEL session variable for Mike is 2. He will get the same result and won't be able to drill down on City as if he had used SELECT City, Revenue from Sales.

Reference

Oracle Documentation

Calendars in Oracle BI Applications 7.9.6

Technorati Tags: OBIEE,Calendar,Oracle BI Applications

Overview of Calendars in Oracle BI Applications

Oracle Business Intelligence Applications Version 7.9.6 supports different calendar formats:

• Enterprise (Global) - cross functional reporting calendar, which can be fiscal or gregorian.

• Fiscal - accounting or financial calendar.

• Gregorian - regular calendar that starts on January 1st and ends on December 31st.

• 13 Period - a calendar is which each year is comprised of 13 periods.

• 4-4-5 - each year is composed of twelve periods of either four weeks of 28 days or five weeks of 35 days.

 

Calendar Categories

Calendars are categorized into two types:

OLTP sourced (also known as Source Calendars) - OLTP sourced calendars are calendars that are defined in ERP sources and brought into the warehouse via ETL maps and standard DAC loads.

Warehouse generated (also known as Generated Calendars) - Generated calendars are fiscal calendars generated in the warehouse based on configuration files.

Both source calendars and generated calendars are stored in the Multiple Fiscal Calendar (known as MCAL) tables. MCAL tables have the prefix W_MCAL.

 

Calendar Tables in Oracle Business Analytics Warehouse

The tables used for Time Dimension calendars can be categorized based on the calendar types as below -

Gregorian Calendar Tables

• W_WEEK_D

• W_MONTH_D

• W_QTR_D

• W_YEAR_D

• W_DAY_D

Fiscal Calendar Tables

• W_MCAL_WEEK_D

• W_MCAL_PERIOD_D

• W_MCAL_QTR_D

• W_MCAL_YEAR_D

Enterprise Calendar Tables

• W_ENT_WEEK_D

• W_ENT_PERIOD_D

• W_ENT_QTR_D

• W_ENT_YEAR_D

 

W_DAY_D is the base table that represents the time dimension in the Oracle Business Analytics Warehouse. This table needs to be populated as a prerequisite for the multiple fiscal calendar tables. If W_DAY_D is not populated, then the fiscal calendar tables will not be populated.

There are two parameters $$START_DATE and $$END_DATE for the task SIL_DayDimension that need to be setup to load the calendar data in W_DAY_D. The SIL mappings use standard time functions to create records for each calendar day falling within the boundary defined by these two parameters. Once the records are created in W_DAY_D, the aggregate calendar tables are loaded by their respective SIL mapping. Then the fiscal calendar tables (known as MCAL tables) are populated.

 

Configuring Enterprise Calendars

An Enterprise calendar (or reporting calendar) enables cross subject area analysis. Enterprise calendar tables have W_ENT prefix. Enterprise calendars can be set to one of the OLTP sourced fiscal calendars or to one of the warehouse generated calendars. This can be done by setting the following source system parameters at the DAC container level:

• $$GBL_CALENDAR_ID

• $$GBL_DATSOURCE_NUM_ID

Scenario 1 Using an Oracle EBS fiscal calendar as the Enterprise calendar

Source System DAC Parameters for Oracle EBS Enterprise Calendars:

• GBL_CALENDAR_ID: This parameter is used to select the Enterprise Calendar. It should be the MCAL_CAL_NAME~MCAL_PERIOD_TYPE for Non-Generated Calendars. For example GBL_CALENDAR_ID will be 'Accounting~41', if the Enterprise Calendar id='Accounting' and the calendar period_type='41'.

• GBL_DATASOURCE_NUM_ID: If Enterprise Calendar is not a Generated Calendar: It should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken. For example, if you have two data sources as PeopleSoft and Oracle, and the Global Calendar is from an Oracle data source, then this parameter value should specify an Oracle data source.

Scenario 2 Using a warehouse generated calendar as the Enterprise calendar

Source System DAC Parameters for Generated Enterprise Calendars:

• GBL_CALENDAR_ID: Should be the CALENDAR_ID of the Generated Calendar (4-4-5 or 13 period type of Calendars). By default the 4-4-5 calendar has a CALENDAR_ID of '10000' and the 13-period calendar has a CALENDAR_ID of '10001'.

• GBL_DATASOURCE_NUM_ID: If Global Calendar is Generated Calendar: It should be the DATASOURCE_NUM_ID value of the OLAP (Data warehouse)

Scenario 3 Using a fiscal calendar loaded via the Universal Adapter as the Enterprise calendar

Source System DAC Parameters for Universal Enterprise Calendars:

• GBL_CALENDAR_ID: Should be the INTEGRATION_ID from the file_mcal_cal_d.csv file of the particular calendar which is defined as the Global Calendar.

• GBL_DATASOURCE_NUM_ID: If Global Calendar is not a Generated Calendars, then it should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken. If it is defined in the file_mcal_period_ds.csv file, then that value should be taken, or else as defined in the DAC for Universal adapter.

 

Generated Calendars

The following Task Level DAC Parameters are needed for Generated Calendars.

$$13P_CALENDAR_ID  - Task: SIL_TimeDimension_MCalWeek13Period. Required if you want to populate the 13 period type of calendar in your Data Warehouse. The value should be the CALENDAR_ID as defined in the W_MCAL_CONFIG_G table for the 13 period type of calendar.

$$445P_CALENDAR_ID  -  Task: SIL_TimeDimension_MCalWeek445. Required if you want to populate the 445 period type of calendar in your Data Warehouse. The value should be the CALENDAR_ID as defined in the W_MCAL_CONFIG_G table for the 445 period type of calendar.

If there is a week (starting on a Sunday and ending on a Saturday) that falls across two calendar years, the week is counted in both years. For example, the week that starts on 12/30/2007 will be counted in both 2007 and 2008. In 2007, the week start date will 12/30/2007 and the end date will be 12/31/2007. In 2008, this will be the first week with start date as 01/01/2008 and end date as 01/05/2008.

W_DAY_D stores 31 records for each month regardless of whether the month actually has 31 days. If the month has a fewer number of days, there will be records with null values in the Calendar Date and Day Date columns. These extra records are loaded for the calculation of Period Ago metrics in the Oracle BI Repository and will not affect the ETL or reporting.

There are some attributes on the W_DAY_D table that are not mapped in the Physical layer of the Oracle BI Repository. Therefore, before creating any new attribute in the repository, check whether the attribute is already available in the Physical layer and if it can be mapped directly.

If your fiscal calendar contains more than 12 months, the extra months will be assigned a value of 0 for the fiscal quarter. The same holds for the fiscal trimester and fiscal half values.

 

How to Include and Exclude Multiple Calendar Support for Subject Areas in DAC

The Multi-Calendar tasks are included in the TASK_GROUP_Load_DayDimension in the DAC.

When the TASK_GROUP_Load_DayDimension task group is added to any Subject Area, the Multi-Calendar tasks are not extracted by default. You need to add a Configuration tag to the Subject area before you run the ETL process to pull these tasks into the ETL process, as described in the following steps.

To include and exclude Multiple Calendar support for Subject Areas:

1. In DAC, display the Design view, and select the appropriate container.

2. Display the Subject Area tab in the upper pane and select the Subject Area to which you want to add Multi-calendar tasks.

3. Display the Configuration Tags tab in the lower pane.

4. Click Add/Remove to display the Choose Subject Area Configuration Tags dialog.

5. Select the configuration tag named 'Multiple Calendar Support' in the left pane and click Add, then OK.

6. In the upper pane, click Assemble.

7. Display the Execute view.

8. Display the Execution Plans tab.

9. Select the Execution Plan that includes the Subject Area to which you added the configuration tag named 'Multiple Calendar Support'.

10. Run this Execution Plan.

 

How to set up a 13 Period calendar

To set up a 13 Period calendar:

1. In the DAC, display the Design tab, and select an appropriate adapter.

2. Display the Source System Parameters tab.

3. Set the value of $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID as follows:

   • GBL_CALENDAR_ID: Should be the CALENDAR_ID of the Generated Calendar (4-4-5 or 13 period type of Calendars). By default the 4-4-5 calendar has a CALENDAR_ID of '10000' and the 13-period calendar has a CALENDAR_ID of '10001'.

   • GBL_DATASOURCE_NUM_ID: If Global Calendar is Generated Calendar: Should be the DATASOURCE_NUM_ID value of the OLAP (Data warehouse).

4. Using a text editor, edit the values in file_mmcal_config_g.csv.

5. In DAC, set the value of 13P_CALENDAR_ID to 10001.

   Note: The task SIL_TimeDImension_McalWeek13Period will run as part of the execution plan for your subject area. Please note that is no separate subject are for common dimensions. They are included in the core subject areas.

 

How to set up a 4-4-5 calendar

To set up a 4-4-5 calendar:

1. In the DAC, display the Design tab, and select an appropriate adapter.

2. Display the Source System Parameters tab.

3. Set the value of $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID as follows:

   • GBL_CALENDAR_ID: Should be the CALENDAR_ID of the Generated Calendar (4-4-5 or 13 period type of Calendars). By default the 4-4-5 calendar has a CALENDAR_ID of '10000' and the 13-period calendar has a CALENDAR_ID of '10001'.

   • GBL_DATASOURCE_NUM_ID: If Global Calendar is Generated Calendar: Should be the DATASOURCE_NUM_ID value of the OLAP (Data warehouse)

4. Using a text editor, edit the values in file_mcal_config_g.csv.

5. In DAC, set the value of 445P_CALENDAR_ID to 10000.

   Note: The task SIL_TimeDimension_McalWeek445 will run as part of the execution plan for your subject area. Please note that is no separate subject are for common dimensions. They are included in the core subject areas.