c2025.11.0 Power BI Composite Model Support

Business Problem:

Power BI Analysts love to combine their own datasets with existing corporate Models to attain new insights.

1. Self-Service integration.  PBI Authors can't be blocked by the Data Team's todo list.
2. Want to use Power BI's data manipulation and visualization capabilities

Solution:

Use Composite Models with AtScale's Tabular 1600 Support!

Create the Cross-Datasource Relationship

• Think of the Non-AtScale data source as a "Fact Table" with Dimension Keys
  • Think of  Dimension Keys as representing an "Degenerate Dimension" that has key values that match the corresponding AtScale Dimension key values.  
  • Create Relationships "dimension-to-dimension" aka on "Conformed Dimensions"
• Data types should be the same (pay attention to datetime cols)
• AtScale Side will be a Dimensional Attribute  (NOT a database column)
  • Relationship is made to the atscale "Value" attribute.
  • AtScale dedupes Dimensional Members by Key.  If you've followed best-practice, and made your "Value" values also unique, then AtScale will always be on a "1" side of the relationship.
• Interpretation of PBI's Cardinality Type, e.g "(1:*)", is always relative to the "from" side of the relationship which depends on the drag-drop order.
  
• Supported Relationship Options
  
  One-to-Many (1:*) - Supported
• One-to-One (1:1) - Supported
• Many-to-Many(*:*) - Supported
• Many-to-One (*:1) - Not Supported when AtScale is on the "Many" Side.
  • If your model falls into this category, then AtScale Attribute's "value" column mapping should be reconsidered.

Completed Composite Data Model


In Summary
 

• Enable with Global setting: 
  xmla.discover.properties.daxdialect=tabular1600
• Connect Power BI to AtScale using a Model-Scoped URL
• Convert workbook to have a "Client-Side" Model and Direct Query Connection
• Connect to Excel Dataset
• Create Relationship(s)

Build Reports!


 Demo