OBIEE Online Training

OBIEE Online training by ex-Rittman Mead and Oracle trainer.
Contact : naveen@bipracticals.com
Watsapp : +65-82830121
Training will be real time and more hands on.
Number of slots available : 2/5

Saturday, June 30, 2012

Fiscal vs Calendar


Time Dimension is very much required in any dimensional modeling. Time dimension contains implicit hierarchy, unless otherwise mentioned, of the form:-





From an Organization’s Perspective, Time fields can be either Fiscal or Calendar. Single Hierarchy won’t suffice. Two Separate hierarchies i.e. Fiscal and Calendar Hierarchies are required to determine the measures properly throughout the Hierarchical Flow. Moreover, in case of only Calendar fields proper Fiscal fields are to be derived in BMM. Sometimes, Some Repository and Session variables are very handy for report building purpose. In this post I will be mentioning the creation of Two different time hierarchies, derivation of Fiscal or Calendar columns based upon whichever is present in actual and the creation of some Time related variables for report building purpose.

CALENDAR and FISCAL Time Hierarchies





Rules for creating two hierarchies based on the single dimension are as follows:-


i) There must be two top levels forming separate hierarchies.


ii) Detail levels should be same containing the Key of the dimension.


Referring to this screenshot, two separate hierarchies are present as shown. Top level (Calendar Year and Fiscal Year) of the Hierarchies are in the same level and Detail level s (Detail) are same.


For Rule (i), create two “Child Levels” (Calendar Year and Fiscal Year) for “Total” level.

For rule (ii), first complete any one Hierarchy (including Detail level) and while completing the Second Hierarchy use the option “New Object” à “Shared Level as Child” on the parent level and choose the “Detail” Level from the already completed Hierarchy.


[P.S: Intermediate Hierarchy creation steps are same as creating a new Level Based Hierarchy]


Time based Repository Init Blocks

I am not going to show you the variable creation. I will only show you the Init block expressions which are useful.

1) Init Block to Find the Start Date of Current Fiscal Year:-

SELECT A.SOY FROM (SELECT 1,
CASE WHEN TO_NUMBER (TO_CHAR (SYSDATE,'MM')) BETWEEN 1 AND 3
THEN TO_DATE (TO_CHAR ((TO_NUMBER (TO_CHAR (SYSDATE,'YYYY'))-1)) || '/04/01', ‘YYYY-MM-DD')
ELSE TO_DATE (TO_CHAR ((TO_NUMBER (TO_CHAR (SYSDATE,'YYYY')))) || '/04/01', ‘YYYY-MM-DD')
END SOY FROM DUAL) A

2) Init Block to Find the End Date of Current Fiscal Year:-

SELECT A.EOY FROM (SELECT 1,
CASE WHEN TO_NUMBER (TO_CHAR (SYSDATE,'MM')) BETWEEN 1 AND 3
THEN TO_DATE (TO_CHAR ((TO_NUMBER (TO_CHAR (SYSDATE,'YYYY')))) || '/03/31', 'YYYY-MM-DD')
ELSE TO_DATE (TO_CHAR ((TO_NUMBER (TO_CHAR (SYSDATE,'YYYY')) +1)) || '/03/31', ‘YYYY-MM-DD')
END EOY FROM DUAL) A

3) Init Block to Find the Start Date of Current Calendar Year:-

SELECT A.CSOY FROM (SELECT 1, TO_DATE (TO_CHAR (SYSDATE,'YYYY') ||'/01/01','YYYY-MM-DD') CSOY FROM DUAL) A;

4) Init Block to Find the End Date of Current Calendar Year:-


SELECT A.CEOY FROM (SELECT 1, TO_DATE(TO_CHAR(SYSDATE,'YYYY')||'/12/31','YYYY-MM-DD') CEOY FROM DUAL) A;

5) Init Block to Find the Current Fiscal Year:-


SELECT
CASE WHEN EXTRACT(MONTH FROM SYSDATE) BETWEEN 1 AND 3 THEN ('FY ' || TRIM( CAST ( EXTRACT( YEAR FROM SYSDATE) - 1 AS CHARACTER ( 30 )))) || '-' || TRIM( SUBSTR(CAST ( EXTRACT( YEAR FROM SYSDATE) AS CHARACTER ( 30 )),3,2)) ELSE ('FY ' || TRIM( CAST ( EXTRACT( YEAR FROM SYSDATE) AS CHARACTER ( 30 ))) || '-' || TRIM( SUBSTR( CAST ( EXTRACT( YEAR FROM SYSDATE) + 1 AS CHARACTER ( 30 )),3,2))) END FROM DUAL;

