Entries in Self-Service BI (6)


Overview of Microsoft Capabilities for Self-Service BI Users

From time to time I'll be contributing to the BlueGranite blog which has lots of great BI information published by my coworkers.

This week I published a blog entry titled "Overview of Microsoft Capabilities for Self-Service BI Users" on the BlueGranite blog.  It provides a quick peek into which tools, features, and capabilities are likely to be used for what purpose and which type of power user the tool may appeal to.  Please check it out!



The Role of Power Users in a Self-Service BI Initiative

From time to time I'll be contributing to the BlueGranite blog which has lots of great BI information published by my coworkers.

This week I published a blog entry titled "The Role of Power Users in a Self-Service BI Initiative" on the BlueGranite blog.  It provides a brief overview of Self-Service BI and discusses how business users possess varying needs, abilities, and preferences for working with self-service BI tools and techniques.  Please check it out!



Why “Power BI” is a Big Deal

A preview of Power BI will be available later this summer.  Here’s a recap of what I’ve learned so far + why I think this is a very big deal for the world of Microsoft Business Intelligence.

Excel is Primary Tool for Self-Service BI

There are four components to Power BI, and all are delivered via Excel.  Microsoft is capitalizing on the fact that a huge percentage of business users are most comfortable in an Excel environment.  Historically a large portion of Microsoft BI was developed with SQL Server and SharePoint tools – i.e., software predominantly available to IT and BI professionals.

The newest Excel capabilities lower the entry point of BI to anyone who has Excel and an interest in working with data.  SQL Server and SharePoint will continue to be my professional-level tools of choice for creating and delivering Corporate BI solutions.  Meanwhile Excel has emerged as the primary mechanism for creation and delivery of Self-Service BI solutions.  This continues to build upon the Personal BI > Team BI > Corporate BI framework promoted by Microsoft using a combination of Microsoft Office that’s installed on the desktop, Office 365 in the cloud, SQL Server and SharePoint.

Four Components to Power BI

Two of the Power BI components are for data preparation:  Power Pivot and Power Query.  The other two are for data visualization:  Power View and Power Map. 

  • Power Pivot.  Imports and integrates data from various sources for the purpose of creating an in-memory data model.  This allows a functional user to combine various data sources which improves its value – classic examples here are the addition of weather data or demographics data to corporate sales data.  The Power Pivot data model also supports calculations, derived fields, aggregates, hierarchies and key performance indicators.  The ability to create a data model with a relatively low learning curve is a very big deal for data analysts, particularly for one-time analysis type of situations.  It can also be helpful to IT and BI folks for prototyping.
  • Power Query.  Formerly Codename Data Explorer, this tool will be able to search for data (just like a web search experience) online or within your corporate data sources.  The selected data set is immediately imported into an Excel table – this search feature alone is a game-changer.  Some of the native data connectivity it offers are truly distinctive and make data access far easier than it’s been in the past.  It also allows you to do things like merge data, rename columns, replace values and other data modifications in a step-by-step process.  A very big deal here is that the step-by-step data cleansing and shaping process is savable and therefore repeatable.  You can also view the state of the data before and after each step.  Optionally, you can bring the data into your Power Pivot model to continue working with the data even further.
  • Power View.  Power View is an interactive data visualization tool.  It does certain things like highlighting and cross-filtering really well with very little learning curve.  Working with data in Power View bears similarities to working with Excel Pivot Tables as well as to PowerPoint.  In addition to charts, graphs, and table data, Power View also supports maps which pan and zoom via integration with Bing Maps.  The naturally interactive behavior of Power View is what makes it a very big deal.
  • Power Map.  Formerly Codename GeoFlow, Power Map is a 3D mapping tool.  What makes it unique is the ability to save scenes and play them over time in a tour.

Cloud-Based Self-Service BI

Power BI will be delivered in Office 365 (which actually uses SharePoint Online).  Although the preview that starts later this summer will use Office 365, all 4 Power BI tools are available to use as Excel add-ins now (some functionality is not fully supported within on-premises SharePoint).  Considering Microsoft has a cloud-first strategy, it’s not a big surprise that the initial preview will be with Office 365.  It’s unclear currently if all Power BI functionality will be available to on-premises SharePoint sites following the initial cloud-based rollout.  I hope so.

This focus on the cloud is a big deal, especially for small and medium-sized businesses.  It can also be a good thing for business units that need self-service capabilities but don’t have IT support or resources to fire up the required infrastructure.  Even large corporate environments can find some benefits to augmenting their on-premises BI environment with the cloud – especially if they want to upgrade to newer versions in the cloud faster than they can upgrade on-premise versions (i.e., a company-wide upgrade to Office 2013 can be a big task).  Creating the SharePoint and SQL Server server infrastructure to support Excel Services, PowerPivot for SharePoint, Reporting Services, and Analysis Services is not an easy task.  Not having to invest so much time, effort, and cost into setting up this infrastructure will be welcomed by many organizations.  However, there are certainly some obstacles which prevent some companies from using Excel in Office 365 even if they wanted to.  It will be interesting to see to what extent companies embrace the cloud, or utilize a hybrid approach to on-premise and cloud BI.

Mobile Support

Quite obviously mobile support is a big deal … Microsoft has announced native mobile applications for Windows 8, Windows RT, and the iPad.  HTML5 will be used for browser-based mobile delivery on other types of devices.  The way I understand it, to see a report on a mobile device it’ll need to be published to a Power BI workspace in O365, and in the properties of the report you’d set it as a “Mobile Favorite.”  Translation:  companies with Excel and Power View in SharePoint now won’t get to take advantage of this mobile functionality right away.


     Source for Image:  Power BI Demo by Amir Netz available here:

My understanding is Silverlight won’t be immediately removed from the stand-alone Power View application in or the SharePoint PowerPivot Gallery, but I’m guessing that’ll come in time.

Data Search and Refresh

Office 365 will utilize collaborative BI workspaces optimized for BI functionality.  Users will be able to publish Excel workbooks to workspaces and securely share them with coworkers.  Reusable data connections can be published, and data refreshes can be handled on-demand or on a schedule.  Data refreshes can access corporate data on-premises as well as data online (with proper security credentials of course).  Being able to search and access your enterprise data is a very big deal (this sounds like something the IT folks will need to facilitate for self-service users to take advantage of).

Queries created from Power Query will be able to be searched and shared among users.  This sharing of queries is a big deal – anything that promotes reusability & consistency gets a big thumbs-up from me.


     Source for Both Images:  Power BI Demo by Amir Netz available here:

Tracking of Data Usage

A new feature I’m particularly excited about is the tracking of data usage.  Stats re: how often data sources & reports are accessed will be readily accessible to those who build models and reports.  This is currently a feature of Central Administration in SharePoint, or by querying the Power Pivot Management Data tabular model if you don’t want to grant Central Admin access to a non-SharePoint administrator.  I look forward to seeing how easily this information can be reviewed for one report, one data source, or for many reports and data sources.

Easy access to these stats is a big deal because if you see usage going way up, then there’s an awareness that this particular report or data source has become critical.  This might mean a data refresh failure is of greater urgency, or data quality is of even greater concern.  Let’s say it’s a Power Pivot model that’s become critical and it continues to grow larger and more complex – that might be a perfect scenario to upgrade the self-service Power Pivot model to a corporate Tabular model in Analysis Services.

Data Stewardship

Lastly, a big component of Power BI is the concept of data stewardship.  There’s not a lot of public information about this available yet.  This will include management of security, data refresh permissions, tracking data usage, as well as a few other things that we’ll learn more about when the preview is available.  I’ve typically thought of data stewardship with the mindset of data quality and/or master data management, so I’m extremely interested in seeing the direction Microsoft will take with this.  Regardless of how you define it, data stewardship is the kind of thing that can really make a self-service BI initiative more successful and/or less problematic – so I think this concept of data stewardship will end up being a very big deal.

Finding More Information

Register for the “Microsoft Power BI for Office 365” preview – Office Site

Chris Webb’s Blog – Some Thoughts About Power BI

Jen Underwood’s Blog – Microsoft Office 365 Cloud Power BI

Jason Thomas’ Blog – Power BI for Office 365

Jamie Thomson’s Blog – Thoughts on Power BI for Office 365

John White’s Blog – Power BI-What Is It?



Report Builder … the Red-Headed Stepchild of Self-Service BI Tools?

A conversation on Twitter earlier this week has inspired me to pose this question…Is Report Builder the “Red-Headed Stepchild” when it comes to the Microsoft Self-Service BI toolset?  Report Builder doesn’t get much attention these days, for various reasons we’ll discuss below.

(Note:  The perspective of this blog entry is Self-Service BI so Report Builder is the reference – however, everything stated is applicable to full-fledged Reporting Services as well.)

Is Report Builder Deprecated?

Nope!  Report Models, however, are deprecated.  As of SQL Server 2012, you can no longer create or update Report Models (SMDL files).  You can continue to use Report Models currently, but it is recommended to move away from them as time allows.  Report Builder itself is at version 3.0 and is a mature product.

Self-Service BI Tools

The primary set of Microsoft Self-Service BI tools includes Excel (+ add-ins for data modeling such as PowerPivot), Power View, and Report Builder.  Some people actually wouldn’t include Report Builder in this list, but I do believe it has a valid place as one of the Self-Service BI tools (albeit, a smaller audience & used for specific purposes).

Drawbacks of Report Builder

  • Learning curve / ease of use for report designer.  There are a significant number of properties and options.  This offers significant control over the output – the cost for this significant control is ease of use because all the options in Report Builder can be a bit overwhelming for the casual business user.  However, it’s not overly difficult to use for technically adept users who enjoy working with reporting tools and data.
  • Limited interactivity.  While there are some interactive features (such as drill-down, drill-through, sortable columns), each has to be explicitly defined by the report designer.  Report Builder isn’t dynamically interactive like Power View or even Excel – rather, Report Builder is far more suitable for fully formatted reporting needs.
  • Longer to develop.  There’s some things that can be done with Power View or Excel that are inherently more work to do in Report Builder.  The first example that comes to mind is hierarchies – with Power View or Excel, you drop a hierarchy onto the row & the navigation up/down works; with Report Builder you’d have to set up what is shown vs. hidden and the toggle properties.  This is not overly difficult to set up, but could be frustrating for someone just getting started with Report Builder.
  • GUI support is limited.  There is a drag & drop graphical interface for SQL Server and Analysis Services data sources (plus a couple of others).  This limited support leaves the report designer writing query syntax sometimes – which is obviously not the most user-friendly for non-technical folks.  Currently the nicest way for users to work with Report Builder is using a BISM data source (i.e., the data is stored in Analysis Services or PowerPivot).  SQL Server (relational) can be ok for users to work with if the data sources are made to be understandable & easy to work with (ex: with reporting views or stored procedures) – this takes some effort from the IT Dept. or BI Center to make sure it’s made suitable for self-service.

