Call today 0845 643 6463

Analysis Services

  • 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

  • Microsoft Acquires Datazen

    April 16th, 2015 in Analysis Services, Blog, Dashboarding and Reporting

    Exciting news in the Microsoft Analytics space. On 14th April 2015 Microsoft announced they are acquiring Datazen – what does this mean, and is it a good or a bad thing? For all of PowerView’s good points, it’s still a very immature product. I’ve still not had a single customer take it seriously, aside from a few proof of concept implementations. It’s a good ‘personal analytics’ tool, but in my opinion…

    » 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

  • Analysis Services Tabular or Multidimensional? A performance comparison

    March 31st, 2014 in Analysis Services, Blog, SQL Server, Tabular

    Can SSAS Multidimensional be faster than SSAS Tabular for distinct counts on large datasets? We’ve all seen how fast the SSAS Tabular model can work – it’s an in-memory, heavily compressed analytics database. However you may have seen my previous posts on Tabular and NUMA, which show that at the moment Tabular is limited to a single CPU. This obviously limits its scalability and maximum performance. The situation A large…

    » 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

  • Dimension ProcessAdd in SSAS

    September 4th, 2013 in Analysis Services, Blog

    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

  • MDX Between Start Date and End Date

    April 14th, 2013 in Analysis Services, Blog

    How do you use MDX to find the number of items between two dates, when the start date and end date are different role playing dimensions? I had an interesting question from Ricardo Santos on another frog-blog post this week (MDX Calculated Member spanning multiple date dimensions). I answered Ricardo in the post, but it was a good question which I thought deserved a full blog post on its own. So thanks to Ricardo for prompting this. The Scenario…

    » Read more