topic Re: Connect to MS analysis services cube to QlickView? in QlikView

Connect to MS analysis services cube to QlickView?

Tue, 20 Oct 2009 18:01:33 GMT

Hi All,

I want to fetch some data from the Microsoft sql server analysis services cube.

How to do that?

I am able to connect that ,but when i am selecting i am not getting any information.

Will someone please help me to fetch the data from cube in my QV file ?

Thanks & regards,

Mahasweta

Connect to MS analysis services cube to QlickView?

Tue, 20 Oct 2009 18:17:46 GMT

Hi ,

I am also looking out for solution reagrding query u ask. Most of them having view is to connect sql Database & pull the data (not from cube) Or design Cube like structure in QVD files.

For all ,

We are using third party cube (using login & password ) which we are currently accessing through Excel . I know i can prepare much better reports & that excrise last only one time , rather than refreshing pivots in excel & modify data further. We can able to connect those but can't extract any data from cube.

So i request Qlik team member to address the same .

Regards,

Bhushan N

Re: Connect to MS analysis services cube to QlickView?

Emmanuelle__Bustos — Fri, 25 Oct 2013 16:22:55 GMT

Hi bnirgude Mahasweta

Were you able to extract data from the cube of microsoft sql analysis services?

Re: Connect to MS analysis services cube to QlickView?

Josh_Good — Fri, 25 Oct 2013 16:34:08 GMT

I would recommend you consider going after the source data for the cubes. Cubes typically have preaggerated data in them which will limited the flexibility of your QV app. Also going after SSAS cubes will require an MDX query with can be a bit complicated to write. Here is how to do it if you still want to give it a try.

To connect to an Analysis Service cube you need to enable adhoc distributed queries (see the following link for more information):

http://tejasnshah.wordpress.com/2009/03/19/sql-server-how-to-enable-ad-hoc-distributed-queries/

Once you have set that up an MDX query similar to the following should work fine:

// Connection string to standard SQL Server instance (note initial catalog to SQL database not required)

CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=biserver;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=biserver;Use Encryption for Data=False;Tag with column collation when possible=False];

// Query to extract MDX as relational data set

SQL SELECT * FROM OPENROWSET

('MSOLAP.3',

'Provide=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=False;Data Source=biserver;Initial Catalog=Analysis Services Tutorial;',

'SELECT {[Measures].[Unit Price]} ON COLUMNS, {[Customer].[Customer].MEMBERS} ON ROWS FROM [Adventure Works DW]'

Reference:

http://community.qlik.com/message/43719#43719

http://community.qlik.com/message/81722#81722

Re: Connect to MS analysis services cube to QlickView?

sasikanth — Fri, 25 Oct 2013 16:43:07 GMT

MDX(multidimensional expressions ) used to pull the data from cubes ..

for reference MDX Query Fundamentals (Analysis Services)

sample Qurey:

SELECT column1,column2 on columns,

row1,row2 on rows from [cube-name];

Hope this may solve your problem

Regards

Sasi

Re: Connect to MS analysis services cube to QlickView?

Emmanuelle__Bustos — Fri, 25 Oct 2013 21:20:21 GMT

@Josh Good

Thank you in advance your help could lead me.

Regards!