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 🙂

    • Eduardo says:

      Will you please post an example of what you have done?

      • luciancotea says:

        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.

  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: