Call today 0845 643 6463

Tag Archive: Calculated Member

  • 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

  • How to add calculations in SSAS cubes

    January 16th, 2011 in Analysis Services, Blog, Business Intelligence, SQL Server

    One of the most useful aspects of a Business Intelligence system is the ability to add calculations to create new measures. This centralises the logic of the calculation into a single place, ensuring consistency and standardisation across the user base. By way of example, a simple calculation for profit (Income – Expenditure) wouldn’t be provided by the source database and historically would be implemented in each and every report. In a data…

    » 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

  • 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

  • MDX Calculated Member Spanning Multiple Date Dimensions

    August 5th, 2010 in Analysis Services, Blog

    It’s common in most cubes to have a number of different date dimensions, whether role playing, distinct, or a combination of both. Say for example, Entry Date, Posting Date and Accounting Period. There may also be numerous hierarchies in each date dimension, such as calendar and fiscal calendar, leading to a relatively complicated array of dates to worry about when calculating semi-additive measures. If we create a date related calculation…

    » Read more

  • Scope Problems with MDX Calculated Members

    November 18th, 2009 in Analysis Services, Blog

    We were recently investigating a problem for a client regarding the use of Scope within MDX calculated members. The code in question was similar to this: CREATE MEMBER CURRENTCUBE.[Measures].[Test Measure To Date] AS "NA", VISIBLE = 1; Scope([Date].[Calendar].MEMBERS); [Measures].[Test Measure To Date] = SUM(NULL:[Date].[Calendar].CurrentMember, [Measures].[Test Measure]); End Scope; Scope([Date].[Fiscal].MEMBERS); [Measures].[Test Measure To…

    » Read more