6) Init Block to Find the Next Fiscal Year:-

SELECT
CASE WHEN EXTRACT(MONTH FROM SYSDATE) BETWEEN 1 AND 3 THEN ('FY ' || TRIM( CAST ( EXTRACT( YEAR FROM SYSDATE) AS CHARACTER ( 30 )))) || '-' || TRIM( SUBSTR(CAST ( EXTRACT( YEAR FROM SYSDATE) + 1 AS CHARACTER ( 30 )),3,2)) ELSE ('FY ' || TRIM( CAST ( EXTRACT( YEAR FROM SYSDATE) + 1 AS CHARACTER ( 30 ))) || '-' || TRIM( SUBSTR( CAST ( EXTRACT( YEAR FROM SYSDATE) + 2 AS CHARACTER ( 30 )),3,2))) END FROM DUAL;

7) Init Block to Find Next To Next Fiscal Year:-
SELECT
CASE WHEN EXTRACT(MONTH FROM SYSDATE) BETWEEN 1 AND 3 THEN ('FY ' || TRIM( CAST ( EXTRACT( YEAR FROM SYSDATE) + 1 AS CHARACTER ( 30 )))) || '-' || TRIM( SUBSTR(CAST ( EXTRACT( YEAR FROM SYSDATE) + 2 AS CHARACTER ( 30 )),3,2)) ELSE ('FY ' || TRIM( CAST ( EXTRACT( YEAR FROM SYSDATE) + 2 AS CHARACTER ( 30 ))) || '-' || TRIM( SUBSTR( CAST ( EXTRACT( YEAR FROM SYSDATE) + 3 AS CHARACTER ( 30 )),3,2))) END FROM DUAL;

8) Init Block to Find the Current Calendar Year:-


SELECT TO_NUMBER (TO_CHAR (SYSDATE,'YYYY')) FROM DUAL;

9) Init Block to Find the Next Calendar Year:-

SELECT TO_NUMBER (TO_CHAR (SYSDATE,'YYYY')) +1 FROM DUAL;

10) Init Block to Find Next To Next Calendar Year:-

SELECT TO_NUMBER (TO_CHAR (SYSDATE,'YYYY')) +2 FROM DUAL;


Derivation of Fiscal Logical Fields:



i) Determination of Fiscal Date from a Calendar Date


TIMESTAMPADD (SQL_TSI_MONTH,-3, <<Calendar Date>>)

ii) Determination of Fiscal Month Name from a Calendar Date

MONTHNAME(TIMESTAMPADD (SQL_TSI_MONTH,-3, <<Calendar Date>>))

iii) Determination of Fiscal Year (say, FY 2011-12 format) from a Calendar Date

'FY ' || TRIM( CAST ( EXTRACT( YEAR FROM TIMESTAMPADD (SQL_TSI_MONTH,-3, <<Calendar Date>>)) AS CHARACTER ( 30 ))) || '-' || TRIM( SUBSTRING( CAST ( EXTRACT( YEAR FROM TIMESTAMPADD (SQL_TSI_MONTH,-3, <<Calendar Date>>)) + 1 AS CHARACTER ( 30 )) FROM 3 FOR 2))




Parent Child Hierarchy in OBIEE 11G


Step-By-Step Example to implement Parent-Child Hierarchy :

There are different types of hierarchy which exists in an Organization data which gives more insight for our analysis.

Ragged Hierarchy :

A hierarchy in which all the lowest-level members do not have the same depth. For example, a Time hierarchy might have data for the current month at the day level, the previous month’s data at the month level, and the previous 5 years’ data at the quarter level. This type of hierarchy is also known as an unbalanced hierarchy.

Skip-level Hierarchy :


A hierarchy in which certain members do not have values for certain higher levels. For example, in the United States, the city of Washington in the District of Columbia does not belong to a state. The expectation is that users can still navigate from the country level (United States) to Washington and below without the need for a state.

Parent-Child Hierarchy :

Consists of values that define the hierarchy in a parent-child relationship and does not contain named levels. For example, an Employee hierarchy might have no levels, but instead have names of employees who are managed by other employees.

A parent-child hierarchy is a hierarchy of members that all have the same type. This contrasts with level-based hierarchies, where members of the same type occur only at a single level of the hierarchy. The most common real-life occurrence of a parent-child hierarchy is an organizational reporting hierarchy chart, where the following all apply:

• Each individual in the organization is an employee.

• Each employee, apart from the top-level managers, reports to a single manager.

• The reporting hierarchy has many levels.

In relational tables, the relationships between different members in a parent-child hierarchy are implicitly defined by the identifier key values in the associated base table. However, for each Oracle BI Server parent-child hierarchy defined on a relational table, you must also explicitly define the inter-member relationships in a separate parent-child relationship table.

Let us start with Parent-Child Hierarchy and see how to implement it .

Am taking Employee table to work around . Click here to download .sql file for Employee table DDL and Data .



Step1 : Create a blank repository and import the Employee table in to Physical Layer .




Step2: Create Alias on EMPLOYEE table 1.’EmployeeDim’ and 2.’SalaryFact’ and give physical join between EmployeeDim to SalaryFact .





Step3: Drag the tables in to BMM layer and give aggregations for the fact columns.



Step4: Now right click on Employees logical table and choose for new parent child hierarchy .



Step5: Choose the member key (by default it will take the primary key . Here Employee Number) and parent column as shown in the below screenshot.



Step6: Click on ‘parent- child settings’ .This is the place where we are going to generate the DDL & DML scripts which we can use to create and populate the hierarchy table which will be used by BI server to report parent child hierarchies. Here click on ‘Create Parent-Child Relationship Table’ .



Step7: Enter the DDL&DML script names and click Next .




Step7: Give name for the Parent Child hierarchy table and Click Next .



Step8: You can see both DDL and Script to populate data here .



Click Finish .

You can see the screen as follows .



Click Ok again Ok .

After finishing the wizard you can see the HierarchyTable got imported automatically.



Right click on the EMPLOYEE_HIERARCHY table –> click on update rowcount and observe that you will get ‘Table does not exist error’

Step9:Go to the path <beahome>\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository. Run the scripts ‘EMPLOYEE_PARENT_CHILD_DDL.sql’ and ‘EMPLOYEE_PARENT_CHILD_DATA.sql’ .(My case I used SQL Developer to Run this scripts)

DDL :

CREATE TABLE EMPLOYEE_HIERARCHY ( MEMBER_KEY DOUBLE PRECISION, ANCESTOR_KEY DOUBLE PRECISION, DISTANCE NUMBER(10,0), IS_LEAF NUMBER(10,0) );

Script For polulate Data :

declare

v_max_depth integer;

v_stmt varchar2(32000);

i integer;

begin

select max(level) into v_max_depth

from EMPLOYEE

connect by prior EMP_NO=MANAGER_ID

start with MANAGER_ID is null;

v_stmt := ‘insert into LINEAGE.EMPLOYEE_HIERARCHY (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)’ || chr(10)

|| ‘select EMP_NO as member_key, null, null, 0 from EMPLOYEE where MANAGER_ID is null’ || chr(10)

|| ‘union all’ || chr(10)

|| ‘select’ || chr(10)

|| ‘ member_key,’ || chr(10)

|| ‘ replace(replace(ancestor_key, ”\p”, ”|”), ”\”, ”\”) as ancestor_key,’ || chr(10)

|| ‘ case when depth is null then 0′ || chr(10)

|| ‘ else max(depth) over (partition by member_key) – depth + 1′ || chr(10)

|| ‘ end as distance,’ || chr(10)

|| ‘ is_leaf’ || chr(10)

|| ‘from’ || chr(10)

|| ‘(‘ || chr(10)

|| ‘ select’ || chr(10)

|| ‘ member_key,’ || chr(10)

|| ‘ depth,’ || chr(10)

|| ‘ case’ || chr(10)

|| ‘ when depth is null then ”” || member_key’ || chr(10)

|| ‘ when instr(hier_path, ”|”, 1, depth + 1) = 0 then null’ || chr(10)

|| ‘ else substr(hier_path, instr(hier_path, ”|”, 1, depth) + 1, instr(hier_path, ”|”, 1, depth + 1) – instr(hier_path, ”|”, 1, depth) – 1)’ || chr(10)

|| ‘ end ancestor_key,’ || chr(10)

|| ‘ is_leaf’ || chr(10)

|| ‘ from’ || chr(10)

|| ‘ (‘ || chr(10)

|| ‘ select EMP_NO as member_key, MANAGER_ID as ancestor_key, sys_connect_by_path(replace(replace(EMP_NO, ”\”, ”\”), ”|”, ”\p”), ”|”) as hier_path,’ || chr(10)

|| ‘ case when EMP_NO in (select MANAGER_ID from EMPLOYEE ) then 0 else 1 end as IS_LEAF’ || chr(10)

|| ‘ from EMPLOYEE ‘ || chr(10)

|| ‘ connect by prior EMP_NO = MANAGER_ID ‘ || chr(10)

|| ‘ start with MANAGER_ID is null’ || chr(10)

|| ‘ ),’ || chr(10)

|| ‘ (‘ || chr(10)

|| ‘ select null as depth from dual’ || chr(10);

for i in 1..v_max_depth – 1 loop

v_stmt := v_stmt || ‘ union all select ‘ || i || ‘ from dual’ || chr(10);

end loop;

v_stmt := v_stmt || ‘ )’ || chr(10)

|| ‘)’ || chr(10)

