topic Re: QlikSense connectivity to SSAS in Connectivity & Data Prep

QlikSense connectivity to SSAS

Thu, 23 Jun 2016 16:32:49 GMT

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!

Re: QlikSense connectivity to SSAS

Fri, 15 Jul 2016 13:34:33 GMT

Dear Sirs, did you get the good answer for your question ? I have also had the same question from my team.

Re: QlikSense connectivity to SSAS

Fri, 15 Jul 2016 13:42:00 GMT

Unfortunately not - I have not received any help on how to get this working.

Best of luck!

Re: QlikSense connectivity to SSAS

Fri, 15 Jul 2016 20:49:52 GMT

Maybe the information is already available as below ?

Re: Linking to a cube

You can use the same script from QlikView in Qlik Sense and easily convert it Qlik Sense Desktop - Converting a QlikView Data Model (video)

Hi Gerald Britt,

Thanks for posting in the Qlik Community. Can you be more specific with your question?

What type of cube would you like to connect to?

We have custom connectors for Essbase, and you can use OLE DB to connect to MS SSAS. With MS SSAS you would need to issue the appropriate MDX statements to pull back the data. So please let us know and we can further help you.

Please mark the appropriate replies as helpful / correct so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

Re: QlikSense connectivity to SSAS

greg_dales — Wed, 09 Nov 2016 07:23:01 GMT

Hi Colette,

I believe the "select data" aspect won't work as it will insert a SQL statement. I have successfully done this by using MDX rather than SQL.

Re: QlikSense connectivity to SSAS

Mon, 17 Apr 2017 21:04:15 GMT

I too have been struggling with this today. Using this type of connection string and the latest OLEDB Driver I can connect. Woohoo. But now I am stuck retrieving an initial catalog. So sad.

OLEDB CONNECT TO [Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XYZ;Data Source=10.1.20.200;Location=TABULAR]

Has anyone actually been able to get this working for real.

Re: QlikSense connectivity to SSAS

kclayton1 — Tue, 25 Apr 2017 21:33:09 GMT

I am having a similar issue. I can connect through OLE DB - Microsoft OLE DB Provider for Analysis Services, but I can only see the data tables that exist, not any of the measures built in the model. Has anyone figured out how to see the measures from the SSAS model?

Thanks.

Kevin

Re: QlikSense connectivity to SSAS

rodjager — Tue, 25 Apr 2017 21:49:39 GMT

Hi Kevin,

Yes, I can confirm this is possible though the query needs to be performed using the openrowset functionality which requires your SQL server environment to be enabled for ad hoc queries.

Once ad hoc queries are enabled you can retrieve data using standard MDX as per the following sample:

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

Hope this helps.

Rod

Re: QlikSense connectivity to SSAS

kclayton1 — Tue, 25 Apr 2017 22:21:43 GMT

Thanks. I will try that. Is there any way to do a "live" connection to the SSAS model?

Re: QlikSense connectivity to SSAS

rodjager — Tue, 25 Apr 2017 22:43:32 GMT

Hi Kevin,

Not sure what you mean by live? If you mean directly hitting SSAS instead of via the ad hoc query method then I don't know if that is possible.

Rod

Re: QlikSense connectivity to SSAS

kclayton1 — Tue, 25 Apr 2017 23:56:02 GMT

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

Thanks.

Kevin

Re: QlikSense connectivity to SSAS

Wed, 26 Apr 2017 02:07:40 GMT

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.

Re: QlikSense connectivity to SSAS

kclayton1 — Thu, 27 Apr 2017 13:12:20 GMT

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.

Re: QlikSense connectivity to SSAS

ma258827 — Thu, 07 Sep 2017 15:18:40 GMT

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.

Re: QlikSense connectivity to SSAS

Thu, 07 Sep 2017 15:25:51 GMT

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

Re: QlikSense connectivity to SSAS

martinsahlin — Wed, 11 Dec 2019 15:45:43 GMT

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