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))