Automated customer reporting – E&C Consultants

We helped with

Business Intelligence
Cloud Experience
Data Vault
ETL
Snowflake

E&C is an energy procurement consulting firm that assists its customers in energy contracting, energy risk management, energy controlling, energy data management and decarbonization energy management.

More and more companies set decarbonization goals due to the occurring energy transition. E&C helps their customers make a better corporate energy transition by applying smart energy procurement practices and reducing the risk of changing energy prices.

The briefing

E&C developed advanced calculations combining data from markets, energy consumption, contracts, and other sources to advise their customers.

Given the complexity of energy contracts, a lot of custom input was required to support all possible variations. These complex calculations were performed in Excel via VBA scripts. All data was stored on-premise in a SQL Server database and SharePoint was used as a document library and presentation layer in combination with Power BI.

Because of the rapid growth of E&C as a company and the increasing amount of data, the on-premise architecture imposed restrictions for the future. To be able to serve its customers the same flexibility in the future, E&C required an optimised and scalable data architecture.

The main goals for building a scalable data architecture are:

  • Efficiently gathering and integrating data from different sources
  • Making data easily accessible for customers
  • Rapid automated reporting

Our solution

Based on a thorough data assessment and E&C’s needs for the future, we established a detailed roadmap to make the transition to a Modern Data Platform.

We set up a four-layer data architecture in Snowflake consisting of a ‘staging layer’, a ‘raw layer’, an ‘enterprise data warehouse layer’ and a ‘data mart layer’.

  • The staging layer is used for transferring the data from the data sources to Snowflake.
  • The raw layer acts as a ‘data lake’ in Snowflake, storing data in its original format.
  • The enterprise data warehouse layer contains the data model based on Data Vault 2.0.
  • The data mart layer contains views of data that Power BI will use.

Incorporating ‘Change Data Capture’ offers the flexibility of only inserting the changed or added data instead of repeatedly loading all data.

Alteryx was deployed as a Data Integration Service to load data into Snowflake and move between the different data warehouse layers. Alteryx provides a dynamic way to build the insertion scripts to load data into the Data Vault.

We used Power BI to build automated reports. A dimensional data model was created based on the views in the data mart layer in Snowflake, which was then used to feed Power BI with data.

The outcome

The integration of a cloud-based data warehouse in Snowflake, dynamic dataflows in Alteryx and automated reporting in Power BI provides E&C with a Modern Data Platform that is scalable and future proof.

Cloubis provided E&C with a Modern Data Platform. This platform is scalable according to their growth and will support E&C to achieve their goals to be more flexible, serve their clients more rapidly and have all their data in one place.

Does your project need our expertise?