Search results matching tag '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!

End of 2012 and news in 2013 for #PowerPivot, #ssas #tabular and BI

sqlbi — Thu, 27 Dec 2012 18:26:38 GMT

This year is going to end, Maya failed their predictions and while this is bad for predictive industry, it’s also good for all of us!

We’ve seen many news in Microsoft BI stack:

Excel 2013 has been RTM’d – its General Availability is expected in early 2013 but many early adopters are already using it daily (myself included)
PowerPivot and Power View are both integrated in Excel 2013 and I think that they are the most compelling reason to upgrade your Excel. Power View alone worth the effort
SQL Server 2012 has been released and I’m seeing the first SSAS Tabular projects going in production in these days.
I, Alberto and Chris published the book Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model, which has good reviews and is selling well. It’s clear that the interest is high in this new technology for Corporate BI
We also run several SSAS Tabular Workshops, many sessions about Tabular topics in conferences and user groups.
I stopped writing blogs/article in the last weeks, but don’t worry, it’s just that I and Alberto are just writing another book…
We recorded several videos about DAX for Project Botticelli and we’ll enhance this library in 2013.

So what’s next in 2013? Let’s see some anticipations and personal predictions:

SharePoint 2013 and Office 365 are going to further innovate Self-Service BI. Maybe that this process is not so fast as Microsoft hopes, but it’s a clear direction and I hope that new features in this area will appear with a sooner release cycle, we simply cannot wait other 3 years to get more features on the cloud.
Mobile BI with Microsoft Technologies is going to be a real thing in 2013. And Office for iPad should be real, too. Power View for iPad as a native application is also another important step.
I will speak at PASS BA Conference 2013 in Chicago, IL (April 10-12, 2013) – at that time, I hope much of the MS Mobile BI tools will be available to anyone.
In March 2013 our new PowerPivot book Microsoft Excel 2013: Building Data Models with PowerPivot will be published by Microsoft Press. We are working hard these days in order to complete the drafts, we listened to all of the feedback we received, especially from Excel users. Time will tell if we did a good job on that.
We will publish other content on SQLBI web site and we have some interesting news for PowerPivot users and Tabular developers… but it’s too early to discuss that. Just stay tuned! Why not registering to our newsletter? (hint – scroll down to the end of the page to insert your email)
We will announce a new PowerPivot Workshop for Excel 2013 (we are working on an updated web site – it should be ready by the end of January)
In January we will announce new dates for our SSAS Tabular Workshop and the new DAX Advanced Workshop.
I hope DAXMD will be released this year, it is already available as CTP. If you create canned reports for SSAS Multidimensional, consider using DAX as a query language when DAXMD will be released.
DAX will be always more important…

Economy is not going well in many countries, and I’ve seen this impacting also our ecosystem. Sometimes you might think that your efforts are worthless, but in the long term the investments in updating skills produces a good return, at both company and individual level. Don’t give up!

I wish you a Great 2013!

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

PASS Summit 2012: keynote and Mobile BI announcements #sqlpass

sqlbi — Wed, 07 Nov 2012 19:11:00 GMT

Today at PASS Summit 2012 there have been several announcements during the keynote. Moreover, other news have not been highlighted in the keynote but are equally if not more important for the BI community.

Let’s start from the big news in the keynote (other details on SQL Server Blog):

Hekaton: this is the codename for in-memory OLTP technology that will appear (I suppose) in the next release of the SQL Server relational engine. The improvement in performance and scalability is impressive and it enables new scenarios. I’m curious to see whether it can be used also to improve ETL performance and how it differs from using SSD technology.
Updates on Columnstore: In the next major release of SQL Server the columnstore indexes will be updatable and it will be possible to create a clustered index with Columnstore index. This is really a great news for near real-time reporting needs!
Polybase: in 2013 it will debut SQL Server 2012 Parallel Data Warehouse (PDW), which will include the Polybase technology. By using Polybase a single T-SQL query will run queries across relational data and Hadoop data. A single query language for both. Sounds really interesting for using BigData in a more integrated way with existing relational databases. And, of course, to load a data warehouse using BigData, which is the ultimate goal that we all BI Pro have, right?
SQL Server 2012 SP1: the Service Pack 1 for SQL Server 2012 is available now and it enable the use of PowerPivot for SharePoint and Power View on a SharePoint 2013 installation with Excel 2013.
Power View works with Multidimensional cube: the long-awaited feature of being able to use PowerPivot with Multidimensional cubes has been shown by Amir Netz in an amazing demonstration during the keynote. The interesting thing is that the data model behind was based on a many-to-many relationship (something that is not fully supported by Power View with Tabular models). Another interesting aspect is that it is Analysis Services 2012 that supports DAX queries run on a Multidimensional model, enabling the use of any future tool generating DAX queries on top of a Multidimensional model. There are still no info about availability by now, but this is *not* included in SQL Server 2012 SP1.

