Update Semantic Model column names with SemPy

SemPy offers many possibilities with Semantic Models in Microsoft Fabric, some of which are remarkably effective and simplify some tedious tasks. As Data Analytics Developers, we often debate the best naming conventions. However, business users expect us to set aside our fixation on naming conventions and create more readable outputs with a simple design.

When we create our models for Power Users, Self-Service BI users, or Power BI designers, they need simple, explainable names. It's not always possible, some times we are just lazy and forgetful and platform restrictions sometimes prevent us. Recently, I’ve been using SemPY and Semantic Link Labs in Microsoft Fabric. I enjoy working on my Mac because I can accomplish everything within the service without needing to open other desktop tools like PBI Desktop. Nevertheless, I still believe that PBI Desktop remains the primary development tool for Power BI development.

Assume you are working with the tables below:

• Dim_Product

• Dim_Customer

• Fact_Sales

In the tables above, you have columns such as

• Product_ID

• Product_Name

• Customer_ID

• Customer_Name

• Sale_ID

• Sales_Date_Time

You can quickly write this straightforward Python code to update all table and column names. I usually start by creating the Semantic Model, create relationships through drag-and-drop, and then use my Notebook to update the column names. It’s a script that I run only when I modify the model.

I drew inspiration from the code in Semantic Link Labs Notebooks; it’s an excellent resource for exploring the potential of SemPy and Semantic Link Labs. - [https://github.com/microsoft/semantic-link-labs/tree/main/notebooks](https://github.com/microsoft/semantic-link-labs/tree/main/notebooks)

An Outline of steps I follow:

Steps:

1. Install Necessary libraries.

I tend to use environment variables and include all necessary libraries like sempy_labs_

! pip install semantic-link-labs
import sempy_labs as labs
from sempy_labs.tom import connect_semantic_model

2. Set Workspace and Dataset names

These are just static variables.

	workspace = 'Workspace ID'
	dataset = 'Semantic Model/Dataset Name'

3. Connect to the Semantic Model and get all Tables

This gives a view of the current tables state.

	with connect_semantic_model(dataset=dataset, readonly=True, workspace=workspace) as tom:
	    for t in tom.model.Tables:
	        print(t.Name)Add example  and video here

If you want to extend it to get column names as well

	with connect_semantic_model(dataset=dataset, readonly=True, workspace=workspace) as tom:
	    for t in tom.model.Tables:
	        print(t.Name)
	        for c in t.Columns:
	            print(c.Name)
	        print('---------------------------------------------------')

4. Connecting to the Semantic Model and update Table Names and Column Names

Here, we substitute _ with a space. This follows the naming convention methodology I discussed in my previous blog post, Naming Conventions. However, modify the code to use any characters that suit your needs.

	# Rename objects in the semantic model

	with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:

	    for t in tom.model.Tables:

	        t.Name = t.Name.replace('_',' ')
	with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:

	    for c in tom.all_columns():

	        c.Name = c.Name.replace('_',' ')Check my next post to see how I am using it for measures creation

5. Connect to Semantic Model and update Column Names to Snake Case.

I prefer using Snake Case for all items shared with end users, and that’s precisely what’s occurring below.

	# Function to convert to Snake case
	def to_title_case(snake_str):
	    return snake_str.title()
	# Convert table names to title case
	with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
	    for t in tom.model.Tables:
	        t.Name = to_title_case(t.Name)
	# Convert column names to Snake case
	with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
	    for c in tom.all_columns():
	        c.Name = to_title_case(c.Name)

This is just one use case; check out my next post, where I discuss how I use Semantic Link Labs to create and update my measures and calculation groups.

Link to Notebook - https://bit.ly/update_semnatic_model_column_names_with_sempy

In the meantime, continue smiling :)

Prathy