Connect Power BI and Spark notebooks with Microsoft Fabric Semantic Link

It appears that Fabric Semantic Link (FSL) is the new hero in town, offering a solution to this problem. The typical workflow with FSL resembles the illustration below:

I created in my Fabric workspace notebook where I will write all the code. You canStrengthen the bond between Power BI and Spark Notebooks with Microsoft Fabric Semantic Link install the Semantic Library in-line for your current session in the notebook using the following command:

%pip install semantic-link

Alternatively, you can install it from the Library Management, which makes it available for the entire workspace:

After installation, you can import the SemPy library to begin working with it.

 

import sempy.fabric as sf

In my examples, I have uploaded one of Microsoft’s sample Power BI models called “Corporate Spend” (you can find it here). The first method I’d like to demonstrate is the read_table function. As the name suggests, it reads a table from the dataset. You simply provide the dataset name as the first parameter and the table name as the second:

df = sf.read_table("Corporate Spend","Fact")
type(df)

I also verified the type of object returned by the read_table function, and as you can see, it is an object called a “Fabric Data Frame”:

Manually in this case means that you can specify values directly:

df = sf.FabricDataFrame({
    "Sales Agent": ["Agent 1", "Agent 1", "Agent 2"],
    "Customer[Country/Region]": ["US", "GB", "US"],
    "Industry[Industry]": ["Services", "CPG", "Manufacturing"],
    "Product Category[Category]": ["Electronics", "Clothing", "Electronics"],
    "Revenue": [10000, 5000, 12000],
})

As I mentioned earlier, with SemPy, you have the functionality of Pandas at your disposal. Therefore, you can easily use methods like head to display the first 10 records of your FabricDataFrame:

df.head(10)

Pandas indeed offers a vast array of methods for data manipulation and analysis (I recommend exploring the Pandas documentation link). In this post, I won’t delve into the common Pandas data manipulation patterns, but rather, I’ll concentrate on the essential functionality of interacting with Power BI using the Semantic Library.

One of the primary exploratory functions available is list_datasets which retrieves all the models within our workspace (the workspace where our notebook is located) along with some fundamental information about them.

sf.list_datasets()

Once you’ve identified the model you want to explore, you can provide its name to the next function, called list_tables to examine the objects contained within it. This function allows you to get a closer look at the tables and elements present in the selected model:

sf.list_tables("Corporate Spend")

Even more intriguing is the ability to examine the relationships between those tables, which can be done using the list_relationships function. As shown below, it provides a comprehensive list with details about the cardinality and the columns on which each relationship is based. This feature is indeed quite valuable for understanding the data structure and relationships within the Power BI model.

sf.list_relationships("Corporate Spend")

If you find that textual descriptions of relationships are not sufficient, you can take it a step further by visualizing these relationships using the plot_relationship_metadata function. This feature is not only visually appealing but also highly beneficial for gaining a more intuitive understanding of the model directly from your code.

from sempy.relationships import plot_relationship_metadata
plot_relationship_metadata(sf.list_relationships("Corporate Spend"))

Looks impressive isn’t it?

As you might anticipate, you can also review measures, including their associated expressions, to gain insights into how calculations and metrics are defined within the model. This capability allows you to examine the logic and formulas behind the measures, aiding in your understanding of the data and its calculations.

sf.list_measures("Corporate Spend")

Now, it’s a breeze to get the hang of how the entire model is put together. And if you ever fancy running a specific measure within a particular context, the evaluate_measure function has got your back. You can dial in not only the measure you want to crunch but also tinker with column groupings, apply filters, and more. It’s like having a toolbox of data tricks at your fingertips!

sf.evaluate_measure("Corporate Spend"
        ,measure="Amount"
        ,groupby_columns=["Department[VP]","Scenario[Scenario]"]
        ,filters={"Scenario[Scenario]": ["Actual", "Plan"]})

The example above is a pretty standard approach for folks working with data frames. However, if you’re fluent in DAX, you can send queries directly using the evaluate_dax method. This method lets you harness your DAX skills to craft custom queries and get precisely the results you need. It’s a powerful option for those who are well-versed in DAX.

df_result = sf.evaluate_dax(
    "Corporate Spend",
    """
    EVALUATE SUMMARIZECOLUMNS(
         'Department'[VP]
        ,'Scenario'[Scenario]
        ,"Total Revenue"
        ,CALCULATE([Amount])
        )
    """)

display(df_result)

%load_ext sempy

%%dax "Corporate Spend"

    EVALUATE SUMMARIZECOLUMNS(
         'Department'[VP]
        ,'Scenario'[Scenario]
        ,"Total Revenue"
        ,CALCULATE([Amount])
        )

spark.conf.set("spark.sql.catalog.pbi", "com.microsoft.azure.synapse.ml.powerbi.PowerBICatalog")

%%sql

SHOW TABLES FROM pbi.`Corporate Spend`

Extracting data from a dataset is a straightforward process. You can retrieve the desired data using standard SQL queries, similar to how you would interact with any other Spark data source. This simplicity makes working with Power BI datasets and performing data selection effortless.

%%sql

SELECT * FROM pbi.`Corporate Spend`.`Fact`
LIMIT 10

Certainly, it’s worth noting that not only Spark SQL but also PySpark constructs are at your disposal through the Native Connector. This means you can leverage the full power of PySpark for data processing and analysis when working with Power BI datasets.

spark.table("pbi.`Corporate Spend`.Fact").printSchema()

Indeed, there are many more possibilities with Semantic Link that I haven’t covered here. Exploring the concept of semantic propagation and the range of semantic functions it offers, along with data augmentation capabilities, is definitely worth delving into. These aspects of Semantic Link can significantly enhance your data workflows and analytical capabilities. for me huge game-changer!

Official documentation:

• What is Semantic Link?
• Announcement Blog Post
• SemPy documentation
• Sample notebook examples