Power View Reporting on Multidimensional Cubes Released

I’m a little late to the celebration but on Friday some there was some exciting news regarding Power View was announced. You can read about the details of it at the Analysis Services and PowerPivot team blog for Microsoft here.

http://blogs.msdn.com/b/analysisservices/archive/2013/05/31/power-view-connectivity-for-multidimensional-models-released.aspx

This is part of a SQL Server Cumulative update pack that you can download here.

http://support.microsoft.com/kb/2833645

One thing to probably make mention of that you can ready on the Microsoft team blog is that this is only for SQL Server and SharePoint Power View.  If you’re using Power View with Excel 2013 only then you’ll have to wait a little longer for an update.

Either way this is great news and for those that were anticipating projects to convert Multidimensional cubes to Tabular or PowerPivot so they could use Power View.

Getting Started with Microsoft Data Explorer

What is Data Explorer

Data Explorer simplifies the data discovery phase for Excel users that are creating self-service Business Intelligence solutions.  It does this by provided straightforward methods for connecting to data previously unheard of, without a developer, in Excel.  It also provides a basic ETL tool for those involved in self-service BI projects all within Excel.

What do I need

Currently Data Explorer is only available as a preview and works with Excel 2010 SP1 or Excel 2013.  You can download the Data Explorer preview from http://www.microsoft.com/en-us/download/details.aspx?id=36803.

Enabling the Add-in

Once you download and install the add-in you will have to enable it by going to File –> Options –> Add-Ins.  Then Select COM Add-ins from the Manage dropdown and click Go

Check off Microsoft “Data Explorer” Preview for Excel from the Add-Ins available list then click OK.

Once you have enabled the add-in the DATA EXPLORER tab will appear in the increasingly crowded Office ribbon.

Let’s take a look at what this new add-in has given us.

What does it do

A very detailed list of each element of Data Explorer can be found here http://office.microsoft.com/en-us/excel-help/start-page-HA104003813.aspx.

In this post I’ll walk you an example that i think all companies are starting to take a lot more seriously, which is social media sentiment.  In other words how does the public perceive our company.

With the built-in ability to import data from Facebook Data Explorer can very easily analyze things like statuses, likes. comments, and much more.  Let’s walk through an example:

1. Launch Excel and ensure the Add-in is enables with the steps detailed above.
2. Select the Data Explorer tab and choose From Facebook from the From Other Sources dropdown selection.
3. You will then be prompted to provide a UserName or object ID.  The default is “me”, which means it will allow you to import data from your personal Facebook account.  However, if you’re an administrator of a corporate Facebook page you could enter that page in here.  For example, I am an admin on the Pragmatic Works page.  So if change the default “me” to PragmaticWorks and set the Connection name to Posts I can see all posts on our corporate page.  Click Apply.
4. Now the true data exploration can begin.  My first step was to hide all the columns I don’t care about.  You can select multiple column headers at once and then right-click to select Hide Columns.
5. Now that we’ve got just the data we care about let’s analyze things like how many likes and comments we’re receiving on our posts.  You will notice on the columns for both comments and likes that the word Link is displayed.  This means there’s more data in a separate object that can be imported.
6. If you click the word Link it will preview that data in that object as shown below when i clicked on likes.  From this I can tell there were 6 likes on this particular post.  There’s also another option if i click Table that will allow be to see the actual users that liked my corporate post.
7. This is great for exploring but if i actually want to add this data to my query then I would clear my likes search on the Steps page as shown below.
8. For my Marketing team’s analysis they really want to know a count of how many likes and comments we had on each status.  To do this I will navigate back to the likes column in my query and click the Expand button to check off new values i want to return.  For this example i just need the count of likes but if I wanted to see who actually made the like on our post I could return the data.
9. We have the data we want now so hit Done and all the Facebook sentiment data will be imported into Excel.
10. Now that this data is in Excel we can create a PowerPivot workbook on it or even Power View report that looks something like this:

Having the ability to create these kinds of report in a very short amount of time is exactly what our Marketing department needs to analysis our true reach.

Building Your First Power View Report

This has been reposted from a guest blog post I wrote on the Microsoft Business Intelligence team blog.

Fulfilling User Needs

Developing a Power View report has been touted as being so simple that within a handful of clicks you can add rather impressive elements, like effective visualizations, to a report. This isn’t always the case with other tools in the market. The goal of this post is to walk you through Power View as if you were designing a report for the first time, and show you how simple it really is to build a Power View report.

Using the Development Environment

Before we start, it’s important to note that you will need SQL Server 2012 and SharePoint 2010 to create a Power View report. Power View also runs on Silverlight 5, which enables you to edit Power View reports in a browser window, as shown below.

The development interface retains the familiar look and feel of Office tools. If you know how to use Excel, picking up Power View for the first time should be a breeze.

