0

I have a rudimentary question, when connecting an SSAS-TM (SQL Server Analysis Services - Tabular Model) database (on-premise) in my own local machine to my Power BI Desktop (also in my local machine) via Import mode.

I am not at all familiar with the memory allocation parameters.

The relational database I have is a very simple AdventureWorksDW. I develop a SQL Server Analysis Services - Tabular Model project, using Visual Studio 2015, and deploy the Project as a new database in the Analysis Services Engine. I am able to query tables in this SSAS-TM database in the following format in the SSMS (SQL Server Management Studio), using DAX language:

EVALUATE 'tablename'

However, when I try to connect this SSAS-TM database to my Power BI Desktop via an Import connection, I get the following error.

AnalysisServices: The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

I have the properties from the SSMS in this image file.enter image description here

I tried to refer some links related to setting up some Vertipaq memory parameters, after referring some links in learn.microsoft.com. It has not been useful to me.

My simple question is this:

What properties do I need to change in the image file above to make this connection successful? This is only training, so am using Adventure Works DW here. So size is not an issue. My laptop has a lot of memory and disk space, and is 64-bit. The Power BI Desktop is 64-bit.

Can someone help me ?

Improve this question

2 Answers 2

Reset to default
0

The Power BI connector for Analysis Services that has the table picker will generate an MDX query instead of a DAX query. And if you try to extract a more than a handful of rows, it will fail. It's a known issue, but low priority because.

  1. Don't import from SSAS. Use Live Connect. You've already got a Cube/Data Set, you can just connect to it and write reports.

  2. If you absolutely must import from SSAS, use a DAX query, eg

In M:

AnalysisServices.Database("MySSAS", "AdventureWorksDW", [Query="evaluate FactResellerSales", Implementation="2.0"])

or in the UI

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

what if i need to import many tables, and not just FactResellerSales ? do i need to do it multiple times ? i can see that for small tables the Import happens, but for large tables it throws the above error (as you have mentioned in your answer). When I use Live Connect mode, then I miss the Data View in the Power BI Desktop.
If you want to import the data, import it from SQL Server not Analysis Services.
Then you should use a live connection to SSAS in Power BI. You don't want to copy all that data from SSAS to Power BI. To work around not having the data view in Live Connect, add some report pages for data exploration.
If you have already deployed a SSAS tabular model and you are querying that SSAS DB to get the data, instead of fetching all those tables again in a PBI, you can write a DAX query (by utlizing measures that already exists in the SSAS DB/ by Defining Measures in the new DAX query that gets created and utlized at the run time) to get the absolute transformed table.
anyway, i have chosen to use LiveConnect; but I was comparing the two options
|
0

Use Live Connect if you are only getting data from cube. If you are getting data from Excel files, etc. then you are forced to use Import. I have used Import to get many tables from cube, no memory errors. What you can do is Import 3 tables at a time, then in Power BI advanced editor select option to add more tables from cube, add another 3 tables, see how that goes. With Live Connect even if the relationship columns are hidden, you still get them. With Import, if they are hidden, you can't select them, so can't create the relationships.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.