With the recent changes to Power BI Microsoft has unleashed the most exciting capabilities in the Microsoft BI space in a very long time. Power BI does not only have the ability to promote self-service BI, it has far greater implications for how we deliver BI projects in an agile manner.

Power BI allows us to work with data in new ways, enabling not only new architectures for our analytics solutions but also an entirely more business centric development process. This new development process help us break away from the traditional Enterprise Data Warehouse (EDW) paradigm, allowing us to truly focus our efforts on the insight and analysis that BI projects always promised yet so rarely delivered in the past.

The status quo

If you have been involved in delivering BI or analytics projects in the past 10 years then you will be very familiar with the EDW as the corner stone of such projects. You might have called this central repository of data by another names such as “Data Mart”, “OLAP Database” or “Data Store”. While each of those have a specific connotation and database structure implied they are all in effect a centralised database that is used to integrate data from many different sources into a format that is more suitable for reporting.

We used ETL tools to load all of the data from our source systems into the EDW. The ETL development in combination with the design of the EDW took the bulk of the time on a BI project. Sometimes being as much as 70% of the work effort.

In addition to our EDW we built OLAP cubes that allowed users to analyse the data stored in the EDW in a drag and drop pivot table analysis style. OLAP cubes gave us tremendous improvements in query performance, especially with regards to queries at aggregate levels. We built dashboards and reports against the OLAP cubes and the EDW to allow users to have structured reporting as not all users wanted to connect to our cubes directly.

The biggest challenge with this approach has always been that it was very much controlled by the IT function inside an organisation. The business users were part of the workshops, design and the eventual testing of the solution but the development of the solution proceeded with the normal rigour of development methodologies. This led to a development process that predominantly looks as follows:

Status Quo

There are many variations on the above process which introduce different development methodologies to try and speed up the delivery of the solution. Unfortunately if you have been doing BI projects for long enough you will know that making changes to the EDW layer of your solution will in almost all cases have a ripple effect through each of the other layers and these changes take time to make and test. The biggest challenge comes when the business needs change rapidly and you end up delivering a project right at the point where the business has moved on and thus have new requirements.

There are many books written on the subject of agile development for Data Warehouses and even more books and white papers written on why BI projects fail. One of the core reasons is the Business not being intimately involved in the entire process. Given the above process and normal corporate IT restrictions we inevitably always end up with a suboptimal delivery process. Luckily with Power BI we have a chance at breaking out of this mould and moving to a new way of delivering our solutions.

Power BI business centric delivery

With the Power BI suite of tools we have the ability to break away from needing to construct our EDW before we can embark on building models to support our reporting. The tools are exceptionally business friendly with most of them either being part of Excel or being available as a separate free application called Power BI Designer.

Tools such as Power Query has tremendous power to connect to many different data sources and perform complex data transformations in an extremely easy manner. It is the “Swiss army knife” ETL tool that most developers have only ever dreamed of in the past. (It is able to connect to many of the new data sources that we are still lacking native adapters for in SSIS today.) The datasets or queries that are built with Power Query can be used as the source for Power Pivot models. In addition to the modelling and ETL goodness of Power BI we have Power Pivot available for data exploration and reporting with full support for iOS devices (yes there is an app for that) and HTML5 to surface your dashboards to a wide variety of devices.

All of these tools and capabilities allow us to create the perfect business centric development process for BI and analytics projects.

New approach

I am sure that the above diagram will step on a few toes as it reduces the involvement of the IT department dramatically during the initial stages of a project. It allows for organic development of models and solutions that the business believe it requires. Many of these solutions might only be in existence for a short while before being superseded by something new or before the business realises that changes are needed to fulfil some business requirements that they did not know about at the outset of the project.

While this organic development process takes place within the business the IT department is kept out of what would normally have been many hours of wasted effort responding to change requests and design changes. The IT department can focus on supporting those systems that are in production and which form the core corporate data solution.

Once the business has passed a predetermined threshold showing that the solution they have developed does truly meet their needs they can hand it over to the IT department to “productionise” the solution. The process of promoting a solution into a managed production environment will be more involved than the one box on the diagram alludes to. It will introduce a process by which the IT department can promote Power Pivot models to Analysis Services Tabular models, change the ETL for certain datasets to better performing routines and put the necessary governance and control in place to support the solutions as part of the core data solution of the organisation.

What this process achieves is playing to the strengths of both business and the IT function. Instead of leading down a path, that has historically caused friction between the different parties involved, it instead allows the opportunity to service fast changing business requirements as well as implementing the much needed IT governance required to successfully maintain and support organisational data assets.

The ideal development team

Given the difference between the IT centric and Business centric development models it should not come as a surprise that there will also be a difference between the skills needed in the teams.

