Approach and Methodology

​

AUDIT OF DATA SOURCES AND PIPELINE

    The project began with an audit of all of our existing data sources that were currently utilized by Operations dashboards. While the majority of existing data was found within Honeywell, our Warehouse Management System (WMS), I needed to devote time to understanding the backend of how data moved from our WMS into existing dashboards on Power BI. The data pipeline was traced and summarized as follows

Understanding the backend helped identify commonly queried data tables and business logic applied in data transformations.

CONSULTATION WITH RELEVANT STAKEHOLDERS

​

Once we had a better understanding of our data pipeline, we consulted with key stakeholders to set our project up for success:

​

1. Operations Leads - They were the main stakeholders of our Operations Fulfillment Dashboard so we needed to understand how they were using existing reports. We found that there were several views that were not necessarily in use, and that there were additional KPIs that had not yet been tracked that they needed visibility on. Since they provided a lot of context to how data is used in their day-to-day activities, conversations with them became critical in identifying new tables within our WMS that we could query from 

   Refresh Frequency of the Table (A calendar table would only ever need to be refreshed once a year while

2. Data Engineering Team - During the development of the dashboard, our Data Engineering team was also in constant coordination with us as they were in the process of migrating WMS data to Snowflake. This meant that we needed to be very transparent about what Operations tables needed to be included in the migration and what our existing timelines were with delivering on key reports within the dashboard.  

​

DEVELOPMENT OF DATA MODEL AND DASHBOARD VIEWS

​

     Once we had the majority of the information we needed from our initial audit of data sources and consultations with stakeholders, we set out to translate the different business questions into a set of expected visualizations. Understanding the expected output also gave us an indication as to what data was readily available due to prior reports that were set-up. Consequently, we also identified which kind of reports we needed to create from scratch since it involved previously unutilized tables. 

From concept to actual execution, there were a lot of changes reflected in the final dashboard product since we had a constant feedback loop with our stakeholders from the Operations team

At the beginning, we relied heavily on using Postgresql and using ODBC as a main data source since we were querying directly from our WMS. Eventually, we migrated to using Snowflake which meant ensuring that our original queries were translated properly into their corresponding fields on Snowflake. 

​

    Throughout this time, we were able to develop multiple views in what would become our Operations Fulfillment Dashboard. The main feature of this dashboard was that it intended to consolidate the different use cases of the multiple dashboards that were already in play, but also introduce new features such as time intelligence, extracting order data, among others. 

One of the main features of the dashboard was including a time intelligence component so that you could not only see where in the fulfillment process certain orders where, you can prioritize which orders needed to be fulfilled given their status. 

PRESENTATION TO STAKEHOLDERS AND ADDITIONAL DATA VALIDATION

​

    There was time devoted as well to meeting up with the Operations team in order to onboard them on how to use the dashboard. We took the opportunity to also gather their feedback to see how certain views can be improved and whether or not how the dashboards were set-up align with their initial use case. 

    During this time, we also identified a transition process that will allow the Operations team to start using this new dashboard while archiving the previous versions that were no longer in use. We needed to also ensure that data accuracy was maintained so steps were taken to ensure we were regularly validating the data reflected in our reports. 
 

CREATION OF OPERATIONS DATA MART​

​

The final phase of the project came when we created the Operations Data Mart on Snowflake. By this time, all of our Operations data was migrated to Snowflake as we had a working Operations Fulfillment dashboard and an Inventory Dashboard that was worked on separately by one of my teammates . To set-up the Data Mart, we needed to  to organize and standardize how the data can be accessed so that any future reports or changes to the dashboards can be done with ease. 

The process began with mapping out how the schema would look like on Snowflake, taking note of existing specific fact or dimensions tables and how they needed to be joined together. There were already existing data models with set relationships in our Operations dashboards, so we combined these into one consolidated schema on Snowflake. 

As a result, the majority of this phase was spent in updating queries for the Snowflake Data Mart and documenting how each view or table was related to one another. Since there were transformations and field value calculations applied at a Power BI level, we replicated the business logic and aggregated data into newly created Snowflake views.  
 

The end product was a working Data Mart schema and Presentation schema on Snowflake. At a high-level, the Data Mart was set-up in the following manner: