Help automating table name change in Direct Lake mode

[OldDogNewTricks000]

Thank you for making this library available! My company is in the midst of migrating to MS Fabric from Alteryx, Tableau, and Business Objects. It is a bit like learning to build a car as we build the car and drive down the freeway going 70mph. I spent several hours this week trying to solve what SHOULD be a simple problem.

We have several delta tables that were created with the incorrect naming convention, however we already have semantic models and reports built on these tables. We are trying to leverage direct lake models as much as possible.

I have been using semantic-link-labs for a great many things, so I thought maybe I could script this process there.

First I tried sempy_labs.directlake.add_table_to_direct_lake_semantic_model(), that did not work because apparently schema enabled lakehouses are not supported in that function yet.

I then tried a combination of sempy_labs.tom.add_table(). This did indeed add a table, but it was blank (no columns) and showed up as a direct query mode, not direct lake.

So then I went into the model via the UI and manually added the new table I had created with the new name. That brought over all of the columns.

That then enabled me to use sempy_labs.tom.update_columns() to update all of the columns with the metadata from the columns of the old table.

I was then also able to move a measure from the old fact table to the new fact table by using sempy_labs.tom.remove_object() and sempy_labs.tom.add_measure(). I even used the old lineage tag from the measure I removed in the new measure hoping this would keep the vizzes from breaking. However, it did not. Vizzes in the power bi report broke because the relationships did not exist to the new table as they did on the old table.

Then I manually fixed the relationships so the measures would work. The moved measure did work as expected, but the vizzes that were using the measure still showed that the "measure was removed from the model".

I then tried to use sempy_labs.report.get_report_json(), in the hopes that I could modify the report somehow, but that didn't work because the Fabric service only seems to store the .pbix and not the .pbib that get_report_json() requires.

At this point, I'm at a loss; I feel like this is a pretty common scenario and there HAS to be a better option than: add table, re-do all relationships, add back column settings, move all measures to this new table, manually open each measure and replace references to the old table with references to the new table.

Thank you for your time reading this very lengthy comment. Reiterating again, these models are all in Direct Lake mode and the goal is to update the Power bi report file and/or semantic model to utilize the renamed table in the place of the existing table. There could be some cases where the table changed slightly, but for the most part it will have the same column names and metadata as well as the same measures and relationships.

Thank you very much for your time and I hope you have an easier answer to what must be a common issue for anybody building in Fabric right now!

[m-kovalsky]

If you want to repoint a table in your direct lake semantic model to a different delta table in the lakehouse, use this function:

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

[OldDogNewTricks000]

Thank you very much for your quickly reply! I tried your suggestion, but it did not appear to work. I duplicated a table in our lakehouse, original table name FactHORelationship; new table name FactHO_Relationship (underscore added). I then executed the code you suggested, which seems to have run correctly:

I then went back into the semantic model (in the service) and clicked "Edit tables", it still shows the original table in the model and not the new table:

Lastly, if I check the tmsl (via fabric.get_tmsl) after the modification I see this:

It appears the SourceLineageTag is still pointing to the old table and the name still reflects the original name.

However, it does appear that the partition source was changed:

But like I said, based on the "Edit tables" above, it does not appear that the source was changed; the names did not change of the partition, SourceLineageTag, or the table itself. How can I verify this is pointed to the new table location/name? Should the names be changed elsewhere in the model as part of the call to update_direct_lake_partition_entity? If not, can I change the partition name?

Do we know when sempy_labs.directlake.add_table_to_direct_lake_semantic_model() will be modified to work with schema enabled lakehouses?

[m-kovalsky]

