Forum Discussion
Importing in from SSAS Cube
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:
Sorry...
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
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.
I agree, it would be great to have more flexibility when connecting to SSAS using a Live connection - especially with adding new calculated measures. Hopefully that will be added one day...
Remember that SSAS also compresses data, and that when measure calculations are involved they are calculated at query time so they don't contribute to the size of the cube. You might not be able to avoid writing some MDX or DAX to filter the data before it gets downloaded.
Chris
Thanks again Chris,
One last question if you don't mind, I have a user that is trying to connect to a new server and it will let him import, but not connect live. When he fills in the server name, it flips the option automatically to import data. Changing it back to connect live and clicking ok doesn't work.
any idea what might be causing that?
It could be that the server has an older version of SSAS installed. You need to have SSAS 2012 SP1 CU4 or later for a live connection:
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-ssas-multidimensional/
Regards,
Chris