Chris Webb's BI Blog

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

Archive for November 2012

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

Send Your Feedback to the SSAS Dev Team!

with one comment

It’s been all over Twitter today and Kasper has already blogged about it, but I thought this was worth a blog post from me all the same: the SSAS dev team are looking for feedback on features for the next version of SSAS and have put together a survey here:

http://www.instant.ly/s/Wqdj4mEAIAA

It’s not a list of features that will definitely be delivered, and doesn’t cover everything they’re thinking about, but it’s a way to help them prioritise some features over others and it should take no more than 20 minutes to complete, so why not help them out?

Written by Chris Webb

November 29, 2012 at 5:16 pm

Posted in Analysis Services

Storage Engine Caching, Measures and Measure Groups

with 6 comments

I’ve been doing some performance tuning work on SSAS Multidimensional recently that has forced me to look at some behaviour I’ve observed several times but never properly understood: what happens with Storage Engine caching when you are querying multiple measures in the same measure group. Here are some of my findings (thanks, as always, to Akshai and Marius for answering my questions on this) although this post only deals with a few basic scenarios…

Consider the following, quite basic cube built from Adventure Works. It has one measure group and two measures, Sales Amount and Tax Amount, that both have AggregateFunction Sum:

image

And a single Date dimension with the following attribute relationships:

image

If I run a Profiler trace, clear the cache and run the following query twice:

SELECT
{[Measures].[Sales Amount]}
ON 0,
[Date].[Year].[Year].MEMBERS
ON 1
FROM
[Measure Caching]

I can see that the first time the query is run it doesn’t hit cache, and the second time the query (in the second red box below) is run it does hit the Storage Engine cache:

image

This is as you’d expect. However, now look what happens when I run a query that returns the Tax Amount measure – which was not in the original query – without clearing the cache:

SELECT
{[Measures].[Tax Amount]}
ON 0,
[Date].[Year].[Year].MEMBERS
ON 1
FROM
[Measure Caching]

image

Even though this is the first time I’ve queried for this measure since the cache was cleared, this query still hits the cache. This is because when you query for one measure, the SSAS Storage Engine will retrieve data for all other measures in the same measure group for the granularity of data requested.

This means that the AggregateFunction property of a measure is significant here. If I add a new measure to the cube with AggregateFunction set to Count instead of Sum:

image

I see the same thing happening, ie queries that request data for Sales Amount or Tax Amount also warm the SE cache with values for Internet Sales Count. This is because a query for Internet Sales Count can be answered with data of the same granularity as a query for Sales Amount. However, if I add a new measure called Last Sales Amount with AggregateFunction Last Non-Empty:

image

And then clear the cache, and run the two following queries one after the other:

SELECT
{[Measures].[Sales Amount]}
ON 0,
[Date].[Year].[Year].MEMBERS
ON 1
FROM
[Measure Caching]

SELECT
{[Measures].[Last Sales Amount]}
ON 0,
[Date].[Year].[Year].MEMBERS
ON 1
FROM
[Measure Caching]

I can see that the first query does not warm the cache for the second query – both queries go to disk:

image

Why is this happening? Why isn’t the cache being used? A clue lies in the Query Subcube Verbose event for both queries. For the first query, using Sales Amount, the following granularity of data is being requested:

Dimension 0 [Date] (0 0 0 *)  [Date]:0  [Month]:0  [Quarter]:0  [Year]:*

Whereas the second query, using Last Sales Amount, requests this granularity:

Dimension 0 [Date] (* 0 0 *)  [Date]:*  [Month]:0  [Quarter]:0  [Year]:*

Both queries have Years on rows, but because Last Sales Amount is semi-additive the values returned are actually from the Year and Date granularity. So, when the semi-additive measure is requested in the second query the data needed for it is not in the Storage Engine cache: the first query requested data at the Year granularity only.

From what I understand, the logic governing this behaviour is very complex and the exact query plan that gets generated will depend on the overall design of your cube, the AggregateFunction used for the measures in each measure group (measures with measure expressions are going to work in a similar way to semi-additive measures) and the queries you’re running. However it is useful to be aware of this kind of behaviour when designing and tuning SSAS cubes. For example, it could be that if you have a large number of measures (tens or even a hundred) in the same measure group it could be worth splitting them out into separate measure groups to improve performance, especially if some measures are never queried together – you would need to test this thoroughly first though. This behaviour would also be relevant in cases where you’re designing aggregations manually.

Written by Chris Webb

November 27, 2012 at 1:53 pm

First Screenshots of Microsoft’s Mobile BI Solution

with 9 comments

It didn’t get much attention at the time (maybe because it was done at the Sharepoint Conference, and not at PASS… why?) but last week Microsoft gave the first public demos of its Mobile BI solution. I wasn’t there to see it but Just Blindbaek was and this morning he tweeted some pictures of what he saw. Some of you Microsoft BI enthusiasts might be interested to see them:
https://twitter.com/justblindbaek/status/270812739365130241/photo/1
https://twitter.com/justblindbaek/status/270785164689412096/photo/1

The codename seems to be ‘Project Helix’.

Written by Chris Webb

November 20, 2012 at 11:07 pm

Posted in BI, Mobile

SQLBits XI Dates Announced

leave a comment »

Yesterday, the SQLBits Committee (which I’m a member of) announced the dates for SQLBits XI: it will be taking place on May 2nd-4th 2013 at the East Midlands Conference Centre in Nottingham, UK. SQLBits is, of course, Europe’s biggest SQL Server conference and the second-biggest dedicated SQL Server conference in the world, and we attract attendees from all over the world. Apart from top-notch sessions from the world’s leading SQL Server experts you can also expect to have a lot of fun: at previous events attendees have had the chance to groove with the Beatles (well, ok, maybe they were just pretending to be the Beatles), play darts with professionals and hang out with Steve Wozniak (the real one). You should come! To find out more, keep an eye on http://sqlbits.com/

