Chris Webb's BI Blog

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

Archive for the ‘Excel’ Category

GeoFlow Public Preview Available

with 8 comments

First big news from the PASS BA Conference: the public preview for GeoFlow is now available. You can download it here:
http://www.microsoft.com/en-us/download/details.aspx?id=38395

Here are the official announcements with all the details:
http://blogs.technet.com/b/dataplatforminsider/archive/2013/04/11/day-2-pass-business-analytics-conference-new-3d-mapping-analytics-tool-for-excel.aspx
http://blogs.office.com/b/microsoft-excel/archive/2013/04/11/dallas-utilities-electricity-seasonal-use-simulation-with-geoflow-preview-and-powerview.aspx

GeoFlow is an addin for Excel 2013 that allows you to visualise your data on a 3D map, to zoom in and explore that data, and record ‘tours’ of this data. It’s a lot of fun! As a taster, here’s a screenshot of a visualisation showing English secondary schools exam results data (average A-Level point score per pupil) broken down by school gender of entry:

image

UPDATE: one other thing I have to mention is that when this was announced in the keynote at the PASS BA Conference this morning, Amir Netz did an absolutely astounding demo showing GeoFlow’s touch-based capabilities running on a massive Perceptive Pixel screen (I think it was this one: http://www.perceptivepixel.com/products/82-lcd-multi-touch-display). It was possibly the most impressive demo I’ve seen of any Microsoft BI product. Anyway, I got to play on it myself later and it was as cool as it looked. If you’ve got $80000 burning a hole in your pocket then you could do worse than invest in one of these babies.

Written by Chris Webb

April 11, 2013 at 2:07 pm

Posted in Excel, GeoFlow

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

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

Technitrain 2013 Course Schedule: SSAS, PowerPivot, MDX, SSIS, TSQL and SQL Server Engine Training in London

with one comment

As you probably know, when I’m not blogging or wrestling with SSAS I run a small SQL Server training company here in the UK called Technitrain. If you’ve got some training budget to spare and London is convenient for you, you might want to check out the 2013 course schedule below:

As you can see, I’m doing a fair bit of teaching myself but I’m also proud to have Thomas Kejser, Allan Mitchell, Dave Ballantyne, Bob Phillips and Andy Leonard teaching courses for me too. The aim is to deliver reasonably-priced, expert-level Microsoft BI and SQL Server training of the sort the regular training companies don’t provide; it’s basically the kind of training you’d get at a SQLBits or SQL Saturday pre-con but in a more traditional classroom environment and not limited to one day. If you’re reading my blog, you’re my target audience for these courses – so I hope to see you at one of them this year!

Written by Chris Webb

January 7, 2013 at 11:14 pm

Excel GeoFlow

with 7 comments

Here’s a second example of Microsoft making a big BI-related announcement at the Sharepoint Conference and not PASS, and so ensuring that no-one in the Microsoft SQL Server BI community hear about it… Excel GeoFlow. It’s an Excel addin for geospatial analysis that is closely integrated with PowerPivot and looks very similar to Layerscape, but properly integrated with Excel and PowerPivot. So far I’ve only found two sources of information on it – Jen Underwood’s blog post:
http://www.jenunderwood.com/blog.htm#PASSandSPC2012
…and, this very detailed post from Patrick Guimonet (in French), which has a lot of screenshots and several long videos shot during the Sharepoint Conference:
http://blogs.codes-sources.com/patricg/archive/2012/11/16/spc12-spc258-geoflow-for-excel-2013-a-new-way-of-exploring-geospatial-data-and-sharing-insights.aspx

If you thought maps in Power View were impressive, just check this out…

Written by Chris Webb

November 29, 2012 at 8:59 pm

Posted in Excel, GeoFlow

Returning Selected Items in an Excel Slicer Using MDX in PowerPivot and SSAS

with 15 comments

One problem I came up against recently is how to find out what has been selected on an Excel slicer connected to SSAS or a PowerPivot model. There are a number of blog posts showing solutions to this problem, both for scenarios where only one item has been selected and when multiple items have been selected, for example (look at the comments as well as the posts themselves):
http://www.powerpivotpro.com/2010/12/another-way-to-get-and-use-slicer-values-in-formulas/
http://www.powerpivotpro.com/2011/09/catching-multiple-slicer-selections-in-a-formula/

…but I’ve come up with a new, MDX-based approach that handles the multiple selection scenario no matter how many items there are in the slicer, and which I thought was worth blogging about. I was tempted to include this in my series on MDX for PowerPivot but since it’s equally relevant for SSAS users, and the MDX is a bit complicated for an introductory series, I thought I’d make it into a standalone post.

To describe this technique I’m going to use same PowerPivot model I describe here, which is based on the AdventureWorks DW sample database. First of all, create a new PivotTable with CalendarYear in a slicer and CalendarYear on rows and any relevant measure on columns:

image

Selecting a Year in the slicer means that that Year appears on rows in the PivotTable, and selecting multiple years means multiple rows are displayed, as you would expect. Next you need to add a new MDX calculated measure to the PivotTable. If you’re using SSAS you can simply add the calculation onto your cube or you can use the OLAP PivotTable Extensions addin; for PowerPivot users the OLAP PivotTable Extensions approach is the only option, so that’s what I’ll demonstrate. Right-click inside the PivotTable and choose OLAP PivotTable Extensions from the right-click menu, and then in the Calculations tab create a new calculation called SelectedYears with the following definition:

Generate(
Except(
  Axis(1)
  , {[DimDate].[CalendarYear].Defaultmember}
)
, [DimDate].[CalendarYear].Currentmember.Name
, ", "
)

image

…and click Add to PivotTable. The result will be a new measure that returns a comma-delimited list of the names of everything selected on the rows axis of the query, and therefore everything selected in the slicer:

image

How does this work? Well, first of all Axis(1) is used to return the set used on the rows axis of the query used to populate the PivotTable (incidentally, this is why it’s important to have another measure in the PivotTable – if it’s not there, the structure of the query generated by Excel will be different and while the MDX can be altered to handle this, some of the items in the slicer will be partially greyed-out). The set returned by Axis(1) will include the All Member of the CalendarYear hierarchy, so the Except() function is used to remove it; finally, the Generate() function is used to iterate over this set and return the comma-delimited list of member names. In this example the CalendarYear field on the DimDate table in my PowerPivot model has become the MDX hierarchy with the unique name [DimDate].[CalendarYear]; please read this post for some background on how PowerPivot objects map to MDX objects.

Now you have the PivotTable you can refer to the top-right cell in it (in this example it’s cell F3)  in it to return the list of years and hide the PivotTable itself; this allows you to create dynamic titles like the following:

="Sales For Years: " & F3

You can then also create other, new PivotTables and hook them up to the original slicer and they will all work as normal:

 image

Written by Chris Webb

October 22, 2012 at 7:44 pm

Introduction to MDX for PowerPivot Users, Part 3: The Members() and Crossjoin() functions

with 2 comments

In the previous post in this series I looked at how MDX set expressions could be used inside Excel to give you total control over what appears on the rows and columns axis of your PowerPivot PivotTables. However, I only showed how to construct basic MDX set expressions using members and tuples; in this post I’ll show you how to use two of the commonest MDX set functions: Members() and Crossjoin().

MEMBERS()

The Members() function returns the set of members from either an entire hierarchy or a single level from that hierarchy. I’d say it is by far the most widely-used of all MDX functions, even if a lot of the time people don’t realise they are using it (see here for why that is). As far as PowerPivot goes it’s not all that useful on its own – if you want to see all the members on a level or a hierarchy, it’s easy to do that without using named sets – but it is frequently used in conjunction with other set functions. Some examples:
[DimDate].[EnglishDayNameOfWeek].MEMBERS
…returns the set of all members on the EnglishDayNameOfWeek hierarchy, whose unique name is [DimDate].[EnglishDayNameOfWeek]. Remember that in PowerPivot, as I said in the first post in this series, a column in a table becomes a hierarchy in MDX and a hierarchy in PowerPivot also becomes a hierarchy in MDX; also that this expression will also return the All Member from the hierarchy, which means that this expression will return a Grand Total row:

image

Compare this with the results returned by the expression:
[DimDate].[EnglishDayNameOfWeek].[EnglishDayNameOfWeek].MEMBERS
Here I’m using the Members() function with the unique name of the EnglishDayNameOfWeek level on the EnglishDayNameOfWeek hierarchy, whose unique name is [DimDate].[EnglishDayNameOfWeek].[EnglishDayNameOfWeek]. I don’t want to go into too much detail about how this is different from the previous expression; for a PowerPivot user the thing to note is that the All Member is now not returned in the set, and there is no Grand Total row returned:

image

CROSSJOIN()

The Crossjoin() function takes two or more sets and returns a set of tuples representing all possible combinations of items in these sets. So, for example, the crossjoin of the two sets {A, B} and {X, Y} is the set of tuples {(A,X), (A,Y), (B,X), (B,Y)}. There are in fact several ways to write a crossjoin in MDX as I showed in this post, and I prefer to use the * operator over the Crossjoin() function because it’s less verbose. Here’s an example of two set expressions that return the same result using the Crossjoin() function and the * operator:

[DimDate].[EnglishDayNameOfWeek].[EnglishDayNameOfWeek].MEMBERS
*
[DimProductCategory].[EnglishProductCategoryName].[EnglishProductCategoryName].MEMBERS

…and…

CROSSJOIN(
[DimDate].[EnglishDayNameOfWeek].[EnglishDayNameOfWeek].MEMBERS
,
[DimProductCategory].[EnglishProductCategoryName].[EnglishProductCategoryName].MEMBERS
)

…and here’s the output:

image

Using Members() And Crossjoin() To Optimize PivotTables With Many Hierarchies On Rows And Columns

In this series I want to balance out each dose of theory with some useful practical tips, and here’s the first practical tip: when you have a PivotTable with many hierarchies on rows or columns, you may find that it takes a long time to refresh and that using a named set instead may help improve performance. This is because of a design flaw in the way Excel generates the MDX for PivotTables which means that even when you opt not to display subtotals and grand totals, Excel still requests some of them in the queries it runs against your PowerPivot model. This issue has been blogged about in detail several times by Rui Quintino, Richard Lees and me:
http://rquintino.wordpress.com/2010/10/25/excel-20072010-pivot-tables-getting-detailedgranular-table-reports-from-olap-in-seconds/
http://richardlees.blogspot.ch/2010/04/improving-excels-cube-performance.html
http://cwebbbi.wordpress.com/2011/10/07/excel-subtotals-when-querying-multidimensional-and-tabular-models/

So, for example, if you have put CalendarYear, EnglishDayNameOfWeek and EnglishProductCategoryName on rows in your PivotTable like so:

image

You can replace this with a named set with the following definition:

[DimDate].[CalendarYear].[CalendarYear].MEMBERS
*
[DimDate].[EnglishDayNameOfWeek].[EnglishDayNameOfWeek].MEMBERS
*
[DimProductCategory].[EnglishProductCategoryName].[EnglishProductCategoryName].MEMBERS

Here, I’m asking for the crossjoin of all the members (except the All Members) on the CalendarYear, EnglishDayNameOfWeek and EnglishProductCategoryName hierarchies and not requesting any subtotals or grand totals at all in the query. The larger the number of hierarchies on rows or columns in your PivotTable the more noticeable the improvement in performance will be when using this MDX pattern. The penalty for doing this, though, is that end users lose the ability to drill up or down or to alter the selection made without editing the MDX.

In part 4, I’ll take a closer look at how to filter sets.

Written by Chris Webb

October 21, 2012 at 10:42 pm

Posted in Excel, MDX, PowerPivot

Some thoughts on what Office 2013 means for Microsoft BI

with 21 comments

You may have seen the news late last week that Office 2013 has RTMed, which in itself isn’t that significant – it’s not going to be until mid-November that the likes of you or I can download it. But it’s a milestone and therefore a good time to think about what Office 2013 means for Microsoft BI as a whole.

Let me start by saying that I’ve spent a lot of time playing with Office 2013, especially Excel 2013, over the last few months and I’ve been very impressed with it. I think it’s a great product and also that it represents a significant turning point for Microsoft BI. I won’t summarise everything I’ve said in previous blog posts about new functionality (you can read those yourself!), but here are what I consider some of the important points to consider when assessing its impact:

  • Number 1 on the list of new features for BI has to be the way PowerPivot has been integrated into Excel. Indeed, although PowerPivot still exists as a separate addin, I’m not sure it’s particularly helpful to think of PowerPivot and DAX as something distinct from Excel any more – we should think of them as the native Excel functionality that they are. Maybe we shouldn’t even use the names PowerPivot and DAX at all any more? And of course, now that users will get it by default, it will open the way to much, much wider adoption. I’m working on a PowerPivot/Excel 2010 project at the moment where the customer’s desktops are locked down and it took several weeks to get PowerPivot installed on even a few desktops; with Excel 2013 those problems won’t occur.
  • The integration of Power View into Excel comes a close second in terms of significant new functionality. Like a lot of people I was impressed by the technology when I saw first saw the Power View in Sharepoint last year, but frankly the Sharepoint dependency meant none of my customers were even vaguely interested in using it and I thought it was stillborn. Putting Power View into Excel changes all this – it’s effectively giving it away to all corporate customers and, as with PowerPivot, this will remove a lot of barriers to adoption. It might not be as good at data visualisation as something like Tableau, but it doesn’t need to be – you’re going to get it anyway, it will do most of what these other tools do, so why bother looking at anything else?
  • The way PivotTables and Power View reports now work so well in the browser with Excel Services and the Excel Web app means that Excel should now be considered the premier web reporting and dashboarding solution in the Microsoft BI stack, and not just as something for the desktop. I’ve never been fond of PerformancePoint (and again I never saw significant uptake amongst my customers – indeed, over the years, I’ve seen it used only very rarely) and I see less and less reason to use it now when Power View does something similar. SSRS still has its own niche but even it will start to decline slowly because it will be so much easier for BI pros and end-users to build reports in Excel. This in turn will make the whole Microsoft BI stack much more comprehensible to customers and a much easier sell – Excel will be the answer to every question about reporting, data analysis, data visualisation and dashboards. 
  • Office 365 will help overcome the problems customers have with the Sharepoint dependency in the Microsoft BI stack. I discussed this problem at length here; having now used Office 365 on the Office Preview myself, I’m a convert to it. I’ve had Sharepoint installed on various VMs for years but it’s only now with Office 365 and freedom from the pain of installation and maintenance that I can start to appreciate the benefits of Sharepoint. For small companies it’s the only way Sharepoint can be feasible. More important than anything else, though, is the subscription pricing that has just been announced: Office 365 is a no-brainer from a cost point of view.  I saw recently that Toyota Motor Sales in the US have just decided to go to Office 365 and I wouldn’t be surprised if other, larger enterprises to do the same; this isn’t just something for SMEs.
  • The ability to stream Excel 2013 to desktops means that yet more barriers to deployment will be removed.
  • We’re still waiting for Microsoft’s mobile BI solution, of course. I hope it’s coming soon! Whatever form it takes, I would expect it to be very closely linked to Office 2013.

What do you think, though? I’m interested in hearing your comments – have I drunk too much Microsoft Kool-Aid?

Written by Chris Webb

October 14, 2012 at 11:07 pm

Posted in BI, Excel, Office 2013

Importing Azure Marketplace Data into Excel 2013 with Web Queries

with 8 comments

A few weeks ago, when I was playing around with the new WebService() function in Excel 2013, I was disappointed to learn that it didn’t work with services that require authentication – a pretty big limitation in my opinion – so, for example, it meant I couldn’t use it to import data from the Azure Marketplace into Excel. You might be wondering why I’d want to do this, when there’s already built-in functionality for importing Azure Marketplace data into Excel 2013 and an addin to do this for earlier versions of Excel; the reason is that I want to be able to dynamically construct the url used to call the service inside the spreadsheet. For example, in this post I’m going to be using the Bing Search API and I want to be able to enter the search term I pass to it in a cell in a worksheet rather than hard-code it in the connection, which is what happens when using the native functionality. I’m sure I could use some VBA to do the same thing but using VBA always seems like an admission of defeat to me, so in this post I’m going to show how you can use Excel Web Queries to do it instead.

So let’s start with the Bing Search API, one of the many APIs and datasets available via the Windows Azure Marketplace. There are plenty of posts explaining how to use the Azure Marketplace website to import data into PowerPivot (see here for example) so I won’t go into too much detail at this point, but here’s an example Url that returns the top 15 news results for the search term ‘Microsoft’:

https://api.datamarket.azure.com/Data.ashx/Bing/Search/v1/News?Query=%27microsoft%27&$top=15&$format=Atom

image

Next, you need to fire up Excel 2013 and create a Web Query, a feature that’s been in Excel since ohhhh, Office 2000. There are a lot of good articles on the web describing how this feature works but here’s one of the best I found:
http://www.vertex42.com/News/excel-web-query.html

Specifically, you need to go to the Data tab and click the From Web button:

image

This opens the New Web Query dialog; you then paste the Url from the Azure Marketplace into the Url box and click go. You’ll be prompted for a username and password, and you need to enter your Azure Marketplace account key for both. Click on the small yellow arrow in the top left hand corner of the browser window and you’ll see something like this:

image

Do not press Import at this point though! Instead, press the Save Query button in the toolbar (highlighted in the screenshot above) and then save the query to disk as an .iqy file and click Cancel. Next, find the .iqy file you’ve just saved and open it in Notepad. The contents will be something like this:

WEB
1
https://api.datamarket.azure.com/Data.ashx/Bing/Search/v1/News?Query=%27microsoft%27&$top=15&$format=Atom

Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

What you need to do now is to parameterise the Url in this file so that, instead of hard-coding it to search for news about microsoft (as in this example) you can enter your own search term. The link above describes how to do this in detail, but here’s the parameterised version of the Url for reference:

https://api.datamarket.azure.com/Data.ashx/Bing/Search/v1/News?Query=%27%5B"Query&quot;, "Search For:"]%27&$top=15&$format=Atom

You then need to save the file and go back to Excel.  First, enter a search term in a cell in a worksheet. Then, go to the Data tab, click the Existing Connections button and then the Browse For More button and select the .iqy file. The next step is to choose a cell in a worksheet to dump the data to, and then when prompted for the parameter value click on the cell where you entered your search term and check the Use This Value/Reference For Future Refreshes and Refresh Automatically When Cell Value Changes boxes.

image

Click OK and you’ll be prompted for credentials again – although Excel will remember them – and the query will run, resulting in some XML appearing in the cell you selected:

image

This is ugly, and indeed Excel has the ability to import XML returned from a Web Query as XML – but what I’ve found is that if you don’t use the approach I’m showing here, you won’t be able to refresh you query properly.

So you now have our search results as XML and you want to be able to display them in a more meaningful way. This is where the new FilterXML function in Excel 2013 comes into its own. Create an Excel table with four columns: Rank, Title, Description and Url; then enter numbers from 1 to 15 in the Rank column (there are fifteen results returned from the Bing search). You can then use Excel table formulas like this one to get the Title, Description and Url from the XML and display it in the table:

=FILTERXML($B$4, "//feed/entry[" & [Rank] & "]/content/m:properties/d:Description")

image

(I’m very grateful to Phil Quinn, who I met at SQL Saturday 162, for helping me with the XPath here – I spent ages trying to get it to work with no luck and he managed to provide a working query in 2 minutes)

With this done, you now have the ability to enter a search term in your worksheet and automatically get Bing search results displayed in an Excel table. Of course, this approach would work with any dataset from the Azure Marketplace and indeed any OData source – for example, now that SSRS 2008 R2 and greater can render reports to OData, it should be possible to consume data from an SSRS report in a much more elegant way than the method I described here, because you’d be able to pass parameters from Excel to SSRS (hmmm, maybe this needs its own blog post). Of course, now you have a table in Excel you can add it to the Excel Model and do all kinds of interesting PowerPivot-y things with it.

What this really highlights, though, is that it should be much easier to parameterise the queries used to load data into the Excel Model/PowerPivot, whether they are OData data sources, SQL, MDX or DAX. Not being able to parameterise these queries means you increase the temptation to load all the data that might ever be needed by a user into PowerPivot; if it were easier to parameterise these queries then it would encourage PowerPivot users to build solutions where they only imported the data they actually needed to work with at any given moment.

Written by Chris Webb

September 10, 2012 at 11:14 am

Posted in Excel, OData

Follow

Get every new post delivered to your Inbox.

Join 3,083 other followers