SSAS Cube Connection and MS Power Query

[BI_Pleb]

Morning,

I have a need to get data out of an SSAS cube into Alteryx, to blend without data source. There does not seem to be way of doing this directly, so have created an MS Power Query in Excel (effectively a refreshable table) which I refresh prior to running my alteryx model.

Two questions:

• Does anyone know of a direct way to connect to an SSAS cube into Alteryx?
• Does anyone know how to trigger the refresh of MS Power Query in Excel as part of the Alteryx work flow?

Cheers

Dave

[s_pichaipillai]

1. Unfortunately, you cannot connect to the SSAS Cube. I think it’s because of multidimensional and can directly be connected with any BI tools or Dashboard tools

Instead of MDX query, I thought I can connect by at least using SSAS DMV queries using SSAS OLEDB connection, it did connect but all resulted Nulls

 Ex. Query “select * from [Adventure Works].[$Product].[$Product] where Color = 'Red'”

Well, you can post this in Idea forum as a suggestion

2. Do you want to refresh the connection when you open the Power query?

refer the below

http://www.excel2013.info/power-query/automatic-update/

3. if  there is an Command line utility then you can use Run command tool to refresh it by ALteryx

Right now , i dont have any idea about that, if i find that i will update this post :)

[BI_Pleb]

Thanks for the feedback, so not just me having issue with SSAS, thats good to know :)

Tip on refresh when open will help save me a few secounds but still not allowing me to "Automate" the flow.

Lets see if others have idea of how to call the refresh of MS Power Query/excel from alteryx

[s_pichaipillai · Accepted solution]

Hi @BI_Pleb

I did write a Macro as a workaround for your SSAS Connection

I have developed a C# console command line utility which will accept, SSAS Servername,Databasename,MDX Query and path for result output

What you need to do is, for the macro , you need to provide the below inputs

1. SSAS Server Name

2.SSAS Database Name

3.MDX query (sorry about that, you need to wirte it :) 

4. you need to add the user constant for the path of the ssas.exe as below

Source code project and SSAS Exe available here

Note: the MDX query need to be single line , not with the new line . i will fix it when i get time :)

meantime, please try it and let me know your feedback 

Happy Data Blending!!!

Thanks

Saravanan

[nilbot]

Hi Saravanan,

I was hoping this was going to the answer to all my problems but get the following errors. Any help would be appreciated. Thanks

[s_pichaipillai]

Sorry aboyt that

i attached it now 

[s_pichaipillai]

@nilbot

attaching all Macro

please test it now and let me know if that works 

[nilbot]

Thanks for the quick reply. Although I am still having issues with Error code 1 or 255 returned now

[s_pichaipillai]

can you share a screenshot of the configuration you did for SSAS

also, go to the the alteyx temp path, default will be under (C:\ProgramData\Alteryx\Engine) and open the BAT file and paste the bat command it generated 

[nilbot]

mdxQuery.bat is attached which contains the SSAS config details.

The workflow starts and looks to be working until this...then I get the 255 error