- Transactional data and reporting information cannot easily co-exist in the same system
- IT departments are over worked with simple query and report requests
- Data within reports is not always “fresh” or up-to-date
- Reporting data is often transferred to spreadsheets for further manipulations or chart creations
- Operational reports tend to take a long time to produce results
- Executing such reports usually affects the perfomance of operational systems
- Ad-hoc reports sometimes take a long time to develop but are usually only used once
- No central place where merged data is stored. (CRM, Marketing, Finance, etc)
- Detailed reports tend to be too detailed while summery reports suffer from not having enough information for you to act upon
- True dashboard functionality enabling you to see very summarised data but also allowing you to drill down to detailed level within the same work area
- Separate database optimized for running queries without affecting transaction system
- IT departments not needed to get involved with creating or running BI reports
-
One central place where all the organizational information can be stored and shared.
Introduction
The purpose of this guide is to provide a brief introduction to the world of business intelligence and reveal some of the possibilities it affords. Previously the domain of large companies ‐ only they could afford it ‐ DesignBI aims to be at the forefront of bringing the technology easily within the reach of smaller companies.
Firstly, a definition: Business intelligence (“BI”) provides the means to translate data into information that can be acted upon.
Behind this deceptively simple‐sounding goal lies a stack of technologies, processes, and skills along with a growing lexicon of intimidating‐sounding words, some of which might be new to you: ”data warehouse”, “OLAP”, “cube”, “ETL”, “data analytics” and so on.
Learn about:
“... information that can be acted upon” requires the qualities of being timely, reliable, and consistently defined. Here are some of the problems that prevent companies meeting these requirements:
The need for better reporting
The majority of operational systems are built using relational database technology, which is an ideal choice for ensuring the integrity of data but is not great for reporting. Such systems are usually built to store data that can answer questions like “show me the order for this customer”. They are much less capable of answering questions like “tell me what the total sales were for US retail customers last year”, particularly if there are many thousands, or millions or more of such records.
Furthermore, the performance of those operational systems would quickly degrade if large numbers of users were given free reign to run such queries and reports.
It is important to understand that this is a criticism of neither your operational systems nor the reporting tools (no matter how good they might be) that you might have deployed to query them.
Instead, it is an inevitable consequence of the use of relational database technology ‐ the database structures needed for good operational systems are different from those needed for great reporting.
The need for timely delivery of information
The pressure is on to deliver better information and deliver it quickly. It is a fortunate organisation that does not suffer from some or all of these:
• Departments spending valuable time re‐crafting data (summarising, applying rules, calculating etc ‐ often using spreadsheets) from different sources in order to present it back to managers in an acceptable or understandable format
• Reliance on operational reports run long after the information they contain can be acted upon. “We have the P&L A/C for the last quarter and profits are down 20% on last year”. Maybe, but when did you found out?
• Reliance on IT departments to design and run ad hoc reports. We often encounter companies hiding vast libraries of custom written reports most of which were subsequently re‐run rarely or not at all.
Information is actionable when it is fresh.
The need to link data held in different systems
Much valuable corporate data sits in silos where it cannot be easily used.
For example, you might have an ERP system containing customer sales and order data and an Accounts Receivable ledger for managing customer indebtedness + a CRM system for handling customer interactions + a credit rating system + a marketing database containing valuable customer insights.
Unless this information can be linked it is not possible answer questions such as “how much do European customers with a low credit‐rating and high dispute‐rate owe us?”.
Linking data enables you to answer questions that would otherwise require much time and analysis to answer, giving you new insights into your business (e.g. identifying uneconomic arrangements, spotting new sales opportunities etc). Bad enough if such questions are ad hoc and infrequent, but there will inevitably be situations where you need regular (quarterly, monthly, daily) reports linking data from different systems ‐ for example, to meet regulatory or your own management information requirements.
Two more issues to ponder:
• It might come as a surprise to some to learn that many integrated business systems (some well‐known ERP systems included) are in fact modular in design with cross‐modular reporting being clumsy at best, or impossible at worst. In other words, even your integrated systems could be acting as unlinked silos of valuable information.
• Spreadsheets are superb. However, they are difficult to secure, make poor databases and it is not practical to adopt large‐scale linking of the information they contain to data held in other systems. The proliferation of spreadsheets hiding away in departments, laptops etc has even spawned a new, derogatory word: “spreadmarts”.
Linking your data unlocks the information you already have.
The need for reliable information, consistently defined Decisions stall when the reliability of supporting data needs to be confirmed, validated, or crosschecked against other sources.
For example, experienced finance staff instinctively check that trial balance actually balance, debtor reports agree to the general ledger AR control account and so on. Systems that report otherwise will be abandoned.
Consistent definitions: Not only does data need to be reliable it needs to be consistently defined to ensure everybody in the company understands the same thing. For example, does “sales” mean invoiced sales, sales including fulfilled orders, or something else?
The above PMP findings show the improvement in data quality after implementing a data warehouse (the underlying database component of a BI system) is primarily a result of the process of creating the data warehouse, which requires data to be consistently defined and cleansed before it is loaded.
Why are others acquiring BI technology?
A fast introduction to implementing a BI system
Step 1: Copy data from your sources and store it centrally • The data is moved to an area where it can be reported upon without such reports interfering with the operational systems • Data from different sources are moved to a store where they can be linked • Data can be transformed and cleansed so that it is consistent (especially linked data)
• Tools that can Extract data from your source systems, Transform it (add rules, cleanse data, etc) and Load it to the target store (“ETL”) • A separate database store (Oracle, MS SQL Server, etc) • Tools to schedule the ETL processes so that they happen at the right time
Things to consider: • How to identify changed data in source systems (for incremental loads) vs. the practicality of full data load refreshes. • Controlling the loading of data (e.g. scheduling/timing of loads, frequency of loads, unloading of data) • Transformation rules (data and master data) |
 |
Step 2: Store the data optimally for reporting
The star schema
Although it might be tempting to simply copy source data to a central repository, there is a better way of storing data. The star schema is based upon a dimensional model with a central fact table containing one or more measures (amounts, values). Every fact record is uniquely defined by dimension keys, each dimension being a table linked to the fact table. In diagram form it looks like a star, hence its name: the “star schema”.
 |
The enhanced star schema
There are some subtle variations on the basic star schema. For example, DesignBI uses an enhanced star schema where dimensions are linked to collections of master data tables. This makes the system more flexible. SAP BW uses a similar approach. Others promote lookup tables from the dimension tables (“the snowflake method”).
Cubes
The data that is held in a BI database if often referred to as a “multi‐dimensional cube” or simply “a cube”.
In the example opposite, the data fits neatly into a 3‐dimensional cube. Its showing how a query would work, if the question was: "Show me the Margin in February for Product C" However, “Show me the sales margin for all products for February for all customers” would require more than 3 dimensions, hence the term “multi‐dimensional”. |
 |
A bi system will typically contain many cubes addressing different subjects (finance, sales, HR etc).
Step 3: Deploy tools that report and analyze