Call today 0845 643 6463

Tag Archive: Cube

  • Tabular Cube Processing Report

    December 9th, 2020 in Analysis Services, Blog, Business Intelligence, Power BI, Tabular, Uncategorized

    I have created a Power BI report which provides detail on the state of processing in a Tabular Cube.The report uses the cube’s dynamic management views to provide information about the cube’s partitions. To use the tabular cube processing report, you need to insert the following information: Server Database (Cube Name) Once entered and assuming the connection is fine you need to accept some native queries. These statements are select…

    » Read more

  • Renaming an SSAS Tabular Model

    February 12th, 2014 in Analysis Services, Blog

    I came across a frustrating problem today. I’d just finished processing a large tabular cube (SQL Server 2012), which had taken 11 hours in total. On trying to connect to the cube to test it, I’d made a schoolboy error; The database was named correctly, but the model inside it was named MyCubeName_Test instead of MyCubeName. No problem, I’ll just right click the cube in SSMS and rename it. Well, no, there is no option to rename…

    » Read more

  • SSAS Tabular performance – DefaultSegmentRowCount

    December 11th, 2013 in Analysis Services, Blog

    I’m currently investigating a poorly performing Tabular model, and came across some interesting test results which seem to contradict the advice in Microsoft’s Performance Tuning of Tabular Models white paper. Some background: 7.6Gb SSAS tabular cube, running on a 2 x CPU 32 core (Xeon E5-2650 2Ghz, 2 x NUMA nodes) server with 144Gb RAM SQL Server 2012 SP1 CU7 Enterprise 167m rows of data in primary fact 80m distinct CustomerKey…

    » 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

  • 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

  • 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

  • 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

  • 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