What is the source of the Velocity Chart?

Where is the data to create the Velocity chart exactly coming from?

I have access to all the database tables on the server where Jira is installed.
We use all the issue and changed item data for example, but we are not able to calculate the committed points foor all boards/sprints/all teams at once/aggregated.

I have heard that Jira makes sort of snapshots per sprint to create the Velocity Chart. Where does Jira stores those snapehots? in which table can I find the underlying data?

Regards,

Jeroen van Woudenberg

3 answers

The database is not built for reporting, so you're not going to have a good time trying to do this with SQL.

The data is drawn from the issues and sprint data, so jiraissue, customfieldvalue and the AO tables related to Software are the starting point.  But some of it is calculated and kept only in the index, and more calculated at run time, so you can't get to it from the database and you'll need to replicate the calculations the code does.

Hi Nic,
thanks for your quick reaction. we have access to all the tables and data on the server where we installed jira. Also the AO tables. is it possible to get insight in those calculations?

or where can I find the index on the server?
I have attached an overview of the tables, available for us.
can you point out if I need one of it?

  AO_21D670_WHITELIST_RULES AO_38321B_CUSTOM_CONTENT_LINK AO_3B1893_LOOP_DETECTION AO_4AEACD_WEBHOOK_DAO AO_550953_SHORTCUT AO_563AEE_ACTIVITY_ENTITY AO_563AEE_ACTOR_ENTITY AO_563AEE_MEDIA_LINK_ENTITY AO_563AEE_OBJECT_ENTITY AO_563AEE_TARGET_ENTITY AO_575BF5_PROCESSED_COMMITS AO_575BF5_PROVIDER_ISSUE AO_587B34_GLANCE_CONFIG AO_587B34_PROJECT_CONFIG AO_5FB9D7_AOHIP_CHAT_LINK AO_5FB9D7_AOHIP_CHAT_USER AO_60DB71_AUDITENTRY AO_60DB71_BOARDADMINS AO_60DB71_CARDCOLOR AO_60DB71_CARDLAYOUT AO_60DB71_COLUMN AO_60DB71_COLUMNSTATUS AO_60DB71_DETAILVIEWFIELD AO_60DB71_ESTIMATESTATISTIC AO_60DB71_ISSUERANKING AO_60DB71_ISSUERANKINGLOG AO_60DB71_LEXORANK AO_60DB71_LEXORANKBALANCER AO_60DB71_NONWORKINGDAY AO_60DB71_QUICKFILTER AO_60DB71_RANK_ISSUE_LINK AO_60DB71_RANKABLEOBJECT AO_60DB71_RAPIDVIEW AO_60DB71_SPRINT AO_60DB71_SPRINTMARKER AO_60DB71_STATSFIELD AO_60DB71_SUBQUERY AO_60DB71_SWIMLANE AO_60DB71_TRACKINGSTATISTIC AO_60DB71_VERSION AO_60DB71_WORKINGDAYS AO_7DEABF_ATTACHMENT AO_7DEABF_CHANGE_ZJEGROUP AO_7DEABF_CHANGE_ZJEITEM AO_7DEABF_COLUMN_LAYOUT_ITEM AO_7DEABF_CYCLE AO_7DEABF_EXEC_CLUSTER_MESSAGE AO_7DEABF_SCHEDULE AO_7DEABF_SCHEDULE_DEFECT AO_7DEABF_STEP_DEFECT AO_7DEABF_STEP_RESULT AO_7DEABF_TESTSTEP AO_7DEABF_ZFJCOLUMN_LAYOUT AO_7DEABF_ZQLFAVORITE_ASOC AO_7DEABF_ZQLFILTER AO_7DEABF_ZQLSHARE_PERMISSIONS AO_82B313_ABILITY AO_82B313_ABSENCE AO_82B313_AVAILABILITY AO_82B313_INIT AO_82B313_PERSON AO_82B313_RESOURCE AO_82B313_SKILL AO_82B313_TEAM AO_88263F_HEALTH_CHECK_STATUS AO_88263F_PROPERTIES AO_88263F_READ_NOTIFICATIONS AO_8BAD1B_BOOTSTRAP AO_8BAD1B_FAVORITES AO_8BAD1B_FOLDER AO_8BAD1B_FOREST AO_8BAD1B_FOREST_CHANGE AO_8BAD1B_GENERATOR AO_8BAD1B_HISTORY_CHANGE AO_8BAD1B_HISTORY_ENTRY AO_8BAD1B_HISTORY_ISSUE AO_8BAD1B_HISTORY_PROJECT AO_8BAD1B_ISSUE_INDEX AO_8BAD1B_ITEM_EVENTS AO_8BAD1B_ITEM_TYPE AO_8BAD1B_NODE_SIGNS AO_8BAD1B_ORDERING AO_8BAD1B_PERSPECTIVE AO_8BAD1B_PROCESS_STATE AO_8BAD1B_PROPERTY AO_8BAD1B_ROW AO_8BAD1B_STATISTICS AO_8BAD1B_STRUCTURE AO_8BAD1B_STRUCTURE_PROPERTY AO_8BAD1B_STRUCTURE_TO_VIEW AO_8BAD1B_SYNC_AUDIT_RECORD AO_8BAD1B_SYNC_EVENT AO_8BAD1B_SYNC_INSTANCE AO_8BAD1B_VIEW AO_8BAD1B_VIEW_TO_STRUCTURE AO_97EDAB_USERINVITATION AO_A0B856_WEB_HOOK_LISTENER_AO AO_A415DF_AOABILITY AO_A415DF_AOABSENCE AO_A415DF_AOAVAILABILITY AO_A415DF_AOCONFIGURATION AO_A415DF_AOCUSTOM_WORDING AO_A415DF_AODEPENDENCY AO_A415DF_AODOOR_STOP AO_A415DF_AOESTIMATE AO_A415DF_AOEXTENSION_LINK AO_A415DF_AONON_WORKING_DAYS AO_A415DF_AOPERMISSION AO_A415DF_AOPERSON AO_A415DF_AOPLAN AO_A415DF_AOPLAN_CONFIGURATION AO_A415DF_AOPRESENCE AO_A415DF_AORELEASE AO_A415DF_AOREPLANNING AO_A415DF_AORESOURCE AO_A415DF_AOSKILL AO_A415DF_AOSOLUTION_STORE AO_A415DF_AOSPRINT AO_A415DF_AOSTAGE AO_A415DF_AOSTREAM AO_A415DF_AOSTREAM_TO_TEAM AO_A415DF_AOTEAM AO_A415DF_AOTHEME AO_A415DF_AOWORK_ITEM AO_A415DF_AOWORK_ITEM_TO_RES AO_A44657_HEALTH_CHECK_ENTITY AO_B9A0F0_APPLIED_TEMPLATE AO_CFF990_AOTRANSITION_FAILURE AO_D9132D_ASSIGNMENT AO_D9132D_ASSIGNMENT_EXT AO_D9132D_CONFIGURATION AO_D9132D_DEP_ISSUE_LINK_TYPES AO_D9132D_DISTRIBUTION AO_D9132D_EXCLUDED_VERSIONS AO_D9132D_HIERARCHY_CONFIG AO_D9132D_INIT AO_D9132D_ISSUE_SOURCE AO_D9132D_NONWORKINGDAYS AO_D9132D_PERMISSIONS AO_D9132D_PLAN AO_D9132D_PLAN_CUSTOM_FIELD AO_D9132D_PLANSKILL AO_D9132D_PLANTEAM AO_D9132D_PLANTHEME AO_D9132D_PLANVERSION AO_D9132D_PROGRAM AO_D9132D_RANK_ITEM AO_D9132D_SCEN_CUSTOM_FIELD AO_D9132D_SCENARIO AO_D9132D_SCENARIO_ABILITY AO_D9132D_SCENARIO_AVLBLTY AO_D9132D_SCENARIO_CHANGES AO_D9132D_SCENARIO_ISSUE_CMPNT AO_D9132D_SCENARIO_ISSUE_LABEL AO_D9132D_SCENARIO_ISSUE_LINKS AO_D9132D_SCENARIO_ISSUE_RES AO_D9132D_SCENARIO_ISSUES AO_D9132D_SCENARIO_PERSON AO_D9132D_SCENARIO_RESOURCE AO_D9132D_SCENARIO_SKILL AO_D9132D_SCENARIO_STAGE AO_D9132D_SCENARIO_TEAM AO_D9132D_SCENARIO_THEME AO_D9132D_SCENARIO_VERSION AO_D9132D_SCENARIO_XPVERSION AO_D9132D_SHARED_REPORT AO_D9132D_SOLUTION AO_D9132D_STAGE AO_D9132D_THEME AO_D9132D_VERSION_ENRICHMENT AO_D9132D_X_PROJECT_VERSION AO_DC78D7_HUDSON_ASSOCIATION AO_DC78D7_HUDSON_SERVER AO_E8B6CC_BRANCH AO_E8B6CC_BRANCH_HEAD_MAPPING AO_E8B6CC_CHANGESET_MAPPING AO_E8B6CC_COMMIT AO_E8B6CC_GIT_HUB_EVENT AO_E8B6CC_ISSUE_MAPPING AO_E8B6CC_ISSUE_MAPPING_V2 AO_E8B6CC_ISSUE_TO_BRANCH AO_E8B6CC_ISSUE_TO_CHANGESET AO_E8B6CC_MESSAGE AO_E8B6CC_MESSAGE_QUEUE_ITEM AO_E8B6CC_MESSAGE_TAG AO_E8B6CC_ORG_TO_PROJECT AO_E8B6CC_ORGANIZATION_MAPPING AO_E8B6CC_PR_ISSUE_KEY AO_E8B6CC_PR_PARTICIPANT AO_E8B6CC_PR_TO_COMMIT AO_E8B6CC_PROJECT_MAPPING AO_E8B6CC_PROJECT_MAPPING_V2 AO_E8B6CC_PULL_REQUEST AO_E8B6CC_REPO_TO_CHANGESET AO_E8B6CC_REPO_TO_PROJECT AO_E8B6CC_REPOSITORY_MAPPING AO_E8B6CC_SYNC_AUDIT_LOG AO_E8B6CC_SYNC_EVENT AO_ED669C_SEEN_ASSERTIONS AO_F86E5E_PROJECT_MAPPINGS  

