Call today 0845 643 6463

Tag Archive: MDX

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

    December 12th, 2016 in Analysis Services, Blog

    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…

    » Read more

  • MDX Compare or Rank Similar Members

    August 13th, 2015 in Analysis Services, Blog

    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…

    » Read more

  • Excel creates inefficient MDX

    May 5th, 2015 in Analysis Services, Blog, Dashboarding and Reporting

    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…

    » Read more

  • MDX Between Start Date and End Date

    April 14th, 2013 in Analysis Services, Blog

    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…

    » Read more

  • Querying dimension members in MDX

    November 30th, 2012 in Analysis Services, Blog

    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…

    » Read more

  • Find first order date in MDX

    September 3rd, 2012 in Analysis Services, Blog

    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…

    » Read more

  • Debug MDX queries using Drillthrough in SSMS

    February 2nd, 2011 in Analysis Services, Blog

    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…

    » Read more

  • Deploying MDX calculation scripts with xmla

    October 22nd, 2010 in Analysis Services, Blog

    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,…

    » Read more

  • Will DAX replace MDX

    September 3rd, 2010 in Analysis Services, Blog, PowerPivot

    For those that haven’t yet heard of DAX, it’s an expression language developed by Microsoft to perform calculations against PowerPivot. Stepping back one step further, PowerPivot is essentially a local Analysis Services cube that runs within Excel 2010. I’ve heard plenty of comments from various sources about how DAX is the [multi-dimensional] query language of the future and how it’s going to kill off MDX. Ok….…

    » Read more

  • Calculate Run Rate (Full Year Projection) in MDX

    August 18th, 2010 in Analysis Services, Blog

    This post explains how to create an MDX calculated member that will take a value from the cube and project it forward to the end of the year. This provides a simple mechanism for calculating what your expected total will be at year end, based upon current performance. To do this more accurately you should use time series data mining models in SSAS and use DMX expressions to query the results, but this method is very simple and requires little…

    » Read more