5 Replies Latest reply on Oct 15, 2012 1:46 AM by Ahsan Shah

    HR Analytics: Headcount calculation query


      Does anyone know how the headcount metrics are calculated in HR Analytics - say as represented on the Workforce Profile- Overview page? If an employee has two active assignments because they have two job roles, would they be counted twice in these numbers or once?

      Is there anywhere were this level of detail is actually described - the metrics guide is pretty high level? I don't have access to an Informatica repository to see where the data is sourced and how.

        • 1. Re: HR Analytics: Headcount calculation query
          I am unable to answer your main question. But do you have the Data lineage spreadsheet handy ? That would give you the lowdown on the source for a specific metric etc..

          Try the metalink note - Oracle Business Intelligence Applications ETL Data Lineage Guide Release and [ID 1274680.1]

          Good luck
          • 2. Re: HR Analytics: Headcount calculation query
            Thank you - I have, yes.

            It looks like w_wrkfc_bal_a holds this in the headcount attribute, but in the data lineage spreadsheet, the detail in the SQL Overrides tab is truncated (this seems to be a common issue with this spreadsheet) - I'm using Excel 2007 and have tried downloading the XLS again. So, I cannot see the code use. As I mentioned, I don't have access to an Informatica environment unfortunately as we don't have any environments in place yet.

            Any thoughts?

            • 3. Re: HR Analytics: Headcount calculation query
              Ok .. I see what you mean.

              I just googled the aggregate table name and found this URL useful http://www.scribd.com/doc/77309289/30/Workforce-Aggregate-Fact-W-WRKFC-BAL-A
              page 13 has an explanation that might give you inputs for further research..

              1 person found this helpful
              • 4. Re: HR Analytics: Headcount calculation query
                Andy Coates
                Hi John,

                The only precise way to figure out how the measure is derived is to work with a BI Apps developer to track the measure back through the product stack e.g. OBIEE metadata (RPD) -> DataWarehouse Admin Console (DAC) -> Informatica mappings -> source tables/columns. The ETL, by it's very nature, may manipulate a base measure from the source system (EBS, PeopleSoft etc) in unusual ways. Once you have performed this data lineage, I recommend updating the tool tips (column descriptions) in the RPD to make the product more intuitive for end users.

                So, to answer your question, there is nowhere specifically where this information is described in such detail.

                You may wish to look at the BI Applications metrics reference and the business analytics warehouse data model reference documents in MOS:-

                Oracle Business Intelligence Applications Metrics Reference Guide Release 7.9.6, [ID 846917.1] -- I do not believe there will be a later version available until is released
                Oracle Business Analytics Warehouse Data Model Reference Version [ID 1325948.1]

                Please mark as helpful/correct,
                1 person found this helpful
                • 5. Re: HR Analytics: Headcount calculation query
                  Ahsan Shah
                  The answer is NO..having 2 job roles will not equal 2 people in your headcount. The ETL process will ensure that each employee is counted once in the target fact table (W_WRKFC_EVT_MONTH_F). Note that you will have Employee and Contingent workers as separate calculations.

                  if helpful, pls mark correct/helpful