Hi Nic,
thanks for your quick reaction. we have access to all the tables and data on the server where we installed jira. Also the AO tables. is it possible to get insight in those calculations?

or where can I find the index on the server?
I have attached an overview of the tables, available for us.
can you point out if I need one of it?

AO_21D670_WHITELIST_RULES AO_38321B_CUSTOM_CONTENT_LINK AO_3B1893_LOOP_DETECTION AO_4AEACD_WEBHOOK_DAO AO_550953_SHORTCUT AO_563AEE_ACTIVITY_ENTITY AO_563AEE_ACTOR_ENTITY AO_563AEE_MEDIA_LINK_ENTITY AO_563AEE_OBJECT_ENTITY AO_563AEE_TARGET_ENTITY AO_575BF5_PROCESSED_COMMITS AO_575BF5_PROVIDER_ISSUE AO_587B34_GLANCE_CONFIG AO_587B34_PROJECT_CONFIG AO_5FB9D7_AOHIP_CHAT_LINK AO_5FB9D7_AOHIP_CHAT_USER AO_60DB71_AUDITENTRY AO_60DB71_BOARDADMINS AO_60DB71_CARDCOLOR AO_60DB71_CARDLAYOUT AO_60DB71_COLUMN AO_60DB71_COLUMNSTATUS AO_60DB71_DETAILVIEWFIELD AO_60DB71_ESTIMATESTATISTIC AO_60DB71_ISSUERANKING AO_60DB71_ISSUERANKINGLOG AO_60DB71_LEXORANK AO_60DB71_LEXORANKBALANCER AO_60DB71_NONWORKINGDAY AO_60DB71_QUICKFILTER AO_60DB71_RANK_ISSUE_LINK AO_60DB71_RANKABLEOBJECT AO_60DB71_RAPIDVIEW AO_60DB71_SPRINT AO_60DB71_SPRINTMARKER AO_60DB71_STATSFIELD AO_60DB71_SUBQUERY AO_60DB71_SWIMLANE AO_60DB71_TRACKINGSTATISTIC AO_60DB71_VERSION AO_60DB71_WORKINGDAYS AO_7DEABF_ATTACHMENT AO_7DEABF_CHANGE_ZJEGROUP AO_7DEABF_CHANGE_ZJEITEM AO_7DEABF_COLUMN_LAYOUT_ITEM AO_7DEABF_CYCLE AO_7DEABF_EXEC_CLUSTER_MESSAGE AO_7DEABF_SCHEDULE AO_7DEABF_SCHEDULE_DEFECT AO_7DEABF_STEP_DEFECT AO_7DEABF_STEP_RESULT AO_7DEABF_TESTSTEP AO_7DEABF_ZFJCOLUMN_LAYOUT AO_7DEABF_ZQLFAVORITE_ASOC AO_7DEABF_ZQLFILTER AO_7DEABF_ZQLSHARE_PERMISSIONS AO_82B313_ABILITY AO_82B313_ABSENCE AO_82B313_AVAILABILITY AO_82B313_INIT AO_82B313_PERSON AO_82B313_RESOURCE AO_82B313_SKILL AO_82B313_TEAM AO_88263F_HEALTH_CHECK_STATUS AO_88263F_PROPERTIES AO_88263F_READ_NOTIFICATIONS AO_8BAD1B_BOOTSTRAP AO_8BAD1B_FAVORITES AO_8BAD1B_FOLDER AO_8BAD1B_FOREST AO_8BAD1B_FOREST_CHANGE AO_8BAD1B_GENERATOR AO_8BAD1B_HISTORY_CHANGE AO_8BAD1B_HISTORY_ENTRY AO_8BAD1B_HISTORY_ISSUE AO_8BAD1B_HISTORY_PROJECT AO_8BAD1B_ISSUE_INDEX AO_8BAD1B_ITEM_EVENTS AO_8BAD1B_ITEM_TYPE AO_8BAD1B_NODE_SIGNS AO_8BAD1B_ORDERING AO_8BAD1B_PERSPECTIVE AO_8BAD1B_PROCESS_STATE AO_8BAD1B_PROPERTY AO_8BAD1B_ROW AO_8BAD1B_STATISTICS AO_8BAD1B_STRUCTURE AO_8BAD1B_STRUCTURE_PROPERTY AO_8BAD1B_STRUCTURE_TO_VIEW AO_8BAD1B_SYNC_AUDIT_RECORD AO_8BAD1B_SYNC_EVENT AO_8BAD1B_SYNC_INSTANCE AO_8BAD1B_VIEW AO_8BAD1B_VIEW_TO_STRUCTURE AO_97EDAB_USERINVITATION AO_A0B856_WEB_HOOK_LISTENER_AO AO_A415DF_AOABILITY AO_A415DF_AOABSENCE AO_A415DF_AOAVAILABILITY AO_A415DF_AOCONFIGURATION AO_A415DF_AOCUSTOM_WORDING AO_A415DF_AODEPENDENCY AO_A415DF_AODOOR_STOP AO_A415DF_AOESTIMATE AO_A415DF_AOEXTENSION_LINK AO_A415DF_AONON_WORKING_DAYS AO_A415DF_AOPERMISSION AO_A415DF_AOPERSON AO_A415DF_AOPLAN AO_A415DF_AOPLAN_CONFIGURATION AO_A415DF_AOPRESENCE AO_A415DF_AORELEASE AO_A415DF_AOREPLANNING AO_A415DF_AORESOURCE AO_A415DF_AOSKILL AO_A415DF_AOSOLUTION_STORE AO_A415DF_AOSPRINT AO_A415DF_AOSTAGE AO_A415DF_AOSTREAM AO_A415DF_AOSTREAM_TO_TEAM AO_A415DF_AOTEAM AO_A415DF_AOTHEME AO_A415DF_AOWORK_ITEM AO_A415DF_AOWORK_ITEM_TO_RES AO_A44657_HEALTH_CHECK_ENTITY AO_B9A0F0_APPLIED_TEMPLATE AO_CFF990_AOTRANSITION_FAILURE AO_D9132D_ASSIGNMENT AO_D9132D_ASSIGNMENT_EXT AO_D9132D_CONFIGURATION AO_D9132D_DEP_ISSUE_LINK_TYPES AO_D9132D_DISTRIBUTION AO_D9132D_EXCLUDED_VERSIONS AO_D9132D_HIERARCHY_CONFIG AO_D9132D_INIT AO_D9132D_ISSUE_SOURCE AO_D9132D_NONWORKINGDAYS AO_D9132D_PERMISSIONS AO_D9132D_PLAN AO_D9132D_PLAN_CUSTOM_FIELD AO_D9132D_PLANSKILL AO_D9132D_PLANTEAM AO_D9132D_PLANTHEME AO_D9132D_PLANVERSION AO_D9132D_PROGRAM AO_D9132D_RANK_ITEM AO_D9132D_SCEN_CUSTOM_FIELD AO_D9132D_SCENARIO AO_D9132D_SCENARIO_ABILITY AO_D9132D_SCENARIO_AVLBLTY AO_D9132D_SCENARIO_CHANGES AO_D9132D_SCENARIO_ISSUE_CMPNT AO_D9132D_SCENARIO_ISSUE_LABEL AO_D9132D_SCENARIO_ISSUE_LINKS AO_D9132D_SCENARIO_ISSUE_RES AO_D9132D_SCENARIO_ISSUES AO_D9132D_SCENARIO_PERSON AO_D9132D_SCENARIO_RESOURCE AO_D9132D_SCENARIO_SKILL AO_D9132D_SCENARIO_STAGE AO_D9132D_SCENARIO_TEAM AO_D9132D_SCENARIO_THEME AO_D9132D_SCENARIO_VERSION AO_D9132D_SCENARIO_XPVERSION AO_D9132D_SHARED_REPORT AO_D9132D_SOLUTION AO_D9132D_STAGE AO_D9132D_THEME AO_D9132D_VERSION_ENRICHMENT AO_D9132D_X_PROJECT_VERSION AO_DC78D7_HUDSON_ASSOCIATION AO_DC78D7_HUDSON_SERVER AO_E8B6CC_BRANCH AO_E8B6CC_BRANCH_HEAD_MAPPING AO_E8B6CC_CHANGESET_MAPPING AO_E8B6CC_COMMIT AO_E8B6CC_GIT_HUB_EVENT AO_E8B6CC_ISSUE_MAPPING AO_E8B6CC_ISSUE_MAPPING_V2 AO_E8B6CC_ISSUE_TO_BRANCH AO_E8B6CC_ISSUE_TO_CHANGESET AO_E8B6CC_MESSAGE AO_E8B6CC_MESSAGE_QUEUE_ITEM AO_E8B6CC_MESSAGE_TAG AO_E8B6CC_ORG_TO_PROJECT AO_E8B6CC_ORGANIZATION_MAPPING AO_E8B6CC_PR_ISSUE_KEY AO_E8B6CC_PR_PARTICIPANT AO_E8B6CC_PR_TO_COMMIT AO_E8B6CC_PROJECT_MAPPING AO_E8B6CC_PROJECT_MAPPING_V2 AO_E8B6CC_PULL_REQUEST AO_E8B6CC_REPO_TO_CHANGESET AO_E8B6CC_REPO_TO_PROJECT AO_E8B6CC_REPOSITORY_MAPPING AO_E8B6CC_SYNC_AUDIT_LOG AO_E8B6CC_SYNC_EVENT AO_ED669C_SEEN_ASSERTIONS AO_F86E5E_PROJECT_MAPPINGS

0 votes

The tables you need are the AO_60DB71 ones for the sprint and board data, and then you'll want to be reading either customfieldvalue or work-logs combined with jiraissue for your estimation data, and customfieldvalue to identify the sprints.

Trying to read the index is probably a non-starter.  You can read off lucene indices with the right tools, but it's still only partial data and you'll still have to do a lot of the calculations yourself.

I really cannot recommend you do this in this way, it's a mess.

You should look at pulling the data you need out over the REST interface, it is a far more simple approach

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Nov 27, 2018 in Portfolio for Jira

Introducing a new planning experience in Portfolio for Jira (Server/DC)

In the past, Portfolio for Jira required a high degree of detail–foresight that was unrealistic for many businesses to   have–in   order to produce a reliable long-term roadmap. We're tur...

2,650 views 18 21
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you