Chris Webb's BI Blog

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

Archive for the ‘Power View’ Category

Analysis Services Multidimensional Now Works With Power View–And Why That’s Important

with 22 comments

By now you may have already heard the news that, as part of SQL Server 2012 SP1 CU4, new functionality has been released that means that Power View now works with Analysis Services Multidimensional (ie cubes, as opposed to the Tabular Model, which always worked with Power View). I won’t bother to repeat the technical details which you can read about here:
http://blogs.msdn.com/b/analysisservices/archive/2013/05/31/power-view-connectivity-for-multidimensional-models-released.aspx

…but the main points are that Analysis Services Multidimensional can now be queried in DAX, and this plus some tweaks to Power View mean that the two can be used together for the first time. Unfortunately Power View in Excel 2013 doesn’t work with Analysis Services Multidimensional yet, but I hope that will also be fixed very soon.

I’ve been playing with the public CTP of this for a while and done a few presentations with it, and from a technical point of view it’s a solid bit of work by the Analysis Services dev team. It just works, and while there are a few limitations they’re trivial. Arguably it should not have been necessary to do it in the first place – why didn’t Power View speak MDX when it was built, which would have meant it could have queried both Tabular and Multidimensional? But it’s here now, and that’s what counts. It also opens up some interesting possibilities for using DAX queries to create detail-level reports on cubes, and also for defining DAX calculations inside those queries.

However I think its real importance is strategic. This is the first significant bit of new functionality in Analysis Services Multidimensional for a long while, and it acts as a bridge between the classic SQL Server BI stack that most of us are using and the brave new world of Office/Sharepoint-led BI. It is also the first time in a long time that Analysis Services Multidimensional users have had a dedicated client tool for data analysis from Microsoft that isn’t Excel. Don’t get me wrong, I love Excel as a client tool for SSAS but I’ve always thought (and I think industry trends over the last few years support this view) that even though Excel is a great way to bring data analysis to the masses, there’s still an important niche among power users for a more advanced data analysis and data visualisation tool.

You may be thinking at this point that pretty graphs and charts are all very well, but your users don’t need anything other than the SSRS reports and basic PivotTables that they’ve been using for the last few years. I say that you ignore Power View at your own risk. Microsoft’s competitors in the BI space are hungry for new customers and are interested in migration projects. You might well arrive at the office next Monday morning to find that there’s a new CFO who used QlikView in his last job, and who wants the same pretty graphs and charts he had there again. It’s not going to be any use arguing that you’ve spent years developing this cube, that it’s lightning fast and has all sorts of tricky business logic coded into thousands of lines of MDX – if your BI solution’s user interface looks and feels dated, then whatever its technical merits it will have the musty smell of legacy software about it. If, however, you can fire up a VM with Sharepoint 2013 and Power View on and show off some slick dashboards created from your existing cubes, even if this is something the majority of your end users wouldn’t really be interested in (and you may be wrong, they might love it), you’re going to be showing the business two important things:

  • Microsoft can do sexy dashboards and visualizations too, and while they come at a price, that price is probably a lot less than it would cost to rip and replace what you’ve got with a competitor’s software. So the option’s there if you want to spend the money and do the upgrade.
  • Analysis Services cubes are not a dead-end, and Microsoft has made a significant investment here to prove this. I’d still love to see a coherent roadmap that explains where Microsoft is heading with its BI tools and how it expects its existing customers to get there, but I doubt we’ll get one. This functionality was, however, delivered in response to popular demand, so I’m hopeful that if we as customers can make our voices heard as to what we want in the future then we can influence Microsoft’s direction.

So go forth and Power View. Both Rob Kerr and Koen Verbeeck have recently published some excellent, detailed guides to setting up a Sharepoint 2013 demo environment; you have no excuse for not testing this out and being ready to face the competition.

Written by Chris Webb

June 2, 2013 at 9:25 pm

Parameterising PowerPivot Connection Strings in Excel 2013

with 20 comments

One of the things I’ve always wanted to do with PowerPivot is to parameterise the connections used to import data. Despite PowerPivot’s ability to handle massive data volumes, most of the time you want your end users to import only the data they actually need – and for them to be able to specify filters on the data themselves somehow. The only way to do this in Excel 2010 was to have them go into the PowerPivot model and change the connection themselves, which is not very user-friendly, but now we have a proper PowerPivot object model in 2013 we can modify connection strings in VBA (so we can take filtering information direct from the worksheet) and this post shows how. I’d like to acknowledge the help I got from Kasper’s post here which covers very similar ground, but I came across a few interesting things while building the example here so I thought it was worth a post on its own.

