PowerPivot calculated columns are not dynamic

A quick and dirty one – in attempting some clever dynamic security modelling in DAX I was warned about a gotcha in my design – that calculated columns were only evaluated when the model processed, so any approach based on calculated columns was doomed to failure. I didn’t quite believe it so I decided to do a road test in PowerPivot. Using a simple data set of one Dimension and one Fact, like below:

Simple Data Model
Simple Data Model

I hooked them up in PowerPivot with a relationship between “Column” on both tables. Then, using the ISFILTERED() function I created a couple of calculations. One, at Row Level, that would return a value of 1 if I filtered on the Attribute column:

=IF(ISFILTERED(Dim[Attribute]),1,0)

Which I then put a SUM on top of. I also added one at measure level, perfoming a similar function:

FilterCheckAsMeasure:=IF(ISFILTERED(Dim[Attribute]),1,0)

Then I created a Pivot Table checking the results, and got this:

Results
Results

The takeaway being that filtering on the Attribute was picked up by the table level measure, but the calculated column did not change value.

You can have a check of this yourself in my sample workbook: DAX Calculated Columns Evaluation Order.

What does the documentation say?

Well, nothing terribly clear, but in this page there is this small paragraph:

When a column contains a formula, the value is computed for each row. The results are calculated for the column as soon as you create the formula. Column values are then recalculated as necessary, such as when the underlying data is refreshed.

It doesn’t say exactly when “recalculated as necessary” is necessary, but the implication is that it’s a model level change, rather than the switching of context, or in my case the changing of the user viewing the model.

So in summary, we have to assume that our calculated column values are fixed upon initial calculation, formula changes or data load (or processing in a Tabular model) and there isn’t a way to make the value in a given cell change.

Read More

Adventures in DAX, a mild grumble

Being back in the field is proving to be interesting as I get back into the daily frustrations and challenges of development (as opposed to those of PowerPoint). Anyway, one of the new things I’m hands on with is and Tabular Models and DAX table queries, and I’m feeling a little underwhelmed.

Syntax

I’m now torn with whether to hate DAX more than MDX. It seems that in the design of DAX table queries, any opportunity to make it accessible was largely ignored. What do I mean? Well…  here’s how to get all the rows from a given table in a Tabular Model:

EVALUATE
(
‘A Fact Table’
)

Ugh…   “EVALUATE”? I’m sure it makes sense to someone, but even MDX managed to have “SELECT”.
But then hiding in there are some weird syntactic quirks at the simplest level. Say I want to SUM a given column, I do this:

EVALUATE
(
SUMMARIZE
(
‘A Fact Table’,
‘A Fact Table’[My Measure]
)
)

So setting aside the Americanisation of SUMMARIZE, the above only actually works if the Measure is in the base table. If its a calculated column, you have to give it an alias to be able to access it, i.e:

EVALUATE
(
SUMMARIZE
(
‘A Fact Table’,
“My Alias”, ‘A Fact Table’[My Measure]
)
)

So that’s just annoying, as the error message if you don’t use an alias simply implies the measure isn’t there.

Usability

That’s of course just the small stuff. There’s the bigger challenges.

SSMS has no DAX query editor, so you need to use an MDX query template. And then in that there’s no Intellisense and the Metadata panel is useless as it is for querying your tabular model in MDX mode.

SSRS can only cope with DAX queries if you pretend you are writing a DMX query.

I’ve yet to interact with it via SSIS but I doubt the experience is much better.

The Upside

Anyway, it’s not all bad. To get started, take a look at these posts by Chris Webb (don’t fall over yourself looking at the official documentation though, it’s a bit sparse).

The language looks pretty powerful and getting sets of data out of it is simpler than MDX. However it’s not as friendly as it could be and it’s lacking some basic support as part of day to day usability. Hopefully this will improve by SQL2016…

Read More

PowerPivot – things to love, things to hate

I’ve recently been involved in a couple of cases for a client that revolved heavily around PowerPivot, and its prompted me to highlight some of the things which make me simultaneously love and hate it (though in balance its clearly a thing to love).

I hate PowerPivot because…

So let’s start with the negative. It’s a v1 Product, which means of course it will get better. But because it’s v1 and Microsoft clearly felt a compelling need to give Qlikview something to worry about (and TM1 to some extent) so it’s been shoved into the limelight in a starring role its not entirely ready for.

So, my gripes are:

  • No drillthrough. You can slice and dice superfast, sure…  but unlike SSAS (which has flawed drillthrough), you can’t drillthrough at all in a Pivot Table pointing at a PowerPivot workbook
  • No API. So there’s no way to programatically build a  PowerPivot workbook, or interact with the PowerPivot data model from Excel (even for a simple data refresh)
  • No Data Mining Support. There are some half hearted workarounds documented here but you simply cannot point the Excel Data Mining Add-In at a PowerPivot data model. If you want to do that, you need to lean on Predixion Software (fortunately Jamie McLennan, part of the original MS Data Mining team is heavily involved so it’s a good bet this is a good option, but the cloud aspect may deter some). To add insult to injury, there is no Data Mining Add-In for 64-bit Excel, and the Data Mining and Power Pivot Add-ins often conflict.

Another common gripe is about a lack of Hierarchy support (this doesn’t really bother me but drives SSAS guys up the wall). This is think is due to be resolved by BISM anyway in Denali. So, at the end of round 1, it’s taken a few punches, but it’s the new kid and it’s far from out for the count…

I love PowerPivot because…

Microsoft have been pushing “BI For the Masses” as their vision since SQL2005. With SharePoint 2010 and Office 2010 I think they are finally getting their vision translated as reality. To start with, I wasn’t entirely sure what the benefit of loading PowerPivot workbooks as data sources into SharePoint actually was, but I had my lightbulb moment last week. If you have a Data Warehouse, or a set of data sources that as a business user you don’t really have any control over, you can be a bit stuck. Building cubes can be a bit of a challenge and you may not be able to get the access / skills / project approval needed to build such a data model. But if you can do it in PowerPivot… then you can model your data to your need, upload to SharePoint – and suddenly you have your data mart for your department. The amount of power this gives to the users who are stuck in the “Import to Access, do some dodgy manipulation, then surface in Excel” rut is phenomenal. I’m not going to get started on what can be done with the REST API with Excel and SharePoint.

Of course another easy thing to love about it is that it all lives in the Excel environment that so many BI users know and love, so user adoption is rarely a challenge.

I think however, there’s one thing that simply cannot be ignored – it’s mindbogglingly fast. In a POC I did recently with my colleague Boyan Penev (to be fair, he did all the work) we were tossing around an 80 million row fact table with some hairy relationships in it and it still responded in subsecond times. Because of this, it’s going to be very, very hard to ignore as the underlying technologies (SharePoint and Office 2010) gain traction.

Read More