Standardised DataMart Model

Core Views and their Standard Structures

Each DataMart will be built on six primary views:

Details (or Core) – Foundational attributes, primary keys, and entity identification.
Dates – Key lifecycle dates in ISO format.
Values – Measures and calculated fields in structured form.
Codes – Dimensions, hierarchies, and identifiers.
Status – Lifecycle state, progress indicators, and historical tracking.
Roles – Entity-to-person relationships, e.g., project managers.

1. Details (or Core) View

Purpose:

This view acts as the foundation for the DataMart.
It contains primary entity identifiers and high-level descriptive information.
The Company and Object_ID couplet serves as the primary key.
It includes a structured JSON object (SEQ) to support further relationships and metadata.

Schema:

Column Name	Data Type	Description
`company`	`VARCHAR`	Primary entity identifier
`object_id`	`VARCHAR`	Unique object identifier (project_id)
`seq_json`	`JSON`	Structured metadata reference for linking
`name`	`VARCHAR`	Name of the project/entity
`description`	`TEXT`	Brief descriptive text
`category`	`VARCHAR`	Categorical classification (e.g., business unit, cost centre)

🔹 Example of SEQ_JSON structure:

{
  "project_id": "123-456",
  "company": "BMT",
}

2. Dates View

Purpose:

This view stores all key dates related to the entity in a meta/fact table format.
Dates are always stored in YYYY-MM-DD ISO format.
Provides single-instance lifecycle tracking—for full history, a separate history table is required.

Schema:

Column Name	Data Type	Description
`seq_json`	`VARCHAR`	Foreign key to `details.core`
`key`	`VARCHAR`	Date attribute name (`start_date`, `end_date`)
`meta_value`	`DATE`	Lifecycle date in ISO format

🔹 Example:

seq_json	key	meta_value
{ “project_id”: “123-456”, “company”: “BMT”,}	start_date	2025-01-12
{ “project_id”: “123-456”, “company”: “BMT”,}	completion_date	2025-06-30

3. Values View

Purpose:

Stores all numeric and financial measures.
Includes a JSON object to store base values, exchange rates, and units.
Allows for flexible value representation, including currency conversions.

Schema:

Column Name	Data Type	Description
`seq_json`	`VARCHAR`	Foreign key to `details.core`
`key`	`VARCHAR`	Measure name (`total_cost`, `revenue`)
`meta_value`	`DECIMAL(18,2)`	Stored numerical value
`meta_details`	`JSON`	JSON object describing base/currency values

🔹 Example of meta_details JSON:

{
  "base_value": 1000000,
  "currency": "GBP",
  "exchange_rate": 1.2,
  "local_value": 1200000
}

🔹 Example:

seq_json	key	meta_value	meta_details
{ “project_id”: “123-456”, “company”: “BMT”,}	total_revenue	1000000	`{"currency":"GBP","exchange_rate":1.2,"local_value":1200000}`
{ “project_id”: “123-456”, “company”: “BMT”,}	total_hours	5000	`{"unit":"hours"}`

4. Codes View

Purpose:

Stores dimension values such as cost centres, project hierarchies, and external relationships.
Provides structured code-description pairs or parent-child hierarchies.
Ensures consistency in linking datamarts, especially for shared identifiers.

Schema:

Column Name	Data Type	Description
`seq_json`	`VARCHAR`	Foreign key to `details.core`
`key`	`VARCHAR`	Dimension type (`customer_id`, `category_code`)
`meta_value`	`VARCHAR`	Dimension ID or code
`meta_desc`	`VARCHAR`	Description of the code
`parent_code`	`VARCHAR` (nullable)	Parent in hierarchy (if applicable)

🔹 Example:

json_seq	key	meta_value	meta_desc	parent_code
{ “project_id”: “123-456”, “company”: “BMT”,}	customer_id	CUST-789	ACME Corp	NULL
{ “project_id”: “123-456”, “company”: “BMT”,}	pru_code	PRU-101	Engineering Division	PRU-100
{ “project_id”: “123-456”, “company”: “BMT”,}	project_hierarchy	PH-01	Phase 1	PH-00
{ “project_id”: “123-456”, “company”: “BMT”,}	cost_hierarchy	CC-02	Cost Centre 2	CC-01

5. Status View

Purpose:

Tracks the current state of an entity and enables historical status tracking.
Similar to a code but treated separately due to state transitions.
Captures timestamped status changes.

Schema:

Column Name	Data Type	Description
`json_seq`	`VARCHAR`	Foreign key to `details.core`
`key`	`VARCHAR`	Status type (`project_status`, `approval_state`)
`meta_value`	`VARCHAR`	Current status (`Active`, `Pending`, `Completed`)
`status_date`	`DATE`	Date when status changed

🔹 Example:

json_seq	key	meta_value	status_date
{ “project_id”: “123-456”, “company”: “BMT”,}	project_status	In Progress	2025-02-01
{ “project_id”: “123-456”, “company”: “BMT”,}	project_status	Completed	2025-06-30

6. Roles View

Purpose:

Captures personnel and entity relationships (e.g., Project Manager, Line Manager).
Includes email for direct reference.
Useful for ownership and accountability tracking.
This view is not intended for resource activity tracking but rather for associating entities with key personnel

Schema:

Column Name	Data Type	Description
`json_seq`	`VARCHAR`	Foreign key to `details.core`
`key`	`VARCHAR`	Role type (`project_manager`, `line_manager`)
`meta_value`	`VARCHAR`	Employee ID
`meta_email`	`VARCHAR`	Email (if applicable)

🔹 Example:

json_seq	key	meta_value	meta_email
{ “project_id”: “123-456”, “company”: “BMT”,}	project_manager	EMP-1001	pm@example.com
{ “project_id”: “123-456”, “company”: “BMT”,}	line_manager	EMP-2002	lm@example.com

Conclusion

This standardised common DataMart model:
✅ Ensures consistency across all DataMarts
✅ Provides a clear separation of entity details, measures, and dimensions
✅ Enables easy JSON-based extensions
✅ Supports historical tracking via status

2. Potential Additional View Types (For Consideration)

2.1 History View

Purpose: Captures historical changes in attributes over time.
Why? The Status table captures high-level changes, but if field-level tracking (e.g., changes in project budgets, scope, or assigned teams) is needed, a dedicated history table may be beneficial.

Schema Example:

Column Name	Data Type	Description
`company`	`VARCHAR`	Foreign key to `details.core`
`project_id`	`VARCHAR`	Foreign key to `details.core`
`key`	`VARCHAR`	Attribute being tracked (`budget`, `priority`)
`old_value`	`VARCHAR`	Previous value
`new_value`	`VARCHAR`	Updated value
`change_date`	`DATE`	Date of change

2.2 Relationships View

Purpose: Defines associations between different entities, such as dependencies or parent-child relationships.
Why? While Codes captures some hierarchical data, a more structured approach to linking projects, employees, customers, or external entities may be needed.

Column Name	Data Type	Description
`company`	`VARCHAR`	Foreign key to `details.core`
`project_id`	`VARCHAR`	Foreign key to `details.core`
`related_entity`	`VARCHAR`	The linked entity (`customer_id`, `parent_project_id`)
`relationship_type`	`VARCHAR`	Type of relationship (`sub-project`, `linked_customer`)

2.3 Attachments View

Purpose: Stores references to external documents, images, or URLs.
Why? If project records need to be linked to documentation (e.g., contracts, reports, compliance documents), a structured way to track these references is useful.
Schema Example:

Column Name	Data Type	Description
`company`	`VARCHAR`	Foreign key to `details.core`
`project_id`	`VARCHAR`	Foreign key to `details.core`
`attachment_type`	`VARCHAR`	Type of document (`contract`, `invoice`)
`file_path`	`VARCHAR`	Link to the document location

2.4 Notes/Comments View

Purpose: Allows free-text notes, comments, or annotations.
Why? This is useful for tracking audit trails, collaboration comments, or additional explanations tied to a project, employee, or other entity.
Schema Example:

Column Name	Data Type	Description
`company`	`VARCHAR`	Foreign key to `details.core`
`project_id`	`VARCHAR`	Foreign key to `details.core`
`comment`	`TEXT`	Free-text note or annotation
`added_by`	`VARCHAR`	User who added the comment
`added_date`	`DATE`	Date of comment

2.5 Forecast/Projections View

Purpose: Stores future estimates for budget, costs, or resource planning.
Why? If forecasting is a critical part of your analytics, a dedicated table can hold expected versus actual values.
Schema Example:

Column Name	Data Type	Description
`company`	`VARCHAR`	Foreign key to `details.core`
`project_id`	`VARCHAR`	Foreign key to `details.core`
`forecast_type`	`VARCHAR`	Type (`budget`, `revenue`, `resource_hours`)
`forecast_value`	`DECIMAL(18,2)`	Predicted value
`forecast_date`	`DATE`	Date of forecast

{"Current stable version":"202409","exec":"@version = ‘202409’","Development" : "@version = ‘beta’","Change":"Regional View"}

{"Current stable version":"202409","exec":"@version = ‘202409’","Development" : "@version = ‘beta’","Change":"Stable 09 View"}

Expected Condition The DataMart View BusOpp.Dates is in the style of 202409 As Found Condition The unversioned (beta) edition of…

Double check your WITH READ ONLY — it’s typically only used on views, not raw SELECT queries unless you're defining…

{"Current stable version":"202409","exec":"@version = ‘202409’","Development" : "@version = ‘beta’","Change":"Cross Apply version"}

{"Current stable version":"202409","exec":"@version = ‘202409’","Development" : "@version = ‘beta’","Change":"CTE Version"}

Core Views and their Standard Structures

1. Details (or Core) View

Schema:

2. Dates View

Schema:

3. Values View

Schema:

4. Codes View

Schema:

5. Status View

Schema:

6. Roles View

Schema:

Conclusion

2. Potential Additional View Types (For Consideration)

2.1 History View

2.2 Relationships View

2.3 Attachments View

2.4 Notes/Comments View

2.5 Forecast/Projections View

Leave a Comment Cancel reply