Tag Archive: SSAS

  • Dimension ProcessAdd in SSAS

    When you have very large dimensions in SQL Server Analysis Services (SSAS) cubes, the time taken to process them can cause a problem. This post builds upon an excellent walkthrough of the ProcessAdd option written by Daniel Calbimonte on MSSQLTips.com. and shows how to automate the identification of new data to be added. What are the main options for processing a dimension?  (Technet details here) Process Full – the whole dimension is…

    » Read more
  • Find first order date in MDX

    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
  • Video: Automating SSAS OLAP Cube documentation

    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
  • How to add calculations in SSAS cubes

    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
  • 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
  • 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
  • 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
  • Excel Cube Pivot drillthrough limited to 1000 rows

    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