Excel PowerPivot SQL 2012 vs. Excel 2013 Performance Demo

[tweetmeme source=”denglishbi” only_single=”false”]

I just wanted to do a blog post to demonstrate a performance situation I ran into last week.  I had created a PowerPivot model in Excel 2013 and it performed very well (quite the understatement, phenomenal) when creating PivotTables and Power View reports.  I then went ahead and recreated the model in Excel 2010 with the SQL Server 2012 PowerPivot add-in.  The model creation went fine, but when it came to trying to replicate the report I ran into a slight snag.  Well I actually couldn’t even complete the report, I ran out of memory on a box with 8GB of memory.

We decided to upgrade the box and gave it an additional 4GB of memory, but that didn’t change anything, the query still consumed all of the memory and wouldn’t complete.  We decided to test this out and migrate the model to a Tabular SSAS instance on a server with 64GB of RAM.  Well the query for the PivotTable just kept running and we watched the Analysis Services process climb to over 18GB and it was still climbing with no results coming back.  Needless to say I was confused and a bit embarrassed.  I decided to give it a go in Excel 2013 again and once again no issues and the results were very fast (might even say blazing fast).

To demonstrate this I have gone ahead and created a demo of the exact scenario with a very comparative sample so you can get a feel for what I was seeing, just take a look at this video:

Excel PowerPivot Performance Demo from Dan English on Vimeo.

In reaching out to the product team I discovered that in Office 15 there have been some performance improvements when it comes to dealing with filters.  Well we can definitely see that based on my results:)  It turns out that these same performance improvements will be coming eventually to the current version as well, we just need to wait for the release of SP1 for SQL Server 2012.  Some of the improvements seen will also be available to querying the data in Analysis Services as well, both Tabular and Multidimensional!  So just hold on until SP1 is released.  If you want to checkout the performance improvements today have a go with PowerPivot for Excel 2013 (you will not be disappointed).

Final Call for the MN SQL Saturday 149 Speakers

[tweetmeme source=”denglishbi” only_single=”false”]

This is the final weekend to submit your sessions for the Minnesota SQL Saturday event coming up in September (and yes, for the first time this is actually on a Saturday).  The speaker abstracts can be submitted through the end of July and then the fun begins to start selecting the sessions and setting up the official schedule.

So far 85 sessions have been submitted by 36 different speakers and 19 of those submitted more than one session.

If we do a breakdown of the current list that has been submitted it looks like the following so far:

Just a word of warning, if you have only submitted 1 session you might want to review the list that has already been submitted.  If your topic is a duplicate the chances of you making the final cut might be tough, particularly if the duplicate submission speaker only submitted 1 as well:)

So if you were holding out to submit a session to speak your time is up, time to submit.  If you already submitted, time to review the full list and re-evaluate your submission(s) to secure your spot.

Call for Speakers

Submitted Sessions

Good luck to all the speakers that submitted and can’t wait for the big event.

Power View now available on the Nook!

[tweetmeme source=”denglishbi” only_single=”false”]

This is not a joke, you can now purchase and get the book Visualizing Data with Microsoft Power View on the Nook now:

Barnes & Noble: Visualizing Data with Microsoft Power View by Brian Larson, Mark Davis, Dan English, Paul Purington

The book is also available on the Kindle from Amazon as well (available for free 2-day shipping if you have a Prime membership)

This is a great book to get you going on the new self-service BI reporting feature that is currently available with SQL Server 2012 using Reporting Services in SharePoint 2010 Integrated Mode and is also available now in Excel 2013 which is available for consumer preview:)

Not only does the book cover what is Power View and how to use it, but it provides tons of Learn By Doing exercises, tons of videos, great scenarios on reasons for using, and also covers how to create tabular models to support Power View along with DAX calculations and functions as well!

So what are you waiting for, don’t just take my word about it, listen to what people are saying about it on the Amazon reviews (which by the way is 5 out of 5 starts with 3 reviews currently).  Here are some snippets:

“A truly good book teaches me better than to read it. I must soon lay it down, and commence living on its hint. What I began by reading, I must finish by acting”

The authors (Brian Larson, Mark Davis, Dan English and Paul Purington) have done a splendid job of explaining all the features of Power View in an easily understandable format. Even though the target audience for this book are Power View beginners which includes non-technical business users, it also gives professionals who are acquainted with the tool (like me) an opportunity to review all the functionalities and fix the gaps in learning.

