The importance of a Data warehouse in a Business Intelligence Solution

Posted on by Paul Kukk

I consider the data warehouse as the foundation of a business intelligence solution – your single source of truth. As with any good structure if the foundation is not good the structure will come crumbling down. Before we go any further about the importance of a data warehouse in a bi solution lets define it. A data warehouse is a large repository that contains data that is collected from a wide range of sources. These sources of data are usually the business’s transactional systems. The data warehouse is used to guide decision making at all levels and is optimized from read access. Taking the time here to build your warehouse by defining your schemas and tables  is crucial to the solution and the business. You do not want to rush through this part by any means. By not designing the data warehouse correctly the project’s cost can increase significantly, cause delays as well as potentially incorrect reporting of the data that is being stored in the warehouse incorrectly. Take your time here to ask the right questions, develop the right solution, review the solution with your team, and then implement and test. You will need to make sure the data warehouse is not only designed right but can also grow and scale as your business’s data needs grow. One thing you do not want to happen is in six months to a year  you are getting reports that the system is either slow and/or unresponsive. If one of my clients were asking me about this situation, my first thoughts would be around did the team calculate the right size warehouse or server, did they miss something within the database structure (wrong join, wrong columns were index, etc.). I can not say this enough, the data warehouse is the foundation of the business intelligence solution and it deserves the time to be designed, built, and tested correctly. Like I stated earlier if the foundation is bad then your business intelligence solution will have many issues, if you do not do your due diligence around the building of the data warehouse your solution will have a lot of issues in the future.

Why you need a Data warehouse

Posted on by Paul Kukk

Let’s start off by defining Data Warehouse. A data warehouse is a central repository of information that can be analyzed to make better-informed decisions. It can also be further defined as a repository that stores large amounts of data that has been collected and integrated from multiple sources – such as a CRM, payroll or accounting software, or inventory and sales systems.

“Why do we need  a data warehouse that is separate from our business transactional systems?”  This is a question that we get asked frequently at CopperHill Consulting. We answer this with several statements:

  • Business transactional systems are built for tasks and very specific work flows.
  • Business transactional systems allows editing, while a data warehouse is read-only.
  • Business transactional systems should only hold current data. A data warehouse can hold historical and current data.

 

If you want to move forward with your BI strategy, you need a data warehouse.

The data warehouse is a core component of Business Intelligence. Here’s how a data warehouse makes an impact:

  • Maintains a copy of data from your transactional systems. This allows you to keep your transactional systems lean and processing quickly with only the most recent and relevant data visible. It also lets you keep a history of all past transactions for recordkeeping and analysis.
  • Improves the quality of the data. Identify duplicate entries and records. Find anomalies in your data. Build custom views. These are all ways that a data warehouse can help improve the quality of your data both in your transactional systems and in your reporting.
  • Restructures information for different users. Create different user roles to restrict permissions and set different views to make it easier for users to understand the story their data is telling them.
  • Integrates data from multiple transactional systems. This lets you see a bigger and clearer picture of your business across all departments and silos.
  • Delivers excellent query performance without compromising business transactional systems. No need to worry about your systems slowing down, timing out, or crashing.

Your data warehouse will change and evolve as your business gets larger and greater over time. As your company grows, your requirements shift.  Your data warehouse needs to be designed to be flexible and scalable so it can handle changing requirements. Automated integration solutions to move company data from your business transactional systems and flat files to the data warehouse is one way you can make sure your data warehouse can grow with your company. Automations keep costs low as well as lower the chance of errors.