Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Archive for February 2013

Public Preview of Data Explorer

with 7 comments

The Public Preview of Data Explorer (which some of you know I’ve been following for a while, since it first appeared in SQL Azure Labs), is now available for download. You can get it here:
http://www.microsoft.com/en-us/download/details.aspx?id=36803

There’s also a good video overview here:

Data Explorer

In a nutshell, Data Explorer is self-service ETL for the Excel power user – it is to SSIS what PowerPivot is to SSAS. In my opinion it is just as important as PowerPivot for Microsoft’s self-service BI strategy.

I’ll be blogging about it in detail over the coming days (and also giving a quick demo in my PASS Business Analytics Virtual Chapter session tomorrow), but for now here’s a brief list of things it gives you over Excel’s native functionality for importing data:

  • It supports a much wider range of data sources, including Active Directory, Facebook, Wikipedia, Hive, and tables already in Excel
  • It has better functionality for data sources that are currently supported, such as the Azure Marketplace and web pages
  • It can merge data from multiple files that have the same structure in the same folder
  • It supports different types of authentication and the storing of credentials
  • It has a user-friendly, step-by-step approach to transforming, aggregating and filtering data until it’s in the form you want
  • It can load data into the worksheet or direct into the Excel model

There’s a lot to it, so download it and have a play! It’s supported on Excel 2013 and Excel 2010 SP1.

UPDATE: Check out the following blogs/links for Data Explorer:

http://blogs.msdn.com/b/dataexplorer/archive/2013/02/27/announcing-microsoft-data-explorer-preview-for-excel.aspx

http://social.msdn.microsoft.com/Forums/en-US/dataexplorer/

http://office.microsoft.com/en-us/excel-help/learn-about-data-explorer-formulas-HA104003958.aspx?CTT=5&origin=HA104003813

http://blogs.msdn.com/b/mllopis/archive/2013/02/28/get-microsoft-quot-data-explorer-quot-preview-for-excel-today.aspx

Written by Chris Webb

February 27, 2013 at 6:04 pm

Posted in Data Explorer

Bringing Location Information Into Excel 2013 With The WebService() Function

with 2 comments

Last summer I wrote a post about the new WebService() function in Excel 2013 and showed how it could be used to display data from the BBC Weather web service in a worksheet. I hadn’t thought about it much after that, but the other day I was working with a customer who wanted to work out how far people had to travel to visit a location and I wondered whether this was something that could be achieved with the WebService() function. It turns out that it can be – once you’ve worked around some of the quirks of WebService() – so I thought I’d post a couple of worked examples of how to use Bing Maps and Google Maps REST APIs to solve this kind of problem in Excel.

Let’s start with Bing, because after all I’m still a Microsoft loyalist and it does get some good reviews! Before you can use the Bing Maps REST API you need to create an account key (which is free), which you can do here:
https://www.bingmapsportal.com
You’ll need to use this in all your API calls. Now, what can we do with the Bing Maps REST API? Well, lots of things as you can see here:
http://msdn.microsoft.com/en-us/library/ff701713.aspx
Geocoding (finding a latitude and longitude for an address so you can plot it on a map) is a popular thing to do, but actually in Excel it’s not so important because all of the options for mapping in Excel 2013 such as Power View, GeoFlow (Jason Thomas did a good post on it recently) and the Bing Maps Excel 2013 app, all do it for you automatically. So I decided to show how to do reverse geocoding instead – looking up an address from a given latitude and longitude – which is also possible with the Bing Locations API. Here’s how:

image

In cell C3 I have pasted my Bing Maps API key. C5 and C6 contain the latitude and longitude I want to search for. B8 contains the formula with the WebService() function that calls the API and returns the result as XML:
=WEBSERVICE("http://dev.virtualearth.net/REST/v1/Locations/" & C5 & "," & C6 & "?o=xml&key=" & C3)

C10 uses the FilterXML() function to retrieve the address from the XML:
=FILTERXML(B8, "/Response/ResourceSets/ResourceSet/Resources/Location/Name")

Easy-peasy. Another common problem is that one I mentioned earlier about calculating the distance between two points. There are a few examples out there of how to calculate direct distances (like this one I saw halfway through writing this post), but direct distances aren’t all that useful – what you really need is the time needed to drive or walk somewhere along the available roads. Bing has a web service that provides route information and it works perfectly, but I found that the WebService() function returned errors in some cases – I suspect because the XML returned by Bing was too long, but I’m not sure. Anyway, Google has a similar API (with no key needed, at least for light usage) that returns only distances and not detailed routes, and WebService() seems to like it better. Here’s an example:

image

Here, I’m entering a start address and an end address in cells C2 and C3, and then calling the Google Distance Matrix API as follows:

=WEBSERVICE("http://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & ENCODEURL(C2) & "&destinations=" & ENCODEURL(C3) & "&mode=driving&sensor=false")

Then, I’m using FilterXML() to get the text of the driving distance and duration in cells C7 and C8 from the XML returned:

=FILTERXML(B5, "/DistanceMatrixResponse/row/element/distance/text")

=FILTERXML(B5, "/DistanceMatrixResponse/row/element/duration/text")

You can download a sample workbook that contains both these examples, minus my Bing Maps API key, here.

One final thing to mention is that the WebService() function seems to be horrendously slow, so you might not be able to use it to make a large number of calls very quickly, although many of these services allow you to make multiple lookups in a single API call. Also, remember that there’s usually a limit on the number of calls you can make to these APIs without paying.

Written by Chris Webb

February 26, 2013 at 9:07 pm

Posted in Excel

Tagged with

MDX on Cloudera Impala

with 5 comments

You may have seen today’s announcement (and comment from Mary-Jo Foley) on the official SQL Server blog about HortonWorks Data Platform for Windows; I won’t repeat what it says, but it’s clear Microsoft is taking Hadoop and Big Data (yuk, I hate that term, but sometimes I can’t avoid using it…) very seriously. However, yesterday I saw another, very interesting announcement from the guys at Simba that presents an alternative vision for Microsoft BI and Big Data. You can see a demo of it here:

Simba MDX Provider for Impala

There are some more details here:
https://groups.google.com/a/cloudera.org/forum/?fromgroups=#!topic/impala-user/BniN1ELYNDs

Basically, Simba have built an OLEDB for OLAP Provider that translates the MDX generated by Excel PivotTables into HiveQL queries against Cloudera Impala. You can read a good, short overview of what Impala is here:
http://blog.cloudera.com/blog/2012/10/cloudera-impala-real-time-queries-in-apache-hadoop-for-real/
and there’s more detailed information here:
https://ccp.cloudera.com/display/IMPALA10BETADOC/Impala+Frequently+Asked+Questions

In summary, what this gives you is fast, interactive analysis from within Excel going direct against large amounts of data stored via Impala, with no need for users to have to write Hive queries or stage the data in PowerPivot in the way the current Microsoft/Hortonworks solution does. Even more interesting is the fact that Simba support MDX calculated members and not just MDX queries, so given that Excel 2013 allows you to define your own calculated members you could do some very powerful analysis this way. Well, those of us who know some MDX, at least :-)

Impala is open source and isn’t shy about the debt it owes to Google’s Dremel, which of course is available publicly now as BigQuery. When I first saw BigQuery I thought putting an MDX interface over the top would make it appealing to a much wider audience – maybe not a good thing for Microsoft, but it would be a clever move on the part of Google certainly. Microsoft hasn’t announced that it’s working on anything comparable to BigQuery, alas; already, BI tools like Tableau and BIME can connect to and query BigQuery (Tableau also connects to Amazon’s new Redshift database too), and it’s these tools that are Excel’s big competitors in the BI client tool space. I guess SSAS 2012 Tabular in DirectQuery mode going against PDW would be the only vaguely similar Microsoft solution, but PDW is on-prem only and pretty expensive. Translating MDX to the SQL used by tools like Impala, in the way that the Simba MDX Provider does, puts Excel on a more equal footing with Tableau et al. I don’t know how Simba/Cloudera will be making this MDX Provider available but I would be surprised if they didn’t charge for it; Microsoft’s close relationship with Hortonworks, a competitor to Cloudera, makes me think that Microsoft might not want to promote this particular tool either, which is a shame. Maybe an acceptable solution for MS would be to build new cartridges for SSAS 2012 and enable DirectQuery for data sources other than SQL Server? It’s unlikely to happen, I think, but it would be an option.