Let’s say we want to build our own Bing search engine client in Excel 2013, where a user can enter a search term in a cell, click a button to run the search and then not only be able to see the search results but analyse them in PowerPivot and Power View. The first step is to sign up to the Bing search API in the Azure Marketplace and then import some search results with a hard-coded search term (this tutorial will help if you’re not sure how to do this); I used Bing image search to return some results with urls pointing to images on the web. This will create a connection in the Workbook which we can then modify programmatically. However I ran into a problem at this point: I found that only connections created on the Data tab on the ribbon can be modified in VBA, whereas connections created in the PowerPivot addin cannot. This means I had to click here:

image

..to create my connection, and NOT here:

image

When you open connections created in the PowerPivot window in the Connections dialog from the Data tab, you see the following message: “Some properties cannot be changed because the connection was modified using the PowerPivot Add-In”

image

Trying to edit connections created in PowerPivot using VBA just gave me an error.

Not much of a issue though. With the connection in place, here’s the VBA code that’s needed to alter the connection string and replace the search term with a value from a cell in the worksheet:

Sub RunImageSearch()
Dim mdl As ModelTable
Dim wcon As WorkbookConnection
Dim cs As String
Dim ss As String
Dim azurekey As String
azurekey = "Insert your Azure Marketplace account key here"
Set mdl = ActiveWorkbook.Model.ModelTables("Image")
Set wcon = mdl.SourceWorkbookConnection
cs = "DATAFEED;" & _
 "Data Source=https://api.datamarket.azure.com/Bing/Search/v1/" & _
 "Image?Query=%27ReplacePlaceholder%27;" & _
 "Namespaces to Include=*;Max Received Message Size=4398046511104;Integrated Security=Basic;" & _
 "User ID=AccountKey;Password=" & azurekey & _
 ";Persist Security Info=false;" & _
 "Base Url=https://api.datamarket.azure.com/Bing/Search/v1/Image?Query=%27ReplacePlaceholder%27"
ss = WorksheetFunction.EncodeURL(CStr(ActiveWorkbook.Sheets("Search Term").Cells(2, 3).Value))
wcon.DataFeedConnection.Connection = Replace(cs, "ReplacePlaceholder", ss)
mdl.SourceWorkbookConnection.Refresh
End Sub

 

Three points to note here:

  • If you’re copying this code, you need to enter your own Azure Marketplace account key in the place specified
  • The search term needs to be url encoded, and luckily there’s a new function to do this in 2013: EncodeURL()
  • If you’re using a different data source then obviously the connection string will be different. Also, because I’m using data from the Azure Marketplace my SourceWorkbookConnection object has a connection of type DataFeedConnection – other data sources will have different connection types, so check the value returned by SourceWorkbookConnection.Type (the list of values in the XLConnectionType enumeration on the web is, at the time of writing, out of date but Object Explorer is up-to-date)

That’s all there is to it. The macro can be bound to a button on the worksheet like so:

image

And we can then do all kinds of clever things to analyse the search results. For example, with the image search results we can show thumbnails in a Power View report (see Jeremy Kashel’s post for details):

image

You can download the whole example workbook here, although again you’ll have to edit the VBA to enter your own Azure Marketplace account key if you want it to work.

Written by Chris Webb

January 6, 2013 at 11:49 pm

Posted in Power View, PowerPivot, VBA

Power View on SSAS Multidimensional

with 6 comments

Earlier this week I mentioned that the announcement about Power View working on SSAS Multidimensional had leaked out. There was a full session on it yesterday giving all the details and I thought I’d summarise them here for your enjoyment. Here are the main points:

  • This has not shipped yet – it is not in SSAS 2012 SP1. No release date had been announced but it sounds like it is coming very soon.
  • It will come as a new build of SSAS 2012, so to use Power View you will need to upgrade to that build and no earlier version of SSAS will work.
  • It will also require an update to Power View. This means:
    • Power View in Sharepoint (ie SSRS) will need to be updated too
    • Power View in Excel will still not work initially, even when the new build of SSAS has been released. We’ll have to wait for another update for Office (perhaps a service pack?) before Power View in Excel works on SSAS Multidimensional too.
  • In technical terms, what has happened is that SSAS Multidimensional now
    • Exposes Tabular metadata
    • Can be queried in DAX
  • There is no translation going on from DAX to MDX, SSAS Multidimensional supports DAX natively.
  • The consensus in the session room was that the SSAS team had done a really good job thinking through all the details of how this will work. In general your existing cube will not need to be redesigned, it will just work and all your queries and calculations will return the results you expect. In the session a lot of time was spent showing how things like default members will work.
  • But some things are not supported: if you have named sets or actions they will not be exposed; in some scenarios calculated members on non-measures dimensions will not be exposed either, but time utility dimensions should work; and cell security is not supported either – if a user is a member of a role that has cell security applied, they will not be able to run DAX queries.
  • There’s a minor new feature in SSAS that allows you to mark an attribute as containing a URL for an image, so that Power View can display the images automatically.

