4 Replies Latest reply: Feb 7, 2013 9:11 AM by Shauli Bergman RSS

    Connecting Qlikview to SSAS cube

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

        • Re: Connecting Qlikview to SSAS cube
          Henrik Matz

          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
            Lucian Cotea

            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
              Henrik Matz

              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