Call today 0845 643 6463

Analysis Services

  • Azure Storage Backup Retention

    November 22nd, 2021 in Analysis Services, Azure, Blog, PowerShell

    This blog is a follow up to a previous blog I wrote about backing up Azure Analysis Services cubes in Azure, that blog can be found here. This blog shows how to implement a retention policy using PowerShell in Azure Runbooks to remove the backups after a set number of days. To create a new Runbook in the Azure portal, go to the relevant Automation account in the relevant resource group and then select Runbooks from the left hand pane. Note you…

    » Read more

  • 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

  • Azure Analysis Services Cube Backup

    December 23rd, 2020 in Analysis Services, Azure, Blog, PowerShell

    This blog is a quick guide on how to back up an Azure Analysis Services cube using PowerShell. The PowerShell is used in a runbook inside an Automation account in Azure to automate the backup process. A pre-requisite for this is to configure the backup storage settings, this details the location of the backups. Two parameters are passed into the runbook (shown below), where AnalysisServerName is the server name for the analysis services cube and…

    » Read more

  • 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

  • My experience in obtaining an MCSE

    August 7th, 2020 in Analysis Services, Blog, Business Intelligence, Integration Services, SQL Server

    Background I have recently completed my MCSE in Data Management and Analytics, and I wanted to share my experience of working towards and passing the exams that have led to me getting this MCSE. In this post I will cover some of the challenges I faced and provide some tips and advice, hopefully others following the same certification path, or other paths, will find this useful. I am a business intelligence developer at Purple Frog, I have…

    » 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

  • SSAS Tabular Deployment Wizard fails Newtonsoft.Json error

    July 25th, 2018 in Analysis Services, Azure, Blog

    The Scenario Deploying an Analysis Services Tabular model to SSAS Azure using the Analysis Services Deployment Wizard. Both Visual Studio 2017 & SQL Server 2017 installed on the client. Try and click on the ellipses to change the data source connection string or impersonation information results in a Newtonsoft.json error: “Could not load file or assembly ‘Newtonsoft.Json, Version 6.0.0.0, Culture=neutral, ……”…

    » Read more

  • Showing December to December to see opening values for a year in SSAS/MDX

    December 12th, 2016 in Analysis Services, Blog

    I came across an interesting MDX challenge this week; within a cube’s Date dimension, how to show December twice, once where it should be and again as the opening value for the following year. i.e. for each year I need to show Dec (prev yr), Jan, …, Nov, Dec. Why? Well if you consider the following pivot chart, you can clearly see growth from Jan to Feb, Feb to Mar, etc., but it’s very difficult to see the growth between Dec…

    » Read more

  • MDX Compare or Rank Similar Members

    August 13th, 2015 in Analysis Services, Blog

    Or should this be called: “Recreating DAX’s EARLIER function in MDX” – either way, a useful technique that solves a problem… MDX makes it very easy for us to compare one member against others, using functions such as RANK() etc. But how do we dynamically compare a member against a subset of other members? I came across a customer requirement recently where we had to rank a member against other members that had…

    » Read more