6 Replies Latest reply: Aug 29, 2013 4:59 PM by Vinodkumar Dhanabalan RSS

    Pull data from an Analysis Services cube

    jlynn

      Hi..

      I would like to report on data straight from our MS Analysis Services cubes. I have seen posts on this site suggesting exporting Fact and Dimension tables to CSV, importing to QVD and then creating routines to maintain data freshness. Also suggestions on creating connection and MDX to get data set to be used in QVD. A lot of additional legwork which seems counterproductive to Cube build process.

      Is it possible to view AS cubed data in Qlikview as you would do in other tools i.e excel, proclarity.

      responces much appreciated.

      J

       

       

       

       

        • Pull data from an Analysis Services cube
          Tom Mackay

          There is no direct support for building or executing MDX-queries in QlikView. By using openrowset() in SQL, it is possible to encapsulate the MDX-query in a standard SELECT statement. In this example Microsoft OLEDB for Olap Services is used to query Analyis Services:


          SQL SELECT * FROM
          openrowset('MSOLAP.3','DATA SOURCE=localhost;Initial Catalog=Adventure Works DW',
          'SELECT { [Measures].[Sales Amount], [Measures].[Tax Amount] } ON COLUMNS,
          { [Date].[Fiscal].[Fiscal Year].&[2002], [Date].[Fiscal].[Fiscal Year].&[2003] } ON ROWS
          FROM [Adventure Works] WHERE ( [Sales Territory].[Southwest] )')

          Note: Support for openrowset is disabled by default in SQL 2005 Server.

            • Pull data from an Analysis Services cube
              jlynn

              As I thought.. Thankyou

              • Pull data from an Analysis Services cube

                Hello Tom,

                I am trying to build MDX-queries to access our cubes in Analysis Services. We have enabled openrowset in SQL Server but still I am getting anerror when trying to connect. I hope you can help.

                Error: OLEDB connection failed

                Here is my script:

                CONNECT TO [Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=peb;Data Source=post-sql01;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error];


                SQL SELECT * FROM OPENROWSET
                ('MSOLAP.4',
                'Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=False;Data Source=post-sql01;Initial Catalog=peb;',
                'SELECT {[Measures].[Market Volume]} ON COLUMNS, {[Date].[By Calendar Period].[Month].MEMBERS} ON ROWS FROM [DW]')

                Sorry for replying on such an old post, but I performed a search on this topic and found this thread so i thought you may be the most helpful.

                Thanks

                Mike

                • Pull data from an Analysis Services cube
                  brijeshvaidya

                  thank you for your answer..

                   

                  it is solved... Yes

                  • Re: Pull data from an Analysis Services cube
                    Nandha Kumar
                    Hi TMY,
                    I have a MDX query as below. How can i use this in QV to get the corresponding fields to create charts?
                    SELECT
                    {[Measures].[USD],
                    [Measures].[USD - Fct Budget],
                    [Measures].[USD - Fct Forecast]

                    }

                    DIMENSION
                    PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY

                    CrossJoin(

                    {[Dim Account Main3].[Account Key].Children},

                    {[Dim Activity Main].[Activity Key].Children},

                    {[DIM COST CENTRE MAIN].[Cost Centre Key].Children})

                    ON ROWS

                    FROM [IB Test]
                    ;

                     

                    Thnx & Rgds,

                    Nandha

                      • Re: Re: Pull data from an Analysis Services cube

                        Hi Nandha,

                         

                        Where you able to connect the SSAS to qlikview? I heed some help on this.

                         

                        I am working on the connecting SSAS cube to Qlikview.

                        I took help of the community to execute the script. Most of the answers shows the adventure works sample query. Based on this here is my Facts and Dimensions.

                         

                        Measures:

                        Total Qty, TEM, Fact Sales Count

                         

                        Dimensions:

                        Dim Customer, Dim Time, Dim Product, DIM ALIGNMENT ZIP TERR

                         

                        The above is the structure of my cube. It is a star schema.

                         

                        Qlikview Script:

                         

                        OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CUBE NAME;Data Source=SERVER NAME(wher my cube is deployed);Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SERVER NAME (QV is residing);Use Encryption for Data=False;Tag with column collation when possible=False];

                         

                        SQL SELECT * FROM OPENROWSET

                        ('MSOLAP.5',

                        'Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=False;

                        Data Source=SERVER NAME(where my cube is deployed);Initial Catalog=CUBE NAME',

                        'SELECT {[Measures].[TOTAL QTY]} ON COLUMNS,

                        {[DIM PRODUCT].[PROD CD]} ON ROWS FROM [CUBE NAME]')

                         

                        Error:

                         

                        Connection failed:

                        SQLState: '42000'

                        SQL Server Error: 4060

                        Cannot open database "MEDINCQV" requested by the login. The login failed.

                         

                        1) How to rectify this error. Is there any problem with my mdx QV Script?

                        2) Do I have any access issue?

                        3) Do I have to do settings from SSAS also?

                         

                        NOTE: I do not want to use any third party application to achieve this.

                         

                        Thanks in Advance.