The goal of Self-Service Business Intelligence (BI) is for Business Users to answer their own questions without having to rely on the IT department.  Most organizations have an active self-service BI initiative which usually starts with the selection of a Self-Service BI platform.  The hope is that all Business Users can create their own charts, reports and analytics from “semantic” models developed by IT.

The reality is that this is rarely the case. One approach is for the IT Department to create a dashboard that can be navigated by the majority of Business Users. This works for some Business Users. Others, many times executives, rely on Power Users to create, construct and then distribute the results of their analysis.  Power Users that build report books and are either technically savvy Business Analysts or in the IT Development Group. 

Power Users often create Report Books as their primary output. Report books tell the complete story, i.e. they combine reports and commentary into one document which is then distributed to the business community. This document is typically distributed as a PDF or Microsoft Office Document (Word or PowerPoint) format.

Backlogs often occur when an IT Developer is the resource responsible for creating and maintaining the report book. This historically has resulted in the business choosing to create their own solution. In this scenario a Business Analyst uses Excel and “spread-marts” to create their report book and other deliverables to Business Users. The downside of “spread-marts” is that Excel is not a database, does not live in a controlled environment, and the process of loading Excel with data results in multiple versions of the truth and questionable data quality.

Microsoft’s introduction of PowerPivot has provided a wonderful alternative to “spread-marts”. PowerPivot is a powerful in-memory “semantic layer” that is tightly integrated with Excel. The benefits to this approach is:

  •  PowerPivot provides one version of quality data and can be uploaded to SharePoint
  •  Business Analysts use Office (Word, Excel and PowerPoint) to create the report book
  •  Eliminating IT reports, developers significantly reduce development and ongoing costs

The remainder of this post first reviews the process of answering business questions; provides an overview of self-service BI; introduces the “spread-mart” issue; provides an overview of semantic layers; shows how PowerPivot solves this issue and how Word and Excel combine seamlessly to produce active report books.

A following post will dive deeper into the process and mechanics of building a Report book with Office and PowerPivot.

The need for Self-service BI

The need for self-service BI has arisen from the traditional approach for answering business questions illustrated below in figure 1.

Figure 1 – Existing Process

Answering business questions:

  • The Business User provides a business question or questions to the Business Analyst.
  • An IT – Developer is then responsible for providing the data sets to the Business Analyst.
  • The Business Analyst analyzes the data set and returns the answer to the Business User

Many times both the Business Analysts and IT – Developers have a large backlog of requests, the longer the backlog the longer the time it takes to provide an answer to the business.

This following are common issues with this approach:

  • The Business Analyst develops a back log due to life cycle of scoping, creating requirements, post-processing and final format and delivery
  • The IT – Developer develops a back log due to their life cycle with includes, cleansing data, creating a data mart, adding calculations and building reports.
  • The data required for the result data set is not available or is available but not rationalized.

Many times the IT Developer backlog creates tension between the business and IT. This, in many cases, causes the Business Analyst to bypass IT. In these cases, Excel is typically used to consolidate and aggregate the source data as illustrated in figure 2.

Figure 2 – Bypassing IT developers

The steps in this process typically follow this pattern:

  • The Business User provides a business question or questions to the Business Analyst
  • The Business Analyst imports the data source or sources directly into an Excel spreadsheet
  • The Business Analyst analyzes the data set and returns the answer to the Business User

Business questions are answered faster, however, the disadvantages include:

  • Inaccurate results – The complex process of cleansing, matching, consolidating and aggregating data is left in the hands of the Business Analyst. Many times the results reflect this.
  • Multiple versions of the truth – There’s an explosion of Excel spreadsheets, each containing different data producing different results.
  • Ongoing costs and maintenance – Changes to source system formats break the Excel source access. Each spreadsheet must account for data quality issues.
  • Business Analysts still have a backlog. When this occurs, Business Users still do not get timely answers to their questions.

This scenario is seen in many organizations today. This proliferation of Excel spreadsheets or “spread-marts” is a reality in today’s organizations. This can become a nightmare for IT organizations to maintain over time. In response to this, many IT organizations have embarked on a “Self-Service” BI initiative. The objective is to provide Business Users with an easy to use set of tools that allow the end Business User to answer their own business questions…

Self-Service BI

Today’s Self-Service BI solutions are typically implemented in the following ways.

Figure 3 – Self Service BI

This solution is typically:

  • Implemented as a Site accessible by Web browsers
  • With a Home page containing Dashboard(s) and links
  • That supports the filtering of results, e.g. select a date range, and a subset of business units, product categories…
  • Users can “drill-through” to obtain more detail
  • Supports saving to various formats, e.g. PDF, PPT, Excel…

