KPI

From UG

Revision as of 13:50, 24 July 2012 by Montira (Talk | contribs)

(diff) ← Older revision | Current revision (diff) | Newer revision → (diff)

Jump to: navigation, search

KPI

Info

Mantis

Mantis:

parent: 0003545
category: DR/KPI:ph1

Environments

QA/SIT/UAT:
- http://dev4-kuchma.jaguarfreight.com/internal
- http://dev4-kuchma.jaguarfreight.com/Client

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

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

Top right panel

Axis X:

Axis Y: options:

Total weight (kg)
Average Cost (USD)
Shipment Count
Average weight (kg) per shipment

plot A: this year

plot B: last year

Bottom right panel Tab 1

Type: bar/pie
Axis X: top 20 Carriers
Axis Y: Total weight (kg)

Bottom right panel Tab 2

Type: bar/pie
Axis X: top 20 Carriers
Axis Y: Average Cost (USD)

Bottom right panel Tab 3

Type: bar/pie
Axis X: top 20 Carriers
Axis Y: Shipment Count

Bottom right panel Tab 4

Type: bar/pie
Axis X: top 20 Carriers
Axis Y: Average weight (kg) per shipment

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".

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 MOT

For example we should be able to enable just Cost per pound, just Main View, for just Air MOT. (is that overkill?)

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.

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
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

Detailed spec TBD!

SOW 32 (DR6 Lane Optimization Equipment Utilization) Extend this KPI to other MOTs (all units): add Truck-Ocean and Truck Air

mantis:

Detailed spec TBD!

Misc

Vics reports: https://docs.google.com/spreadsheet/ccc?key=0AicSe1YRkhifdGdYMndSSkU4ZXpPZ1BDSzFwRXl5bkE#gid=3

KPI

From UG

Contents

Info

Mantis

Environments

SVN

Team

Glossary

Requirements

DR1 and DR2

Standard Layout

DR3 Cost per unit KPI by Shipper Consignee Carrier Lane

DR3 Layout

Title and Main Menu

KPI Panel

Tolerance levels parameters

Left panel, Top right panel, Bottom right panel

Main View

Carriers View

Consignees View

Shippers View

Lanes View

DR3 Misc

MOTs

YTD average line

Top Bottom X selector

Mappings

DR3 Phase 1A: EA DOM Portal only

DR3 ph 1A Mock up Main View

TMS flag

Questions

DB for load

Date

Company City Note

Misc

DR3 Phase 1A1: EA DOM Portal only Main View only

Admin

DR4 Phase 1A2 Dates View

Dates View General Info

Dates View Title

Dates View Main Menu

Dates View KPI panel

Dates View Left panel

Dates View Top Right panel

Dates View Bottom Right panel

Dates View Mock Up

Work breakdown structure and Change Requests Part A

Sequence

3567 DR1 Simple Shipment count DR

3575 Evaluate LogiXML

0003583 DR2 Complex Shipment Data per Client per Year DR, PART A

0003595: [DR/KPI] DR3 Phase 1A: EA DOM Portal only

Work breakdown structure and Change Requests Part B

SOW 0 DR4 Dates KPI

SOW 1 Spec gaps and some changes

SOW 2 Look and feel changes

SOW 3 Make it very flexible to enable KPI or part of KPI to specific user

SOW 4 (DR3 Cost Per Unit KPI, EA DOM only) Add all other Views

SOW 5 Add Quarters

SOW 6 Cost per pound is incorrect for some records

SOW 7 Remove pick up and/delivery trucker switch (combine data)

SOW 8 For Bottom Right panel add drill down to CT and load level

SOW 9 Show all weeks all data from MAY 1, 2010

SOW 10 Left panel drill down

SOW 11 DR4 Dates KPI (On time performance: Truck dom (TMS) only

SOW 12 Elevate 0 value graph in order to see a bar chart for zero value

SOW 13 DR3 Cost per Unit (cost per LB and cost per KG for all modes)

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

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

SOW 21 (DR3 Cost Per Unit KPI) Extend this KPI to other MOTs (all units): add Air

SOW 22 (DR3 Cost Per Unit KPI) Extend this KPI to other MOTs (all units): add Ocean (LCL/FCL/Vendor Console)

SOW 23 (DR3 Cost Per Unit KPI) Extend this KPI to other MOTs (all units): add Truck-Ocean and Truck Air

SOW 24 (DR4 Lead Time KPI) Extend this KPI to other MOTs (all units): add Air

SOW 25 (DR4 Lead Time KPI) Extend this KPI to other MOTs (all units): add Ocean (LCL/FCL/Vendor Console)