title | description | ms.author | author | ms.reviewer | reviewer | ms.topic | ms.custom | ms.date | ms.search.form | |
---|---|---|---|---|---|---|---|---|---|---|
Read data from semantic models and write data that semantic models can consume using python |
Learn how to read from semantic models and write data that can be used in semantic models using python. |
franksolomon |
fbsolo-ms1 |
marcozo |
eisber |
how-to |
|
11/15/2023 |
Read write powerbi |
In this article, you'll learn how to read data and metadata and evaluate measures in semantic models using the SemPy python library in Microsoft Fabric. You'll also learn how to write data that semantic models can consume.
[!INCLUDE prerequisites]
- Go to the Data Science experience in [!INCLUDE product-name].
- Create a new notebook to copy/paste code into cells.
- [!INCLUDE sempy-notebook-installation]
- Add a Lakehouse to your notebook.
- Download the Customer Profitability Sample.pbix semantic model from the datasets folder of the fabric-samples repository, and save the semantic model locally.
In this article, we use the Customer Profitability Sample.pbix semantic model. This semantic model references a company manufacturing marketing materials and contains data about products, customers, and corresponding revenue for various business units.
- Open your workspace in Fabric Data Science.
- Select Upload > Browse and select the Customer Profitability Sample.pbix semantic model.
:::image type="content" source="media/read-write-power-bi/upload-power-bi-data-to workspace.png" alt-text="Screenshot showing the interface for uploading a semantic model into the workspace." lightbox="media/read-write-power-bi/upload-power-bi-data-to workspace.png":::
Once the upload is done, your workspace will have three new artifacts: a Power BI report, a dashboard, and a semantic model named Customer Profitability Sample. You'll use this semantic model for the steps in this article.
:::image type="content" source="media/read-write-power-bi/uploaded-artifacts-in-workspace.png" alt-text="Screenshot showing the items from the Power BI file uploaded into the workspace." lightbox="media/read-write-power-bi/uploaded-artifacts-in-workspace.png":::
The SemPy Python API can retrieve data and metadata from semantic models located in a Microsoft Fabric workspace and execute queries on them.
[!INCLUDE sempy-default-workspace]
To read data from semantic models:
-
List the available semantic models in your workspace.
import sempy.fabric as fabric df_datasets = fabric.list_datasets() df_datasets
-
List the tables available in the Customer Profitability Sample semantic model.
df_tables = fabric.list_tables("Customer Profitability Sample", include_columns=True) df_tables
-
List the measures defined in the Customer Profitability Sample semantic model.
[!TIP] In the following code, we've specified the workspace for SemPy to use for accessing the semantic model. You can replace
Your Workspace
with the name of the workspace where you uploaded the semantic model (from the Upload the semantic model into your workspace section).df_measures = fabric.list_measures("Customer Profitability Sample", workspace="Your Workspace") df_measures
Now we've determined that the Customer table is the table of interest.
-
Read the Customer table from the Customer Profitability Sample semantic model.
df_table = fabric.read_table("Customer Profitability Sample", "Customer") df_table
[!NOTE]
- Data is retrieved using XMLA and therefore requires at least XMLA read-only to be enabled.
- The amount of data that's retrievable is limited by the maximum memory per query of the capacity SKU hosting the semantic model and by the Spark driver node (see node sizes) that's running the notebook.
- All requests use low priority to minimize the impact on Microsoft Azure Analysis Services performance and are billed as interactive requests.
-
Evaluate the Total Revenue measure per customer's state and date.
df_measure = fabric.evaluate_measure( "Customer Profitability Sample", "Total Revenue", ["'Customer'[State]", "Calendar[Date]"]) df_measure
[!NOTE]
- By default, data is not retrieved using XMLA and therefore doesn't require XMLA read-only to be enabled.
- Furthermore, the data is not subject to Power BI backend limitations.
- The amount of data that's retrievable is limited by the maximum memory per query of the capacity SKU hosting the semantic model and by the Spark driver node (see node sizes) that's running the notebook.
- All requests are billed as interactive requests.
-
You can add filters to the measure calculation by specifying a list of values that can be in a particular column.
filters = { "State[Region]": ["East", "Central"], "State[State]": ["FLORIDA", "NEW YORK"] } df_measure = fabric.evaluate_measure( "Customer Profitability Sample", "Total Revenue", ["Customer[State]", "Calendar[Date]"], filters=filters) df_measure
-
You can also evaluate the Total Revenue measure per customer's state and date by using a DAX query.
df_dax = fabric.evaluate_dax( "Customer Profitability Sample", """ EVALUATE SUMMARIZECOLUMNS( 'State'[Region], 'Calendar'[Date].[Year], 'Calendar'[Date].[Month], "Total Revenue", CALCULATE([Total Revenue])) """)
[!NOTE]
- Data is retrieved using XMLA and therefore requires at least XMLA read-only to be enabled.
- The amount of data retrievable is limited by the available memory in Microsoft Azure Analysis Services and the Spark driver node (see node sizes).
- All requests use low priority to minimize the impact on Analysis Services performance and are billed as interactive requests.
-
You can evaluate the same DAX query without the need to import the library, by using the
%%dax
cell magic. Let's run the cell below to load%%dax
cell magic.%load_ext sempy
The workspace parameter is optional and follows the same rules as the workspace parameter of the
evaluate_dax
function. The cell magic also supports accessing Python variables using the{variable_name}
syntax. To use a curly brace in the DAX query, escape it with another curly brace (e.g.EVALUATE {{1}}
).%%dax "Customer Profitability Sample" -w "Your Workspace" EVALUATE SUMMARIZECOLUMNS( 'State'[Region], 'Calendar'[Date].[Year], 'Calendar'[Date].[Month], "Total Revenue", CALCULATE([Total Revenue]))
The resulting FabricDataFrame is available via the
_
variable, which captures the output of the last executed cell.df_dax = _ df_dax.head()
-
Alternatively, you can add measures to data retrieved from external sources. This approach combines three tasks: it resolves column names to Power BI dimensions, defines group by columns and filters the measure. Any column names that can't be resolved within the given semantic model are ignored (see the supported DAX syntax).
from sempy.fabric import FabricDataFrame df = FabricDataFrame({ "Sales Agent": ["Agent 1", "Agent 1", "Agent 2"], "Customer[Country/Region]": ["US", "GB", "US"], "Industry[Industry]": ["Services", "CPG", "Manufacturing"], } ) joined_df = df.add_measure("Total Revenue", dataset="Customer Profitability Sample") joined_df
The SemPy read_table
and evaluate_measure
methods have more parameters that are useful for manipulating the output. These parameters include:
fully_qualified_columns
: If the value is "True", the methods return columns names in the formTableName[ColumnName]
.num_rows
: Number of rows to output in the result.pandas_convert_dtypes
: If the value is "True", the resulting DataFrame's columns are cast to the best possible dtype, using pandas convert_dtypes. If this parameter is turned off, type incompatibility issues can result between columns of related tables that might not have been detected in the Power BI model due to DAX implicit type conversion.
SemPy read_table
also uses the model information provided by Power BI.
multiindex_hierarchies
: If True, converts Power BI Hierarchies to pandas MultiIndex structure.
Spark tables added to a Lakehouse are automatically added to the corresponding default semantic model. This example demonstrates how to write data to the attached Lakehouse. The FabricDataFrame accepts the same input data as Pandas dataframes.
from sempy.fabric import FabricDataFrame
df_forecast = FabricDataFrame({'ForecastedRevenue': [1, 2, 3]})
df_forecast.to_lakehouse_table("ForecastTable")
By using Power BI, the ForecastTable table can be added to a composite semantic model using the Lakehouse semantic model.