Title: Historical data load considerations of project labor
This entry describes considerations when importing historical time.
What’s covered in this document:
Unanet Historical Data Policy
When historical data is part of the scope of a consulting engagement, the standard practices and scope include:
- Prior fiscal years are loaded in lump sum by person, by project, by year. Depending upon other attributes included in the timesheet such as labor category and task, the historical labor may be grouped by these attributes also.
- The current fiscal year data is loaded by month.
- Implementing more granular data such as daily actuals, or non-lump sums from prior years, is out of scope with respect to the standard consulting engagement. The client may request historical labor at a more granular level during the sales process resulting in additional consulting hours. If the client requests to have data at a more detailed level during the implementation process, a separate proposal must be executed on a T&M basis. Some conversion packages we offer include detailed level for current year, and annual sums for prior years. Any additional level of detail requires separate assessment.
- Note that when considering ODCs, bi-directional integrations (SL, Costpoint and GCS) will require ODCs to be imported at the detailed transaction level. This is necessary because the interface checks to see if the detailed transaction is in Unanet when performing the extraction of new ODC transactions from the accounting system.
Factors to take into consideration when determining the granularity of the historical data used include:
- What types of reports are needed?
- Note that historical periodic reports for prior years will not return useful results when using the practices outlined above. All of the labor is grouped into the last day of the fiscal year. If historical periodic reports are required, labor must be imported at the lowest level required for reporting purposes.
- Some systems only report time in lump sum per pay period for the current year. At the end of the year they aggregate by year, so it is not possible to import by month or day.
When would you use the Time-Historical import vs. the Time Import?
Generally speaking, for periods where another system other than Unanet is the system of record, you will want to load the labor data into Unanet as Time-Historical import. On the other hand, for periods where Unanet is the system of record, you will want to load the labor data into Unanet as Time import.
The Time-Historical import may be used in most cases, if:
- You have the bill or cost amounts but not rates. (The import will allow you to import the amount and the hours and will then derive the rate. For example, a bill amount of $2000 and 10 hours would result in a derived bill rate of $200.) Note that you can supply the rate instead of the amount if you prefer. If there are blended bill/cost rates across a span of time and you can only receive the aggregated $ rather than the discrete hours x bill/cost rates, the Historical Time import is easier to use rather than doing the math.
- You can import "Costs without Hours." While the Time-Historical Import format requires hours, there is a technique that allows your result to be in cost but not hours. To do this, you take two steps:
- Import 1 hour with the cost.
- Import -1 hour with 0 cost for a different date (the same date will not work).
- This will result in original cost but zero hours.
- You need to import negative hours (this can be useful if you need to change amounts on invoices that have already been posted, and which cannot be unposted).
- You don't want to worry about temporarily activating inactive people or inactive projects.
- You want to protect your historical data against re-rating or dilution calculations that may occur down the road.
- You want the ability to completely wipe out LOCKED timesheet if items don’t tie in a specific period for Historical Time, or if a Billing & Revenue or Labor Cost Post doesn’t tie in, versus having to make adjustments to a standard LOCKED/EXTRACTED timesheet and potentially have a significant number of adjustments displaying in the history.
- You want to ensure that Cost Structures and Cost Elements are specified on the time (vs. defaulting).
- You do not want corrections during the historical load to result in multiple adjustments records. (Time-Historical does not use adjustments; corrections replace the errors without a record of the original, which is fine for historical data.)
- You do not want inadvertent edits to the Rates tab on the Person profile to change timesheet rates or to trigger dilution.
The Time import may be used less often, for example:
- You need to correct time that was entered/recorded into the system after launch. For example, you loaded labor via Time-Historical imports through December 31, 2016, and now it's April 2017 and you realize that labor entered into your system as of January 2017 through April 2017 is incorrect. You may want to correct it en masse via the import.
- You want or need the ability to make adjustments in the future (note that Time-Historical can be changed, but it will be wiped out and replaced without a listing of all adjustments).
- This may be because you are doing recurring imports (not historical imports) such as recurring subcontractor time imports, to which you want to allow Adjustments.
- Certainly if there’s a potential for adjustments to be required in the future, the time import may be preferable since it allows you to be in control of the adjustments rather than requiring Unanet Support or Consultant activity via Historical Import files.
- You want Unanet to do the cost dilution calculations on the hours you are importing.
- For example, if you have the correct number of hours and you know the salaried person's cost rate, you can set the correct cost rate in the Unanet Person Profile, then import the hours. Dilution will then calculate the correct diluted cost rate (note that you must provide all hours including indirect hours like vacation and holiday in order for the cost to distribute appropriately).
- You want the Unanet system to apply defaulting logic to bill/cost rates (if you are not supplying the rates on the file).
- You want to submit and or approve the imported time in Unanet. (The Time-Historical format, on the other hand, may only be imported into the LOCKED status which precludes submissions or approvals.)
- You want to validate initial system configuration and bill/cost rate values during initial system implementation. You can do this by importing Time with hours but no cost or bill rates. Upon import, Unanet will then apply the appropriate cost/bill rates. When you run reports and validate against your previous labor system records, if Unanet does not match then you will know that something is awry in your Unanet configuration or bill/cost rate values. (Note that Time-Historical does not allow you to import Hours without either a Rate or Amount.)
- ***With all of the above, you will want to keep in mind that using the Time import instead of the Time-Historical import requires much more vigilance due to the complexities of the import. Corrections to LOCKED time may be challenging and you may end up with rows and rows of corrections. (The Historical Time Import, on the other hand, lets you purge LOCKED timesheets if you find there’s an error during invoicing - see above.)
|Time-Historical Import||Time Import|
|Can import negatives||Y||N|
Can import against inactive users, orgs, projects, tasks, project types
Time imported using the Historical Time import will be protected against any changes such as re-rating, dilution recalculations or Person Profile Rate tab edits or exports.
If you don't have rates but have $ amounts and hours per person, the rates can be calculated via the import process by taking the amounts divided by the hours.
|Defaults in the system apply|
(will need to supply items such as Project Type, Pay Code, Project Org Override, Person Org Override on Add)
|Can import adjustments|
(But you can import changes)
|Applies all validations in system (will vary by status for Time Import). For example, Require Comments will be required in any status; VSP's will be required only if INUSE.||N||Y|
|Allows import into any status|
|Can process Approvals|
(may want if you are importing time and training on approvals)
Historical Time import behaves differently from the Time import in these ways
- Bill Rate/Bill Amount and Cost Rate/Cost Amount fields/behavior.
- Historical Time Import includes "Cost Amount" and "Bill Amount," used to calculate the rates as described in the feature matrix above (not necessarily behavior, but relevant).
- Time imported is automatically LOCKED.
- "Historical Time" previously imported CAN be updated via a subsequent Historical Time import by Appending or Replacing. Unlike traditional time imports, "adjustments" are not created
to the time - even though it is LOCKED. It is a clean replacement. Since no adjustments are made, there is no trail stored of changes made. Note that using Replace will replace hours and comments; you may need to use First Purge to delete the timesheet and recreate it if you want to replace an item like Cost Rate. For more information, consult the Help Docs page on historical time.
- Separate import selection on import screen titled "Time - Historical."
- For a given person and a given time period, you may have either a historical timesheet or a non-historical timesheet.
Possible data load scenarios
- You may decide to load all time as a Time import. This is generally not recommended.
- You may decide to load all time prior to launch date of Unanet as a Historical Time import. The advantage here is that all labor cost/bill in your legacy system matches the Unanet Historical Time.
- You may decide to load all prior fiscal years as a Historical Time import, and the current fiscal year as a Time import. The current fiscal year timesheets would then be able to have timesheet adjustments made to them (but are at risk for inadvertent changes in the Rates tab).
Time-Historical Export Template
These export templates export time in the format of a Time-Historical import. You can import them to your system by saving (without opening), then going to Admin > Export > Exchange Templates > Import.
Here are two sample templates:
- Time-Historical Export Template.csv
- Time-Historical.csv (This version pulls timesheet cell effective hours/rates, so it speeds up preparing historical time correction files. It also works for putting in new historical time.)