Install the latest .whl package¶

Check here to see the latest version.

In [ ]:

%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.

In [ ]:

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)

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¶

In [ ]:

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

In [ ]:

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¶

In [ ]:

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])")

In [ ]:

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¶

In [ ]:

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')

In [ ]:

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¶

In [ ]:

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'])

In [ ]:

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¶

In [ ]:

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¶

In [ ]:

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¶

In [ ]:

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¶

In [ ]:

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¶

In [ ]:

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)

In [ ]:

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\""
    )

In [ ]:

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)

In [ ]:

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')

In [ ]:

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¶

In [ ]:

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

In [ ]:

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]))")

In [ ]:

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]))")

Add translations to a semantic model¶

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    tom.add_translation(language='it-IT')

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    tom.set_translation(object = tom.model.Tables['Product'], language='it-IT', property='Name', value='Produtto')

Add a Field Parameter to a semantic model¶

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    tom.add_field_parameter(table_name='Parameter', objects="'Product'[Color], [Sales Amount], 'Geography'[Country]")

Remove an object(s) from a semantic model¶

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    for t in tom.model.Tables:
        if t.Name == 'Product':
            tom.remove_object(object=t.Columns['Size'])
            tom.remove_object(object=t.Hierarchies['Product Hierarchy'])

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    tom.remove_object(object=tom.model.Tables['Product'].Columns['Size'])
    tom.remove_object(object=tom.model.Tables['Product'].Hierarchies['Product Hierarchy'])

Custom functions to loop through non-top-level objects in a semantic model¶

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=True, workspace=workspace) as tom:
    for c in tom.all_columns():
        print(c.Name)

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=True, workspace=workspace) as tom:
    for m in tom.all_measures():
        print(m.Name)

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=True, workspace=workspace) as tom:
    for p in tom.all_partitions():
        print(p.Name)

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=True, workspace=workspace) as tom:
    for h in tom.all_hierarchies():
        print(h.Name)

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=True, workspace=workspace) as tom:
    for ci in tom.all_calculation_items():
        print(ci.Name)

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=True, workspace=workspace) as tom:
    for l in tom.all_levels():
        print(l.Name)

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    for rls in tom.all_rls():
        print(rls.Name)

See Vertipaq Analyzer stats¶

In [ ]:

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

    for t in tom.model.Tables:
        rc = tom.row_count(object = t)
        print(f"{t.Name}  : {str(rc)}")
        for c in t.Columns:
            col_size = tom.total_size(object=c)
            print(labs.format_dax_object_name(t.Name, c.Name) + ' : ' + str(col_size))

'UsedIn' functions¶

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=True, workspace=workspace) as tom:
    for c in tom.all_columns():
        full_name = labs.format_dax_object_name(c.Parent.Name, c.Name)
        for h in tom.used_in_hierarchies(column = c):
            print(f"{full_name} : {h.Name}")

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=True, workspace=workspace) as tom:
    for c in tom.all_columns():
        full_name = labs.format_dax_object_name(c.Parent.Name, c.Name)
        for r in tom.used_in_relationships(object = c):
            rel_name = labs.create_relationship_name(r.FromTable.Name, r.FromColumn.Name, r.ToTable.Name, r.ToColumn.Name)
            print(f"{full_name} : {rel_name}")

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=True, workspace=workspace) as tom:
    for t in tom.model.Tables:
        for r in tom.used_in_relationships(object = t):
            rel_name = labs.create_relationship_name(r.FromTable.Name, r.FromColumn.Name, r.ToTable.Name, r.ToColumn.Name)
            print(f"{t.Name} : {rel_name}")

In [ ]:

with connect_semantic_model(dataset=dataset, readonly=True, workspace=workspace) as tom:
    dep = labs.get_model_calc_dependencies(dataset = dataset, workspace=workspace)
    for o in tom.used_in_rls(object = tom.model.Tables['Product'].Columns['Color'], dependencies=dep):
        print(o.Name)