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.

tackytechtomNovember 2, 2024tutorialdata engineering, fabric, power bi, python, sempy 16

16 Comments

Chris November 4, 2024 at 3:33 am - Reply

Is there a way to get the template of the pbix file?
- tackytechtom November 6, 2024 at 6:42 am - Reply
  
  Mail’s out :)
Wanni November 19, 2024 at 5:16 pm - Reply

Is there a way to get the template of the pbix file?
- tackytechtom November 19, 2024 at 6:48 pm - Reply
  
  Hi Wanni! Have a look into your inbox :)
Linford November 20, 2024 at 2:37 pm - Reply

Hi is it possible to get the pbi template? Thanks!
- tackytechtom November 21, 2024 at 8:44 am - Reply
  
  Hey! Mail has been sent :)
Alexandru Badiu November 22, 2024 at 10:30 am - Reply

Hello! I am interested as well by the PBIX or PBIT ! Thank you in advance
- tackytechtom November 27, 2024 at 8:53 pm - Reply
  
  Hi Alex! Pls check your inbox :)
URJA GANDHI February 19, 2025 at 10:13 am - Reply

Hello,

Please share the Report file.

Thanks in advance.
- tackytechtom February 21, 2025 at 9:38 pm - Reply
  
  Hi Urja, Pls check your inbox :)
shilpa manjunath murthy March 19, 2025 at 3:02 pm - Reply

Hi , Can I get the template.. thanks
- tackytechtom March 20, 2025 at 12:19 pm - Reply
  
  Mail’s out! Hope this helps :)
chibi zakaria April 3, 2025 at 3:03 pm - Reply

Can i get the template
- tackytechtom April 6, 2025 at 2:17 pm - Reply
  
  Hiya!
  Just sent you the email :)
Bjorn E April 9, 2025 at 9:14 am - Reply

Hello, great article. Could I get the template please? :)
- tackytechtom April 14, 2025 at 4:10 pm - Reply
  
  Hi Björn, pls check your inbox :)

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

introduction.

prerequisites.

plan of action.

1. What’s the goal?

2. The script

end.

16 Comments

Leave A Comment Cancel reply