|| ‘where ancestor_key is not null’ || chr(10);

execute immediate v_stmt;

end;

/

Click on COMMIT to commit changes .



Right click on the EMPLOYEE_HIERARCHY table –> click on update rowcount and observe that you will not get ‘Table does not exist error’ .

Step10: Pull the ‘ParentChildHierarchy’ into Presentation Layer ,Check Consistency and save the repository .Now we are ready to Build reports .




Click Here to download Repository (Repository Password : Admin123)

Step11: Go to answers create analysis including Employee Hierarchy . Thats it…


Reference:http://prasadmadhasi.com/2011/11/15/hierarchies-parent-child-hierarchy-in-obiee-11g/

Logical Dimension Hierarchies


Dimensions are key to navigating the business intelligence system that means dimensions define the axis of investigation of a fact and hierarchy is a set of parent-child relationships between attributes within a dimension and key to navigating dimensions.

In OBIEE Logical dimension hierarchy plays a very important role to calculate useful measures and allows users to drill down for more details.

Logical dimension hierarchies are categories of attributes by which the business is defined. Periods, products, markets, customers are very common dimensions in business model.
Dimension is mainly about “View By”, “Group by”, and “Filter By”.

Each level has at least one logical key, the top level is marked as the “grand total” level, and you can drag other attributes in to each level and have OBI EE display those, rather than the level key, when users drill down in reports.

First of all Update All Rows Counts -> check how many rows in every table and last updated state. This will help you to define level number of members at this level for each level of all the hierarchies.



 
1. Now first step is to create a logical dimension hierarchy, create a logical dimension object. There are two methods to create logical dimension objects.

Method1:- Right Click on the Business model in BMM layer select New Object-> Logical Dimension-> Dimension with Level Based Hierarchy.(Here structure is selected as Time because it’s a time dimension for other dimension hierarchies don’t select Time as structure )




Method2:- Right Click on a logical dimension table and select Create Dimension -> Dimension with Level-Based Hierarchy, this will create a dimension hierarchy for the selected dimension table.




