A few decades ago, the term “business intelligence” emerged as an umbrella term—much like “big data,” “web 2.0,” and “artificial intelligence”—to represent a set of technologies and processes that together provide business metrics for business users.
Typical business intelligence (BI) implementations require the creation of a data warehouse—a placeholder for the business’ key data tables, or “fact tables,” which contain the facts about what is happening in the company. For example, a fact table might state that, for a specific date, x number of units of a product were sold, the customer they were sold to, the date they were shipped, from what warehouse or selling point, and for what price. Fact tables are typically linked to other tables, or “dimension tables,” which hold more information about the fact in question.
Put together, these tables form a star or snowflake schema, resulting in a data warehouse that typically looks something like this:
Getting the data into a data warehouse requires creating interfaces from the source systems or the transactional systems. A transactional system can include financial, order entry, shipping, sales, customer relationships, and human resources data. These systems typically use screens to allow users to enter transactions and store them in a database. Transactional systems can be on-premise or in the Cloud. For example, CRM systems, such as Oracle Siebel CRM, are typically on-premise. On the other hand, Salesforce is a cloud version of a CRM system. For small and medium-sized businesses, Quickbooks is available both as an online product in the Cloud and as an on-premises, local installation.
Regardless of where your transactional system is, an interface is needed to pull the data into your data warehouse. You might wonder why this is necessary. After all, couldn’t you just access the data directly from all of your systems and report directly against their databases?
While, of course, it’s possible—it’s surely not advisable.
The Role of a Data Warehouse
Many applications, including Excel, QlikView, and Tableau, have direct database access connectors that allow you to report directly from their data sets. And many transactional systems come with a fairly good reporting system built-in, so producing a report from one of them is not all that difficult to do.
The problem is, that such an approach offers limited visibility of what is really going on in your business. To get the executive reports that you seek, you’d need to collate reports from multiple systems, identify gaps between what one system says versus the next, manually recognize achievements between budget and actuals, or find ways to produce something that provides more supporting data than one system at a time can offer.
A bigger problem that is prevalent in most large companies has to do with data cleansing and harmonization. Companies that have expanded geographically or through acquisitions typically have multiple transactional systems doing the same thing for different product lines or lines of business. At times, transactions start in one system but end up in another, even if they use the same software vendor. And modules may not be interconnected in ways that ensure that the data that is used for calculations has been standardized and cleaned.
The third problem is more technical in nature. Imagine that a company executive has just clicked on a report to disclose sales for the last two months and compare them with the two months before that. While she is doing that, the entire order-entry team is taking new orders, and the eCommerce website is simultaneously serving hundreds of customers who are checking stock, prices, and shipping dates. Here’s the problem: while transactional systems are great at processing row-by-row entry of data, they are not very efficient at producing aggregations or analytical operations such as those currently being requested by the executive. A report, chart, or table that summarizes all of the orders for the last two months requires calculating totals for those rows of data at the same time that new rows are being inserted and deleted. That agility takes a toll on the database, which, if not handled correctly, will result in a lot of frustrated customers and employees.
Data warehouses solve these problems by separating the transactional from the analytical databases and providing faster analytics and cleaner (more functional) data that can encompass multiple transactional applications.
The Role of Data Visualization
The use of data visualization—the graphical and numeric depiction of the data and data aggregates, such as numbers, tables, and charts—is key to conveying information quickly, effectively, and intuitively. In other words, data visualization turns regular data into information.
Once your data warehouse contains all the data that’s required for your business operations and management to do their jobs effectively, then your business needs to generate reports to make the data useful. But the word “reports” might be a misleading term since what we consider “reports” typically include pages and pages of detailed information about facts or transactions. We recommend using the word “reporting” to include the three ways that business users consume data, namely, dashboards, reports, and alerts.
Dashboards should be a single page of key metrics represented by tables, charts, gauges, colors, and numbers, and arranged and consolidated in such a way that the consumer can identify and focus on areas requiring immediate attention or more investigation. That focus may even lead to additional dashboards or other reports.
Reports are typically a multi-column list of transactions that took place between a certain date range, within a specific location or region, or by sales rep. It is a listing, or in many cases an Excel export, potentially with groups and totals.
Alerts are typically single metrics or phrases that are sent to users. They have to do with one key metric and provide a warning that an event has just taken place that exceeds a meaningful self-defined threshold. Alerts prompt users to action or further investigation.
What About Data Analytics?
Data analytics can occur as the data warehouse is being built and in the delivery of data visualization. It can also occur during ad-hoc processes when the business requires information that would be based on data that may or may not exist in the data warehouse and hence may not exist in existing reports.
Data analytics is the result of decision-makers thinking outside of the standard operations of the business and asking, “What if?” They might also ask, “Is there an opportunity?”
To answer either of those questions, the person building the report or investigating the potential new business opportunity needs to dig deep into the data. The required data might well be hidden in some transactional system, or the data warehouse may have already processed it. If the latter is the case, care must be taken that it has not been filtered too much or modified in such a way that it will invalidate the results.
Similar to how a data warehouse is built, a data analyst might create an interface and pull data from one or more transactional systems into a “staging area”—a temporary location for data to be pulled into the data warehouse. Data in a staging area can also be used for other purposes such as data cleansing and the temporary creation of tables. In essence, it is a database.
The data analyst might sometimes use completely different tools to dig into the data. Tools such as Excel, R, Python, and SQL are commonly used to produce different views of the data and to identify data patterns. The data analyst might also use data visualization to assist in the process.
Finally, a presentation can produce an assessment of the analysis utilizing the assets created during the process. If the analysis will need to be repeated frequently, it can be included in the data warehouse and data visualization process and automated, so as not to waste the data analyst’s time. However, if just a one-time investigation is needed, the scripts and tables can be archived to possibly re-use in the future.
So, What is Business Intelligence?
BI is everything we’ve described above—and potentially more. It may also include other areas of data exploration, such as forecasting, predictive analytics, including machine learning and statistics, master data management, mobile, and embedded data visualization, and database technology, such as document-based No SQL.
The bottom line is that BI is not simply a single type of software. It is a group of applications that are used to transform data into information that can be readily consumed by the company’s employees to monitor and execute their functions effectively.