Analysis Services · MDX · Multidimensional

Analysis Services Query Analyzer

Chris Webb

Last week a new, free tool for analysing the performance of MDX queries on SSAS Multidimensional was released: Analysis Services Query Analyzer. You can get all the details and download it here:

https://ssasqueryanalyzer.github.io/

…and here’s a post on LinkedIn by one of the authors, Francesco De Chirico, explaining why he decided to build it:

https://www.linkedin.com/pulse/asqa-10-released-francesco-de-chirico/

I’ve played around with it a bit and I’m very impressed – it’s a really sophisticated and powerful tool, and one I’m going to spend some time learning because I’m sure it will be very useful to me. It’s an AddIn for SQL Server Management Studio and captures Profiler and Perfmon data for an MDX query when you run it, does so for a cold and warm cache, and then displays the results in a variety of graphs and charts. And it even has an MDX formatter built in!

image

You can find a full list of features here, and when you download the tool it comes with very detailed documentation. Definitely a must-have for all serious SSAS Multidimensional developers.

Published by Chris Webb

My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.

13 thoughts on “Analysis Services Query Analyzer”

    1. CoDe is chronic Farce! – Milan, Italy – Francesco De Chirico, is a consultant, trainer and speaker specialized in modeling and developing complex Business Intelligence solutions with the whole Microsoft BI stack. He has been working with Analysis Services since 2001 and has a strong knowledge of the MDX and DAX languages. In 2007 Francesco joined SolidQ where he worked for 11 years as a Mentor in the Italian branch. In the last years, he also focused his activity on Big Data, Machine Learning and all the technologies and services of the Microsoft Cortana Intelligence Suite. Co-leader of the PASS Global Italian Virtual Group, active member of the Italian User Group of SQL Server since 2007, regular speaker at technical conferences and events like PASS SQLSaturday, Francesco has been Microsoft Certified Trainer for many years and holds several Microsoft certifications including the prestigious SSAS Maestro for BI certification (2012). In 2017, he also achieved the Microsoft Professional Program Data Science certification. He is the project creator and co-developer of the Analysis Services Query Analyzer tool.
      Francesco De Chirico says:

      Hi Ibrahim,
      in the ASQA documentation (paragraph 1.4), Alberto and I explain why we decided not to support DAX queries (at least in this first version of ASQA). However, if we will receive a significant number of requests for this feature, we may consider the possibility of undertaking this new challenge as well.
      Regards
      Francesco

      Reply
  3. Pingback: SSAS Query Analyzer – Curated SQL
  4. Pingback: Analysis Services Query Analyzer 1.0 – UGISS

  5. Hi Chris
    I am getting an error that saying MDX cue not found. Look over not seeing anywhere gives me a change connection option, Do you know how to fix it?

    Reply
    1. CoDe is chronic Farce! – Milan, Italy – Francesco De Chirico, is a consultant, trainer and speaker specialized in modeling and developing complex Business Intelligence solutions with the whole Microsoft BI stack. He has been working with Analysis Services since 2001 and has a strong knowledge of the MDX and DAX languages. In 2007 Francesco joined SolidQ where he worked for 11 years as a Mentor in the Italian branch. In the last years, he also focused his activity on Big Data, Machine Learning and all the technologies and services of the Microsoft Cortana Intelligence Suite. Co-leader of the PASS Global Italian Virtual Group, active member of the Italian User Group of SQL Server since 2007, regular speaker at technical conferences and events like PASS SQLSaturday, Francesco has been Microsoft Certified Trainer for many years and holds several Microsoft certifications including the prestigious SSAS Maestro for BI certification (2012). In 2017, he also achieved the Microsoft Professional Program Data Science certification. He is the project creator and co-developer of the Analysis Services Query Analyzer tool.
      CoDe is chronic Farce! says:

      Hi Kate,
      could you kindly report the problem with more details to the ASQA support email address (ssasqueryanalyzer@gmail.com) or in the issues page of the ASQA Github project repository (https://github.com/SSASQueryAnalyzer/SSASQueryAnalyzer/issues)?
      We will take care of the problem as soon as possible
      Anyway, regarding the connection, ASQA MDX editor window has the same menu options of a standard SSMS MDX editor window plus the specific ASQA features. Hence you can change connection in the same way you do it in the standard SSMS MDX editor window (right click –> Connection –> Change Connection)
      Thank you

      Francesco

      Reply
  6. Pingback: Analysis Services Query Analyzer - SSWUG.ORG

  7. Hi Chris,
    Thank you for this article. We use SQL profiler to investigate performance of a report (which can contain several mdx queries). Would it be possible to do the same in ASQA as well? I mean, run the report and produce a summary in ASQA with info regarding performance of each mdx query in the report?

    Reply
    1. CoDe is chronic Farce! – Milan, Italy – Francesco De Chirico, is a consultant, trainer and speaker specialized in modeling and developing complex Business Intelligence solutions with the whole Microsoft BI stack. He has been working with Analysis Services since 2001 and has a strong knowledge of the MDX and DAX languages. In 2007 Francesco joined SolidQ where he worked for 11 years as a Mentor in the Italian branch. In the last years, he also focused his activity on Big Data, Machine Learning and all the technologies and services of the Microsoft Cortana Intelligence Suite. Co-leader of the PASS Global Italian Virtual Group, active member of the Italian User Group of SQL Server since 2007, regular speaker at technical conferences and events like PASS SQLSaturday, Francesco has been Microsoft Certified Trainer for many years and holds several Microsoft certifications including the prestigious SSAS Maestro for BI certification (2012). In 2017, he also achieved the Microsoft Professional Program Data Science certification. He is the project creator and co-developer of the Analysis Services Query Analyzer tool.
      CoDe is chronic Farce! says:

      Hi Dorota,
      the short answer is “no, you must execute a specific analysis for each MDX query.”
      This because, as stated in the ASQA documentation (in paragraph 2.1.2: “ASQA SSAS Assembly”), the ASQA SSAS Assembly is forced to manage only one analysis at a time. This makes perfect sense since two analyses running simultaneously on the same server would negatively influence each other and, consequently, the results of both would not be accurate.

      Francesco

      Reply
  8. Pingback: Power Tools for Power Work | Flávio Fernandes
  9. Pingback: Power Tools for Power Work - Flávio Fernandes

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.