The BI Cantos: Introduction


The BI Cantos: Introduction

This blog post series aims to provide the unfamiliar leader or participant with a view of what is required to successfully deploy a business intelligence (BI) system to ensure success. Deploying a BI system is challenging. Why would you want to undertake a project to build such a system?

To get better information faster.

Your BI system will provide reliable information if you undertake the project with the right team, adequate funding and the appropriate mindset. Nothing great is free. Fortunately, technology has improved, and the cost and risk associated with “great” are declining.

BI System Design

BI systems are designed to take data from various sources, extract and load it into a database, then clean and transform it to make it ready for consumption.

This database repository is called a data warehouse. Reports are made by visualizing the improved data as interactive dashboards that can be consumed easily and adapted quickly. This dashboard interactivity enables people to understand what has happened and what to do about it. This result requires good planning, executive buy-in, software, training, expert consulting assistance and a sufficient budget.

Canto Defined

What is a Canto? Webster provides this definition:

One of the major divisions of a long poem.

This name is fitting because deploying a business intelligence (BI) system is a significant undertaking, better managed by dividing the project into smaller pieces. We refer to the smaller parts as sprints. Sprints require two to eight weeks duration to finish. Each sprint must result in a finished product. The product includes workflows that extract and transform data from the data sources and load the data into a database. End users access the data in the data warehouse via interactive dashboards or searches that make it easy to find the information needed to understand what is happening and to perform additional analysis.

My early introduction to data may provide context for why I chose to work in business analytics professionally and how I settled upon the three most important aspects of business information.

My Data Origin Story

When I was ten years old, my father asked me if I wanted to come to work with him. I jumped at the opportunity. On the way to the factory, he told me they were conducting an inventory, and he thought I might like to see all the machines and how the factory was organized.

I didn’t know what an inventory was, but I understood that factories made things from raw materials and parts. Dad let me wander around the facility for a few hours that morning. As the morning progressed, I realized people were counting the parts that went into the products.

Later, I entered the office. Three people were working. They looked different from the people counting in the factory because they were not wearing coveralls. They wore business clothes without ties and jackets. They were pressing buttons on machines (adding machines). Mountains of paper were accumulating in front of their desks. The smell of fresh mimeograph paper was in the air.

When my dad collected me to go home, I asked him about the guys in the office. I liked the sound of the adding machines. What did those adding machine guys do? He told me they were accountants. I didn’t know what an accountant was, so I asked, and he told me. I also asked him what made an accountant a good accountant.

My dad’s answer: Speed and accuracy. Good accountants are fast, and they don’t make mistakes.

Speed, Accuracy and Accessibility

My father said that to me in 1969. The world has changed since then. Mimeograph machines and adding machines are long gone. That visit to my dad’s factory is why I got into data work, and my business degree was in accounting. Curiosity is why I started learning SQL in the 1980s. In the 1990s, SQL interest drove me to learn about database design.

The company I worked for at that time was acquiring businesses. Each acquisition exposed data inaccuracies. Data and the workflows we developed to feed our database became crucial for resolving data quality issues and reducing the time required to provide high-quality information to managers.

I’ve spent a lot of time over the past 40 years working with business information and analyzing data. The only thought that I add to my dad’s speed and accuracy comment is accessibility.

The potential of a business intelligence system is unlocked when timely, accurate and detailed data is turned into accessible information used to make informed decisions.

For the past 14 years, I’ve been doing this kind of work at InterWorks. I’ve benefited from working with our talented team and savvy clients. I’ve learned a few things about building and deploying Business Intelligence systems. I appreciate that most people aren’t technology experts and need help understanding technical jargon.

You don’t have to understand all the technical details of BI systems. Still, appreciating the types of technologies involved will help you understand the functions they perform.

Differences Between ERP and BI Systems

Enterprise Resource Planning and Business Intelligence system serve fundamentally different purposes.

Enterprise Resource Planning (ERP) systems are designed to ensure that all transactions are quickly and accurately input into the system. This data entry is accomplished manually, through automation, or both. ERP systems (sometimes called transaction systems) are intended to improve operational workflows by making them more efficient and accurate. The data captured includes customer details, product details, order details, and employee records—everything required for processing orders, paying vendors, billing customers, collecting receivables, managing inventory, paying employees, and so on. These workflows are the building blocks for your system to deliver customer value—the primary goal of ERP systems is to capture transactions accurately and as quickly as possible.

