topic Re: Connecting Qlikview to SSAS cube in QlikView

Connecting Qlikview to SSAS cube

Mon, 02 Jan 2012 15:04:58 GMT

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

Re: Connecting Qlikview to SSAS cube

Anonymous — Mon, 02 Jan 2012 16:26:28 GMT

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

Re: Connecting Qlikview to SSAS cube

luciancotea — Mon, 02 Jan 2012 16:34:05 GMT

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

Re: Connecting Qlikview to SSAS cube

Anonymous — Mon, 02 Jan 2012 18:43:41 GMT

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

Re: Connecting Qlikview to SSAS cube

Thu, 07 Feb 2013 14:11:04 GMT

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