Search results matching tags 'excel' and 'Power View'

New PowerPivot 2013 book available! #excel #powerpivot

sqlbi — Tue, 02 Apr 2013 11:25:00 GMT

Our new book about PowerPivot 2013 is finally available in printed edition, too!

The title is Microsoft Excel 2013: Building Data Models with PowerPivot and it is a partial rewriting of the previous book about PowerPivot for Excel 2010. In the previous book we had a target audience that included advanced Excel users and BI developers, because at that time there was no option to get the same engine in Analysis Services. But 30 months are elapsed, a new version of Analysis Services has been released and in this new book we focused mainly on Excel users. For this reason, we wrote a comprehensive book of all the feature of PowerPivot, but most important we tried to pass concepts of data modeling that might be pretty obvious for a DBA and a BI developer, but are completely new to an Excel user that never had the ability to create a data model with more than one table.

This book is focused on Excel 2013, so we included specific feature of this release related to PowerPivot, such as writing DAX queries and linked back tables, and features unique to Excel 2013, such as Power View. However, all of the PowerPivot features (so the 85% of the book) are good also for PowerPivot for Excel 2010 in its latest release (SQL Server 2012 SP1 PowerPivot for Microsoft Excel 2010), so you can safely use this book for both version of Excel.

You can download the first chapter of the book from the book page on SQLBI web site. And if you want to attend a training in a classroom or online, look at the complete list of available trainings on PowerPivot Workshop web site. The next online courses are scheduled on April 22-24, 2013 and June 17-19, 2013 (following online workshops are every other month).

Here are the links to directly order the book on Amazon around the world:

Amazon.com: hardcopy – kindle
Amazon.ca: hardcopy – kindle
Amazon.co.uk: hardcopy – kindle
Amazon.de: hardcopy – kindle
Amazon.es: hardcopy – kindle
Amazon.fr: hardcopy – kindle
Amazon.it: hardcopy – kindle

And here is the list of chapters:

Chapter 1 Introduction to PowerPivot
Chapter 2 Using the unique features of PowerPivot
Chapter 3 Introducing DAX
Chapter 4 Understanding data models
Chapter 5 Publishing to SharePoint
Chapter 6 Loading data
Chapter 7 Understanding evaluation contexts
Chapter 8 Understanding CALCULATE
Chapter 9 Using Hierarchies
Chapter 10 Using Power View
Chapter 11 Shaping the Reports
Chapter 12 Performing Date Calculations in DAX
Chapter 13 Using Advanced DAX
Chapter 14 Using DAX as a Query Language
Chapter 15 Automating Operations Using VBA
Chapter 16 Comparing Excel and SQL Server Analysis Services

This book should help you starting with PowerPivot at the very beginning, and you will probably use only the first chapters at that point. Over time, you will use following chapters and will learn more advanced techniques. This is not a book you can digest in a couple of days (after all, it is 500 pages long!), it will be your companion for several months, until you will master PowerPivot!

Olympics data available for all on Windows Azure SQL Database and Power View

jamiet — Thu, 29 Nov 2012 23:19:37 GMT

Are you looking around for some decent test data for your BI demos? Well, if so, Microsoft have provided some data about all medals won at the Olympics Games (1900 to 2008) at OlympicsData workbook - Excel, SSIS, Azure sample; it provides analysis over athletes, countries, medal type, sport, discipline and various other dimensions. The data has been provided in an Excel workbook along with instructions on how to load the data into a Windows Azure SQL Database using SQL Server Integration Services (SSIS).

Frankly though, the rigmarole of standing up your own ~~Windows Azure SQL Database~~ ok, SQL Azure database, is both costly (SQL Azure isn’t free) and time consuming (the provided instructions aren’t exactly an idiot’s guide and getting SSIS to work properly with Excel isn’t a barrel of laughs either). To ease the pain for all you BI folks out there that simply want to party on the data I have loaded it all into the SQL Azure database that I use for hosting AdventureWorks on Azure.

You can read more about AdventureWorks on Azure below however I’ll summarise here by saying it is a SQL Azure database provided for the use of the SQL Server community and which is supported by voluntary donations.

To view the data the credentials you need are:

Server mhknbn2kdz.database.windows.net
Database AdventureWorks2012
User sqlfamily
Password sqlf@m1ly

Type those into SSMS and away you go, the data is provided in four tables [olympics].[Sport], [olympics].[Discipline], [olympics].[Event] & [olympics].[Medalist]:

I figured this would be a good candidate for a Power View report so I fired up Excel 2013 and built such a report to slice’n’dice through the data – here are some screenshots that should give you a flavour of what is available:

A view of all the available data

Where do all the gymastics medals go?

Which countries do top ten all-time medal winners come from?

You get the idea. There is masses of information here and if you have Excel 2013 handy Power View provides a quick and easy way of surfing through it. To save you the bother of setting up the Power View report yourself you can have the one that I took these screenshots from, it is available on my SkyDrive at OlympicsAnalysis.xlsx so just hit the link and download to play to your heart’s content. Party on, people!

As I said above the data is hosted on a SQL Azure database that I use for hosting “AdventureWorks on Azure” which I first announced in March 2013 at AdventureWorks2012 now available for all on SQL Azure. I’ll repeat the pertinent parts of that blog post here:

I am pleased to announce that as of today … [AdventureWorks2012] now resides on SQL Azure and is available for anyone, absolutely anyone, to connect to and use for their own means.

This database is free for you to use but SQL Azure is of course not free so before I give you the credentials please lend me your ~~ears~~ eyes for a short while longer. AdventureWorks on Azure is being provided for the SQL Server community to use and so I am hoping that that same community will rally around to support this effort by making a voluntary donation to support the upkeep which, going on current pricing, is going to be $119.88 per year. If you would like to contribute to keep AdventureWorks on Azure up and running for that full year please donate via PayPal to adventureworksazure@hotmail.co.uk

Any amount, no matter how small, will help. If those 50+ people that retweeted me beforehand all contributed $2 then that would just about be enough to keep this up for a year. If the community contributes more than we need then there are a number of additional things that could be done:

Host additional databases (Northwind anyone??)

Host in more datacentres (this first one is in Western Europe)

Make a charitable donation

That last one, a charitable donation, is something I would really like to do. The SQL Community have proved before that they can make a significant contribution to charitable orgnisations through purchasing the SQL Server MVP Deep Dives book and I harbour hopes that AdventureWorks on Azure can continue in that vein. So please, if you think AdventureWorks on Azure is something that is worth supporting please make a contribution.

I’d like to emphasize that last point. If my hosting this Olympics data is useful to you please support this initiative by donating. Thanks in advance.

@Jamiet

Power View in SkyDrive revisited

jamiet — Thu, 02 Aug 2012 07:45:41 GMT

Three days ago I wrote a rather scathing blog post Power View in SkyDrive where I criticised Microsoft’s collaboration story in Excel 2013 in its various guises. I said then:

I clicked on one of those workbooks in order to view it in my web browser and I saw this:

“Unable to load the requested workbook”

Oh, did they forget to tell you? Workbooks containing Power View reports cannot actually be viewed on SkyDrive

Well, it transpires that something has changed in the interim because I tried it out this morning and saw this:

OK, I still can’t see the Power View report but nonetheless at least it gives me an explanation this time and I am assuming that did the data connections not have to be refreshed then this would work – that’s better news (I’m still confused as to why an inability to refresh connections prevents me from seeing the Power View report given that some data does already exist in the workbook, let’s save that whinge for another day). At least I am seeing something informative and there is an image that alludes to the existence of Power View. One important point; the above screen, even though it does not show anything useful, still requires SilverLight and that is not a surprise to me.

I must stress that I have not done anything different today from what I did three days ago, I simply clicked on the workbook within SkyDrive (it behaves the same on both IE & Chrome by the way). So, either they’ve updated the web app on SkyDrive or three days ago there was a temporary glitch.

OK, so we have some progress. I’ll continue investigating and if I find anything out I shall post it here.

@Jamiet

Power View in SkyDrive

jamiet — Mon, 30 Jul 2012 14:07:00 GMT

UPDATE 02/08/2012, I have written an important follow-up to this blog post at Power View in SkyDrive revisited which has better news than what I have written below. Please read that blog post as well as this one.

Sean Boon has begun an interesting blog series where he is analysing data from the Olympics using using Power View in Excel 2013, his first post in the series is at Visualizing the Olympics with Power View in Excel 2013: Day 1. While the capabilities of Power View itself are impressive this gives me an opportunity to highlight what I think is a massive failing in the whole Power View Excel in 2013 story.

Sean provides a link to a SkyDrive folder, Power View Olympics, where he is collecting his demo workbooks. I clicked on one of those workbooks in order to view it in my web browser and I saw this:

“Unable to load the requested workbook”

Oh, did they forget to tell you? Workbooks containing Power View reports cannot actually be viewed on SkyDrive. Whoops! What is even more vexing about this is that if the workbook were hosted on SharePoint then I *would* be able to view it using my web browser, however unless I am mistaken, with SharePoint I can’t share it with the whole world like I can using SkyDrive! SharePoint offers online Power View while SkyDrive offers frictionless sharing – neither offers both! This glaring lack of parity between SharePoint and SkyDrive is, to my mind, the most infuriating part of Microsoft’s Excel collaboration story.

None of this came as a surprise to me. I was lucky enough to be invited onto the Office 2013 Technical Preview back in 2011 and I discovered back then that Excel workbooks containing Power View reports would not be viewable in SkyDrive; I fed back vociferously that this was a big failing, I guess that feedback fell on deaf ears.

Welcome to office collaboration, Microsoft style! You can share your Power View workbooks with the entire world but if anyone wants to actually look at them they’re gonna have to install Excel or SharePoint! Meanwhile if you want frictionless, collaborative, immersive BI on the web other offerings are merely a click away!

Power View in Excel 2013. So near, yet so far!!

@Jamiet

For more scathing opinion on Microsoft’s Excel collaboration efforts take a read of: