locked
Process SSAS using Power Query server side RRS feed

  • Question

  • I'm currently looking at the PowerQuery add-in in excel, and PowerBI Desktop and trying to evaluate the most suitable tool for us to use.

    I've found that in Power BI Desktop, you can connect to an SSAS database with the option of 'Connect Live' (or Import data) and all processing seems to be done on the server.  When I use Power Query Excel (2010) add-in, connecting to an SSAS service, there's no option to 'Connect Live' and the only action results in importing data to be processed client side. 

    We're dealing with a large, complex data set, and want to use server side processing for this, but we want to keep it on site. While Power BI would allow us to do this, the only way to publish seems to be to the MS BI Cloud. With Excel add-in, we're unable to use server side processing, but can keep what we do on site.

    Is this down to the version of Excel we're using (2010), or am I missing another way of doing things?

    Monday, April 18, 2016 11:14 AM

Answers

  • What features of Power Query are you wanting to use? Because even in Power BI Desktop, using Live Connect means you can't use the PQ features.

    Ehren

    Thursday, April 21, 2016 6:30 PM
  • Hi Chris. You may want to look into using Power BI. When connecting live to an AS server, it should provide more flexibility for filtering etc. that what you're seeing in Excel. You could also look into allowing the cloud service to access your data (which would still be kept on prem) via the Power BI Enterprise Gateway. If you don't want the data in the cloud at all, perhaps you could share Power BI Desktop files locally.

    Ehren

    Wednesday, April 27, 2016 8:08 PM

All replies

  • Hi, ChrisOnesBroken

    This is the forum to discuss questions and feedback for Excel for Developers, I'll move your question to the Technet forum for Power Query

    https://social.technet.microsoft.com/Forums/en-us/home?forum=powerquery

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, 
    and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.
     
    Thanks for your understanding.
    Tuesday, April 19, 2016 7:04 AM
  • Hi Chris,

    Have you considered querying the data live from Excel (without Power Query)?

    Ehren

    Tuesday, April 19, 2016 7:22 PM
  • Ehren,

    Thanks for your reply.  We've looked at this option, and while its lightening quick, it doesn't allow us to do what we want, which are some of the features available with Power Query.

    Regards

    Chris

    Thursday, April 21, 2016 9:24 AM
  • What features of Power Query are you wanting to use? Because even in Power BI Desktop, using Live Connect means you can't use the PQ features.

    Ehren

    Thursday, April 21, 2016 6:30 PM
  • I haven’t had sufficient experience with the different excel/power/query tools, but limitations I did find with whatever it was I played with (the excel link from the BI model I built) included:

    • Inability to retrieve data between date parameters – you could click select all or each date one at a time in the dropdown, but there was no option to type in the range required
    • Inflexibility in report layouts – basic pivot tables only
    • Date-based queries – all records in last n days/months/years etc
    • Complex filters – AND is easy – but how to do an OR or combine various AND, OR and NOT NULL etc
    • Counting things like enquiry loglines where there’s a pattern match. Fell over instantly.
    Wednesday, April 27, 2016 2:08 PM
  • Hi Chris. You may want to look into using Power BI. When connecting live to an AS server, it should provide more flexibility for filtering etc. that what you're seeing in Excel. You could also look into allowing the cloud service to access your data (which would still be kept on prem) via the Power BI Enterprise Gateway. If you don't want the data in the cloud at all, perhaps you could share Power BI Desktop files locally.

    Ehren

    Wednesday, April 27, 2016 8:08 PM