Migrating data marts
You can migrate data marts from one environment to another, while preserving custom objects in the target environment.
The following diagram shows a data mart model:
Exporting data marts
When you export data marts, a separate folder containing the CSV files will be created for each data mart:
Example:
datamarts.csv
datamart1\facts.csv
datamart1\FactDimensionsLinks.csv
datamart1\dimensions.csv
datamart1\factattributes.csv
datamart1\dimensionattributes.csv
datamart2\facts.csv
datamart2\factdimensions.csv
datamart2\dimensions.csv
datamart2\factattributes.csv
datamart2\dimensionattributes.csv
Data marts CSV
The datamarts.csv consists of one row per data mart.
Header name | Required | If column is missing | If column exists but value is empty | Comments |
---|---|---|---|---|
Data Mart Name |
Yes |
Reject |
Reject |
Case insensitive |
Description |
No |
Empty |
Empty |
- |
Table Prefix |
No |
Empty |
Empty |
Default for future fact and dimension creation |
View Prefix |
No |
Empty |
Empty |
Default for future fact and dimension creation |
When exporting a data mart, the following objects will not be included: View schema, database name, and schema name. As these objects are environment-specific, they need to be set them up manually after importing the data mart to the target environment (unless you wish to user the defaults from the data warehouse).
Facts CSV
The facts.csv consists of one row per fact table.
Header name | Required | If column is missing | If column exists but value is empty | Comments |
---|---|---|---|---|
Name |
Yes |
Reject |
Reject |
Case insensitive The name corresponds to the fact dimension name |
Description |
No |
Empty |
Empty |
- |
Fact Type |
No |
On ADD: Transactional |
On ADD: Transactional |
Transactional, Aggregated, or State Oriented |
Fact Table Name |
Yes |
Reject |
Reject |
- |
Fact View Name |
No |
Empty |
Empty |
Empty means that no view is created |
Transaction Date |
*No |
If mandatory, reject |
If mandatory, reject |
Mandatory for transactional and aggregated facts Ignored for state-oriented facts Includes full path with dot notation; for example, in AdventureWorks it might be: OrderDetail.OrderHeader.ModifiedDate |
Source Filter |
No |
No filter |
No filter |
Filter on the source columns (which would eventually translate to an SQL "where" statement) Must be formatted as an expression |
Source Filter Params |
No |
Same name for attribute-parameter, similar to expression below |
Same name for attribute-parameter, similar to expression below. |
- |
Aggregation Filter |
No |
No filter |
No filter |
Filter on the aggregated columns (which would eventually translate to an SQL "having" statement) Must formatted as an expression |
Aggregation Filter Params |
No |
Same name for attribute-parameter, like expression below |
Same name for attribute-parameter, like expression below |
- |
Root entity |
Yes |
Reject |
Reject |
The root entity used. For example, if the fact is a denormalization of order details and orders, it will contain "orders" |
Fact As Type 1 | No |
Enable the option |
Enable the option |
Boolean: Accepts the values TRUE or FALSE For an explanation of this option, see Editing star schemas. |
Dimensions CSV
The dimensions.csv consists of one row per dimension.
Header name | Required | If column is missing | If column exists but value is empty | Comments |
---|---|---|---|---|
Name |
Yes |
Reject |
Reject |
Case insensitive |
Description |
No |
Empty |
Empty |
- |
Dimension Table Name |
Yes |
Reject |
Reject |
- |
Dimension View Name |
No |
Empty |
Empty |
Empty means that no view is created |
History Type |
No |
Type 2 |
Type 2 |
Type 1/ Type 2 |
Source Filter |
No |
No filter |
No filter |
Filter on the source columns (which would eventually translate to an SQL "where" statement) Must be formatted as an expression |
Source Filter Params |
No |
Same name for attribute-parameter, like expression below |
Same name for attribute-parameter, like expression below |
- |
Root entity |
Yes |
Reject |
Reject |
The root entity used. For example, if the fact is a denormalization of order details and orders, it will contain "orders" |
Fact dimensions CSV
The FactDimensionsLinks.csv consists of one row per dimension usage in the fact.
Header name | Required | If column is missing | If column exists but value is empty | Comments |
---|---|---|---|---|
Fact Name |
Yes |
Reject |
Reject |
Case insensitive |
Dimension Name |
Yes |
Reject |
Reject |
Case insensitive |
Referenced data mart | No | The data mart does not contain a referenced dimension. | Means that it is not a referenced dimension. | - |
Referenced dimension name | Only for referenced dimensions. | The data mart does not contain a referenced dimension. | Means that it is not a referenced dimension. | Name in the referencing data mart. |
Fact attributes CSV
The factattributes.csv consists of one row per attribute and includes the OID attributes as well.
On export, the order is determined by the attributes order. On import, the order is determined by the read order.
Header name | Required | If column is missing | If column exists but value is empty | Comments |
---|---|---|---|---|
Star schema Name |
Yes |
Reject |
Reject |
Case insensitive |
Attribute Name |
Yes |
Reject |
Reject |
Case insensitive |
Entity Path |
No |
Treat as empty |
Treat as empty |
Case insensitive. If the column is directly mapped to a data warehouse, the field will contain the model entity path (for example: Orders.Customers). An empty field means that the entity path should be calculated using an expression |
Description |
No |
Empty |
A specific attribute may have empty value as well. |
- |
Data Type |
Yes |
Reject |
Reject |
Use combined syntax: Varchar(50) Decimal(10,2) |
Aggregation |
No |
No aggregation columns (this will return an error if the Fact is aggregated). |
No aggregation columns. |
Empty or SUM/COUNT/MAX/MIN/COUNT_DISTINCT |
Expression |
No |
No expressions in any attribute. |
No expression in that attribute. |
See Stored objects. |
Expression Params |
No |
All attribute-parameter mapping is trivial (same name) |
All attribute-parameter mapping is trivial (same name) |
See Stored objects. |
Dimension attributes CSV
The dimensionattributes.csv consists of one row per dimension attribute in a fact dimension. These may also include Date or Time dimensions (e.g. Customer.ModifiedDate).
On export, the order is determined by the attributes order. On import, the order is determined by the read order.
Header name | Required | If column is missing | If value is missing | Comments |
---|---|---|---|---|
Dimension Name |
Yes |
Reject |
Reject |
Case insensitive |
Attribute Name |
Yes |
Reject |
Reject |
Case insensitive |
Entity Path |
No |
Treat as empty |
Treat as empty |
Case insensitive. If the column is directly mapped to a data warehouse, the field will contain the model entity path (for example: Orders.Customers) Am empty field, means that the entity path should be calculated using an expression |
Description |
No |
Empty |
Specific attribute may have empty value as well |
- |
Data Type |
Yes |
Reject |
Reject |
Use combined syntax: Varchar(50) Decimal(10,2) |
Expression |
No |
No expressions in any attribute |
No expression in that attribute. |
See Stored objects. |
Expression Params |
No |
All attribute-parameter mapping is trivial (same name) |
All attribute-parameter mapping is trivial (same name) |
See Stored objects. |