November 2, 2024|tutorial|

how to utilise semantic-link-labs to gather detailed information about all your semantic models.

introduction.

At Fabcon ’24 in Stockholm, I attended a session by Sandeep and Michael presenting the stuff that had freshly been released to semantic-link-labs – it was impressive to say the least.
So impressive that when the session was over, I immediately picked out my phone and wrote down onto my blog idea list: “Check out semantic-link-labs and see what you can do with it”.
And here we are! Below is a script that uses semantic-link-labs saving data about all accessible semantic models into a delta table in a Lakehouse. The script exposes information like the date of creation, whether it is a default semantic model, whether RLS is applied, how many measures there are, how many reports are built on top of the semantic model and many more things. At the very end of this article, you can find an example screenshot displaying all columns that result from the script. Feel free to add stuff or align it!
For some inspiration, I also built a Power BI report on top of it. Let me know in the comments below, if you wanna have a copy of the report and I’ll send it to you via email. Also, there’s gonna be an equivalent post where we are going to collect all sorts of information about all our Power BI reports in our Fabric tenant. Of course, we will be utilising semantic-link-labs for this, too. You can see the report part in the visual below as well.

prerequisites.

1. A Fabric capacity and workspace
2. A Fabric Lakehouse

plan of action.

1. What’s the goal?
2. The script

1. What’s the goal?

The goal is to utilise semantic-link-labs in order to get information about all our semantic models in Fabric (that is all semantic models that we have access to). The idea is to store this information in a delta table and to create a Power BI report on top of it, effectively enhancing our governance capabilities: With this, we can easily check out whether our analytics engineers and Power BI developers stick to guidelines provided, e.g. are there models with calculated columns or tables? ;-)

2. The script

Below the snippets of the script. Just create a notebook and copy & paste the snippets. Make sure to attach the notebook to the Lakehouse where the delta table shall reside before executing it. Feel free to set up a schedule for the notebook to run.
We start off by installing semantic-link-labs.

Copy to Clipboard

Next, we import all packages needed.

Copy to Clipboard

Now, we get all our semantic models and do some basic cleansing.

Copy to Clipboard

# find all semantic models
df_sem_model = spark.createDataFrame(admin.list_datasets())

df_sem_model_cleaned = df_sem_model.toDF(*(c.replace(' ', '_').lower() for c in df_sem_model.columns)) \
                .withColumn("upstream_datasets", col("upstream_datasets").cast(ArrayType(StringType()))) \
                .withColumn("users", col("users").cast(ArrayType(StringType())))

df_workspace = spark.createDataFrame(admin.list_workspaces()) \
    .withColumn("workspace_id", col("Id")) \
    .withColumn("workspace_name", col("Name")) \
    .select("workspace_id", "workspace_name")

df_sem_model_cleaned = df_sem_model_cleaned.join(df_workspace, 'workspace_id', "left") \
    .select("dataset_id", 
        "dataset_name", 
        "workspace_id", 
        "workspace_name", 
        "add_rows_api_enabled", 
        "is_refreshable", 
        "is_effective_identity_required", 
        "is_effective_identity_roles_required", 
        "target_storage_mode", 
        "created_date", 
        "content_provider_type", 
        "upstream_datasets", 
        "users", 
        "is_in_place_sharing_enabled", 
        "auto_sync_read_only_replicas", 
        "max_read_only_replicas"
    )                                       # excluded cols: create_report_embed_url, qna_embed_url, configured_by, web_url

display(df_sem_model_cleaned)

With this rather basic “semantic model” dataframe, we can loop over each row and retrieve even more details about our models, e.g. the number of calculated columns or tables, whether it is a default semantic model or whether RLS is applied.

Copy to Clipboard

dataframes = []

for row in df_sem_model_cleaned.collect():

workspace_name=row['workspace_name']
    dataset_name = row['dataset_name']

objects_pd = labs.list_semantic_model_objects(dataset=dataset_name, workspace=workspace_name)

if len(objects_pd) == 0:
        number_of_tables = 0
        number_of_columns = 0
        number_of_hierarchies = 0

else:
        objects = spark.createDataFrame(objects_pd)
        number_of_tables      = objects.filter("`Object Type` = 'Table' OR `Object Type` = 'Calculated Table'").count()
        number_of_columns     = objects.filter("`Object Type` = 'Column' OR `Object Type` = 'Calculated Column'").count()
        number_of_hierarchies = objects.filter("`Object Type` = 'Hierarchy'").count()
    number_of_reports     = len(labs.list_reports_using_semantic_model(dataset=dataset_name, workspace=workspace_name))

with connect_semantic_model(dataset=dataset_name, workspace=workspace_name, readonly=True) as tom_wrapper:
        number_of_calculated_columns = len(list(tom_wrapper.all_calculated_columns()))
        number_of_calculated_tables  = len(list(tom_wrapper.all_calculated_tables()))
        number_of_measures           = len(list(tom_wrapper.all_measures()))
        has_date_table               = tom_wrapper.has_date_table()
        has_hybrid_table             = tom_wrapper.has_hybrid_table()
        has_rls                      = False if not len(list(tom_wrapper.all_rls())) else True
        is_direct_lake               = tom_wrapper.is_direct_lake()
        is_default_semantic_model    = labs.is_default_semantic_model(dataset=dataset_name, workspace=workspace_name)

row = Row(
        workspace_name               = workspace_name, 
        dataset_name                 = dataset_name, 
        number_of_reports            = number_of_reports, 
        number_of_tables             = number_of_tables, 
        number_of_columns            = number_of_columns,
        number_of_hierarchies        = number_of_hierarchies, 
        number_of_calculated_columns = number_of_calculated_columns, 
        number_of_calculated_tables  = number_of_calculated_tables, 
        number_of_measures           = number_of_measures, 
        has_date_table               = has_date_table,
        has_hybrid_table             = has_hybrid_table,
        has_rls                      = has_rls,
        is_direct_lake               = is_direct_lake,
        is_default_semantic_model    = is_default_semantic_model
    )

schema = StructType([
        StructField("workspace_name", StringType(), True),
        StructField("dataset_name", StringType(), True),
        StructField("number_of_reports", IntegerType(), True),
        StructField("number_of_tables", IntegerType(), True),
        StructField("number_of_columns", IntegerType(), True),
        StructField("number_of_hierarchies", IntegerType(), True),
        StructField("number_of_calculated_columns", IntegerType(), True),
        StructField("number_of_calculated_tables", IntegerType(), True),
        StructField("number_of_measures", IntegerType(), True),
        StructField("has_date_table", BooleanType(), True),
        StructField("has_hybrid_table", BooleanType(), True),
        StructField("has_rls", BooleanType(), True),
        StructField("is_direct_lake", BooleanType(), True),
        StructField("is_default_semantic_model", BooleanType(), True)
    ])

# Create a DataFrame with one row
    df_enriching_info = spark.createDataFrame([row], schema = schema)

dataframes.append(df_enriching_info)

# Union all DataFrames in the list
df_enriching_all_sem_models = dataframes[0]
for temp_df in dataframes[1:]:
    df_enriching_all_sem_models = df_enriching_all_sem_models.union(temp_df)

# Show the final DataFrame
display(df_enriching_all_sem_models)

Lastly, we join the basic and the enriched dataframe.

Copy to Clipboard

And finally, we are ready to save our semantic model meta info as a delta table to our attached Lakehouse.

Copy to Clipboard

Here how the table looks like after loading it into Power BI:

end.

And this is it! Once again, let me know if you wanna have a copy of the report. Otherwise, I highly encourage you to check out semantic-link-labs for other things as well.