UPDATE: While I was writing this post, Cathy Dumas of Simba (you were wondering what happened to her, weren’t you?) also blogged about this

http://blogs.simba.com/simba_technologies_ceo_co/2013/02/demo-microsoft-excel-pivottables-on-cloudera-impala-via-simba-mdx-provider.html

Written by Chris Webb

February 25, 2013 at 11:38 pm

Posted in Excel, MDX

Tagged with

Counting Consistent Customers in MDX

with 4 comments

The post I wrote a few years ago on counting new and returning customers in MDX has proved to be one of the most popular here; it’s certainly a topic I’ve returned to a number of times for DAX, and other people (most recently Gerhard Brueckl) seem equally interested in solving this problem. However I had a comment from Sohrab Niramwalla yesterday which raised an interesting question: how do you could the number of customers who have bought from you in every time period from the beginning of time until the current date?

The Customer dimension in Adventure Works isn’t actually very good for illustrating this because customers very rarely buy more than once, but if you look at Countries then you can recreate the scenario. Consider the following query:

select
{[Measures].[Internet Sales Amount]} *
[Customer].[Country].[Country].members on 0,
[Date].[Date].[Date].members
on 1
from [Adventure Works]

image

From the screenshot you can see that on July 1st 2001 there were purchases in 4 out of 6 countries; of those 4 countries, only 2 (Australia and USA) had purchases on July 2nd; both of those had purchases on July 3rd; but by July 4th only USA had seen purchases on every day since the beginning of time.

How then is is possible to get this count of the number of countries that have seen purchases since the beginning of time? I can think of a few approaches. One would involve using recursion and strtoset/settostr, but I generally like to avoid recursion and strtoset because performance can be unpredictable and bad. Probably the best way is to think of the problem like this: if a country has had sales on every day since the beginning of time, then count of days that had sales since the beginning of time will be the same as the total count of days since the beginning of time. Therefore, you can write a query something like this:

with
–count the number of days since the beginning of time
member measures.daycount as
count(null:[Date].[Date].currentmember)

–count the number of non empty days for sales since the beginning of time
member measures.nonemptydaycount as
count(
nonempty(
null:[Date].[Date].currentmember
, [Measures].[Internet Sales Amount])
)

–count the number of countries that have nonemptydaycount = daycount
member measures.[Consistent Customers] as
count(
filter(
[Customer].[Country].[Country].members
, measures.nonemptydaycount = measures.daycount)
)
select measures.[Consistent Customers] on 0,
head([Date].[Date].[Date].members, 15) on 1
from [Adventure Works]

image

Mosha’s technique for optimising count(filter()) calculations might also be useful here, although I have to admit I’ve not tested it to see if it does improve performance.

However, for those of you who are fans of obscure MDX, here’s another solution:

with
member measures.[Consistent Customers] as
count(
–iterate over every date since the beginning of time
generate(
{null : {{[Date].[Date].currentmember} as currentdate}.item(0)}
,
{
–if the current date in the iteration is the first date
iif([Date].[Date].currentmember is [Date].[Date].[Date].members.item(0)
,
–then define the inline named set customerset as
–all the nonempty customers
intersect(
nonempty(
[Customer].[Country].[Country].members
, [Measures].[Internet Sales Amount]) as customerset
, {})
,
–else, redefine the set customerset as the intersection of
–customerset and the nonempty countries in the current time period
intersect(
intersect(
customerset
, nonempty(
[Customer].[Country].[Country].members
, [Measures].[Internet Sales Amount])
) as customerset
, {})
)
–note that the intersect function is used to ensure only an empty
–set is ever returned from these expressions
,
–if the current date in the iteration is the current time period
–ie we are at the final iteration
iif([Date].[Date].currentmember is currentdate.item(0)
–then return the contents of the named set customerset
, customerset
, {})
}
))

