SSAS Cube Connection and MS Power Query

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

Input

Output

Accepted answers

s_pichaipillai

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

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

QueryOLAPCube.yxzp

s_pichaipillai

Hi Dave , @nilbot

i fixed this issue . this is caused by the data comes with comma which i did not think

Download the updated SSAS.exe and replace with the OLD one

https://www.dropbox.com/s/rgfk4ffmhdoy0i8/SSAS.zip?dl=0

All comments

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

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703