Custom Software Apps & SharePoint Consulting

There are many different technical frameworks available for implementing a business intelligence solution, all offered by a variety of vendors. In a general sense though, there are three overarching pieces to business intelligence:

  • Integration and the data warehouse
  • Data cubes and analytics
  • Dashboards, KPI’s and reports

Understanding these pieces and how they work in tandem to address organizational needs is critical in software consulting to achieve a successful implementation. I will be covering one each week for the next few weeks, starting with integration and the data warehouse.

Where is Your Information?

One of the most basic goals of any business intelligence solution is to consolidate information from a variety of different systems in one place. This one place is known as the data warehouse. The data warehouse (or data mart, in some solution frameworks) is a common database that houses information from those separate business systems. It is important to note the data warehouse does not replace the proprietary data storage mediums used by a company’s business software; instead, an integration process reads data from those systems and copies it to the data warehouse.

The design of the data warehouse is fundamentally different from the way most business software stores its information. The database powering a payroll system, for example, was written specifically for that payroll software. It may relate a table of employees to a table of projects to a table of time entries and so on. A data warehouse is designed to incorporate the data structures of all of the systems in an organization and consolidate them into dimensions and facts.

  •  A dimension describes properties of a data point. Common dimensions include calendar days, clients, employees and locations.
  • A fact describes measurable data. Facts may include sales, units of output and work done.

Each fact can be associated with multiple dimensions. A sale may have a sale price (the fact) and be associated with a time, date, salesperson and client (the dimensions). When consolidating data from multiple systems, there is often an overlap between dimensions, and so they are consolidated into one dimension table to be used throughout the warehouse. The benefits of re-organizing data this way will become apparent when we cover data cubes next week.

Different software systems across an organization store their information in a variety of different formats, numerical precisions and units, so this data must be transformed before being copied to the data warehouse. The process of reading the data from each system, transforming it and placing it into the warehouse is called an Extract, Transform and Load process, or ETL.

  • The Extract step copies the raw data from each system into a duplicate table. It is important to copy data out of the native system in order to provide a single snapshot of the data at a particular point in time and to minimize the impact  the ETL has on the native system’s performance.
  • The Transform step uses a variety of different data transformation techniques to organize data and prepare it for the data warehouse.
  • The Load step moves the transformed data into the data warehouse.

Once the data warehouse has been populated with information, it can then be further processed and analyzed. Check back next week for part 2!

Find out more about getting the most out of SharePoint Business Intelligence for your business…


Nate Richards
Nate has over 18 years of software engineering and consulting experience. He founded Entrance in 2003. Nate is the past President of the Board of LifeHouse Houston, a Christ-centered maternity home ministry, and is past Executive committee member and Treasurer of Houston Achievement Place, a foster care and social skills training non-profit organization.
Nate Richards on Linkedin
Carol Entrance Software Consulting making software better