Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Connecting Qlikview to SSAS cube

Has anyone worked on connecting Qlikview to an MS SSAS cube?

4 Replies
henrikmatz
Contributor II

Re: Connecting Qlikview to SSAS cube

There is no connector as fare, maybe because a cube works on the file system and qlikview in memory.

So you need to load all contence of the cube into qlikview for each qlikview reload - and a cube is not design for that.

I suggest you identify the sql's that is used to create the cube and reuse them in qlikview.

Henrik

luciancotea
Valued Contributor

Re: Connecting Qlikview to SSAS cube

I know I did it once, here some code (can't test it):



ODBC CONNECT TO CUBE;


Cube:

LOAD `[Measures].[d Price]` as Pret, `[Items].[Produs].[Produs].[MEMBER_CAPTION]` as Produs;

SQL SELECT * FROM OpenQuery(LINKED_OLAP,'SELECT {[Measures].[d Price]} ON COLUMNS, {[Produs].[Produs].Members} ON ROWS FROM [SFS_CUBE]');


/* Other version -------------------------------------------------

SELECT * FROM OPENROWSET

('MSOLAP.4',

'Provide=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=False;Data Source=LUCIAN-PC;Initial Catalog=Analysis Services Project1;',

'SELECT {[Measures].[d Price]} ON COLUMNS, {[Produs].[Produs].Members} ON ROWS FROM [SFS_CUBE]'

)

------------------------------------------------- */



/* Requirements -------------------------------------------------

// 1. In SSMS, expand the folder ‘providers’ below the folder ‘linked server’, click the provider ‘MSOLAP’, and then select the option ‘Allow InProcess’.

// 2. Ad-hoc queries


sp_configure

sp_configure 'show advanced options',1

reconfigure

sp_configure

sp_configure 'Ad Hoc Distributed Queries',1

reconfigure


// Linked OLAP server


EXEC sp_addlinkedserver

@server='LINKED_OLAP', -- local SQL name given to the linked server

@srvproduct='', -- not used

@provider='MSOLAP', -- OLE DB provider (the .4 means the SQL2K8 version)

@datasrc='localhost', -- analysis server name (machine name)

@catalog='SFS_CUBE' -- default catalog/database

henrikmatz
Contributor II

Re: Connecting Qlikview to SSAS cube

I guess we can conclude that you in both ways will end up writing a lot of Qlikview load scripting.

If you goal is to have a full copy of the SSAS cube in Qlikview, I would connect directly to a relational database and not the SSAS cube.

Henrik

Not applicable

Re: Connecting Qlikview to SSAS cube

Not necessary.

Try This Great Tool !!!!

"O-delta™ by DataForce Ltd. is the most comprehensive Solution today for companies to dramatically cut down Time & Money of Connection, Migration & Integration of OLAP (mdx.) data cubes to any QlikView’s models, from months/ weeks to days/ hours !!! Http://www.dataforce-solutions.com The Benefits are: • Minimum need of skills/ knowledge of the current OLAP cubes’ structure • Leverages OLAP cubes development investments • Intuitive, integrated & consolidated view of all BI information as a front end • Mobile availability of old and new BI applications"

http://www.youtube.com/watch?feature=player_embedded&v=dlb6nf0w6TQ

Community Browser