This is a very cool way to learn Power View. The book contains step by step instructions that show you how to take advantage of all Power View features, and the accompanying DVD contains a video of the instructions being implemented.

There are 4 hours 48 minutes and 56 seconds of video included on the DVD. They show you how to implement the learn by doing sections of the book. Although I have an environment built for implementing the samples, the videos are great for people that don’t have the resources to put one in place.

This book covers everything you need to know to get started with Power View and creating BI Semantic Models. If you buy one book on Power View make it this one.

“Visualizing Data with Microsoft Power View” is a step by step guide which not only shows the end user how to use the Power View product but also get the most benefit out of their visualizations.

“Visualizing Data with Microsoft Power View” is well targeted at the business user and written in a context that does not alienate a non-technical user.

So what are you waiting for, get a copy, read the book, do the exercises, and most of all leave us feedback or post reviews.  Let us know what you think.  Power View is a fantastic data visualization tool and I am very excited about this new feature that is available for us to use and I hope you all are as well.

Power View meet Microsoft Excel 2013 Part 2

[tweetmeme source=”denglishbi” only_single=”false”]

Last week I did my initial post of going over using Power View inside of Excel 2013 – Power View meet Microsoft Excel 2013.  In that post I went over how I converted the PowerPivot model of the Pan-Geo Hospitality & Travel that was used in our Visualizing Data with Microsoft Power View book (well at least a Tabular SSAS version was used, I recreated the entire model in PowerPivot).  I upgraded that model to use the Excel 2013 version of PowerPivot and then showcased some of the new features such as:

• Background Images
• Pictures
• Themes
• Relationships
• Pie Charts
• Map Visualizations (Bing Maps)
• Key Performance Indicators

So what other key features are that that might be of interest?  Hmmm, good question.  And yes, there are more features.  Have you been wanting support for hierarchies?  What about drill up and down capabilities?  Well guess what, you get them both:)

You might have noticed in the previous post that you saw some hierarchies listed in the field list:

Let’s take a quick look at using the Country Hierarchy and see how this looks in a table, matrix, and a chart.

Table

Matrix

Chart

So in the table and matrix no change other than the fact that we can see our hierarchies in the field list and select them.  This allows us to quickly add multiple fields to the canvas.  What about the chart though?  I picked the country hierarchy, but I am only seeing the first level of the hierarchy which are the continents.  Where are the countries?

Well if you double click on the columns in the chart it will actually drill down into that continent and display the data for the countries!

You will also see that the sort by and title text changed to display country and there is a new icon available when you hover over the chart, an arrow that provides drill up functionality.  So the drill up will bring you back up to continent. 

Now the neat thing about the drill up and down functionality is that you can expand on this.  The fields do not necessarily need to be part of a hierarchy, you can actually build out the drill up and down capabilities by adding fields into the field well.  So in this example I will add more fields into the chart below the country:

So let’s double click a few times to see what we get:

Now the one thing is that we really are not sure what we are looking at, meaning that what continent, country, and chain type did we select previously?  I can see based on the sort the trail that I have navigated, but what were the values associated with them.  That would be a nice feature.

The drill down also works for the map reports as well.

So if we double click on the China pie chart we will go down to the city level based on the navigation defined in the field well:

And you can use the zooming capabilities of the map as well.

Pretty cool and you can do more with the map with the layout settings in the Ribbon:

In addition to hierarchy, drill up & down, and cool map capabilities there is another minor addition that was added and that was support for hyperlinks in a text box.

In this example I have a text box that includes the words ‘Microsoft BI’ and below that a hyperlink to the web site.  If I hold the CTRL key and click on the hyperlink it goes to the web site:

They hyperlink simply gets added after you type in the URL and press a space, hit enter, or click out of the text box.

Well that covers the majority of the new enhancements to Power View, for the complete list checkout the links at the bottom of my previous posting – Power View meet Microsoft Excel 2013.

I will continue to work with the new features and post any additional findings I might come across like using images from the data models and the issues you might encounter along with using these files in SharePoint.

Power View meet Microsoft Excel 2013

[tweetmeme source=”denglishbi” only_single=”false”]

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

PASSMN July 2012 Meeting

[tweetmeme source=”denglishbi” only_single=”false”]

