Microsoft Business Intelligence: future #3

The central theme of these articles is that Microsoft has been moving in a direction that will make creating an enterprise solution considerably much harder to achieve. 

One key to such a solution is having some mechanism that’s capable of holding all data in a user-friendly manner i.e. which makes sense when looked at from the viewpoint of the business.  In this slightly technical article, we’re going to look at how the data needs to be structured so that it is in a business-friendly format, and why that’s going to be harder, given Microsoft’s current directions.

From the business’ viewpoint, nobody really cares about the technology. The business does care about provision of an enterprise solution.  As we’ve noted, the central requirement is to build a single source of the truth i.e. one data warehouse. Sure, whilst a single logical data warehouse could be implemented as multiple physical ‘data structures’ (being as generic as possible) the real requirement is unequivocal. You shall create one place to go for all information, calculations, KPIs, and so on.

Regardless of the reporting delivery mechanisms (Power BI, SSRS, or some third-party product like Pyramid Analytics), the critical underlying need is to have a single, unified enterprise-wide picture or model of all data. This is vital for both creation and maintenance of the single data warehouse.

And there lies the heart of the issue. Real, honest-to-goodness, full size enterprise data warehouses are not trivial. Source systems can easily have tens of thousands of tables: that could boil down to a hundred facts and several hundred dimensions in a dimensional model. Alternatively, we would likely have thousands upon thousands of tables if we kept it in a relational format. Not trivial at all.

Twenty years of practical experience across the world have conclusively demonstrated that the business can understand and work well with dimensional models. These exploit the concepts that facts contain numbers, and these are aggregated by dimensions.  For example, Sales Amount is Summed by Date, by Store. Simples.

On the other hand, enormous relational models are not so easily understood. In the 1990s, reporting on top of enterprise relational databases required the building of complex and specialized interface layers, before report developers could even begin their work.  I’m sure everyone remembers Business Objects and Universes!

Of course, much highly useful BI is produced by someone concentrating on a small, specific subject area and generating reports for that specific audience. But that’s not enterprise BI!   In our view, enterprise BI requires enterprise data modelling; and the entity-relationship approach does not cut the mustard.  A powerful dimensional modelling toolset is required, instead.

Microsoft SQL Server Analysis Services (SSAS) Multidimensional is just such a power tool.  Not only does it provide all the modelling capabilities needed, translation into a physical data warehouse can be as simple as issuing a single command — Deploy.  We build a single enterprise model, and automatically generate a single physically implemented SSAS Multidimensional Cube, just like it says on the tin.  It is blindingly fast.  We tell our clients that if it ever takes longer than one second to bring back the answer, then something’s broken.  What’s not to like?

One thing that’s not to like is the fact that Microsoft haven’t made any serious upgrades to SSAS Multidimensional since 2010. All their efforts in the past six years have gone towards something called Analysis Services Tabular, which is dependent on building tables to be held in gigantic amounts of memory. It is simple to build simple solutions, it can be just as fast as a Multidimensional Cube, and delivery can be very quick.  It works in the cloud. Some folks have reported real success. It drives Power BI as a reporting toolkit. So why not ditch Multidimensional and go to Tabular?

Redwing’s view is this: it’s really hard – and a ton of work - to build an enterprise BI solution.   (Incidentally, it is also a serious long-term commitment by the organisation.) We’ve developed giant relational data warehouses (back in our Oracle days) and we’ve delivered Microsoft Tabular models . . . but they never grew to enterprise level very well. Indeed, we’ve seen a number of failures, and the cause was attempting to create one source of the truth, using a relational model. This approach does not lend itself at all well to building and maintaining the huge model representing the enterprise data warehouse. Take a look at the state today of most enterprise-level Business Objects Universes, if you need proof.

Unfortunately, it seems that Microsoft’s directions are oriented completely away from Multidimensional (which at one time was on Azure, but is no longer) and towards Tabular, in the shape of Power BI.  In Redwing's view, there’s a big difference between Power BI for the Enterprise and Enterprise BI. Personally, I like Power BI. We think Power BI should spread throughout the enterprise as a personal toolset for point solutions . . . but Power BI is just not right for enterprise BI. And neither is Tabular.

Next week, we will take a look at Microsoft's published roadmap for business intelligence, and do a little reading between the lines.