Manage Learn to apply best practices and optimize your operations.

Sound policy key to data quality management in Microsoft PowerPivot

Microsoft PowerPivot lets users do data analysis without IT’s help, but too much access can pose serious data quality management issues.

For end users who crave the freedom to do their own analysis without waiting on IT to make data accessible, there is a new tool on the market -- Microsoft PowerPivot for SQL Server 2008 R2. It lets business users slice and dice millions of rows of data and share the results with colleagues through SharePoint Server 2010.

It is such a powerful self-service business intelligence (BI) tool that some experts worry about data quality management in Microsoft PowerPivot. IT sets access rights and policies around the database, but PowerPivot lets end users create analyses from mashups of clean corporate data and data from outside sources and then pass the findings around as credible information.

“It opens up something of a Pandora’s box,” said Boris Evelson, principal analyst with Forrester Research Inc. “There needs to be some governance. If thousands of end users start creating and sharing applications without controls around them, then it could be dangerous.”

For example, any inaccurate conclusions reached in the analysis could possibly be shared and reused by other analysts. Somewhat like rogue software programs that can proliferate outside the IT department’s control, these data sets can spread across the organization unchecked.

“I’ve seen a lot of shadow data systems, where people with access to the database pull data into a spreadsheet and generate initial analysis using PowerPivot,” said Rick Sherman, founder of Athena IT Solutions and an expert in data warehousing and decision support systems. “Then other users take that and do what they want with it, adding macros and formulas and not always documenting the changes.”

Moreover, once the data is outside the corporate database, more people have access to potentially sensitive and proprietary information.

Policing PowerPivot files
So, how should IT maintain the accuracy and integrity of those newly created data sources as well as the enterprise database?

Impatience with IT is often the reason users may feel forced to do their own analysis with self-service tools such as PowerPivot. Analysts who need answers to business questions don't want to wait for IT to create officially sanctioned data analysis cubes. So trying to keep PowerPivot and other BI tools out of the workplace is not a good strategy, Evelson said.

Instead, take the time to create policies to control and secure the data.

Rita Sallam, research director at Gartner Inc., recommends that database administrators (DBAs) take advantage of the security features in SharePoint, which let administrators and users set different types of restrictions on files. 

DBAs can also require that all workbooks be saved to trusted file locations and they can use SharePoint controls to limit access to trusted users. These authors alone will be allowed to save to these locations or download files from them.

"Make them put all of their [BI applications and data] in shared folders on the SharePoint server, instead of their own C drives,” Evelson said. “Then monitor the usage of these on a regular basis. If one of them is getting high usage -- involving many users and large volumes of data -- then that’s probably a red flag that you need to intervene,” he said.

SharePoint 2010 allows administrators to limit access to files to read-only, make specific types of changes or create, approve and contribute items. That allows them to set limits on how other users can access certain types of files or data, for instance, requiring authorization from an administrator before publishing a workbook to SharePoint. Authors can set alerts to let them know when someone changes their files.

“IT can monitor which workbooks are accessed the most. If they see a workbook that is getting a lot of activity, they might want to then go and create a data analysis cube [in the SQL Server corporate database],” Sallam said. That will encourage users to access that clean, sanctioned data instead of reusing data from workbooks and outside data sources.

According to Sherman, as self-service BI tools become more powerful, end users will be more responsible for handling and safeguarding data. He advises IT departments to put more responsibility for accessing and controlling corporate data into the hands of power users, who are more technically savvy and can serve as intermediaries for other users who need to do data analysis.

“Not all end users are renegades. [Power users] are by and large responsible and trustworthy. These are often the same group of people who does acceptance testing for IT, so giving them responsibility makes sense.”

For data quality management in Microsoft PowerPivot, Sherman recommends crafting policies that give different levels of access rights to different user groups, depending on their data needs and their technical abilities, and then encourage or require users to set access rights to their own data files in an effort to minimize the proliferation of rogue data.

“Decide what limits should be put on what can be changed in the data, like the algorithms and macros and the data definitions. Those might be managed by the power users [but not anyone else],” Sherman said.  

It also pays to find out what, exactly, business users expect to be able to do on their own. Not all end users expect or want the same level of data access, Sherman said. So what might seem like a restrictive policy to one power user will likely be perfectly fine for less technically savvy users.

“For a lot of folks, just being able to drag measures in the pivot, that's self-service. They don’t want to change formulas or data,” he said. “Self-service is in the eyes of the beholder.”

Sue Hildreth
is a contributing writer based in Waltham, Mass. She can be reached at

Dig Deeper on SQL Server Business Intelligence Strategies

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.