Skip to main content
System StatusContact Support
VersionOne Community

Database Sizing

This feature is available in Enterprise and Ultimate editions.

Overview

The VersionOne Data Mart database is a denormalized representation of your highly-normalized VersionOne application database. The Data Mart database is optimized for reporting over large volumes of historical data and slicing results across various dimensions. As such, the size of the database is expected to be an order of magnitude larger than the database used by your VersionOne application instance, and it is expected to grow continuously over time.

Initial database size is a determined by the amount of data in your VersionOne application instance and the amount of historical data. When estimating the initial size of the database, a good rule of thumb is to expected a size 5-10 times larger than your VersionOne application database.

The growth of the database also depends on the amount of data in your VersionOne instance and how the instance is used. When adding new projects, or growing existing projects, the database will grow at a faster rate. During periods of management and tracking, the database will grow at a slower rate.

Based on our experience, we recommend the following when considering Data Mart database size and growth

  1. When considering storage for any data mart and/or data warehouse do no think in terms of disk drives, think in terms of disk systems; for example Storage Area Networks
  2. Start by expecting a database 10x the size of your VersionOne application database.
  3. After the initial load, monitor the database size daily for several weeks to determine growth patterns.
  4. Respond accordingly.
  5. Keep monitoring the database size on a less frequent basis as appropriate.

For those who desire more information regarding database sizing and/or growth, we offer the information below. The "Row Size" values were determined by summing the maximum size for each column based on the column's data type. Depending on your database server configuration, the actual row size may differ.

Dimension Table Sizing

Dimension Tables contain the current values from your VersionOne instance. These tables fall into three broad categories: List Dimensions, Data Dimensions, and Constant Dimensions.

List Dimensions

The following dimensions correspond to drop down lists defined in your VersionOne instance. Each table contains one row for each list value plus 2 extra rows: one to indicate when the list value is not applicable, and another to indicate the blank value.

The VersionOne System Info report can be used to determine how many values you have in each list type. To access this report, open the About Box in your VersionOne instance and click the System Info link. The count information you need is in the "AssetCounts" section, which is generally at the end of the report.

Once you have the number of values for a particular list, use the following formula to calculate the estimated space needed for each row: (Row Size) * (Asset Count + 2). For example, the Acceptance Test Status list in VersionOne ships with 2 values by default: Passed and Failed. However, the corresponding table (Dim.TestStatus) will contain 4 rows. Therefore the estimated size for this table would be 8056*4=32224bytes.

Table Name Row Size (in bytes) VersionOne List
Dim.AttachmentCategory 8064 Attachment Type
Dim.BuildSource 8064 Build Source
Dim.BuildStatus 8064 Build Status
Dim.ClassOfService 8064 Class Of Service
Dim.DefectResolution 8064 Defect Resolution
Dim.DefectType 8064 Defect Type
Dim.EffectiveAssetState 18 Asset State based on AssetState and the Reporting Category assigned to Status values
Dim.EpicPriority 8064 Epic Priority
Dim.EpicStatus 8064 Epic Status
Dim.EpicType 8064 Epic Type
Dim.GoalPriority 8064 Goal Priority
Dim.GoalType 8064 Goal Type
Dim.IssueCategory 8064 Issue Type
Dim.IssuePriority 8064 Issue Priority
Dim.IssueResolution 8064 Issue Resolution
Dim.IssueStatus 8064 Issue Status (No longer available)
Dim.NoteCategory 8064 Note Type (No longer available)
Dim.ProjectStatus 8064 Project Status
Dim.RegressionTestStatus 8064 Regression Test Status
Dim.RequestCategory 8064 Request Type
Dim.RequestPriority 8064 Request Priority
Dim.RequestResolution 8064 Request Resolution
Dim.RequestStatus 8064 Request Status
Dim.StoryRisk 8064 Workitem Risk
Dim.StoryType 8064 Story Risk
Dim.StrategicThemeLevel 8064 Strategic Theme Level
Dim.TaskSource 8064 Task Source
Dim.TaskStatus 8064 Task Status
Dim.TaskType 8064 Task Type
Dim.TestStatus 8064 Acceptance Test Status
Dim.TestType 8064 Test Type
Dim.ThemeArea 8064 Theme Area
Dim.ThemeComplexity 8064 Workitem Risk
Dim.ThemeSource 8064 Theme Source
Dim.ThemeStatus 8064 Theme Status
Dim.ThemeType 8064 Theme Type
Dim.WorkitemPriority 8064 Workitem Priority
Dim.WorkitemSource 8064 Workitem Source
Dim.WorkitemStatus 8064 Workitem Status

 

Data Dimensions

The following dimensions correspond to specific data types in your VersionOne instance. Each table contains one row for each instance of the specified type, plus 2 extra rows; one to indicate when the data type is not applicable, and another to indicate that no data exists.

The VersionOne System Info report can be used to estimate how many instances of a particular type are in your VersionOne instance. To access this report, open the About Box in your VersionOne instance and click the System Info link. The count information you need is in the "AssetCounts" section, which is generally at the end of the report. Note: The System Info report only shows the number of items visible to the logged in member. When Members don't have access to a particular project, they do not see counts for data in that project.

Use the following formula to calculate the estimated space needed for each row: (Row Size) * (Asset Count + 2). For example, if your System Info report indicates that you have 10 Goals, the corresponding table (Dim.Goal) will contain 12 rows. Therefore the estimated size for this table would be 48086*12=577032 bytes.

The Row Size value in the table below is for an installation that contains no Custom Fields. Adding Custom Fields to a data type will increase the row size for that type. The amount of increase depends on the Custom Field data type. These are explained in the Custom Fields section below.

Table Name Row Size (in bytes) VersionOne Datatype
Dim.BuildProject 16040 BuildProject
Dim.Environment 32094 Environment
Dim.Epic 112208 Epic
Dim.EpicTest 48124 Test
Dim.Goal 48094 Goal
Dim.Issue 104130 Issue
Dim.Iteration 64312 Timebox
Dim.Member 32260 Member
Dim.MemberGroup 8040 MemberLabel
Dim.PrimaryWorkitem 240318 Sum the counts for Story, Defect, and TestSet
Dim.Program 8040 ScopeLabel
Dim.Project 88524 Scope
Dim.RegressionPlan 56298 RegressionPlan
Dim.RegressionSuite 64336 RegressionSuite
Dim.RegressionTest 72132 RegressionTest
Dim.Request 104114 Request
Dim.Schedule 24240 Schedule
Dim.SecondaryWorkitem 128282 Sum the counts for Task and Test
Dim.StrategicTheme 32110 Strategic Theme
Dim.Team 8164 Team
Dim.TestSuite 16060 TestSuite
Dim.Theme 128148 Theme

 

Constant Dimensions

The following dimensions contain VersionOne constant values that are necessary for reporting. Because they are constants, these dimension tables have a fixed number of rows.

Table Name Row Size (in bytes) Row Count Estimated Size (in bytes)
Dim.AssetState 18 8 144
Dim.AssetType 104 7 728
Dim.BooleanAttribute 204 4 816
Dim.Date 215 7672 1611120

 

Fact Table Size

Fact Tables store the historical record of changes to your VersionOne application data over time. These tables fall into four broad categories: Parity Facts, Historical Facts, Roll-up Historical Facts, and Days In Facts

Parity Facts

Data in the following fact tables have a 1:1 mapping to the same type of data in your VersionOne application database. Therefore you can use the Asset Counts from the System Info report to determine the number of rows in the database. The growth of these tables will parallel the growth of the same information in VersionOne.

The VersionOne System Info report can be used to estimate how many instances of a particular type are in your VersionOne instance. To access this report, open the About Box in your VersionOne instance and click the System Info link. The count information you need is in the "AssetCounts" section, which is generally at the end of the report. Note: The System Info report only shows the number of items visible to the logged in member. When Members don't have access to a particular project, they do not see counts for data in that project.

Once you have the number for a particular type, use the following formula to calculate the estimated space needed for each row: (Row Size) * (Asset Count). For example, if your System Info report indicates that you have 100 Actual records, the corresponding table (Fact.Effort) will contain 100 rows. Therefore the estimated size for this table would be 60*100=6000 bytes.

The Row Size value for these tables are not impacted by Custom Fields, because Custom Fields are not available on the VersionOne objects used to populate these tables.

Table Name Row Size (in bytes) VersionOne Datatype in the SystemInfo Report
Fact.BuildRun 16036 BuildRun
Fact.Capacity 24 Capacity
Fact.Effort 60 Actual
Fact.TestRun 40 TestRun

Historical Facts

The following fact tables contain data that is trended over time. To accomplish this, the table contains one row per day, after the corresponding VersionOne asset is created. This adds a layer of complexity to estimating initial size and growth.

Here's an example to better explain how these tables work. Given 5 consecutive days; when a user adds only one story per day to their VersionOne application; then the corresponding database table (Fact.PrimaryWorkitem) will have 15 records at the beginning of day 6 (5 for the story added on Monday, 4 for story added on Tuesday, 3 for the story added on Wednesday, 2 for story added on Thursday and 1 for the story added on Friday).

