Script that connects OLAP-cube from qlikview

[#1]

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

[kaushiknsolanki]

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

[#3]

hi,

What would xyz refer to is it cube name?

Regards,

Prajna

[kaushiknsolanki]

Hi,

     Yes it should be cube name.

     you can also use the schema_name.CubeName

Regards,

Kaushik Solanki

[#5]

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

Regards,

Prajna

[kaushiknsolanki]

Hi,

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

Regards,

Kaushik Solanki

[#7]

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

[bruno_m_santos]

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

[#9]

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

[bruno_m_santos]

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.