Data Modelling (Energy Tariffs)

Objective :

Produce a logical data model that will support querying with SQL and visualisation tools such as Power BI.
Example use cases for this data include:
[if !supportLists]· [endif]At a specified date in time, monthly, quarterly and annual, produce summary statistics of cheapest, average and most expensive tariffs [if !supportLists]· [endif]As above by tariff type [if !supportLists]· [endif]As above by service type (E = Electricity, G = Gas, D = Dual Fuel) [if !supportLists]· [endif]As above by supplier [if !supportLists]· [endif].

Data :

The Domestic Tariffs (DT) data source is available via an API call.
The source data, from the API, is updated daily.
The data is open source and has no PII.

Logical Data Model :

• STAR schema

• Warehousing for OLAP systems

• SQL queries and aggregations run smoothly

Ease of querying and Visualization

• Clearly defined relationships between dimension and fact tables, enables straightforward joins in SQL queries.

• Common dimensions across different fact tables allow for consistent reporting and analysis, reducing the complexity for end-users.

• Power BI filtration across dimensions like service_type, region etc.

Performance and Scaling

• Critical columns, indexed to speed up query performance can be implemented easily.

• The data is partitioned (e.g., by date or region) to reduce query load times, particularly for large datasets.

• The model is designed to handle an increasing volume of data without a significant drop in performance, thanks to our schema that accommodates growth in data size and complexity.