Interesting. Thanks for the detailed feedback. I made a PR (#487) to ensure that the source lineage tag is updated accordingly when the update function is executed. Hopefully that fixes it. This is one of those nuances which make working with TOM and the semantic model web experience a bit funky. The fix will be in the next release.

[OldDogNewTricks000]

Thank you. I just tested this and it appears that did the job!

[v-dhashah]

Related problem - Tables disappearing from Semantic Model when trying to refresh data, if Model Tables were pointed to a specific Lakehouse Table using sempy_labs.directlake.update_direct_lake_partition_entity.

Encountered this in Power BI Desktop. Opened the DL Model in Edit mode in PBI Desktop, hit "Refresh Data" for a table and then after refresh all the tables that had been pointed to a LH table with this function disappeared.
This function was used as the LH tables had a slightly different name then in the Model.

[m-kovalsky]

Please wait for the fix discussed in this discussion to go live in 0.9.4. Then run this function again.

[v-dhashah]

Tried with 0.9.4 - new error
The tables are replaced with new ones - named according to source Lakehouse tables. The relationships disappear, likely because the table names all changed.

[m-kovalsky]

I am not able to reproduce this error. I updated table/partition in a direct lake model to point to a new lakehouse table using the function, opened Power BI Desktop and connected to the direct lake model in Edit mode and the relationship with that table still exists. Could you give me the exact scenario/steps taken?

[v-dhashah]

This happened when, after connecting to DL model in Edit mode, I run refresh data on either that table or the whole model.

Full Scenario:

• Migrate Import Mode Semantic Model to Direct Lake (SM_I -> SM_DL)
  -- Migration is successful, no steps failed
  -- Refresh failed, as source table in Lakehouse (LH) that model table T1 in SM_DL was supposed to point to is not available.
  -- Error message is: "Error: We cannot access the source Delta table 'T1' referenced by table 'T1'. Either the source Delta table does not exist, or you don't have access permissions. Consider removing the table from the model. Please refer to https://go.microsoft.com/fwlink/?linkid=2248855 for more information."
• Point T1 in SM_DL to correct table T1_LH in LH using update_direct_lake_partition_entity
  -- Refresh succeeds when manually refreshing the dataset
• Open PowerBI desktop and connect to SM_DL in Edit mode
• Go to Model View, where T1 is still named T1. Click on the three dots on top-right for the table and select Refresh Data.
• Tables are refreshed. T1 is replaced by T1_LH (same for any others pointed this way) - relationships disappear.

If it helps, before 0.9.4, the behavior was that all tables updated with this function disappeared from the model entirely.

[OldDogNewTricks000]

Did you update the table name as well?

Did you also update the measures once the table name changed?

Maybe this will help:

Get current measure data:

lst_meas = []
with connect_semantic_model(dataset = dataset_name, readonly = True, workspace = workspace_name) as tom:
    for t in tom.model.Tables:
        if t.Name == current_table_name:
            # using this method instead of tom.all_measures() because this gets more details
            for m in t.Measures:
                meas_to_create={}
                meas_to_create['measure_name'] = m.Name
                meas_to_create['description'] = m.Description
                meas_to_create['expression'] = m.Expression
                meas_to_create['data_category'] = m.DataCategory
                meas_to_create['lineage_tag'] = m.LineageTag
                meas_to_create['source_lineage_tag'] = m.SourceLineageTag
                meas_to_create['hidden'] = m.IsHidden
                meas_to_create['format_string'] = m.FormatString
                meas_to_create['display_folder'] = m.DisplayFolder

                lst_meas.append(meas_to_create)

df_meas = pd.DataFrame(lst_meas)
display(df_meas)

Update partition (via function mentioned above)

Update partition and table name
Function above only changes the source and will be updated when the new release of semantic-link-labs exists
This script updates the name of the table in the model to match the underlying source

# it appears that when the table is renamed the partition is renamed as well
with connect_semantic_model(dataset = dataset_name, readonly = False, workspace = workspace_name) as tom:
    tom.model.Tables[current_table_name].Name = new_table_name
    print(f'Updated table name from {current_table_name} to {new_table_name}') 

Update measures
Update all measure calculations referring to the old table name

# limit the dataframe to rows that need to be updated
df_filtered = df_meas[df_meas['expression'].str.contains(current_table_name, na=False, case=False)]
print(f'{len(df_filtered)} measures need to be updated...')

# loop throug the filtered df, update measure
for tup in df_filtered.itertuples():
    print(f'\tUpdating {tup.measure_name}...')
    with connect_semantic_model(dataset = dataset_name, readonly = False, workspace = workspace_name) as tom:
        tom.update_measure(
            measure_name = tup.measure_name
            ,expression = tup.expression.replace(f'{current_table_name}',f'{new_table_name}')
        )

[v-dhashah]

The update_direct_lake_partition_entity function does update both the Partition and the Source Lineage Tag as well with 0.9.4 version.

Our current setup assumes that table name in Model and Lakehouse will be different - changing this could involve considerable effort which is why we want to use this function to point Model table to LH table with different name.