1 Reply Latest reply: Aug 20, 2013 9:48 AM by Joao Manuel Da Silva Fontes Coelho RSS

    Fetch data from Microsoft Analysis Services cube with OLE DB connection

      Hello,

       

       

      I'am trying to fetch some data from a Microsoft Analysis Services cube with OLE DB connection using the usual process.

      I am hable to connect and select the tables i want, but using simple SQL selects doesn't work - error: "the cube does not exist or it is not processed"

       

      Then i saw some instructions to apply OpenRowSet() or QueryOpen() using a MDX query, and I tried that, but it doesnt return results or any error message. The script just does the connection but doesn't load anything, and no errors at all!

       

      I think the problem may be in the MDX query - I am just starting to learn it - but i tried to call some 'fields' ON COLUMNS and ON ROWS without success.

       

      I saw some examples, like this one:

      SQL SELECT * FROM OPENROWSET
      ('MSOLAP.3','Provide=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=False;Data Source=My_AS_Server;Initial Catalog=Adventure Works DW;',
      'SELECT {[Measures].[Sales Amount]} ON COLUMNS, {[Product].[Product].Members} ON ROWS FROM [Adventure Works]');

       

      Any help? Any way to debug the query I am using? I dont have direct access to the database, so i can't extract data or use ODBC connection...

       

       

      NOTE: with EXCEL I can get the data I want using the same OLE DB connection without any problem.

        • Re: Fetch data from Microsoft Analysis Services cube with OLE DB connection

          The solution I found was to use an OLEDB connection and apply MDX (Multidimensional Expressions) queries.

           

          // QlikView Script – Configure OLEDB connection

          OLEDB CONNECT32 TO

          [Provider=MSOLAP.5;

          Persist Security Info=True;

          User ID=USER_ID;

          Initial Catalog=CATALOG_NAME;

          Data Source= XXX.XXX.XXX.XXX;

          Location=XXX.XXX.XXX.XXX]

          (XPassword is ZLCRZSBOBDbEDYAGYRBA);



          There are some helpful queries to understand the cubes structure:

           

          List all cubes

          CUBOS:
          Load *;
          SELECT [CATALOG_NAME] AS [DATABASE],CUBE_CAPTION AS [CUBE/PERSPECTIVE],BASE_CUBE_NAME
          FROM $system.MDSchema_Cubes
          WHERE CUBE_SOURCE=1

           

          List all dimensions and attributes

          SELECT [CATALOG_NAME] as [DATABASE],

          CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],

          HIERARCHY_DISPLAY_FOLDER AS [FOLDER], HIERARCHY_CAPTION AS [DIMENSION ATTRIBUTE],

          HIERARCHY_IS_VISIBLE AS [VISIBLE]

          FROM $system.MDSchema_hierarchies

          WHERE CUBE_NAME  ='Adventure Works'

          AND HIERARCHY_ORIGIN=2

          ORDER BY [DIMENSION_UNIQUE_NAME]

           

           

          All Attributes with key and name columns

          SELECT [CATALOG_NAME] as [DATABASE],

                CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],

                LEVEL_CAPTION AS [ATTRIBUTE],

                [LEVEL_NAME_SQL_COLUMN_NAME] AS [ATTRIBUTE_NAME_SQL_COLUMN_NAME],

                [LEVEL_KEY_SQL_COLUMN_NAME] AS [ATTRIBUTE_KEY_SQL_COLUMN_NAME]

          FROM $system.MDSchema_levels

          WHERE CUBE_NAME ='Adventure Works'

          AND level_origin=2

          AND LEVEL_NAME <> '(All)'

          order by [DIMENSION_UNIQUE_NAME]

           

           

          All Levels of Hierarchies (user-defined)

          SELECT [CATALOG_NAME] as [DATABASE],

              CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],

              [HIERARCHY_UNIQUE_NAME] AS [HIERARCHY],

              LEVEL_CAPTION AS [LEVEL],

              [LEVEL_NAME],

              [LEVEL_NUMBER] AS [LEVEL NUMBER],

              [LEVEL_NAME_SQL_COLUMN_NAME] AS [NAME_COLUMN],

              [LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME] AS [UNIQUE_NAME_COLUMN],

              [LEVEL_KEY_SQL_COLUMN_NAME] AS [KEY_COLUMN]

          FROM $system.MDSchema_levels

          WHERE CUBE_NAME ='Adventure Works'

          AND level_origin=1

          order by [DIMENSION_UNIQUE_NAME]

           

           

          All Levels of Hierarchies (Parent-Child)

          SELECT [CATALOG_NAME] as [DATABASE],

              CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],

              [HIERARCHY_UNIQUE_NAME] AS [HIERARCHY],

              LEVEL_CAPTION AS [LEVEL],

              [LEVEL_NAME],

              [LEVEL_NUMBER] AS [LEVEL NUMBER],

              [LEVEL_NAME_SQL_COLUMN_NAME] AS [NAME_COLUMN],

              [LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME] AS [UNIQUE_NAME_COLUMN],

              [LEVEL_KEY_SQL_COLUMN_NAME] AS [KEY_COLUMN]

          FROM $system.MDSchema_levels

          WHERE CUBE_NAME ='Adventure Works'

          AND LEVEL_ORIGIN=3

          order by [DIMENSION_UNIQUE_NAME]

           

           

          All Measures

          SELECT [CATALOG_NAME] as [DATABASE],

              CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE],

              [MEASURE_IS_VISIBLE]

          FROM $SYSTEM.MDSCHEMA_MEASURES

          WHERE CUBE_NAME ='Adventure Works'

          ORDER BY [MEASUREGROUP_NAME]

           

           

          QlikView should be able to natively read and integrate with all 3 types of Microsoft Datacubes.

          Vote for it: http://community.qlik.com/ideas/1872