Analytics Reports MAIN WIKI

From UG

Contents 1 Info 2 Core need 3 SA notes (concept vision) 3.1 Main goal 3.2 Data 3.3 Handling 3.4 Output 3.5 Modelling 4 Suggested solution 4.1 Version 1 4.1.1 Tasks 4.1.2 Implementation phases 4.2 Version 2 4.2.1 Summary 4.2.2 Concept 4.2.3 Tasks 4.2.4 Implementation phases 4.3 Version 3 4.3.1 Note on scope 4.3.2 Methodology

Info

0003003: (Analytics Reports) Create a module to produce this type of reporting

Core need

We want to automate generation of special class of xls reports ("Utilization and Cost Saving Modeling reports"):

• based on CT2 database data
• Complex: mulitab, mix of graphics and tables on one page
• Normally related to utilization rates and costs, times
• Used as a tool for modeling of how to tweak different parameters such as transit times and utilization rates to reduce costs
• Challenge is that every time we need to pull fresh data and apply formulas and charts
• we also want to have “in system” (DR (Dashboard Reports)) version of these reports
• see 2 examples in 0003003
  • examples#1 is well defined in TMS Monthly Stats Report - see Copy of Copy of July Activity Stats Final.xlsx
  • ex#2 - see up+kpi+-+analytics+report+SAMPLE.rar

SA notes (concept vision)

Main goal

Core need:
Automate generation of special class of xls reports ("Utilization and Cost Saving Modeling reports")

That class of reports is designed to give the statistical and operational data to analyze and should help to take the business decision for reduce the cost of freight charges.

Currently there are 2 specific reports that needed to be implemented:

• Elizabeth Arden's TMS Monthly Stats Report. Algorythms of cost savings is based upon reducing the load count to Best possible Load Count.
• Coty report. Algorythm is TBD.

Data

Core need:
Report should be based on CT2 database data

Currently EA monthly statistical report is generated from Descartes TMS in the form of report called "Supplier Detail Previous Month". It contains the summary totals of achieved values (loads, weights, charges etc) for every lane "Supplier-Consignee" per given month. The same lanes with different Carriers are considered as unique.

For both mentioned reports, first task is providing of saving in CT2 database the initial data for reports. So we need to define:

• list/mapping of existing fields that can be used,
• list of new fields that should be added into DB.

Handling

Core need:
Normally related to utilization rates and costs, times
Challenge is that every time we need to pull fresh data and apply formulas and charts

First need get list of indicators for every report. Then:

• develop a procedure of input/export initial data,
• make calculation of needed indicators by given formulas.

Output

Core need:
Complex: mulitab, mix of graphics and tables on one page
We also want to have “in system” (DR (Dashboard Reports)) version of these reports

• System should provide an availability of selecting different output options - for admin and user.
• Every indicator should have its own table or chart (on common or separate page).

Modelling

Core need:
Used as a tool for modelling of how to tweak different parameters such as transit times and utilization rates to reduce costs

This as in http://en.wikipedia.org/wiki/Scientific_modelling.

TBD

Suggested solution

Version 1

It is suggested to divide all implementation tasks onto two parts: content and visualization.

Tasks

Content tasks

• Preparing of data:
  • define mapping of existing fields,
  • define list of new filds to add into DB structure.
• Getting of data:
  • develop procedure of input/export data into DB.
• Handling of data:
  • define indicators (given an=d calculated),
  • specify appropriate calculations.

Visualization tasks

• Configuration of report:
  • admin part - visibility rules, constraints and defaults,
  • set of output columns.
• Output of report:
  • versions of report - standalone/scheduled/dashboard,
  • output format - XLS/HTML,
  • output objects - tables & charts.

Implementation phases

Implementation phases should be the same for every kind of analytics report.

Phase 1. Prototype of report

• prepare DB,
• create prototype of report,
• create live "global" table using CT2 data.

Phase 2. Full report

• create flexible report,
• output of all tables and charts.

Version 2

Summary

Jaguar's operational group periodically makes analytics reports with a lot of indicators the freight activities. Usually an analytic report is Excel file that contains one tab with source data and some number of other tabs with analitic indicators. Indicators calculates by preliminary defined formulas and can be displayed as tables and/or graphic charts, each on its own tab. Process of preparing these reports takes a lot of time and resources. That is why it is suggested automatize it.

Currently the following reports require the automatization:

• TMS Monthly Stats Report. Also called "Monthly Activity Stats". It described well here. All indicators and calculations already defined.
• Coty Report.

Concept

Main idea - CT2 should be able generate source report and give it to user in spreadsheet form to further handling.

We suggest the following concept:

• Prepare CT2 database for saving all needed data
• Provide the import TMS data into CT2 database by using the existing "EDI to TMS" interface, that should be updated accordingly.
• Generate the source report from CT2 in spreadsheet form.

• For TMS Monthly Stats Report this should be analog of existing one in TMS (Supplier Detail Previous Month).

• Download the source report onto user's computer and handle it in Excel.

• Handling of source data in Excel should be executed by using macros in user-friendly way
• Cells on indicators' tabs should contain appropriate formulas (not values)

Tasks

• Get requirements for report.

• Marc as Module Owner explaines to CT2 Support group what type of report he wants to create (tabs, tables, pies, formulas explained with examples).

• Create specifications.

• The Support group creates report specification and list of tasks to implement its automatization.

• Provide saving data in CT2 database.

• Update DB structure if needed.

• Create procedures to configure and output of report.

• Work out procedures to provide configuring and output of the source report.

• Create macros in Excel for handling the source report.

• Each macro should calculate and display the result for specified indicator of report.

• Create shell in Excel for user-friendly run the macros.

• User should be able to configure parameters of report and run needed macros.

Implementation phases

Phase 1. Get the source report from CT2

Phase 2. Primary handling the source report in Excel

Phase 3. Full handling the source report in Excel

Phases 1 and 2 can be started parallelly.

Version 3

Note on scope

New Examples of Reports From Marc such as below expand scope. Now it is not only utilization related but any.

• 0003196: (*OpsRep Misc) Misc reps: Weekly Shpt Counts - Global
• 0003194: (*OpsRep Misc) Misc reps: File Counts Stats Report

Methodology

It is not possible or feasible to create one Wizard/Builder in advance that will produce any report. Instead we have a methodology/process - see below.

• Marc (alone or together with BA) creates mock up of the report and lists requirements on filters, etc
  • probably on average once a week or less frequently
• Support will analyze and see if it can pull data from existing reports
• Support/developer will create "one time" xls with required data
• Marc will "experiment with data" by adding charts/formulas to xls
• The following steps will be required IN SOME cases:
• xls with data/formulas/charts/comments will be forwarded to support
• macros will be written to produce all above based on data automatically for any fresh set of data (this could be quickly done for a purpose of sending regular reports to client for example as in EA case for Dom Trucking)
• "one time" report could be added into CT2
• DR could be added for above