Call today 0845 643 6463

Analysis Services

  • Dynamic MDX in Reporting Services

    September 18th, 2008 in Analysis Services, Blog, Reporting Services, SQL Server

    After a couple of days off work last week with SQL Bits III and SQL 2008 Unleashed, it’s back to the grindstone this week; however before I get onto the serious stuff I’d like to say thank you to the organisers of both events. Watching you on stage headbanging to Rockstar – legendary! (You can see for yourself here and here…). Anyway, back to MDX… This post explains how you can build a dynamic MDX query in Reportins…

    » Read more

  • Mosha's MDX Studio

    September 12th, 2008 in Analysis Services, Blog

    I almost feel embarrassed…, I’ve been writing this blog for over 9 months now, and I have yet to mention Mosha, although in my defence, there is a link to his blog in the links section to the right. As many/most of you may know, Mosha Pasumansky is one the key brains behind designing the MDX language and Analysis Services – nuff said? Over the last year he has been working on a pet project, MDX Studio. It’s an MDX query…

    » Read more

  • Ranking results from MDX queries

    May 21st, 2008 in Analysis Services, Blog

    This post explains how you can create a ranking of data from an OLAP MDX query. This will take the results from the query, and assign a ranking to each row. i.e. 1st, 2nd, 3rd best rows etc. The first thing to do is to decide two things. 1) What measure do you want to rank by 2) What data set are you returning Let’s assume we want to rank all stores by sales value. The basic non-ranked MDX query would be something like this SELECT…

    » Read more

  • Semi Additive Measures using SQL Server Standard

    April 21st, 2008 in Analysis Services, Blog, SQL Server

    One of the most frustrating limitations of SQL Server 2005 Standard edition is that it doesn’t support semi additive measures in SSAS Analysis Services cubes. This post explains a work around that provides similar functionality without having to shell out for the Enterprise Edition. What Are Semi Additive Measures? Semi Additive measures are values that you can summarise across any related dimension except time. For example, Sales and…

    » Read more

  • Extract Datasource and Query from Excel Pivot

    January 23rd, 2008 in Analysis Services, Blog, SQL Server

    Have you ever tried to reverse engineer an Excel pivot table? It’s not as easy as you would think! Whether you just want to find out the datasource details, or identify the query that was used, there is just no simple way of getting Excel to tell you. The macro below will loop through every sheet in a workbook, and will document the datasources, SQL or MDX queries as well as the page, row, column and data fields. To use it, add it into…

    » Read more

  • Excel Addin for Analysis Services

    December 15th, 2007 in Analysis Services, Blog, SQL Server

    For any users of Analysis Services, if you haven’t already downloaded the Excel (2002/2003) addin you’re missing out. It’s a free download from Microsoft which significantly expands Excel’s cube querying ability. Well recommended! Get it at http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en [Update 31 Aug 2023: Microsoft have now removed this download –…

    » Read more

  • Convert MDX fields to SQL

    December 15th, 2007 in Analysis Services, Blog, Reporting Services, SQL Server

    A number of our customers have reporting systems that use both MDX and SQL, retrieving data from both OLAP and SQL Server databases. This generates the problem of converting an MDX field ([Dimension].[Hierarchy].&[Attribute]) into SQL Server field value (Attribute). The following code is a Reporting Services custom code section that will rip off the MDX and leave you with the value.     Public Function MDXParamToSQL(Parameter As String,…

    » Read more