Blog

  • OLAP Cube Documentation in SSRS part 3

    This is the 3rd and final post in this series of blog posts, showing how you can use SQL Server Reporting Services (SSRS), DMVs and spatial data to generate real time automated user guide documentation for your Analysis Services (SSAS) OLAP cube. Part 1 – Creating the DMV stored procs Part 2 – Create the SSRS reports Part 3 – Use spatial data and maps to create a star schema view Download Source Code UPDATE: I presented a 1…

    » Read more
  • OLAP Cube Documentation in SSRS part 2

    In my previous post I described how to create a number of stored procedures that use Dynamic Management Views (DMVs) to return the metadata structure of an SSAS 2008 OLAP cube, including dimensions, attributes, measure groups, BUS matrix etc. In this post I’m going to use those procs to create a set of SSRS 2008 reports that will serve as the automated documentation of your cube. I’m going to make the following assumptions:…

    » Read more
  • OLAP Cube Documentation in SSRS part 1

    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
  • BCS talk on Business Intelligence – Slides

    Thank you to the BCS Shropshire branch for opening your doors to the Purple Frog team. We’re delighted to be given any chance to talk about Business Intelligence, and spread the word to increase awareness of what it is and how it all works. We find it a fascinating world to work in, and hope that there were some converts in the audience! For those that asked, here’s a copy of the slides for you to download. As the presentation was…

    » Read more
  • BCS talk on Business Intelligence

    On Monday 20th September, Alex & Hollie from Purple Frog Systems will be giving a talk on business intelligence to the Shropshire branch of the British Computer Society. The event is free to attend, even for non BCS members, and will be held from 6.15pm at the Telford campus of Wolverhampton Uni. We’ll be providing an introduction to BI, explaining what it is, how it works and how it can benefit your organisation. We’ll present…

    » Read more
  • 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