Call today 0845 643 6463

Analysis Services

  • OLAP Cube Documentation in SSRS part 1

    September 25th, 2010 in Analysis Services, Blog, Business Intelligence, Reporting Services, SQL Server

    Being a business intelligence consultant, I like to spend my time designing data warehouses, ETL scripts and OLAP cubes. An unfortunate consequence of this is having to write the documentation that goes with the fun techy work. So it got me thnking, is there a slightly more fun techy way of automating the documentation of OLAP cubes… There are some good tools out there such as BI Documenter, but I wanted a way of having more control over…

    » 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

  • 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

  • SQL User Group Session 24 June 2010

    June 9th, 2010 in Analysis Services, Blog, SQL Server

    I’m excited to be presenting another session to the South Wales SQL Server User Group. On Thursday 24th June 2010, Eversheds in Cardiff are kindly hosting the event, to run from 18:45 to 21:00. The event is free, and you’ll even get pizza thrown in – what more can you ask for? Oh yes, some BI content… My session will cover data warehouse modelling, including a number of hands on business case studies including…

    » Read more

  • SQL Server 2008 R2 – PowerPivot and Master Data Services

    May 18th, 2010 in Analysis Services, Blog, Integration Services, Reporting Services, SQL Server

    Purple Frog spent a very interesting day at Microsoft last week, at one of their many events promoting the launch of SQL Server 2008 R2. Rafal Lukewiecki presented an entertaining (as always!) and informative series of talks covering the release, focusing on the enhanced Business Intelligence tools available. The primary changes to note are Power Pivot – An in memory, client side add-in to Excel, that allows users to create virtual cubes on…

    » Read more

  • Excel Cube Pivot drillthrough limited to 1000 rows

    March 25th, 2010 in Analysis Services, Blog

    When browsing a cube using Excel 2007, you can drillthrough the measures to display up to 1000 rows of the transaction level source data. I often get asked whether this limit of 1000 rows is configurable – well the good news is yes it is. There is an option in the actions tab of the BIDS cube designer which allows you to specify the maximum rows, but helpfully this is ignored by Excel. Instead, you have to set it in Excel when you create a…

    » Read more

  • MDX Sub select Vs WHERE clause

    March 2nd, 2010 in Analysis Services, Blog

    I’ve just read an interesting thread on the SQL Server Developer Center forum, regarding how to filter results. Specifically the difference in MDX between using a subselect SELECT x on COLUMNS, y on ROWS FROM ( SELECT z on COLUMNS FROM cube)) or using a where clause SELECT x on COLUMNS, y on ROWS FROM cube WHERE z In a simple query they produce the same results, but what is the actual difference? You can read the full thread here, but to…

    » 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

  • Excel 2007 and SSAS 2008 Error

    October 30th, 2009 in Analysis Services, Blog

    I was working on a new SSAS 2008 cube today, and came across an error which Google was unable to help with. I thought I’d post the solution here to help anyone else who may encounter it. The cube in question will be primarily be accessed using Excel 2007, so I’d been dutifully testing it along the way to ensure all was well. And then, after a number of changes the following error appeared when connecting to the cube from Excel to…

    » Read more