UPDATE: the public CTP is now available http://blogs.msdn.com/b/analysisservices/archive/2012/11/29/power-view-for-multidimensional-models-preview.aspx

Written by Chris Webb

November 9, 2012 at 6:20 pm

Thoughts on the PASS Summit 2012 Day 1 Keynote

with 4 comments

Normally I’d rush to blog about the announcements made in the keynotes each day at the PASS Summit, but this year I had a session to deliver immediately afterwards and once I’d done that I saw Marco had beaten me to it! So, if you want the details on what was announced in today’s keynote I’d advise you to read his post here:
http://sqlblog.com/blogs/marco_russo/archive/2012/11/07/pass-summit-2012-keynote-and-mobile-bi-announcements-sqlpass.aspx

I can’t not comment on some of these announcements though, so here (in no particular order) are some things that occurred to me:

  • The first public sighting of Power View on Multidimensional raised the biggest cheer of the morning, which surprised even me – I didn’t realise there were so many SSAS fans in the audience. I’m certainly very pleased to see it, even if it isn’t shipping right now (it’s not in SP1 either). Part of why I’m pleased is that all too often Microsoft BI has been good at building amazing new products but then forgetting about the migration path for its existing customers: think of the Proclarity debacle, and more recently I’ve heard a lot of complaints about the abandonment of Report Models. I suspect this is because Microsoft is not like most other software companies in that it doesn’t do much direct selling itself, but lets partners do the selling for it, and when partners get stick from customers over issues like Proclarity migration then the partners have no leverage over Microsoft to make it deal with the problem. Power View on Multidimensional is a welcome exception to this pattern, and I’d like to see more consideration given to this issue in the future even if it comes at the expense of developing cool new features.
  • The PDW V2 news is interesting too. It was clearly stated that Polybase will, initially allow TSQL to query data in Hadoop but that other data sources might be supported in the future. I wonder what they will be? DAX/Tabular perhaps? Or something more exotic – wouldn’t it be cool if you could query the Facebook graph or Twitter or even Bing directly from TSQL? I’m probably letting my imagination run away with me now…
  • The other thing that popped into my mind when hearing about Polybase was that it might be possible, one day, to use SSAS Tabular in DirectQuery mode on top of PDW/Polybase to query data in Hadoop interactively. I know Hadoop isn’t really designed for the kind of response times that SSAS users expect but I’d still like to try it.
  • It hardly seems worth repeating the fact that Mobile BI is very, very late but again it was good to get some details on what is coming. As partners we can deal with the criticism we get from customers and plan better if we have some idea of what will be delivered and the timescales involved, something that has been conspicuously lacking with Mobile BI up to today. To use a current phrase, Microsoft and its partners are “all in this together”, so please, Microsoft, let us help you!

Written by Chris Webb

November 8, 2012 at 1:06 am

Building a Simple BI Solution in Excel 2013, Part 2

with 22 comments

In part 1 of this series I showed how to build a BI dashboard with UK weather data in Excel 2013. What I’m going to do now is show, very quickly, how this dashboard can be shared with other people via a web browser.

Before I do that, though, an aside: I found a bug in the preview version of Excel 2013 which means that the dashboard I built in the previous post won’t work with what I’m going to describe here. It’s related to how I imported the data – in my last post I imported data from the Azure DataMarket from the Data tab, and what I’ve had to do for this post is import data from inside the PowerPivot window instead. In a way it’s a good thing I did this because the UI for importing data from the Azure DataMarket in PowerPivot is much better than what I showed before (although it too is quite buggy at the moment). No more fumbling around for account keys and OData queries in the way that Rob Collie complains about here, it’s all handled from within the wizard. All you need to do is pick a data set:

image

And then check the tables you want and optionally apply filters to certain columns that support it:

image

Anyway, with my workbook rebuilt, the next thing I’m going to do is save it. There’s actually a lot more to saving in Office 2013 than you might expect, and certainly lots of different options for places to save stuff too, so I recommend you read this post to get a feeling of what’s possible. I’ve signed up for the Office 365 Professional Plus Preview which means I have access to all kinds of cloud-based services including Sharepoint in the cloud, so I can save my workbook up to Sharepoint:

image

I can then go to my Sharepoint site on another computer and view and interact with my dashboard using Office Web Apps in the browser:

image

image

Yes, that’s my Power View dashboard in a web browser – and I can change filters and it all works! Here’s my PivotTable:

image

I can not only drill down, but I can even change what’s on rows and columns by dragging and dropping fields in the Field list in the usual way. It’s a bit slow and (again) a bit buggy at the moment, but by RTM I can see this being a big selling point: anyone with Office 365 will have in place everything they need not only for BI on the desktop, but for sharing their reports over the web (though data refresh may be a problem here). I’m very impressed, and it’s good to see such a strong answer to the question I asked here last year.

