SSAS Architecture
XMLA Commands Between Power BI and SSAS#
Power BI uses XMLA (XML for Analysis) commands to communicate with SQL Server Analysis Services (SSAS). These commands are essential for querying, managing, and interacting with SSAS data models. Below is a detailed explanation of the specific XMLA commands sent between Power BI files (.pbix
) and SSAS, along with examples.
Common XMLA Commands#
- DISCOVER:
- Used to retrieve metadata about the SSAS model, such as available tables, measures, and hierarchies.
-
Output Example:
-
EXECUTE:
- Used to execute MDX (Multidimensional Expressions) or DAX (Data Analysis Expressions) queries against the SSAS model.
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis"> <Command> <Statement> EVALUATE SUMMARIZE( Sales, "Year", Sales[Calendar Year], "Total Sales", SUM(Sales[Sales Amount]) ) </Statement> </Command> <Properties> <PropertyList> <Catalog>AdventureWorks</Catalog> </PropertyList> </Properties> </Execute>
-
Output Example:
-
PROCESS:
- Used to refresh SSAS objects, such as tables or partitions, ensuring that data is up-to-date.
-
Output Example:
-
ALTER:
- Used to modify SSAS objects, such as updating the schema or changing properties.
- Output Example:
Workflow of XMLA Commands in Power BI#
- Initialization:
-
When a Power BI file connects to SSAS, it sends a
DISCOVER
command to retrieve metadata about the data model. -
Query Execution:
-
Power BI sends
EXECUTE
commands to fetch data based on user-defined queries or visualizations. -
Data Refresh:
-
During scheduled refreshes, Power BI may send
PROCESS
commands to ensure the SSAS model is updated. -
Model Updates:
- If changes are made to the SSAS model, Power BI can send
ALTER
commands to apply updates.
Interaction Diagram: Power BI Desktop and SSAS#
Below is a mermaid diagram illustrating the flow of XMLA commands between Power BI Desktop and SSAS:
sequenceDiagram
participant PBI as Power BI Desktop
participant SSAS as SQL Server Analysis Services
PBI->>SSAS: IMAGE LOAD
SSAS-->>PBI: SSAS Creates a database based on the DataModel in the `pbix` file. Returns Confirmation of process.
PBI->>SSAS: DISCOVER
SSAS-->>PBI: Metadata (Tables, Measures, Hierarchies)
PBI->>SSAS: EXECUTE (MDX/DAX Query)
SSAS-->>PBI: Query Results
PBI->>SSAS: PROCESS (Refresh Data)
SSAS-->>PBI: Confirmation
PBI->>SSAS: ALTER (Update Schema)
SSAS-->>PBI: Confirmation
PBI->>SSAS: IMAGE SAVE
SSAS-->>PBI: SSAS saves the database to the DataModel embedded in the `pbix` file. Returns Confirmation of process.
This diagram visually represents the communication between Power BI Desktop and SSAS, highlighting the key XMLA commands and their responses.