Call today 0845 643 6463

Tag Archive: SSAS

  • 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

  • Excel creates inefficient MDX

    May 5th, 2015 in Analysis Services, Blog, Dashboarding and Reporting

    Whilst investigating a slow set of Excel pivot tables (connected to a multidimensional SSAS cube), I found some odd behaviour in how Excel generates its MDX, sometimes doing far more work than if required and causing significant performance reduction. Take the following example, just querying Customer Count by Promotion, against the AdventureWorks cube. The profile trace (just using “Query Begin”, “Query End” and…

    » Read more

  • SSAS Tabular String Imported as Integer

    August 9th, 2014 in Analysis Services, Blog, PowerPivot, Tabular

    Let me start by saying that I think the SSAS Tabular model is great. But…. there are a number of problems that Microsoft still need to get ironed out. Not least of which is being able to import data properly directly from CSV/Text files. Yes you can import directly from csv, but you are given absolutely no control over the process, and this can lead to some serious problems. One of these issues is the rather odd automated data type…

    » Read more

  • SSAS Tabular performance – NUMA update

    February 12th, 2014 in Analysis Services, Blog

    How does the SSAS 2012 Tabular model performance change when you add more CPU sockets / NUMA nodes? In my last post (SSAS Tabular NUMA and CPU Cores Performance) I presented the results of some testing I’d been doing on the scalability of the SSAS 2012 Tabular model. Specifically with the performance of distinct count measures over large data volumes (50-200m distinct customers). The conclusion was that moving from 1 NUMA node (CPU socket)…

    » 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 – NUMA and CPU Cores Performance

    December 13th, 2013 in Analysis Services, Blog

    [UPDATE] After further investigation, I found that the tests in this post were inacurate and the results unreliable. Updated NUMA test results here In my last post (SSAS Tabular Performance – DefaultSegmentRowCount) I presented some analysis of the query performance impact of changing the DefaultSegmentRowCount setting. This post describes the next tests that I ran on the same system, investigating the impact of restricting SSAS to just 1…

    » 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