A data warehouse is a centralized repository where information from the entire company is stored, and this information is stored in a format that allows for easy and understandable reporting in tools such as Excel or other tools, such as Power BI.
At a certain stage in the life cycle of every company, you will realize that you have too many business systems and too many information requirements to be able to efficiently (meaning correctly and quickly) obtain and transform this information into understandable reports. This stage occurs at different times for every company.
It depends on the nature of the business, the number of employees, the organizational structure, the requirements for data accuracy, the quality of the analytical/controlling department, and so on. Often, you also need to integrate business systems because one system has certain information while another system has a different piece of the puzzle.
It cannot be generalized entirely, but I would say the right time for a change (or at least consultation with an expert) occurs when you subjectively feel that you can no longer handle the volume and structure of information in the company.
I know many medium and large companies that rely solely on Excel and have perfectly adequate sophisticated reporting. However, even this approach requires quality people who create the concept… Even an Excel reporting concept can be done well or poorly. Certainly, a smaller company can be managed in extreme cases even without information or only with fragmented, irregular information because the owner has important facts in mind. However, it is difficult for medium and large companies to operate in this way and not for long 🙂
Phases of the Data Warehouse Project:
- Selection of a processing company
- Processing project documentation along with project pillars and time requirements + contractual documentation – important part
- Work on the project, ongoing monitoring, acceptance/complaints of individual parts. Gradual release of funds
Contacting and Selecting a Consulting Company
Introduction: If you decide that you want to build a data warehouse in your business environment, you will probably contact a specialized company. They will send a consultant to assess the scope of work required and provide you with a preliminary offer.
Solid companies that have already built many warehouses can estimate the time required fairly well based on structured discussions with you.
It is very important that you are prepared for this interview and know what you actually expect – what is the goal of the project (e.g., what financial reports should be created/access to data for analysts, etc.).
It is quite common for the client not to really know what they want. Or rather, they know they want a data warehouse, but they don’t know what functions they expect from it and what problems the data warehouse should solve. What information do you actually want to report in the end? In such cases, be prepared to have the consultant guide you. It indicates that you may not be able to manage the project – provide a quality briefing, monitor its progress, and results.
An unreliable processing company will quickly sense this. They can do whatever they want with you… promise, talk sweetly, deliver slowly and low quality… you will pay a lot – with money, your time, and your nerves. Often, the project does not reach completion.
What Is Important in Selecting a Consulting Company
- Choose a company with references, ideally one that has completed a similar project in a company with a similar business model.
- Have an in-house expert in the field. Not a strict IT specialist who installs printers but a business intelligence professional, a financial controller with database experience, etc. Alternatively, hire project supervision. When you build a house, you also hire someone like that.
- Ask in advance for the hourly rate and take this fact into consideration when selecting a company. Hourly rates (beware of lower or higher rates):
- Company hourly rates range from 500 to 1000 USD/day (here in Czech republic)
- Freelancer hourly rates 250 – 600 USD/MD
- Do not make payments until the project documentation and price are approved. A reputable company will come to the first meeting for free.
Processing Project Documentation and Data Warehouse Price
Project documentation is a very important document that gives the project structure. Precisely prepared documentation is also a support in potential disputes over work and the like. It should be part of the contractual documentation. This is especially true for larger projects.
Many people think that documentation is not worth it because various situations arise during the project that are dealt with on the fly. To some extent, this is true, but even for these situations, you can think about them in the document and somehow define them and set rules on how to handle them. This will prevent situations where the work does not meet expectations, and the contractor says, “Well, you didn’t tell us that” or “What do you want for this money?”
Creating documentation is a time-consuming task that requires cooperation on both the client’s and the contractor’s side. Ideally, it is good to involve a third party, someone independent. Project documentation should be divided into project pillars, which are large enough (small).
For larger projects, it is standard for payments for work to be released upon acceptance of a certain pillar. This means that there should be a sufficient number of pillars, and they should be as independent of each other as possible.
Each pillar should be further broken down into individual activities, and these should be assessed in terms of hours/days required for completion. The contractor should also declare in the documentation what they need from you to successfully complete a task. The document may also include customer needs analysis, feasibility analysis, etc.
You can then refer to the project documentation in the contractual documentation, where you can address:
- Billing for individual pillars/activities
- What to do if the budget for a particular pillar is not met
- How to complain about delivery, and does the contractor have the right to additional compensation?
- Termination of the contract
- Others
Data Warehouse and Project Documentation Pillars
The pillars of a medium-sized project could look like this. Each pillar should ideally be broken down into smaller tasks like these. Also you can ask chat gpt for breaking down your dwh project.
Project Pillars for a Data Warehouse Project – Example
- Customer Needs Analysis (can be contractually and invoicing treated separately) (10 MD)
- Investment and Setup of SQL Server (6,000 USD + 3.5 MD) Note: On-premises architecture, with the use of cloud services like Snowflake, eliminates the need for investment, and instead operates on a “pay as you go” model
- License Purchase (6,000 USD)
- Installation and Configuration of VS (0.5 MD)
- Installation and Configuration of SQL Server (report server, emails, job agent, security, testing…) (2 MD)
- Installation of SW 1…n (1 MD)
- Architecture Design of the Data Warehouse (24 MD)
- Incremental Area – 50 tables from 3 different source systems (3 MD)
- Stage Area – 50 tables from 3 different source systems (3 MD)
- Presentation Layer (final data storage) – creation of snowflake schemas from tables from the previous step – in the end, 23 tables in 3 schemas are created (15 MD)
- Logging – logging framework (2 MD)
- ETL Area – database for ETL objects (1 MD)
- Design and Implementation of ETL Processes + Testing (60 MD) – Note: With the use of democratizing tools, much much less
- Source System 1…n to Increment Area – 10x SSIS packages that process data from source systems + metadata creation (10 MD)
- Source System 1…n to Stage Area – 50x SSIS packages that historize data from the increment area (10 MD)
- Data Transformation to the Presentation Layer – creation of procedures and SSIS packages that transform and store data in the Presentation Layer (20 MD)
- Testing (functionality, performance tests) (20 MD)
- Design and Implementation of Reporting (16 MD)
- Reporting Services Report – 5 reports (10 MD)
- Analysis Services Report 2 OLAP cubes (3 MD)
- Excel, Power BI Report 2 Power BI reports (3 MD)
- Employee Training (10 MD)
Note: We can see that according to the design, Pillar 4 is significantly disproportionate to the other pillars, and it might be appropriate to divide it further to diversify the risk and improve ongoing control, so that this part is not too much of a black box.
Update (2024-02-08) – This article was written several years ago. A significant part of the project’s cost lies in the older, conservative approach to building data warehouses, particularly the ETL (extract, transfer, load) process. A newer approach is based on ELT (extract, load, transfer) – meaning data is first loaded and then transformed. This approach is founded on the new trend of data democratization and a data-driven approach to management. Thanks to this global trend in the market, a new segment of tools has emerged that support this concept, capable of not only significantly reducing ETL costs by tens of percent, but also enabling less technical colleagues to control the entire solution (you don’t have to be a techie). Such tools include Keboola (ETL/ELT), Fivetran (ELT), dbt (transformation), and others.
Total Price for the Data Warehouse – Example
When we add up all the planned tasks from the previous chapter, we get an expected budget that we either accept or perhaps trim/change the requirements. In any case, setting expectations in this way is good and prevents surprises.
- TOTAL 123.5 MD
- MD Rate 320 USD
- Total Work 39,520 USD
- Investment in SQL Server 6000 USD
- TOTAL excluding VAT 45,520 USD
Note: The above figures represent only investment costs. You will need to calculate your internal project management costs and potentially additional costs for employees internally. Someone will need to take care of the data warehouse in the future, handle changes in reports, new systems, etc.
It is good for a new employee to be involved in the project from the beginning and participate in it. If you don’t want to hire a new employee, you need to adjust the structure and complexity of the project so that the data warehouse requires as little maintenance and future overhead costs as possible. In such a case, you can outsource warehouse maintenance for a reasonable price.
Conclusion
This is how a budget for a well-designed data warehouse for a medium-sized company that integrates data from 3 different business systems could look. The data warehouse automatically (daily) pulls data from source systems and stores the results.
The contractor also produced about a dozen quality reports to support decision-making, which are regularly sent by email to managers. Data analysts can connect to OLAP cubes via Excel, which contain all the important data in the data warehouse that underlies the reports.
Managers can access key reports from their mobile phones using Power BI. The contractor provided complete training for all employees who will come into contact with the data warehouse in the future. Manual report processing, which took a long time, has been eliminated. Everything is replaced by new reporting.
Of course, what it looks like on paper and what it looks like in reality are different. Ongoing deliveries need to be carefully monitored, and the project needs to be actively managed to ensure that the contractor does not fall asleep on laurels!!!
