How to connect QlikView to MS Analysis Services Cube

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!

Advertisements
Tagged with: , , ,
Posted in Script
9 comments on “How to connect QlikView to MS Analysis Services Cube
  1. Prajna Alva says:

    hi,

    can u tell me what items,product and again product refer to?

  2. Prajna Alva says:

    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?

  3. Prajna Alva says:

    Hi,
    Thank you I got it 🙂

  4. yaya says:

    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?

  5. Zienab says:

    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?

  6. Pasquale says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: