FAQ & Support - Business Intelligence Competency Center

IMPORTANT: ACCESS TO PAYROLL CHARGE DATASET IS RESTRICTED. PLEASE CONFIRM WITH YOUR SUPERVISOR THAT YOU HAVE THE CORRECT HR PERMISSIONS TO ACCESS THE DATASET PRIOR TO SETUP.

If you are trying to connect to the SFA Simplified Dataset, refer to the SFA/XL Connection Guide instead.

This guide outlines the steps needed to set up an initial connection to the Payroll Charge database. Once created, this connection will be saved and can be accessed easily.

Setting up the initial connection:

After installing the SAP HANA Client Software, please do the following:

1. Connect to the VPN via the Cisco AnyConnect program. Your BoilerKey will be required to log on. The following Purdue VPNs will allow access:

webvpn.purdue.edu
webvpn2.purdue.edu
zonevpn.itap.purdue.edu/sapbpr

2. Once connected to the VPN, open Excel.

For Office 365, using the search feature at the top of the spreadsheet, search for ‘Data Connection Wizard’. Select “From Data Connection Wizard (Legacy)” from the search result.

For other versions of Excel (2007, 2010, 2013 or 2016), on the Data tab in the Ribbon, click “From Other Sources”. In the drop down menu that appears, click “From Data Connection Wizard”.

3. In the Data Connection Wizard, choose “Other/Advanced” and click Next.

4. In the Data Link Properties window, on the Provider tab, choose “SAP HANA MDX Provider” and click Next. Note: if you do not see this option, the Excel for HANA add-on was not properly installed on your machine. Check with your local IT department for help with installation.

5. Complete the fields on the Connection tab with the information below. Note that you do not need to fill in the User and Password fields unless instructed. Copy and paste the URL below into the Host field and complete the other fields as pictured.

pubip1.epilabs.purdue.edu

Click the “Test Connection” button. If the popup that appears says “Test Connection Succeeded”, you may click OK to proceed to the next step. If you get any other message after clicking “Test Connection”, please contact the Treasury Reporting team at treasreporting@purdue.edu for assistance.

NOTE: If you are a Regional user (and have not previously created a successful connection), your account will require additional setup. Please contact treasreporting@purdue.edu for assistance.

6. In the Select Database window that appears, ensure that the PAYROLL_CHARGE_DATASET_S is selected and click Next.

7. On the 'Save Data Connection File and Finish' screen, click Finish.

8. In the Import Data window, ensure that PivotTable Report is selected and click OK.

Congratulations! You now have a connection to the Payroll Charge dataset. If you have previously set up a connection to the Simplified Dataset, keep in mind that you now have two connections to choose from when creating a new workbook (Simplified Dataset and Payroll Charge). Workbooks with an existing connection will default to their previous connection.

To reconnect after setting up the initial connection:

On the Data tab, click “Existing Connections”.

Select the Payroll Charge Dataset from the list of available connections and click Open.

Choose where you would like your Pivot to be located (Existing or New worksheet) and click OK.

Pre-requisites & System set up

In order for users to have access to the tool, they must follow the steps below.

Cognos role HR Level 1 or higher, HR Level 2 or higher is needed when using wage type number or name fields.
If the user has never used Excel Pivot Tables, they can take an online course to familiarize themselves with the tool. See LinkedIn Learning for Excel Pivot Table training opportunities.
Refer to the Payroll Charge Dataset Connection Guide to set up your initial connection.

Data Concepts

The tool uses the Payroll Charge Dataset as its source. Below is information that may help in understanding how these terms are used.

Fields with “Organization” (i.e. Business Area Display – Organization) are from the perspective of the Department’s cost center hierarchy. Use these fields to view data based on the employees in a department.

Fields with “Reporting” (i.e. Financial Unit Display – Reporting) are from the perspective of the Parent Funded Program’s cost center hierarchy. Use these fields to view data based on the account charged.

Source field identifies if the data is from Pay_Charge or FM (Funds Management). The data from payroll charge are transactions that route through the payroll process. The data from FM are compensation transactions that did NOT route thru the payroll process. i.e. JV processed for moving fringe benefits.

Central Fringes Flag, this flag is “Y” when fringe benefits are charged to a central university account

Employee Class/Group is a combined field that includes the employee group field for transactions prior to 1/1/2019 and employee class for transactions 1/1/2019 forward; employee class is also available

Hierarchical Fields

Some fields are available in both a hierarchical field and as a stand-alone field. You should be aware of which you are choosing as they act differently within the pivot table and filters.

Hierarchical

Commitment Item – contains all levels from Category 0 to Commitment Item.

Cost Center Organization - contains all levels from Company to Cost Center from an Organization point of view

Cost Center Reporting - contains all levels from Company to Cost Center from a Reporting point of view

Wage Type Hierarchy – contains category level 1, 2 and wage type display

Stand Alone

Campus Display – Organization	Campus Display – Reporting
Segment – Organization	Segment – Reporting
Business Area Display – Organization	Business Area Display – Reporting
Financial Unit Display – Organization	Financial Unit Display – Reporting
Financial Sub Unit Display – Organization	Financial Sub Unit Display – Reporting
Program Display - Organization	Program Display - Reporting
Cost Center Display – Organization	Cost Center Display – Reporting
Category 1	Wage Type Category Level 1
Category 2	Wage Type Category Level 2
Category 3	Wage Type Display
Category 4
Commitment Item Display

Cautions

General/Tool

You MUST be logged onto VPN. If you are not, you will get the error message shown below.

Data

