Originally published on January 8th, 2018.

Establishing a strong Extract-Transfer-Load (ETL) process for Business Intelligence (BI) can be extremely powerful - if it gets done correctly. The key to getting it right? Having a complete strategy that combines historical context with forward-looking predictive analytics.

Here, we break down exactly how to develop an ETL for BI strategy and how to use it to strengthen your business.

Table of Contents

What is BI and How It Can Help Your Business?

In the big picture, BI (Business Intelligence) is essentially a comprehensive analytics solution that you can use to better understand your company and the state of the market that you’re growing in. The operative word here is "comprehensive": for your ETL for BI solution to really work, you have to start from the ground up and ensure that you don’t leave out any key components.

How to Create Your ETL for Business Intelligence Strategy

There’s a lot to consider when it comes to creating and implementing a new ETL for BI strategy. Here are the 4 main components that must be addressed:

1) Your Business Intelligence Roadmap

At its core, BI is all about analytics and data, which means that you need to have both of those things if you want a strong overall strategy. Specifically, you need to understand and organize the following:

  • Reporting and Analytics: What are the main analytics that you want to keep track of? What metrics are most important for you to improve your strategies? Where is that information coming from? Determine your primary needs and start building your strategy from there.
  • Industry KPIs: Don’t just think about your company. Research your industry KPIs - like sales, ROI, and profit margins - and develop a firm understanding of these benchmarks so that you know exactly how your business is doing in the big picture.
  • Custom KPIs: There are going to be company-specific metrics that you’ll need to keep track of. Set these up early on, so you know what to track and how you’re doing.
  • Historical Data: You can’t fully understand your business’ progress unless you monitor its changes over time. Keeping track of historical data can help you get a bird’s eye view of your company. This, in turn, can help you learn and pinpoint exactly where your efforts are struggling or where you need to make pivots in your strategies.
  • BI Clients: Consider who will be using your BI solution and cater to their needs.

Knowing how each of these factors applies to your business will help you configure your ETL to fit your specific BI needs.

2) Your Data Sources

These are the places from which you will extract your information (the E in the ETL process). That's the first stage; after that, there's the "Transform" stage to prep the data for the proper sources (in Xplenty's case, our on-platform transformation tools allow its customers to transform, normalize and clean their data) and the final "loading" stage.

Let's focus on the first stage - "Extract" - here. Most businesses these days have data coming in from many different sources, and all of this information must be analyzed comprehensively. This means that you have to gather and organize your:

  • Core Data: Data generated by your business via mobile app, website, online shop, etc.
  • Peripheral Data: Data generated from purchased products or services, like a CRM or an analytics system.
  • External Data: Data gathered from things like sentiment analysis.

The first step here, then, is figuring out what data sources you have, what information is most relevant from each source, and how to look at them comprehensively.

This brings us to our next point - data storage.

3) Your Warehouse

Once you know the what your data sources are and which type of information you need, you have to decide where you want to store (or Load) the information. For most businesses, this means choosing and building a data warehouse. If they’re organized correctly, data warehouses can give you a comprehensive view of your company’s history so that you can understand how well your efforts are working and make powerful strategic decisions. Once again, though, it’s all about taking your time and doing it correctly. For data warehouses, this means determining things like:

  • Schema design
  • Cloud vs. On-premise
  • DB Size
  • Concurrency
  • Scaling

Related Reading: What to Consider When Selecting a Data Warehouse for Your Business

4) Your Business Intelligence Team

As you've probably gathered from previous sections, building a strong ETL for BI plan is a lot of work. You need to make sure that your team can effectively organize and execute their tasks. To do this, you will need someone in each of the following five main roles. Keep in mind, this doesn’t mean you need five different people accomplishing these tasks, but rather that you should ensure that someone on your team has the bandwidth and skill to fill these shoes:

  • Head of BI: Equipped with business and technological skills, this person will establish and execute the BI strategies that generate insights and improve your business.
  • BI Developer: Your developer will design and build data pipelines to integrate data from various sources, ensuring that all of your most important information is properly extracted, transformed, and loaded into your data warehouse.
  • Data/business analyst: The analyst acquires, processes, and summarizes data. He then uses this information to supply their organization with reports, summaries, and visualizations, thereby transforming the analytics into comprehensible, actionable insights.
  • Database Analyst: This person is in charge of all things database-related. The DBA maintains database systems, creates new database applications, supports existing database applications, and manages an organization’s data and metadata.
  • Data Scientist: The data scientist utilizes computer programming, statistics, analytical tools, and machine learning to pull out actionable insights from big data.

So why do you need all of these roles filled on your team? BI is completely useless if it’s done wrong or if it's incomplete. Without committed BI team members, existing employees will have to split their time between conducting their analytics and focusing on other core aspects of their position. In such instances, BI will always come second, which means that certain aspects of the job will most certainly fall through the cracks.

This is especially true if there is no one in the company that’s holding the team accountable and ensuring that BI efforts don’t go to waste, so it’s particularly helpful to have someone in your upper management as part of your BI team and support system.

Putting It All Together

Focusing on these four components and developing them is the first step to creating a comprehensive and useful ETL BI strategy for your business. Of course, what you see here is merely a cursory overview of the planning process. For more resources on business intelligence, check out these related articles:

For a step-by-step breakdown, check out our eBook How to Build an End-to-End BI Solution or contact one of our solution experts today.

How Xplenty Helps

A strong ETL for BI strategy must have a strong ETL solution at its core. That's where Xplenty comes in. Xplenty's powerful on-platform tools allow its customers to effectively transform and analyze its data, all while adhering to compliance best practices. With Xplenty, you can integrate, process, and prepare critical data for analytics on the cloud. To experience the Xplenty platform for yourself, contact us to schedule a demo.