Building a Data Warehouse: 7 Crucial Steps

Having spent over 35 years working with customers on their data warehouses, there are few mistakes that I haven’t seen made during their construction. The reality in fact, is that many organisations underestimate the complexities of building a data warehouse – or at least, building the RIGHT data warehouse for their business needs.

A data warehouse is not just a read only copy of your live databases. It should be a purpose built and carefully designed repository that links data from your many operational system databases to enable your to report on all aspects of your business, end to end - what they call a 360 degree view!

A data warehouse is often your Singe Source of Truth providing a reliable and trustworthy central data repository for reporting and analytics.

Here are seven crucial aspects to building a data warehouse that will help you avoid the many pitfalls along the way of its construction.

Steps 1 and 2

Step 1: Set a BI strategy

Before you even press the start button on any build project, you need to be sure what business information will be required from your data warehouse, now and in the future. This will form the basis of your BI strategy. If you don’t have a BI Strategy in place, you may end up investing a lot of time and money on the biggest white elephant your business has ever created.

PTR is a Microsoft Solutions Partner for AI & Azure Data offering a comprehensive data analytics consultancy service which includes BI Data Strategy assistance.

Step 2: Engage the end user

Once a BI strategy is in place, ensure it really achieves what users expect from it. Often, users have a high-level idea of what they require but don’t fully understand all its implications. The only way to avoid extra, unnecessary development costs is to get to the bottom of user expectations at the start of the process. And this involves patience, understanding and effective communication. And, once in place, ensuring all users are properly trained in using the data warehouse will help in its being quickly adopted throughout the organisation.

It is worth considering holding stake holder workshops with business units across your business to ascertain what their ultimate reporting and analytics requirements are. If you don't put data in, you can't get information ouit, so make sure before you start that you can identify what data needs to go in to your data warehouse.

Step 3: Design a suitable star based schema

With user expectations and desires to hand, and an understanding of the source datasets to be brought in you can start on your exciting journey of designing your data warehouse. There are many approaches to managing the design process including constructing bus matrices for your core business processes, identifying fact tables and the business grain they represent, identifying the conformed dimension tables that will act as the glue between your many fact tables, identifying the business relationships between your facts and dimensions.

The most common design methodology for a data warehouse is the Kimball approach. Many designs are hybrids of several design approaches, but we largely follow the Kimball design approach. We have a wealth of experience in offering data warehouse design consultancy & mentoring, and data warehouse design training. So if you need a guiding hand to get you underway with your data warehouse design we have a team of data enthusiasts which can help you.

Step 4: Ensure high quality data

Your data warehouse performance will only be as good as the data it receives and stores. In today’s business technology environment, it’s highly likely that it receives data from several sources – which leaves the data wide open to errors. Inconsistent data, duplicates, logic conflicts and missing data all create real challenges in ensuring the smooth running of any data warehouse and will affect the reliability of any resulting analytics.

Data cleansing and preparation is huge undertaking and accounts for a large part of project resource time for data warehouse projects. There are many different approaches to data cleansing and preparation. A popular methodology is the Medallion Architecture method.

Step 5: Regularly test your data

Once you have ensured the quality of your incoming data, the priority then turns to testing it on a continuing basis to ensure its continuing accuracy. This can be done by developing an effective Software Testing Life Cycle (STLC) and is the only way of ensuring your data remains fit for purpose.

Step 6: Optimise performance

Even with the best of foundations, most warehouses will need “tweaking” to optimise its performance. Think of it as finely tuning the engine of a performance car – there’s usually a refinement to be made here or there to get the result you want more efficiently.

Step 7: Avoid spiralling costs

Relying on your in-house team to build a new data warehouse might seem a good idea at the time. In fact, many organisations have lived to regret that decision as they see development costs spiral, sorting out and reshaping projects as they go. As you can see from the few points above, there are many issues to consider – and, as with all things, you don’t know what you don’t know. Calling in the experts at the beginning of your project is a wise investment, saving you both time and money, and ensuring you get the data warehouse your organisation needs.