Doctor's Note (May): Modern Business Intelligence

In the last note, I mentioned that the Microsoft platform provides the better tools for BI. I was asked to expand on that. First of all, there is no concise definition of BI. The Wikipedia definition does not help because it is so broad that it includes everything. Almost everyone will agree that a huge component of BI is the process of bringing the required data to the point of decision making.

As BI comes into the mainstream, stand-alone systems are being consolidated. Many of the major players have been acquired by either ERP systems or other software vendors. In the future, BI will be part and parcel of every decision support system and not a separate function delivered by a separate system.

The first generation of BI tools assumed that the data needed to make decisions was stored in ERP systems. They delivered transactional data to people through predefined reports. The second generation like Hyperion, Cognos, and others extended their scope to systems beyond just the ERP. The ERP’s responded with data warehouses and tools to deliver their data to users from a consolidated database through predefined reports. All these systems missed a key point; people need up to date data, but they also need consistent data. For example, having the inventory from 2 hours ago and orders from a minute ago creates a problem because you have trouble figuring out whether an order in the order file has been decremented against the inventory. The only way to deliver consistent data is to create snapshots. It is for this reason that S&OP reports created using transactional systems rarely work. Imagine going into an S&OP meeting where each person has a different report of the current inventory.

Another important aspect is that a lot of the data that people use to make decisions is not in the ERP, but in spreadsheets and other unstructured sources. Unfortunately, spreadsheets are notorious for being error prone. In fact a recent study claims that almost 90% of all financial spreadsheets contain major errors that can materially affect results, and there is no reason to believe that supply chain spreadsheets are any better.

The third generation of BI tools is beginning to address the issue of inconsistent and disbursed data. Because the Microsoft platform is so well integrated with the user’s desktop, it provides an ideal framework for developing this capability.

All first and second generation BI systems separated the specification of what is extracted from the data delivery . These systems rely on a separate step to define the selection and aggregation of data. This is contained in the report definition which often requires special skills to create. Users are left with the choice of either learning these skills or relying on IT to create them.

The problem, of course, is that you can’t really define what to look for if you don’t know exactly what you need. The third generation of BI systems like Zemeter get away from predefined reports altogether. Their strength is in data analysis and not in creating reports. They put the search, select, sort and aggregate functions directly in the hands of the user.

All data has a number of qualifications and attributes. The search and select process consists of looking at these attributes to narrow down the search space. Sorting helps to separate the trivial many from the important few. Aggregation is not just summing up the data. It can involve functions like averaging, removing outliers, trending, and weighting the data with various factors.

Back to Microsoft. Modern systems like Zemeter would not be possible without the underlying capability for database access and analytics provided in the Microsoft SQL server database family. This also addresses the data distributed in spreadsheets. The SQL database can be used to consolidate the key data elements shared by the users.