Problem solve Get help with specific problems with your technologies, process and projects.

For SMBs, the data expedition starts with BI integration

The first step in any business intelligence (BI) implementation, according to consultant Don Jones is BI integration -- getting both BI and business systems in sync.

The whole point of a business intelligence (BI) system is to access your existing organizational data: sales data, payroll, shipping costs, customer records -- the more the merrier. By getting at this data and tying it together, the system lets you see connections, explore trends and patterns and make decisions that would otherwise be pretty difficult.

But for that to happen, your BI system needs to be able to see all of that data, and that connectivity doesn’t happen magically. It begins with BI integration.

In large companies, implementing a BI system can often take months -- and they’ve been known to go on in phases for years. A huge part of that time is spent connecting to external data. In many cases, existing data is extracted and transformed into a BI-friendly format and loaded into a data warehouse (the process often goes by the acronym ETL), where the BI system can use it. In some cases, live business data may also be included in various scorecards, reports and dashboards.

Small and medium-sized businesses (SMBs) may also use data warehouses, but they’re just as likely to rely on in-memory analysis servers that directly access live data. In the case of an SMB, you’re a lot less likely to want to spend months and years putting a BI system together, but you’re also likely to be using more standardized systems and data stores.

For example, it’s not at all unusual for an SMB to keep a great deal of data in simple Excel spreadsheets, accounting data in an off-the-shelf package like QuickBooks or Microsoft Dynamics and customer data in a cloud system like Even if you’re using a custom-built application, the data is likely stored in an easily accessible, standardized repository like Microsoft SQL Server.

The BI systems marketed to SMBs usually include built-in connectors to reach out and grab all these different kinds of data. They’ll do so through published application programming interfaces (APIs), through standardized database connectors such as Open Database Connectivity and Object Linking and Embedding, Database and through import mechanisms, especially for file-based data like Excel spreadsheets or Access databases.

You’ll still have to explain that data to the BI system and help it understand the connections. For example, if the “EmpID” field in one of your business applications corresponds with the “User” field in another, the BI system needs to recognize employee activity in both places. In some cases, BI vendors will provide implementation services that help you get those connections in place faster.

The real decision point comes when you’re researching and evaluating BI systems for your organization. Start by thinking about all of the places where you have important business data. Don’t forget about those spreadsheets or the Access database that Joe cooked up five years ago and that’s still used to run a portion of the business.

Also think about which systems don’t need to be connected. For example, while it can be useful to have payroll information reflected in your BI system -- since that helps you understand where some of your costs lie -- including a database of employee benefit information probably won’t offer a lot of advantages in terms of business analysis. Once you’ve got a good inventory of your data and where it lives, you can start shopping for BI systems, making sure that the ones you’re considering will support all of the data sources you need to connect to it.

You need to know how the BI system will make that connection, too. For example, in a traditional ETL environment, data is basically copied from the business system into a data warehouse. That creates a one-time performance hit on the business data service, although it’s usually scheduled for unattended, off-hours periods, when it won’t affect users. BI systems that access live business information, on the other hand, can impose a significant performance load on your production systems. Make sure your systems are capable of supporting that extra load. If not, the first time you drill into a dashboard you could drag the whole company to a grinding halt.

A little understanding of BI integration goes a long way. With it, you can make smarter decisions in evaluating, purchasing and finally implementing a system that meets your business needs. 

Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 books on IT and a speaker at technical conferences worldwide. He can be reached through his website at

Dig Deeper on SQL Server Business Intelligence Strategies

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.