ms olap (ssas) connection - Qlik Community

felcar2013 · ‎2013-11-14

hi

i need to connect to a ms olap cube which is located in an external database (different location, different company). we need to get social media data into a qlikview application. Any connection to the database source will not be possible.

we do not have ms sql installed on the qv server and cannot do that, but we have installed it with SSAS on another server

what would be the best way to get the data into qlikview? and how can i do that?

thanks

felipe

Report Inappropriate Content · ‎2013-11-14

First, the best option to work with cubes is loading the same information from the datawarehouse they are using to build the cubes, because the cubes engines (like SSAS) are not very good to extract information because of its bad performance and different levels of aggregations and details could not be in the cube.

And said this, if you have to get the data from the SSAS cube, the best method is using the OLEDB connector and writing the MDX sentence in the QlikView script instead of SQL, in this case you cannot use the SQL assistant, better you go to Management Studio and build the MDX query and then copy and paste into the QV script.

In your case, if you do not have access to the SSAS server then you have to export and import the cube database into your accesible server, or generate a .CUB file from the SSAS server and build the OLEDB connection to that file (the performance could be worse when working with CUB files).

felcar2013 · ‎2013-11-15

Dear Juan

thanks for your help. We have on another cluster MS SQL Server installed, with SSAS and BI Development Studio. Can I import the external source cube (from different company, location and server) into our cluster/server first and then create the OLEDB connection to Qlikview?

felipe

Report Inappropriate Content · ‎2013-11-17

Yes you can. The best method is doing a SSAS backup in the remote server, copying the file into your internal server and restore the OLAP database, you will have the same cubes with the same data (if there is no ROLAP nor HOLAP data, that resides in other tables). The only thing you won't be able to do is refresh the cube data, so you will have to restore new backups to get updated data.

There is another method, by replicating the databases, but both servers should be connected.

As I told you before, I recommend you try first to load the detailed data into QlikView instead of the cube data. SSAS usually have performance degradation as you start querying many records.

luciancotea · ‎2013-11-17

How to connect QlikView to MS Analysis Services Cube

felcar2013 · ‎2013-11-21

Thanks both for the link and the method. We are checking the replication with the external data provider

Report Inappropriate Content · ‎2013-11-21

Hi,

You may also look the MDX Query Generator for QlikView which can connect to the SSAS and create MDX queries on the cubes. This script can be pasted back in the edit script and can be reloaded.

Thanks,

Sai