QlikSense connectivity to SSAS

[#1]

Hi,

I have been struggling to load data into QlikSense (v2.2) from a SQL Services Analysis cube. I'm wondering if anyone has success with this?

I've tried creating an OLE DB data connection, selecting one of the following drivers (against both SSAS 2008R2 and 2012):

• Microsoft OLE DB Provider for Analysis Services 10.0
• Microsoft OLE DB Provider for Analysis Services 11.0

The test connection succeeds in each case and I can load and select my 'database' - in this case a cube.

I am presented with a list of 'tables' in my cube and I can see the column names if I select a table from the list. However if I try to preview or load the data I get the following error:

Any suggestions on how I can get this to work?

Thanks!

[kclayton1]

Correct. The user wants to connect directly to the SSAS model as they do with Microsoft PowerBI.

Thanks.

Kevin

[#3]

Ok I finally got this to work, here are my notes:

I upgraded my client on the Qlik server with the latest / matching drivers that come with Sql Server Data Tools

https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt

Since I am using Integrated Security, I must remember to use the correct credentials in the correct context. This seems so obvious except, I use several accounts and not all of them are defined in my SSAS server. When this type of security error is raised it is not always obvious what the issue is.

Use the "Data Load Editor" to define the connection. At one point I had a connection string:

OLEDB CONNECT TO

If you try to edit this type of connection in the QMC, so type of internal parse error occurs. Eventually I was able to redefine my connect string as

OLEDB CONNECT TO

And things get better…

Finally by using the “Data Load Editor” you are forced to define your own tables. I must assume that the “Data Manager” is unable to generate the proper language to query SSAS, typically MDX.

So Finally I am able to run a DataLoad Script like…

LIB CONNECT TO 'SSAS';

:

SELECT

NON EMPTY { .[# of Pets in Pet Registry] } ON COLUMNS,

NON EMPTY { (.[Country Name].[Country Name].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION,

MEMBER_UNIQUE_NAME ON ROWS

FROM CELL PROPERTIES Value

————

I think you are asking is there a way to use the Web UI to do this and the answer I think is NO.

[kclayton1]

I believe I understand what you are saying.  I am able to query with in QlikView / QlikSense to pull data out using MDX.  Problem I am having is, I have to redefine all the measures built into the model since they are not stored in a table format that Qlik likes to consume.

[ma258827]

I have to setup MS OLE DB provider for Analysis Services 11.0, but i don't see it on my OLE DB list of connectors.

do i need to install it explicitly.  if yes, could you please share setup links.

[#6]

I remember installing some of the ms data tools, Have a look at this…

https://docs.microsoft.com/en-us/sql/analysis-services/instances/install-windows/install-analysis-services-data-providers-amo-adomd-net-msolap

[martinsahlin]

Hi,

Yes, we have developed a Qlik connector for MS SSAS Tabular Models 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