Building a Data Shipyard for BW LNG

BW LPG is a leading global provider of maritime gas transportation services, specializing in the shipping of liquefied petroleum gas (LPG). The company owns and operates one of the world’s largest fleets of Very Large Gas Carriers (VLGCs), which are purpose-built vessels for the safe and efficient transportation of LPG across international markets.

BW LPG’s operations focus on delivering reliable, cost-effective, and environmentally responsible transport of LPG, which is used for cooking, heating, and as a cleaner fuel alternative in industrial applications.

The company is publicly traded on the Oslo Stock Exchange and is known for its commitment to operational excellence, sustainability, and innovation in the maritime sector.

BW LPG and Scandic Fusion collaboration to build a new data shipyard

Background

Automated data integration and reporting reduce time spent on manual data preparation, allowing employees to focus on analysis and make data-driven decisions. For this reason, BW LPG and Scandic Fusion partnered to implement a new ERP analytics solution as a part of a common LPG DWH/BI platform, built on the Oracle ERP Cloud system, to ensure that various LPG business departments receive timely, reliable and well-structured business data replacing the previous solution, which lacked traceability and did not inspire confidence in data accuracy.

In the previous solution, BW LPG had decent data models, but they were fragmented, each relying on separate logic and filters within the data warehouse, making them difficult to follow and maintain. At the beginning of the project, business users shared that they had avoided creating ad-hoc reports because they were unsure which data model would provide the necessary data.

Construction of new data shipyard

A shipyard is a workshop for building, repairing, and maintaining vessels, and in the same way, a data warehouse serves as the hub for building, maintaining, and refining data as needed. For this case study, we can say we’ve constructed a new ‘data shipyard’- a space designed to handle data with the same care and precision required in shipbuilding/repairing.

The construction of the new data shipyard began with a pre-study of technical and business needs to define its structure and functionality. This pre-study resulted in a specified list of the components for the DWH/BI architecture, as well as a defined data model that includes the first-priority data, as determined by the relevance for business users.

During the construction of the data shipyard, an all-in-one unified ERP data model based on best practices was established to serve the diverse needs of departments such as Finance, Technical and Fleet Management, Procurement, and Management. Based on the unified ERP data model, several interactive, user-friendly, and visually appealing dashboards were created, offering data analysis across multiple departments.

While the work on dashboards creation is still ongoing, the progress so far has been made possible by the strong collaboration between the Scandic Fusion team, the BW LPG IT team, and the key business users who have been, and continue to be, actively involved in the project.

^"To encourage business users—whose workdays are busy and focused on their core responsibilities— to participate in building the data shipyard, it was crucial to earn their trust. This was accomplished by showcasing our expertise in BI and DWH and asking insightful, data-driven questions about their business processes.
_{Liene Ziediņa, Scandic Fusion – Data Analytics Consultant & Project Manager at Scandic Fusion}

Technical architecture

To build a stable foundation, the first step is careful planning. Whether constructing a shipyard or a data solution, the process is similar: we need to determine its location, assess the required capacity to meet all the needs, and identify the right contractors to bring the vision to life.

The technical architecture of the new data shipyard was defined during the pre-study phase by gathering requirements with input from both the BW LPG IT and Scandic Fusion teams.

The components of the project’s technical architecture are shown in the technical architecture diagram:

When defining technical architecture, several factors were considered, including data volume, the need to integrate data not just from Oracle ERP Cloud, but also from other systems, such Power Apps, which is widely used by BW LPG, and the preference for Power BI as the BI tool previously used by the data consumers.

The foundation – data warehouse

With the planning phase completed, the construction of the data warehouse began.

Shipyards rarely have walls and a roof, but for this case study, let’s imagine one with both—this will help us structure the data warehouse and highlight the importance of each step within the project.

We all know that without a strong, stable foundation, achieving lasting results is nearly impossible. No house will stand without a solid foundation. Even the most beautiful renovations - lavish interiors, elegant finishes, and stylish furnishings - will only bring short-lived joy if the structure itself isn’t sound. In the same way a solid foundation supports a shipyard, a unified data warehouse provides the stability and reliability needed for high-performance dashboards. With a unified data warehouse, businesses can ensure that their analytics efforts are built on a stable, dependable base, allowing insights to be both meaningful and sustainable over time.

BW LPG data warehouse relies on Oracle ERP Cloud data as the primary source, as Oracle ERP Cloud integrates seamlessly with most surrounding systems, making it easier to incorporate data from multiple sources. In designing the data model, it was essential to follow a star schema modelling principle. This approach not only optimized query performance but also enabled the data warehouse to be built in a logical, structured way, facilitating the addition of data from connected systems. By adhering to this structure, data accessibility improved, and reporting was streamlined, creating a smoother experience for end users.

