Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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.

1 Solution

Accepted Solutions
Not applicable

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

1 Reply
Not applicable

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

Community Browser