The Excel template defines how an indyco Builder project can be mapped to an Excel file.
To obtain a sample Excel file, open the sample project (menu “View > Start page”, then "open sample project...") and export it to Excel (menu "Tools > Export > Project to Excel).
The Excel file is organized into four worksheet templates:
| PROJECT | Describes the basic structure of the project: it’s name, the list of fact schemata and conformed hierarchies and some mappings between logical names and Excel worksheets. |
| FT_MEAS_* | Enumerates the measures contained into a fact schema. One sheet per fact schema . |
| FT_ATTR_* | Enumerates the attributes contained into a facts schema and their relations (hierarchies). One sheet per fact schema. |
| CH_ATTR_* | Enumerates the attributes contained into a conformed hierarchy and their relations. One sheet per conformed hierarchy. |
PROJECT worksheet
The PROJECT worksheet must contain:
- A cell named “projectName”, anywhere on the sheet, containing the project name.
- An Excel table named “prj_facts” containing the list of fact schema, their properties and a reference to the worksheets containing their definitions.
- An Excel table named “prj_hierarchies” containing the list of fact schema and a reference to the worksheets containing their definitions.
If you do not know how to name a cell, have a look at this Microsoft tutorial on Excel named ranges.
If you do not know about Excel tables, have a look this Microsoft tutorial.
Expected columns of "prj_facts" table
These columns are mandatory. Any additional column will be ignored.
Name | The fact schema name |
Sheet name | The identifier of the two worksheets that defines this fact schema. Every fact schema is defined by two worksheets: FT_ATTR_<sheet-name> and FT_MEAS_<sheet-name>. |
Description | The fact schema description |
Area(s) | A semicolon separated list of areas containing this fact schema |
Datamart | The datamart containing this fact schema |
Historical depth | The historical depth of the fact into this fact schema |
Loading Frequency | The loading frequency of the fact into this fact schema |
FT_MEAS_* worksheet
This sheet must contain an Excel table listing every measure contained in the referenced fact schema.
Every additional column will be treated as a custom property, so its definition will be added to the project and its value will be added to the measure.
| Item Name | The measure name |
| Description | The measure description |
| Notes | The measure notes |
| Source Table | The source table |
| Source Field | The source field |
| Formula | A formula describing how the measure is computed. To reference other measures: [[FactName.MeasureName]] To reference other attributes: [[FactName.AttributeName]] |
FT_ATTR_* worksheet
This sheet must contain an Excel table listing every attribute contained in the referenced fact schema and their relations (arcs).
Every additional column will be treated as a custom property, so its definition will be added to the project and its value will be added to the attribute.
| Item ID | The identifier of an attribute into this worksheet. It is required to define relations between attributes (arcs): the attribute name does not univocally determine an attribute. |
| Item Type | Used to specify if the current row is describing an attribute or an arc. Valid values: “Attribute” or “Arc”. |
| Hierarchy | The hierarchy name of the current attribute. Not used on arc rows. |
| Conformed Hierarchy | If the current attribute is imported from a conformed hierarchy, here you have to put the conformed hierarchy name. Not used on arc rows. |
| Conformed Hierarchy Attribute | If the current attribute is imported from a conformed hierarchy, here you have to put the attribute name on the conformed hierarchy. In order to import a conformed hierarchy into a fact you don’t need to define every attribute of the conformed hierarchy: just the root attribute of your choice. Every other attribute will be added automatically. Not used on arc rows. |
| Item Name | On attribute rows, it contains the attribute name. On arc rows, it contains the arc role. |
| From Item ID | In case the current attribute have just one parent, you can put its ID to define the relation. This is an easier alternative to define an entire arc row. The arc will be created with standard properties. If the current attribute is connected directly to the fact, just use the standard id “FACT”. Not used on arc rows. |
| To Item ID | On arc rows, it should be used in conjunction with “From Item ID” to define the source and target element this arc will connect to. |
| Description | A description of the current attribute. Not used on arc rows. |
| Sample Values | The sample values of the current attribute. Not used on arc rows. |
| Notes | The notes of the current attribute. Not used on arc rows. |
| Is Multiple | A Boolean value indicating that the current arc is multiple. Not used on attribute rows. |
| Is Optional | A Boolean value indicating that the current arc or attribute is optional. |
| Is Shared | A Boolean value indicating that the current attribute is shared. Not used on arc rows. |
| Is Descriptive | A Boolean value indicating that the current attribute is descriptive. Not used on arc rows. |
| Is Cross Dimensional | A Boolean value indicating that the current attribute is cross dimensional. Not used on arc rows. |
| Is Convergence | A Boolean value indicating that the current attribute is convergent. Not used on arc rows. |
| Temporal Scenario | The temporal scenario of the current attribute. Allowed values are “UpToDate”, “Historical” and “Rollback”. Not used on arc rows. |
| Source Table | The source table of the current attribute. Not used on arc rows. |
| Source Field | The source field of the current attribute. Not used on arc rows. |
CH_ATTR_* worksheet
This sheet must contain an Excel table listing every attribute contained in the referenced conformed hierarchy and their relations (arcs).
The format is the same as FT_ATTR_* worksheet.