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.
Now, I’d like to cover data cubes and analytics.
Mining Your Data For Awesome Business Intelligence
The tables inside a consolidated data warehouse can be queried and reported against, but effective business intelligence solutions developed by software consultants generally do further processing of the data warehouse to enable vastly superior reporting, forecasting and analysis. This is called online analytical processing or OLAP.
The advantages of OLAP are numerous and all of those advantages begin with the data cube. A data cube is a structure that points to the information stored in the data warehouse and pre-computes sums, averages, counts and other aggregations on a regular basis. A data cube also defines common calculations and filters to provide a monumental speed boost to reports and dashboards.
One business intelligence solution may have several data cubes defined: A giant cube that encompasses the entire data warehouse and is processed daily along with a sales cube that is processed continuously, for example. Each cube may be accessible by certain divisions or individuals in an organization. Defining separate cubes allows business intelligence architects to achieve a balance between security, speed and flexibility in reporting.
OLAP also enables organizations to turn their data into knowledge through data mining. Data mining is the automated process of finding trends and anomalies in well-structured data. Using cutting-edge algorithms and processing power, an analytics engine can sift through the structures defined in a data cube and provide organizations with valuable insight they may have otherwise missed. Rather than depending on pre-defined dimensions and measures to build reports, organizations can also define data mining structures to do forecasting and grouping of information.
Dashboards, KPIs, and Reports: Why do they matter for business intelligence?
The true value of a data warehouse and related OLAP (online analytical processing) structure is finally realized when they are surfaced to the user through dashboards, key performance indicators and reports. Each of these provides specific benefits:
- In the context of a business intelligence solution, a report is a relatively static, non-interactive sampling of data. Reports are analogous to the traditional paper reports used throughout an organization. A modern business intelligence solution will generally include scheduled reports delivered via e-mail or to a centralized location to give stakeholders a daily, weekly or monthly rollup of important metrics.
- A dashboard is a highly interactive view of an organization’s data assets. Modern dashboards provide power and flexibility to end users in a variety of different ways:
– Interactive rollup and aggregation on-the-fly
– Security trimming based on group membership
– Drill-through to detailed information and views
– Multiple dataset visualizations e.g. spatial data on a map as well as a chart - Key Performance Indicators (KPIs) are components designed to convey at-a-glance performance relative to a goal. KPIs are defined at the OLAP level to provide a central definition of organizational goals, rather than being set at the report level. KPIs can be used and re-used across reports and dashboards. KPIs can be used in tandem with visual elements like arrows and “stoplights” to provide users with quick and authoritative information.
Read our case study to find out how we leveraged business intelligence to reduce the amount of time needed to access data and create reports.
Find out more about refining your business intelligence solution by asking the right questions…