Challenging the single corporate Enterprise Data Warehouse (EDW)

In this blog I want to focus on the architecture involved in realising a company-wide Information Management (IM) solution for flexible reporting and in so doing challenge the current dominance of the single, corporate Enterprise Data Warehouse platform.

However, before I continue (and because of the plethora of terminology being used by different people), I want to take some time to formalise my definitions of “Technical Architecture”, “Data Model” and “Information Model”. I will do this from the bottom up.

Definitions

Technical Architecture
Technical Architecture is made up of the different software and hardware components in your solution landscape. In the context of IM, you will have one or more Data Warehouses (e.g. Teradata, SAP BW, Oracle Exadata, and Microsoft SQL Server), an ETL infrastructure (e.g. Informatica, SAP Data Services), a network infrastructure and (finally) reporting tools (e.g. Business Objects, Cognos, PowerPivot). The physical hardware together with the software installed on top are what comprise the Technical Architecture. It provides the platform and tools that you can use to design and build your Information Management solution.

In my opinion people are overly focused about which of tools to use for their Technical Architecture. Software Vendors will obviously push their products and attempts to differentiate between the different tools on the basis of application features – think of SAP pushing the HANA platform on the basis of the In-memory acceleration or Teradata on the basis of Massively Parallel Processing. In a recent project we had a Data Warehouse platform shootout and found that all the platforms provided more or less the same performance and functionality. Where they differed was in cost, in how they delivered the features, and the availability of skills in the different markets to develop and maintain these platforms (I will share my thoughts on this topic in another blog). For me the platform you choose will probably be good enough to do what you want it to do since they all provide more or less similar functionality – what is critical is that you employ the right skills in order to realise your IM solution.

Data Model
The Data Model consists of the extraction, transformation and staging of fields, tables and relationships (between those tables) for different data objects within the IM Landscape. The Data Model (should) provide the platform for a scalable and supportable IM solution through guaranteed repeatability as well as accuracy. In Bill Inmon’s Corporate Information Factory Model, and in SAP’s model of Layered Scalable Architecture, Data Architecture represents everything up to and including the Enterprise Data Warehouse (EDW) Layer.

Information Model
The Information Model is the layer on top of the Data Model which delivers your organisations’ KPI’s. For example, if you have a KPI such as Gross Margin, it will be calculated in this layer based on the “Sales Value” and “Cost of Goods Sold” data objects available in the underlying Data Model. The combination of these 2 data objects, plus the calculation required to determine the KPI will be represented in the Information Architecture through either pre-calculation and staging or on-the-fly report calculations.

The way in which the Information Model is materialised is again influenced by a combination of (1) the richness of data in the underlying Data Model and (2) the abilities (technical skills) of the technical resources you use to develop your IM solution.

Challenging the dominance of the single reporting EDW platform
Now this is where it becomes interesting. Over the past 12 years I have been involved in numerous Data Warehouse implementations. I specialise in SAP BW, but also have experience with some of the other IM platforms (SQL Server and Oracle in particular). My focus has always been to create a single EDW within the SAP BW platform. The idea is: if you have all your data in a single reporting EDW platform (the Data Model), you can generate whatever reports that are required and satisfy all your IM reporting needs (through the Information Model). Don’t get me wrong, I am not saying this approach is no longer valid – if you have an infinite amount of money to implement an IM solution (think ECB bailing out countries or the Fed bailing out banks…), and your business remains static, and you get the right resource, and… then you probably have a fair shot at this.

The biggest issue is that business evolves over time, and with that evolution IM requirements will also change. Even worse, through evolution the sources of data will also be constantly changing. So looking at the diagram above, both the Data and Information Model will have to be constantly adjusted to reflect the changes in the underlying data sources and reporting requirements. None of this is impossible, but it all comes at a (hefty) price regardless of the Technical Platform selected (with the Vendor’s original promises about flexibility and low TCO a faint and distant memory of the past).

We have been grappling with this particular issue at one of my customers. The platform they are using is SAP BW. The problem is that their current reporting solution is too costly, and it lacks the flexibility to cope with the changing business environment. So we were tasked with coming up with a solution that will address both of these issues.

In order to deliver a flexible and cost-effective IM solution, we had to challenge a number of paradigms such as:

1.    “You need to have a single set of reports that can be used by everyone”

The truth is that for some business functions this will be true some of the time, and for others this will not be true most of the time. Think of Finance – you would imagine a P&L is a P&L. Well, mostly it is, until you want to generate management insight with the data sitting underneath the P&L. Same with your sales function – a “Sales by Customer” report will show you if you are going to hit your revenue targets, but unless you link Sales and Trade Promotion and Point-of-Sales data (all coming from different sources that will also change over time) you get a limited view of the effectiveness of your marketing strategies. The conclusion we came to was that you need a number of “Anchor” reports that will also provide you with baseline KPI’s against which you can reconcile your Flexible reports.

2.    “You need a single, centralised IM Solution”

You might need it, but the reality is that as soon as anything is centralised, people throughout the organisation will start developing their own solutions (I call it reporting factories, but it could be anything from your “trusted” MS Excel solutions to reporting models generated in MS Access all the way through SQL Servers sitting hidden out of clear sight under desks in the office or in data centres in the cloud). The fact is, people want control and they want flexibility, especially when it comes to Information Management.

3.    “A centralised MI solution will be cheaper”

When you tell end-users that you are going to deliver a single, centralised MI solution, they immediately think to themselves “I have one shot at defining all my reporting needs and that will be it – so I am going to spec out all my past reporting requirements as well as everything I can dream up for the future so that I am covered for all possible scenarios”. Not only is it extremely expensive to establish the single, centralised IM solution (if you are a large multinational and want to go down that road, be prepared to fork out in excess of $100m), but it will also be expensive to maintain because of the complexity. Then, to add insult to injury, after you have delivered the solution that nobody really wants (because it is over-spec’d to the point of being unusable), the users go off and create their own reporting factories and the additional cost of IM is again hidden throughout the organisation.

4.    “You will bring all data in the organisation into your centralised IM solution”

That simply does not make sense. Take Point-of-Sales data as an example. The level of detail and the format of the data varies hugely from country to country. On top of that, the data is only relevant for analysis in the country of origin – at best maybe at the regional level – but you almost probably will have no requirement to analyse that level of data at a global level.

5.    “All reporting should come from the Web”

If that was the case, why do most information analysts still insist on using Microsoft Excel? Don’t get me wrong, I believe there is a valid place in the organisation for web based reporting (where the audience is numerous and they require limited analytical ability), but to build an IM platform with the dictate that all reports will be delivered via the web is much too simplistic and unrealistic. Fact is, the really serious analysts want to use MS Excel and they will not settle for a semi-functioning internet based solution.

If you are faced with the challenge of delivering a flexible and cost-effective IM solution for your business, please contact us. We provide a practical, pragmatic approach to delivering IM solutions that address your specific issues and challenges.

CONTACT US