So what about Mobile BI? Well, even if not announced during the keynote, there is a dedicated session on this topic and there are very important news in this area:

iOS, Android and Microsoft mobile platforms: the commitment is to get data exploration and visualization capabilities working within first half of 2013. This should impact at least Power View and SharePoint/Excel Services. This is the type of UI experience we are all waiting for, in order to satisfy the requests coming from users and customers. The important news here is that native applications will be available for both iOS and Windows 8 so it seems that Android will be supported initially only through the web. Unfortunately we haven’t seen any demo, so it’s not clear what will be the offline navigation experience (and whether there will be one). But at least we know that Microsoft is working on native applications in this area. I’m not too surprised that HTML5 is not the magic bullet for all the platforms. The next PASS Business Analytics conference in 2013 seems a good place to see this in action, even if I hope we don’t have to wait other six months before seeing some demo of native BI applications on mobile platforms!
Viewing Reporting Services reports on iPad is supported starting with SQL Server 2012 SP1, which has been released today. This is another good reason to install SP1 on SQL Server 2012.

If you are at PASS Summit 2012, come and join me, Alberto Ferrari and Chris Webb at our book signing event tomorrow, Thursday 8 2012, at the bookstore between 12:00pm and 12:30pm, or follow one of our sessions!

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:

Analysis Services Tabular books #ssas #tabular

sqlbi — Mon, 09 Jul 2012 12:10:00 GMT

Many people are looking for books about Analysis Services Tabular. Today there are two books available and they complement each other:

Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model by Marco Russo, Alberto Ferrari and Chris Webb
Applied Microsoft SQL Server 2012 Analysis Services: Tabular Modeling by Teo Lachev

The book I wrote with Alberto and Chris is a complete guide to create tabular models and has a good coverage about DAX, including how to use it for enriching a semantic model with calculated columns and measures and how to use it for querying a Tabular model. In my experience, DAX as a query language is a very interesting option for custom analytical applications that requires a fast calculation engine, or simply for standard reports running in Reporting Services and accessing a Tabular model. You can freely preview the table of content and read some excerpts from the book on Safari Books Online. The book is in printing and should be shipped within mid-July, so finally it will be very soon on the shelf of all the people already preordered it!

The Teo Lachev’s book, covers the full spectrum of Tabular models provided by Microsoft: starting with self-service BI, you have users creating a model with PowerPivot for Excel, publishing it to PowerPivot for SharePoint and exploring data by using Power View; then, the PowerPivot for Excel model can be imported in a Tabular model and published in Analysis Services, adding more control on the model through row-level security and partitioning, for example. Teo’s book follows a step-by-step approach describing each feature that is very good for a beginner that is new to PowerPivot and/or to BISM Tabular. If you need to get the big picture and to start using the products that are part of the new Microsoft wave of BI products, the Teo’s book is for you.

After you read the book from Teo, or if you already have a certain confidence with PowerPivot or BISM Tabular and you want to go deeper about internals, best practices, design patterns in just BISM Tabular, then our book is a suggested read: it contains several chapters about DAX, includes discussions about new opportunities in data model design offered by Tabular models, and also provides examples of optimizations you can obtain in DAX and best practices in data modeling and queries.

It might seem strange that an author write a review of a book that might seem to compete with his one, but in reality these two books complement each other and are not alternatives. If you have any doubt, buy both: you will be not disappointed! Moreover, Amazon usually offers you a deal to buy three books, including the Visualizing Data with Microsoft Power View, another good choice for getting all the details about Power View.

Many-to-many in #BISM Tabular issue in #crescent and #powerview

sqlbi — Tue, 18 Oct 2011 17:06:37 GMT

If you are looking to create many-to-many relationships based models in PowerPivot and in BISM Tabular, probably one day you will be interested in using them with Power View (formerly codename “Crescent”). There is a very bad news for you: it appears that, at least in its first release, Power View will not support this scenario, showing a behavior that (and this really worries me) is very different from Excel.

The description of the issue is in this connect item: please vote it! This is going to seriously impact usability of many models from Crescent. I understand the reason of this choice (avoiding selection that doesn’t make sense) but I would like whether there was a way to disable this automatic hiding feature in particular conditions (that might be the routine for a certain data models).

I will investigate for some workaround, but many votes to this issue may raise its priority in Redmond.