Business Problem
Yakima Chief Hops has offices around the world and data needed to be updated frequently to reflect the current-location, allocation, quality, and quantity of their products. YCH wanted to be able to take their systems to the next level and enable their sales teams to make more competitive deals, while also enabling the executive team to make stronger strategic decisions and grow the business.
Client Objective
Fueled By
We brought an executive level of understanding to the table, starting with their sales team and extending through every technical asset they placed on the team. Baufest earned trust and ultimately the business by asking hard questions, learning the underlying business before proposing solutions, and setting appropriate and achievable expectations for the project outcome.Solution Developed
A visit to YCH’s office was arranged between our client representative and a Data expert to understand firsthand their needs and work done to date. There was a significant amount of work done by YCH that needed to be considered before revamping their solution.According to our co-creation way, Baufest recommended a series of steps to allow for YCH BI team to make informed decisions for their journey into the new Enterprise Data Warehouse solution.
A Technology Decision Matrix so that YCH can compare the main features of Azure offers for EDW and decide with objective parameters the most suitable choice for their needs. An architecture diagram, resulting from the technology chosen, A suggested, phased Roadmap with clear deliverables to implement and evolve the solution, And the description of typical EDW project Roles adapted and suggested for YCHBusiness
YCH BI team had developed several reports in Power BI, supporting 150 users in the last 2 years. Realizing that the existing platform could not support requirements such as intraday updates and historical information for tendency analysis and prediction capabilities, YCH IT & BI teams envisioned an Enterprise Data Warehouse solution.
With historical production and sales data readily available, the data science team could build a model to predict the probability of over/under inventory levels, proactively freeing up inventory for additional sales.
Data Ecosystem
Data Warehouse
environment is proposed using schemas inside a single Azure SQL Database. Schemas include:
Dimensional Layer: to unify and encapsulate business logic in a single layer optimized for intuitive data access to allow for Self Service BI in the reporting tool.
Atomic Layer: consolidated 3rd normal form relational model, including business rules. Staging area: cleansed data with technical validation rules and record counts.
Raw data: Depending on origin source type. Json files can be stored in Cloud Storage to keep original format.
Data Science Sandbox can be extended using an additional Schema on Azure SQL Database, extracting information from Atomic Layer or Staging Area depending on the need, or adding additional data types (such as json), including external ad-hoc sources.
Data integration layer
SSIS is the most suitable choice in terms of current volume needs.
Azure Integration Runtime to orchestrate executions
Visualization Layer
Power BI as visualization tool
Connect with Dimensional Layer via DirectQuery. This allows to build visualizations over very large datasets, where it would otherwise be
unfeasible to first import all the data with pre-aggregation.
DirectQuery reports always use current data.Results
YCH now has access to 6 distinct business units’ data in a scalable, measurable, and controllable environment.
With the creation of the EDW at YCH we were able to create a single source for information eliminating the need to query multiple sources. This also enabled the business to focus on analysis rather than operational tasks to gather and cleanse data, ensuring consistency, quality controls and timeliness.
Real talk
It takes time to experience long term effects and benefits. The greatest immediate gain is the access of information to their entire organization and company, from executives to daily personnel. How an organization uses that information is up to them and with the right strategy can improve revenue by 5%-10% by proactively releasing overstock inventory instead of letting it build up over any extended period.
YCH Data
Admin Manager