…brew your own Business Intelligence

Problems with the Self-Service Paradigm

There’s no denying the substantial benefits of self-service BI – arguably one of the most important paradigm shifts in the past 5 years. And I can’t help but give Microsoft the nod for a job well done on that front. The whole suite of “power tools” (e.g. Power Pivot, Power View, Power Query, Power Maps, Power BI) targeted at the self-service information worker is incredible and watching these, initially disparate, tools becoming more tightly integrated with each release has been enjoyable.

That said, there is still a very strong argument that I see being brought up time after time by businesses that are hesitant to jump on the self-service bandwagon. No, it’s not security – although that’s also a legitimate concern. What I’m talking about is control and the ability to audit the artifacts created by these self-service tools… spreadsheets, Power Pivot models, reports, etc.

Typical Self-Service Lifecycle

Self-service BI is about putting the power in the hands of the information workers – giving them the ability to mash up data from multiple sources and build BI solutions.  In the Microsoft ecosystem, information workers can now create a PowerPivot model that consumes data from the corporate DW, feeds from the Windows Azure Marketplace, and data from a Power Query. A visually appealing Power View report can then be dropped on top of this data model and everything can be uploaded to a SharePoint site and automatically refreshed on a recurring schedule so that the rest of the department can consume updated information on a daily basis. All of this without requiring intervention or assistance from IT!

awesome

But what does this environment start to look like after a few months, a year, 2 years? Without tight controls, it can start to get a bit messy. Hundreds of Power Pivot models all over the place with calculations that may or may not adhere to the corporate standards. Eventually you start to run into the same problems plaguing businesses prior to the rise of the enterprise data warehouse: spreadmarts and multiple versions of the truth.

Fortunately, Microsoft appears to be taking steps to address these issues. Back in 2011, they acquired an enterprise risk management company called Prodiance and have finally started to incorporate some of the acquired auditing and risk management technologies into the Microsoft Office ecosystem.

Microsoft’s Data Governance Stack for Office

Below is a diagram of the stack of tools Microsoft is releasing to facilitate data governance in the Microsoft Office ecosystem…

spreadsheet controls

As you can see there are server and desktop components to this stack.  Starting with the desktop tools, we see a few capabilities that have already started to be incorporated into Excel 2013 via an Add-In called Inquire which Devon and Paul have already blogged about below:

These are some interesting capabilities that will really help with detailed auditing of important excel files. However, the other risk with self-service is the “not knowing what is out there” …and that is where the server tools come into play. Being able to crawl the network and SharePoint farm for excel files that may contain sensitive information and collecting cell-level metadata is a very powerful feature that should hopefully start quell the fears of these companies hesitant to buy into the self-service paradigm.

Below is some summary information on both of the server-level tools, neither of which have been discussed much in the Microsoft BI blogsphere.

Office Audit and Control Management Server 2013

Microsoft Audit and Control Management Server 2013 automates internal control over business-critical spreadsheets and Access databases through system-wide monitoring and reporting of changes. Audit and Control Management Server automates key controls including the following: cell level auditing, version control, change control, security, data integrity, segregation of duties, and reporting.

If you have “mission-critical” Excel workbooks or Access databases in your organization, these spreadsheet and database administration tools can help you reduce the risk. Microsoft Audit and Control Management Server provides powerful change management features for Excel and Access files, and it is complemented by Microsoft Discovery and Risk Assessment Server 2013, which provides inventory and analysis features, designed to help you reduce the risk associated with using tools developed by end-users in Excel and Access.

Discovery and Risk Assessment Server

Microsoft Discovery and Risk Assessment Server 2013 is a server application that works together with Microsoft Audit and Control Management Server 2013. You can use Microsoft Discovery and Risk Assessment Server 2013 to identify, analyze, and monitor risk in end-user computing (EUC) applications, such as Excel workbooks and Access databases that are used in your organization. Microsoft Discovery and Risk Assessment Server 2013 enables you to identify risks that include errors in formulas or calculations, manually entered values where formulas should be used, inconsistencies within files, broken links, and so on.

Manually locating workbooks, databases, and EUC applications within your organization can be a complex task. Microsoft Discovery and Risk Assessment Server 2013 makes all of this easier by automatically detecting and analyzing files according to predefined definitions and rules. You can view reports that are always up to date so you can see at a glance what potential risks might exist within your organization’s important files. Microsoft Discovery and Risk Assessment Server 2013 is based on industry best practices and proven risk frameworks.

Future State

Ultimately, we’ll need these tools to be able to crawl Power Pivot models, Power View reports, and saved Power Queries. After all, that’s where the self-service magic is happening. It would also be nice to see a similar offering, from an automated meta-data collection perspective, in the Data Platform product line (SQL Server, SSIS, SSRS, SSAS).

I think Microsoft is heading in that direction (anyone recall Project Barcelona) which is a good thing because, as of now, their offerings in the world of metadata management leaves A LOT to be desired.

Update 2014-01-08:

Please consider the following article from the Kimball Group which was published on January 7, 2014:

Design Tip #162: Leverage Data Visualization Tools, But Avoid Anarchy

In this design tip, Joy Mundy (long time member of the Kimball Group) lays out the joys of self-service from the business users’ perspective and the problems seen from IT’s perspective. Better yet, she came up with a very creative and appropriate name for this phenomena: “self-service BI anarchy”. This is the very same phenomena I am describing in the post above.

Her proposed solution is primarily focused on communication which I agree is vitally important.  But, when communication does breakdown (because it will at some point in time), the server-level discovery and auditing tools above will definitely make life easier.

Leave a Reply