Let`s continue with Method1 to create dimension hierarchies.

2. Start to add a logical level which is highest level of hierarchy. Right click on the logical dimension and select New Object-> Logical level.




3. Give a name to this level -> select Grand Total Level, basically the first level should be grand total level. (Selecting the Grand total level check box is to indicate that this is the grand total level of this hierarchy).




4. Now add a child level to dimension hierarchy.
 
 Right-click on the Parent level (Total) and select New Object -> Child Level.




Give a name for the level and enter the Number of levels at this level.

Now you need to identify the number of elements for each level. The number of elements represents the distinct count of rows for each level and is used by Oracle BI Server to determine the most efficient way to access data when picking aggregate sources. The number does not have to be exact, but ratios of numbers from one level to another should be accurate and you want to enter a larger number for child levels in respect to parent levels.


Enter number of elements at this level from updated all row counts and enter value that how many rows year column contains.



5. Similarly add more child levels below this level as shown in below screen shot.




6. Create Level Keys: - Level keys define the unique elements in each level and provide the context for drill down. Each level needs to have one or more attributes that compose a level key. The level key defines the unique elements in each level and provides the context for drill down.

A level may have more than one level key. When that is the case, you need to specify which key is the primary key of that level. All dimension sources that have aggregate content at a specified level need to contain the column that is the primary key of that level.
To create Level keys drag a logical column which could be the key for that level.




7. Double-click a level to open the level properties window-> Click the Keys tab and enter key name-> click on the primary keys dropdown select the key name to enable the edit and delete button.

Then click on edit button to open the logical key dialog box for this level. Select a column form columns to create a key column for this level and select use for display-> click ok.



8. Check out the changes and assigned key at the level-> click ok. The color of level key icon will change and also select check box Use for Display.



9. Similarly assign keys for each level following above steps.



10. Set the Preferred Drill Path(Optional): - You can use the Preferred Drill Path tab to identify the drill path to use when Oracle BI Presentation Services users drill down in their data requests. You should use this only to specify a drill path that is outside the normal drill path defined by the dimensional level hierarchy. It is most commonly used to drill from one dimension to another. You can delete a logical level from a drill path or reorder a logical level in the drill path.

To set a preferred drill path for dimension levels Open the properties of the level and select the Preferred Drill Path tab and press ADD button which open the Browse dialog box, where you can select the logical levels to include in the drill path. You can select logical levels from the current dimension or from other dimensions.




Check the preferred drill path.



11. Create Presentation Hierarchies

To create hierarchies in presentation layer follow below methods

Method 1:- If presentation tables are already exists in Presentation layer. Drag and drop logical dimension hierarchy into previously creates dimension table to which this hierarchy belongs. Will create a presentation hierarchy automatically.(Such as drag time dimension hierarchy into D0 Time presentation table).

1.1) Presentation Table without Presentation hierarchy.



1.2) Drag and drop logical dimension hierarchy from BMM layer into presentation table of Presentation layer.




Method 2:- If Presentation tables are not exits into presentation layer, Drag and drop the Dimension table from BMM layer into Presentation layer, will create a presentation table, columns with presentation hierarchy automatically.




Method 3:- Create Presentation Hierarchy manually

Right click on presentation table for which you want to create a presentation hierarchy.

3.1) Select New Object à Presentation Hierarchy.



 
3.2) Give a name into Name field in presentation hierarchy window and click on browse button.



3.3) select a logical dimension hierarchy from BMM layer.




 
3.4) after that a new presentation layer hierarchy will get created without ant level.




3.5) Now create levels for presentation hierarchy.

Right click on hierarchy icon -> Select New presentation level.




3.6) Give name of level and browse to assign level from BMM layer.




3.7) Similarly create or add other levels of presentation layer hierarchy following above steps.




12) Testing Hierarchy into presentation layer (answers)

12.1) Create a request select columns (Year and a measure) from left pane.




12.2) Click on result tab to see result. You will get Revenue by year.




12.3) Now click on any value (data) for column (Per_Name_Year) of dimension table and check the result, drill down is available now and you can see detail result for a specific year which is the top level of hierarchy.




12.4) Click on next levels values to go for more detail result, you can drill down till low level of hierarchy.




12.5)



12.6)



12.7)





13. Create Level Based Measures

As per a business requirement need to calculate Total Revenue, Yearly Revenue, Quarterly Revenue etc, at different levels of dimension and these measure were used later to create shared measure.


13.1) To create a level based measures create a logical column in BMM layer in Fact table and map it from an existing logical column.



13.2) Select a level from Time Dimension Hierarchy to set it for measure.



13.3) Click ok.



13.4) Check the measure in BMM layer under FACT table.




13.5) Now add this level based measure into dimension hierarchy, drag and drop the measure or copy and paste into under the dimension hierarchy.



Following the above steps you can create more level based measures, and no need to repeat the total calculations like Total Revenue, if the basis is same for different levels, because measure is calculated automatically at run time to the appropriate level. If the calculation for a level is different from that of another level need to be have a separate measure or logical column.


14) Creating Share Measure: - Share measures are calculated by taking some measure and divide it by a level based measure to calculate percentage.

14.1) Create a logical column -> Go to expression builder-> Select Functions-> Mathematical Functions-> Select Round function.

14.2) Select digits and enter a value, select Source Number to create a formula as shown below.




15) Add all these newly created measures to the presentation layer and create a request.



 
16) Compare result for all selected column how these columns are showing result.

16.1) Check result and verify with the data , that values in the columns are desired or not.

Here Total Revenue is calculating Total Revenue overall and show same data at each level of dimension hierarchy but the share (always show round off data) and revenue sum all will get change.



16.2)




16.3)



16.4).



17) Now go back to criteria and create one more request with Presentation Hierarchy to get result in parent child form.




18) Click on result and check the hierarchy and measure value.

Result will appear in a pivot table view



18.1) Result for Total, click on + plus sign for Total, it will generate result for year.




18.2) Similarly process result for deep child level (Quarter level)



18.3) Month Level



Thank you

- Naveen