Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

QlikSense connectivity to SSAS

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:

Error.PNG

Any suggestions on how I can get this to work?

Thanks!

15 Replies
Not applicable
Author

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

Not applicable
Author

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

Best of luck!

Not applicable
Author

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

greg_dales
Partner
Partner

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.

Not applicable
Author

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.

kclayton1
Contributor III
Contributor III

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

rodjager
Partner
Partner

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

kclayton1
Contributor III
Contributor III

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

rodjager
Partner
Partner

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