How to Ingest Data from SSAS Cubes in a PySpark Notebook

Hi prabhatnath,

Thank you for reaching out in Microsoft Community Forum.

Yes, it is possible to ingest data from SSAS cubes into Fabric Lakehouse using Notebooks. This approach offers better manageability, version control with Git, and automation capabilities.

Please follow below steps To ingest data from SSAS Cubes into Fabric Lakehouse using Notebooks;

1.Install pyodbc for connecting to SSAS:
%pip install pyodbc

2.Use the service account credentials:
conn_str = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER=<SSAS_SERVER>;DATABASE=<CUBE_NAME>;UID=<SERVICE_ACCOUNT>;PWD=<PASSWORD>'
conn = pyodbc.connect(conn_str)

3.Run the DAX query and load the results into a DataFrame:
dax_query = "EVALUATE 'Sales'"
cursor = conn.cursor()
cursor.execute(dax_query)
rows = cursor.fetchall()

4.Convert the DataFrame to Spark DataFrame and save it to Lakehouse:
df_spark = spark.createDataFrame(df)
df_spark.write.format("delta").mode("overwrite").save("Tables/SSAS_Data")

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

Hi prabhatnath,

Thank you for reaching out in Microsoft Community Forum.

Please follow below to Secure Authentication Without Storing Passwords;

1.Use Azure Key Vault for Secure Storage;
Store the Service Account credentials (username and password) in Azure Key Vault and Retrieve the credentials securely at runtime in the notebook.

from azure.identity import DefaultAzureCredential
from azure.keyvault.secrets import SecretClient

# Connect to Key Vault
key_vault_url = "https://<YOUR-KEYVAULT-NAME>.vault.azure.net"
credential = DefaultAzureCredential()
client = SecretClient(vault_url=key_vault_url, credential=credential)

# Retrieve credentials
username = client.get_secret("SSAS-Username").value
password = client.get_secret("SSAS-Password").value

2.Use Service Principal with Managed Identity
Enable Managed Identity for your notebook in Fabric Admin Portal and Authenticate without storing credentials by using the Managed Identity token.

from azure.identity import ManagedIdentityCredential
import pyodbc

# Authenticate using Managed Identity
credential = ManagedIdentityCredential()
token = credential.get_token("https://database.windows.net/.default").token

# Connect to SSAS
conn_str = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER=<SSAS_SERVER>;DATABASE=<CUBE_NAME>;Authentication=ActiveDirectoryMsi;Token={token}'
conn = pyodbc.connect(conn_str)

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.