What is Business Intelligence?

Perhaps you think Business Intelligence is something only the largest companies have, or perhaps you think of it as a fancy name for reporting. In fact, Business Intelligence is different to regular report writing, and it is something that small & medium businesses can benefit from as much as multinational corporations. First, let's talk about reporting versus Business Intelligence.

The Evolution of Reporting: Built-In Reports

At its most basic level, you may own a piece of software that comes with pre-built reports. Depending on the software, these reports can range from being useless to being all you need. Very old software might spit out a static table of data that then has to be consumed using a tool like Monarch, whereas newer software will include fancy charts and dashboards and will allow exporting to PDF or Excel.



Example of a built-in report from Quicken


Relying on the software for reporting makes sense if all your data is in a single application (such as QuickBooks) and you are using the system in the way that it was designed. It has a much smaller learning curve than other reporting tools, which makes it especially attractive when you don't work in IT.

That said, relying on built-in reports quickly becomes limiting if you want to report from multiple systems at once (e.g. you want to report from your CRM tool and your mainframe at the same time) or if you want to report on something in a way that the built-in reports don't allow for. In these situations, the most common resort is to take data-dumps from the various systems, get them into Excel and do your own analysis.

The Evolution of Reporting: Centralized Reports

The next step after built-in reports is writing your own reports using a database language called SQL. There are a lot of different pieces of software that allow you to write SQL: Excel, Microsoft Access (both part of the Office Suite), WinSQL or fully fledged database systems such as Oracle and SQL Server.

The benefit of writing your own SQL is that you are not limited by the design of the software you are reporting from. You can combine data from multiple systems, aggregate data, run your own calculations and apply your own business rules. However, when writing SQL it is very easy to make a mistake (like an inner join instead of an outer join) and inadvertently exclude data that you didn't mean to exclude. Reports can be wrong, but in such a subtle way that it is hard to tell.



Example SQL code: spot the deliberate mistake!


Since writing SQL isn't straightforward, you'll see that in most companies the IT department ends up writing most SQL-based reports. This comes with its own set of problems: anyone who has ever had a wonderful idea for a report, sent the request to IT and then waited 6 months or more for it to happen knows what this pain point is.



Example report request: but how long will it be before it gets done?


So what are the disadvantages of centralized SQL-based reports?


  1. Slow. A typical IT department doesn't have the resources to build any & every report request from every area of the business. Report turnaround times can be slow and many reports never get built at all.

  2. Complex. Even a simple report of sales by employee is more challenging than it sounds. What counts as a sale? If an item is returned 6 months later, do you want to dock the original sale? Do you want the report for last week, for last month or YTD? Do you want to compare to last years YTD? Do you want a management level view that rolls up individual employees to their managers? How should the rolling up work if an employee regularly works in different locations? What happens if your system for selling insurance is separate from your system for selling your products?. Suddenly a simple report of sales by employee has gotten a whole lot more complicated.

  3. Mistake Prone. With SQL, any business logic written in one report has to be copied & replicated in every other report that includes the same data. This doesn't sound terrible, but when calculations are copied, there's a possible that the copies don't match. For example, say you have a calculation to determine which credit score is the one used to score a loan. That calculation is fairly complicated as it has to account for different laws in different states. That calculation is now used in 20 different reports. A change is made in the law, and now that calculation has to be updated. What if IT misses one or more of the reports? You might not be able to tell it's calculating wrong, because 80% of the time it's calculating right. Except now for that one state where the law did change, some of your reports are now wrong.

  4. Inconsistent.The other downside of having IT write all your reports is that often the analysis of the data falls back on the business user, who might be importing the data from the report into Excel, filtering or performing their own calculations, combining it with other reports and producing executive summaries for senior management. This sounds wonderful (less work for IT!), but here there are two challenges: first, end-users are spending an awful lot of time manually manipulating reports that could be automated. Second, business rules and logic are buried in Excel and known only to one or two people. What happens if they leave? IT might not know what business rules are getting applied before the report goes to senior management, so when another report built by IT doesn't match, pandemonium breaks loose and a lot of time is wasted investigating what the difference is.

The Evolution of Reporting: Self-Service Reporting

