Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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):
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!
Dear Sirs, did you get the good answer for your question ? I have also had the same question from my team.
Unfortunately not - I have not received any help on how to get this working.
Best of luck!
Maybe the information is already available as below ?
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
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.
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.
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
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
Thanks. I will try that. Is there any way to do a "live" connection to the SSAS model?
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