There are a limited number of years’ worth of data included in this dataset, Fiscal Year 2019 - present at initial population.
Fields with “Organization” (i.e. Business Area Display – Organization) are from the perspective of the Department’s cost center hierarchy. Use these fields to view data based on the employees in a department.
Fields with “Reporting” (i.e. Financial Unit Display – Reporting) are from the perspective of the Parent Funded Program’s cost center hierarchy. Use these fields to view data based on the account charged.
Funded Program attributes are primarily from the Parent Funded Program perspective.

Sharing Files with Others

You can share your spreadsheet with individuals who do not have the software on their machines, but they will be unable to make any changes to the file. The below error will display if they try to make a change.

Validation Sources

The Statement of Payroll Charge standard report can be used to validate transaction with a Source of Pay_Charge. The Statement of Payroll Charge standard report “Compensation Transaction NOT included in Statement of Payroll Charge – EXCEL” to validate transactions with a Source of FM.

Any of the SFA version can be used to validate at a commitment item level when using fields containing “Reporting” for the account that was charged. Pay close attention to notes on filters in the header of the SFA reports to ensure you are filtering on the same items.

Refreshing your Data

Select the Data menu ribbon (or Analyze menu ribbon), and then click Refresh or Refresh All to refresh all pivot tables. As with regular pivot tables, you can also right click the pivot table and select Refresh Data.

OR

Unhook your file from the Data Source so that it doesn’t update

If you have created a dataset and don’t want the numbers to be updated, you can remove the link to the dataset. Click the Data menu item, then select Connections. Highlight the connection and click Remove. You will get a warning dialog box. Click OK.

Troubleshooting

VPN

You MUST be logged onto VPN. If you are not, you will get the error message shown below.

Refresh Error

In some cases, the error shown below is received when trying to refresh data. After the error, click Refresh Data again, and it will work.

Setting up the initial connection:

After installing the SAP HANA Client Software, please do the following:

1. Connect to the VPN via the Cisco AnyConnect program. Your BoilerKey will be required to log on. The following Purdue VPNs will allow access:

webvpn.purdue.edu
webvpn2.purdue.edu
zonevpn.itap.purdue.edu/sapbpr

2. Once connected to the VPN, open Excel.

For Office 365, using the search feature at the top of the spreadsheet, search for ‘Data Connection Wizard’. Select “From Data Connection Wizard (Legacy)” from the search result.

For other versions of Excel (2007, 2010, 2013 or 2016), on the Data tab in the Ribbon, click “From Other Sources”. In the drop down menu that appears, click “From Data Connection Wizard”.

3. In the Data Connection Wizard, choose “Other/Advanced” and click Next.

4. In the Data Link Properties window, on the Provider tab, choose “SAP HANA MDX Provider” and click Next. Note: if you do not see this option, the Excel for HANA add-on was not properly installed on your machine. Check with your local IT department for help with installation.

5. Complete the fields on the Connection tab with the information below. Note that you do not need to fill in the User and Password fields unless instructed. Copy and paste the URL below into the Host field and complete the other fields as pictured.

pubip1.epilabs.purdue.edu

Click the “Test Connection” button. If the popup that appears says “Test Connection Succeeded”, you may click OK to proceed to the next step. If you get any other message after clicking “Test Connection”, please contact the Treasury Reporting team at treasreporting@purdue.edu for assistance.

NOTE: If you are a Regional user (and have not previously created a successful connection), your account will require additional setup. Please contact treasreporting@purdue.edu for assistance.

In the Select Database window that appears, ensure that the SFA_SIMPLIFIED_DATASET is selected and click Next.

On the next screen, click Finish.

In the Import Data window, ensure that PivotTable Report is selected and click OK.

Congratulations! You now have an active connection to the SFA Simplified Dataset.

To reconnect after setting up the initial connection:

On the Data tab, click “Existing Connections”.

Select the SFA Simplified Dataset from the list of available connections and click Open.

Choose where you would like your Pivot to be located (Existing or New worksheet) and click OK.

In order to make financial data more accessible to business users, a new Excel on HANA tool is being implemented. This tool allows users to drag and drop fields directly into an Excel pivot table. The tool uses the SFA Simplified Dataset as its data source. Like the SFA Simplified Dataset and other Boiler Insight stars, the data is updated nightly.

Pre-requisites & System set up

In order for users to have access to the tool, they must follow the steps below.

If the user has never used Excel Pivot Tables, they must take an online course to familiarize themselves with the tool. See LinkedIn Learning for Excel Pivot Table training opportunities.
Ensure the software has been pushed to your machine. Data related questions contact FP&A at TreasReporting@purdue.edu and software questions contact BICC@purdue.edu.
Refer to the SFA/XL Connection Guide to set up your initial connection.

Data Concepts

The tool uses the SFA Simplified Dataset as its source. In general, you will see some new terms added to field names. Below is information that may help in understanding how these terms are used.

Posting – This indicates where the actual posting in SAP/S4 occurred. More commonly, in fiscal years prior to 2019, these could be different from the Reporting fields. This term is used with Funded Program and Cost Center Hierarchy.

Reporting – This term refers to who owns the master data element, not necessarily where the posting happened in SAP/S4. This term is used with Parent Funded Program and Cost Center Hierarchy.

Trend – This field indicates that logic has been added to postings prior to fiscal year 2019 to help align postings with the new master data elements implemented in 2019 for trend purposes.

A best practice would be to stay consistent with these fields within your view, i.e. if you are using Reporting fields, always use reporting fields, don’t combine Reporting with Posting. Or, if you are looking at multiple years, use Trend fields consistently.

Hierarchical Fields

Some fields are available in both a hierarchical field and also as a stand-alone field. You should be aware of which you are choosing, as they act differently within the pivot table and filters.