select {measures.[Consistent Customers]} on 0,
head(
[Date].[Date].[Date].members
, 15)
on 1
from [Adventure Works]

 

It uses the generate() function to loop over ever date from the beginning of time to the current time period, and then redefines an inline named set (called customerset) on every step of the iteration to find the non empty countries. It doesn’t perform as well as the previous solution in this particular case, but if there were more countries that might change; a few tweaks to the code might also speed it up. I thought it was worth mentioning, though, for the novelty value.

Written by Chris Webb

February 24, 2013 at 9:25 pm

Posted in Analysis Services, MDX

DatabaseDays SQL Server Conference in Switzerland

leave a comment »

Apart from all the public training courses I’m running in London this year, I’m also going to be running pre-conference seminars (as well as speaking) at SQL Server conferences outside the UK. The first of these will be at the DatabaseDays conference in Switzerland, which will be taking place at the Trafo Conference Centre in Baden, not far from Zurich, on the 17th-19th April. My pre-conference seminar will be a one-day Introduction to MDX,  and other speakers at the conference include Jen Stirrup, Stacia Misner and various members of the SQLCat team; you can see the the full list of conference sessions here. I love visiting Switzerland (I lived there for a few years a while back) so I’m really looking forward to it!

Written by Chris Webb

February 22, 2013 at 9:34 am

Posted in Events

Dynamic DAX Query Tables in Excel 2013

with 9 comments

PivotTables are all well and good, but sometimes when you’re building reports you just want a plain old list of things. Excel tables are perfect for this, and in Excel 2013 you can bind a table to the results of a static DAX query against the Excel Data Model. Unfortunately it’s not possible to make this query dynamic without a bit of VBA – so in this post I’ll show you how to do it.

Before I start, though, you may be thinking “What’s the point of this?”. After all, if you have too much data for the native Excel table functionality to handle, you can always use the Excel Data Model and make a PivotTable look just like a table, and when you do that you can use filters, slicers and so on to control what gets displayed. This is certainly a valid approach but the big disadvantage of a PivotTable is that it doesn’t always give you the best possible performance because of the way it generates its MDX, and because DAX queries are anyway faster than MDX queries for this kind of detail-level reporting. For large tables with lots of columns then a hand-rolled DAX query might give you significantly better performance than a PivotTable, as well as more control over the filtering logic.

Let’s look at a worked example…

Step 1: Import some data into a table

For my example, I have imported the DimDate table from the Adventure Works DW database in SQL Server into a table in Excel.

image

The key thing to remember at this point is to make sure you check the box to add the data to the Excel Data Model:

image

Step 2: Define a DAX query for this table

Kasper shows here how to use a static DAX query to populate a table in Excel, so I won’t repeat what he says. All I’ve done in my example is to change the table to use the following DAX query:

evaluate DimDate

…which returns the whole contents of the DimDate table, so in fact at this point the table looks exactly the same as it did before I made this change.

image

image

Step 3: Add some UI to allow the user to filter the data

Now I want the user to be able to filter this table in two ways:

1. By using a slicer to control which days of the week are displayed

2. By entering a value into a cell, and filtering the table so only the rows where the day number of the month is greater than that value

Here’s what this looks like:

image

I’ve also added a ‘Run Report’ button onto the worksheet for the user to press when they want to refresh the data in the query

Step 4: Use VBA to dynamically generate the query used by the table

The challenge is now to take the selection in the slicer and the value entered for the day number of month filter and use that to construct a DAX query.

Here’s an example of what one of these DAX queries might look like:

evaluate
Filter(
DimDate
, DimDate[DayNumberOfMonth]>21
&& (DimDate[EnglishDayNameOfWeek]=”Monday” || DimDate[EnglishDayNameOfWeek]=”Saturday”))
order by DimDate[DateKey]

