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:
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.
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.