Modelling data with Microsoft SQL and PowerBI
Updated: Aug 10
The daily activities of organisations generate multiple points of data, and these data points are not all in the same location. Some data may be in sales, some in production and other in HR. This causes difficulties in determining relationships between the data and gaining a holistic view of the organisation when trying to make decisions. Combining the data can be a massive task, involving multiple sheets and multiple individuals trying to make the connections. This also tends to become a regular task, where focus could have rather been placed on the analysis of the data.
Belgotex is a leading carpet and vinyl flooring manufacturer. As a soft flooring specialist, they design, make and distribute high-quality flooring solutions. Their organization consists of multiple parts and processes, generating vast amounts of data. The problem was that the data was spread across the organization and required significant effort to combine and analyse.
First Digital was initially hired to develop dashboards. We then identified gaps in the availability data to report on and initiated the development of a common data warehouse where all the data could be moved to and be modelled.
The data was extracted from the IBM servers (IBM db2 I series) and moved to a central SQL server. The data was then modelled and relationships identified with the help of the various business users and the various subject matter experts. All data was moved first to a staging area, then to a transformation area where the business logic was added, and any transformations applied to make the data usable and enrich it. We provided Belgotex with a semantic layer to report from and it provided them with reporting capabilities they previously did not have. This reporting included dynamic reporting and drill through functionality. The solution was designed and built to incorporate future real-time reporting from IoT devices spread throughout the factory.
SUMMARY AND BENEFITS
• Dynamic reporting that allows for richer data explorations.
• Central location for all the data to identify relationships and correlations.
• Fully automated to allow users to focus on the analysis rather than the wrangle of data.