https://community.fabric.microsoft.com/t5/Notebook-Gallery/DAX-Performance-Testing/m-p/4784750#M23 <H2><STRONG><A href="https://github.com/microsoft/fabric-toolbox/blob/main/tools/DAXPerformanceTesting/DAXPerformanceTesting.ipynb" target="_self">Link to the notebook</A>&nbsp;</STRONG></H2><H2><STRONG><BR />Overview of the Notebook</STRONG></H2><P>This notebook takes an Excel file with DAX queries, runs them under cold, warm, and hot cache states, and logs the results in a Lakehouse table.</P><P><STRONG>Key Features:</STRONG></P><OL><LI><STRONG>Model Definitions</STRONG><UL><LI>The definitions for models to be tested are stored in a dictionary, specifying storage mode, workspace, and cache states to test.</LI></UL></LI><LI><STRONG>Query Input</STRONG><UL><LI>Queries are pulled from an Excel file in your Lakehouse. Each query needs an ID and at least one column that matches the runQueryType in the model definition.</LI></UL></LI><LI><STRONG>Different Cache States</STRONG><UL><LI><STRONG>Cold Cache</STRONG>: Clears all cache. For Import/Direct Query, this involves pausing capacity, reassigning workspaces, and clearing VertiPaq cache. For DirectLake, it triggers a dataset clearValues refresh and then a full refresh. For Direct Query, the way cold-cache is set assumes that your data store is in a Fabric workspace, e.g., Lakehouse, Warehouse, etc.</LI><LI><STRONG>Warm Cache</STRONG>: Partial caching. For Direct Query, we set cold-cache first, then run a query to “prime” the model. For Import and Direct Lake, we ensure all columns are framed by running the target query, then we clear the Vertipaq cache.</LI><LI><STRONG>Hot Cache</STRONG>: Runs the query twice before measuring the third time to ensure columns are framed and all caches are set.</LI></UL></LI><LI><STRONG>Capacity Pause/Resume</STRONG><UL><LI>&nbsp; &nbsp;<STRONG>**Warning**</STRONG>: Pausing a capacity will interrupt any running workloads on that capacity. Resuming will take time and resources, and can affect other workspaces assigned to the same capacity.</LI><LI>For cold-cache queries on Import and DirectQuery models, the notebook pauses and resumes capacities to ensure truly cold-cache testing. Configure your workspace capacities in the config cell if using this feature.</LI></UL></LI><LI><STRONG>Logging to Lakehouse</STRONG><UL><LI>Query logs are captured using an Analysis Services trace and stored in your attached Lakehouse. This includes duration, CPU time, and success/failure status.</LI></UL></LI></OL><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P><SPAN class="msgUrl hidden">https%3A%2F%2Fgithub.com%2Fmicrosoft%2Ffabric-toolbox%2Fblob%2Fmain%2Ftools%2FDAXPerformanceTesting%2FDAXPerformanceTesting.ipynb</SPAN></P> Fri, 01 Aug 2025 17:43:34 GMT justinmartin 2025-08-01T17:43:34Z https://community.fabric.microsoft.com/t5/Notebook-Gallery/DAX-Performance-Testing/m-p/4784750#M23 <H2><STRONG><A href="https://github.com/microsoft/fabric-toolbox/blob/main/tools/DAXPerformanceTesting/DAXPerformanceTesting.ipynb" target="_self">Link to the notebook</A>&nbsp;</STRONG></H2><H2><STRONG><BR />Overview of the Notebook</STRONG></H2><P>This notebook takes an Excel file with DAX queries, runs them under cold, warm, and hot cache states, and logs the results in a Lakehouse table.</P><P><STRONG>Key Features:</STRONG></P><OL><LI><STRONG>Model Definitions</STRONG><UL><LI>The definitions for models to be tested are stored in a dictionary, specifying storage mode, workspace, and cache states to test.</LI></UL></LI><LI><STRONG>Query Input</STRONG><UL><LI>Queries are pulled from an Excel file in your Lakehouse. Each query needs an ID and at least one column that matches the runQueryType in the model definition.</LI></UL></LI><LI><STRONG>Different Cache States</STRONG><UL><LI><STRONG>Cold Cache</STRONG>: Clears all cache. For Import/Direct Query, this involves pausing capacity, reassigning workspaces, and clearing VertiPaq cache. For DirectLake, it triggers a dataset clearValues refresh and then a full refresh. For Direct Query, the way cold-cache is set assumes that your data store is in a Fabric workspace, e.g., Lakehouse, Warehouse, etc.</LI><LI><STRONG>Warm Cache</STRONG>: Partial caching. For Direct Query, we set cold-cache first, then run a query to “prime” the model. For Import and Direct Lake, we ensure all columns are framed by running the target query, then we clear the Vertipaq cache.</LI><LI><STRONG>Hot Cache</STRONG>: Runs the query twice before measuring the third time to ensure columns are framed and all caches are set.</LI></UL></LI><LI><STRONG>Capacity Pause/Resume</STRONG><UL><LI>&nbsp; &nbsp;<STRONG>**Warning**</STRONG>: Pausing a capacity will interrupt any running workloads on that capacity. Resuming will take time and resources, and can affect other workspaces assigned to the same capacity.</LI><LI>For cold-cache queries on Import and DirectQuery models, the notebook pauses and resumes capacities to ensure truly cold-cache testing. Configure your workspace capacities in the config cell if using this feature.</LI></UL></LI><LI><STRONG>Logging to Lakehouse</STRONG><UL><LI>Query logs are captured using an Analysis Services trace and stored in your attached Lakehouse. This includes duration, CPU time, and success/failure status.</LI></UL></LI></OL><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P><SPAN class="msgUrl hidden">https%3A%2F%2Fgithub.com%2Fmicrosoft%2Ffabric-toolbox%2Fblob%2Fmain%2Ftools%2FDAXPerformanceTesting%2FDAXPerformanceTesting.ipynb</SPAN></P> Fri, 01 Aug 2025 17:43:34 GMT https://community.fabric.microsoft.com/t5/Notebook-Gallery/DAX-Performance-Testing/m-p/4784750#M23 justinmartin 2025-08-01T17:43:34Z