The Data Warehouse contains the Position Funding Cube, which provides payroll expense details and encumbrance amounts by chartstring by employee. The report can be used as a personal services detail or payroll cost report. The cube includes Payroll Actuals from 7/1/2018-12/31/2018 data that has been added from legacy systems.
In this article, you will learn the steps to download and view the Position Funding Cube from the Data Warehouse.
Start by connecting to your VPN, then point your browser to dwreports.uga.edu . Follow the prompts to log in using your MyID and password.
First log into UGA Data Warehouse
After you have logged in, navigate to the cube using the following folders:
All UGA Faculty and Staff Reports > Financial-HCM
In the Excel Workbooks section, click the Position_Funding_Cube.xlsx link.
The Position_Funding_Cube.xlsx file is downloaded to your computer.
Click the Open button.
If presented with a security warning, click the Enable Content button.
Click the Row Labels cell to activate the PivotTable Fields.
The PivotTable Fields menu is displayed on the right side of the screen.
To view data by department, scroll down and drag the Dept ID Descr UGADEPTBUDGRTP field to the FILTERS quadrant.
The Dept ID Descr UGADEPTBUDGRPT field is now displayed in the FILTERS quadrant and in the PivotTable Fields list with a checkmark.
The Dept ID Descr UGADEPTBUDGRPT field is also displayed on the report in Row 1 of the sheet as a filter applied to the data.
Click the drop-down list of the Dept ID Descr UGADEPTBUDGRPT All field to narrow down the data to a specific departmental tree.
Note : Selecting a specific tree will restrict the cube to only show data for this departmental tree.
The Search Dept dialog box is displayed.
Click the checkbox for the Select Multiple Items option.
Click the All tree [+] icon to expand the tree.
All the departments are displayed.
Deselect the All tree item by clicking the checkbox.
Click the [+] Expand button for the 00000000 - Departments tree item.
Select the department(s) for which you want to view data.
Click the OK button.
The selected department is displayed in the top row.
The Budget Amount, Encumbrance Amount, and Actual Amount for the selected department(s) are displayed according to the Accounting Periods in column A.
Click and drag the ACCOUNTING_DATE field from the ROWS quadrant back to the PivotTable Fields to remove this data from the page.
The Accounting Date data is cleared from the page.
You can add additional fields to analyze your data by dragging the field to the appropriate quandrant (Filters, Columns, Rows, or Values).
Example shown here: Pay Group Code Descr field moved to the ROWS quadrant.
The different pay groups are displayed in the Row Labels.
Drag the PAY_END_DATE.Fiscal Date item to the ROWS quadrant to view information by Pay End Date as well.
Click the [+] icon for the FY1900-#NA field to expand the tree to display the Fiscal Yr Acctg Pd data.
The tree item is expanded. Right-click the item.
Example shown here: 1900-01-01 00:00:00.0000000 tree item.
A pop-up menu is displayed. You can click Show/Hide Fields to show or hide any of the rows.
Example shown here: Show/Hide Fields > Fiscal Yr.
Follow the same steps to show/hide any other data as needed.
Example shown here: Fiscal Yr Acctg Pd
The selected data is displayed.
Example shown here: view the information according to employee.
In the PivotTable Fields, scroll down to drag the Name Emplid BadgeNo item under the EMPLOYEE field to the ROWS quadrant. This breaks down each Paygroup by Employee.
Pay Group data is displayed by Employee ID.
In the PivotTable Fields section, click the Expand button to expand the More Fields tree item.
Drag the Fund Code Descr tree item to the ROWS quadrant.
The information is now displayed by Fund Code.
Click the Expand button before the More Fields tree item.
Scroll up and drag the GL Account ID Descr to the ROWS quadrant.
The Cube now shows Payroll budget by Fund, Department, and Account Code.
Since Budget is not allocated by employee or pay date, the budget shows up under 1900-01-01 with an #NA Pay Group and Employee Name.
Column B shows the Budget Amount for each level: fund, department, and account.
Scroll down to view encumbrances (Column C) displayed by Pay Group, Employee, and Employee funding (Fund/Dept ID/Account Code).
Note : Because Encumbrances are not associated with a specific pay date, all the encumbrances show up under a paydate of 1900-01-01.
Scroll down to view data in the Actuals column (Column D). The Cube shows Actual Payroll totals by Pay Date. For each pay date, it shows the Actuals by Paygroup, Employee, and Employee funding (Fund/Dept ID/Account Code).
Additional Information:
Note: Currently, you must drill through on the Budget Amount of Encumbrance Amount fields. Drill through is not available on calculated fields like Actual Amount. However, drill through returns all values for the entire row related to Budget, Encumbrance, and Actuals.
Data in the Data Warehouse gets refreshed nightly. A best practice is to review filters on a regular basis to ensure you are pulling any new data your existing filters may exclude. Occasionally, new data elements are added which may create users impacts, requiring adjustmnts to repopulate data.
Tutorial Resources
Quick Start to the Position Funding Cube
Cube Field Definitions Quick Reference Guide
How to Use a Cube
Lynda.com Training Resources