Call today 0845 643 6463

Analysis Services

  • Querying dimension members in MDX

    November 30th, 2012 in Analysis Services, Blog

    Today we’re back looking at MDX with a quick how-to. How do you extract a list of dimension members using MDX? This could be used for example to populate a user interface/parameter of a report/dashboard. Let’s look at two scenarios; normal dimensions, and then hierarchies. All the queries here use the “Adventure Works DW 2008 R2” “Adventure Works” cube. Standard dimension attributes So how do we get a list of…

    » Read more

  • 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

  • PowerPivot Vs QlikView 101 – SQLBits Video

    November 10th, 2011 in Analysis Services, Blog, Business Intelligence, PowerPivot, QlikView

    PowerPivot Vs QlikView 101 – SQLBits Video The video is now available from my PowerPivot and QlikView talk at SQLBits 9 in Liverpool in September 2011. You can download or watch the video here. In this 1 hour session I create interactive dashboards from scratch in both PowerPivot and QlikView, showing how to set up the data model, overcome common pitfalls and build the dashboards. I create almost identical dashboards in both systems and…

    » Read more

  • Video: Automating SSAS OLAP Cube documentation

    May 26th, 2011 in Analysis Services, Blog, Business Intelligence, Reporting Services

    Automating OLAP cube documentation – SQLBits presentation For anyone that missed my presentation at SQLBits 8 in April, the video is now available here. In this 1 hour session I present a method of automating the creation of documentation for SSAS OLAP cubes by using DMVs (dynamic management views) and spatial data, querying the metadata of the cube in realtime. The results include the BUS matrix, star schemas, attribute lists, hierarchies…

    » Read more

  • The Business Intelligence Semantic Model (BISM)

    May 18th, 2011 in Analysis Services, Blog, Business Intelligence, PowerPivot

    I’m a happy chap. Why? Because I read a blog post yesterday by T.K. Anand (SSAS Principal Group Program Manager) about the vision and roadmap of Analysis Services. There were slightly concerning questions last November (following the PASS conference) surounding the future of Analysis Services, or more specifically the UDM, the dimensional model that we all know and love in SSAS 2005 & 2008. The arrival of PowerPivot into the Microsoft…

    » Read more

  • Debug MDX queries using Drillthrough in SSMS

    February 2nd, 2011 in Analysis Services, Blog

    One of the great features of using Excel to browse an SSAS OLAP cube is the drillthrough ability. If you double click on any cell of an OLAP pivot table, Excel will create a new worksheet containing the top 1000 fact records that went to make up the figure in the selected cell. N.B. The limit of 1000 rows can be altered, as per one of my previous blog posts here. This feature is pretty well known, but not many folk realise how easy it is to…

    » 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

  • OLAP Cube Documentation in SSRS part 3

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

    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

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

    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