Positives of Report Builder

  • Native connectivity to many data sources, including BISM.  The Microsoft BI framework is very different from other BI tools (such as Cognos or Business Objects) which require a metadata layer – i.e., a report model between the data source & the reporting tool.  Microsoft permits tremendous flexibility here – in fact, you can natively send queries from Report Builder to a variety of databases including non-Microsoft.  Power View is very limited in terms of accepted data sources, and Excel can be somewhat limited (unless you bring the data into an intermediary PowerPivot model first – PowerPivot offers great flexibility in this regard, but do realize you are storing the data redundantly). 
  • Significant formatting control.  If you need a pixel-perfect highly formatted report, Report Builder is the tool for you.  Ironically, this is the inverse of the “learning curve / ease of use” drawback listed above.  With Excel you can exercise a lot of control over the look & feel of your report (except Pivot Tables – you have to use formulas if you need to "break” out of the Pivot Table).  Power View has some text size control and some color schemes to choose from, but overall offers minimal user control over formatting (after all, it’s a data discovery tool meeting an entirely different need – and it is purposely trying to remain simple).
  • Consistent RDL file format.  If a business user starts a report in Report Builder and needs some help with it, one of the IT or BI folks can open the report using their tool of choice (BIDS or SSDT in Visual Studio), make some updates, and send it back to the user with the file format intact.  The consistent format is also helpful if a report is being promoted from the Self-Service environment to the Corporate BI environment.
  • Reusable elements.  To facilitate Self-Service BI using Report Builder, things such as shared data sources (reusable data connections) are obvious but there are others as well.  Shared datasets (reusable queries) can be really helpful to handle common things like Dates and Geography.  Report parts (reusable charts, graphs, tables) can be helpful to display commonly used elements.  Images can also be stored centrally for reuse.  Setting up reusable elements does take some effort from the IT Dept. or BI Center though, but can improve the Self-Service user’s experience tremendously.
  • Parameterization.  Reports with a number of parameters (within reason of course) can sometimes be thought of as “guided ad-hoc analysis” because one report can yield many different combinations of results depending on parameter values.  Report Builder handles parameterization well.
  • Subscriptions and alerts.  If you wish to have reports delivered to you at a predefined frequency or based upon a condition, then Reporting Services is the tool to make that happen.
  • Export and RSS capabilities.  Report Builder can export to many different file formats.  It can also publish an RSS feed – this can be very useful for a business user to consume existing aggregated/calculated data that has been rendered by Report Builder without recalculating or reinventing anything.  Utilizing published report data via RSS also helps with the elusive “one version of the truth” that’s a constant challenge.
  • Integration with SharePoint.  With a Report Builder report, you can view or edit the report directly from the SharePoint document library (with appropriate permissions of course).  Alternatively, a Report Manager portal can be used (although it would be used in isolation from other BI tools and reports).

So, even though sometimes Report Builder seems to be the “Red-Headed Stepchild” I very much appreciate having the tool in our toolbox.  I hope it’s alive and well for a long time. 

Got any thoughts on this subject?  Leave a comment … I’d love to hear your thoughts.



Corporate BI and Self-Service BI - One Size Does Not Fit All

Data.  Analytics.  Business Intelligence.  Every company, large or small, needs it.  The depth of analytics required will vary wildly from company to company though.  As an organization grows in size, we start differentiating its needs for Corporate BI and Self-Service BI.  Does a mid to large size company need both?  Probably.  Are Corporate BI and Self-Service BI somewhat at odds with each other?  Sometimes, but not always.

The following charts depict one way of viewing levels of Business Intelligence within an organization.




Where the organization wants to plot itself on the above axes depends on many things. The top right is not necessarily the “ideal” or “best” for every company. The bottom left is not always “bad.” Ultimately, the desired level of maturity depends on what the company needs and wants in terms of analytic capabilities. It also depends on the individual business units.  Perhaps the Purchasing Department gets by quite nicely with Corporate BI capabilities; however, the Competitive Pricing Department may have significant need for advanced Self-Service BI tools and techniques.

This is one situation where Corporate BI and Self-Service BI can co-exist quite nicely within an individual organization.  Different business units have unique and evolving needs; therefore, each unit’s level of BI capabilities can and should adapt accordingly.  Put another way: one size doesn’t fit all.

If you like this topic, then be sure to attend the April meeting of the Charlotte BI Group so we can continue the conversation.  Javier Guillén (Blog | LinkedIn) and I will be facilitating a roundtable conversation titled “Corporate BI + Self-Service BI:  Friends or Foes?”  Hint: Javier doesn't always share the same viewpoint I have, so this will be fun!  Please join us if you can - we would love to hear your thoughts on this subject.



New Auditing and Compliance Features for Excel and Access

Office 2013 introduces some great new features to help audit and manage Excel workbooks and Access databases – sometimes referred to as EUCs or End User Computing applications. 

Although I’m a huge proponent of enabling knowledge workers with Self-Service BI capabilities, I’m equally a proponent of IT assuming responsibility for managing and securing the environments.  These features, an evolution from the Prodiance Corp. acquisition, are a welcome addition for both end users and IT to manage Excel and Access.  And as a bonus, your auditors will love it!

Office 2013 desktop desktop applications   <—Focus:  1-2 files.  Target audience: Anyone with Excel

  • Inquire
  • Spreadsheet Compare
  • Database Compare

Office 2013 server applications    <—Focus:  many files.  Target audience: IT system admins, auditors, or anyone who wants to monitor changes to a set of files

  • Microsoft Office Audit and Control Management Server 2013
  • Microsoft Discovery and Risk Assessment

Following is a brief review of the capabilities of each item.


Applicable to:  Excel 2013 (Office Professional Plus)

Accessed via:  Inquire Ribbon Menu inside of Excel



  • Workbook Analysis – Report which contains metadata about a workbook such as formulas, cells, ranges, data connections, and links.  Useful for documentation and to aid in understanding a workbook.
  • Workbook Relationship – Diagram which maps out the links to other workbooks & data sources.  Helpful to analyze dependencies, lineage, and the potential effect of changes.
  • Cell Relationship – Diagram which maps out links and formulas between cells within the same workbook or other workbooks.
  • Compare Files – Compares two workbooks currently open; highlights the differences cell by cell. The differences are color-coded and categorized so more critical changes, such as a change to a entered values or calculated values, is separate from an immaterial change like a change to a cell format.  This is actually the same functionality as the Spreadsheet Compare tool, discussed in the next section; this feature is just available within Excel as a convenience.
  • Clean Excess Cell Formatting – Eliminates unneeded formatting to reduce the size of the file and/or improve performance.
  • Workbook Passwords – A Password Manager which stores passwords for Inquire so it can open and perform analysis on password-protected workbook(s).

More info: What You Can Do With Spreadsheet Inquire

Spreadsheet Compare

Applicable to: Excel 2013 (Office Professional Plus)

Accessed via:  Start > All Programs > Microsoft Office 2013 > Office 2013 Tools > Spreadsheet Compare 2013

Capabilities:  Compares two workbooks currently open; highlights the differences cell by cell.  The differences are color-coded and categorized so more critical changes, such as a change to a entered values or calculated values, is separate from an immaterial change like a change to a cell format.

More info:  Basic Tasks in Spreadsheet Compare

Database Compare

Applicable to: Access 2013 (Office Professional Plus)

Accessed via:  Start > All Programs > Microsoft Office 2013 > Office 2013 Tools > Database Compare 2013

Capabilities:  Compares two Access databases and highlights the differences in a query, form, report, or code.  What it will not do is compare the difference in data stored within the tables (workaround: export the data to excel and use Spreadsheet Compare).

More info:  Basic Tasks in Database Compare

Microsoft Office Audit and Control Management Server 2013 (ACM 2013)


  • Logs and reports on changes made to critical spreadsheets.
  • Cell level auditing, versioning, and audit trails.
  • Change control and data integrity.
  • Segregation of duties.

Microsoft Discovery and Risk Assessment

This is actually part of the ACM 2013 suite of products (discussed in the previous section).  This product was formerly known as Prodiance eDiscovery.


  • Creates an inventory of Excel workbooks and Access databases within the file system and/or SharePoint document libraries.
  • Rates the level of complexity, materiality/impact, and risk to the organization.
  • Identifies errors in formulas.
  • Identifies broken links.
  • Creation of custom rules for compliance purposes.

More info: Use Microsoft Discovery and Risk Assessment

Finding More Information

Excel Blog – Introducing spreadsheet controls in Office 2013!

Access Blog – Feral Cats:  Managing Access databases in your organization