Business Intelligence (BI) systems are designed to facilitate data analysis at any level of detail over any time frame. This data must be stored at the most detailed level possible and be enriched during the extract, load and transform processing to make it easier for people to analyze accurate, high-performance data. The primary goal of BI systems is accurate reporting and analysis. BI systems require several software components.

  1. A database
  2. A dashboarding tool
  3. Extraction tools for pulling data out of source systems
  4. Transformation tools for fixing errors and augmenting data

Databases that hold BI data are called data warehouses. Your ERP system also has a database but isn’t designed for data analysis. The software for moving data from the source systems into the data warehouse must accommodate the velocity, volume and variety of the data in the workflow.

What Is the Cloud?

In the past, all ERP systems and BI systems hardware and software resided on-premises at a company’s physical location. The business owned, operated and maintained these systems.

Over the past decade, we’ve experienced the emergence of cloud platforms. The cloud refers to software that resides on computer servers physically located in buildings owned by software vendors or cloud platform providers. These locations are also referred to as server farms.

Customers access cloud platforms using secure connections over the Internet. Cloud platforms are how software-as-a-service (SaaS) vendors provide customers access to their products. The data that customers add is stored in databases that reside in the cloud, not on-premises, at their location. Today, companies prefer hosting their ERP software on-premises but strongly prefer hosting BI systems in the cloud.

One of the first cloud software-as-a-service (SaaS) vendors was Salesforce. In the late 1990s, Salesforce pioneered customer relationship management (CRM) software using a SaaS billing model. CRM software is similar to ERP software but focuses exclusively on customer relationship management. Think of CRM software as a specialized subset of ERP software. It focuses on collecting transactional data related to adding and developing customer relationships, managing new orders, and managing sales and marketing communications related to customers.

The SaaS model “leases” access to the software over the Internet. This billing model is popular because it reduces the initial investment for software, spreading the cost over the service contract term. SasS vendors provide a variety of contract durations. Different licensing durations allow customers to experiment with new software without spending much money. When the customer is ready, they can sign up for a more extended period at lower rates. Typically, contracts that exceed one year require up-front annual payments.

There is a large and growing SaaS marketplace for processing and analysis of data in the cloud, and data integration software is more accessible. The SaaS model wins market share because it provides cost and security advantages.

Cloud Versus On-Premises Systems?

Cloud-based platforms have become the preferred way to store and maintain data because they provide significant performance and cost advantages over on-premises systems. Why? Cloud-based systems:

  • Require much less time to install.
  • Eliminate the need to purchase and maintain hardware.
  • Make it easier to provision and adjust software and hardware capacity.
  • Connect to other cloud data sources more efficiently.
  • Provide robust security based on industry-standard protocols.

Cloud hardware is provisioned via contract by software vendors or cloud platform providers. Once you get access to a cloud platform and the SaaS software you select, you can load data. An experienced consultant can accomplish these tasks in less than one day.

On-premises systems require you to buy hardware and software, then install the hardware and software. These installations can require weeks or months to complete. Typically software vendors charge a maintenance fee to keep the software up-to-date, and you must install the updates.

SaaS software is always up-to-date. You don’t have to install anything. The vendors own, operate and maintain the software and hardware. You pay an annual fee to access the software. Depending on the SaaS vendor, licensing is done based on capacity or the number of users.

As described earlier, significant cost savings are realized in the first year of deployment using Cloud platforms and SaaS software. Perform a complete accounting of the total cost of ownership of on-premises systems, and you will discover the cloud/SaaS model is typically less costly over the long term.

The SaaS model also enables fast and easy capacity adjustments as your needs change. You make changes by selecting menu options on a website.

Finally, many data sources are now cloud-based. There is a large marketplace of tools for connecting and extracting data into your cloud database from the cloud and local sources. Applications Programmer Interfaces (APIs) have revolutionized this work by providing per-engineered connections. APIs make it easier and less costly to move data when compared to custom-built solutions. These savings can’t be overstated. APIs save time.

