We need to store semantic models for further usage or historic data. Currently I cannot find a pipeline that allows the usage of semantic models as input.
What are your suggestions how to solve this?
I currently came up with the following solution:
- Run a notebook after refresh of the semantic model to read the data from the semantic model and save its data to lakehouse (see code example below)
- Create a sql connection from Power BI to lakehouse, to be able to do further transformations via powerquery
This approach seems to work. It also does not seem to run into the DAX export limits as power automate jobs do with large tables.
But isn't there a better/already build-in approach?
Please tell me, if you know any better solutions or have other ideas.
Code example:
def process_semantic_to_lakehouse(config )
from datetime import datetime
# 1. Read the table from the semantic model
logger.info("Reading semantic model table from workspace '%s', dataset '%s', table '%s'.",
config['workspace'], config['dataset'], config['table'])
df_semantic_model = FabricDataFrame.read_table(
workspace=config['workspace'],
dataset=config['dataset'],
table=config['table']
)
logger.info("Successfully read semantic model table with %d rows.", len(df_semantic_model))
# 2. Optionally add a timestamp column for historical tracking
if config.get("add_import_timestamp", False )
logger.info("Adding import timestamp column 'Import Datum' to the DataFrame.")
display("Adding import timestamp column...")
df_semantic_model['Import Datum'] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
else:
logger.info("Skipping import timestamp column as per configuration.")
# 3. Save the data to the lakehouse table
logger.info("Saving DataFrame to lakehouse table '%s' with mode '%s'.",
config['lakehouse_table'], config['mode'])
df_semantic_model.to_lakehouse_table(
name=config['lakehouse_table'],
mode=config['mode']
)
logger.info("Data successfully saved to lakehouse table '%s'.", config['lakehouse_table'])