Understanding your Data

Understanding your data and what you want to achieve with it is one of the most important aspects of building a Power View report. The better a report designer understands the layout of the data model, the easier it becomes to produce insightful results. Power View is designed to help you produce fast results in an easy to understand format.

When designing PowerPivot reports with images and visualizations, you may want to consider BISM Tabular data sources for Power View reports. Shown below is a report that leverages a data set with images enabled by BISM Tabular data sources.

Building your First Report

In this demonstration of building a basic Power View report I will be using a data set found on Microsoft’s live demo site. To follow along go here to access the blank Power View Report (this will require a Windows Live ID).

Upon launching this live example you will see a blank report in presentation view. To edit this blank report, select Edit Report in the top left of your screen.

You can create report elements by selecting the data you would like to visualize from the data pane on the right side of your screen. It’s helpful to know how your data model is organized because you may find yourself struggling to find the fields you need otherwise.

Next, from the Product table, select Image and then use the Visualizations toolbar to set the field as Tiles. This will place the product images in slide tile view. You may need to stretch the tiles across the width and the length of the report for layout purposes.

To capture the details of each product in the slide tile view, select a single product image and select Card from the Visualizations toolbar dropdown. Add the fields Product Name, Category, Channels, Item Group, SKU and Retail Pricefrom the Product table. To fit all of the information you may need to resize the Card as shown below. Think of the Card as a baseball card that captures all the key information about a product.

Next, we will add regional sales information to the report. To get the regional data to interact with the product image tiles, select a tile and pick Region Map and Region Name data elements from the Region table. From the Sales table selectRevenue. This gives a tabular view of the region sales for the selected product with an image that highlights the part of the country that makes up that region. Move this table to the bottom right of the report to improve the layout of the report.

To complete the regional view of our report, we will put together a bar chart of profitability metrics by product. Begin by selecting one of the tiles and pick the data elements Profitability from the Sales table and Wholesales Price from the Product table. Turn the new table into a chart by selecting Clustered Bar from the Visualizations toolbar. Resize and move the chart to the bottom left of the report. Lastly, name the report Product Sales Report. The final result of this first view should look like this.

Power View reports can have multiple views, which appear as multiple pages to a user. This allows users to easily flip through multiple reports in a single Power View document. To create a new view, select the View context menu on the left side of the design surface and click New View as shown below.

In the new view we will demo one of the most impressive visualizations in Power View –the Scatter chart. To begin, go to the Sales table and select Revenue, Quantity, and # Products, and from the Product table select Category. Once the data elements are selected, choose Scatter Chart from the visualizations toolbar to turn the table into a chart. The result is a nice scatter that shows which product categories generate the most revenue for the business. To take this chart to the next level, we could add animation to it by dragging Month from the Date table to the Play Axis of the chart. Resize the chart to fill the entire report view and hit the play button on the bottom left to view product category performance over time. Lastly, name the report Sales Analysis by Month.

With this report complete, you can view it as a user by selecting Full Screen under the Home tab of the toolbar. This allows you to flip through multiple views of the report and preview the report views by selecting the button in the bottom left of your screen.

Hovering over the views, you get a larger preview on the top report. When you select a report, it appears in full screen mode.

Going through this demo should have given you the idea of how with just a few clicks, you can create impactful, insight-driving reports with Power View.

SQL Lunch #68 – Designing Your First Power View Report

Next week I will be speaking on designing your first Power View report for SQL Lunch.  Be sure to join me for the event and as always “No fluff, just stuff”.

Add To Outlook: Add To Calendar

Date and Time: 7/18/2012 11:30:00 AM CST

Topic: #68-Designing Your First Power View Report
Power View has the capability of creating eye-popping visualizations within SharePoint 2010. It provides intuitive ad-hoc reporting that can be used by a variety of business users to make critical decisions. In this session you will learn how to design your own dazzling reports using Power View.

Choosing the Right Microsoft Reporting Technology Part 5: Power View

In part five of this on going series about choosing the right reporting tool we will discuss the newest of tools called Power View.  While Power View is part of Reporting Services in SQL Server 2012 I’ve decided to split it as it’s own topic to help understand when you would use it over the traditional SSRS reports.  Now, because this is one of the newest reporting tools you can count on much of this post to get outdated over time.  In fact, a lot of the limitation you may see me describe are rumored to be fixed in future releases.

I’ve mentioned in previous posts that there’s not one tool that can solve all reporting needs so you may find yourself using multiple tools to provide a well rounded complete reporting solution.  This will be the last part of this series that describes tools, but there will be a part 6 that will help guide you in making the appropriate decision on which tool to use for each report you design.

If you’re new to reading this blog series I encourage you to start from the beginning even if you think you have a strong understanding of the tools detailed because you may reconsidered using tools that previous you dismissed as an option. Here’s the complete list of tools I’ve reviewed:

