Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Certification Exam Sale - 40% off. Register by June 30, 2020. GET DETAILS
Highlighted
Not applicable

Script that connects OLAP-cube from qlikview

Hi All,

Please..

Can anyone suggest me the script to connect to OLAP cube.. I found below.. but m confused with name qualifier...that is bolded.

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]');

Regards,

Prajna

10 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Script that connects OLAP-cube from qlikview

Hi,

     As far as i know, nothing gr8 needs to be done.

     Just try simple sql select statement to load data form cube to qlikview.

     Something like.

     Sql Select * from xyz;

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Not applicable

Re: Script that connects OLAP-cube from qlikview

hi,

What would xyz refer to is it cube name?

Regards,

Prajna

Highlighted
MVP & Luminary
MVP & Luminary

Re: Script that connects OLAP-cube from qlikview

Hi,

     Yes it should be cube name.

     you can also use the schema_name.CubeName

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Not applicable

Re: Script that connects OLAP-cube from qlikview

it doesn't work.. can u tell me what should be the connection name?

Regards,

Prajna

Highlighted
MVP & Luminary
MVP & Luminary

Re: Script that connects OLAP-cube from qlikview

Hi,

     You first need to do connection to database, using either ODBC connection or OLEDB connection,

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Not applicable

Re: Script that connects OLAP-cube from qlikview

yeah..  connection to analysis service? as the cube becomes invalid object if I give select * from cubename.

Am new to DW as well.. can u guide me what should be connection ? we need to provide server name and then credentials rite? what should I give?

Regards,

Prajna

Highlighted
Partner
Partner

Re: Script that connects OLAP-cube from qlikview

I give you a sample that works for me....

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=qv;Data Source=YOURSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XXXX;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is XXXX);

FACT:
LOAD "[Account].[Account].[Account].[MEMBER_CAPTION]" as Account,
"[NAV DEPARTAMENTO].[NAV DEPARTAMENTO].[NAV DEPARTAMENTO].[MEMBER_CAPTION]" as Depart,
"[Measures].[General Ledger Real]" as Amount
;


SQL SELECT * FROM openrowset('MSOLAP','DATA SOURCE=YOURSERVER;Initial Catalog=XPTO',
'SELECT { [Measures].[General Ledger Real] } DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON COLUMNS, NON EMPTY
CrossJoin({[Account].[Account].Children},
{[NAV DEPARTAMENTO].[NAV DEPARTAMENTO].children})
on Rows
FROM [Dynamics NAV]');

Highlighted
Not applicable

Re: Script that connects OLAP-cube from qlikview

can u tell me
LOAD "[Account].[Account].[Account].[MEMBER_CAPTION]" as Account,
"[NAV DEPARTAMENTO].[NAV DEPARTAMENTO].[NAV DEPARTAMENTO].[MEMBER_CAPTION]" as Depart,
"[Measures].[General Ledger Real]" as Amount

in the above code what is "[Account].[Account].[Account].[MEMBER_CAPTION]"

what is "[Account] that is repeated thrice and

what is MEMBER_CAPTION?

In code which one  is fact and which is dimension here?

Regards,

Prajna

Partner
Partner

Re: Script that connects OLAP-cube from qlikview

In the Cube the column is repeated. Member caption is the name.

You can build your query in excel, using the connector an then copy to Qlikview.

This Query is for an fact table. Regard that I've an Amount Column.