Traditionally BI development teams were made up of members with specialists in Data Warehouse Design, ETL development, OLAP Cube development and Report development. These teams favoured specialisation in each of the mentioned disciplines but did allow for some overlap to make teams more self sufficient. These teams could be relatively large and would include additional people to take control of Business Analysis and Project Management.

The new Business centric development team should ideally be a small team that consists of no more than 3 team members. Each team member should be cross-skilled in the different BI disciples such as Data Modelling, ETL, Reporting and Advanced Analytics. The team should have a very good grasp of the business domain or alternatively one of the team members should be an expert in the particular business area. A team such as this would be able to turn around solutions for the business much faster than previously possible. They will also have all the required knowledge to ensure that the hand over to the IT department goes smoothly thus allowing for a seamless transition from business to IT.

Some final thoughts

The aim of this post has been to highlight one way in which Power BI allows us to make fundamental changes to the way we deliver BI solutions and thus has been at quite a high level. In future posts I will explore the Power BI tools in more detail, specifically in light of how they can be used to usher in better processes and faster delivery of solutions. There are many more features of Power BI which can truly revolutionise the way we give people access to data, the Corporate Data Catalog functionality is one such feature that comes to mind.

If you would like to have an in depth overview of Power BI you can have a look at the session delivered on Microsoft Virtual Academy.

The greatest missed opportunity will be to not take advantage of the new ways in which Power BI enables faster delivery of BI and Analytic solutions.

This article has 7 comments

  1. Shaun

    Nice post. Seems like a better approach than to commence building a behemoth star schema based entirely on source data trying to fit the world into a warehouse that never gets finished and takes too long to iterate. There is always the proverbial chicken and egg problem. To think you can build a good fit for purpose and cost effective warehouse in sensible time frame without knowing what information, ad-hoc BI or reports are required is a bit bonkers. Tools to iterate the BI stack quickly focusing on the output are all good and at the end of the day have to deliver business benefit. There’s some cool stuff out on the market to speed this up not just power BI. BIML is pretty good and worth looking at. There’s huge opportunity to fill the agile BI gap, I’ve seen a lot of projects get canned when the estimate comes through for putting in a new source system just because the estimate is over compensated into a waterfall approach. It’s better to manage risk using a better process rather than just adding a massive mitigation cost into the estimate.

    1. Christo Olivier

      Thank you very much Shaun. I could not agree with you more. I have not looked at BIML in a very long time so will definitely give it another look. I feel that, like you said, too many projects try to compensate for bad process with contingency in the estimates. It seems that few people are motivated to solve the root cause of their issues, the majority would rather try and alleviate the symptoms instead hence the mess most projects end up finding themselves in.

  2. Simon

    Hey Christo.  Nice post, you pretty much describe what we did with a certain restaurant chain from your homeland and I’ve been working on this type of method since with excellent results.

    Power BI designer is still in preview and currently has many limitations, the biggest (for me anyway) is you can’t write DAX in it yet (MS tell us it’s coming soon).  The Analysis Services Connector is also something for you to have a look at along with Power BI APIs giving your architecture options some real bite.

    As Shaun said BIML kicks some serious booty for metadata driven ETL (do you remember the demo I gave you around a metadata SSIS solution I had done?  Well BIML blows that out of the water), I’m a BIML community partner here in NZ and would be happy to hook up with you on a VC to give you a quick demo and share some of my experiences with BIML and Power BI.

    Cheers SP

    1. Christo Olivier

      Hi Simon

      Thank you very much for the feedback it is greatly appreciated. It is great to hear that you have been implementing solutions using this approach. I remember the demo you gave around metadata SSIS and it is great to hear that BIML is even more powerful. Will be great to get in touch around BIML and Power BI.

      Hope all is going exceptionally well on your side!


  3. Reuben Anderson

    Errmm.. I tihnk this is lovely but wishful thinking. In reality I would expect the business to struggle along repeatedly tuning requirements and models in a highly bespoke and inconsistent manner… before delivering a ‘solution’ to IT that has to be unpicked and rebuilt from scratch into something that is productionisable and compliant with the enterprise architecture. Yes, IT needs more agility, and yes the Business needs better tools… but in reality the value is slap bang in the middle of the paradoxical space in between. I would tune your model thus; i) the Business solution development side needs to be facilitated and managed by IT to retain it’s focus toward an agreed set of outcomes, ii) the “Promote solution into IT..” box needs to be more realistically opened out.

    1. Christo Olivier

      Hi Reuben

      I agree that this is the ideal scenario being shown here. I do not see the small teams working in the business as purely business people, they would be a kind of “hybrid” team that consists of some IT people that do have the knowledge to keep an eye out for developing a solution that could be more easily pushed into production.

      The value would most definitely be found in the space in between where IT and business work together to achieve the desired end goals. Unfortunately that is the message preached for the last decade without it really having worked at all with the old BI tools that we have been using. I believe with tools like Power BI we are going to be able to bridge the gap much better to get us to the ideal we aim for.

Comments are closed.