Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

SSAS Cube Connection and MS Power Query

BI_Pleb
7 - Meteor

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

36 REPLIES 36
s_pichaipillai
12 - Quasar

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

 

  1. 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
7 - Meteor

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
12 - Quasar

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 :) 

MDX.PNG

 

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

MDX exe.PNG

 

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
5 - Atom

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

 

16-02-2016 2-38-14 PM.png

16-02-2016 2-38-45 PM.png

 

s_pichaipillai
12 - Quasar

Sorry aboyt that

i attached it now 

s_pichaipillai
12 - Quasar

@nilbot

 

attaching all Macro

please test it now and let me know if that works 

nilbot
5 - Atom

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

16-02-2016 3-11-21 PM.png16-02-2016 3-21-17 PM.png

s_pichaipillai
12 - Quasar

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
5 - Atom

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

 

16-02-2016 4-10-12 PM.png

Labels