MyFloridaMarketPlace
11.0 Data Analysis – Instructor NotesCourse Overview / Analysis is the MyFloridaMarketPlace tool that gives users the power to base buying decisions on your own intelligent, self-customized analyses of data from other Ariba products. The goal of Ariba Analysis is to improve your ability to see spending patterns to identify opportunities to decrease costs. It is also a tool for examining purchasing processes themselves for possible areas of improvement to save money.
Course Length / 4 hrs
MFMP Users / Purchasing Agents, Finance & Accounting
Course Objective / After training, participants will be able to:
• Create analytical reports
• Save and edit analytical reports
• Add reports and additional tabs to the Dashboard
• Edit analytical report and create computed fields
• Create Analysis folders
• Export Ad-Hoc reports to Excel and create CSV data files
Prerequisites / • Basic computer skills
• Basic understanding of pivot tables
• Internet skills
• Knowledge of the FL procurement policies and procedures
• General knowledge of critical data and report design
• System Basics training course
• Functional knowledge of Microsoft Excel (recommended)
Key Talking Points
Lesson 11.1: Create Ad-Hoc Reports / Ariba Analysis has three ways to work with reports:
§ Creating analytical reports with the Analysis Wizard: A guided process aids you in designing analytical reports. Pivot tables, the end product of the Analysis Wizard, are commonly used by the power user who is investigating deep aspects of an organization’s spend in complex data analysis.
§ Running a parameterized report: You only have to select the exact data you want to see, because someone else has already determined the format of the analytical report for you. Parameterized reports are for the business user who wants to run common reports frequently and quickly.
§ Designing a compound report: A guided process assists you in building a compound report, which is the composite of data from multiple spend objects into a single report. The content of a compound report comes from analytical or parameterized reports you have already created in Ariba Analysis.
Accessing Ariba Analysis
§ The Ariba Analysis tool can only be accessed through Ariba Buyer. Users which have been given the role ‘Analysis Authorized’ will have an Analysis section displayed at the bottom of the shortcut menu.
§ Once you have logged into Ariba Analysis, a new web browser window will be opened and a new session will be created. Your session will be terminated after 15 minutes of inactivity. If terminated, you will need to re-establish your connection to Ariba Analysis.
Ariba Analysis Database
§ Ariba Analysis works with a database independent of other Ariba products to ensure the utmost speed of all Ariba applications. Data from other products must be loaded into the Ariba Analysis database. As a result of the need to load data, your analytical reports will not be ‘real-time’.
Using the Analysis Wizard
§ The Analysis Wizard guides you through creating a pivot table, which is the basis of an analytical report. You also invoke the wizard when you want to edit a saved report.
§ The purpose of the wizard is to help you create your report quickly. Keep in mind that you can still change your analytical report after you're finished with the wizard. That is, after you create the report, you can alter its appearance easily, drilling down into the data, moving data from one location on the pivot table to another, changing titles, and much more.
Wizard Step 1: Select Source Data and Measures: There are several predefined spend objects (facts) available for investigations:
§ Purchase Orders: commodity purchasing patterns
§ Invoices: supplier billing patterns
§ Requests: general term for requisitions
Report title format: When entering a title for your report, it cannot be longer than 50 characters or contain any of the following special characters:
§ backslash \
§ slash /
§ semicolon :
§ asterisk *
§ question mark ?
§ quotation mark “
§ less than <
§ greater than >
§ pipe |
Wizard Step 2: Pivot Layout: You must select the hierarchies you want to include in your analytical report and put them in locations on the pivot table. You can choose any of the locations:
§ Page field – as many hierarchies as desired may be added
§ Row field – at most 2 hierarchies may be added
§ Column field – at most 2 hierarchies may be added
Wizard Step 3: Refine Data: In the final step of the wizard, you must refine the data you want to investigate. You can refine data in the following ways:
§ Choose the date range you want (Calendar or Fiscal)
§ Include or exclude partial units of time (year, quarter, or month)
§ Constrain data for any of the selected hierarchies
Troubleshooting an Analytical Report: If Ariba Analysis encounters difficulty with your analytical report, it directs you to the Analysis Wizard to edit or change your analytical report in such a way that it can be processed. Common troubleshooting tasks include:
§ Long queries: Almost every operation in Ariba Analysis results in a query against its database. If the query times out (3 minutes), Ariba Analysis will ask you to alter the parameters of your analytical report such that the query can be executed.
§ Large data returned: You might choose hierarchies that represent an amount of data so large that it exceeds the established thresholds.
o Pivot table maximum rows to display: 201
o Pivot table maximum columns to display: 25
o Inspect line item details maximum records: 1,000
Lesson 11.2: Save Ad-Hoc Reports / You are prompted to save your analytical report if you exit the wizard without first saving the report. You are giving the two options when saving:
§ Save: stores the analytical report with the title you specified in Step 1 and overwrites any other saved analytical reports with the same title
§ Save As: allows you to make a copy of the analytical report under a different title and in a folder of your choosing
You can click the Discard button to not save any changes made to your report.
Lesson 11.3: Dashboard Navigation / The dashboard is a single screen on which you can organize and access key tasks, actions, and information. Rather than switching between requests and reports, you can simply add the items to your dashboard. You can then monitor and access the items from one convenient location.
You can add additional tabs to your dashboard by clicking the Customize button.
§ When entering a title for your tab, it cannot be longer than 30 characters any may contain special characters.
§ You will need to select the appropriate tab before adding the report to the dashboard.
§ Reports may be added to your dashboard as the following:
o Pivot table
o Chart
o Folders
§ After adding a report to your dashboard, you can refresh the data by:
o Launching the report
o Click the Force Refresh all content on this page
Lesson 11.4: Edit Ad-Hoc Reports / You can alter the data field view to show the measure in any of the following ways:
§ Normal (a number)
§ Percentage of Row:
§ Percentage of Column:
You can also change the number format to display the values as:
§ Normal
§ Currency
§ Percentage
If you decide to show numbers in currency format, you can select the currency symbol Ariba Analysis displays next to the amount, or None.
Creating Computed Fields
§ In Ariba Analysis you can define a calculation to create a measure not already available with a spend object. Such a calculation is called a computed field or a derived measure: the result of a calculation based on other measures.
§ Only the measures related to the spend object you are investigating are available. For example, if you are investigating purchase orders, you cannot compute a new field based on data about invoices.
Lesson 11.5: Create Folders / You can organize your saved analytical reports folders you create, moving them from folder to folder, deleting and renaming folders, making copies and other functions. You can also share your analytical reports with other Ariba Analysis users.
Ariba Analysis comes with two default folders: Favorites and Public. There are system folders and they cannot be deleted, renamed, or moved into other folders. Saved reports and other things you can add to the dashboard are called Folder Items. Ariba Analysis lists folder items at the top of the folders view, or you can store them in subfolders.
Personal folders names cannot contain any of the following characters:
§ backslash \
§ slash /
§ semicolon :
§ asterisk *
§ question mark ?
§ quotation mark “
§ less than <
§ greater than >
§ pipe |
Sharing Reports with the Public Folder: Your public folder is a convenient way to allow other Ariba Analysis users to see and copy your analytical reports. To share an analytical report, move or copy it to your Public folder.
You can view the public folders belonging to all Ariba Analysis users by clicking the hyperlink All Folders. In the Public folder, you can search to locate a particular user’s public folder name.
Lesson 11.6: Export Ad-Hoc Reports to Excel / You can export (download) the data from an analytical report to your computer for use with spreadsheet applications such as MS Excel. To export the data to MS Excel, you must use the IE Explorer 5.5 or higher. If you use some other browser, you can only export using the comma-separated values (CSV) format.
Ariba Analysis uses ActiveX Scripting in your browser to send data to MS Excel on your computer. Before attempting to export data, configure your browser to allow the Ariba Analysis server to send data to your computer.
There are two parts to configuring the Excel export:
§ Selecting a format
§ Selecting the data
You can export data in two formats:
§ Excel 2000 or Excel 2002 (MS Office XP): You must have Microsoft Excel 97, 2000, or 2002 installed on your computer to use this export format. Your Web browser contacts Excel on your computer to display the data you want to download.
§ Comma-separated values (CSV): Select this radio button if you want a data file with Comma-separated Values (CSV) that can be read into older versions of Excel or some other application. The file includes one header row that defines the columns in the file, and the rows have cells delimited by commas.
o You can add the file extension .csv to the filename when Ariba Analysis prompts you for the location to save the file. This extension will allow you to double-click the file to launch Excel.
Unless you have selected a template that uses fixed columns, you have two choices for the data fields you can export: Current pivot table contents or Custom. If the template you have selected for exporting contains fixed column, the exported data are defined by those fixed columns. You cannot alter the data during export.
You can select how the data is exported: Aggregate Data or Line Details
§ You have a choice about the kind of data you want to export:
o Aggregated Data measures are rolled-up such as amount, split, or count which you selected as the basis for your analytical report
o Line Details allows you to export information such as PO ID, PO line number, Split accounting number and description.
§ Warning: Do not export line details or the lowest level of a hierarchy without some careful thought. You may be unknowingly selecting too much data to export.
Exporting Computed Fields
§ You cannot export computed fields from Ariba Analysis to Excel. The reason is that computed fields cannot be properly recalculated on export. Your data is exported without computed fields. If you want to recreate the computed fields, you should export all the fields on which you have based the computations and then in Excel recreate the formulas.
After the export is complete, Ariba Analysis tells you how much data was actually exported by displaying the number of rows sent to your computer. Analysis saves the export settings you associate with an analytical report.
Data Limitations in Exporting to Excel: You should be aware that MS Excel has limitations that can possibly affect the data you export to it:
§ Ariba Analysis can export only 64K rows to Excel. Excel silently loses any rows exceeding this limit.
§ Ariba Analysis imposes a limit of 250 characters per cell on the data it exports to Excel because any cells longer than that causes Excel to fail.
§ If you are using a proxied MS Internet Information Server (IIS) running securely (with SSL or HTTPS), that server must use the default SSL port: port 443. Any other port causes Excel to fail to retrieve data from Ariba Analysis.
Last Updated on 11/22/06 Page 6 of 6