The next Minnesota SQL Server User Group meeting is coming up next week. This month Steve Hughes (@DataOnWheels) and Denny Cherry (@MrDenny) will be talking.  The topics this month will be on oData and optimizing SQL Server in virtual environments.  Be sure to register so that your name badge will be available for you at the Microsoft Technology Center when you arrive.

The sponsor for this month’s meeting is Magenium Solutions.

Location: 3601 West 76th Street, Suite 600 Edina, MN 55437

Date/Time: Tuesday, July 17 from 4 to 7 p.m.

Live Meeting:

• URL: https://www.livemeeting.com/cc/usergroups/join?id=BFQM8K&role=attend&pw=FZ%2Bw3%3F%5BFm
• Meeting ID: BFQM8K
• Entry Code: FZ+w3?[Fm

Agenda:

• 4:00-4:15 : Registration / hospitality / networking
• 4:15-5:45 : O, there’s my data: The Open Data Protocol (OData) – Steve Hughes 
• 5:45-6:00 : Announcements and prize giveaways
• 6:00-7:00 : Optimizing Microsoft SQL Server Performance in a Virtual Environment – Denny Cherry

Please click here for meeting details and to RSVP for the event

Presentation 1:

O, there’s my data: The Open Data Protocol (OData) Earlier this year, Mark Souza from the SQL Server product team spoke at our user group.  During the Q&A I asked about interacting better with Oracle.  Mark mentioned that OData would be the best way to handle this.  I will be discussing what OData is and what tools are involved.  I will also explore how to create an OData feed and how to interact with it using tools such as PowerPivot, SSIS, and SQL Azure.  I will also examine the impact of OData for data professionals, both good, bad and ugly.  In the end, you should understand how OData works and where it can help us expose our data appropriately to our users.

Steve Hughes is a Principal Consultant at Magenic. His area of expertise is in data and business intelligence architecture on the Microsoft SQL Server platform. He was also the data architect for a SaaS company which delivered a transportation management solution for fleets across the United States. Steve has co-authored two books and delivered more than 30 presentations on SQL Server and data architecture over the past six years.  He also provides insights from the field on his blog at http://www.dataonwheels.com.

Presentation 2:

Optimizing Microsoft SQL Server Performance in a Virtual Environment In this session we’ll look over some of the things which you should be looking at within your virtual environment to ensure that you are getting the performance out of it that you should be.  This will include how to look for CPU performance issues at the host level.  We will also be discussing the Memory Balloon drivers and what they actually do, and how you should be configuring them, and why.  We’ll discuss some of the memory sharing technologies which are built into vSphere and Hyper-V and how they relate to SQL Server.  Then we will finish up with some storage configuration options to look at.

Denny Cherry is an independent consultant with over a decade of experience working with platforms such as Microsoft SQL Server, Hyper-V, vSphere and Enterprise Storage solutions. Denny’s areas of technical expertise include system architecture, performance tuning, security, replication and troubleshooting. Denny currently holds several of the Microsoft Certifications related to SQL Server for versions 2000 through 2008 including the Microsoft Certified Master for SQL Server 2008 as well as being a Microsoft MVP for several years and a VMware vExpert.

PASS Data Warehousing and BI Virtual Chapter July Sessions

[tweetmeme source=”denglishbi” only_single=”false”]

Are you looking for some more online training during the summer months? Well you are in luck. If you were not aware, there are some free webinar sessions available through the Data Warehousing and Business Intelligence PASS Virtual Chapter.  The sessions available are the following:

• Thu, July 12 – Managed Self-Service BI (Melissa Coates) 12 to 1 p.m. EST
• Tue, July 17 – Dimensional Modeling 101 (Thomas LeBlanc) 11:30 to 12:30 p.m. Central
• Thu, July 19 – SSIS Catalog; Big Step for ETL Framework (Reza Rad) 10 to 11 p.m. EST
• Tue, July 24 – Programmatically Managing the BI Stack (objects) metadata via C# (Jesse Kraut) 12 to 1 p.m. EST
• Tue, July 31 – Applying SSRS to .NET applications (David Liebman) 12 to 1 p.m. EST

You can check out the upcoming sessions that are available here along with the abstracts – http://bi.sqlpass.org and you can also follow the Twitter handle @PASSBIVC.  Some of the presentations have been recorded and are available for viewing in the archive section as well – http://bi.sqlpass.org/PresentationArchive.aspx.