I had meant to follow up my recent post on how to find the columns touched by a DAX query by writing one on how to use this technique to find the size of these columns in memory, so you can find the total size of the columns that need to be paged into memory when a DAX query runs on a Direct Lake semantic model. Before I could do that, though, my colleague Michael Kovalsky messaged me to say that not only had he taken the query from that first post and incorporated it in Semantic Link Labs, he’d done the work to get column sizes too. All that’s left for me to do, then, is give you some simple examples of how to use it.
To use Semantic Link Labs you just need to create a new Fabric notebook and install the library:
This returns a dataframe with one row for each column touched by the query, plus various statistics about the size of each column in memory.
If you’re working with a Direct Lake semantic model, though, in order to get the correct sizes of each column in memory the query itself will need to have been run beforehand; you can ensure that this happens by setting the optional parameter put_in_memory to True:
Last of all, if you don’t want a dataframe but just want a single number representing the total memory needed by all columns touched by a query, you can use sempy_labs.get_dax_query_memory_size, for example like this:
A few years ago a new pair of Profiler events was added for Power BI Import mode datasets (and indeed AAS models): the Job Graph events. I blogged about them here but they never got used by anyone because it was extremely difficult to extract useful data from them – you had to run a Profiler trace, save the trace file, run a Python script to generate a .dgml file, then open that file in Visual Studio – which was a shame because they contain a lot of really interesting, useful information. The good news is that with the release of Semantic Link in Fabric and the ability to run Profiler traces from a Fabric notebook it’s now much easier to access Job Graph data and in this blog post I’ll show you how.
Quick recap: what are the Job Graph events and why are they useful? Let’s say you have a Power BI Import mode semantic model and you want to optimise refresh performance. When you refresh a semantic model, that refresh is made up of multiple jobs which themselves are made up of multiple jobs: refreshing a semantic model involves refreshing all the tables in that model, refreshing a table involves refreshing all the partitions in that table, refreshing a partition involves loading the data and building attribute hierarchies, and so on. Some of these jobs can happen in parallel but in some cases there are dependencies between jobs, so one job can only start when another has completed. The Job Graph events give you information on these refresh jobs and the dependencies between them so you can work out which jobs you need to optimise. In order to capture information from them you need to run a trace while the semantic model is being refreshed; the data from some of these Job Graph events can be reconstituted into a Directed Graph Markup Language (DGML) file, which is an XML-based format, and once you’ve got that you can either visualise the DGML file using a suitable viewer or extract the data from it and analyse it further.
[Before I carry on I have to acknowledge that I’m extremely new at Python and a lot of the code in this post is adapted from the code in my colleague Phil Seamark’s excellent recent post on visualising Power BI refresh information with Semantic Link. Any feedback on ways to optimise the code is gratefully received.]
Here’s some Python code that you can use in a Fabric notebook to run a refresh and generate a DGML file. Each code snippet can be used in a separate code cell or combined into a single cell.
First of all you need to install Semantic Link:
%pip install semantic-link
Next you need to define the events you want in your trace, which in this case are just the Job Graph events:
import sempy.fabric as fabric import pandas as pd import time import warnings
# define events to trace and their corresponding columns
event_schema = { "JobGraph": base_cols }
warnings.filterwarnings("ignore")
You then need to start a trace using this definition, refresh the semantic model, stop the trace and filter the events captured so you only have those with the EventSubclass GraphFinished, remove the event which contains the metadata (which has a value of 0 in the IntegerData column) and then finally sort the rows in ascending order by the values in the IntegerData column:
WorkspaceName = "Insert workspace name here" SemanticModelName = "Insert semantic model name here"
with fabric.create_trace_connection(SemanticModelName,WorkspaceName) as trace_connection: # create trace on server with specified events with trace_connection.create_trace(event_schema, "Simple Refresh Trace") as trace:
trace.start()
# run the refresh request_status_id = fabric.refresh_dataset(SemanticModelName, WorkspaceName, refresh_type="full") print("Progress:", end="")
while True: status = fabric.get_refresh_execution_details(SemanticModelName, request_status_id, WorkspaceName).status if status == "Completed": break
print("â–‘", end="") time.sleep(2)
print(": refresh complete") # allow ending events to collect time.sleep(5)
# stop Trace and collect logs final_trace_logs = trace.stop()
# only return GraphFinished events final_trace_logs = final_trace_logs[final_trace_logs['Event Subclass'].isin(["GraphFinished"])] # ignore metadata row final_trace_logs = final_trace_logs[final_trace_logs['Integer Data'].ne(0)] # sort in ascending order by Integer Data column final_trace_logs = final_trace_logs.sort_values(by=['Integer Data'], ascending=True)
Finally, you need to take all the text from the EventText column of the remaining events and concatenate it to get the contents of the DGML file and then save that file to the Files section of the Lakehouse attached to your notebook:
# concatenate all text in TextData column out = ''.join(final_trace_logs['Text Data']) # change background colour of critical path nodes so it's easier to see in VS Code out = out.replace("#263238", "#eba0a7")
#dispose of trace connection trace_connection.disconnect_and_dispose()
I found a nice Visual Studio Code extension called DGMLViewer which makes viewing DGML files easy. Rather than manually downloading the file, OneLake Explorer makes it easy to sync files in OneLake with your PC in a very similar way to OneDrive, which makes working with these DGML files in VS Code very straightforward because you can simply open the local copy when it syncs.
Here’s what one of thse DGML files, generated from the refresh of a very basic semantic model, looks like when viewed in DGML Viewer:
If you have Visual Studio you can also use it to view DGML files (you need to install the DGML Editor first); I found a VS extension called DgmlPowerTools 2022 which adds some advanced features. Here’s what a DGML file for a refresh looks like when visualised in Visual Studio 2022:
OK, so this looks cool but it also looks very complicated. What does it all mean? How can you interpret all this information and use it to optimise a refresh? That’s something for a future blog post!
[In my next post I look at how you can interpret this data and understand the concepts of blocking and waiting, and in the post after that show how you can extract the data in this DGML file to a table using a Dataflow]
