Power View meet Microsoft Excel 2013
Posted by denglishbi on July 19, 2012
This week Microsoft released the Public Review of Office 2013 (aka Office 15). You can download and check this out now for yourself and read some reviews of the new changes here:
- Sign up and check it out, was just released on Mon, July 16 – http://www.microsoft.com/office/preview
- Find out additional information here on some of the new features – http://www.engadget.com/2012/07/16/microsoft-office-15-preview/
One of the items that I have been patiently waiting to talk about and demonstrate is the new Power View and not only the new enhancements to it, but that it also is available in Excel 2013! With Excel 2013 the PowerPivot and Power View COM add-ins are provided with the installation, you simply need to enable them in the Options->Add-Ins section.
Before I dive into the new Power View let me show a quick screenshot of one of the Power View reports from our Visualizing Data with Microsoft Power View book.
A couple of things I want you to take a look at primarily is the field list. Also you will be able to compare this look to the enhanced one that I will now show off in Excel 2013. To create my Excel 2013 I decided I would go against a complete Excel solution, so I created a complete Pan-Geo Hospitality & Travel PowerPivot Model. I did this first in Excel 2010 and then upgraded this in Excel 2013 by accessing the Model in the PowerPivot Ribbon in Excel 2013 (you will notice that in this screenshot you will see two PowerPivot tabs and that is because I have both versions of Office running along with the add-ins (PowerPivot for Excel from SQL 2012 and now the one for Microsoft Office PowerPivot for Excel 2013).
When I opened up my Excel 2010 file with the PowerPivot model in Excel 2013 (which I also enabled the PowerPivot add-in) I then went to the 2nd PowerPivot tab in the Ribbon and clicked the Manage button in the Data Model section.
Could really use a line break in this warning message box:)
So just to reiterate the situation and give you an option to cancel.
Time to reboot:)
WARNING: I did notice one issue with the upgrade, the KPI that I had defined in my Excel 2010 PowerPivot model did not convert over, so I had to create the measure from scratch and setup the KPI again
Once I had the model converted over I used the new option in the Insert tab of the Ribbon for Power View (right in the middle in the Reports section)!
If you click on this and you do not have the add-in enabled you will be prompted and you can enable it then.
This creates a new worksheet in the workbook and then you can start building out your report, just like you can do with the current version of Power View, but this time you are doing it in Excel and not in your web browser as part of the Reporting Services 2012 integration with SharePoint 2010.
Here is what I ended up with recreating the Hotel Performance Dashboard report displayed above (notice if you can see a few extra items I have added and any other differences):
Did you see anything new on the report? No, not the fact that I changed the Play Axis in the scatter chart or the data labels being added in the bar chart. Look closer….okay, so you did see it, there are images included in the report and they are not part of the model definition!
The first one that I will point out is in the top left hand portion of the report and the new option I used here is the Picture option in the Insert section.
You can include Image files now so you can include the company logo as a report header (jpg, jpeg, and png files).
The other image you see is in the background of the report and for this I used the Set Image option in the Background Image section.
Here you can select the same type of image files as the picture and then you can specify the position option as Fit, Stretch, Tile, or Center. Along with that you can specify the transparency level and here I have it set to 90%.
Pretty neat, right?
Well you can do more formatting. If you recall in the existing Power View we have Styles – Using Styles in Microsoft Power View. In the new version here we can really tweak the report setup using the Themes section.
I can change the Font from Segoe UI to Times New Roman. I can select a different Theme, adjust the Font Size, and alter the Background (these changes are all at the report level, not item). The result (not saying this is ideal) ends up looking like the following:
Another interesting item you can do here in Power View is in the Data section of the Ribbon. You can actually review and modify relationships.
Pretty slick.
Let’s quick go back to the second screenshot above and take a look at the Field List. Notice something different here? What are those Globes and Spotlights doing in the list? Yep, you guessed it, Power View now provides geo-coding capabilities to use with the new Map visualization, plus it also supports Key Performance Indicators (KPIs)! In addition to the new Map visualization, the Pie chart has also been made available.
You can even include Pie charts in Maps (just a bit of a warning here, currently the Pie charts do not support data labels, similar to PerformancePoint Analytical Chart reports).
The mapping capability uses Bing Maps just like SSRS does for some of its mapping that was introduced in SQL Server 2008 R2.
Here is an example of a Map (including Pie charts, just to put it over the top):
You can see in the Field Well that there are new spots for Locations, Longitude, and Latitude.
If you are looking for the Card visualization it is now under the Table.
Now what about those KPIs, how do those look?
Well, looks like we still need some work here, but it is a start. The indicators it is using are not correct. I would like to see a more condensed view and options here to provide the detail information about the KPIs, but at least we can include them now.
Now there are more new items and capabilities that I haven’t even touched on yet, so stay tune for another post next week.
For more information about the new BI features in Office 2013 check out the following links:
- Excel 2013: Complete and powerful self-service BI tool
- Learn More about PowerPivot in Excel 2013 Preview
- Learn More about Power View in Excel 2013 Preview
Enjoy and kudos to all of the Microsoft teams that were involved to make all of this happen!
If you liked this post, check out the 2nd part here – Power View Meet Microsoft Excel 2013 Part 2
bradosterloo said
Really? Have they actually removed the limitation on Sharepoint? If so, that would be terrific news that I have been waiting for?
BI Visualizations | The View from Office 301 said
[…] this last week, I found Dan English’s post describing how with Office 2013, PowerView will be able to be used directly fro…, no Sharepoint required – and that’s got me happy because I think there will be some very […]
La self Bi ca fait peur … ou pas. | The SqlGrrrl said
[…] deguster sans modérationLa preview d’Excel 2013Microsoft BI Team blogDan English BlogChris Webb Blog Share this:TwitterFacebookJ'aime ceci:J'aimeSoyez le premier à aimer […]
PASS SQL Saturday World Map | Sam Vanga said
[…] I used from here. Dan English (@denglishbi) wrote an article titled PowerView meet Excel 2013 part1 and part2. That helps you get started. Dan also wrote a book on PowerView you may to check out. I […]
AJ Jenkins said
Hi Dan. I recently purchased your book Visualizing Data With Microsoft Power View but I am really interested in using Power View in Excel 2013. Would you consider making the Pan-Geo Hospitality & Travel PowerPivot model available for download?
Regards
AJ
denglishbi said
I have a post that talks about this and you can download the 2010 or 2013 version of the Excel files in this post – https://denglishbi.wordpress.com/2012/08/09/visualizing-data-with-power-view-pan-geo-goes-powerpivot/
Enjoy:)
MAURO MARCHI said
Hi Denglishbi.
I read these articles and i found them very useful !!!
I have 3 questions :
1) Can i use PowerView only if i have Excel 2013 ??? Is not compulsory nothing else ???
2) Power View in Excel 2013 can be considered a good tool to produce dashboard report in BI scenario ??? (Considering that i have not any product to produce DASHBOARDS…)
3) With PowerView can i connect only to a TABULAR MODELS or i can connect also to an existing MULTIDIMENSIONAL MODEL ????
Thanks in advance.
denglishbi said
1) Power View is available with SQL 2012 when you run SSRS in SharePoint integrated mode as well as in Excel 2013 now.
2) You could use Power View to create dashboard style reports, really depends on your requirements.
3) Currently Power View works against tabular models. A CTP was released in November 2012 for multidimensional support in SQL 2012. This should be released as some point this year and will require multidimensional models to be upgraded to SQL 2012.
MAURO MARCHI said
Thanks a lot for your answer.
Yesterday I bought your book…it will be arrive in February…i live in Italy.
Only a little question…about point 3)
How can i know when POWER VIEW will be able to use MULTIDIMENSIONAL CUBE ???
Regards in advance.
Ciao.
denglishbi said
I would monitor the BI blogs – http://blogs.msdn.com/b/business-intelligence/ the last blog post on it was the feature drill down http://blogs.msdn.com/b/analysisservices/archive/2012/12/09/power-view-for-multidimensional-models-feature-drill-down.aspx that was done by the SSAS team.
MAURO MARCHI said
Hi DenglishBi.
I installed Excel 2013 , i activated PowerView and i am doing tests about it using also your good and useful book i bought.
Soon our company will upgrade our SSAS 2008 R2 MULTIDIMENSIONAL CUBES in SSAS 2012 ones.
So how can i browse SSAS Cubes Multidimensional only with PowerView in Excel 2013 on my PC as i was browsing a PowerPivot ..
Is is possibile ??? Or is it possible only using SSRS 2012 ??? I hope no…
You wrote Multidimensional Cubes must be upgraded in SQL 2012 okay, but when will do that upgrade,,,i will be able to use PowerView with MULTIDIMENSIONAL CUBES only With Excel 2013 without using SSRS 2012 on my STANDALONE PC ????
Where can i find CTP you talked about months ago ????
Sorry for my silly questions…but i am really interested to use PowerView to do any simple dashboards.
denglishbi said
Power View will only work with tabular data models currently (PowerPivot or Tabular SSAS). There is a CTP that was released back in Nov that does provide support for Power View against multidimensional SSAS. You would need to install this version of SSAS and then upgrade your existing multidimensional databases to this version in order for Power View to work.
Microsoft SQL Server 2012 With Power View For Multidimensional Models CTP – http://www.microsoft.com/en-us/download/details.aspx?id=35822.
This should be released hopefully sometime this year, not specific date has been announced yet though.
MAURO MARCHI said
Hi DenglishBi .
Thanks a lot for your kind reply.
So if i understtod good all it will be possibile..
1) I will install with our Software House , SQL SERVER 2012 Standard Edition.
2) We will do all jobs necessary to migrate and upgrade our SSIS , DB and finally upgrade our MULTIDIMENSIONAL CUBES in 2012 version.
3) Install Microsoft sql server 2012 with Power View for Multidimensional Models CTP over it.
4) Install Excel 2013 in PC that need to use Power View…(Smart users)
So after that i will be able to browse multidimensional Cubes 2012 with Excel 2013 both classic Pivot and with new one PowerView…
Nothing to do in single PC ???
Is is all right ???
Many regards…