Formula Caching and Query Scope

The Analysis Services formula engine’s ability to cache the results of calculated members can be crucial to the overall performance of your cube, but it’s all too easy to turn off this caching. I’ve blogged already about the impact that subselects have on this and today I thought I’d discuss how you need to be careful using different calculation contexts. This is a topic that’s covered in the Analysis Services 2008 Performance Guide, but I thought it would be worth talking about here because it does deserve a lot more visibility as an important cause of poor query performance, especially in SSRS reports with hand-coded MDX.

As you know, you can define calculated members in three different places in Analysis Services: on the cube (the global context), within the session (the session context) and in the WITH clause of a query (the query context). The important paragraph from the Performance Guide is this one: 

If the query processor encounters calculations created at query time, it always uses the query context, even if a query also references calculations from the global context (there is an exception to this – queries with query calculated members of the form Aggregate(<set>) do share the session cache). If there are no query calculations, but there are session calculations, the query processor uses the session cache. The query processor selects the cache based on the presence of any calculation in the scope. This behavior is especially relevant to users with MDX-generating front-end tools. If the front-end tool creates any session calculations or query calculations, the global cache is not used, even if you do not specifically use the session or query calculations.

What does this mean in practical terms though? Consider the following query on Adventure Works that uses a calculated measure defined on the cube, Internet Ratio to Parent Product:

SELECT [Measures].[Internet Ratio to Parent Product] ON 0,
[Product].[Product Categories].MEMBERS ON 1
FROM [Adventure Works]

Clear the cache and run the query and you’ll see the normal stuff taking place if you run a Profiler trace. Then, if you rerun the query on a warm cache, you should see something like this in Profiler (if you are looking at the Get Data From Cache event):


Clearly the result of the calculation has been cached, and the second time you run the query you’re retrieving values from the formula engine cache. Now, consider the following query:

SELECT [Measures].[Internet Ratio to Parent Product] ON 0,
[Product].[Product Categories].MEMBERS ON 1
FROM [Adventure Works]

We’re now creating a calculated measure in the WITH clause but not using it in the query. What happens when we run this query on a warm cache?


We’re still hitting cache, but it’s the storage engine cache and not the formula engine cache; the calculations are being re-evaluated for the query. So, even the presence of a calculated measure in the WITH clause, even though we’re not actually using it, prevents the use of the formula engine cache and if we’ve got complex calculations used in our query this could make our overall query performance significantly worse.

Note that the presence of a named set in the WITH clause does not have the same effect, so the following query can use the formula cache:

SELECT [Measures].[Internet Ratio to Parent Product] ON 0,
[Product].[Product Categories].MEMBERS ON 1
FROM [Adventure Works]

So, some recommendations that follow on from this:

  • If calculation performance is a problem for you, be very, very wary of writing queries that have a WITH clause. Where possible, put the calculated members you need on the cube; even if you don’t want other users to see them you can always set Visible=false.
  • When evaluating client tools look out for those that create session or query scoped calculated members for their own internal use (running a Profiler trace will show you whether they do). This could have the side-effect of reducing query performance on calculation-heavy cubes.

SQLBits IV voting now open

We’ve now closed session submission for SQLBits IV, which will be taking place on March 28th in Manchester. Once again we’ve had a great response and it’s now time to vote for which of the 83 submitted sessions you’d like to see; to do this you need to register on the site and then go to
and choose the sessions you’d like to see.

Last comments on the PPS Planning news

By now just about everybody who has anything to say about the killing-off of PPS Planning has blogged, so I thought I’d post up some links. The judgement seems to be that it’s hardly a surprise that it wasn’t successful and that even if they could have got the tech side of the product right, they didn’t have the means to sell it; on the other hand, the news that the surviving parts of PPS will be effectively free for Sharepoint customers is certainly going to be a positive move for its uptake – although it can also be seen as another blow for another set of partners, those who provide web-based SSAS client tools.

Anyhow, the links –
Nigel Pendse:
Johan Pellicaan:
Mauro Cardarelli:
The Panorama blog:
Cindi Howson:
The official word from the Microsoft BI Blog:

Last of all, Andrew Wiles makes a very sensible suggestion here:!43141EE7B38A8A7A!451.entry

Given that several partners and customers who were Planning early adopters have been pretty much [expletive deleted] by this move, why shouldn’t Microsoft open source it and make the code available somewhere like Codeplex? It would be a nice gesture.

More thoughts on the death of PPS Planning

I’ve just seen a copy of the PerformancePoint announcement, and it makes interesting reading. Peter Koller’s blog entry that I pointed to in my previous post has all the main points and a very good analysis, but there are some other things I’d like to pick up on…

First of all, a classic bit of PR "let’s put a positive spin on this":
These changes enable customers to deploy a complete BI solution with existing investments in SharePoint Server, SQL Server, and Excel, the most widely used analysis and planning tool in market today.

LOL, after all that time spent telling us we should move away from Excel hell we now find that the death of PerformancePoint Planning means we’re now free to go back to Excel!

Performance management is a critical component of business intelligence and Microsoft will continue marketing and R&D investment in this area in future product releases.

How you interpret this depends on what ‘performance management’ means. Some people have speculated that MS are planning to acquire a replacement for PPS Planning but I doubt this. As I said yesterday, could Gemini be somehow used as a planning tool? Gemini would certainly help make doing planning in Excel faster but you really need a dedicated app to manage the planning process properly surely? I think MS are simply abandoning this space.

The core ProClarity capabilities that made that product successful will migrate to SharePoint and Excel over the coming releases. As for Business Scorecard Manager and ProClarity Desktop Pro, we don’t anticipate any further customer demand for this.

Everyone had already guessed that Microsoft’s BI fat-client consisted solely of Excel, but this confirms it. Personally I think they’re wrong in thinking that there’s no customer demand for a tool like Proclarity Desktop Pro – Excel 2007 is good but has some serious limitations as a high-end client for Analysis Services, and I doubt the next version of Excel will do everything power users want either (and let’s face it it’s going to be years and years before most organisations even think about migrating to Excel 14!).

It would be good to have a clear statement of Microsoft’s future BI strategy now, if only so that partners can work out what they should and shouldn’t invest in. I’ve already talked about how the uncertainty caused by the Proclarity acquisition has actively damaged the third-party client tools market and in turn reduced choice for customers and made the MS BI platform less attractive. The same thing goes for planning: everyone in BI knows that this is a highly lucrative market to be in – if it wasn’t, MS wouldn’t have entered it in the first place. But PerformancePoint Planning killed off most of the ecosystem of planning and budgeting applications that used the MS BI platform, such as tools like Enterprise Reporting that MS had acquired, or Outlooksoft which sold itself to SAP (I assume the product still exists, but I’ve not heard much of it since). And now Planning itself is dead, what’s left? And what partner will want to bet on this area again?

So, farewell then PerformancePoint Planning

Obviously the big news today was the job cuts at Microsoft, but in the BI area there was also the significant announcement that PerformancePoint Planning is being killed and Monitoring and Analytics will be rolled into the Sharepoint team:
(I’ll add more links later when I can find some!)

All I can say is thank goodness I never spent any time learning Planning (more through laziness/luck than judgement); my sympathy goes out to people who did. Why have MS done this? I always had my doubts about the architecture of Planning, but like everyone else I assumed MS would get it right in the long term. Perhaps now MS have decided they can’t afford to spend years developing products that only become successful after a few years. Perhaps the future is Gemini? Who knows…

UPDATE: the link above seems to be down at the moment, but I’ve had the news confirmed from other sources. And here’s Peter Koller with more details:!68755AEAC31F9A6C!992.entry

UPDATE: some more links:!CD3E77E793DF6178!893.entry

