Why a Data Warehouse will supercharge your Business Intelligence system

You have an ERP system. And you also have—or plan to have—a Business Intelligence system. So do you need a Data Warehouse as well? Quite definitively, no. It’s perfectly possible to run Business Intelligence against a live ‘production’ ERP database.

Now let’s ask a different question. Should you have a Data Warehouse? And the answer, quite definitively, is yes. A Data Warehouse isn’t essential for Business Intelligence—but having one will certainly make a huge difference.

Why? Simply put, a Data Warehouse transforms the ease with which you can exploit your investment in Business Intelligence, and also greatly enhances its power, usability, and performance.

Let’s look at why.

A Data Warehouse is built for reporting.

All ERP systems are built on relational databases. If you’re running an Oracle ERP system, for instance, you’ll often be running on an Oracle database. Microsoft ERP systems—think Dynamics AX and Dynamics NAV—typically run on Microsoft’s SQL Server database, as do many smaller ERP systems from other vendors.

And, of course, it’s perfectly possible to run a reporting suite or Business Intelligence tool directly against that SQL Server relational database. Heck, Microsoft even bundles in some tools, such as SQL Server Reporting Services, to help you do that.

But it’s not a good idea. That’s because raw ERP databases—from whatever vendor—employ a technology called ‘normalisation’, which spreads data across multiple tables in order to minimise redundancy and make the database more efficient at storage.

Data storage versus data reporting

And the result is that making a database more efficient at storage isn’t the same as making it more efficient at reporting. Not the same thing at all.

That’s because in order to meaningfully run reporting queries against a normalised database, it’s necessary to specify the links between all those normalised tables. And there are three problems with this.

First, it’s complicated. You don’t have to be a hardcore programmer—or even work in the IT function—to use Microsoft’s Structured Query Language (SQL), although it certainly helps. But as for self-service reporting and ad hoc queries by ordinary end users, forget it.

Second, because it’s complicated, it’s also easy to make mistakes. And in a reporting context—especially financial reporting—this isn’t a good idea. And even worse than reporting queries which report the wrong results, are reporting queries which crash the database.

Third, running a reporting query against a live database slows that database down. So the order entry process slows down. The accounting and finance function slows down. And the warehouse and factory floor slows down. Plus, of course, the people waiting for the reports are slowed down, too.

So what’s the answer?

A Data Warehouse is built for reporting.

Once it’s pointed out, the answer is actually glaringly obvious. Take your database, and build a second copy of it—but in a manner optimised for reporting, not storage.

And that, at its simplest, is what a Data Warehouse is: a database built for reporting.

So it isn’t normalised, but de-normalised. Relationships and data aren’t spread over multiple tables, but instead rigidly encoded in a data ‘cube’. (Think of it as slices across a 3D chessboard.) And—critically—getting data out of that data cube doesn’t call for complicated IT skills, so self-serve reporting becomes easier.

That’s because all the hard work in terms of mapping those relationships has already been hard-coded into the ‘Extract, Transform and Load’ (ETL) tool that takes data out of the live database and pops it into the Data Warehouse.

Just as importantly, specifying data relationships within the ETL tool eliminates error—if it’s specified correctly in the ETL tool, then it will be right in the reports.

The bottom line

Now, so far—and in the interests of strict impartiality and neutrality—we’ve been using the term ‘reporting suite’ as something of a catch-all. But in fact, all of the above applies to any Business Intelligence tool that runs against a live database.

So you won’t be surprised to learn that here at Matillion, we regard a Data Warehouse as a core component of our Cloud-based Business Intelligence ‘software as a service’ offering.

Select Matillion as your Business Intelligence provider, in short, and our Data Warehouse and ETL tool comes as part of the package.

Along with all the advantages of running Business Intelligence against a Data Warehouse, not a live ERP database.

If you would like to find out more about the role of a Data Warehouse in a Business Intelligence solution, consider downloading this useful Ebook.