This can work when the requirements have been well-defined and an ample amount of budget is available. However, in the majority of cases there are still many business executives who choose to have a Business Analyst create and deliver a tailored document, a.k.a. a report book. Figure 4 shows an example of this scenario.

Figure 4 – Self-Service BI reality

The bottom line is that Business Analysts are the resources that most often access a Self-Service BI environment. One side effect of this is that many times data sets are exported from the Self-Service BI environment into Excel because of the familiarly Business Analysts have with Excel. This leads to an ironic situation, i.e. organizations still have the “spread mart” issues even with a Self-Service BI solution in place.

To be clear, it’s ok to have multiple versions of Pivot Tables and Pivot Charts as long as they’re accessing the same “semantic” layer.  It’s not ok to have multiple versions of the data feeding the Pivot Tables and Pivot Charts as Figure 5 illustrates.

Figure 5 – Excel Self-service BI

What is a semantic layer

A semantic layer sits between data and presentation as shown in figure 6.

Figure 6 – Semantic layer

Semantic layers are typically implemented on top of a star or snowflake data model and augment this model with friendly names, calculations, measures, trend analysis and other items which provide a “business view” of the data. Examples of semantic layers include a Business Objects Universe and an Analysis Services cube. Semantic layers typically require a BI savvy IT resource which in many cases are always in demand.

The good news – Excel now has a semantic layer

The good news is that Microsoft Office, i.e. Excel, now has a semantic layer, i.e. PowerPivot. Figure 7 illustrates how Excel and PowerPivot work seamlessly to allow a Business Analyst to create charts, reports and analysis.  These objects can then be exported and delivered to Business Users.

Figure 7 – Excel and PowerPivot

PowerPivot is an in-memory multi-dimensional database engine that can either exist within the spreadsheet or be hosted within a SharePoint environment. Encapsulating all data, data relationships and calculated columns within PowerPivot provides one version of the data. PowerPivot models are currently developed by IT, however the seamless integration of Office and PowerPivot hold the promise for BI savvy Business Analysts to grow into this role.

The additional benefits of Microsoft Office

This story is enhanced by including the complete Microsoft Office stack including SharePoint. This now becomes an ideal platform for creating and distributing report books as illustrated in Figure 8.

Figure 8 – Microsoft Office Self Service BI

The Business Analyst now has a complete set of tools to first create the charts, reports and analytics; next to include active links to these charts and reports within a Word “report book”; then to publish to SharePoint for easy access or email to the business community.

The Pivotal role of BI Analyst

Now the Business Analyst has a platform to create Self-Service BI solutions. The final step is for technically savvy Business Analysts to become proficient in PowerPivot. Business Analysts now become BI Analysts and the role of IT is reduced to assisting the BI Analyst with accessing and understanding source data.

Report Books – Customer Experience

BlueMetal Architects (BMA) has successfully implemented multiple Report books for our client base, typically one of two scenarios. Scenario one is an “Enterprise” solution that uses SharePoint, SQL Server Reporting Services and Analysis Services to create dashboards, reports, graphs, dials and gages for access by 1000’s of users.

Scenario two is more of a Department level solution which leverages SharePoint, Office 2010 and PowerPivot to create Word report books which contain commentary interspersed with charts, graphs and reports.

Both are valid scenarios; Scenario one is required when the scope is at the Enterprise level, with 1000’s of users and large data volumes. Scenario two is appropriate when the organizational scope is smaller, the data volumes are smaller and most importantly the budgets are limited. In one example, BMA produced a Report book at one third the cost and as importantly transitioned the ongoing maintenance of this to a Business Analyst.


In summary, the need for Self-Service BI has resulted from the need to quickly answer business questions. The promise of Self-Service BI is to enable Business Users to answer their own business questions directly without assistance from IT or from any other resource. The reality is that many Business Users, many times influential executives rely on Business Analysts for their BI needs.

The introduction of PowerPivot has provided Business Analysts with a powerful in-memory database which is seamlessly integrated into Microsoft Office. This seamless integration now allows Business Analysts to create Office and Excel centric solutions without the proliferation of spread-marts. In addition, seamless integration with SharePoint allows for the creation of “shared” solutions.

BMA is excited about these new capabilities and has successfully implemented them within our client base. Look to our website for the next blog post on this topic which will provide step-by-step instructions on how to create Report books using Office and PowerPivot.