Configure SQL Server Profiler as an External Tool …


Introduction

The November 2022 version of Power BI Desktop shipped 3 new DAX functions, EVALUATEANDLOG, TOCSV and TOJSON. While the latter two may be of questionable value overall, the new EVALUATEANDLOG feature as explained here provides the potential to help more easily debug DAX. In a nutshell, EVALULATEANDLOG logs information that can be viewed using a SQL Server Profiler trace. This article explains how to do that in detail as well as how to easily configure SQL Server Profiler as an external tool for Power BI Desktop for even greater convenience.

 

First Things First

The first thing that you will need is to install SQL Server Management Studio (SSMS) from this link. The installation of SSMS includes the installation of the SQL Profiler tool, PROFILER.EXE. Once installed, launch SSMS. Make sure that you also have your Power BI Desktop file open. Once SSMS launches, you will be presented with a Connect to Server dialog as shown below:

Greg_Deckler_0-1670183849018.png

For Server type, choose Analysis Services. Also choose Windows Authentication for the Authentication. For the Server name, in this case localhost:54256 there are a number of ways to identify the Analysis Services server used by the current Power BI Desktop application. One of the easiest is if you have any of my MSHGQM tools installed or any of the tools I created for Enterprise DNA. Simply launch one of these tools, such as Quick Measures Pro and in the upper-left of the screen the current server and database are identified as shown below:

Greg_Deckler_1-1670184095865.png

The Data Source identifies the server while the Initial Catalog identifies the database or tabular cube. You can simply use your mouse to select the text after the first equals (=) sign up until the first semi-colon (;) and use Ctrl-C and then Ctrl-V to copy and paste this information into the Server name field.

 

Now click the Connect button. Within the Object Explorer, expand Databases and find the database corresponding to the Initial Catalog as shown below:

Greg_Deckler_2-1670184522424.png

 

Select this database and then choose ToolsSQL Server Profiler from the menu as shown in the following image:

Greg_Deckler_3-1670184623739.png

Your First Trace

SQL Profiler will launch and ask for connection information once again. You should be able to simply click the Connect button as the connection information will be the same as the most recently entered information for SSMS. Once connected, a Trace Properties dialog appears as shown below:

Greg_Deckler_5-1670184907857.png

Click on the Events Selection tab at the top. On the Events Selection tab, check the box for Show all events and then under Query Processing find and check the box for DAX Evaluation Log as shown in the following image:

Greg_Deckler_6-1670185084021.png

Now simply click the Run button and you have initialized your first trace for the DAX Evaluation Log!

Test it Out!

Switch to Power BI Desktop and create a simple measure like the following:

Measure 2 = EVALUATEANDLOG(SUM('Table'[Column2]))

Place this meausre into a visual and your trace should catch the data generated by EVALUATEANDLOG. You can force a refresh of any visuals on the page using the Performance analyzer pane and this will also be captured within the trace. Within the trace window in SQL Server Profiler, find the line corresponding to an EventClass of DAX Evaluation Log and select it to view the output of EVALUATEANDTRACE as shown below:

Greg_Deckler_7-1670185524941.png

As shown in the image, the evaluation of the formula within the EVALUATEANDLOG function resulted in an output of 21.

Making it Easier

The use of SQL Server Profiler with Power BI Desktop can be made even easier and more seemless by configuring SQL Server Profiler as an external tool. This can be done by downloading SQLProfiler.pbitool.json from this site. The contents of the file are simply:

