Chris Webb's BI Blog

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

Archive for November 2012

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

PASS Summit 2012 Schedule (and the first bit of juicy gossip!)

with one comment

I’ve now arrived in Seattle for the PASS Summit 2012, and as always I’m looking forward to a busy week. There are a few things I wanted to highlight:

  • There are always a few big announcements at PASS, and while I’ll be blogging every day you’ll hear the news first if you follow me on Twitter – I’m @Technitrain. For example I’ve just noticed a session on “Using Power View with Multidimensional Models” on the schedule which means, I suspect, that the long-promised support for Power View over SSAS Multidimensional, via DAX queries, will be announced this week!
  • I’m doing a session on “What’s New in SSAS 2012?” (BIA-303) on Wednesday morning at 10:15am. Unfortunately, the printed schedule and summit guide booklet have the wrong session title and/or abstract at the moment; the session details online are correct. This means that contrary to what you might see in some places I will not be doing a pure SSAS Multidimensional session;  there will be about 15-20 minutes of 2012 Multidimensional content (I’d struggle to fill more than that) and the rest will cover terminology, Tabular, and choosing between Tabular and Multidimensional.
  • I’m doing a book signing with Marco and Alberto for our new book “SQL Server Analysis Services 2012: The BISM Tabular Model” on Thursday lunchtime from 12:00pm to 12:30pm at the Summit bookstore.
  • I’m doing a second session on “The Best Microsoft BI Tools You’ve Never Heard Of!” (BI-202) on Thursday at 1:30pm. This will be a fun session showing how you can use tools like NodeXL and Layerscape alongside PowerPivot for BI purposes; and yes, I know, if you’re a regular reader of my blog you will have heard of these tools… but hopefully you’ll find it enjoyable nonetheless.

If you see me wandering around at any point, feel free to say hello! I’m quite friendly and I won’t bite. I might be a bit jet-lagged though – I’m wide awake at 4:45am right now…

Written by Chris Webb

November 5, 2012 at 12:46 pm

Posted in Events, PASS

Introduction to MDX for PowerPivot Users, Part 4: Filtering

with 8 comments

The combination of the filtering functionality built into PivotTables, and the ability to delete and reorder tuples in a set without needing to edit the set expression itself that the Excel named set functionality gives you, means that you can usually implement the filters you need in PowerPivot without needing to resort to MDX. However there are some scenarios where knowing the MDX functions that allow you to filter a set are useful and in this post I’ll show a few of them.

FILTER()

The Filter() function is the Swiss-Army penknife of filtering in MDX: it can do pretty much anything you want, but isn’t always the most elegant method. It’s quite simple in that it takes two parameters, the set that is to be filtered and a boolean expression that is evaluated for every item in the set and which determines whether that item passes through the filter or not.

Here’s a simple example. Consider a simple PivotTable (using my example model, described here) with FullDateAlternateKey on rows and the Sum of SalesAmount measure on columns:

image

The set of members on the FullDateAlternateKey level of the FullDateAlternateKey hierarchy can be obtained by using the .Members() function as I showed earlier in this series:
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS

This set can then be filtered by passing it to the Filter function, which itself returns a set, so it too can be used to create a named set. Let’s say we only wanted the set of dates where Sum of SalesAmount was greater than £10000; we could get it using the following expression:

image

Filter(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, ([Measures].[Sum of SalesAmount])>10000)

What I’m doing is passing the set of all dates into the first parameter of Filter() and then, in the second parameter, testing to see if the value of the tuple ([Measures].[Sum of SalesAmount]) is greater than 10000 for each item in that set.

Here’s the result:

image

As I’ve mentioned before, it’s very important that you remember to check the ‘Recalculate set with every update’ button if you want the filter to be re-evaluated every time you change a slicer, which you almost always want to do.

Where is the filter function actually useful though? Here’s the same PivotTable but with Color on columns and with only Red and Black showing:

image

It’s not possible to filter this PivotTable to show only the dates where sales for Black products are greater than Sales of Red products using native functionality, but it is using MDX. Here’s the set expression:

Filter(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, ([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])
>
([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Red])
)

Here I’m doing something similar to what I did in the first example, but now comparing two tuple values for each date: the tuple that returns the value of Sum of SalesAmount for Black products, which is
([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])
and the tuple that returns the value of Sum of SalesAmount for Red products, which is
([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Red])

NONEMPTY()

The NonEmpty() function also does filtering, but it’s much more specialised than the Filter() function – it filters items from a set that have empty values for one or more tuples. As with the Filter() function its first parameter is the set to be filtered, but its second parameter is another set, each of whose items are evaluated for each item in the first set. If one item in the second set evaluates to a non empty value for an item in the first set then that item passes through the filter.

That explanation is, I know, quite hard to digest so let’s look at an example. Here’s our PivotTable with no filter applied on rows and all Colors displayed on columns:

image

If you wanted to see only the rows where there were sales for Black products, you could use the following expression:

NonEmpty(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, {([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])})

image

If you wanted to see only the rows where there were sales for Black OR Silver products, you could use this expression:

NonEmpty(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, {([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])
,([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Silver])})

image

If you wanted to see only the rows where there were sales for Black AND Silver products you’d need to use two, nested NonEmpty functions:

NonEmpty(
NonEmpty(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, {([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])})
, {([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Silver])})

image

TOPCOUNT(), BOTTOMCOUNT(), TOPPERCENT(), BOTTOMPERCENT()

The TopCount() and related functions are, as you’ve probably guessed from their names, useful for doing top N style filters. If you wanted to see the top 10 dates for sales of Black products you could use the following expression:

TopCount(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, 10
, ([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])
)

image

Notice here how the dates are displayed in descending order for the Black column, but no other – that’s how you can tell that the TopCount() function is doing what you want.

To get the top N dates that provide at least 5% of the total sales across all time for Black products, you can use the following expression:

TopPercent(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, 5
, ([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])
)

image

The BottomCount() and BottomPercent() function (I’m always reminded of one of the old, old jokes here when I use the BottomCount() function…) do the opposite and return the bottom items in a set, but you need to be careful using them because the bottom items in a set often have no values at all which is not very useful. So, for example, if you wanted to find the bottom 10 dates that have sales for Black products you need to use the NonEmpty() function as well as the BottomCount() function as follows:

BottomCount(
NonEmpty(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, {([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])})
, 10
, ([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])
)

image

Here I’m taking the set of all members on the FullDateAlternateKey level of the FullDateAlternateKey hierarchy, passing that to the NonEmpty() function to return only the dates that have values for Black products and Sum of SalesAmount, and then getting the bottom 10 of those dates.

In part 5, I take a look at running MDX queries against a PowerPivot model.

Written by Chris Webb

November 5, 2012 at 4:45 am

Posted in MDX, PowerPivot

Follow

Get every new post delivered to your Inbox.

Join 3,301 other followers