Recently I talked to many companies about this topic: why a data warehouse is essential to support company decisions, in all departments. I decided to summarise my points in this article, for future reference.
The need for centralised information
All company departments use some applications to run their everyday operation, from the accountancy software to the web applications used by HR, but also IT, marketing, sales, logistics, and so on. All these applications are responsible for generating, maintaining and reporting a segment of company data.
Such information differs by several aspects:
- Users, therefore its goals;
- Where the data is stored: on servers that are under the company control, or on remote services;
- How the data can be accessed: graphical interfaces, SQL, APIs, and more;
- The logic used to organise and present data.
No one has access to all this information. But even having the necessary accesses, it would be impossible or extremely inpractical for a single person to deal with the different formats, the information organisation, and so on. Not to mention that the data contained in different sources can be conflicting, partially inaccurate or malformed, or incomplete.
Even relatively simple statistics might be made hard or impossible becayse of this data fragmentation and polyglotism. For example:
- How many customers call support, average and maximum by day of the week;
- Number of products sold this year by month and brand.
This information can be regularly reported with dashboards that are updated once a day. Anyone who knows what to do with such information will be able to look at a fancy graph and understand it. But these can also be one-off questions. Nowadays most data analysts and business analysts know SQL. They are able to connect to the data warehouse with a graphical interface, write an SQL query, and get the data they need. This is extremely important for data warehouses, as it allows analysts to enjoy the flexibility they need.
More complex analyses that span over different functions and processes of the company are generally impossible without a data warehouse. For example, finding out how recent recruitment affects the cost of providing a service and customer satisfaction, data coming from a single application or database won’t be enough. Instead, one needs access to information generated by multiple processes and multiple departments.
Data warehouse to the rescue
A data warehouse is the place where all the business information converges, in a form that is suitable for data analytics.
From a technical perspective, it is just a relational database that can be queried by humans or applications using the SQL language. It is often used by specialised programs to generate dashboards and graphs that present key business information in a friendly way. Reports of any types and alerts can also be generated from a data warehouse. But data analysts and marketing people can connect to the database and run SQL queries directly. This allows them to perform any custom analysis they might need by joining, aggregating, filtering and ordering data in any way.
Information is organised in a data warehouse in standard ways: a so-called star schema is normally used. This has several important benefits from a technical perspective, but even more importantly, star schemas are well understood by data analysts. They will not have to spend time studying how data is organised or how to extract a certain information: once they know which data is contained in the data warehouse and the name of such data, they’ll immediately know how to write their SQL queries. Think of a star schema as a language: when you talk about a new, specific topic you might have to learn new words, but if you know the grammar, you know how to express the concepts you need to communicate.
The database technology used to build a data warehouse should not be the same used for operational databases. It will work for simple cases, but when data grows and analyses become more complex, database performance will be insufficient. Specialised professionals are also required to build and maintain a data warehouse, and the data pipelines that feed it with the data generated by every department. But these topics will be discussed in future articles.
Benefits of a data warehouse: bullet points time!
Now, let’s summarise the benefits of data warehousing with a series of bullet points.
- A data warehouse is a single version of the truth, meaning that it stores complete, consistent, non-redundant information about a business.
- A data warehouse can scale up to the maximum level of data aggregation, and/or down to a very high level of details.
- Data and business analysts can query a data warehouse relying on a consistent schema style, so they don’t have to learn intricate design details.
- Data warehouses make it easier to produce dashboards and reports with the same user interface, consistent to each other.
- Data warehouses run on dedicated technology that scales very well for their use cases.
- Having all business information in one place, running on a single technology, makes data governance easier and less expensive.
- It is always possible to add new information to a data warehouse, and even add more data sources.
- While a company data warehouse should aim to be a single version of the truth, it’s entirely possible to create separate data lakes. They can feed the data warehouse, or be independent from it.
Conclusions
Building a data warehouse unlocks all type of analysis of a business, at any level of details and complexity. But building it is not an easy task, because it requires big business and technical efforts. Highly skilled personnel is also needed. And mistakes made in the early stages can bring problems that will affect you for the whole lifetime of a data warehouse, limiting its efficiency and usefulness.
But you can call us. Use our expertise to start this adventure with the right foot. Together, we’ll join forces to build a sound, efficient, scalable data warehouse, from both a business and technical perspective. Contact us to discuss what exactly we can do for you. Want to learn more technical details? Attend my webinar on data warehousing on June 5!
Federico Razzoli
0 Comments