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]

everything looks good except the path of EXE

can you provide the native path instead UNC path i.e "\\ay3nas01\SSAS\SSAS.exe" to "D:\folder\ssas.exe"

i suspect this could be an issue 

[s_pichaipillai]

also, try to run the below command in command prompt window

"\\ay3nas01\SSAS\SSAS.exe" "sg3server" "AuBIx" "SELECT NON EMPTY { [Measures].[SLS] } ON COLUMNS, NON EMPTY { ([DIM TM].[YEAR].[YEAR].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [AuBIx]" "C:\Users\C094783\AppData\Local\Temp\Engine_5700_ba9a693949ee4cb5bb1a8f26b30ba493_\\"  

send me that error as well 

[nilbot]

@s_pichaipillai

Bingo!

Got it working. Running the command in the prompt window threw up an error regarding Microsoft.AnalysisServices.AdomdClient version 11

I checked and I only had version 10 (C:\Windows\assembly).

I was able to find version 11 through the MS SQL Server 2012 feature pack where I was able to download and install ADOMD.NET

Once I had upgraded this, the workflow executed without issue.

Thanks again for the troubleshooting and the tool, this will save a huge amount of time/effort.

This is my first experience in the Alteryx community and a very positive one. Cheers

[s_pichaipillai]

@nilbot

Excellent, Glad to see its working :)

I was about to ask the version of SQL server you are using as I have developed this code using ADOMD 11 client

I will also create the version for both and document it

[BI_Pleb]

Hey, I'm getting an error code 

"Error: MDX (5): Tool #11: The external program "%temp%\mdxQuery.bat" returned an error code: -532462766"

Not sure if this an issue with the MDX i'm running or a  config issue

[s_pichaipillai]

Dave

can you send me the workflow

also go to the tem folder and copy and paste the mdxquery batch command 

[BI_Pleb]

After line breaks in MDX this worked a treat :) thanks a lot for your support on this!

[s_pichaipillai]

Excellent, Glad to see its working for both of you :)

Happy Data Blending!!!

Thanks

Saravanan

[BI_Pleb]

One challange i'm finding is where the output from MDX has comma in the text for example "Korea, North", which is causing columns to be split.

Is there away of adding text delimitor to output?

[s_pichaipillai]

good to see there is an issue :)

i will take a look and send you the updated soon