The next logical step for many places is to give business users direct access to the tools IT uses for reporting. Some may think of this as self-service reporting (since, after all, the business users can now build their own reports). In reality, this is the worst of both worlds.


  1. Steep learning curve. The business user still has to know & learn SQL in order to be effective. This may come easily to some, but certainly not all. Mistakes can result in incorrect data being reported.

  2. Inconsistent. The business user is directly accessing the raw data in the system. This requires a good understanding of how the data is architected. Additionally when business logic or calculations have to be applied to the raw data, it's possible that the business user does something differently to IT, resulting in contradictory reports and causing a general mistrust of reporting.

  3. Insecure. Frequently, access to the raw data means complete & unlimited access to everything. Any security that exists in the system gets bypassed. This might be ok if the person building their own reports is a manager. However, if the person building their own reports then wants someone else to run them, more & more people start getting complete & unrestricted access to everything.

Business Intelligence

Business Intelligence combines the best of everything above while simultaneously addressing many of the pain points. Essentially, IT takes the business data and stores it separately from the system it came from (typically in a data mart or data warehouse with fact and dimension tables). By doing this, they can combine data from multiple systems. They can also apply business logic to the data and store the results with the data. Essentially, they "cleanse" the data up-front, so that the cleansing doesn't have to be done as part of the report writing.

This cleansed data can be reported on directly (though this makes less sense as you'd still have to know SQL to write reports). The better option is to expose the data in one or more Business Intelligence cubes. Cubes can be accessed directly from Excel using Pivot Tables, allowing business users to build their own reports. This has a huge number of advantages over other forms of reporting:


  1. Accurate. The data is pre-cleansed. This means that there's no chance a report of Sales written by Sales against the cube would not match a report of Sales written by Finance from the same cube. Neither department is applying business logic to the data they get back as the business logic has or should already have been applied.

  2. Easy. The data is easy to report on. Instead of having to understand SQL and the intricacies of joins, end-users just need to drag-and-drop fields into Excel. It's easy to do. If you want a report of all sales yesterday, drag the field over. If you want to know sales by salesperson, drag the salesperson field over. If you want to know the top 10 salespeople, click the sort button.

  3. Fast. The data is fast. With SQL, the reports are running against the source system. If you want to report on sales YTD, the query has to fetch every single transaction from January 1st until today and then add them up. This can take a long time. With a cube, the data has already been fetched and pre-aggregated. Set your date filter to the current year, and the number(s) will come back in a blink of an eye.

  4. Secure. The data is both accessible and secure. If there is data you don't want most people to see, you can restrict it while still allowing access to the rest of the data. Giving someone the ability to build their reports no longer means giving them unlimited access to all data.
  5. Empowering. The end-user can explore the data themselves. If the number for a given month looks wrong, the end-user can break down the number by day, or by person, or down to each individual transaction to find out what happened and address it. No longer do they simply have to trust what IT tells them.



Example pivot table: notice the drag-and-drop fields of data on the right, the drop-down box at the top that allows you to change who the report is for, and the + symbol on the table rows that allow you to drill in further - all built in less than a minute in Excel

The two main downsides to cube reporting:

  1. It isn't real-time. Typically reports get updated overnight for the previous day. So, if you're looking to spot a problem with activity as soon as it happens, an SQL report makes more sense. However in this case the SQL report is a simple report looking for a specific scenario and alerting you to it - it isn't a monstrous report that's calculating sales YTD and re-creating business logic that exists in the cube.

  2. A cube can be slow if you want to get a "data-dump" of every single transaction for you to do your own analysis. Cubes are not optimized for this - cubes work best when they do the analysis for you.

Conclusion

As you can see, Business Intelligence is more than just writing reports. It's about empowering end-users to write their own reports in an easy-to-understand Excel environment, safe in the knowledge that the underlying data is right. IT ceases to be a bottleneck that slows down access to vital business data. Now, anyone with Excel skills can explore their own business data without delay.

If you work in IT and do reporting but not Business Intelligence, I encourage you to learn more about Business Intelligence before you lose your sanity. If you work outside IT and are frustrated by slow report turn-around times and inaccessible data, I encourage you to advocate for the development of Business Intelligence capabilities in your organization. Having Business Intelligence capabilities is a competitive advantage: giving managers the ability to spot problems, identify trends and make fact-based decisions quickly.



Example dashboard, built in Excel using Business Intelligence tools