1 2 Previous Next 15 Replies Latest reply on Sep 20, 2016 7:17 PM by Thomas Dodds

    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!

        • 1. Re: Make a date dimension for dashboarding
          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?

          • 2. Re: Make a date dimension for dashboarding
            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>

            • 3. Re: Make a date dimension for dashboarding
              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.

              • 4. Re: Make a date dimension for dashboarding
                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

                • 5. Re: Make a date dimension for dashboarding
                  Christian Berg

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

                  • 6. Re: Make a date dimension for dashboarding
                    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.

                    • 7. Re: Make a date dimension for dashboarding
                      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...

                      • 8. Re: Make a date dimension for dashboarding
                        Christian Berg

                        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:

                        • 9. Re: Make a date dimension for dashboarding
                          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!

                          • 10. Re: Make a date dimension for dashboarding
                            Christian Berg

                            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.

                            • 11. Re: Make a date dimension for dashboarding
                              bjbreitling

                              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?

                              • 13. Re: Make a date dimension for dashboarding
                                bjbreitling

                                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.

                                • 14. Re: Make a date dimension for dashboarding
                                  Christian Berg

                                  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

                                  1 2 Previous Next