Call today 0845 643 6463

Tag Archive: Analysis Services

  • Variables in DAX

    September 10th, 2021 in Analysis Services, Blog, DAX, Power BI

    Variables can simplify your DAX code, help with debugging and help improve performance. To use variables in your DAX measure you need to declare a variable using the VAR keyword, give the variable a name, and then assign an expression to the variable. You can use multiple variables in a measure but when using variables you must use a RETURN statement to specify the final output to be returned. To show how to make use of variables I’ll go…

    » Read more

  • SSAS Tabular Calculation Groups – avoid using SELECTEDMEASURE or ISSELECTEDMEASURE

    June 17th, 2020 in Analysis Services, Blog, Power BI, Tabular

    Introduction: There is a very serious limitation in the behaviour of calculation groups when using the SELECTEDMEASURE or ISSELECTEDMEASURE functions, and we recommend not using them. Why? If a user creates their own custom calculations within their Power BI report (or composite model) then the value of SELECTEDMEASURE changes, breaking your calculation group logic. Let me explain with an example: In a tabular cube we may look to use calculation…

    » Read more

  • Cube deployment error: Cannot resolve all paths while de-serializing Database

    May 7th, 2020 in Analysis Services, Blog, Tabular

    I recently came across the following error while deploying a tabular cube using the Analysis Services Deployment Wizard: My updates for this deployment included removing a table from the cube. As part of the cube deployment options I chose to “Retain roles and members”. However the issue here was that an existing role was referencing the table I had deleted, for example the below image shows the Role Manager in Visual Studio and…

    » Read more

  • 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

  • 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

  • MDX Calculated Member Spanning Multiple Date Dimensions

    August 5th, 2010 in Analysis Services, Blog

    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