Connect to Tabular Object Model 03-24-2025 15:27 PM - last edited 03-27-2025 13:15 PM

[mikova]

Download this notebook from: semantic-link-labs/notebooks/Tabular Object Model.ipynb at main · microsoft/semantic-link-labs · Git...

Install the latest .whl package

Check here to see the latest version.

%pip install semantic-link-labs

Connect to the Tabular Object Model (TOM)

Setting the 'readonly' property to False enables read/write mode. This allows changes to be made to the semantic model.

import sempy_labs as labs
from sempy_labs.tom import connect_semantic_model

dataset = '' # Enter dataset name
workspace = None # Enter workspace name

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

Connect to the Tabular Object Model using a Service Principal (also supports connecting to Azure Analysis Services)

key_vault_uri = '' # Enter your key vault URI
key_vault_tenant_id = '' # Enter the key vault key to the secret storing your Tenant ID
key_vault_client_id = '' # Enter the key vault key to the secret storing your Client ID (Application ID)
key_vault_client_secret = '' # Enter the key vault key to the secret storing your Client Secret

dataset = '' # Enter dataset name
workspace = None # Enter workspace name (for Azure Analysis Serivces instance use this format: "asazure://.asazure.windows.net/")

with labs.service_principal_authentication(
    key_vault_uri=key_vault_uri, 
    key_vault_tenant_id=key_vault_tenant_id,
    key_vault_client_id=key_vault_client_id,
    key_vault_client_secret=key_vault_client_secret):

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

Make changes to a semantic model using custom functions

Note that the custom functions have additional optional parameters (which may not be used in the examples below) for adding properties to model objects. Check the documentation to see all available parameters for each function.

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('_',' ')

Add measure(s) to the semantic model

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    tom.add_measure(table_name='Internet Sales', measure_name='Sales Amount', expression="SUM('Internet Sales'[SalesAmount])")
    tom.add_measure(table_name='Internet Sales', measure_name='Order Quantity', expression="SUM('Internet Sales'[OrderQty])")    

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    for t in tom.model.Tables:
        if t.Name == 'Internet Sales':
            tom.add_measure(table_name=t.Name, measure_name='Sales Amount', expression="SUM('Internet Sales'[SalesAmount])")
            tom.add_measure(table_name=t.Name, measure_name='Order Quantity', expression="SUM('Internet Sales'[OrderQty])")

Add column(s) to the semantic model

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    tom.add_data_column(table_name='Product', column_name='Size Range', source_column='SizeRange', data_type='Int64')
    tom.add_data_column(table_name= 'Segment', column_name='Summary Segment', source_column='SummarySegment', data_type='String')

    tom.add_calculated_column(table_name='Internet Sales', column_name='GrossMargin', expression="'Internet Sales'[SalesAmount] - 'Internet Sales'[ProductCost]", data_type='Decimal')

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    for t in tom.model.Tables:
        if t.Name == 'Product':
            tom.add_data_column(table_name=t.Name, column_name='Size Range', source_column='SizeRange', data_type='Int64')
        elif t.Name == 'Segment':
            tom.add_data_column(table_name = t.Name, column_name='Summary Segment', source_column='SummarySegment', data_type='String')
        elif t.Name == 'Internet Sales':
            tom.add_calculated_column(table_name=t.Name, column_name='GrossMargin', expression="'Internet Sales'[SalesAmount] - 'Internet Sales'[ProductCost]", data_type='Decimal')

Add hierarchies to the semantic model

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    tom.add_hierarchy(table_name='Geography', hierarchy_name='Geo Hierarchy', levels=['Continent', 'Country', 'State', 'City'])

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    for t in tom.model.Tables:
        if t.Name == 'Geography':
            tom.add_hierarchy(table_name=t.Name, hierarchy_name='Geo Hierarchy', levels=['Continent', 'Country', 'State', 'City'])

Add relationship(s) to the semantic model

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    tom.add_relationship(
        from_table='Internet Sales', from_column='ProductKey',
        to_table='Product', to_column ='ProductKey', 
        from_cardinality='Many', to_cardinality='One')

Add a table with an M partition to a semantic model

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    table_name='Sales'
    tom.add_table(name=table_name)
    tom.add_m_partition(table_name=table_name, partition_name=table_name, expression='let....')

Add a table with an entity partition to a Direct Lake semantic model

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    table_name = 'Sales'
    tom.add_table(name=table_name)
    tom.add_entity_partition(table_name=table_name, entity_name=table_name)

Add a calculated table (and columns) to a semantic model

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    table_name = 'Sales'
    tom.add_calculated_table(name=table_name, expression="DISTINCT('Product'[Color])")
    tom.add_calculated_table_column(table_name=table_name, column_name='Color', source_column="'Product[Color]", data_type='String')

Add role(s) to the semantic model

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    tom.add_role(role_name='Reader')

Set row level security (RLS) to the semantic model

This adds row level security (or updates it if it already exists)

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    tom.set_rls(
        role_name='Reader', 
        table_name='Product',
        filter_expression="'Dim Product'[Color] = \"Blue\""
    )

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    for r in tom.model.Roles:
        if r.Name == 'Reader':
            tom.set_rls(role_name=r.Name, table_name='Product', filter_expression="'Dim Product'[Color] = \"Blue\"")

Set object level security (OLS) to the semantic model

This adds row level security (or updates it if it already exists)

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    tom.set_ols(role_name='Reader', table_name='Product', column_name='Size', permission='None')

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    for r in tom.model.Roles:
        if r.Name == 'Reader':
            for t in tom.model.Tables:
                if t.Name == 'Product':
                    tom.set_ols(role_name=r.Name, table_name=t.Name, column_name='Size', permission='None')

Add calculation groups and calculation items to the semantic model

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    tom.add_calculation_group(name='MyCalcGroup')

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    tom.add_calculation_item(table_name='MyCalcGroup', calculation_item_name='YTD', expression="CALCULATE(SELECTEDMEASURE(), DATESYTD('Calendar'[CalendarDate]))")
    tom.add_calculation_item(table_name='MyCalcGroup', calculation_item_name='MTD', expression="CALCULATE(SELECTEDMEASURE(), DATESMTD('Calendar'[CalendarDate]))")

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    for t in tom.model.Tables:
        if t.Name == 'MyCalcGroup':
            tom.add_calculation_item(table_name=t.Name, calculation_item_name='YTD', expression="CALCULATE(SELECTEDMEASURE(), DATESYTD('Calendar'[CalendarDate]))")
            tom.add_calculation_item(table_name=t.Name, calculation_item_name='MTD', expression="CALCULATE(SELECTEDMEASURE(), DATESMTD('Calendar'[CalendarDate]))")

To see the rest of this notebook, please go to: semantic-link-labs/notebooks/Tabular Object Model.ipynb at main · microsoft/semantic-link-labs · Git...

https%3A%2F%2Fgithub.com%2Fmicrosoft%2Fsemantic-link-labs%2Fblob%2Fmain%2Fnotebooks%2FTabular%2520Object%2520Model.ipynb