IBM Cognos® TM1 is is an enterprise planning software that provides a complete, dynamic environment for developing timely, reliable and personalized forecasts and budgets. TM1 provides more efficient (faster) data storage, fast data access, easy data maintenance, flexibility in data management, and fast aggregation of data needed for typical analytical reporting. UCAR has designed and implemented TM1 to serve as a data modeling and analytics tool in which budgets are created and brought together with actual Human Resources Information System (HRIS) and Financial System data to deliver multi-dimensional analytics and reporting that supports the budget planning and budget management processes of the institution.
The UCAR TM1 environment consists of OLAP cubes that are made up of dimensions. Typical OLAP implentations consist of cubes which all contain at least one time dimension (e.g. Month, FY), a scenario dimension, and a measure dimension. Additionally, some cubes are designed for data entry and some serve only a reporting need. Below is a diagram of the UCAR TM1 Cube Design.
Below is a listing of some of the UCAR Cubes, the dimensions that make them up, and their usage in the UCAR planning model. Note that in the listing below we use cc_ to identify a cube, cd_ to identify a dimension and cdm_ to specifically identify a measure dimension:
Cube Name | Cube Function | Dimensions
|
| cc_Employee | Data Entry: Plan Employee Salaries and Reclassification | cd_Scenario |
| cd_Employee | ||
| cd_Position Nbr | ||
| cd_Employee Record | ||
| cdm_Employee Record | ||
| cc_Employee Assumptions | Data Entry: Define worktime % and CSC division for an employee or group of employees. | cd_Scenario |
| cd_Employee | ||
| cd_Line Num | ||
| cdm_Employee Assumptions | ||
| cc_Division Assumptions | Limited Data Entry: Used to record divisions and their annual divisional CSC rates so this information can be used to calculate CSC rates by employee in planning scenarios. Sourced from Bi-Tech/IFAS for current year and maintained by a central authority for out-planning years. | cd_Scenario |
| cd_Year | ||
| cd_Division | ||
| cdm_Division Assumptions | ||
| cc_ Employee Account Allocation | Data entry: For assigning employees to account keys based on a % of time between a begin date, end date on key. Replaces the iVantage assignment function. | cd_Scenario |
| cd_Account | ||
| cd_Employee | ||
| cd_Position Nbr | ||
| cd_Account Key Assignment Nbr | ||
| cd_Budget Type | ||
| cdm_Employee Account Allocation | ||
| cc_Employee by Account Pay Period | Data Entry: For assigning employees to account keys based on hours by month or quarter. | cd_Scenario |
| cd_Date - PP | ||
| cd_Employee | ||
| cd_Position Nbr | ||
| cd_Account | ||
| cd_Budget Type | ||
| cdm_Employee by Account | ||
| cc_Employee Plan to Actual Report by Pay Period | Reporting: Used to evaluate budget vs actual hours, salary, benefit, overhead, and CSC expenses by employee or group of employees, by account key, by pay period | cd_Scenario |
| cd_PayPeriod_Expense | ||
| cd_Employee | ||
| cd_Position Nbr | ||
| cd_Account | ||
| cd_Object Employee | ||
| cd_Budget Type | ||
| cd_Position | ||
| cd_Fund Source | ||
| cd_Contract | ||
| cd_Facility | ||
| cd_Sub Facility | ||
| cd_Entity | ||
| cd_Lab | ||
| cd_Division | ||
| cdm_Employee Plan to Actual | ||
| cc_Non Salary Planning | Data Entry: Used to plan budgets for non-salary items (e.g. Materials and Supplies, Equipment, etc.) | cd_Scenario |
| cd_Year | ||
| cd_Month | ||
| cd_Budget Type | ||
| cd_Account | ||
| cd_Object | ||
| cd_Line Num | ||
| cdm_Non Salary Planning | ||
| cc_Financials Exploded Report | Reporting: Brings all planned and actual expenses into one analytic model, breaking out important account key attributes into dimensions for ease of grouping and analysis. | cd_Scenario |
| cd_Year | ||
| cd_Month | ||
| cd_Budget Type | ||
| cd_Account | ||
| cd_Object | ||
| cd_Fund Source | ||
| cd_Contract | ||
| cd_Sub Facility | ||
| cd_Facility | ||
| cd_Report Entity | ||
| cd_Entity | ||
| cd_Lab | ||
| cd_Division | ||
| cd_Federal Award | ||
| cdm_Financial Exploded | ||
| cc_Scenario Assumptions | Limited Data Entry: Used to define overhead rates, benefit rates, and default salary increases and work time % for an entire scenario. | |
Below is a listing of the UCAR dimensions and their usage in the UCAR planning model:
Dimensions
| Dimension Description
|
| cd_Scenario | Used to house various what-if budget versions, actual data, encumbrance data. See Scenario Management for more information. |
| cd_Employee | List of employees (PEID) with name as alias, rolling up to HR org. |
| cd_Position Nbr | Employees who have more than one current position need multiple position numbers. |
| cd_Employee Record | Line numbers used for adding multiple planned salary, FTE, and/or position changes over a planning period. |
| cdm_Employee Record | Measure dimension used to hold salary and position information about the employee (e.g. FTE, Position Code, salary, salary adjustmnent %, etc.) |
| cd_Line Num | Line numbers used to add line item detail in cc_Employee Assumptions and cc_Non Salary Planning cubes. |
| cdm_Employee Assumptions | Used to define CSC Division Assignments and CSC Begin and End dates for employees. |
| cd_Year | Time dimension: Used to aggregate by Fiscal Year |
| cd_Division | Listing of the UCAR divisions. |
| cdm_Division Assumptions | Measure dimension used to capture the CSC rate for a division. |
| cd_Account | Account Keys organized into a Financial Hierarchy used for planning and reporting expenses. |
| cd_Budget Type | Budget categories that exist in IFAS/BiTech that need to be reported on: Uncommitted Carryover, Committed Carryover, Budget Transfers, New Funds. [Expected Funds??] |
| cdm_Employee Account Allocation | Measure dimension used to capture an employee's % on an account and a begin and end date to that assignment. |
| cd_Date | Time dimension: Used to hold date by day to enhance precision of calculations. |
| cdm_Employee by Account | Measure dimension used to capture an empployee's hours on and account. Used in cc_Employee by Account for users who prefer the hourly data entry method for assigning employees to an account by Quarter or Month. |
| cd_PayPeriod_Expense | Time dimension: Used to aggregate employee expenses by Pay Period, month, quarter, and year |
| cd_Object Employee | Expense classes and object codes that pertain to employee salaries and employee-specific expenses (e.g. salaryies, benefits, CSC charges). |
| cd_Object | A complete listing of all IFAS/Bi-Tech Expenses Classes and Object Codes, used for plannng non-salary expenses and analyzing all expenses and encumbrances (planned, actual, etc.). |
| cd_Contract | Derived from the IFAS/Bi-Tech Contract Code attribute of an account key. |
| cd_Fund Source | Derived from the IFAS/Bi-Tech Fund Source Code attribute of an account key. |
| cd_Facility | Derived from the IFAS/Bi-Tech Facility Code attribute of an account key. |
| cd_SubFacility | Derived from the IFAS/Bi-Tech SubFacility Code attribute of an account key. |
| cd_Entity | Derived from the IFAS/Bi-Tech Entity Code attribute of an account key. |
| cd_Lab | Derived from the IFAS/Bi-Tech Lab Code attribute of an account key. |
| cd_Division | Derived from the IFAS/Bi-Tech Division Code attribute of an account key. |
| cdm_Employee Plan to Actual | Measure dimension: Used to house measurements for salary planning (Amount, Hours. Person Year, Percent in Key) |
| cdm_Non Salary Planning | Measure dimension: Used to house measurements for non-salary planning (Amount) |
| cd_Month | Time dimension: Used to aggregate by Month. |