Make a date dimension for dashboarding
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!
Answers
-
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?
0 -
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>
0 -
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.
0 -
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
0 -
I remember that one but for the life of me I have no clue from where.
0 -
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.
0 -
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...
0 -
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:
0 -
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!
0 -
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.
0 -
Hi Thomas,
I see this sql code is in Oracle SQL. Do you know where I would paste it to generate the date dimension. Would I just need to make an oracle database and bring it into the physical layer?
0 -
What is your warehouse DB? SQL Server? if so, https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/
0 -
OK, Now I have 3 tables, one of them is a date table that has a bunch of dates from help above including month and year. The other two tables contain employee id and one contains each employee ids hirer date and the other eachs termination date (null if not terminated.) How do I join these in the physical layer, what measures do I need to create, and what would I have to show in the analysis layer to get an employee count by month.
I know I need something like joining the dates in the physical layer but I'm not sure how to do it to give me the appropriate join to show what I want. I've tried joing the day in the date table to the termination date in the termination table and the hirer date in the hirers table. Then I make a count distinct measure of the employee id. Then when I show this in the analysis layer by month and year columns from the date table it doesn't give me the correct number. I also tried making a measure that gives a count of 1 when the hirer date is earlier than the month and the termination is null or greater than the month. This doesn't give me what I want either.
I didn't think either of these would work but was testing them. Can anyone walk me through with moderately detailed steps what to do that will give me a monthly headcount that I can trend with.
0 -
EmpID/HireDate table = fact
EmpId table (i hope there is more in there) = emp dim
Date table = Date dim
Measure = count distinct empid in fact table
0 -
You need a fact table that stores the count of employees by date - if you want the historical 'inventory' of headcount. It's a snapshot fact - take the snapshot on regular intervals (monthly, quarterly, weekly, etc)
or if you don't care about measuring over time ... do what @Christian Berg has suggested.
0
