SEMPY - Remame Column Semantic Model

[kwenzelmms]

Hi,

I'm able to generate a semantic model using sempy lab's generate_direct_lake_semantic_model , but once I do that, I would love to rename the colum within the model to "nice" names, as our database names are not so nice.  Is there way either through the TOM or another way to do so?  update_column looks like a great way to update a column's attributes, but not the name itself.  Am I missing something somewhere?

I know this can be done, as the UI support does support the concept!

Thanks for your help.

[tharunkumarRTK · Accepted solution]

@kwenzelmms 

Seems like smepy.update_column cannot change the column name. Not sure if there any other function which can perform this task, But here are a couple of suggestions 

1. with TMSL, you can run this script either from tabular editor or SSMS to update mutiple column names programitically

{
  "operations": [
    {
      "operationType": "update",
      "object": {
        "database": "YourDatasetName",
        "table": "YourTableName",
        "column": "OldColumnName"
      },
      "property": "name",
      "value": "NewColumnName"
    }
  ]
}

2. first get the definition (bim file) of your semantic model - sempy_labs.get_semantic_model_from_bim
and then update it with this function sempy_labs.update_semantic_model_from_bim

https://semantic-link-labs.readthedocs.io/en/stable/sempy_labs.html

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[kwenzelmms]

Hi,  Your idea got me off to the right start and on to the solution.  Thanks!  Here is the prototype that works, the dictionary lookup code is sketcy, but it's Sunday here, and I'm just having fun playing on a snowy afternoon.  🙂   I took this out of the fabric notebook I was using to piece it together:

from sempy_labs.directlake import generate_direct_lake_semantic_model
from sempy_labs import get_semantic_model_bim, update_semantic_model_from_bim


#Intention will be to call one gold lakehouse is created.
semantic_model_name = lakehouse_name+"KAW"

#from: https://semantic-link-labs.readthedocs.io/en/stable/sempy_labs.directlake.html#sempy_labs.directlake.generate_direct_lake_semantic_model
generate_direct_lake_semantic_model(
    dataset=semantic_model_name,
    workspace="DataScienceProd",
    lakehouse_tables=lakehouse_tables,
    lakehouse=lakehouse_name,
    overwrite=True,
    refresh=True
)

model_bim = get_semantic_model_bim(dataset=semantic_model_name)

def rename_column_in_table(data, table_name, old_column_name, new_column_name):
    # Get the list of tables
    tables = data.get('model', {}).get('tables', [])
    for table in tables:
        # Check if the current table matches the table_name
        if table['name'] == table_name:
            # Iterate over columns and rename the column if it matches
            for column in table.get('columns', []):
                if column['name'] == old_column_name:
                    column['name'] = new_column_name
                    column['sourceColumn'] = new_column_name  # Optional, update the source column
    return data

updated_bim= rename_column_in_table(
    model_bim,
    table_name='reconcileitem',
    old_column_name='study_name',
    new_column_name='better study name'
)

from pprint import pprint
pprint(updated_bim)

update_semantic_model_from_bim(semantic_model_name, updated_bim)

For context this is for some pharamceutical study data that is for highly standardized datasets that got to powerbi visualizations.  The dataset got to the FDA.  They have short and unfriendly names.  The idea is to use the standard's data dictionary to reamame them with the semantic model so we can visualize, yet on the lakehouse side keep the original column name so biometrics and datascience can continue to work as usual. 

Next step to make production worthy.  🙂  

Connect with me on linkedin if you wish - https://www.linkedin.com/in/kriswenzel/

[kwenzelmms]

Hi,

Thanks for the tip.  I'll try that direction and let you know how it goes.

Kris.