devoteam
ABOUT

While working for a top energy player in Portugal with a market cap of 12 billion $, I managed to solve many underlying and struggling issues for this company. Below is just one example.


Project: 360 Client View.
Overview: Development of a one-pager Power BI Report that included all the transactional data (based on two measures: volume and margin) of all the companies products.

Tech stack: Power BI, Dremio
Languages: SQL, M, Dax
Sources and systems used: SAP BW S4, multiple files (json, csv, xlsx), Dataflows, Dremio, Dataverse, Google Analytics, Adobe Analytics, REST APIs
Problem
The KAMs for the B2B business unit didn't have a centralized view of their clients. They were not aware of their volumes and margins per products nor of their information on the company's CRM (active agreements, opportunities, leads, etc.) in an unique one pager report.
Data Sources and Preparation:
First, since the company had an ongoing governance issue - business units don't communicate with each other nor have processes - I decided to address the business owners of the datasets that contained volumes and margins of the products: the Revenue Assurance department. I requested them dataflows, in order to ensure there was a single source of truth.
Regarding, dimensional datasets, I decided to use the company's CRM - Microsoft Dynamics.
Data prep techniques
Some of the data, as in any real-world case was messy...
Whatever the raw datasets would be, I had to ensure I tailored data so that I could have a star-schema. This is the best solution since it's scalable and performant for the Power BI tool.
Let's look at the Accounts dataset.
Since some of the data was missing in certain foreign keys (e.g. the client SAP id field in order to connect to transactional views from SAP BW S4 dataflows), in order to ensure referential integrity, I had to fill the missing values with unique identifiers.
Another good example of data prep is the Opportunities dataset. The CRM's data model is a snow-flake style, which isn't the best option for Power BI. In order to ensure that my model maintained it's star-schema, I had to join multiple tables (including junction tables from the system), so that I could link the Opportunities with the Accounts and Product dataset.

Diving more deeper into the realms of debt and credit data where no one even had an ERD, I also had to create composite keys which were a concatenation of various functionally relevant fields. I also had to do some error handling since some of the data had null values.
Problem
The KAMs for the B2B business unit didn't have a centralized view of their clients. They were not aware of their volumes and margins per products nor of their information on the company's CRM (active agreements, opportunities, leads, etc.) in an unique one pager report.
Data Sources and Preparation:
First, since the company had an ongoing governance issue - business units don't communicate with each other nor have processes - I decided to address the business owners of the datasets that contained volumes and margins of the products: the Revenue Assurance department. I requested them dataflows, in order to ensure there was a single source of truth.
Regarding, dimensional datasets, I decided to use the company's CRM - Microsoft Dynamics.
Data prep techniques
Some of the data, as in any real-world case was messy...
Whatever the raw datasets would be, I had to ensure I tailored data so that I could have a star-schema. This is the best solution since it's scalable and performant for the Power BI tool.
Let's look at the Accounts dataset.
Since some of the data was missing in certain foreign keys (e.g. the client SAP id field in order to connect to transactional views from SAP BW S4 dataflows), in order to ensure referential integrity, I had to fill the missing values with unique identifiers.
Another good example of data prep is the Opportunities dataset. The CRM's data model is a snow-flake style, which isn't the best option for Power BI. In order to ensure that my model maintained it's star-schema, I had to join multiple tables (including junction tables from the system), so that I could link the Opportunities with the Accounts and Product dataset.

Diving more deeper into the realms of debt and credit data where no one even had an ERD, I also had to create composite keys which were a concatenation of various functionally relevant fields. I also had to do some error handling since some of the data had null values.
Made on
Tilda