Drafting a Fact & Dimension matrix during the pre-study phase and maintaining it as a living document after the data warehouse was built was also an essential step in establishing a strong and lasting foundation. While documentation can be tedious and overwhelming, it is essential in every aspect of the project. The Fact & Dimension matrix is just one part of the overall documentation created, but it plays a crucial role. A well-documented matrix offers a clear overview of available data fields, making it easy to identify which dimensions can be used to analyze each fact table.  When creating new dashboards, having an organized matrix allows analysts to quickly understand the relationships between tables and ensures consistency in data usage across the organization. The picture below provides a glimpse of the Fact & Dimension matrix used in this project. This is just a small excerpt from total data model:

Ongoing discussions internally and with BW LPG about every detail, followed by maintaining consistency in all aspects:

• Agreeing on the Naming Conventions and documenting them so that these conventions are available for everyone. Consistent naming conventions create a common language across the data model, making it easier for users to understand and locate fields. For example, consistent prefixes such as dt_ for date & time fields and d_ for date-only fields prevent confusion. Similarly, distinguishing transactional versus functional currency in amount fields clarifies their purpose. Standardized names simplify collaboration and reduce errors, as team members can easily recognize field types and purposes at a glance. Naming conventions apply not only to fields but also to tables, which are named in a structured and consistent way that indicates the source system. This approach makes it easier to distinguish between dimensions, especially if new systems are introduced in the future.
• Enforcing consistency in binary values (e.g., “Yes”/“No” for fields prefixed with “Is_”) prevents confusion and ensures users interpret data correctly. Without this standardization, mixed formats (e.g., Yes/No, 0/1, Y/N) can lead to misinterpretations and errors in reporting. Uniform values across dimensions create a reliable, user-friendly experience, especially for those unfamiliar with the underlying data structures.
• Ensuring consistent formatting across the data model, such as decimal precision, date formats, and unit indicators, makes data easier to read and reduces the risk of misinterpretation. ‍
• Filling all fields or ensuring they are nullable as intended prevents data gaps and incomplete insights in analysis. Blanks can create inaccuracies or cause errors in aggregations and calculations, reducing the reliability of the data. ‍
• Structuring dimensions and facts according to best practices. For example, adding '-1' rows in dimension tables ensures that foreign keys remain valid, even when data is missing.

Nevertheless, even with most aspects discussed, continuous communication with BW LPG was essential. We held weekly calls and addressed questions as they arose to clarify details. After the data model was developed, business users had a chance to test it - much like walking into a new house, where you need time to feel comfortable, get oriented, and settle in. To help users navigate the data model more easily specific PowerBI report was created, featuring specific tables to facilitate testing and assist in data validation.

^"SCF has been very collaborative in their approach to our partnership, that has helped us deliver our new Data warehouse and ERP reporting dashboards. They have been instrumental in establishing the foundations of a modern analytics platform that we will continue to scale with new use cases. Their proactive engagement style has helped us define new processes and incorporating insights into our workflows, which will continue to drive better business decisions and outcomes.
_{Yngve Jacobsen - Manager - Business Control at BW LPG}

Walls and roof – Power BI semantic model

Once the foundation was established, the construction of the rest of the shipyard could begin. With the foundation level solid, the walls could be straight, just as when facts and dimensions are modeled according to best practices, creating a semantic model in Power BI becomes straightforward and intuitive. Adding well-structured dimensions to the semantic model is like laying bricks one by one, each step bringing you closer to the final result and allowing you to appreciate the outcome.

However, it’s important to keep in mind that each BI tool has its own unique characteristics when creating a data model. BW LPG’s BI solution is Power BI, and just as bricks come in different types and have various properties to consider, each BI tool has its own specific requirements and features to take into account.

Rome wasn’t built in a day, and similarly, creating a detailed semantic model in Power BI took time. While it didn’t take years, a notable amount of time was still required. The BW LPG semantic model consists of around 31 facts and 54 dimensions, and it was essential that each table was formatted consistently. Here are the main points that were considered while building the semantic model in Power BI:

• Consistency in Formatting and naming: Each field in the Power BI semantic model needed renaming to provide business meaning, making it essential to maintain consistent naming conventions on the DWH side. This consistency allowed for a straightforward transfer of names into Power BI. Standardizing formats for date and date-time fields, as well as ensuring consistent decimal precision, was essential. For example, setting a specific format for dates (e.g., "DD-MM-YYYY") ensured that all users interpreted dates correctly, regardless of their location or device settings. Similarly, using no decimals for counts but adding thousand separators helped users interpret values quickly and minimized the risk of misinterpretation. A consistent approach to formatting fostered a cohesive look across dashboards and improved readability for all users.

{{slider}}

