Reporting & Data Analysis

Question 1

6.1 Reporting strategy

Answer 1

• The reporting strategy must meet the needs of the fleet personnel, clients and stakeholders.

Question 2

6.1.1 What are the key components of the reporting strategy? (p.168)

Answer 2

• Audience Analysis & Planning for Reports
• Planned (Canned) Reports: Including SLA & Exception ( Preparation & Transmission)
• Ad Hoc Reports Including Congrol Process for Requests
• Data and Report Accuracy & Validation Method plus Implementation

Question 3

6.1.2 What are some things to be included in the report that must be decided on? (p.168)

Answer 3

• If canned reports are already available then it is best to start with these to ensure that you set the correct expectations in terms of the report content and format.
• Whether the reports will be available online for the audience to pull or if the system will push out the reports to them.
• The parameters around downloading and storing the reports must also be decided
• The report format (e.g. Excel, CSV, or PDF)
• Common or distributed storage
• Update and transmission frequency, etc.

Question 4

6.1.3 What are actionable reports and how are they used? (p.169)

Answer 4

• Critical area to include in the reporting strategy is the set of actionable reports which enable the fleet personnel to quickly point to a problem area and to take action
• This is accomplished with “Exception reports”.

Question 5

6.1.4 What should be your strategy towards running ad hoc reports? (p.169-170)

Answer 5

• Minimize the need for “one-off” reports by planning ahead.
- A guiding principle is to the extent possible use the same report for multiple purposes.
• Use self-serve tools.
- Often, a single person usually the data analyst, is assigned to take the requests for ad hoc reports and to execute the query.
• Include the steps or pathway starting from the report request, to data collection or retrieval, to data analysis and finally the actual report creation and transmission.
- The request for ad hoc reports needs to be controlled and managed.
• In the event that the same type of ad hoc report is being requested often or by multiple parties, consider making it part of your planned report inventory which you generate proactively.

Question 6

6.1.5 What is the data and report validation step? (170)

Answer 6

• Above all, your reporting strategy must include a data and report validation step.
• It is the single most important element before sharing any report or data

Question 7

6.2 Report Builders and Report Readers

Answer 7

• It is not about software query tools such as SQL Report Builder.
• About the people within the organization who build reports and people who need them.

Question 8

6.2.1 What are the attributes of report builders? (p.170-171)

Answer 8

• Have strong technical skills related to data entry, data query and report development using tools and applications such as SQL query server, Excel, Access, etc.
• Know the database table content and structure and the data which is available from the system.
• Are able to deconstruct a report request into the data field requirements and the mathematical manipulations required to create the desired information.
• Have working knowledge of the fleet functions and operations from a business perspective.

Question 9

6.2.2 What different categories can report readers be broken up into? (p.171)

Answer 9

• Fleet personnel who need information and reports to do their day to day jobs. (daily garage performance metrics as a dashboard on his/her landing / page on the FIMS.) vehicle acquisition and provisioning, fuel, and administration.
• Finance and fleet managers who are responsible for expense management against the approved budget.
- i.e. General Ledger account number for vehicle acquisition, maintenance and repair labor and parts, etc.). It might contain a bar chart to compare year-to-date (YTD) spending year-over-year (YOY). It might also show the problem areas (e.g. overspending) in the color red.
- The goal of this type of report is to enable the readers to identify the problematic areas quickly and focus on the remedial measures.
• Stakeholders such as the Executive who need to approve the fleet budget and who are ultimately accountable for the fleet budget.
- This type of reader needs only the salient information necessary to make the decisions required of them and to review the fleet KPIs.
• Fleet clients who need to manage their own budgets and to review the performance metrics related to fleet, especially the results that can be improved through action taken by the fleet client.
- Preventive Maintenance compliance percentage and the drill down on the percentage of vehicles showing up for the PM appointment.

Question 10

6.2.3 What are some types of data that would be used in a fleet study? (p.171)

Answer 10

• Performance metrics related to fleet, especially the results that can be improved through action taken by the fleet client
• Preventive Maintenance compliance percentage and the drill down on the percentage of vehicles showing up for the PM appointment.

Question 11

6.2.4 What types of information might a company executive want to see regarding the fleet? (p.171)

Answer 11

• This type of reader needs only the salient information necessary to make the decisions required of them and to review the fleet KPIs.

Question 12

6.3 Information requirements

Answer 12

• More often than not, it takes more than one conversation to finalize the content and the format of the information which the business requires and at best, it’s an iterative process

Question 13

6.3.1 What are the guiding principles of the Iterative process flow? (p.174-175)

Answer 13

• Conversation with the intended audience to understand their goals (or problems) and how the required information will be used to achieve their goals
  • Document and maintain the relational diagram of the information and the data sources used.
  • Document and keep all the assumptions, inferences, formulae, descriptions of nomenclature being used, etc.
  • Document and share the outcome of discussions with the stakeholders.
  • Document how the information will be used and by whom.
  • Keep all versions of the Report as the Report evolves.
  • Ensure that the Reports can be systemized, and automate to the extent possible.
  • Only set expectations which you can deliver cost effectively – don’t mismanage expectations and don’t spend an unreasonable amount of time to meet unreasonable requests. Remember that time is money!
  • Periodically revisit the need for the Report and the adequacy of the Report with the stakeholder – revise the Report if required.

Question 14

6.3.2 What is the suggested approach to use when identifying and finalizing information with stakeholders? (p.175)

Answer 14

