Blog

  • SSAS Tabular – NUMA and CPU Cores Performance

    [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

    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

    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
  • LogParser Performance Inserting into SQL Server

    Recently I’ve been using LogParser a lot to import log files, such as W3C logs from an IIS web server. The distasteful format of these files is made palatable by the awesomeness that is LogParser; it just takes care fo it for you. (Check out this SQLBits session on LogParser by Jonathan Allen (Twitter | Blog) for a good intro to LogParser) However I’ve been suffering with very poor performance with large files, so started to…

    » Read more
  • SQL Relay Interview

    A while back Richard Douglas interviewed me regarding my role as Chair of the SQL Relay 2013 conferences. You can read the resulting interview here! There’s only two weeks to go before the Relay starts in Glasgow, before travelling onto Leeds, Birmingham, Norwich, Cardiff, Reading and London. There are some spaces left at a few of the events, but register quick to make sure you don’t miss out! We’ve got Microsoft’s SQL…

    » Read more
  • SQL Relay 2013

    SQL Relay 2013 is only a month away! If you’ve not signed up yet to SQL Relay 2013 then you’d better get in there quick, as 2 of the 8 events are already full… What is SQL Relay? A series of 8 SQL Server conferences around the UK, held between 17th June and 27th June. They are FREE to attend (thanks to the generous sponsors!), and each contains a full day of technical talks covering different aspects of SQL Server. Who’s…

    » Read more
  • MDX Between Start Date and End Date

    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
  • Database Management PhD survey

    If you’re involved in database management, can you spare 20 minutes to complete a survey? Victoria Holt is studying for her PhD, and would very much appreciate it if you could help her out with her survey. Go on… Fill it in whilst you have a break over a cup of tea & a mince pie… Full in the survey here On behalf of Victoria, thanks

    » Read more
  • SSIS Expression Editor and Tester

    I stumbled upon a gem of a tool this morning, written by Darren Green (blog | twitter) of SQLBits & Bids Helper fame; the SSIS Expression Editor and Tester. Kudos to Darren, this is a great little tool for fine tuning and testing those complex SSIS expressions without having to keep running tests within SSIS. It’s downloadable with 3 versions, for SSIS 2005, 2008 & 2012, and doesn’t even need installing, it just runs as an…

    » Read more
  • Querying dimension members in MDX

    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