Call today 0845 643 6463

# Tag Archive: MDX

• ## Showing December to December to see opening values for a year in SSAS/MDX

I came across an interesting MDX challenge this week; within a cube’s Date dimension, how to show December twice, once where it should be and again as the opening value for the following year. i.e. for each year I need to show Dec (prev yr), Jan, …, Nov, Dec. Why? Well if you consider the following pivot chart, you can clearly see growth from Jan to Feb, Feb to Mar, etc., but it’s very difficult to see the growth between Dec…

• ## MDX Compare or Rank Similar Members

Or should this be called: “Recreating DAX’s EARLIER function in MDX” – either way, a useful technique that solves a problem… MDX makes it very easy for us to compare one member against others, using functions such as RANK() etc. But how do we dynamically compare a member against a subset of other members? I came across a customer requirement recently where we had to rank a member against other members that had…

• ## Excel creates inefficient MDX

Whilst investigating a slow set of Excel pivot tables (connected to a multidimensional SSAS cube), I found some odd behaviour in how Excel generates its MDX, sometimes doing far more work than if required and causing significant performance reduction. Take the following example, just querying Customer Count by Promotion, against the AdventureWorks cube. The profile trace (just using “Query Begin”, “Query End” and…

• ## MDX Between Start Date and End Date

How do you use MDX to find the number of items between two dates, when the start date and end date are different role playing dimensions? I had an interesting question from Ricardo Santos on another frog-blog post this week (MDX Calculated Member spanning multiple date dimensions). I answered Ricardo in the post, but it was a good question which I thought deserved a full blog post on its own. So thanks to Ricardo for prompting this. The Scenario…

• ## Querying dimension members in MDX

Today we’re back looking at MDX with a quick how-to. How do you extract a list of dimension members using MDX? This could be used for example to populate a user interface/parameter of a report/dashboard. Let’s look at two scenarios; normal dimensions, and then hierarchies. All the queries here use the “Adventure Works DW 2008 R2” “Adventure Works” cube. Standard dimension attributes So how do we get a list of…

• ## Find first order date in MDX

I had an interesting question the other day; “how do I find the date of the first order/activity for a given customer/employee/product etc in MDX”? Well you’ll be pleased to know that it’s pretty simple. The sample code bellow will work with the AdventureWorks DW 2008R2 cube. First of all we need to filter the date dimension to contain only those with orders. Then take the first item of the resulting set, then find its…

• ## Debug MDX queries using Drillthrough in SSMS

One of the great features of using Excel to browse an SSAS OLAP cube is the drillthrough ability. If you double click on any cell of an OLAP pivot table, Excel will create a new worksheet containing the top 1000 fact records that went to make up the figure in the selected cell. N.B. The limit of 1000 rows can be altered, as per one of my previous blog posts here. This feature is pretty well known, but not many folk realise how easy it is to…

• ## Deploying MDX calculation scripts with xmla

If you’re a Business Intelligence developer I assume you have BIDS Helper installed. If not then stop reading this post and go and install it. Now. It adds a number of very useful features to the Business Intelligence Development Studio which provide help with many aspects of SSIS, SSRS and SSAS development. One of my favourite utilities is the Deploy MDX Script function. This takes the calculation script for an SSAS cube (named sets,…