Blog

  • Will DAX replace MDX

    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
  • Calculating Run Rate in DAX for PowerPivot

    In my previous post I explained how to create a calculated MDX member that projects full year data (sales etc.) based on existing year to date data. In this post I’ll be doing exactly the same but in DAX, the new expression language used to enhance PowerPivot data. As it’s the same desired outcome, I’m not going to repeat the background, you’ll have to look at my previous post for that. The expressions below assume that…

    » Read more
  • Calculate Run Rate (Full Year Projection) in MDX

    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
  • Seagate Momentus XT Hybrid Drive

    I thought I’d take a break from writing posts about Business Intelligence and SQL Server, and instead share with you my elation at finding a laptop hard disk that quite simply makes the world a better place, the Seagate Momentus XT hybrid drive. When I purchased my curent laptop (Dell XPS M1530 if you’re interested, with 4Gb RAM) I was presented with a choice between a fast 7200rpm 200Gb drive or a slower 5400rpm 320Gb drive. Due to…

    » Read more
  • MDX Calculated Member Spanning Multiple Date Dimensions

    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
  • Data Warehousing: Kimball vs Inmon

    Anyone involved in the Business Intelligence space has had their head in the sand if they are not aware of the long running, and more often than not misunderstood, debate between the two conceptual models of data warehouse design. Bill Inmon has recently posted an article on www.b-eye-network.com discussing the matter, and to his credit, has tried to put forward a number of balanced pros and cons of each methodology. I’ll state now that…

    » Read more
  • Leap before you can land

    Leap before you can land… Research scientists at the Southern Illinois University have concluded that primitive frogs learned to jump before they learned to land. The result being a rather inelegant crash landing. I’m sure the frogs were happy that they achieved their goal of getting from A to B, but I do feel sorry for them having to endure the presumably painful landing, not to mention the indignity of having the rest of the pond witness…

    » Read more
  • SQL User Group Session 24 June 2010

    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

    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
  • SQLBits and Microsoft BI conferences

    Following the launch of SQL Server 2008 R2 this week, the ever dedicated SQL Bits team are putting on the next installment tomorrow with SQL Bits VI – ‘the Sixth Sets’. The Purple Froggers will of course be there, hopefully in time for a pre-conference bacon butty! Also worth mentioning is ‘Microsoft Solutions for Business Intelligence’, a seminar that Microsoft are holding at their Reading campus on May 12 2010.…

    » Read more