{
  "version": "1.0",
  "name": "SQL Profiler",
  "description": "SQL Profiler",
  "path": "C:\\Program Files (x86)\\Microsoft SQL Server Management Studio 18\\Common7\\PROFILER.exe",
  "arguments": "/A \"%server%\" /D \"%database%\"",
  "iconData": "image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAIAAAACACAYAAADDPmHLAAAACXBIWXMAAAsTAAALEwEAmpwYAAAHM0lEQVR4nO2dW2gcVRjHN0lFLSpKiMleZsaHtBZr6yWprVpqpWpR0RIvrQjigxa1DyIiSBEVHxSLUXywCFH7phClamuFolYFH6yQlz6IBlS02FatbdKbtVIv/y+dQEx2d86ZOZdJ5v+Dj4XszHzfOeeX2ZkzszulEiGEqNJaq9UWhWF4B+JORraoVqvLoyg6w/egKoFi5wRBsBPxL8Nc4B9qH/r2Rt/j25RyuTwbxQ777qwZHCcRa3yPc0Ng6bocdNKMDvTxCbyu8j3WdUFhG313UBEiluAW3+M9BRQ14LtzihK5lIACFFwCCuBHApwq3ux77MdIEgDFDiHeZvwvhgyI8AckuM73+CcKgPPYtb5rzBvSJ4b2BiLBCq+NoQD6JAmAPcQOvP6sKMExbO9ab42hAPoo7AEGZHYVIuzJvQQUQB8VAWS5SqUyV1OC5c4bQwH0URVAgAQXQoK9ihIcxbLXOG0MBdBHRwAhlmCfhgTLnDWGAuijK4AQRdE8VQmw3CHkWOykMRRAnzQCxOstxHv7FSUYdSIBBdAnrQDxupfg/d9VJUBcYbUxFECfLAIIOCa4VEOCEblTy1pjKIA+WQUQYgkOqEqA114rjaEA+pgQQMCB4WU6EoRh2GO8MRRAH1MCCPIZL5/1KhIg9mPZBUYbQwH0MSlAvL0lcuqnKMFPHR0dZxlrDAXQx7QAAnbvV2G9w4oSPGCsMRRAHxsCCFhvKeKIggCvGGsMBdDHlgAC9gRXK+wJUm9/ChRAH4M3hKQNCuATClBwKEDBoQAFB/2yhgIUGPRLJf6CBwUoKuib9RSg4GAv0Ic+2oXXvygAMYbNiaYpUID8QQEKDgUoOE4FwAHMiwnJVhtLRpQIkucZ+o0lg203NUl0LIqiLmPJiBJBwjwD3ltpMl8LNvpevURhGD5sMhFRJ2g8z7DZRr42WPUY4ofYvF0Y/HtsJCLqTJhnOIH4HvFkT0/Pab7rIoQQQgghhBBCyAygVqudGQTBBo0fNdKJ3dju85JDty6Z/MB6T8s2LNSVuT6sU0UMxt/vO4LXLWEYXqS7Hd/IbOAXFjt4fB57G3K1aNQlU9Uf2q4rbX0yb4/4rc52RqMoukB3ELyBoh901cmBxhVGdORdDuvSrg/LvtlEpi3pRsMDKHizww5+XaOuNzwIoFNfwx99ggDHS3p7O3+g2I/y2MHy69yuBZCcGvX92Wxb3d3dp6cbEcc4FkD5IUoUwBGuBECej5GuTaMuCuAC2wJg+z8intV9mCIFcISCANd7qqupAPKUTt1tyjoUYBIUgAJQAApAASgABaAAFIACUAD1+iiA5boogAsoAAVoKoB8SwUdd16aiKLo3Ax1UQAXOLgWcBg5XkWHnKNZFwVwgcOrgZ8hXatGXRTABS4vB+t88ZQCOMLx/QCbNOqiAC5AQ95xJYBmB1MAF6Ah91GAAgsQ33v/CQUoqAAxs9A5j6NRXyMOpg00/CgFmJ4CGMFwB2cSIP7WzkosO78U34tIASyTBwHk0WpYd3DS8sPysEYKYJk8CFBn8Mdzy0MaX6AAFvEtQHDq+3oN10kKCpAR3wLEn/kUwBe+BcDf51MAj/gWALThvW8pgCdyIMDYo9lr6k/lpgDjyIwgCl8qHZs2agm/QG5SAMnVqA683+9bALx/d4Z+7HP6SyNIuCjLrtNSB0/r7wYaiu2VSqU93agq0tXV1YHG/JrDDqYAp2ramm5kFUGSDTntYB8CjFar1SWK9TkRQAJ7gSvTj3ByQz5w2MGDGnXVndFzJMFihfqOO6zp/myj3AQk2OSwg5UfeYJGv+RDAFUJsMx3Dmu6NftINwBHmyscNeJv5LpctS4s34v4J68SYJlnHNWxt1wuzzYz2o0b8wTipM3BRzyiW5c8uqbm/umcEzt/BJ+/c+vVJuf5QYNH7piUELEs+wgrIKeCiJfl4MtkoCH9Ov/5k8F/4ULZhum6VCeLkg5cUd9tWO41w332Fl6fwmslbb+RBERKdPABBQF+8V0rsYSKBBBgj+86iUXkQK/Zx4HO3AWZpsQSjNQZ/IONDgLJDEMGGoP+Lgb9kJx6yX8+xJjjuy5CCLFLa3w5eFUURfN8F0PGaJEbW2RMMDYLrGWRzzgk2TnpwGeb/LqHtaSkKTgWCTAGOyYdkH5u/GnuMr+MDQ83OPV532gyosos9P9XDU5JPy2ZfAgFBnldswmQLNO3JB3o99UJM5M3mEy2MSHZvcaSESWC5CuMj5pMNtAsGY4P1hpLRpTAXvm5BAHWG0tGAfIHBSg4FKDgUICCQwEKDgUoOBSg4FCAgkMBCk6uBEAxQxZuxWY0j29yIwAjl0EBCh4UoOBhVICky8GM/IXRy8EP5aBBDL0w9wS39vb2s7HB3TloFEMh5D7BkslbwoT4ptAvfTeOkTj4Wzs7O883OvgTaEWSXshwe5afiGOYDwx8H+JiWwNPCJmJ/AfeYKM1GJn59AAAAABJRU5ErkJggg=="
}

Place this file into the following directory:

C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools

Now close and relaunch Power BI Desktop. SQL Server Profiler now appears as an external tool in the External Tools ribbon as shown below:

Greg_Deckler_8-1670185922455.png

Clicking the SQL Profiler icon opens SQL Server Profiler and automatically creates a trace to the local tabular instance of Power BI Desktop. Stop the trace and then click on Properies using the icons highlighted in the image below:

Greg_Deckler_9-1670186076258.png

As before, click on the Events Selection tab at the top. On the Events Selection tab, check the box for Show all events and then under Query Processing find and check the box for DAX Evaluation Log as shown in the following image:

Greg_Deckler_6-1670185084021.png

Click the Run button.

Conclusion

The new EVALUATEANDLOG function is a useful addition that can help troubleshoot DAX calculations. With a bit of work, the SQL Server Profiler tool can be configured as an external tool for Power BI Desktop to make such troubleshooting quick, easy and largely seamless.  

 

Watch the video!

 

 

 

 

 



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*