I’ve also heard that FRx is going to be dusted down and developed further. If you’re interested in seeing a good comparison of FRx and PPS Planning, see:

Speeding up the Query Parameters Dialog in the SSRS Query Designer

Boyan Penev recently blogged about the virtues of setting default parameters and he’s right: it’s a good idea to set parameter defaults. However I was with a customer yesterday who were waiting for a really long time to even open the Query Parameters dialog in the SSRS query designer. I was a bit curious as to why this was, so I ran a Profiler trace. What I found was then when I opened the dialog when there was a default selection made, for example:


The query designer would run an MDX query like this:

Clearly it was running queries to check the parameters were valid but in this case (although not in other instances) it was forgetting to add an empty set on the opposing axis, as per Mosha’s recommendations here, and therefore bringing back measure values by accident. Now this doesn’t matter for most cubes but my customer had a very, very large cube, their default measure was on their biggest fact table, and hadn’t built any aggregations that this query could use. And so, not surprisingly, the query ran very slowly and editing the reports was a nightmare.

There are two possible workarounds here:

  1. Build an aggregation for the above query, or
  2. Create a new, hidden calculated measure that returns null and make that the default measure on the cube. Here’s what to add to the MDX Script to do this:


Interestingly the same dialog also seems to generate some errors from the queries it runs which I think reinforces the argument for someone to go back to this code and do some work for the next hotfix. The errors seem to happen when you have a parameter with no default value set – the MDX error message is:
Query (1, 9) Parser: The syntax for ‘on’ is incorrect.
Query Text:
SELECT  on 0 FROM [Adventure Works]

It doesn’t really matter from a performance point of view, but it’s not very tidy. Anyway, I’ve opened an item on Connect if you’d like to vote on this:

Fact dimension relationships

If you build a dimension directly off a fact table (a fact dimension), when you add it to your cube and set the relationship between that dimension and the fact table you have a choice about the type of relationship you can use: you can use either a Fact relationship type or a Regular relationship type. I’ve always used Fact relationships but never known whether there was anything special about them – there was in some of the early Yukon betas but the functionality got dropped before RTM.

I asked someone on the dev team about this a few years ago and they admitted they didn’t know. I finally got round to asking again last week and TK Anand obligingly gave me an answer: fact relationships are essentially the same as Regular relationships, the only difference is that you can see the relationship types in the metadata, and some client tools may choose to display fact dimensions differently. So now you know.

UPDATE: Marco Russo (who I’m working with on… something at the moment), has done more research on this and it turns out there is an advantage to using a fact relationship when it comes to the SQL generated for drillthrough. Unfortunately the SQL generated is only slightly less rubbish than with a regular relationship. Marco has also opened an item on connect asking for drillthrough to be fixed which I urge everyone to vote on! You can find it here:

R and F#

One of my new year’s resolutions – or at least, something that got added to my list of stuff to do in the unlikely event I’ve got some time spare and can be bothered – was to learn more about statistics. I’ve only got a very basic grasp of the subject but, like data mining, it’s one of those things that seems to promise to be incredibly useful in my line of work. However it’s interesting to ponder that I’ve been working in BI for almost a decade and never so far needed to learn much beyond basic stats; my theory is that stats, like data mining, only tends to be used by highly skilled quantitative analysts, whereas the people I work with are business people whose maths skills are very average and who quite rightly don’t trust analysis done using methods they can’t understand.

Anyway, in my browsing on the subject I came across the all-of-a-sudden popular topic of R (see, the statistical programming language. I thought it might make an interesting blog entry, but today I saw John Brookmyre beat me too it so I’ll just link to him instead:

I also got interested in learning about F#, the functional programming language that will be included in VS2010 (for a good overview, see I was struck by some similarities with MDX and began to wonder about how it could be applied to BI; and yet again, a quick Google revealed Aaron Erickson had had the same idea and blogged extensively and intelligently on the subject:

It’ll be interesting to watch the uptake of F# in BI; from what I can see there’s already a lot of activity in the area of data manipulation and stats for F# (see for example Luca Bolognese’s blog) and I’m sure it’s only going to grow. The only complaint I’ve got is that here’s yet another addition to the Microsoft BI toolset and I’m yet to be convinced there’s any kind of company-wide strategy aimed at shaping all these tools into a coherent BI strategy. F# won’t be the language of BI in the way that Aaron wants; it’s more likely to end up as a technology island in the way Aaron specifically doesn’t want. But hey, the .NET guys have arrived at the party! The more the merrier.

Intelligencia Desktop Client

DISCLAIMER: since I licensed my SSRS custom data extension for SSAS to iT-Workplace, and since this technology is used in Intelligencia Desktop Client, I benefit financially from sales of this tool!

If you’re a regular reader of this blog, you’re no doubt aware that about a year ago I came up with an idea for a custom data extension for SSRS that makes it much easier to work with SSAS data sources, which subsequently became part of the Intelligencia Query product (which I blogged about here and has since gone through several releases). iT-Workplace, the company that sells Intelligencia Query, also produces a .NET MDX query-generator component suite called Intelligencia OLAP Controls (used in Intelligencia Query) which is aimed at third parties who want to add MDX query capabilities to their own apps, and midway last year I suggested to Andrew Wiles of iT-Workplace that he wrap these components in an exe and create his own standalone desktop client tool – and this became Intelligencia Desktop Client (IDC hereon), which I thought I’d review here in my continuing series on SSAS client tools.

IDC is distinctive because it deliberately doesn’t compete with most other Analysis Services ad hoc query tools – it’s aimed very much at the planning and budgeting market. At present the only version available is the Standard Edition which gives you query building and reporting functionality; at first impressions it does very much what other advanced ad hoc query tools like Proclarity do, but it has a lot of functionality important to financial users such as the ability to construct complex asymmetric sets on axes that many such tools lack. In fact it’s as much about creating forms for budget data entry via writeback as it is for querying and reporting; the closest comparison to make is with the PerformancePoint Excel addin although for it’s people who have built their own financial applications from scratch in Analysis Services rather than used PerformancePoint. The Enterprise Edition, which is still a CTP, will I believe offer yet more data entry and modelling functionality – I think Andrew wants to move towards incorporating cube building capabilities too.

Some features to note:

  • Creating query-based calculations is very easy, and it has an innovative spreadsheet-formula-like approach to doing so that financial users will feel very at home with:
    Unfortunately you can’t copy a calculation from a single cell to a whole range, yet, but I’ve asked for that for a future release…
  • It has a lot options for formatting the resulting table for printing or inclusion in a document:
    This ties in with its more mature sister product Intelligencia for Office 2007 which takes the form of Word and Excel addins, and is aimed at producing print-quality documents which incorporate live links to OLAP data.
  • This formatting functionality is also useful because IDC can publish queries to Reporting Services:
    Depending on what your requirements are this could be a very easy way of generating SSRS reports based on SSAS data. I wouldn’t go as far as to say that it makes IDC a proper SSRS report design tool since it doesn’t support the creation of any of the more advanced SSRS features; in fact IDC doesn’t have any charting capabilities (although I know this might be in the pipeline) so you can’t create reports with charts.
  • It has an ‘MDX Mode’ where you can turn off the navigation pane and enter whatever MDX you want, with the query results being displayed in the grid; very useful for those times when you have to write the MDX for a query yourself. It even has Intellisense!

Netezza launches data integration strategy for Microsoft BI

Interesting press release from Netezza here:

At the moment it only looks like there’s an OLE DB provider available, but the release says this is only the first part of the strategy. I wonder if Netezza is being considered as a supported data source for Analysis Services so it could be used with cubes in ROLAP mode, as with Teradata today?

%d bloggers like this: