Import SSAS Tabular Database: Unexpected Exception

[Anonymous]

I have a SSAS Tabular database on premise.  I try to Import this database into PowerBI Desktop, however, whenever I select one of the tables in this database I get Unexpected Exception occurred as an error message.  In this database there are several dimension tables and one fact table.  The error comes up whenever the fact table is selected.  Note that If I try to do the same thing but using  Live Connection mode it works fine.

The fact table is of course much larger in size than the other tables but from this error message there's nothing indicative of a size problem.  I have included a screenshot below.

[v-yuezhe-msft]

@Anonymous，

What is version of your SSAS and what is the edition of the SQL Server instance?  Do you get same error when you connect to different SSAS databases?

Regards,
Lydia

[Anonymous]

SSAS: 13.0.1601.5 (64 bit)

SQL Server: Microsoft SQL Server 2016 (RTM-GDR) (KB3179258) - 13.0.1711.0 (X64)   Jul 29 2016 16:16:44   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 

I created another database under the same ssas instance.  This database contains the same Tabular model I was trying to Import before without success.  However in this case I shrunk the fact table size to only 1K rows (whereas the original had 3.6M).  When I do 'Get Data' and 'Import' through Power BI Desktop it works fine.

[v-yuezhe-msft]

@Anonymous,

Based on your test, the issue is related to the size of your fact table size.

I note that your SQL Server is RTM version, I would recommend you install SQL Server 2016 SP1 and latest CU for SP1, then check if the issue still occurs.

Regards,
Lydia

[Anonymous]

We've updated the SQL Server as follows:

SQL Server: Microsoft SQL Server 2016 (SP1-CU6) (KB4037354) - 13.0.4457.0 (X64)   Nov  8 2017 17:32:23

SSAS: 13.0.4457.0

The Size of the source database in SQL Server is: 2.4 GB (MDF File), 4.6 GB (LDF File)

The Size of the database in SSAS Server (the Tabular Cube) is 283 MB.  I get this by right clicking on the databse in SSAS then selecting Reports-->Standard Reports-->General

My computer has 8GB of RAM and at the time I try to Import the SSAS cube in Power BI Desktop I have about 4 GB of free RAM.

The error message I am getting now is below.  Note that We are already using the 64 bit version of the product and the latest November 2017 update.

[v-yuezhe-msft]

@Anonymous,

Regarding to the memory settings in SSAS, please post the question in SSAS forum to get dedicated support. And you can refer to this article.

From Power BI side, try to increase maximum allowed memory, and uncheck the following options.


Regards,

Lydia

[Anonymous]

Thanks for the feedback.  We've attempted several memory settings but none resolved the issue.

However, after futher experiments we've made some discoveries.  So our case was as follows: We have a source database that is modeled as a Star Schema.  Furthermore, we've created a SSAS Tabular cube for this database and then tried to Import this SSAS Tabular Cube into Power BI.  After creating the connection in Power BI Desktop to this SSAS Tabular cube we go to the Navigator window in Power BI that allows the user to pick from a list of tables.  What we have noticted is that as we selected tables from the left hand side of the window, on the right hand side in the preview we can see that the data is displayed as a cross-product of all the rows in all the tables that have been selected.  And as we select more tables the error mentioned above comes up. I am not sure why this cross-product takes place but as you can imagine what is in the preview becomes very huge very quickly and this is likely why the error comes up.

So our understanding is that anything imported into Power BI Desktop will be converted into a Tabular Model so it seemed counter intuitive to take an SSAS cube that is already in Tabular mode and have Power BI Desktop convert it to a Tabular Model again.  Perhaps it is not best practice to do that in such a scenario.

So instead we did an Import directly from the source database (the star schema instead of our SSAS Tabular) and that worked.  Also in that case, as we selected the list of tables to import (from the Navigator window) we did not see any cross-product taking place.  The Navigator in this case was simply showing the contents of each table selected.

[Tx_jeremy]

This has to be a bug, right?  There is no logical reason for the behavior to be a cross product.  Import should either separate the tables selected into distinct tables in Power BI or it should create a results set based on the Tabular model.

[Anonymous]

I can't confirm it's a bug but it sure sounds like one.

[v-yuezhe-msft]

@Anonymous,

Please review the following similar thread to troubleshoot this issue.

https://community.powerbi.com/t5/Desktop/Not-Enough-Memory-Error/td-p/193818

Regards,
Lydia

[Anonymous]

Thanks, it's not clear though from the thread which of the memory settings resolved the issue and there are several of them.  I've included a screen shot of the Memory Related settings on the SSAS instance.  I did make changes to some of them but the memory related error is still coming up.  the screen shot has the deafult settings.  Can you please let me know which of the settings would likely require modifications?

[Anonymous]

Thanks for the response.  I will check with IT if the SQL Server can be updated and test it out again.