KPI
From UG
Info
Mantis
Mantis:
- parent: 0003545
- category: DR/KPI:ph1
Environments
- QA/SIT/UAT:
Good account to test with:
- name: KPI_User@com.com
- pwd: Admin1234
SVN
- /branches/DR_KPI
Team
- Sponsor: Simon
- UAT/Product Manager: Marc
- Architect for frame work: Alex
- SA/PM/SIT: Tira
- Dev1: Sasha
- Dev2: Misha
- QA: Roma
- Graphics: Vic
Email groups:
alex@jaguarfreight.com; a.pivniak@elcosol.com; k.ushakov@elcosol.com; v.makhankov@elcosol.com; r.lakhno@elcosol.com; m.tymoshenko@elcosol.com; montira@jaguarfreight.com;
Glossary
- BI - Business Intelligence
Requirements
- This Phase One should include first portion of DRs/KPIs delivered
- Must be production quality
- This phase is for Arden primarily (all MOTs) but also for Sales Dept to show possibilities to prospects and existing Clients
- This project is very important top visibility project
- Also right tool/lib should be evaluated/selected for the future (example: LogiXML vs ZK API/other Java APIs)
DR1 and DR2
Moved to DR KPI Phase One DR1 DR2
Standard Layout
DR/KPIs are to conform to standard layout - breakdown of page into panels is well defined. Specific data and types of plots on each panel could change.
It consists of the following sections:
- title
- main menu
- KPI panel
- left panel
- top right panel
- bottom right panel
See also Figure below:
DR3 Cost per unit KPI by Shipper Consignee Carrier Lane
DR3 Layout
See #Standard Layout.
Title and Main Menu
Title: Cost per unit KPI by Shipper Consignee Carrier Lane
Main Menu:
- Client Company - single select, list restricted by user visibility
- MOT - single select
- View type - controls what is displayed inside panels. Options:
- Main View
- Shippers View
- Consignees View
- Carriers View
- Lanes View
- Ttl Weight/Cost/Count/Avg Weight selector
- This defines axis Y for Top Right Panel
- not used for Main View
- options:
- Total Weight
- Average Cost
- Shipment Count
- Average Weight
KPI Panel
- label: cost per kg (KPI type and unit of measurement)
- currency: USD
- number: actual value of KPI (if "weekly" is selected then average for last full week, if "monthly" - average for last full month)
- arrow change indicator: comparatively to previous period defined by "timeframe" (up - red, down - green)
- percentage: change in % comparatively to previous period defined by "timeframe"
- timeframe: options: weekly, monthly
- traffic light: Gage, indicates tolerance level, see #Tolerance levels parameters
Tolerance levels parameters
- this is a list of parameters managed by user in user profile
- controls what is considered green vs yellow vs red zone for KPI
Left panel, Top right panel, Bottom right panel
Content on these panels is controlled by "View Type" selector. See details below.
Main View
See mockup below:
Left panel
Left column:
- weeks to date sorting from current week to previous weeks (current week, last week, previous week .... )
- arrow indicator - change from previous week
- Mouse over value description
- gage indicator
- Mouse over value description
Right column:
- KPI value
- Mouse over value description
Top right panel
Axis X: all weeks to date (w1, w2, .... ) or months to date (Jan, Feb,....Dec)
Axis Y:
- plot A: KPI this year
- plot B: KPI last year
- plot C: YTD Avarage
Bottom right panel Tab 1
- Type: bar/pie
- Axis X: top/bottom 25, 20, 15, 10, 5 for Shippers
- Axis Y: KPI
- Logic: SUM(Total cost for a shipper)/SUM(Total weight for a shipper)
Bottom right panel Tab 2
- Type: bar/pie
- Axis X: top/bottom 25, 20, 15, 10, 5 for Consignees
- Axis Y: KPI
- Logic: SUM(Total cost for a consignee)/SUM(Total weight for a consignee)
Bottom right panel Tab 3
- Type: bar/pie
- Axis X: top/bottom 25, 20, 15, 10, 5 for Carriers
- Axis Y: KPI
- Logic: SUM(Total cost for a carrier)/SUM(Total weight for a carrier)
Bottom right panel Tab 4
- Type: bar/pie
- Axis X: top/bottom 25, 20, 15, 10, 5 for Lanes
- Axis Y: KPI
- Logic: SUM(Total cost for a lane)/SUM(Total weight for a lane)
Carriers View
See mockup below: NOTE: Mock up below is incorrect. Under construction. It will be posted soon
File:Body carriers view dr3 final 5.png
Left panel
Left column:
- Carriers
Right column:
- KPI value
KPI Panel
Action 1: Carrier view is selected
- Time frame: options for weekly, monthly are hiding
- Show number and percentage
- Show Total Weight/Cost/Count/Avg Weight selector
- Arrow change indicator and traffic light are hiding
Action 2: A particular row of Left panel is selected such as Jet blue
- Jetblue is selected (one click)
- Time frame: options for weekly, monthly are appeared and available for user to select
- Default: Weekly
- Top right Panel: show graph for a particular carrier (Jet blue) for weekly information (w1, w2...)
- Show number for that particular carrier KPI (avg last full week/month) value and percentage compared last full week/month to previous week/month
- Bottom right Panel: show chart for a particular carrier (Jet blue), apply to all tabs (Total Weight, Average Cost, Sgupment Count, Average Weight)
- Time frame: options for weekly, monthly are appeared and available for user to select
Top right panel
Carrier View: general information
Axis X: all carriers (Current year, Last year, YTD average for current year, YTD average for last year)
Carrier View: Specific information (select from Left panel)
Axis X: all weeks to date (w1, w2, .... ) or months to date (Jan, Feb,....Dec)
Axis Y: options:
General information
- Total weight (kg)
- Average Cost (USD)
- Shipment Count
- Average weight (kg) per shipment
Specific information (particular carrier)
- Total weight (kg)
- Average Cost (USD)
- Shipment Count
- Average weight (kg) per shipment
plot A: this year
plot B: last year
plot C: YTD average(this year)
plot D: YTD average (last year)
Bottom right panel Tab 1
- Type: bar/pie
- Axis X: top/bottom 25, 20, 15, 10, 5 for Carriers
- Axis Y: Total weight (kg)
- Apply:
- General information: YTD average
- Specific information: a particular carrier (weekly/monthly)
Bottom right panel Tab 2
- Type: bar/pie
- Axis X: top/bottom 25, 20, 15, 10, 5 for Carriers
- Axis Y: Average Cost (USD)
- Apply:
- General information: YTD average
- Specific information: a particular carrier (weekly/monthly)
Bottom right panel Tab 3
- Type: bar/pie
- Axis X: top/bottom 25, 20, 15, 10, 5 for Carriers
- Axis Y: Shipment Count
- Apply:
- General information: YTD average
- Specific information: a particular carrier (weekly/monthly)
Bottom right panel Tab 4
- Type: bar/pie
- Axis X: top/bottom 25, 20, 15, 10, 5 for Carriers
- Axis Y: Average weight (kg) per shipment
- Apply:
- General information: YTD average
- Specific information: a particular carrier (weekly/monthly)
Consignees View
Same data/layout as in #Carriers View but for Consignees.
Shippers View
Same data/layout as in #Carriers View but for Shippers.
Lanes View
Same data/layout as in #Carriers View but for Lanes.
DR3 Misc
MOTs
- FCL (two FCL modes combined)
- LCL (two FCL modes combined: LCL/Client Consol)
- Truck (all 3 truck modes combined)
YTD average line
This is a horizontal line on top right plot indicating "YTD average" for KPI, say cost per kg.
Top Bottom X selector
This is a single select for bottom right panel indicating by how many items to group. See full list below:
- top 10
- top 20
- bottom 10
- bottom 20
Mappings
Shippers:
- same for all MOTs
- maps to Ct#Shipper
Consignees:
- same for all MOTs
- maps to Ct#Consignee
Carriers:
We have two fields for Trucking company: Export Pick-up Trucker, Delivery Trucker. So if one shipment has both fields set then I assume both companies get credit as far as shipment count and weight.
Lanes:
- Ocean = origin/destination terminals
- Air = airport of departure / airport of arrival/destination
How to calculate cost:
- cost for EA DOM - see Ph1A section.
DR3 Phase 1A: EA DOM Portal only
In this case:
- we report only on Arden USA Domestic shipments that are handled "with a help of TMS"
- E0 = "EA DOM Portal"
- NOTE: we need to add "flag" into the system that clearly defines what CTs are handled through TMS and what are not - see #TMS flag. This is important because cost is calculated / stored differently in our DB for TMS related and other records.
- MOT = "Trucking Domestic"
- let user select this,
- if they select say "Air" with "EA DOM Portal" obviously 0 records would be found. In this case system should display message in red bold "No data found". I suggest post it above menu or in pop-up.
- replace kg with lb in this case
- Tolerance levels parameters:
- in case connected to TMS/ASN is On the I suggest to define #Tolerance levels parameters as Clint Company specific and define these params there
DR3 ph 1A Mock up Main View
TMS flag
In Client Company profile add check box to "Part A. General":
- label: connected to TMS/ASN
Questions
- when/how load cost arrives into CT2 DB?
- A: With Load plan XML? NOTE: this is estimated cost from TMS tariffs not actual billed cost!
DB for load
- tblLoad
- sql/load.properties
Date
use Actual Deliver Date
Company City Note
- show Company
- City Note on Hint
Misc
Some related code is in the system. Please re-use! See Cost_per_pound_KPI_(TMS_based_Arden_)_(solution) .
DR3 Phase 1A1: EA DOM Portal only Main View only
This is a sub-phase of phase 1A.
It characterized by:
- Main View only
Admin
On internal:
On Client:
DR4 Phase 1A2 Dates View
Dates View General Info
- this DR is on additional tab
- name "Lead times" (aka Dates View)
- in user profile it should be defined/managed separately
Dates View Title
Lead Times by Shipper Consignee Carrier Lane.
Dates View Main Menu
- client company E0
- MOT
- specific dates KPI - time ranges between known CT dates, options:
- created to delivered
- approved to delivered
- created to approved
- approved to pick up
- pick up to departure (air and ocean only)
- departure to arrival (air and ocean only)
- arrival to delivery (air and ocean only)
- pick up to delivery (trucking only)
Dates View KPI panel
- gives average in days for a YTD for selected dates KPI
- gives change in % (this year over previous)
- arrow indicator (up or down from previous year)
- gage indicator for YTD
- weekly/monthly switch - this actually currently controls type of axis X on Top Right panel
Dates View Left panel
Similar to Cost per Unit KPI.
But this is report on selected in main menu particular dates KPI - average for all weeks YTD.
Dates View Top Right panel
Similar to Cost per Unit KPI.
But this is a report on selected in main menu particular dates KPI.
Dates View Bottom Right panel
Similar to Cost per Unit KPI.
But this is a report on selected in main menu particular dates KPI.
Dates View Mock Up
Work breakdown structure and Change Requests Part A
Sequence
- first 3567, 3575 in parallel
- next 0003583
- next 0003595
- all remaining are sequential in order of priorities
3567 DR1 Simple Shipment count DR
- Sasha: Java/SQL
Spec: #DR1 Simple Shipment count DR
3575 Evaluate LogiXML
- Kostya, Vlad
0003583 DR2 Complex Shipment Data per Client per Year DR, PART A
- kostya: SQL, Java b-end
- sasha: b-end, f-end
spec: #DR2 Complex Shipment Data per Client per Year DR, PART A
0003595: [DR/KPI] DR3 Phase 1A: EA DOM Portal only
spec: #DR3 Phase 1A: EA DOM Portal only
Work breakdown structure and Change Requests Part B
SOW 0 DR4 Dates KPI
mantis: 3662
Spec: #DR4 Phase 1A2 Dates View
- Created to Delivered
- Approved to Delivered
- Created to Approved
- Approved to Pick-up
- Pick up to Delivered
SOW 1 Spec gaps and some changes
mantis: 3668
R1,2 - moved to another SOW
- R3: monthly / weekly switch should also apply to Left Panel (group by weeks / month). If monthly is selected then left panel should show breakdown by month
- R4: for Left panel:
- arrow indicator should show change from previous week SAME year not previous year
- R5: for KPI panel:
- arrow and percentage: implement as in original spec (last week/month comparatively to previous week/month)
- R6 (added): for KPI panel:
- Number should show average cost per unit for last week/month (not YTD)
Description for R4, R5, and R6.
- 1) Left Panel: Show data until latest week/month which includes current week/month.
ie. if today is Thursday July 19 (29 Week). Top left should show 29 weeks for weekly left panel.
- 2) KPI Value: Per Example above, current week (29)/month (July) is not a full week/month,
KPI value should show the the last week/month value and percentage of last week/month value compared to previous week/month.
ie. If monthly is selected, shows July data on the left panel as the latest month (current month) and KPI Value shows "Jun KPI value " and percentage "Jun KPI" compared to "May KPI".
ie. If weekly is selected, shows 29 week on the left panel as the latest week (current week) and shows KPI value for last week value "28 week KPI value" and percentage "28 week KPI" compared to "27 week KPI".
Additional changes:
Left Panel:
1) Show the latest of full week/month on the list (do not show current not full week/month value)
a. For example, today is Tuesday July 24 (week 30), show last full week/month as the first one on the list (week 29, Week 28, …/Jun, May). Not necessary to show week 30/July as they are NOT a full week/month.
2) For Weekly/monthly that has “0” value (no data), do not show arrow change indicator for the week/month that has no data(empty) 3) If there is some week period in the middle that has no data (between 2 period of weeks that have data), arrow indicator to show for the week/month that has data compared to the previous last full week that has data
KPI Panel: 1) Show the last full week KPI value: a. If the last full week value has no data (empty), show “0” as KPI value as well as percentage for “0%” and no need to show arrow change indicator b. If the last full week value has data (not empty) and the previous week has no data (empty) then KPI value shows for the last full week value, and % for comparison as well as arrow change indicator to compare between the last full week (not empty) and previous last full week that has data (not empty)
SOW 2 Look and feel changes
mantis: 3690
- Weekly/monthly button: not clear what indicates "pressed" state
SOW 3 Make it very flexible to enable KPI or part of KPI to specific user
mantis: 3691
Internal Admin
This is managed in internal > admin > users > dashboard config Tab
- Enable/disable
- Which KPI(s) class such as Cost Per Unit
- Which KPI(s) with in a class such as cost per pound and cost per item
- Subset of KPI class for view such as Main view (if applicable)
- Set Bottom right tab(s) available
- Which KPI(s) with in a class such as cost per pound and cost per item
- Which MOT
- Which KPI(s) class such as Cost Per Unit
For example we should be able to enable just Cost per pound, just Main View, for just Air MOT. (is that overkill?)
See also #Admin
Client My profile
- Ability for client user to set the KPI setting
- Select which KPI to display from available accessible list
- Set tolerance setting level
SOW 4 (DR3 Cost Per Unit KPI, EA DOM only) Add all other Views
manis: 3692
These views:
- Shippers View
- Consignees View
- Carriers View
- Lanes View
Spec: #DR3 Cost per unit KPI by Shipper Consignee Carrier Lane
SOW 5 Add Quarters
mantis: 3693
Add option Quarterly to Weekly / Monthly
- Quarterly button is added between Weekly and Monthly
- Left Panel:
- Once Quarterly is selected then quarterly data columns are applied
- Show Quarterly from recent quarter back to previous quarters (Q3 Jul-Sep 2012, Q2 Apr-Jun 2012, Q1 Jan-Mar 2012, Q4 Oct-Dec 2011, ....)
- Show KPI value per quarter
- Drill down value: see #SOW_10_Left_panel_drill_down
- KPI Panel:
- KPI value: last full quarter value and percentage compared between last full quarter value and previous last full quarter value
- Top right Panel:
- Axis X: Quarters (Q1, Q2, Q3, and Q4)
- Axis Y:
- Plot A: KPI for this year
- Plot B: KPI for last year
- Plot C: YTD average
- Drill down Left Panel:
- If Q2 Apr-Jun 2012 is selected (one click), then applies chart to the specific quarter value on the bottom right chart.
- Drill down for a particular bottom right chart, show HTML report information for that particular bar/pie chart with ability for download to excel select output option
- If Q2 Apr-Jun 2012 is selected (double clicks), then download to excel option is available for all data for that particular quarter 2.
- If Q2 Apr-Jun 2012 is selected (one click), then applies chart to the specific quarter value on the bottom right chart.
SOW 6 Cost per pound is incorrect for some records
mantis: 0003688
Detailed spec TBD!
SOW 7 Remove pick up and/delivery trucker switch (combine data)
mantis:
Detailed spec TBD!
Notes: The challenge is when one CT has 2 different Truck companies in Pick Uo and Delivery Trucker fields.
Can we find out from data stored in DB what portion of this cost is attributed to one vs another (in TMS case and non TMS case)?
Also, calculations are different for international CTs vs TMS/Truck Dom.
SOW 8 For Bottom Right panel add drill down to CT and load level
mantis:
- If user clicks on a particular bar or pie segment of chart then system should produce pop up with CT/load details for relate data (use same format as was defined by Cost per Pound KPI implemented by AK)
SOW 9 Show all weeks all data from MAY 1, 2010
mantis:3695
- Change Left panel:
- They should appear in this order (top to bottom): w22 2012, w21 2012, ... . w1 2012, w52 2011, ... w1 2011, .... (shows all data in the system that have created on Date from May 1, 2010)
- Any/all KPI dashboards should be able to be filtered based on "Created On Date" after May 1, 2010
- Add graphs on Right Panel:
- It should also include all graphs from year 2010 (data started from May 1, 2010) to current year.
- For Example, this year is 2012, so there should be graphs for year 2012, 2011, 2010 and YTD average
- It should also include all graphs from year 2010 (data started from May 1, 2010) to current year.
- Apply to all KPIs such as Cost per Unit, Leadtime, Total Count, ....KPIs
- see position Left Panel
SOW 10 Left panel drill down
mantis:
- main view
- Highlight table, graph on top right
- Apply bottom right
- Other views
SOW 11 DR4 Dates KPI (On time performance: Truck dom (TMS) only
- Add additional lead time date range to measure On time performance
- Meaning: Actual date of delivery and Cargo Due date. On average these shipments are delivered before or on the Cargo Due date is considered On time and for shipments that are delivered late or after Cargo Due date is considered Late
- Weekly
- Monthly
- Quarterly
- This or particular week
- This month or particular month
- This quarter or particular quarter
- Compare years data
SOW 12 Elevate 0 value graph in order to see a bar chart for zero value
mantis: 3705
- Zero value chart is not visible (blank) and value cannot be shown while mouse over value
- Elevate 0 value graph in order to see actual bar charts for zero value. Apply for all tabs for zero value
- See figures below:
Solution: elevate zero value to show actual zero value chart below
SOW 13 DR3 Cost per Unit (cost per LB and cost per KG for all modes)
- These cost per LB and per KG apply to all modes. This is universal for all modes just a conversion
SOW 14 DR3 Cost per Unit (cost per Item, cost per pallet, cost per carton)
SOW 15 DR3 Cost per Unit (cost per CBM for LCL and Vendor console)
SOW 16 DR3 Cost per Unit (cost per container for FCL )
SOW 17 DR5 Total counter KPI
- Total Shipment Count (CT records)
- Total Gross KG shipped
- Total Loads Count(loads = TMS Dom only)
- Total Masters (all NON-TMS modes)
- Total Cartons
- Total Pallets Count
- Total Container Count
- Total Cost (amount billed to client...for EA DOM = charges used to calculate the "cost per LB")
SOW 18 DR6 Lane Optimization Equipment Utilization
SOW 19 DR7 Overlap
SOW 20 DR3 Cost Per Unit KPI: Extend this KPI to other Client Companies
mantis:
Detailed spec TBD!
SOW 21 Top Right Panel: Show all years graphs since May 2010 with all YTD average
Mantis:
- Data is displayed from May 2010.
- Graphs: 2012, 2011, 2010, YTD average (2012), YTD average (2011), YTD average (2010)
SOW 22 Bottom Right Panel: Spell out what value based on
SOW 23 (DR3 Cost Per Unit KPI) Extend this KPI to other MOTs (all units)
mantis:
- Air
- Chargeable Weight
- Ocean
- CBM (LCL, Vendor Console)
- Container (FCL)
- Truck
- Truck-Air
- Truck-Ocean
Detailed spec TBD!
SOW 24 (DR4 Lead Time KPI) Extend this KPI to other MOTs (all units)
mantis:
- Air
- Ocean
- LCL/FCL/Vendor Console
- Truck
- Truck-Air
- Truck-Ocean
Detailed spec TBD!
SOW 25 (DR5 Total counter KPI) Extend this KPI to other MOTs (all units)
mantis:
- Air
- Ocean
- LCL/FCL/Vendor Console
- Truck
- Truck-Air
- Truck-Ocean
Detailed spec TBD!
SOW 26 (DR6 Lane Optimization Equipment Utilization) Extend this KPI to other MOTs (all units)
mantis:
- Air
- Ocean
- LCL/FCL/Vendor Console
- Truck
- Truck-Air
- Truck-Ocean
Detailed spec TBD!