• Controlled Relationships: Defining clear relationships between tables was crucial for accurate calculations and filtering. By structuring them purposefully, we avoided incorrect aggregations and data duplication. For example, linking fact and dimension tables ensured the integrity of KPIs and metrics while enabling efficient filtering for smooth data analysis. However, if the star schema is followed in the data warehouse model, relationships in Power BI is one of the last concerns.

Hiding Unnecessary Columns: Hiding columns that are not needed in reports or calculations improves user experience by reducing clutter and minimizing the risk of users selecting or analyzing irrelevant data.

• Organized Structure: Organizing tables, fields, and calculations in logical groups improves the usability of the dataset and ensures that business users can navigate and locate data easily. Creating folder structures, categorizing related fields together, and clearly labeling calculated fields enhance data discoverability and save time for end users who regularly interact with the model. An organized data structure also reinforces consistent data usage, improving the overall quality and reliability of insights generated.
• Up-to-Date Documentation: Maintaining accurate documentation for each field, table, and relationship within the dataset is crucial for future maintenance, onboarding, and troubleshooting. BW LPG documentation is stored in two locations: in dbt and in a metadata extract from the Power BI dataset to Excel (which can be updated at any moment, pulling the latest setup from Power BI). dbt serves as an excellent resource for following the logic of each table and ensuring consistent naming between the data warehouse and dataset. The Power BI metadata extract, on the other hand, is invaluable for tracking field names, formatting, descriptions, and calculations. This extract provides a clear overview for anyone interested in understanding the model’s structure.
• RLS/OLS – Just as a roof is essential for feeling warm and secure when the weather outside is harsh, Row-Level Security (RLS) and Object-Level Security (OLS) are crucial for ensuring data safety in a model. A sturdy roof relies on straight walls, just as a well-built dataset allows RLS to be applied without extensive adjustments, adapting smoothly to different scenarios. For BW LPG, implementing RLS wasn’t entirely straightforward due to mixed requirements - users needed to view specific departments and associated costs. However, since the data model was unified and constructed according to best practices, RLS could still be applied effectively to meet these needs.

Interior – dashboards

With the foundational data model and structure in place, we could move to the more creative phase - furnishing the ‘house’ by designing the dashboards. Just like choosing colors, flooring, and furniture for a home can be overwhelming, selecting the right design elements for a dashboard requires careful planning. Before diving in into dashboarding itself, it’s essential to establish a color palette, fonts, and other design details. BW LPG provided their reporting guidelines, which allowed us to create JSON files that standardized these elements across all dashboards. A well-constructed data model also minimized the need for adjustments during dashboard creation. Additionally, all dashboards follow a common structure, with a ‘Welcome Page’ that includes a brief introduction to each section and an ‘Info Page’ where helpful tips are provided.

However, even with these guidelines and a solid data model in place, it was essential to understand the true purpose of each ‘room’ - or in this case, each dashboard. Requirement template was created which facilitated business users to analyze and foresee new dashboard needs. We followed a structured dashboard development process to ensure both functionality and alignment with user needs. The steps we took were as follows:

1. Analyzing the existing reports
2. Gathering and discussing requirements
3. Adjusting the dataset for dashboard needs
4. Preparing a mockup for dashboard structure
5. Creating the first version of the dashboard
6. Testing the dashboard and data
7. Discussing feedback
8. Implementing feedback
9. Conducting user training

For more details on creating the perfect dashboard, read this blog post that dives deeper into best practices and strategies.

By implementing a structured data model for BW LPG, we successfully optimized the dashboards to deliver greater insights and details:

• Consolidated Dashboard View: Previously, different business users required multiple pages to analyze data from various perspectives. Now, with a dynamic, single-page dashboard, users can view data from multiple angles within the same page, customize their view, and save it via bookmarks—simplifying navigation and enhancing user experience.

• Enhanced Drill-Through Capabilities: Users can now drill through data more effectively, providing a deeper, contextual understanding of metrics with minimal effort.
• Single Source of Truth: All dashboards are built on a unified dataset, ensuring consistent information across reports.
• Future Considerations: This model also positions BW LPG to leverage advanced AI features, such as Copilot, to further enhance data insights and analytical capabilities as needed.

Of course, the shipyard isn’t completely finished—further refinement may be needed, perhaps even expansion or adding extras like a “sauna”. Still, the data warehouse is now stable, fast, and reliable, allowing us to enjoy the hard work that has gone into building it.

^"We began our journey with Scandic Fusion in 2023, and despite the challenges posed by time zone differences, the collaboration has been highly enjoyable. A key factor in this success is the Scandic Fusion project team's expertise in the ERP domain. Their ability to communicate confidently with our business users right from the start helped put everyone at ease. As a result, they were able to design an effective data model that meets our needs. We are already pleased with the initial dashboards delivered and are truly excited about what the future holds with Scandic Fusion.
_{Chin Hwee Wong - Manager, Data and Analytics at BW LPG}

‍

‍

‍

‍

‍

‍