Here I’m filtering the DimDate table so that the only rows displayed are where day number of month is greater than 21, and day name of week is either Monday or Saturday. If you’re interested in learning more about writing DAX queries, check out the series of blog posts I wrote on this topic here.

Paul te Braak has a great post here on how to work out what has been selected in a slicer using VBA, and I need to acknowledge the fact I’ve borrowed some of his code! Here’s my VBA routine, called by the button on the worksheet, to build and run the query:

Sub RunReport()
    Dim SC As SlicerCache
    Dim SI As SlicerItem
    Dim SelectedList As String
    Dim DayNumberOfMonthFilter As String
    Dim DAXQuery As String
    Dim DemoWorksheet As Worksheet
    Dim DAXTable As TableObject
    Set DemoWorksheet = Application.Worksheets("TableDemo")
    'Find the value of the cell containing the Day Number Of Month filter value
    DayNumberOfMonthFilter = DemoWorksheet.Range("DayNumberOfMonthFilter").Value
 
    'Find what is selected in the slicer Slicer_EnglishDayNameOfWeek
    Set SC = ActiveWorkbook.SlicerCaches("Slicer_EnglishDayNameOfWeek")
    SelectedList = ""
 
    'Loop through each item in the slicer and if it is selected
    'add it to a string that will be used in the filter condition
    For Each SI In SC.SlicerCacheLevels(1).SlicerItems
        If SI.Selected Then
            If SelectedList <> "" Then
                SelectedList = SelectedList & " || "
            End If
            SelectedList = SelectedList & "DimDate[EnglishDayNameOfWeek]=""" & SI.Caption & """"
        End If
    Next
    'Construct the DAX query
    DAXQuery = "evaluate Filter(DimDate, DimDate[DayNumberOfMonth]>" & DayNumberOfMonthFilter
    DAXQuery = DAXQuery & " && (" & SelectedList & ")) order by DimDate[DateKey]"
    'Bind the table to the DAX query
    Set DAXTable = DemoWorksheet.ListObjects("Table_DimDate").TableObject
    With DAXTable.WorkbookConnection.OLEDBConnection
        .CommandText = Array(DAXQuery)
        .CommandType = xlCmdDAX
    End With
 
    'Run the query
    ActiveWorkbook.Connections("ModelConnection_DimDate").Refresh
End Sub

 

And so there we go, a dynamic DAX table report in Excel 2013. If you’d like to download my example and check it out in detail, you can get hold of it here.

Written by Chris Webb

February 15, 2013 at 10:24 pm

TopCounts With Ties In MDX

leave a comment »

Pretty much everyone that knows MDX knows the TopCount() function, which is used to find the top n items in a set – it can be used for finding your top 10 products, your top 20 salespeople, and so on. However most people don’t consider it’s biggest drawback: it always returns n items, and doesn’t take ties into account.

Consider the following query on Adventure Works, which returns the top 9 dates by Internet Order Count:

SELECT {[Measures].[Internet Order Count]} ON 0,
TOPCOUNT(
[Date].[Date].[Date].MEMBERS
, 9
,[Measures].[Internet Order Count])
ON 1
FROM [Adventure Works]

image

Now look at this query, which does the same thing but returns the top 10 dates:

SELECT {[Measures].[Internet Order Count]} ON 0,
TOPCOUNT(
[Date].[Date].[Date].MEMBERS
, 10
,[Measures].[Internet Order Count])
ON 1
FROM [Adventure Works]

image

Notice how June 11 2004 and June 17 2004 both have the same Internet Order Count of 86, but the latter date isn’t included in the first query. From this you can see that it’s important, when you’re doing a top n query, not to always return n items but to take tied values into account.

Luckily there’s a way of doing this. If you can download and install the dll from the Analysis Services Stored Procedure Project, you can use the TopCountWithTies() function that it provides, like so:

SELECT {[Measures].[Internet Order Count]} ON 0,
ASSP.TOPCOUNTWITHTIES(
[Date].[Date].[Date].MEMBERS
, 9
,[Measures].[Internet Order Count])
ON 1
FROM [Adventure Works]

image

Unfortunately, installing third-party dlls is not always allowed in a production environment, and SSAS 2012 Tabular doesn’t support dlls at all. However there is a way of getting the same result in pure MDX. Here’s an example:

WITH
SET TOP9 AS
TOPCOUNT(
[Date].[Date].[Date].MEMBERS
, 9
,[Measures].[Internet Order Count])
MEMBER MEASURES.TIEDRANK AS
RANK(
[Date].[Date].CURRENTMEMBER
, TOP9
, [Measures].[Internet Order Count])
SET TOP9WITHTIES AS
ORDER(
FILTER(
[Date].[Date].[Date].MEMBERS
, MEASURES.TIEDRANK>0 AND MEASURES.TIEDRANK<=9)
, MEASURES.TIEDRANK
, BASC)

SELECT {[Measures].[Internet Order Count], MEASURES.TIEDRANK} ON 0,
TOP9WITHTIES
ON 1
FROM [Adventure Works]

image

What I’m doing here is first using the TopCount() function to find the set of the top 9 dates (this step isn’t strictly necessary, but it has a significant positive impact on performance), and then using the Rank() function with the third parameter to find the tied rank. The Rank() function should find the position of a member in a set, but when the third parameter for it is specified it doesn’t match members based on the members themselves but on the values of the tuple specified in the third parameter. Hence, even when I have  a date that doesn’t appear in the set TOP9, the Rank() function can still return a value for it if that date has a value for Internet Order Count that does appear in that set.

Written by Chris Webb

February 9, 2013 at 11:05 am

Posted in MDX

Office 2013, Office 365 Editions and BI Features

with 30 comments

By now you’re probably aware that Office 2013 is in the process of being officially released, and that Office 365 is a very hot topic. You’ve probably also read lots of blog posts by me and other writers talking about the cool new BI functionality in Office 2013 and Office 365. But which editions of Office 2013 and Office 365 include the BI functionality, and how does Office 365 match up to plain old non-subscription Office 2013 for BI? It’s surprisingly hard to find out the answers…

For regular, non-subscription, Office 2013 on the desktop you need Office Professional Plus to use the PowerPivot addin or to use Power View in Excel. However there’s an important distinction to make: the xVelocity engine is now natively integrated into Excel 2013, and this functionality is called the Excel Data Model and is available in all desktop editions of Excel. You only need the PowerPivot addin, and therefore Professional Plus, if you want to use the PowerPivot Window to modify and extend your model (for example by adding calculated columns or KPIs). So even if you’re not using Professional Plus you can still do some quite impressive BI stuff with PivotTables etc. On the server, the only edition of Sharepoint 2013 that has any BI functionality is Enterprise Edition; there’s no BI functionality in Foundation or Standard Editions.

[For those of you thinking of upgrading from Excel 2010 PowerPivot to Office 2013, Marco has all the details on compatibility of PowerPivot workbooks across different versions here.]

Office RT, which runs on Windows RT, has several limitations on its BI functionality: there’s no PowerPivot, Power View or Excel Data Model. Luckily, Kasper has summarised what it does do in a blog post here, so I won’t repeat what he says.

Moving on to 2013 functionality in Office 365, and specifically BI in Sharepoint Online, things get more complicated. Although feature support information for Office 365 is on Technet here, the best place to start is Andrew Connell’s blog post and corresponding feature matrix that is viewable through (appropriately enough) the Excel Web App. The feature matrix makes it very easy to filter Office 365 features by workload so you only see the BI-related ones:

image

image

As you can see, the short answer is that you need either Office 365 E3 or E4, or SharePoint Online Plan 2 to get BI functionality. The Office Professional PlusE3 and E4 plans are also the only plans to include subscriptions to the desktop versions of Office Professional Plus, and they allow it to be installed on up to 5 machines per user. The other thing you’ll notice is that PerformancePoint is not available at all in Office 365 (read into that what you will); it is of course available in Sharepoint 2013 Enterprise Edition on-premises.

There are other functionality differences between Sharepoint Online in Office 365 and on-premises Sharepoint too. The details are here, but the important ones are:

  • At least for the moment, Excel workbooks can be no larger than 10MB
  • The Excel Data Model will only refresh successfully if it sources data from the workbook itself; no external data sources are supported (though again I’d be surprised if that restriction isn’t lifted in the future)
  • There is no PowerPivot for Sharepoint functionality such as the Gallery, usage reporting or scheduled data refresh.

These are quite significant restrictions, it’s true, but if you are a purely self-service BI shop and you just want to use Sharepoint Online to publish PivotTable or Power View reports that don’t need to be refreshed (or can be refreshed manually on the desktop and then uploaded) then this functionality should be sufficient. This is the kind of scenario I showed here, and I think a lot of customers with no existing BI will still be impressed with it; obviously it’s a problem if you want to do any kind of corporate BI.

BUT. At the time of writing the Enterprise plans for Office 365 haven’t been fully updated for Office 2013 functionality, so all this BI functionality isn’t actually available yet to most subscribers. This means that the desktop versions of Office you can download are still 2010 and not 2013; online, while you can get the latest Sharepoint features if you’re part of the Office 365 Preview, if you’re currently an Office 365 subscriber you’re probably still on Sharepoint 2010. The official line on when the upgrade to 2013 functionality will take place is a bit vague – it will take place “in the course of 2013” – and there seem to be a few upset customers out there (see here for example). February 27th seems to be a significant date.

Finally, apart from Office 365 it’s also possible to view Excel workbooks via SkyDrive. However pretty much no BI functionality is available when you do this: no Excel Data Model, no external connections, no Power View, just the ability to view (and not alter) PivotTables. These restrictions seem to be more or less the same if you use just the Office Web Apps server on-premises without Sharepoint 2013 – see the relevant table here for details.

In summary: my head hurts! All these editions and licences… it would be nice if it was less complicated.

UPDATE: some new information, and some clarifications, since I first wrote this post

1) Office Professional Plus 2013 will be available via Office 365 on February 27th 2013. The cheapest subscription option that includes Excel on the desktop with PowerPivot and Power View is, as far as I can see, this one, an Office Professional Plus subscription, that is included in the E3 and E4 plans.

2) Office Professional Plus is only available via Open, Select or EA licensing (see http://www.microsoft.com/en-gb/licensing/default.aspx for more details on what these options are). Excel 2013 standalone is only available via Open or Select. This means that no regular retail editions of Excel include PowerPivot or Power View, you can only get them through a Volume Licence Agreement or Office 365 (ie you need to be working for a big company with deep pockets unless you buy yourself an Office Professional Plus, E3 or E4 Office 365 subscription); compare this with PowerPivot for Excel 2010 worked with any edition of Excel. Existing PowerPivot users are not particularly happy about this when they find out: see here and here for example. Is this a good strategy? Hmm…

3) Right now, I’m told there is a problem with how the addins are packaged with Excel 2013 standalone which will be addressed in a future update.

UPDATE  2: I’ve just found out that standalone Power View is not supported at all in Sharepoint Online/Office 365. Only Power View sheets inside Excel workbooks are supported.

UPDATE 3: Power Pivot is now available in standalone versions of Excel too as of August 2013 – http://www.powerpivotblog.nl/power-pivot-and-power-view-now-available-in-excel-stand-alone

UPDATE 4: This blog does not cover the BI features that are available in Power BI. A Power BI subscription is an add-on to an Office 365 subscription and gives you extra functionality. You can find out about the licensing here and I’ve blogged about what it gives you here, here and here.

Written by Chris Webb

February 1, 2013 at 8:04 am

Posted in BI, Office 2013, Office 365

Follow

Get every new post delivered to your Inbox.

Join 3,082 other followers