To get data from a MS cube in QlikView, we need to use the MS Data Engine as a bridge. So, the data will follow this path: MS Analysis Services -> MS Database Engine -> QlikView.
We link the first 2 by executing this query:
EXEC sp_addlinkedserver @server='LINKED_OLAP', -- local SQL name given to the linked server @srvproduct='', -- not used @provider='MSOLAP.4', -- OLE DB provider (the .4 means the SQL2K8 version) @datasrc='localhost', -- analysis server name (machine name) @catalog='MYCUBE' -- default catalog/database
If the command was successful we should see the “LINKED_OLAP” server in “Server objects” -> “Linked Servers” menu of the Server Management Studio.
Then we define a connection to MS Database Engine using ODBC/OLE DB.
Now, in QlikView:
ODBC CONNECT TO MSSQLServer;
CubeData:
LOAD `[Measures].[Qty]` as Qty, `[Items].[Product].[Product].[MEMBER_CAPTION]` as Product;
SQL SELECT * FROM OpenQuery(LINKED_OLAP,'SELECT {[Measures].[Qty]} ON COLUMNS, {[Items].[Product].Members} ON ROWS FROM [CUBENAME]');
Happy Qliking!
Lucian Cotea 
hi,
can u tell me what items,product and again product refer to?
We load:
`[Measures].[Qty]` as Qty
and
`[Items].[Product].[Product].[MEMBER_CAPTION]` as Product
This is how you receive the field names from OpenQuery.
hi,I meant if I had to edit the script what should I replace for
{[Measures].[Qty]} — is this measures.factvalue in my cube
{[Items].[Product].Members}– what is item..product and member? is this one of the dimension
CUBENAME– should I replace this with my cube name?
Hi,
Thank you I got it 🙂
Will you please post an example of what you have done?
That’s all you have to do:link the servers, add an ODBC connection then use OpenQuery() to query the data. The trick is not in QlikView.
In QlikView, you only need to adapt the query to your CUBE.
Hello, How do we define the connection ODBC to MS Database Engine? Is it the same as ODBC to SQL server? what shall I enter as server name?
Hello
If i install qlik sense on my local computer & i want to connect to a cube in a remote server.
Could you guide me, please?
Hi, do you know how connect QV to a SSAS Tabular model by using the Direct Discovery feature? I’ve tried successfully to use a DAX query into a QV script in the traditional manner but I don’t know the right syntax to use the Direct Discovery feature to query a Tabular model. Any helps to me, please? Thanks