Finally, the question I know you’re all dying to ask. Does this work on an iPad? Well, yes and no. The PivotTable works properly and there’s no loss of interactivity, although I’d say that the touch interface doesn’t work all that smoothly yet:

iPad1

Unfortunately the Power View sheet doesn’t work at all – no Silverlight!

iPad2

Oh well, maybe that was too much to ask for… this is clearly not the Microsoft mobile BI solution that was promised at PASS last year. That’s not to say it isn’t useful though – there’s still some good stuff you can do with PivotTables and regular Excel sheets. I’m still very happy with what’s been delivered so far!

Written by Chris Webb

July 18, 2012 at 4:04 pm

Building a Simple BI Solution in Excel 2013, Part 1

with 19 comments

nsurprisingly there’s been a lot of interest in Office 2013 since it was announced yesterday, and I’m certainly very excited by all of the new BI features in it. Luckily I wasn’t working today so I had the chance to have a proper play with it, and I thought it would be useful to walk through the process of building a simple BI solution in Excel 2013 to show off some of the new features.

Let’s start with a blank workbook:

image

Some people don’t like the new look for Office, but I quite like it. You can even set custom backgrounds: you may just be able to see some wispy clouds in the top right-hand corner of the screenshot above. But anyway, to business. To import some data, I first need to go to the Data tab as normal:

image

As you can see there are some new options available here, and I’m going to go to the Windows Azure Marketplace. Actually, I’m going to cheat a little and just say that I’m going to import the UK weather forecast from 12:00am today (July 17th) along with some related tables as described in this blog post. The UI for the import wizard is plain but functional:

image

Interestingly, the data is always imported at the end of the wizard even if I check the ‘Only Create Connection’ option on the last step of the wizard.

Once the data from all the tables has been imported, I need to specify some relationships. I can either do this by clicking on the Relationships button in the Data tab or (better still) going to the PowerPivot window and using the Diagram View. Now as I said yesterday, xVelocity in-memory database is now integrated into Excel but I still need to use the PowerPivot addin in some cases; The PowerPivot addin comes bundled with Excel 2013 but isn’t enabled by default, so in order to use it I first need to enable it; the steps to do this are detailed here. I can then click on the Manage button in the PowerPivot tab to open the PowerPivot window:

image

And then build some relationships between my tables in Diagram View, which can be reached by clicking on the small Diagram button in the very bottom right hand corner of the PowerPivot window. Once in the Diagram View, building relationships is simply a matter of dragging one column onto another:

image

I can also build hierarchies here; Duncan Sutcliffe shows how to do this here, and also how to use hierarchies in Power View which is something new. I’ve created a hierarchy going from Region to Weather Station.

With that done I can of course build a PivotTable. The first thing I’ve noticed is that there isn’t now a separate type of PivotTable for PowerPivot, which is kind of a shame because this means that you don’t seem to be able to create Slicers in the Field List any more, and have to go up to the Ribbon. It’s good for consistency though I suppose. Measure aggregation and number formats are all handled in the Value Field Settings dialog:

image

There’s a new type of Slicer available too for dates called the TimeLine which Raphael has a lot of good detail on here.

Here’s what the new Quick Explore option, which provides some options on where to drill to and for charts to create, looks like when I hover over a Region on Rows:

image

Really, though, the thing to do is to create a Power View sheet. This can be accomplished by going to the Insert tab and clicking the Power View button:

image

This is where things get exciting. Power View in Excel now supports maps and because my weather data contains the latitude and longitude of all of the weather stations in the UK it’s very easy to plot each weather station on a map and visualise the temperature and predicted weather for each station. To do this I just need to drop the Region Name column onto the Filters selection (choosing a region reduces the number of Weather Stations displayed down to a manageable number for the purposes of this demo), then drop the Latitude and Longitude columns onto the main canvas, turn the resulting table into a map, and then set the size of the markers to show temperature and the colour to show the type of weather:

image

I’ll be speaking at SQL South West this week so what’s the weather like down there at the moment?

image

From this I can see that today there’s fog in the Scilly Islands (shown by the green marker in the bottom left hand corner) and that it’s very cold and foggy in Liscombe (shown by the small red marker in the middle of the map). This dashboard was ridiculously easy to create, by the way, and I am already completely in love with Excel 2013 simply because of Power View – it’s a killer feature in my opinion.

At this point we’ve got a working self-service BI solution, made very quickly in Excel 2013 and it’s something that every competent Excel user would be able to achieve.

In Part 2, you’ll see how you can share this solution with your co-workers in the Office Web App.

Written by Chris Webb

July 17, 2012 at 11:33 pm

Follow

Get every new post delivered to your Inbox.

Join 3,146 other followers