Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

Monthly Archives: January 2009

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:

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:

WITH MEMBER MEASURES.TEST AS 1SELECT [Measures].[Internet Ratio to Parent Product] ON 0,[Product].[Product Categories].MEMBERS ON 1FROM [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:

WITH SET TEST AS {}SELECT [Measures].[Internet Ratio to Parent Product] ON 0,[Product].[Product Categories].MEMBERS ON 1FROM [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.

Share this:

Like this:

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 tohttp://www.sqlbits.com/information/PublicSessions.aspxand choose the sessions you’d like to see.

Share this:

Like this:

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.

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.

Share this:

Like this:

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?

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…

Share this:

Like this:

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:

Build an aggregation for the above query, or

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]

Share this:

Like this:

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:https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=403083

Post navigation

Follow Blog via Email

Social

Need some help?

As well as being a blogger, I'm an independent consultant specialising in Analysis Services, MDX, DAX, Power BI, Power Query and Power Pivot. I work with customers from all round the world solving design problems, performance tuning queries and delivering training courses, and I am happy to work on short-term engagements. For more details see http://www.crossjoin.co.uk