If you are only interested in an estimation for the initial size of these tables, you might consider a worst case scenario and a normal-distribution scenario and expect an initial size somewhere in between.
Growth of these tables depends on system usage. During period of heavy growth - when new items are being added - the number of rows in these tables will increase quickly; however as growth turns to data management - changing attributes on existing items - the number of rows will increase at a steady pace. If we reconsider our example and take a scenario where no stories are added for the next 5 days, at the beginning of day 11 there would be 75 stories (15 stories per day for 5 days).

The Row Size value in the table below is for an installation that contains no Custom Fields. Adding Custom Fields to a data type will increase the row size for that type. The amount of increase depends on the Custom Field data type. These are explained in the Custom Fields section below.

Table Name Row Size (in bytes) VersionOne Datatype
Fact.Epic 104 Epic
Fact.EpicTest 36 Test
Fact.Issue 40 Issue
Fact.PrimaryWorkitem 112 Stories, Defects, and TestSets
Fact.ProjectSummary 192 Project Metrics
Fact.Request 40 Requests
Fact.Task 60 Task
Fact.Test 60 Test

 

Roll-up Historical Facts

Fact.Workitem. It's a special case because it allows us to easily roll-up trending data across Stories, Defects, TestSets, Task, and Test. This table is creating by combining the fields and data from Fact.PrimaryWorkitem, Fact.Task, and Fact.Test. Once you have row count estimates for those tables, you can sum them to obtain a row size estimate for this table.
The Row Size value in the table below is for an installation that contains no Custom Fields. Adding Custom Fields to a data type will increase the row size for that type. The amount of increase depends on the Custom Field data type. These are explained in the Custom Fields section below.

Table Name Row Size (in bytes)
Fact.Workitem 144

 

Days In Facts

These fact table contain current metrics for how log a VersionOne asset spent in a given status.  Each of these tables contains one row for each status value an Epic or Workitem appeared.

Here's an example to better explain how these tables work.  Given an instance with 4 Epic Status values (3 + Blank) and 10 Epics.  Initially the table will contain 10 rows; one row for each Epic in the blank status.  If one of the Epic subsequently visits all status values over some time period, then the table will contain 13 rows

Growth of these tables depends on system usage. During period of heavy growth - when new items are being added - the number of rows in these tables will increase quickly; however as growth turns to data management - changing attributes on existing items - the number of rows will increase only when an asset moves to a new status value.

The Row Size value in the table below is for an installation that contains no Custom Fields. Adding Custom Fields to a data type DOES NOT increase the row size for these tables.

Table Name Row Size (in bytes) Description
Fact.EpicDaysIn 32 Epics by Status Value
Fact.PrimaryWorkitemDaysIn 32 Primary Workitems by Status Value

 

Tree Table Size Information

Tree Tables contain information about data hierarchies in VersionOne. Each table contains one row per node in the VersionOne hierarchy, plus one row for each child node. Therefore, in order to calculate initial size and growth for these tables you need to understand the corresponding hierarchy in your VersionOne instance.

Here's an example to better illustrate how this works. Consider the following project tree structure

System
  - Company
      - Call Center
          - Release 1.0
      - Corporate Website
      - IT Support

The corresponding table (Tree.ProjectParent) would contain 16 rows; one for each node (6) and one for each node to all of the nodes children (10).
This table would not grow until another child project was added somewhere in the hierarchy. The amount of growth would depend on where the child was added in the hierarchy.

For instance, adding a sibling to Company

System
  - Company
      - Call Center
          - Release 1.0
      - Corporate Website
      - IT Support
  - Company 2

would only increase the table by 2 rows: One for "Company 2" and one for the relationship between System and Company 2.
However, added a new release to Call Center

System
  - Company
      - Call Center
          - Release 1.0
          - Release 2.0
      - Corporate Website
      - IT Support

would add 4 more rows: one for the "Release 2.0" node, and one this nodes relationship to each of it's parents

Table Name Row Size (in bytes) Corresponding VersionOne Data
Tree.EpicParent 12 Epics
Tree.PrimaryWorkitemSplitFrom 12 Story
Tree.ProjectParent 12 Scope
Tree.ThemeParent 12 Theme


1. Tree.EpicParent only contains Epics. It does not contain entries for leaf level stories that comprise an Epic.
2. Tree.PrimaryWorkitemSplitFrom contains stories that are split. While not typical, splitting a story can create a hierarchical data structure.

Bridge Table Size Information

Bridge Tables represent the many-to-many relationships that exist in your VersionOne instance. These tables fall into two broad categories: Standard Relationships and Temporal Relationships

Standard Relationships