• Reporting Services
• Excel (From the perspective of building PivotTables without PowerPivot)
• PowerPivot
• PerformancePoint
• Power View

So let’s now review the newest and final Microsoft reporting tool Power View.

Power View

What it is

Power View is Microsoft’s answer to some of the popular third party tools like Tableau.  In a nutshell Power View is a highly visual ad hoc reporting tool for end users.  It is designed with the end user in mind and because of that it is incredibly simple to create really impressive reports.  Within a half dozen clicks a user can create a report similar to what you see below.

Power View is a tool that is now part of Reporting Services in SQL Server 2012 but you can only create or view the reports from SharePoint 2010. The tool used for designing the reports is actually dependent solely on Silverlight 5 (subject to change) being installed. So there’s no extra download like all of the other reporting tools on the market.

Power View is a highly metadata driven tool, which means it is important to to have a well defined data source.  With the current version of Power View you can only use data that is either sourced from a PowerPivot document that has been deployed to SharePoint or a BISM Tabular model.  BISM Multidimensional coming in the future according to several forum posts I’ve seen:

http://social.msdn.microsoft.com/Forums/en-US/sqldenreportingservices/thread/663283d7-a89c-44a3-97a2-5e406c1d635f

http://social.msdn.microsoft.com/Forums/en-AU/sqlkjpowerpointforsharepoint/thread/41b183ea-2642-487a-8dbb-8d44cdf71025

You can easily create multiple views on a report so that report consumers can view the data represented on multiple pages and in multiple ways.  Also, Power View reports can easily be exported into tools like PowerPoint so users no longer have to take screenshots of reports to embed in PowerPoint slide decks.  With Power View you will be able to look at live views of the report inside the slide deck and interactive with it like you would in SharePoint.

The quickest way to get started with the tool is to use the SharePoint 2010 template site called a PowerPivot Site which has all the components needed to begin development. After deploying any PowerPivot workbooks or BISM Tabular models to the PowerPivot Gallery you will be able to begin creating Power View reports.

If you would like to get started with learning Power View there are several live demos available. If you go to http://tinyurl.com/PowerViewDemos you will find several examples of completed reports and datasets that you can create your own reports against.

What it isn’t

Since the release of Power View people have often asked me if I think that it means an end for PerformancePoint.  Personally I don’t believe PerformancePoint is going anywhere because they have different purposes and also it is used by different people.  I think many people think they’re similar because they both use SharePoint and are both very visual tools but when you look at the details they really have different purposes.

PerformancePoint I consider more of an executive level dashboarding tool while Power View is for user created ad hoc reporting.  You probably don’t want end users developing executive level dashboard reports.  I prefer to have IT managing and controlling content that is exposed to executives and large scaled deployments to the entire company.  PerformancePoint also has the capability to do KPIs and Scorecards, which Power View does not have natively built-in.

Who Uses it

Power View has been created for the purposes of end users designing their own reports.  With the first version of Power View that is made very clear because everything is drag and drop.  There is no place within the tool to actually write queries like most other reporting tools so it’s very dependent on a strong data source.  While this may be seen as a limitation by some if fulfills the requirement to be an end user tool.  Don’t be surprised if the ability to query inside Power View changes in the future though,

How is it consumed

Power View can be viewed through SharePoint 2010 Enterprise with the Reporting Services add in for SQL Server 2012.  With a PowerPivot Gallery inside SharePoint you can easily select to view existing Power View reports or create new ones off of PowerPivot workbooks and BISM Tabular models.

Limitations

Power View has a very direct purpose so some of the limitations it holders are understandable.  Some of the other limitations are likely to change as the tool matures.  Keep in mind this is the first release for the tool  Here’s the limitations I’ve come up with:

• Limited number of data sources that can be used.  Currently only BISM Tabular or PowerPivot that is deployed to SharePoint.  Should be expanded in the future.
• All metadata driven, so no direct query option right now.
• Can only view Power View reports through SharePoint

Despite these limitations I think you will find a place for this tool because of it’s impressive results.  Also, as Power View goes through some growing pains of being a new tool many of these limitations will be fixed.

Summary

As we go through this series remember these high level characteristics about Power View

• Highly visual End User reporting tool
• Current version must use either BISM Tabular or PowerPivot as a data source
• Requires SharePoint 2010 and SQL Server 2012.  Enterprise for both
• Metadata driven so things like images are actually a good thing

I hope you’ve found this helpful and stay tuned for the Part 6 in this series where we’ll wrap up and talk about how to make your decision. To read any of the other parts to this series follow the links below.

• Part 1: Reporting Services
• Part 2: Excel
• Part 3: PowerPivot
• Part 4: PerformancePoint
• Part 5: Power View
• Part 6: Making the Decision