One last significant difference relates to data security. The three most prominent cloud platform vendors are Amazon, Microsoft and Google. It’s unlikely your internal team will manage security better than these companies.

ETL versus ELT?

Now that you can appreciate the differences between on-premises and cloud systems, there are nuanced differences in the order of operations for cleaning and transforming the source data you want to add to your BI System’s database.

The letters (ETL/ELT) are shorthand, which refers to moving data from raw data sources such as your ERP system, a cloud-based CRM system, or any other cloud or on-premises data sources. Each letter is a shortcut that refers to a part of the process.

  • Extract data for loading into a database.
  • Transform data by correcting errors and improving it to facilitate consumption.
  • Load data into the database.

In on-premises systems, the order in which these steps occur is Extract, Transform, and Load (ETL). Cloud systems facilitate Extract, Load, and Transform. Either way, the process converts raw data from source systems into cleaned and transformed data in a data warehouse.

This small change in the order of operations (ELT-ETL) is an important nuance. The older on-premises ETL process delays getting data into the data warehouse because it only lands in the on-premises database (where information consumers can see it) after it has been transformed into its finished state. Changing the order of the data workflow to loading then transforming data (ELT), the data is loaded into the data warehouse sooner (while it’s still not entirely changed).

The data warehouse can provide more ways to complete the data transformation steps and allows access to end users more quickly. The ELT process is desirable with appropriate controls and warnings, especially when the data is time-sensitive. Cloud-based SaaS software makes this process more manageable while providing superior security and interoperability. Licensing still requires estimating your future needs but over much shorter time horizons.

As I mentioned before, adjusting capacity in a cloud platform is easier and faster because those changes require website selections instead of provisioning additional computer hardware. SaaS software can be contracted in small increments or over the years. You can decide what commitment term is best.

The flexibility and speed enabled by cloud-based SaaS licensing provide greater flexibility than legacy on-premises systems. This is why SaaS licensing is desirable.

How This Series Is Organized

This blog post series discusses planning, deploying, managing and maintaining analytical business information systems. I have divided the series into four parts, each with five sections:

  • Part I: Rationalizing and Planning
  • Part II: Establishing Foundations for Success
  • Part III: Licensing, Expanding and Maintaining
  • Part IV: Cultivating a Data Culture

Each piece starts on Monday and ends on Friday.

Part I: I focus on defining the benefits of analytical business information systems so you can plan and deploy one. I explain how to develop a solid deployment plan that gets executive buy-in, establishing a reasonable timeline and costs for deliverables.

Part II: I discuss discovery sessions, identify critical data workflows and show the importance of capturing user needs so that you can locate suitable software for your database, ELT tools and dashboard software, all essential elements for your BI system. It is vital to start with a proof-of-concept (POC) project sprint that enables your team to develop skills with the software you selected and confidence that it will meet your needs. This POC should have enough complexity to test all the tools.

Part III: Even with SaaS models, you must license software. Improve data workflows. Monitor and manage capacity. Maintain security. Adapt to evolving needs. You must also secure the data and maintain the system you create. Maintenance is uninspiring “plumbing” work. I discuss tactics for effectively dealing with maintenance chores via expert consultants.

Part IV: You can buy and deploy the best tools efficiently, but you must educate your user base to achieve high investment returns. In the final part of this series, I cover the ins and outs of establishing a Center of Enablement (CoE), leveraging public user groups for skill-building, measuring your project’s ongoing ROI, and other suggestions to ensure that you get the value you require from this investment by providing the foundations for developing a data culture that engages your team to turn data into information to make more informed decisions.

If you have questions or comments about the series, feel free to reach out through InterWorks’ Contact Us page or my LinkedIn. I will use your feedback to improve the Amazon ebook published following this series’s conclusion.

More About the Author

Dan Murray

Director of Strategic Innovations
Thoughts on Tableau Conference 2024 I’ve attended every Tableau Conference since the first one in 2008, held at the Edgewood Hotel in Seattle. That conference included 150 ...
Ten Questions for ChatGPT about Tableau and Level of Detail Expressions I had some fun with ChatGPT asking it questions about cohort analysis this week. I’ll spare you the 4,000 words it created on general ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK


Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Geschäftsführer: Mel Stephenson

Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072


Love our blog? You should see our emails. Sign up for our newsletter!