Make a date dimension for dashboarding

bjbreitling

Hi,

How would I make a date dimension for showing something. For example I want to show head count. I have the termination date and hire date and I know the count of the employee unique id for head counts. What I need is a dimension that just gives the date for me to calculate the count of the head counts. I'm pretty new to OBIEE so it would be helpful if someone can explain how to do this.

Thanks!

Gianni Ceresa

Hi,

Are you asking how to do it in practice (add a logical table, add an hierarchy, make it a calendar dimension) or you just ask for a table containing .... dates?

bjbreitling

Just a Table containing dates. I've just made one and am experimenting and bringing into the repository. Is that how you would do something like I described above>

Gianni Ceresa

Well, there are many ways to do what you describe, and you also don't need a table with all the dates if you don't mind the holes when nobody has been hired or terminated at a given date...

And you must also have a table named S_ETL_DAY in the system schema the rcu created when you did the install and that one is quite a good start for a calendar dimension.

Thomas Dodds

Poke around with this ... can't remember where I found it ...

SELECT n AS Date_ID,

TO_CHAR(CurrDate, 'YYYYMMDD') AS DATE_KEY,

TO_DATE(CurrDate) AS DAY_DT,

TO_CHAR(CurrDate, 'DY') AS DAY_NAME_SHRT,

UPPER(TO_CHAR(CurrDate, 'Day')) AS DAY_NAME,

TO_NUMBER(TO_CHAR(CurrDate, 'D')) AS DAY_OF_WK_NUM,

TO_NUMBER(TO_CHAR(CurrDate, 'DD')) AS DAY_OF_MNTH_NUM,

TO_NUMBER(TO_CHAR(CurrDate, 'DDD')) AS DAY_OF_YR_NUM,

TO_NUMBER(TO_CHAR(CurrDate, 'J')) AS JULIAN_DATE_NUM,

CASE

WHEN to_date(CurrDate) = TO_DATE(SYSDATE) THEN

'Y'

ELSE

'N'

END AS CURRENT_DAY_FLG,

DECODE(TO_CHAR(CurrDate, 'D'), '7', 'N', '1', 'N', 'Y') AS WORK_DAY_FLG,

DECODE(TO_CHAR(CurrDate, 'D'), '7', 'Y', '1', 'Y', 'N') AS WEEK_END_FLG,

CASE

WHEN TO_CHAR(CurrDate, 'DD') = '01' THEN

'Y'

ELSE

'N'

END AS MONTH_START_FLG,

CASE

WHEN TO_CHAR(CurrDate, 'MM') != TO_CHAR(CurrDate + 1, 'MM') THEN

'Y'

ELSE

'N'

END AS MONTH_END_FLG,

CASE

WHEN TO_CHAR(CurrDate, 'Q') != TO_CHAR(CurrDate + 1, 'Q') THEN

'Y'

ELSE

'N'

END AS QUARTER_END_FLG,

TO_DATE(CASE

WHEN TO_CHAR(CurrDate, 'D') = '1' THEN CurrDate

WHEN TO_CHAR(CurrDate, 'D') = '2' THEN CurrDate-1

WHEN TO_CHAR(CurrDate, 'D') = '3' THEN CurrDate-2

WHEN TO_CHAR(CurrDate, 'D') = '4' THEN CurrDate-3

WHEN TO_CHAR(CurrDate, 'D') = '5' THEN CurrDate-4

WHEN TO_CHAR(CurrDate, 'D') = '6' THEN CurrDate-5

WHEN TO_CHAR(CurrDate, 'D') = '7' THEN CurrDate-6

END) AS CAL_BEGIN_WK_DT,

TO_DATE(CASE

WHEN TO_CHAR(CurrDate, 'D') = '7' THEN CurrDate

WHEN TO_CHAR(CurrDate, 'D') = '6' THEN CurrDate+1

WHEN TO_CHAR(CurrDate, 'D') = '5' THEN CurrDate+2

WHEN TO_CHAR(CurrDate, 'D') = '4' THEN CurrDate+3

WHEN TO_CHAR(CurrDate, 'D') = '3' THEN CurrDate+4

WHEN TO_CHAR(CurrDate, 'D') = '2' THEN CurrDate+5

WHEN TO_CHAR(CurrDate, 'D') = '1' THEN CurrDate+6

END) AS CAL_END_WK_DT, 

TO_DATE(CASE

WHEN TO_CHAR(CurrDate, 'D') = '2' THEN CurrDate

WHEN TO_CHAR(CurrDate, 'D') = '3' THEN CurrDate-1

WHEN TO_CHAR(CurrDate, 'D') = '4' THEN CurrDate-2

WHEN TO_CHAR(CurrDate, 'D') = '5' THEN CurrDate-3

WHEN TO_CHAR(CurrDate, 'D') = '6' THEN CurrDate-4

END) AS WORKING_WK_BEGIN_DT,

TO_DATE(CASE

WHEN TO_CHAR(CurrDate, 'D') = '6' THEN CurrDate

WHEN TO_CHAR(CurrDate, 'D') = '5' THEN CurrDate+1

WHEN TO_CHAR(CurrDate, 'D') = '4' THEN CurrDate+2

WHEN TO_CHAR(CurrDate, 'D') = '3' THEN CurrDate+3

WHEN TO_CHAR(CurrDate, 'D') = '2' THEN CurrDate+4

END) AS WORKING_WK_END_DT,

TO_NUMBER(TO_CHAR(CurrDate, 'W')) as WEEK_OF_MNTH_NUM,

TO_NUMBER(TO_CHAR(CurrDate, 'WW')) as WEEK_OF_YR_NUM,

TO_NUMBER(TO_CHAR(CurrDate, 'IW')) AS WK_OF_YR_ISO_NUM,

CASE

WHEN TO_DATE(CASE

WHEN TO_CHAR(CurrDate, 'D') = '2' THEN CurrDate

WHEN TO_CHAR(CurrDate, 'D') = '3' THEN CurrDate-1

WHEN TO_CHAR(CurrDate, 'D') = '4' THEN CurrDate-2

WHEN TO_CHAR(CurrDate, 'D') = '5' THEN CurrDate-3

WHEN TO_CHAR(CurrDate, 'D') = '6' THEN CurrDate-4

END) = TO_DATE(CASE

WHEN TO_CHAR(sysdate, 'D') = '2' THEN CurrDate

WHEN TO_CHAR(sysdate, 'D') = '3' THEN CurrDate-1

WHEN TO_CHAR(sysdate, 'D') = '4' THEN CurrDate-2

WHEN TO_CHAR(sysdate, 'D') = '5' THEN CurrDate-3

WHEN TO_CHAR(sysdate, 'D') = '6' THEN CurrDate-4

END) THEN

'Y'

ELSE

'N'

END AS CURRENT_WK_FLG,

UPPER(TO_CHAR(CurrDate, 'Mon')) AS MNTH_NAME_SHRT,

UPPER(TO_CHAR(CurrDate, 'Month')) AS MNTH_NAME,

TO_NUMBER(TO_CHAR(CurrDate, 'YYYYMM')) AS YR_MNTH_NUM,

TO_NUMBER(CASE WHEN TO_CHAR(CurrDate, 'MM') = '01' THEN

TRIM(TO_CHAR(TO_NUMBER(TO_CHAR(CurrDate, 'YYYY'))-1)) || '12'

ELSE

TO_CHAR(CurrDate, 'YYYY') || LPAD(TO_CHAR(TO_NUMBER(TO_CHAR(CurrDate, 'MM'))-1),2,'0')

END) AS PREV_YR_MNTH_NUM,

TO_NUMBER(CASE WHEN TO_CHAR(CurrDate, 'MM') = '12' THEN

TRIM(TO_CHAR(TO_NUMBER(TO_CHAR(CurrDate, 'YYYY'))+1)) || '01'

ELSE

TO_CHAR(CurrDate, 'YYYY') || LPAD(TO_CHAR(TO_NUMBER(TO_CHAR(CurrDate, 'MM'))+1),2,'0')

END) AS NEXT_YR_MNTH_NUM,

TO_NUMBER(TO_CHAR(CurrDate, 'MM')) AS MNTH_NUM,

CASE

WHEN to_CHAR(CurrDate,'MM') = TO_CHAR(SYSDATE,'MM') THEN

'Y'

ELSE

'N'

END AS CURRENT_MNTH_FLG,

TO_NUMBER(TO_CHAR(CurrDate, 'Q')) AS QTR_NUM,

CASE

WHEN to_CHAR(CurrDate,'Q') = TO_CHAR(SYSDATE,'Q') THEN

'Y'

ELSE

'N'

END AS CURRENT_QRTR_FLG,

TO_NUMBER(TO_CHAR(CurrDate, 'YYYY')) AS YR_NUM,

CASE

WHEN to_CHAR(CurrDate,'YYYY') = TO_CHAR(SYSDATE,'YYYY') THEN

'Y'

ELSE

'N'

END AS CURRENT_YR_FLG

FROM (SELECT LEVEL n,

TO_DATE('31/12/1999', 'DD/MM/YYYY') +

NUMTODSINTERVAL(level, 'day') CurrDate

FROM DUAL

CONNECT BY LEVEL <= 44195) order by date_key;

^--- set your initial date ('31/12/1999') and how many days (44195) in the future from that date you want

[Deleted User]

I remember that one but for the life of me I have no clue from where.

bjbreitling

Hi Gianni Ceresa, I don't see the S_ETL_Day in the sys schema on my system. Why is that important? How would you recommend geeting an employee count by month including when nothing changed.

Gianni Ceresa

It's not supposed to be in the SYS schema but in the BIPLATFORM one ...

It's not important at all, Thomas gave you a piece of code to generate one if you prefer.

I have the feeling the question isn't really about a time dimension but more about how OBIEE works.

An employee count by month?

Join the time dimension with your employees table to get all the existing employees at a selected time and do a count distinct on the employee ID or anything describing in unique way your employee...

[Deleted User]

Not SYS. Gianni meant the RCU-created BIEE_BIPLATOFRM (for example; it's BITPLATFORM but the prefix may differ for you)

Vanilla installs contain the necessary scripts - at least up until 11.1.1.9:

bjbreitling

Hi Gianni,

Basically we have two tables, one with employee ids and hire data and one with employee ids and termination date. What we don't have is a table with a time dimension. What would be a recommended path to do what we suggested a monthly employee count trend?

Thanks!

[Deleted User]

Either create a proper data dimension as stated above or derive one from the time stamps in the fact - obviously you'd lose all dates where there's no entry in the facts though.