Written by Chris Webb

November 15, 2012 at 9:33 am

Posted in Events

Tagged with

Analysing #SQLPASS Tweets using NodeXL

with 5 comments

I’ve got a large backlog of serious technical blog posts to write but today, since I’m still recovering from my trip to the PASS Summit in Seattle last week, I couldn’t resist going back to my favourite data visualisation tool NodeXL and having some fun with it instead. Anyone that saw the keynotes last week will know that the future of BI is all about analysing data from Twitter – forget about that dull old sales or financial data you used to use on your BI project – and so, inspired by Sam Vanga’s blog post from today on that same topic I decided to take a look at some Twitter data myself.

In NodeXL I imported 1757 tweets from 515 different people that included the #sqlpass hashtag from the 8th of November when Twitter activity at the conference was at its peak (I couldn’t import any more than that – I assume Twitter imposes a limit on the number of search results it returns). In basic terms, when NodeXL imports data from Twitter each Twitter handle becomes a point on a graph, and a line is drawn between two Twitter handles when they appear in a tweet together. I won’t bother going into any detail about how I built my graph because analysing the results is much more interesting, so I’ll just say that after playing around with the clustering, layout and grouping options here’s what I came up with:

image

It looks very pretty from this distance but it’s not very useful if you can’t read the names, so I saved a much larger .png version of this image here for you to download and explore, and if you’ve got NodeXL you can download the original workbook here (don’t bother trying to open it in the Excel Web App). It’s fascinating to look at – even though the data comes from a very restricted time period the cliques in the SQL Server world emerge quite clearly. For example, here’s the group that the clustering algorithm has put me in  (I’m @Technitrain), which is at the bottom of the graph on the left-hand side:

image

There’s a very strong UK/SQLBits presence there (@timk_adatis and @allansqlis for example), but also a strong BI presence as well with @marcorus and @markgstacey, which is pretty much what you’d expect. There are several other small groups like this, plus a large number of unconnected people in groups on their own in the bottom right-hand corner of the graph, but on the top left-hand side there’s a monster group containing a lot of well-known SQL Server personalities. Jen Stirrup (@jenstirrup) is right in the centre of it, partly because she’s one of the SQL Server Twitter royalty and partly because of her well-deserved PASSion award that day. Highlighting in red just the tweets that involved her shows at the very highest level how well-connected she is:

image

Keeping Jen selected and zooming in shows the people clustered together with Jen a bit better:

image

Selecting not only Jen’s tweets but also the tweets of the people who tweeted to her and also to each other (which is one of many useful features in NodeXL), highlights just how close the members of this group are:

image

This is clearly where the popular kids hang out…

Anyway, I hope this gives you an idea of the kind of thing that’s possible with NodeXL and Twitter data and inspires you to go and try it yourself. Hell, NodeXL is so much fun it might prove to the DBA crowd that BI doesn’t need to be boring!

Written by Chris Webb

November 12, 2012 at 10:32 pm

Posted in NodeXL, PASS

Interesting Products I Saw At PASS

leave a comment »

For my last post from the PASS Summit, I thought I’d mention briefly some of the products that caught my eye as I wandered round the exhibition hall this afternoon:

  • OData Connectors from RSSBus (http://www.rssbus.com/odata/), a series of web apps that expose OData feeds (which then of course can be consumed in PowerPivot and SSAS Tabular) from a variety of data sources including Quickbooks, Twitter and MS CRM. I’d seen the website a month or so ago, actually, but I found out today they are close to releasing OData connectors for Google, Google Docs, Facebook, Email and PowerShell as well, which open up some intriguing possibilities for PowerPivot analysis. I can imagine doing a really cool demo where I set up an email address, got the audience to email me, then hooked PowerPivot up to my inbox and analysed the emails as they came in!
  • XLCubed (http://www.xlcubed.com/) – well, ok, they aren’t exactly new to me but it was good to have a chat with the guys on the stand. It’s worth pointing out they have a good mobile BI story for SSAS users.
  • Kepion (http://www.kepion.com/) – I was quite impressed with the demos I saw of their products for building SSAS-based BI solutions, especially for (but not restricted to) financial planning; it looked pretty slick. 
  • Predixion (http://www.predixionsoftware.com/predixion/) – again, the company itself isn’t new to me but I got a demo of their new product, Predixion Enterprise Insight Developer Edition, which I’d been meaning to check out for a while. This is an immensely powerful free tool for doing data mining in Excel and it’s very closely integrated with PowerPivot too. Even if you don’t want to do complex stuff, it has some features that would be useful for regular PowerPivot users such as the ability to select a column in a PowerPivot table, analyse the data in it and then generate bandings which are then persisted in a new calculated column.

Written by Chris Webb

November 10, 2012 at 6:12 am

The Future of Data Explorer

with 2 comments

You might have seen me mention Data Explorer a few times over the last year in various blog posts; it’s a self-service ETL tool that is currently available via SQL Azure labs:
http://www.microsoft.com/en-us/sqlazurelabs/labs/dataexplorer.aspx

I’ve had a lot of fun using it and so I was pleased, and quite surprised, to see the new version of it being used in the keynote here at the PASS Summit on day 2. After a few behind the scenes enquiries, I can now confirm that the ‘Data Explorer experience’ is currently being worked on by Microsoft, and a public preview of ‘the new Excel-based experiences’ (ie what was shown in the keynote) will be available pretty soon, hopefully. Which is very good news.

Written by Chris Webb

November 10, 2012 at 12:47 am

Posted in Data Explorer

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

Follow

Get every new post delivered to your Inbox.

Join 2,868 other followers