nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

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!

Find more posts tagged with

Accepted answers

All comments

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?

Thomas Dodds

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/

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.

[Deleted User]

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

Thomas Dodds

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.