BI Architecture is not Data Architecture

BI delivery teams sometimes make the mistake of believing that business intelligence is mostly about architecture of the data mart and data warehouse, and once that has been done, “the BI part is easy”. After all, the only task left is to point the BI tool to the data mart and pull in the tables, right?

Let’s take a simple example. Our business users have given us the following requirements:

1. I need the ability to start at the year, then drill down to the quarter, month, and day.
2. I need the ability to include or exclude dates that fall on holidays, as they tend to disrupt analysis of “normal” operations
3. I need the ability to analyze general data trends across quarters and months, regardless of year (e.g., do my sales tend to go up in the fourth quarter?)

In response to these requirements, we’ve built the following Date dimension in ERwin:

And here is what you get in the BI tool if you stop here and declare victory:

What’s Missing

In this case, the “BI part” was easy because we left it incomplete. The users are left wondering whether we fulfilled the requirements they gave us (it’s certainly not evident from what we have presented them).

Have we given the end users the functionality they need? Yes, but you’ll probably have to take my word for it, since it’s not obvious from the data model. The real problem is that users aren’t going to be able to understand how to use this functionality, even if we have been successful in providing it to them. For example, to drill down from 2003 to the first quarter of 2003, do I go from CALENDAR_YEAR to CALENDAR_QUARTER or CALENDAR_YEAR_QUARTER? This isn’t defined or documented anywhere (and will make a big difference in the results!). How do I trend across quarters? This is not evident in the BI tool, or even the data model diagram. The column names are technical, and there is nothing indicating their purpose or how they fit in with the other columns from a functional standpoint.

BI Architecture

In stark contrast to the data architecture effort, let’s now consider what it means to architect the BI layer itself. It is important to drive the modeling efforts at the technical level from the understanding of the business requirements. Let’s first model those three business requirements we’ve been given by the business in Consensus.

Here we have defined a user story diagram articulating how the users will interact with Date data. We have taken each of their requirements and modeled it in Consensus. The first requirement, “I need the ability to start at the year, then drill down to the quarter, month, and day” is represented in the first hierarchy on the left, named “By Calendar Year”. The hierarchy has four levels: year, year-quarter, year-month, and day. The second requirement, “I need the ability to exclude dates that fall on holidays”, is represented in the last hierarchy on the right, named “By Holiday Indicator”. The third requirement, “I need the ability to analyze general data trends across quarters and months, regardless of year”, is represented by the middle two hierarchies.

Creating the user story diagram in this way makes it clear that we have covered all three requirements, something that is not obvious when looking at either the data model diagram or the BI tool. Additionally, when looking at the hierarchical needs of the solution, new requirements become clear, like the need to populate Calendar Year-Quarter with not only the quarter but also the year. In the data model view, it is not obvious why this needs to be done, and we may even think we can re-use Calendar Month (we’d find out much later that we can’t). Finally, when generating a BI solution out of Consensus using this Calendar Day topic, Consensus will create the DDL for the data mart in the above ERwin diagram. But it will also create all the necessary hierarchical structures in the BI tool so that the users are presented with a solution that behaves the way they think, and does not have them wondering how to get a flat data model solution to give them the hierarchical answers they need:

There is no longer any question of how to navigate from year to quarter; it is built into the solution. The confusing technical column names are replaced with business names, and hovering over the name will provide a business definition (in case you’re wondering about the difference between Calendar Year-Quarter and Calendar Quarter, just check the definition of each). There’s no question about what columns to use to trend across quarters and months, as they are in their own hierarchies on the left.

Users are increasingly demanding solutions that work the way they think. Gone are the days where we can simply import technical database metadata into a BI tool and call it done. Understanding how the user needs to interact with the data is essential to creating a solution they can use effectively, and it is essential to obtaining a high level of business user satisfaction with your BI delivery.