Forum Discussion
SQL Query SM in notebook
hi comunnity, i n this time, I try to write a query in SQL to SM by notebook, I know the "explore" option to get the matrix data, and "write DAX queries" are useful tools to query data, but my user it's more comfortable with SQL syntax.
So I decided to use notebook to query data from SM using SQL syntax. (sempy )
The first question is: it its possible to query as example: ? to SM 🙂
%%sql
SELECT YEAR(OrderDate) AS OrderYear,
SUM((UnitPrice * Quantity) + Tax) AS GrossRevenue
FROM salesorders
GROUP BY YEAR(OrderDate)
ORDER BY OrderYear;
The next step, I'm tryting to use the library, but dont work . 😞
The SM is a power bi dataset, so I'm using the sempy library. I have a test.
In the notebook. I have the next code.
import sempy.fabric as fabric
dataset = "SM-example matrix filtered by column" # Enter the name or ID of the semantic model
workspace = "TEst" # Enter the workspace name or ID in which the semantic model exists<p> <li-code lang="markup">fabric.list_measures(dataset)
so It's fine, but the next code, It trigger a expection.
# convert to a spark dataframe so that can use select syntax
sparkdf = spark.createDataFrame(fabricdf)
ValueError: Some of types cannot be determined after inferring
Why the valueerror ? 😞
and the next code should be:
daxdf=sparkdf.select("Measure Name","Measure Expression").where(sparkdf["Measure Name"]=="Measure")
display(daxdf)
thanks in advance.
Hi Peter_23 ,
Thanks for reaching out to the Microsoft fabric community forum
Can You Query SM with SQL Syntax?
Not directly in the way you're hoping. Semantic models (SMs) in Power BI are fundamentally DAX-based, not SQL based. Even though sempy lets you interact with SMs in notebooks, it doesn't support SQL querying like:
SELECT YEAR(OrderDate), SUM(...) FROM salesorders
Instead, sempy is designed to:
- List metadata (tables, columns, measures)
- Execute DAX queries
- Retrieve results as pandas DataFrames
So your SQL Style query needs to be rewritten in DAX and executed via sempy's evaluate_dax() method.
Why the ValueError on spark.createDataFrame(fabricdf)?
This error typically means that the fabricdf object you're passing to Spark doesn't have clearly inferrable types for all columns. Here's what might be going wrong:
- fabric.list_measures(dataset) returns a pandas DataFrame, not a Spark DataFrame.
- Spark needs explicit schema or cleanly inferrable types to convert a pandas DataFrame.
If some columns contain mixed types (e.g., None, strings, numbers), Spark can't infer them automatically.
How to Fix It
Option 1: Stick with pandas
If you're just filtering and displaying metadata, pandas is simpler and works fine:
python
import sempy.fabric as fabric
dataset = "SM-example matrix filtered by column"
workspace = "TEst"
fabricdf = fabric.list_measures(dataset)
Filter using pandas
daxdf = fabricdf[fabricdf["Measure Name"] == "Measure"]
display(daxdf)
Option 2: Explicitly define schema for Spark
If you must use Spark:
from pyspark.sql.types import StructType, StructField, StringType
schema = StructType([
StructField("Measure Name", StringType(), True),
StructField("Measure Expression", StringType(), True)
])
sparkdf = spark.createDataFrame(fabricdf, schema=schema)
daxdf = sparkdf.select("Measure Name", "Measure Expression").where(sparkdf["Measure Name"] == "Measure")
display(daxdf)
Semantic link propagation with SemPy - Microsoft Fabric | Microsoft Learn
SemPy in Microsoft Fabric: From SQL Scripts to Sem... - Microsoft Fabric Community
Best Regards,
Lakshmi.
3 Replies
Hi Peter_23 ,
Thanks for reaching out to the Microsoft fabric community forum
Can You Query SM with SQL Syntax?
Not directly in the way you're hoping. Semantic models (SMs) in Power BI are fundamentally DAX-based, not SQL based. Even though sempy lets you interact with SMs in notebooks, it doesn't support SQL querying like:
SELECT YEAR(OrderDate), SUM(...) FROM salesorders
Instead, sempy is designed to:
- List metadata (tables, columns, measures)
- Execute DAX queries
- Retrieve results as pandas DataFrames
So your SQL Style query needs to be rewritten in DAX and executed via sempy's evaluate_dax() method.
Why the ValueError on spark.createDataFrame(fabricdf)?
This error typically means that the fabricdf object you're passing to Spark doesn't have clearly inferrable types for all columns. Here's what might be going wrong:
- fabric.list_measures(dataset) returns a pandas DataFrame, not a Spark DataFrame.
- Spark needs explicit schema or cleanly inferrable types to convert a pandas DataFrame.
If some columns contain mixed types (e.g., None, strings, numbers), Spark can't infer them automatically.
How to Fix It
Option 1: Stick with pandas
If you're just filtering and displaying metadata, pandas is simpler and works fine:
python
import sempy.fabric as fabric
dataset = "SM-example matrix filtered by column"
workspace = "TEst"
fabricdf = fabric.list_measures(dataset)
Filter using pandas
daxdf = fabricdf[fabricdf["Measure Name"] == "Measure"]
display(daxdf)
Option 2: Explicitly define schema for Spark
If you must use Spark:
from pyspark.sql.types import StructType, StructField, StringType
schema = StructType([
StructField("Measure Name", StringType(), True),
StructField("Measure Expression", StringType(), True)
])
sparkdf = spark.createDataFrame(fabricdf, schema=schema)
daxdf = sparkdf.select("Measure Name", "Measure Expression").where(sparkdf["Measure Name"] == "Measure")
display(daxdf)
Semantic link propagation with SemPy - Microsoft Fabric | Microsoft Learn
SemPy in Microsoft Fabric: From SQL Scripts to Sem... - Microsoft Fabric Community
Best Regards,
Lakshmi.
Hi Peter_23 ,
I wanted to follow up and confirm whether you’ve had the opportunity to review the information we provided. If you have any questions or need further clarification, please don’t hesitate to reach out.
We appreciate your engagement and thank you for being an active part of the community.
Best regards,
Lakshmi.Thanks v-lgarikapat I understand