Each of the following Bridge tables will contain one row for the relationship they represent. Their initial size is determined by the number of relationships that exist in your VersionOne instance and they will grow as these relationships are added. If the relationship is removed in the VersionOne application, it will be removed in the Data Mart database.

Table Name Row Size (in bytes) Relationship
Bridge.DefectFoundInBuildRun 8 Defect Found in a BuildRun
Bridge.EpicStrategicTheme 8 Epic Strategic Theme Relationship
Bridge.GoalTargetedByProject 8 Goal Targeted to a Project
Bridge.MemberGroup 8 Member in a Member Group
Bridge.PrimaryWorkitemBrokenByDefect 8 Primary Workitems Broken By Defect
Bridge.PrimaryWorkitemDependencies 8 Upstream and Downstream dependencies between PrimaryWorkitems
Bridge.PrimaryWorkitemCompletedInBuildRun 8 PrimaryWorkitem Completed in a BuildRun
Bridge.PrimaryWorkitemRequest 8 PrimaryWorkitem to Requests
Bridge.PrimaryWorkitemResolvedIssue 8 PrimaryWorkitem Resolves an Issue
Bridge.ProjectMember 8 Member that can view data in a Project
Bridge.ProjectProgram 8 Project in a Program
Bridge.StoryDependsOnStory 8 Story that depends on another Story

Temporal Relationships

These bridge tables behave like Historical Fact tables. Each of the following Bridge tables will contain one row per day for the relationship they represent, after the relationship is created in your VersionOne application. If the relationship is removed in your VersionOne application, the Data Mart database will retain the historical dates when the relationship was valid.

Table Name Row Size (in bytes) Relationship
Bridge.EpicBlockedByIssue 12 Epics blocked by an Issue
Bridge.EpicOwner 12 Member that owns an Epic
Bridge.PrimaryWorkitemBlockedByIssue 12 PrimaryWorkitem blocked by an Issue
Bridge.PrimaryWorkitemGoal 12 PrimaryWorkitem assigned to a Goal
Bridge.WorkitemOwner 12 Member that owns a Workitem

Operational Table Size Information

The following tables are used by the ETL as part of its operation. They are subject to change at any time.

Log table

The ETL creates a record in the database for each execution.

Table Name Row Size (in bytes) Table Contains
Meta.ETLActivityLog 4020 One row each time the ETL runs

Internal Use Tables

These tables are used by the ETL for processing.

Table Name Row Size (in bytes) Table Contains
Meta.TableInfo 499 84 Rows
Config.NumberPattern 150 13 Rows
dbo.SystemConfig 199 2 Rows

 

Custom Fields Size Information

The sections below describe how each custom field type impacts database size

Checkbox (Booleans)

Each Custom Checkbox results in new fields on the appropriate Fact and Dimension table.

  • The appropriate Fact table is updated with new column for the custom check box and a foreign key relationship to to Dim.BooleanAttribute. This will increase the Fact table size by 4 bytes.
  • The appropriate Dimension table is updated with a nvarchar field to hold a text representation of the checkbox value. This will increase the Dimension table size by 200 bytes.

Number

Each custom number results in new fields on the appropriate Fact and Dimension table.

  • The appropriate Fact table size will increase by by 4 bytes.
  • The appropriate Dimension table size will increase by 4 bytes.

Date

Each custom date field will increase the appropriate dimension by 12 bytes. Custom Dates have no impact on Fact tables.

Text

Each custom text field will increase the appropriate dimension by 8000 bytes. Custom Text has no impact on Fact tables.

List

Custom List result in a new dimension based on the list name. This table will contain 1 row for each value defined in VersionOne, plus 2 rows: one to indicate that the list is not applicable and one to indicate that no value is set.

Table Name Row Size (in bytes)
Dim.Custom_{Custom Field Name} 8056

The appropriate Fact table is updated with a foreign key to this dimension. This will increase the Fact table size by 4 bytes.
The appropriate Dimension table is updated with a nvarchar field to hold the text representation of the selected value. This will increase the Dimension table size by 8000 bytes.
The appropriate Dimension table is updated with a foreign key field to hold the select value key. This will increase the Dimension table size by 4 bytes.

Multi-Select List

Custom Multi-Select List result in a new bridge table based on the attribute name and the asset type to which is is assigned. This table will contain 1 row for each relationship assigned in VersionOne.   The table is NOT temporal; therefore the assignments in this Bridge table are current as of the last Data Mart update. 

Table Name Row Size (in bytes)
Bridge.Custom_{AssetType}{Custom Field Name} 8

Additionally, if you create a new List of values for the Mult-Select Custom Drop Down, a List dimension will be created as defined above. 

  • Was this article helpful?