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
- 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
- Start by expecting a database 10x the size of your Agility application database.
- After the initial load, monitor the database size daily for several weeks to determine growth patterns.
- Respond accordingly.
- 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 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 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 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 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 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 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 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 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 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 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 Name | Row Size (in bytes) | Corresponding VersionOne Data |
Tree.EpicParent | 12 | Epics |
Tree.PrimaryWorkitemSplitFrom | 12 | Story |
Tree.ProjectParent | 12 | Scope |
Tree.ThemeParent | 12 | Theme |
- Tree.EpicParent only contains Epics. It does not contain entries for leaf level stories that comprise an Epic.
- 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 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 Primary Workitems |
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 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 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
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 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 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 Name | Row 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.