1. Conversation w/ Stakeholder after problem is identified and you have a goal / Info requirement / use / Data sources
2. Premilinary Report Review: Assumptions / Data sources & process / Info review
3. Draft Report Review: Validation of required info / Frequency and method to obtain
4. Final Report Review: Review for any other revisions / Full Agreement / Establish recipient list / Finalize transmission method
5. Implementation: Develop and produce Report in system / Systemize Transmission / Notifiy recipients / provide training

Question 15

6.4 Data freshness

Answer 15

• The system and database designers need to balance the use of system resources between both data freshening and running the applications

Question 16

6.4.1 What should you consider when deciding how fresh you want your data to be? (p.176)

Answer 16

• The problem is that the system response time can become too long and therefore unacceptable for the application transactions if the data freshening is extensive due to sharing the same system resources.

Question 17

6.4.2 What is the system designer’s goal? (p.176)

Answer 17

• To optimize the system Quality of Service (QoS) metrics including response time, throughput, and availability and Quality of Data (QoD) metrics which are mainly about data freshness and accuracy.

Question 18

6.4.3 What are “caching techniques”? (p.176)

Answer 18

• There are “caching” techniques which are used extensively in web based applications to store or cache the website address or the actual data the last transaction yielded
• This technique is used to improve the response time for the user and to reduce the load on system resources

Question 19

6.4.4 What is base data? (p.176)

Answer 19

• In terms of maintaining data freshness, it is important to understand the concept of base data and the derived data tables.
• The base data is “temporal” in that it changes with time (an example is the maintenance and repair data for a given vehicle which is updated in the base data as the work order is completed)

Question 20

6.4.5 What are the derived data tables? (p.176)

Answer 20

• On the other hand, derived data tables result from computations or other operations done on the base data.
  
  • There are two commonly used methods for data freshness in the derived tables
    > Immediate & On-Demand Updates
• Immediate update, the data is updated as soon as a new value is available which keeps the data fresh in real-time, - can slow down the application runtime on average
• On-Demand method, the data is updated when they are accessed by incoming application transactions, which ensures a comparatively more responsive system but the base data and derived data tables can be out of synch.

Question 21

6.5 Level of detail

Answer 21

• Having a meaningful conversation with the intended audience and balancing their expectation with the actual system reporting capability will determine the right level of information for the report.

Question 22

6.5.1 Give some examples of fleet personnel who use reports in their day to day activities. (p.177)

Answer 22

• Someone who is responsible for fuel will want to see the fuel exception transactions every day
• An operations manager would want to see a near real-time report of open work orders at each repair facility
• A parts manager may want to run parts inventory reports periodically.

Question 23

6.5.2 What are the two categories of summary reports? (p.177)

Answer 23

• The first category is usually required by managers who are responsible for fleet operations and budgets to see performance metric trends, track actual spending against budget, create corrective action plans and so on.
  
  • Fleet personnel, fleet finance and fleet clients are users of this category of summary reports.
  • The following table provides an example of the types of metrics to be included in this category of reports – it is intended to be only for illustration purposes – organizations may require additional or fewer metrics
• The second category of summary reports is for the senior managers and executives who approve plans consisting of projects, initiatives, and capital and expense budgets for the future years.
• These summary reports need to include year over year trends for fleet KPIs and budget requirements based on history but projected for the future.

Question 24

6.5.3 What types of information might be included in a summary report intended for management? (p.177-178)

Answer 24

Performance metric trends, track actual spending against budget, create corrective action plans and so on.

Question 25

6.5.4 List some of the guiding principles for the second category of summary reports. (p.179)

Answer 25

• Capture all assumptions, data exclusions and inclusions, formulae for calculations, data scrubbing, etc.
• Always start with the detailed data and keep track of how you got to the summary. This will be useful for repeating the same exercise to create different alternatives (e.g. vehicle replacement plan options to match the budget).
• Create separate summaries for each fleet client since usually the executive team will include members from the fleet client departments.
• Ensure that each summary report you create is repeatable and as automated as possible. Leverage system capabilities to the extent possible.
• Keep all versions of summary reports.
• Make sure there is an ability to drill down – bottom up preparation of summary reports is really important.
• Make the reports simple, use colors for visual impact and messaging.
• Only provide data which supports the conclusions, key messages, etc.
– avoid the tendency to impress the executive with data which is not necessary.
• In the case of data which is outside of the range of normal whether favorable or unfavorable, be prepared to answer the question, “How did you get this information?”

Question 26

6.6 Reporting applications

Answer 26

• A best in class COTS FIMS includes reporting capabilities and it becomes a matter of learning these capabilities and becoming proficient at using them.
• The goal for reporting is to automate and leverage systems and applications to the extent possible – this is the most effective approach for repeatability and to reduce manual work.

Question 27

6.6.1 What is a report writer and what are its uses? (p.180)

Answer 27

• AKA Report Generator : Report Writer software as part of a database management system which extracts information from one or more files and data sources and presents the
  information in a specified format.
• The fleet manager who knows how to run report writers for common databases can produce powerful reports in a relatively short amount of time
• Examples of Report Writer applications are SQL Server Reporting Services (SSRS) and Crystal Reports
• You don’t have to wait for IT resources to run the reports and the report writers are more efficient to use than manipulating large data files in applications such as Excel and Access

Question 28

6.6.2 What are Ad hoc reporting tools and how are they used? (p. 180)

Answer 28

• Ad Hoc reporting tools are used to provide “just in time” information quickly and cost effectively.
• The simpler Ad Hoc Reporting tools allow the reading of data for query and reporting purposes