Maximizing the benefits of Excel add-ins: PowerPivot and Power View
A comprehensive collection of articles, videos and more, hand-picked by our editors
This is the second part of an excerpt from Chapter 1, "Getting Started," of Visualizing Data with Microsoft Power View by Brian Larson, Mark Davis, Dan English and Paul Purington (McGraw-Hill Professional; 2012) with permission from McGraw-Hill. Read the first part of this book excerpt here. Download a PDF of the full chapter here. Get insights from the authors in this Q&A.
To make the leap from departmental resource to enterprise-wide tool, we leverage another Microsoft product, SQL Server Analysis Services.
Analysis Services and the BI Semantic Model
Microsoft SQL Server Analysis Services has provided the support and capabilities for corporate-level business intelligence for over ten years. Analysis Services efficiently delivers large amounts of aggregate data to many corporate users for use in reports and interactive tools. It accomplishes this feat by using objects called cubes.
Cubes are loaded with aggregates of numeric quantities such as sales figures or inventory amounts. These aggregations are calculated when the data is loaded into the cube. The aggregates don't need to be calculated when the user queries the data. As a result, totals can be retrieved from Analysis Services cubes very quickly.
For example, if Ashley needs a report listing the sales for the company, totaled by department and year, Analysis Services will have already calculated these values in the cube. Instead of having to sum up the thousands of individual sales and group the totals by department and year, Ashley's report can retrieve the sales figures already totaled by department and year from the cube in Analysis Services.
Analysis Services cubes and their supporting data are known as Multidimensional BI Semantic Models. These models are not built by the users of the data. Business intelligence professionals within the IT department work with the users to design and build these models. As a result, the method of creating Multidimensional BI Semantic Models resembles the traditional method for building business intelligence solutions more closely than it resembles the new, user-centric method.
To remedy this situation, Microsoft has enhanced Analysis Services to host a new type of structure known as a Tabular BI Semantic Model. As the name implies, the Tabular BI Semantic Model manages data in a tabular format similar to the way Excel and, more importantly, PowerPivot manage data. This model provides the architecture and optimization for a large number of users to access data in a format identical to the data storage method used by PowerPivot. Having Analysis Services host Tabular BI Semantic Models fills the need for the top level of scalability in this user-centric approach to business intelligence.
Tabular BI Semantic Models are built using a tool called SQL Server Data Tools that comes with Microsoft SQL Server. With this tool, a model can be created from scratch or a PowerPivot workbook can be imported to create the basis for the model. Once the model is complete, it is deployed to Analysis Services, which provides the infrastructure to make the model available to reporting tools and other client software.
The user-centric approach to business intelligence now functions in this manner:
- A business decision maker connects to existing data sources and loads the data into PowerPivot for Excel in order to begin analysis.
- As time passes, the decision maker determines the workbook would be beneficial to the entire department and deploys the PowerPivot workbook to a Microsoft SharePoint document library used by the department. The members of the department can use and enhance the workbook.
- Over time, the department members enhance the PowerPivot workbook, adding calculations and charts that would be useful to the entire company. The IT department imports the PowerPivot workbook from Microsoft SharePoint and uses it as the foundation for a Tabular BI Semantic Model. The IT department deploys the model to Analysis Services to make the data available to the entire company.
Our users now need a way to analyze and explore data in a Tabular BI Semantic Model deployed to Analysis Services. Power View fulfills that role.
Launching Power View
Now that you're familiar with a little of the history of Microsoft business intelligence, you're ready to get back to learning about Power View. In fact, it's time to start up Power View and take a look around.
Power View and Microsoft SharePoint
Microsoft Power View is not a stand-alone application like Microsoft Word or Microsoft Excel. You do not install Power View onto your PC to begin building and using reports. Much of the functionality of Power View depends upon the features built into Microsoft SharePoint and, as a result, Power View can be launched only from a SharePoint environment.
For more on Microsoft Power View and PowerPivot
Learn how Power View facilitates business intelligence reporting
Explore SQL Server 2012 features, including Power View, in this slideshow
Get a step-by-step demo on how to import SQL Server data into PowerPivot
SharePoint Server was introduced by Microsoft in 2001 and was originally used as a document management and search tool. It provided the ability to upload documents and track document versions as they were being authored. Microsoft SharePoint Server also allowed users to find documents easily by searching both the names and the contents of the documents.
Microsoft SharePoint has changed a great deal since the original version in 2001. SharePoint is now used as a powerful tool for sharing information internally between employees and externally with customers and vendors. With Microsoft SharePoint 2010, companies can host employee blogs, share project and team information using many different site templates, provide business intelligence dashboards, build custom applications, and provide many social networking features to their employees and customers.
As a result, SharePoint is a natural fit for Power View. SharePoint is focused on collaboration and content sharing. Power View is a tool used to both visualize data and share it with others.