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?
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.
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.
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 possiblity 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.
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.
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.
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.
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 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
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:
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.
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.
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.
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.
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
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:
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.
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.
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