Simple format file can be an Excel or an appropriately formatted csv or a tsv file. The Excel workbook should ideally contain one tab containing data. If more than one tab is present, you will be prompted to select the tab containing the data. In all formats the data must also conform to following rules:
- The first row must be the column names. Column names cannot have special characters, unclosed quotes, or new lines. Example as shown in the table below: Id, Name, To Do
- It is preferred that the first column be ID column. The ID column header should be labeled as id, ID or must contain word ID in it. ID column must be unique alphanumeric or integer. for example C1, UST3, TSK4.
Note: If no ID column is provided it is auto-generated.
- Short description of the card if present should be labeled under the Name column header. Whenever possible the name column should appear second following the ID column.
- Both the column names: ID and Name are reserved and case insensitive. They should be used for the above-described purpose only.
- Each column should have values of only one data type, i.e. string, number, or date.
- Date should be in US date format, like,
mm/dd/yyyyor ISO 8601 date format. If the date has time component it must be in
- Date columns are treated as workflow states (Kanban queues) by default. If a date column is an attribute of the card object, you can select it on the next page.
- The workflow columns should be defined corresponding to the sequential steps of the workflow. This can be changed during loading of data.
- The date/time in workflow column (queue) represents the time when the card entered that column.
- Workflow column names “Backlog” and “Archive” are reserved. In addition, names containing “Done” refer to “Wait” states (buffer).
- Any number of generic columns can follow the workflow steps. These columns are treated as card attributes.
- Only card attributes of type string that come from small set of values (e.g. size: small, medium, high) can be used as filter attributes. Numeric and Date fields can be present but their use is limited.
|1||C1||First Card||10/14/2017 10:30:45||10/16/2017 09:00:00||10/20/2017 14:15:30||UserStory||Standard|
|2||C2||Second Card||10/14/2017||10/18/2017 12:10:00||10/21/2017 09:30:15||Enhancement||Fixed Date|
|3||C3||Third Card||10/15/2017 08:00:00||Task||Intangible|
|4||C4||Fourth Card||10/18/2017 16:45:14||10/20/2017 09:00:00||Defect||Standard|
During loading, the data is verified. As a part of this verification, you can change the assignment of columns between card and queue. Read more about the verification process.
Moreover, you can also ignore columns or re-order the queues in the value-stream if they are not in correct order in the file.
The simple format provides an easy way to extract data from the external systems for flow-based analysis. However, it has following limitations:
- A card can be in a given workflow state only once. This represents a linear sequential workflow. But if the workflow has cycles, a card can be in the same workflow state more than once. This cannot be represented by the simple format.
- Changes to card attributes during its journey through the workflow cannot be represented in the simple format.
- Blocking and unblocking, deleting/discarding cannot be represented using the simple format.
When these conditions are present, it is suggested to use the Advanced Excel format.
You can download the sample Excel sheet here.
Advanced Excel Format
For analysis of data where the cards can be moved both forward and back (cyclic workflow), and events other than movement, such as Blocking and Unblocking need to be measured, the simple Excel format is not sufficient.
To support such type of analysis, we have developed a second advanced format based on Excel Workbook. The Excel workbook contains five worksheets that are used to import data. Additional tabs, if present, are ignored.
These five tabs are:
- Board: Defines the structure of the value stream
- Cards: Define all the work items and their attributes,
- Log: Sequential log of events such as move, block, unblock and so on.
- Extent: Provides additional (optional) information needed for analysis, such as the start date and end date of the logs, and the master lists for filtering cards and so on. Such type of information, if not present in the source file, is then generated from the available cards and logs.
- Attributes: Provides information about card attributes.
Note: All the column headers in the above tabs of the advanced Excel file are case sensitive. So, you must conform to it as prescribed in the sample Excel file.
This tab describes the workflow or value stream. Each row below the queueName column header describes one workflow state (value stream queue). The row order represents the sequential forward flow of the workflow. For workflows with cycles, the order should maximize the forward direction of flow.
- queueName: A queue name is a string name for the column or a sequence of names separated by # in of nested column structure. It is best to avoid special characters other than space, dash, underscore, and pound sign.
- queueType: Type of queue. The system recognizes four types of queues
- Ready: if present, this is the first queue in the workflow. This represents the commitment to work on the card
- In Progress: This type of queue represents steps where work is performed.
- Wait: The card is waiting to be pulled into the next state.
- Done: The card is completed.
- WIP: Defines the WIP for the column. This is used during playback visualization, and may also be used in the future for other types of Analytics.
|Ready for development||Ready||10|
|Development#Coding + Junits||In Progress||3|
|Development#Func Automation||In Progress||3|
|Development#Code Review||In Progress||2|
This tab defines all the cards that participate in the workflow. The format for this tab is similar to the simple Excel format, except that it contains only the attributes of the cards. As described earlier, the column header ID and Name are reserved. All other column headers are optional and data dependent. In addition to ID and optionally Name, the card must have at least one additional attribute (like Type, Class of Service, priority as mentioned in the table below) that can be used to filter the cards. Moreover, the first column must be the ID column.
|UST11210||UST102 UST||Intangible Class||M||Medium|
This tab logs the changes to the state of the card. This tab should have the following column headers which define the content.
|block||11/25/16 01:09:16||UST616255||Development#Design||reason||Task Switching|
- operation: the operation to be performed on a card and column. The operations are:
- moveTo: Move the card into the queue.
- block: Block the card in the queue.
- unblock: Unblock the card in the queue.
- delete: Delete the card in the queue.
- at: The time when the operation is performed. The time must be in
- card: ID of the card.
- queue: The queue where the card is at the end of the operation.
- attribute: Name of the card attribute, If the additional attribute is associated with the operation.
- value: Value of the additional attribute.
These operations must be specified in increasing time order. That is the time of a given operation should be same or greater than preceding operation. If the log is not in temporal order, the extension tab should specify as
This tab defines the card attributes, their data type, and use.
- name: Name of the attribute
- type: Data type of attribute. Possible data types are string, number, and date.
- fun: Defines how the attribute is used in analysis. It can take following values:
- id: column to be used as id for the card
- name: column to be used for name or short description of the card
- filter: Only string attributes with small number of unique values can be used as a filter. The filter specifies that these values are shown in the filter section of the analysis and can be used to filter the dataset.
- other: other attributes, that are loaded and can be used for pivot analysis, but are not part of the filter.
- Ignore: these attributes are not loaded.
This tab provides additional information about the time-range for analysis and if the logs are sorted in ascending order by time.
- extent: the start and end dates
mm/dd/yyyyfor display of analytic charts. Often it is useful to have log data that precede the time-frame of analysis to allow the cycle time computation for cards that begin their journey through the workflow before the start of the analysis. If not provided, it is assumed that the period of analysis is same as the start and end of the log data.
- sort: TRUE, if the log data is pre-sorted, FALSE otherwise. The default value is TRUE.
You can download a sample Excel sheet here.