topic Re: Script that connects OLAP-cube from qlikview in QlikView

Script that connects OLAP-cube from qlikview

Mon, 11 Nov 2013 12:29:50 GMT

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

Re: Script that connects OLAP-cube from qlikview

kaushiknsolanki — Mon, 11 Nov 2013 12:37:50 GMT

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

Re: Script that connects OLAP-cube from qlikview

Mon, 11 Nov 2013 12:40:49 GMT

hi,

What would xyz refer to is it cube name?

Regards,

Prajna

Re: Script that connects OLAP-cube from qlikview

kaushiknsolanki — Mon, 11 Nov 2013 12:42:06 GMT

Hi,

Yes it should be cube name.

you can also use the schema_name.CubeName

Regards,

Kaushik Solanki

Re: Script that connects OLAP-cube from qlikview

Mon, 11 Nov 2013 12:44:50 GMT

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

Regards,

Prajna

Re: Script that connects OLAP-cube from qlikview

kaushiknsolanki — Mon, 11 Nov 2013 12:46:39 GMT

Hi,

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

Regards,

Kaushik Solanki

Re: Script that connects OLAP-cube from qlikview

Mon, 11 Nov 2013 12:53:59 GMT

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

Re: Script that connects OLAP-cube from qlikview

bruno_m_santos — Mon, 11 Nov 2013 12:58:26 GMT

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

Re: Script that connects OLAP-cube from qlikview

Mon, 11 Nov 2013 13:06:37 GMT

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

Re: Script that connects OLAP-cube from qlikview

bruno_m_santos — Mon, 11 Nov 2013 15:08:38 GMT

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.

Re: Script that connects OLAP-cube from qlikview

Thu, 06 Feb 2014 20:15:22 GMT

Perhaps NAVdiscovery (QlikView Toolbox for Dynamics NAV) can help you with your problems:

http://www.prisma-informatik.de/erp-blog/2014/01/qlikview-connector-fuer-microsoft-dynamics-nav/