Business Intelligence within the CUBE

Most businesses gather large amounts of data without even realizing it. This could be everything from time cards to product orders, as well as sales, contracts, basic costs and the normal accounting that takes place in every industry.

However with a little extra effort, this data can be used to provide much more detailed and insightful information that can help inform larger strategic business decisions and provide advanced warning of upcoming problems.

Amadeus Consulting is a custom software development company that features a specialty in building complex data collection and management solutions that help businesses manage their data simply and efficiently. This article focuses on the process of designing and building a business intelligence system, and leverages a system called "the Cube" that Amadeus Consulting built to synchronize its own internal processes and disparate data systems. For those more familiar with data storage, you will recognize this as a relatively uncreative name for an OLAP cube data structure that we developed for managing and analyzing internal data from multiple sources and perspectives.

How do you begin?

• Are you already gathering data?

• Is there something you would like to know that the data is having trouble telling?

First, if you are not gathering the basic information then it is important to establish a basic operational system as a first step. In some cases this may include CRM systems, project management systems, sales management, etc. This can be complex databases, or even as simple as Excel sheets.

The second part is determining what you want to know and want to be able to learn from your data, and what actions you would like your data to help inform. This could be increasing internal efficiency, improving customer satisfaction, or increasing production quality as examples.

Example: Where the Amadeus Consulting Cube Started

The Cube is our own internal system for synchronizing data. Before developing the Cube we managed data through multiple sources that – at least on the surface – were difficult to compile data into one place. With a unified data system, project management could be even more efficient and reduce the number of unexpected problems or challenges. Designing the system meant understanding how we currently collected information including data from time management systems, task management systems, sales tracking and multiple additional points of data.

Our goal was to provide near real-time visibility into task progress, employee resource allocation, and related management information that would allow us to better serve clients and more efficiently manage projects.

Implementation

The implementation of business intelligence systems really comes down to the use of relational databases. Relational databases help show how one system is related to another system, and how objects (data) in one system are related to objects in another system. In its most basic form, most software systems we use are just beefed up databases that store and organize data. By tapping into those existing databases we can find information that was not otherwise easily available.

A business intelligence system is really about building relationships between existing systems and databases and presenting that information in a manageable format. As a simple example, you may have one system tracking how much time has been allocated for a specific task, and another system tracking the progress of that task. However by bringing those two sources of data together (along with many other points of data) you can more completely understand the overall progress of a task or project.

The challenge is in a process called ETL: Extract, Transform and Load. This is how a business intelligence system gathers information from an existing system, stores it in a common database, and then presents it to the end user.

Often people get caught up focusing on how the data is stored, when the focus should be on defining the data that you gather. This means defining things like "what is a project" and which metrics are a part of that definition. A "project" as a piece of data will be defined by many common elements which might include allocated time, time used, budget, budget used, assigned employees and resources, deadline, etc. Each of these elements may be pulled from disparate systems but all go into a single definition of "project."

Example: How we built the Cube

Step one: The Hardest Part

The hardest part of any business intelligence system is figuring out what you want and defining each element of that "thing." For us it involved defining "what is a project?" and "what metrics are used to define a project." For the definition of a project, this may involve identifying metrics related to budget, time and resources, as well as how those resources are being consumed in real time.

Step two: Building the system

This isn’t a technical case study, but in short we needed to build a system that could that extract, transform and load the required data into a single output system. This is really our sweet spot in terms of technical capability, so it is complex, but not overly difficult. In fact, once the design was determined and the definitions planned out, it was really built by a single developer over the course of a few weekends. Since then we’ve been modifying and improving it as our needs evolve.

The system we built pulled data from Salesforce, an employee time tracking system, and a system called FogBugz that we use for managing the software development cycle, as well as a few other metrics from various locations. These systems are all fairly common types of setups that we see in companies our size, and most data driven companies use fairly comparable systems in one way or another.

In order to provide near real-time updates (the information is refreshed every 15 minutes) we use SSAS (SQL Server Analysis Services) to run the Cube and SSIS (SQL Server Integration Services) to pull the data from the other databases.

Step 3: Simplifying the output and tools

Users do not need to understand the whole data set in order to understand the most relevant part. This may mean creating an executive dashboard or warning lights that can indicate problems with an important process. You do not need to know all the details if you have a simple display that shows a task’s progress. However if there is a red warning light of sorts, it still allows you to dig into the data and quickly identify obstacles before they become larger problems.

We have built many types of business intelligence systems for many types of companies and organizations. It is exciting to work with executives to help them extract meaningful and actionable data from their existing systems and it is equally exciting to have our own internal tools to help us serve our clients even better.

Would a business intelligence system benefit your company? Would you feel more comfortable with your strategic direction if your company had a business intelligence system? Feel free to comment below with your ideas.