Fetch data from Microsoft Analysis Services cube with OLE DB connection

[#1]

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.

[#2 · Accepted solution]

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

[srinivasa_mutha_k_45]

Hi ,

I am trying to connect MS SSAS cube through Qlikview. I am following exact process you mentioned over here. but i am not able to connect the Cube.

I am getting OLEDB error.

i am mentioning my cube name in openquery with details required in server portion. but i am not able to crack this one.

Could you please give an example which give the information on loading the whole cube (dimensions and measures) data?

It will help me alot.

Thanks in Advance

[SahrierParvez]

Hi,

Will you please help me. i can connect but when i load data then script error is showing.I am attached 2 files for your help.

Thanks

[srinivasa_mutha_k_45]

IF you are writing simple SQL queries then it wont work, we need to write MDX qureis to load the dat while connecting to SSAS cube. You can easily get the MDX queries in SSAS.

Note: No need to write the open query also. A simple MDX query will work

[martinsahlin]

Hi,

we have developed a Qlik connector for MS SSAS Tabular Model and Multidimensional cubes.

You can read about it on Qlik Market: https://market.qlik.com/solutions/Cube_Connector_for_Multidimensional_&_Tabular_SSAS_Models

And also view it here: https://www.youtube.com/watch?v=DRTUqxUk7-E

Please feel free to contact me at martin.sahlin@stretch.dk if you'd like to try the connector with a trial license.

Kind regards

/Martin

[Raptor69]

Hello Martin ,

the link is not existing anymore , where could i find the article / connector ??

Kind regards, Erik Sagan

[martinsahlin]

Dear Erik,

Please find the correct link here: https://info.stretch.se/qlik-sense-connect-ssas-azure-cube

Kind regards
/Martin