Skip to main content
Version: Early Access

Database Sizing

Database sizing is the process of estimating the storage capacity, memory requirements, and computational resources needed for a database to function efficiently and effectively over its expected lifespan.

Overview

The Digital.ai Agility Data Mart database is a denormalized representation of your highly-normalized Agility 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 Agility application instance, and it is expected to grow continuously over time.

Initial database size is a determined by the amount of data in your Agility 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 Agility application database.

The growth of the database also depends on the amount of data in your Agility 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 Agility 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 Agility 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 Agility 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 Agility 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 Agility 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 Agility 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 NameRow Size (in bytes)VersionOne List
Dim.AttachmentCategory8064Attachment Type
Dim.BuildSource8064Build Source
Dim.BuildStatus8064Build Status
Dim.ClassOfService8064Class Of Service
Dim.DefectResolution8064Defect Resolution
Dim.DefectType8064Defect Type
Dim.EffectiveAssetState18Asset State based on AssetState and the Reporting Category assigned to Status values
Dim.EpicPriority8064Epic Priority
Dim.EpicStatus8064Epic Status
Dim.EpicType8064Epic Type
Dim.GoalPriority8064Goal Priority
Dim.GoalType8064Goal Type
Dim.IssueCategory8064Issue Type
Dim.IssuePriority8064Issue Priority
Dim.IssueResolution8064Issue Resolution
Dim.IssueStatus8064Issue Status (No longer available)
Dim.NoteCategory8064Note Type (No longer available)
Dim.ProjectStatus8064Project Status
Dim.RegressionTestStatus8064Regression Test Status
Dim.RequestCategory8064Request Type
Dim.RequestPriority8064Request Priority
Dim.RequestResolution8064Request Resolution
Dim.RequestStatus8064Request Status
Dim.StoryRisk8064Workitem Risk
Dim.StoryType8064Story Risk
Dim.StrategicThemeLevel8064Strategic Theme Level
Dim.TaskSource8064Task Source
Dim.TaskStatus8064Task Status
Dim.TaskType8064Task Type
Dim.TestStatus8064Acceptance Test Status
Dim.TestType8064Test Type
Dim.ThemeArea8064Theme Area
Dim.ThemeComplexity8064Workitem Risk
Dim.ThemeSource8064Theme Source
Dim.ThemeStatus8064Theme Status
Dim.ThemeType8064Theme Type
Dim.WorkitemPriority8064Workitem Priority
Dim.WorkitemSource8064Workitem Source
Dim.WorkitemStatus8064Workitem Status

Data Dimensions

The following dimensions correspond to specific data types in your Agility 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 Agility 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 Agility 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 NameRow Size (in bytes)VersionOne Datatype
Dim.BuildProject16040BuildProject
Dim.Environment32094Environment
Dim.Epic112208Epic
Dim.EpicTest48124Test
Dim.Goal48094Goal
Dim.Issue104130Issue
Dim.Iteration64312Timebox
Dim.Member32260Member
Dim.MemberGroup8040MemberLabel
Dim.PrimaryWorkitem240318Sum the counts for Story, Defect, and TestSet
Dim.Program8040ScopeLabel
Dim.Project88524Scope
Dim.RegressionPlan56298RegressionPlan
Dim.RegressionSuite64336RegressionSuite
Dim.RegressionTest72132RegressionTest
Dim.Request104114Request
Dim.Schedule24240Schedule
Dim.SecondaryWorkitem128282Sum the counts for Task and Test
Dim.StrategicTheme32110Strategic Theme
Dim.Team8164Team
Dim.TestSuite16060TestSuite
Dim.Theme128148Theme

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 NameRow Size (in bytes)Row CountEstimated Size (in bytes)
Dim.AssetState188144
Dim.AssetType1047728
Dim.BooleanAttribute2044816
Dim.Date21576721611120

Fact Table Size

Fact Tables store the historical record of changes to your Agility 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 Agility 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 Agility.

The Agility 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 Agility 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 NameRow Size (in bytes)VersionOne Datatype in the SystemInfo Report
Fact.BuildRun16036BuildRun
Fact.Capacity24Capacity
Fact.Effort60Actual
Fact.TestRun40TestRun

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 Agility 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 Agility 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 NameRow Size (in bytes)VersionOne Datatype
Fact.Epic104Epic
Fact.EpicTest36Test
Fact.Issue40Issue
Fact.PrimaryWorkitem112Stories, Defects, and TestSets
Fact.ProjectSummary192Project Metrics
Fact.Request40Requests
Fact.Task60Task
Fact.Test60Test

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 NameRow Size (in bytes)
Fact.Workitem144

Days In Facts

These fact table contain current metrics for how log a Agility 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 NameRow Size (in bytes)Description
Fact.EpicDaysIn32Epics by Status Value
Fact.PrimaryWorkitemDaysIn32Primary Workitems by Status Value

Tree Table Size Information

Tree Tables contain information about data hierarchies in Agility. Each table contains one row per node in the Agility 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 Agility 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 NameRow Size (in bytes)Corresponding VersionOne Data
Tree.EpicParent12Epics
Tree.PrimaryWorkitemSplitFrom12Story
Tree.ProjectParent12Scope
Tree.ThemeParent12Theme
  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 Agility 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 Agility instance and they will grow as these relationships are added. If the relationship is removed in the Agility application, it will be removed in the Data Mart database.

Table NameRow Size (in bytes)Relationship
Bridge.DefectFoundInBuildRun8Defect Found in a BuildRun
Bridge.EpicStrategicTheme8Epic Strategic Theme Relationship
Bridge.GoalTargetedByProject8Goal Targeted to a Project
Bridge.MemberGroup8Member in a Member Group
Bridge.PrimaryWorkitemBrokenByDefect8Primary Workitems Broken By Defect
Bridge.PrimaryWorkitemDependencies8Upstream and Downstream dependencies between Primary Workitems
Bridge.PrimaryWorkitemCompletedInBuildRun8PrimaryWorkitem Completed in a BuildRun
Bridge.PrimaryWorkitemRequest8PrimaryWorkitem to Requests
Bridge.PrimaryWorkitemResolvedIssue8PrimaryWorkitem Resolves an Issue
Bridge.ProjectMember8Member that can view data in a Project
Bridge.ProjectProgram8Project in a Program
Bridge.StoryDependsOnStory8Story 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 Agility application. If the relationship is removed in your Agility application, the Data Mart database will retain the historical dates when the relationship was valid.

Table NameRow Size (in bytes)Relationship
Bridge.EpicBlockedByIssue12Epics blocked by an Issue
Bridge.EpicOwner12Member that owns an Epic
Bridge.PrimaryWorkitemBlockedByIssue12PrimaryWorkitem blocked by an Issue
Bridge.PrimaryWorkitemGoal12PrimaryWorkitem assigned to a Goal
Bridge.WorkitemOwner12Member 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 NameRow Size (in bytes)Table Contains
Meta.ETLActivityLog4020One row each time the ETL runs

Internal Use Tables

These tables are used by the ETL for processing.

Table NameRow Size (in bytes)Table Contains
Meta.TableInfo49984 Rows
Config.NumberPattern15013 Rows
dbo.SystemConfig1992 Rows

Custom Fields Size Information

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

Check box (Booleans)

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

  • The appropriate Fact table is updated with new column for the cusm 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 check box 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 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 NameRow 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 Lt 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 Agility. The table is NOT temporal; therefore the assignments in this Bridge table are current as of the last Data Mart update.

Table NameRow Size (in bytes)
Bridge.Custom\_{AssetType}{Custom Field Name}8

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