Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BarryB75
Helper I
Helper I

Importing in from SSAS Cube

I'm having trouble importing from an SSAS cube.  It just gets stuck at the "Evaluating" stage and nothing ever happens.  Any ideas?

2 ACCEPTED SOLUTIONS

It is the case that Power BI can only connect to SSAS Multidimensional Enterprise Edition (or BI Edition, though that has gone in SQL Server 2016):

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-ssas-multidimensional/

 

You can vote to try to have that changed here:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/11331360-connect-multidimensional...

 

Sorry...

 

Chris

 

View solution in original post

I've just had a second confirmation from Microsoft that DAX queries are supported in SSAS 2016 Multidimensional Standard Edition, and someone I know has tested Power BI Desktop with the same configuration and he says it works (though I haven't tested it myself). Are you sure you're using the latest version of everything?

 

Regards,

 

Chris

View solution in original post

23 REPLIES 23
xxcorpxx
Advocate I
Advocate I

I'm trying to figure out how to use an SSAS based excel report on Powerbi.com.

 

There has to be a way to connect an SSAS connected Power Pivot file to Powerbi.com so that it refreshes and funtions properly.

I'd like to use the charts I have from Excel in Powerbi.com.

 

Any tips?

To be clear, you are using Power Pivot to import data from SSAS into Excel, then uploading that workbook to Power BI? As far as I know, this should work. I guess you've seen these pages, and the links to the troubleshooting tips?

https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-excel-file-onedrive/

https://powerbi.microsoft.com/en-us/documentation/powerbi-service-excel-workbook-files/

 

Chris

 

 

@cwebb This is a cross post from this thread. It still isn't a supported feature to my knowledge.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

No - that thread is about an Excel PivotTable connected direct to SSAS, but the question here is about refreshing a Power Pivot model using SSAS as a data source which I think should work. But I asked for clarification on the details just to be sure.

 

Chris

@cwebb  Thank you for the reply.  I'll check on the version the user has.  I've had another odd occurence with importing, my measures are coming over as text fields.  Is there any way to fix that?

You can always change the data type of the columns as a new step in the query editor. The problem is that the same measure in SSAS can return numbers, text or dates, so I think Power BI may decide to import them as text just to be safe. That said I've just tested it and Power BI does set the correct data types for my measures.

 

I remember seeing an "Import measures as text" checkbox somewhere in Power BI or Power Query in the past but I can't find it now. However the option is still there in the M code. On the Source step for your query, do you see an expression like this in the Query Editor formula bar?

 

= AnalysisServices.Databases("localhost", [TypedMeasureColumns = false])

 

Does changing TypeMeasureColumns to true make a difference? For me, measures always seem to come out typed regardless of the setting but maybe there's something about your measures...

 

Chris

It actually says is set to true.  I looked again and it's not all of my measures.  There is one that is a percentage that comes over as text.  There might be a percentage sign in there that's throwing it off, I'll check.  Also, as to the above about not being able to connect live, The user does appear to have the correct SSAS version installed.  Any other ideas as to why it won't do a direct connect

Maybe you could try (re)installing the latest version of the OLEDB for OLAP provider? You can get it here:

https://www.microsoft.com/en-us/download/details.aspx?id=52676

 

Regards,

 

Chris

@cwebb  I'm very afraid the problem maybe the SQL is a standard edition, and it Power BI may require an Enterprise version.  Really hope that's not the case as it really de-values Power BI for SSAS cube connections.

It is the case that Power BI can only connect to SSAS Multidimensional Enterprise Edition (or BI Edition, though that has gone in SQL Server 2016):

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-ssas-multidimensional/

 

You can vote to try to have that changed here:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/11331360-connect-multidimensional...

 

Sorry...

 

Chris

 

That's too bad.  That takes away a huge part of the appeal.  A Microsoft forum probably isn't the best place to ask, but are you aware of any other front end reporting tools that can accomplish this?

Actually, I've just found out that you can use Power BI with SSAS 2016 Multidimensional Standard Edition too.

 

Are you asking about other front end tools for Analysis Services? There are still a few left out there: Pyramid Analytics, XLCubed, Panorama Necto and Targit.

 

Chris

@cwebb We just installed 2016 SQL Standard and it's not allowing a live connection.

Hmm, I was specifically told by someone senior at Microsoft that this would work. I'll ask again and get back to you. Are you using the very latest version of Power BI Desktop?

 

Chris

I've just had a second confirmation from Microsoft that DAX queries are supported in SSAS 2016 Multidimensional Standard Edition, and someone I know has tested Power BI Desktop with the same configuration and he says it works (though I haven't tested it myself). Are you sure you're using the latest version of everything?

 

Regards,

 

Chris

Got it working now.  The only issue I'm having now is we changed the name of the cube and the pbix desktop files that had the live connection won't let me in.  The server name is the same, but there's not a place to put in the new cube database name.  I don't know a way to change that, so I'll have to start all over.

This is marked as solved????

This was about not being able to import large cubes into Power BI.

There are no solutions in this thread.

What exactly are the limitations of power bi on size, cube or otherwise?

BarryB75
Helper I
Helper I

Actually, it finally quit spinning and I got an error that the operation has been cancelled and not enough memory avialable for the applicataoin and consider upgrading to the 64 bit (I'm on that) version.  Where exactly do I not have enough memory at?

Hi Barry,

 

It sounds like you are trying to import more data from SSAS than Power BI can handle - Power BI stores all of its data in-memory, and I guess you don't have enough memory on the machine you're developing on. You have three options:

1) Work on a different machine with more memory on

2) Import less data. The key thing here will be no to import columns of data unless you need them (eg columns containing calculated values should probably not be imported if you can recreate the calculations in DAX after the import), and especially avoid importing columns with a large number of distinct values in them.

3) By far the best option is not to import the data but use a Live connection back to SSAS - if you do this, then memory on the desktop is not an issue. Is there a good reason why you need to import the data?

 

Regards,

 

Chris

Chris,

 

Thank you for the reply.  What's odd is the data bases aren't that big, and shouldn't be taking up that much memory.  

 

Good point on number 2.  I have had some luck just picking and choosing and finding the data fields that hold things up.  I could use the DAX statement at the start of it to filter down to just get a year's worth, etc., but I'm not familiar with how to do that.

 

Connecting live is great and that is the preferred method, but right now, Power BI doesn't allow you to change very much when connecting live to an SSAS cube.  Even simple changes that can be done in Excel (like changing number formats, etc.) can not be done.  Also, you aren't able to add columns or measures.  Additionally, several of the visuals have more options open up (like averaging, summing, adding trend lines with inputted values (not pulled from fields).  

 

If I could get some more options, I'd always connect live and never download anything.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors