Connect to Tabular Object Model
Download this notebook from: semantic-link-labs/notebooks/Tabular Object Model.ipynb at main · microsoft/semantic-link-labs · GitHub 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 · GitHub https%3A%2F%2Fgithub.com%2Fmicrosoft%2Fsemantic-link-labs%2Fblob%2Fmain%2Fnotebooks%2FTabular%2520Object%2520Model.ipynbHow to Use Semantic Link Labs in Microsoft Fabric Notebook (Python Demo Walkthrough)
Working with Power BI reports within Microsoft Fabric Notebook just got a whole lot easier! If you are ever tired of manually completing tasks within Microsoft Fabric or exploring the UI console seems like a lengthy process, especially when working with Power BI reports. The Semantic Link Labs is a Python library designed specifically for you to perform tasks within Microsoft Fabric Notebooks programmatically. This Python Library was developed by Michael Kovalsky and it builds on the foundational capabilities of Semantic Link, introducing enhanced features that enable deeper integration and more seamless workflows within the Fabric ecosystem while leveraging the power of Microsoft Fabric Notebook. Here’s what you need to know about this amazing Python Library. You don’t need to be a Python Expert before you get started. It's an Open-Source library. It allows you to programmatically access all the artefacts in your Workspace(Semantic Models, Lakehouse, Warehouse, Reports, etc). It showcases the power of Microsoft Fabric notebooks. You can perform over 304+ Functions with this Library. So, let’s explore it? Our Scenario: Anna just joined AMA Enterprise as their Fabric Analytics Engineer, and she has been tasked to do the following tasks leveraging the power of the Semantic Links Labs in Microsoft Fabric Notebook. Result from Adventureworks Dashboard report pages.Result from Adventureworks Dashboard report pages. Result from Adventureworks Dashboard report pages. Result from Adventureworks Dashboard report pages. Result from Adventureworks Dashboard report pages. Result from Adventureworks Dashboard report pages. Result from Adventureworks Dashboard report pages. Anna’s task: Understand the existing report metadata (show how many report pages, visuals in a dashboard, different filters used in the report, bookmarks, etc). Show the frequency of the semantic model object used within reports. Find broken visuals in a Power BI report. Get the sizes of all the semantic models in a workspace. Save a report in Power BI Service as a .pbip file. Credit: The snapshot of the Adventureworks Dashboard used for this demonstration was built by my sister, Rofiat Adebayo. You can download the Microsoft Fabric Notebook used for this Python Demo Walkthrough here. Let's get started: Open your Notebook in Microsoft Fabric, and let's start seeing some of the code examples in action. Before we get started, we must make sure we have the Semantic Link Labs library installed as well as the other necessary libraries. #Install the Semantic link Labs library in your Fabric notebook %pip install semantic-link-labs After that, we will also be installing the necessary libraries in your notebook. ### Once installed, run this code to import the library into your # notebook import sempy_labs as labs from sempy_labs.report import ReportWrapper import sempy.fabric as fabric 1. Understand the existing report metadata (show how many report pages, visuals in a dashboard, different filters used in the report, bookmarks, etc). Note: This function require the report to be in the PBIR format. #View Report Metadata report = 'Adventureworks Dashboard' # Name or ID of the report # Name or ID of the workspace in which the report resides workspace = 'Fabricday' rpt = ReportWrapper(report=report, workspace=workspace) # To view the report pages rpt.list_pages() #To list all visual in a dashboard #rpt.list_visuals() #To list all custom visual in a dashboard #rpt.list_custom_visuals() #To list all report filters in a dashboard #rpt.list_report_filters() #To list all page filters in a dashboard #rpt.list_page_filters() #To list all visual filters in a dashboard #rpt.list_visual_filters() #To list all visual objects in a dashboard #rpt.list_visual_objects() #To list all bookmarks in a dashboard #rpt.list_bookmarks() #To list all report level measures in a dashboard #rpt.list_report_level_measures() #To list the semantic model objects in a dashboard #rpt.list_semantic_model_objects() The result shows that we have 11 report pages with other details about the Adventureworks Dashboard. 2. Show the frequency of the semantic model object used within reports. Note: This function require the report to be in the PBIR format. # Show the frequency of the semantic model object used within reports. # Enter the name or ID of your semantic model dataset = 'Adventureworks Dashboard' # Enter the name or ID of the workspace in which the semantic model resides workspace = 'Fabricday' df = labs.list_semantic_model_object_report_usage(dataset=dataset, workspace=workspace, include_dependencies=True, extended=True) display(df) The result below shows that Order Quantity, Product Price, Total Revenue, Total Orders, OrderNumber are the top 5 most used semantic model objects used in the Adventureworks Dashboard. 3. Find broken visuals in a Power BI report. One of Anna’s tasks is to make sure all dashboard works fine and that there are no broken visuals in any of the report pages. Manually scrolling through a 12-page report dashboard seems like a Herculean task since Anna manages 10 other dashboards, too. This is why you can automatically detect broken visuals as a result of changes in measures or calculations with the code below. Note: This function require the report to be in the PBIR format. # Find broken visuals in a Power BI report. import sempy_labs as labs from sempy_labs.report import ReportWrapper # This is for a single report report = 'Adventureworks Dashboard' # The name or ID of the report # The name or ID of the workspace in which the report exists workspace = 'Fabricday' rpt = ReportWrapper(report=report, workspace=workspace) df = rpt.list_semantic_model_objects(extended=True) display(df) The result shows the Valid Semantic Model Object as True because there are no broken visuals in my report, as seen from the GIF I had shared earlier. But if they are broken visuals, Valid Semantic Model Object will be False. 4. Find the sizes of the semantic model. Knowing the size of a semantic model can help Anna to effectively manage her Team Microsoft Fabric capacity and licenses. # Get the size of a semantic model import sempy_labs as labs import sempy.fabric as fabric # Enter the name or ID of your semantic model dataset = 'Adventureworks Dashboard' # Enter the name or ID of the workspace in which the semantic model # resides workspace = 'Fabricday' # To check for a single semantic model model_size = labs.get_semantic_model_size(dataset=dataset, workspace=workspace) display(model_size) The result shows that the total size of the Adventureworks >>> semantic model is 7761847.496032715 4b. To check the size of all the semantic models in the Fabricday my workspace. # Check for all semantic models within a workspace # importing the necessary library import sempy_labs as labs import sempy.fabric as fabric # Enter the name or ID of the workspace in which the semantic model #resides workspace = 'Fabricday' model_sizes = {} dfD = fabric.list_datasets(workspace=workspace, mode="rest") for _, r in dfD.iterrows(): d_name = r["Dataset Name"] d_id = r["Dataset Id"] if not labs.is_default_semantic_model(dataset=d_id, workspace=workspace): model_size = labs.get_semantic_model_size(dataset=d_id, workspace=workspace) model_sizes[d_name] = model_size display(model_sizes) The result shows the sizes of the two semantic models I have in my Fabricday workspace. 5. Save a report in Power BI Service as a .pbip file. Saving a report as .pbip file encourages team collaboration, source control and CI/CD for a Power BI dashboard. It also allows you to easily do a batch update on the item definition on your report, visuals and semantic models. # Saving a report as .pbib file import sempy_labs.report as rep # Name or ID of the report report = 'Sales & Returns Sample v201912' # Name or ID of the workspace in which the report resides workspace = 'Fabricday' # If set to True, saves the report and underlying semantic model. If # set to False, saves just the report. thick_report = True # If set to True, saves a .pbip live-connected to the workspace in # the Power BI / Fabric service. If set to False, saves a .pbip with # a local model, independent from the Power BI / Fabric service. live_connect = True # Enter the name or ID of the lakehouse where you want to save the #.pbip file lakehouse = 'new_fabric_day_lakehouse' # Enter the name or ID of the workspace in which the lakehouse # exists workspace = 'Fabricday' lakehouse_workspace = None rep.save_report_as_pbip(report=report, workspace=workspace, thick_report=thick_report, live_connect=live_connect, lakehouse=lakehouse, lakehouse_workspace=lakehouse_workspace) The result shows the sampleSales & Returns Sample v201912 has been saved as .pbip file in the File section of my lakehouse. Conclusion Working with Power Report and Semantic models in Microsoft fabric just got easier with Semantic link labs. You can download the Microsoft Fabric Notebook used for this Python Demo Walkthrough here. References Semantic Link Labs Git Hub Repository by Michael Kovalsky: https://github.com/microsoft/semantic-link-labs You can connect with me via my socials below if you want to discuss further. LinkedIn: Musili Adebayo Twitter: Musili_Adebayo https%3A%2F%2Fgithub.com%2FMusili-Adebayo%2F-Musili-Adebayo-fabric_day_project_dashboard%2Fblob%2Fmain%2Ffabric_day_nb.ipynbReport Analysis
Download this notebook from: semantic-link-labs/notebooks/Report Analysis.ipynb at main · microsoft/semantic-link-labs · GitHub Install the latest .whl package Check here to see the latest version. %pip install semantic-link-labs Import the library and set the initial parameters import sempy_labs as labs import sempy_labs.report as rep from sempy_labs.report import ReportWrapper # For checking reports report_name = '' # Enter the report name report_workspace = None # Enter the workspace in which the report exists rpt = ReportWrapper(report=report_name, workspace=report_workspace) Report Best Practice Analyzer (BPA) rep.run_report_bpa(report=report_name, workspace=report_workspace) Report BPA using custom rules import pandas as pd rules = pd.DataFrame( [ ( "Error Prevention", "Semantic Model", "Error", "Fix report objects which reference invalid semantic model objects", lambda df: df["Valid Semantic Model Object"] == False, "This rule highlights visuals, report filters, page filters or visual filters which reference an invalid semantic model object (i.e Measure/Column/Hierarchy).", "", ), ( "Performance", "Page", "Warning", "Reduce the number of visible visuals on the page", lambda df: df["Visible Visual Count"] > 15, 'Reducing the number of visable visuals on a page will lead to faster report performance. This rule flags pages with over " + visVisuals + " visible visuals.', ), ( "Performance", "Visual", "Warning", "Reduce the number of objects within visuals", lambda df: df["Visual Object Count"] > 5, "Reducing the number of objects (i.e. measures, columns) which are used in a visual will lead to faster report performance.", ) ], columns=[ "Category", "Scope", "Severity", "Rule Name", "Expression", "Description", "URL", ], ) rep.run_report_bpa(report=report_name, workspace=report_workspace, rules=rules) View all semantic model objects within a report rpt.list_semantic_model_objects() View broken report objects (if any semantic model objects used in the report do not exist within the semantic model) The 'Valid Object' column indicates if the object (measure/column/hierarchy) exists within the semantic model used by the report rpt.list_semantic_model_objects(extended=True) View broken report objects across all reports tied to a semantic model dataset_name = '' # Enter the semantic model name dataset_workspace = None # Enter the workspace name in which the semantic model exists (if set to None it will use the workspace in which the notebook is running) labs.list_report_semantic_model_objects(dataset=dataset_name, workspace=dataset_workspace, extended=True) List functions within the ReportWrapper rpt.list_custom_visuals() rpt.list_pages() rpt.list_visuals() rpt.list_visual_objects() rpt.list_report_filters() rpt.list_page_filters() rpt.list_visual_filters() rpt.list_report_level_measures() rpt.list_visual_interactions() rpt.list_bookmarks() Perform actions on a report Set a custom theme for a report based on a Power BI theme file rpt.set_theme(theme_file_path='/lakehouse/default/Files/CY23SU09.json') rpt.set_theme(theme_file_path='https://raw.githubusercontent.com/PowerBiDevCamp/FabricUserApiDemo/main/FabricUserApiDemo/DefinitionTemplates/Shared/Reports/StaticResources/SharedResources/BaseThemes/CY23SU08.json') Set whether a Power BI report page is hidden or visible rpt.set_page_visibility(page_name='Page 1', hidden=True) Set the active page (page shown upon opening the report) rpt.set_active_page(page_name='Page 2') Disable the 'show items with no data' for all visuals within a Power BI report rpt.disable_show_items_with_no_data() Remove all custom visuals in a Power BI report which are not used in any visuals within the report rpt.remove_unnecessary_custom_visuals() Hide all pages which are used for a tooltip or drillthrough rpt.hide_tooltip_drillthrough_pages() https%3A%2F%2Fgithub.com%2Fmicrosoft%2Fsemantic-link-labs%2Fblob%2Fmain%2Fnotebooks%2FReport%2520Analysis.ipynbOptimize Power BI Report with Model Best Practice Analyzer and Vertipaq Analyzer using Semantic Link
Every Power BI Developer pays attention to the underlying performance of their dashboard beyond just churning out reports. This is why adhering to best practices rules should no longer feel like a task, irrespective of your level of experience with Power BI. Semantic Link Labs is here to get the job done. If you are reading about Semantic Link Labs for the first time, you can read my demo introductory article here. This is a Python library developed by Michael Kovalsky that makes this process easier with just a few lines of code in Microsoft Fabric Notebook. Our Use Case: David, A Power BI developer at the AMA enterprise, got several complaints from colleagues that the Sales and Return dashboards take too long to load and sometimes time out. David thought of many reasons why this could have happened, but he wants to start by using the Model Best Practice Analyser Method and Vertipaq Analyzer to investigate the issue. Before this day, David knew he could use an external tool like Tabular Editor for this task. But he recently found out about a new Python library called Semantic Link Lab using Microsoft Fabric Notebook that can help him figure out this issue with the report with just a few lines of code. The sample Sales & Returns Sample v201912 dashboard use for this demo is available for download here You can also download this Microsoft Fabric Notebook used for this Demo here. David’s Task: Perform a Model Best Practice Analyser (BPA) on the Semantic Model of your Power BI report. Perform a Vertipaq Analyzer on your semantic model. Let’s get started: Open your Notebook In Microsoft Fabric, let's investigate what went wrong. Before we get started, we must make sure we have the Semantic Link Labs library installed as well as the other necessary libraries. #Install the Semantic link Labs library in your Fabric notebook %pip install semantic-link-labs After that, we will also be installing the necessary libraries in your notebook. ### Once installed, run this code to import the library into your notebook import sempy_labs as labs from sempy_labs import report as rep from sempy_labs.report import ReportWrapper 1. Performance Model Best Practice Analyzer (BPA) on a Semantic Model report. Model Best Practice Analyzer uses best practice rules to scan your semantic models for bad DAX code, improper formatting and usage of data types, inappropriate relationships, and potentially risky calculation patterns that should be fixed in your report. #Model Best Practice Analyzer import sempy_labs as labs # Enter the name or ID of your semantic model dataset = 'Sales & Returns Sample v201912' # Enter the name or ID of the workspace in which the semantic model # resides workspace = 'Fabricday' labs.run_model_bpa(dataset=dataset, workspace=workspace) When you toggle on the results below, we can see that we have: 16 DAX expression with high rule violation severity, 10 Medium severity, report pages with other details in the Sales & Return Sample v201912 Dashboard. 64 Formatting Medium rule violation severity and 41 on low-level/hints severity. 146 Maintenance low-level/hints rule violation severity and 10 Medium rule violation severity. 46 Performance Medium rule violation severity. & Returns Sample v201912 2. Perform a Vertipaq Analyzer on your Semantic Model. import sempy_labs as labs # Enter the name or ID of your semantic model dataset = 'Sales & Returns Sample v201912' # Enter the name or ID of the workspace in which the semantic model # resides workspace = 'Fabricday' #x = labs.vertipaq_analyzer(dataset=dataset, workspace=workspace) # Setting export='table' will export the results to delta tables in # the lakehouse attached to the notebook x = labs.vertipaq_analyzer(dataset=dataset, workspace=workspace, export='table') # Setting export='zip' will export the results to a .zip file in the lakehouse attached to the notebook. # x = labs.vertipaq_analyzer(dataset=dataset, workspace=workspace, export='zip') The result below shows that the Vertipaq Analyzer Analysis of the Sales & Returns Sample v201912 has been exported to a delta table in my Lakehouse. A snapshot of the Vertipaq Analyzer in my Lakehouse. This shows the Vertipaq Analyzer columns, hierarchies, tables, model, partitions, relationships and tables which can be visualize to understand the semantic model performances. Conclusion If you ever have a slow Power BI Dashboard that takes time to load, and you want to understand the reason for the slow performance. You can download the Microsoft Fabric Notebook used for this optimisation and enhancement Power BI report with Semantic Link Labs here. References Semantic Link Labs Git Hub Repository by Michael Kovalsky: https://github.com/microsoft/semantic-link-labs Feel free to connect with me via my socials below if you want to discuss further. LinkedIn: Musili Adebayo Twitter: Musili_Adebayo https%3A%2F%2Fgithub.com%2FMusili-Adebayo%2F-Musili-Adebayo-fabric_day_project_dashboard%2Fblob%2Fmain%2Fpowerbi_report_notebook.ipynbBest Practice Analyzer Report
Download this notebook from: semantic-link-labs/notebooks/Best Practice Analyzer Report.ipynb at main · microsoft/semantic-link-labs · GitHub Install the latest .whl package Check here to see the latest version. %pip install semantic-link-labs Import the library and necessary packages import sempy_labs as labs import sempy_labs.report as rep Collect semantic model Best Practice Analyzer stats Collect stats for all semantic models within a single workspace labs.run_model_bpa_bulk(workspace='Workspace 1') Collect stats for all semantic models within a list of workspaces labs.run_model_bpa_bulk(workspace=['Workspace 1', 'Workspace 2']) Collect stats for all semantic models within all accessible workspaces labs.run_model_bpa_bulk(workspace=None) Create a Direct Lake semantic model (called 'ModelBPA') for analyzing the Best Practice Analyzer results labs.create_model_bpa_semantic_model() Create a Power BI report called 'ModelBPA' based semantic model created in the previous cell, which can be used to analyze the Best Practice Analyzer results rep.create_model_bpa_report() Note: The 'BPAReport' Power BI report is located within the workspace in which the default lakehouse attached to this notebook resides. Navigate to this workspace to open the report and view the Best Practice Analyzer results. Going forward, you just need to run the 'run_model_bpa_bulk' function which will append BPA results to the 'modelbparesults' delta table in your lakehouse. Since the 'BPAModel' semantic model is in Direct Lake mode, the data will appear in the semantic model and report automatically without any need for processing the semantic model. https%3A%2F%2Fgithub.com%2Fmicrosoft%2Fsemantic-link-labs%2Fblob%2Fmain%2Fnotebooks%2FBest%2520Practice%2520Analyzer%2520Report.ipynbSemantic Model Audit
Link to Notebook Overview This tool is designed to provide a comprehensive audit of your Fabric semantic models. Key Features Model Object & Metadata Capture: Retrieves and standardizes the latest columns and measures using Semantic Link and Semantic Link Labs. Captures dependencies among model objects to get a comprehensive view of object usage. Query Log Collection: Captures both summary query counts and detailed DAX query logs. Unused Column Identification: Compares lakehouse/warehouse and model metadata to identify unused columns in your model's source lakehouse/warehouse. Removing unused columns will result in greater data compression and performance. Cold Cache & Resident Statistics: Deploys a cloned model to measure cold cache performance. Records detailed resident statistics (e.g., memory load, sizes) for each column. Star Schema Generation: Produces a set of star schema tables, making it easy to integrate with reporting tools. https%3A%2F%2Fgithub.com%2Fmicrosoft%2Ffabric-toolbox%2Fblob%2Fmain%2Ftools%2FSemanticModelAudit%2Fnotebook%2FSemanticModelAudit.ipynbRefresh a Semantic Model
Download this notebook from: semantic-link-labs/notebooks/Semantic Model Refresh.ipynb at main · microsoft/semantic-link-labs · GitHub Install the latest .whl package Check here to see the latest version. %pip install semantic-link-labs Import the library and set the initial parameters import sempy.fabric as fabric import sempy_labs as labs dataset = '' # Enter your dataset name workspace = None # Enter your workspace name (if set to None it will use the workspace in which the notebook is running) Refresh a semantic model labs.refresh_semantic_model(dataset=dataset, workspace=workspace) Visualize the refresh of a semantic model The resulting dataframe returns the trace logs capturing the details of the refresh operation df = labs.refresh_semantic_model(dataset=dataset, workspace=workspace, visualize=True) Refresh specific tables labs.refresh_semantic_model(dataset=dataset, workspace=workspace, tables=['Sales', 'Geography']) Refresh specific partitions labs.refresh_semantic_model(dataset=dataset, workspace=workspace, partitions=["'Sales'[Sales FY20]", "'Sales'[Sales FY21]"]) Refresh a combination of tables and partitions labs.refresh_semantic_model(dataset=dataset, workspace=workspace, tables=['Geography', 'Calendar'], partitions=["'Sales'[Sales FY20]", "'Sales'[Sales FY21]"]) Clear the values of a table labs.refresh_semantic_model(dataset=dataset, workspace=workspace, refresh_type='clearValues') View semantic model refreshes fabric.list_refresh_requests(dataset=dataset, workspace=workspace) Cancel a semantic model refresh labs.cancel_dataset_refresh(dataset=dataset, workspace=workspace) # Specify the request_id based on a value from list_refresh_requests labs.cancel_dataset_refresh(dataset=dataset, workspace=workspace, request_id='') View a semantic model's refresh history labs.get_semantic_model_refresh_history(dataset=dataset, workspace=workspace) View details of a specific semantic model refresh labs.get_semantic_model_refresh_history(dataset=dataset, workspace=workspace, request_id='') https%3A%2F%2Fgithub.com%2Fmicrosoft%2Fsemantic-link-labs%2Fblob%2Fmain%2Fnotebooks%2FSemantic%2520Model%2520Refresh.ipynbModel Optimization
Download this notebook from: semantic-link-labs/notebooks/Model Optimization.ipynb at main · microsoft/semantic-link-labs · GitHub Install the latest .whl package Check here to see the latest version. %pip install semantic-link-labs Import the library import sempy_labs as labs from sempy_labs import lakehouse as lake from sempy_labs import directlake import sempy_labs.report as rep dataset_name = '' workspace_name = None Vertipaq Analyzer labs.vertipaq_analyzer(dataset=dataset_name, workspace=workspace_name) Export the Vertipaq Analyzer results to a .zip file in your lakehouse labs.vertipaq_analyzer(dataset=dataset_name, workspace=workspace_name, export='zip') Export the Vertipaq Analyzer results to append to delta tables in your lakehouse. labs.vertipaq_analyzer(dataset=dataset_name, workspace=workspace_name, export='table') Visualize the contents of an exported Vertipaq Analzyer .zip file. labs.import_vertipaq_analyzer(folder_path='', file_name='') Best Practice Analyzer This runs the standard rules for semantic models posted on Microsoft's GitHub. labs.run_model_bpa(dataset=dataset_name, workspace=workspace_name) This runs the Best Practice Analyzer and exports the results to the 'modelbparesults' delta table in your Fabric lakehouse. labs.run_model_bpa(dataset=dataset_name, workspace=workspace_name, export=True) This runs the Best Practice Analyzer with the rules translated into Italian (can enter any language in the 'language' parameter). labs.run_model_bpa(dataset=dataset_name, workspace=workspace_name, language='italian') Note: For analyzing model BPA results at scale, see the Best Practice Analyzer Report notebook (link below). Best Practice Analyzer Report Run BPA using your own best practice rules import sempy sempy.fabric._client._utils._init_analysis_services() import Microsoft.AnalysisServices.Tabular as TOM import pandas as pd dataset_name = '' workspace_name = '' rules = pd.DataFrame( [ ( "Performance", "Table", "Warning", "Rule name...", lambda obj, tom: tom.is_calculated_table(table_name=obj.Name), 'Rule description...', '', ), ( "Performance", "Column", "Warning", "Do not use floating point data types", lambda obj, tom: obj.DataType == TOM.DataType.Double, 'The "Double" floating point data type should be avoided, as it can result in unpredictable roundoff errors and decreased performance in certain scenarios. Use "Int64" or "Decimal" where appropriate (but note that "Decimal" is limited to 4 digits after the decimal sign).', ) ], columns=[ "Category", "Scope", "Severity", "Rule Name", "Expression", "Description", "URL", ], ) labs.run_model_bpa(dataset=dataset_name, workspace=workspace_name, rules=rules) Translate a semantic model's metadata labs.translate_semantic_model(dataset=dataset_name, workspace=workspace_name, languages=['italian', 'japanese', 'hindi'], exclude_characters='_') Direct Lake Check if any lakehouse tables will hit the Direct Lake guardrails. lake.get_lakehouse_tables(lakehouse=None, workspace=None, extended=True, count_rows=False) lake.get_lakehouse_tables(lakehouse=None, workspace=None, extended=True, count_rows=False, export=True) Check if any tables in a Direct Lake semantic model will fall back to DirectQuery. directlake.check_fallback_reason(dataset=dataset_name, workspace=workspace_name) OPTIMIZE your lakehouse delta tables. lake.optimize_lakehouse_tables(tables=['', ''], lakehouse=None, workspace=None) Refresh/reframe your Direct Lake semantic model and restore the columns which were in memory prior to the refresh. directlake.warm_direct_lake_cache_isresident(dataset=dataset_name, workspace=workspace_name) Ensure a warm cache for your users by putting the columns of a Direct Lake semantic model into memory based on the contents of a perspective. Perspectives can be created either in Tabular Editor 3 or in Tabular Editor 2 using the Perspective Editor. directlake.warm_direct_lake_cache_perspective(dataset=dataset_name, workspace=workspace_name, perspective='', add_dependencies=True) https%3A%2F%2Fgithub.com%2Fmicrosoft%2Fsemantic-link-labs%2Fblob%2Fmain%2Fnotebooks%2FModel%2520Optimization.ipynbComposite Model: Direct Lake + Import
Composite Model Deployment: Direct Lake + Import This notebook deploys a Composite Semantic Model based on Direct Lake and Import storage modes. Import tables can come from any supported data source and relationships between Direct Lake on OneLake and Import tables are regular relationships. Small dimension or lookup tables already in Direct Lake storage mode can instead use import storage mode, giving you the option to extend the table with calculated columns and structuring the table with hierarchies for use in Power BI reports and Excel pivot tables. The feature was announced in Power BI May 2025 release. Requirements: Direct Lake on One Lake semantic model: This model is based on the AzureStorage.DataLake protocol. The model must be created in the Power BI Desktop, otherwise it will use the Sql.Database protocol. Import semantic model: This model will be used as a reference for replacement and deployment. Only the desired tables to be in Import mode are required. XMLA read/write: This will enable the deployment through XMLA endpoint. The permission is enabled at the Capacity level and permissions are managed at the Admin Portal. Libraries The only mandatory library is Semantic Link Labs, a Python library designed for use in Microsoft Fabric notebooks. This library extends the capabilities of Semantic Link offering additional functionalities to seamlessly integrate and work alongside it. In this notebook, Semantic Link Labs is used to capture the reference models definition files and to create/update the Composite Model. Remaining libraries are optional, and are used to export the Composite Model definition file to a Lakehouse. # Install Semantic Link Labs %pip install semantic-link-labs # Required Library import sempy_labs as labs # Optional Libraries import json from notebookutils import fs Parameters All the parameters expect the name or the id of the object, except the import_tables parameter. This one requires a list of names of the tables that will be in Import mode. # Required parameters workspace = 'ws_demo' # Reference workspace dataset_import = 'sm_import' # Source: Import model dataset_directlake ='sm_directlake' # Source: Direct Lake model dataset_composite = 'sm_composite' # Sink: Composite model import_tables = ['table1', 'table2', 'table3'] # List of tables that will be set to Import mode # Optional parameters storage = 'lh_demo' # Lakehouse to ouput the Composite Model definition file Model Definition Process workflow: Capture the original model definition (BIM) files Remove from the Direct Lake file the tables that will be kept in Import mode Add in the Direct Lake file the Import tables based on the Import file definition Deploy the model Existing relationships on the Direct Lake model are persisted. It will fail if the column names are different (between Direct Lake and Import models). # Get Model Definition file (BIM) from source models bim_import = labs.get_semantic_model_bim( dataset = dataset_import, workspace = workspace ) bim_directlake = labs.get_semantic_model_bim( dataset = dataset_directlake, workspace = workspace ) # Delete the tables that will change storage mode from the Direct Lake file bim_directlake['model']['tables'] = [ table for table in bim_directlake['model']['tables'] if table['name'] not in import_tables ] # Include the tables with changed storage mode to Import in the Direct Lake file for table in bim_import['model']['tables']: if table['name'] in import_tables: bim_directlake['model']['tables'].append(table) Deployment The deployment is done via XMLA endpoint. It will create or update the model if it already exists. After the deployment, a new connection will be displayed in the Gateway and Cloud Connections section of the Semantic Model Properties. A rebind of the Import tables connection is required, since it cannot rely on Single Sign On as Direct Lake does (this is only required after the first deployment). If a connection already exists for the Import table, just check it on the Map to. Otherwise, create a new connection and then Map to it. After the rebind the semantic model can be refreshed. # Create/Update the Composite Model try: labs.create_semantic_model_from_bim( dataset = dataset_composite, bim_file = bim_directlake, workspace = workspace ) except: labs.update_semantic_model_from_bim( dataset = dataset_composite, bim_file = bim_directlake, workspace = workspace ) Optional Steps The steps below are optional. The first one validates if the Import connection has been binded (this step must be done manually after deployment). The last one outptus the Composite Model definition file to a Lakehouse. Analyze Connection Bindings # Retrieves the list of connections visible in the Fabric environment connections = labs.list_connections() # Retrieves the list of connection dependencies in the Semantic Model model_connections = labs.list_item_connections( item_name = dataset_composite, item_type = 'SemanticModel', workspace = workspace ) # Connections that do not rely on AzureDataLakeStorage (Direct Lake on Onelake) connection_ids = model_connections[ model_connections['Connection Type'] != 'AzureDataLakeStorage' ]['Connection Id'] for con in connection_ids: connection_path = model_connections[ model_connections['Connection Id'] == con ]['Connection Path'].iloc[0] if con: connection_name = connections[ connections['Connection Id'] == con ]['Connection Name'].iloc[0] print(f'The path {connection_path} has been mapped to the {connection_name} connection.') else: print(f'No connection found for path {connection_path}. Bind the connection before refreshing the semantic model.') Output Model Definition File # Save model definition to Lakehouse path = f'abfss://{workspace}@onelake.dfs.fabric.microsoft.com/{storage}.Lakehouse/Files/{dataset_composite}.json' file = json.dumps( bim_directlake, indent = 2 ) fs.put( path, file, overwrite = True ) https%3A%2F%2Fgithub.com%2Fdiego-dsanalytics%2Ffabric-notebooks%2Fblob%2Fmain%2FFiles%2Fcomposite_model.ipynbSemantic Model Management
Download this notebook from: semantic-link-labs/notebooks/Semantic Model Management.ipynb at main · microsoft/semantic-link-labs · GitHub Install the latest .whl package Check here to see the latest version. %pip install semantic-link-labs Import the library and necessary packages import sempy_labs as labs source_dataset = '' # Name of the semantic model to backup target_dataset = '' # Name of the semantic model to restore source_workspace = '' # Name of the workspace in which the semantic model resides target_workspace = '' # Destination workspace of the semantic model source_file_path = '' # Name/path of the backup file to create target_file_path = '' # Name/path of the backup file to be copied to the target workspace storage_account = '' # Name of the ADLS Gen2 storage account associated with both source & target workspaces Prerequisites Create an ADLS Gen2 storage account (in the Azure Portal) Assign the ADLS Gen2 storage account to both source and target workspaces Navigate to your workspace. Select 'Workspace settings'. Select 'Azure connections'. Within 'Azure Data Lake Gen2 Storage' click 'Configure'. Enter your Subscription, Resource Group and Storage Account. Click 'Save'. Backup, copy and restore a semantic model to a new workspace labs.backup_semantic_model( dataset=source_dataset, file_path=source_file_path, workspace=source_workspace, ) labs.copy_semantic_model_backup_file( source_workspace=source_workspace, target_workspace=target_workspace, source_file_name=source_file_path, target_file_name=target_file_path, storage_account=storage_account, ) labs.restore_semantic_model( dataset=target_dataset, file_path=target_file_path, workspace=target_workspace, ) https%3A%2F%2Fgithub.com%2Fmicrosoft%2Fsemantic-link-labs%2Fblob%2Fmain%2Fnotebooks%2FSemantic%2520Model%2520Management.ipynb
