SSAS Tabular Import Cross Join

[Tx_jeremy]

Selecting the Import (versus Live Query) mode for SQL Server Analysis Services Tabular Models produces unexpected results.

On the Navigator screen when selecting columns for import, if columns from more than one table are selected then the results is a cross join between the tables.  As an Analysis Services source, the relationships are defined in the database and therefore the expectation would be for the result to be a product of those relationships.  Creating a cross product result of the two tables defeats the entire purpose on the Analysis Services model.

In addition to being logically useless in almost all use cases, the cross product is also funtionally impractical as it exceeds practical memory limitations as well.

[v-qiuyu-msft]

Hi @Tx_jeremy.

Based on my research, what you got is expected. Columns can't propagate as measures. In your scenario, besides selecting these two columns from different tables, please also select a measure which is related to these columns, then you will find final results are not cross joined. 

Best Regards,
Qiuyun Yu 

[Tx_jeremy]

Thank You Quiyn, that is helpful.

However, I still don't understand why the behavior is different than "Connect Live".  I can select dimensions from two tables in live query and the grid will load appropriately.  Does this mean that Import is MDX based and "Connect Live" is DAX?

[v-qiuyu-msft]

Hi @Tx_jeremy,

You can understand Connect Live and Import via these links: 

https://docs.microsoft.com/en-us/power-bi/desktop-analysis-services-tabular-data

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#power-bi-connectivity-modes

Regarding the question "Does this mean that Import is MDX based and "Connect Live" is DAX?", I will consult it internally and back here once I get information. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @Tx_jeremy,

I got response below: 

Both are used!
MDX is still working with Tabular as it permit to the Excel’s PivotTable to work correctly (this one use MDX always)

From PBI, only DAX is used.
Queries are visible using a tool like DAX Studio

Best Regards,
Qiuyun Yu

[Tx_jeremy]

Thank You for the follow-up, Qiuyun.

I think that a more complete documentation of the functionality and behavior of Import for Analysis Services Tabular models would help avoid some of this confusion.  The articles that you referenced make no mention of the requirement to include a measure or the potential for a cross-join product.

[Anonymous]

I have a model where I selected all the attributes/measures of all tables.  So there I have "selected a measure which is related to these columns" and the problem still occurs.