Question on getting YTD data using OBIEE 11.1.1.6.0

Question

Hi Team,

I have requirement to get YTD data for one of my measures but before that let me explain the data I have.

I have only one Flat file where in we get HR data for all our employees. We will receive one record per employee for each month. So we can say our grain is at the month level. We have created one master table in our warehouse to store this data. And the OBIEE, BMM layer we have created separate degenerated dimensions to categorize into separate dimensions and Fact. In that way we have created a Time dimension as well with 3 levels in the hierarchy Year, Quarter, Month. Now I have a situation where in I need to show the YTD data for Employee Headcount. Please advise with the current data I have can I achieve the YTD functionality. Please help me with your thoughts.

Thanks,

Dany

User_6LJME · Answer

Hi Gianni Ceresa,

Sure I will try TODATE function and will get back on how it goes.I just quoted Employee Headcount as an example, but we are looking really for YTD number of Hires against the Target Hires for the Year.

Thanks,

Dany

Gianni Ceresa · Answer

Hi,

as long as you have a time dimension in your model you can use the TODATE function and get YTD values with it (as you have a year level in your time dimension it's possible).

The point is more in what "Employee Headcount YTD" is supposed to mean from a business point of view: a count distinct on all your employees ID for the year? The headcount of the last available month for the year? It's not really like sales where you can sum money together month after month, headcount generally works in a different way.