topic Re: Qlikview connectivity to MS BI OLAP CUBE in QlikView

Qlikview connectivity to MS BI OLAP CUBE

rajtechnocraft — Fri, 20 May 2011 03:55:08 GMT

Dear Community members,

we are trying to connect qlikview desktop to MS bi olap cube .

we are able to see the cube database but we are not able to see dimensions and measures defined in the cube.

please help us to do this connectivity as we need this on urgent basis

we are using oledb connectivity for microsoft sql server analysis services.

Regards

arvind

Qlikview connectivity to MS BI OLAP CUBE

suniljain — Fri, 20 May 2011 11:44:51 GMT

Actually , I donot try on MS BI OLAP Cube, But it successfully working in case of SAP BW Cube.

Re: Qlikview connectivity to MS BI OLAP CUBE

Thu, 09 Jun 2011 07:10:44 GMT

Hi Arvind,

My understanding is it does not connect directly to MS SSAS Cubes. If you want to achieve this you can create MDX scripts that are stored on a SQL server (personal preference than inside QlikView) using a Stored Procedure or SQL View to bring the results into QlikView. The script requires distributed queries being enabled on the SQL server ( http://msdn.microsoft.com/en-us/library/ms187569.aspx).

The code used would be along the lines of this:

SELECT * FROM OPENROWSET

('MSOLAP.3',

'Provide=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=False;Data Source=MyServer;Initial Catalog=Adventure Works DW;',

'SELECT {[Measures].[Sales Amount]} ON COLUMNS, {[Product].[Product].Members} ON ROWS FROM [Adventure Works]'

);

Hope this helps,

Jon

Re: Qlikview connectivity to MS BI OLAP CUBE

dvasseur — Thu, 09 Jun 2011 21:19:50 GMT

You can do it directly within Qlikview but I think you will have to write the MDX queries yourself.

Here is an example:

OLEDB CONNECT TO [Provider=MSOLAP;Integrated Security=SSPI;Initial Catalog=Adventure Works DW 2008R2 SE;Data Source=localhost];

LOAD

"[Product].[Product].[Product].[MEMBER_CAPTION]" AS Produit,

"[Measures].[Date Key]" AS Date,

"[Measures].[Internet Sales Amount]" AS Sales

;

SQL

WITH

SET Axes AS CrossJoin([Product].[Product].[Product].Members, [Date].[Date].[Date].Members)

MEMBER [Measures].[Date Key] AS [Date].[Date].CurrentMember.Member_Key

SELECT

{[Measures].[Internet Sales Amount], [Measures].[Date Key]} oN 0,

NonEmpty(

{Axes}, {[Measures].[Internet Sales Amount]}

) ON 1

FROM [Adventure Works];

Re: Qlikview connectivity to MS BI OLAP CUBE

Wed, 27 Feb 2013 14:29:24 GMT

Did you check O-delta™?

"O-delta™ by DataForce Ltd. is the most comprehensive Solution today for companies to dramatically cut down Time & Money of Connection, Migration & Integration of OLAP (mdx.) data cubes to any QlikView’s models, from months/ weeks to days/ hours !!! Http://www.dataforce-solutions.com The Benefits are: • Minimum need of skills/ knowledge of the current OLAP cubes’ structure • Leverages OLAP cubes development investments • Intuitive, integrated & consolidated view of all BI information as a front end • Mobile availability of old and new BI applications"

http://www.youtube.com/watch?feature=player_embedded&v=dlb6nf0w6TQ

www.dataforce-solutions.com

Re: Qlikview connectivity to MS BI OLAP CUBE

Emmanuelle__Bustos — Fri, 25 Oct 2013 16:58:39 GMT

Hi David VASSEUR so the script you wrote down:

SQL

WITH

SET Axes AS CrossJoin([Product].[Product].[Product].Members, [Date].[Date].[Date].Members)

MEMBER [Measures].[Date Key] AS [Date].[Date].CurrentMember.Member_Key

SELECT

{[Measures].[Internet Sales Amount], [Measures].[Date Key]} oN 0,

NonEmpty(

{Axes}, {[Measures].[Internet Sales Amount]}

) ON 1

FROM [Adventure Works];

It's already using AMDX script?

Re: Qlikview connectivity to MS BI OLAP CUBE

dvasseur — Mon, 28 Oct 2013 19:07:49 GMT

What do you mean about "using AMDX script" ? this query is an MDX query, like SQL but the language used is MDX for Analysis Services

Re: Qlikview connectivity to MS BI OLAP CUBE

Emmanuelle__Bustos — Mon, 28 Oct 2013 19:14:07 GMT

i meant MDX query or script sorry, so this is already an MDX query that will fetch data from the cube right?

Re: Qlikview connectivity to MS BI OLAP CUBE

dvasseur — Mon, 28 Oct 2013 20:16:07 GMT

Right! Writing MDX queries from scratch is not easy, most tools generate them automatically...

Re: Qlikview connectivity to MS BI OLAP CUBE

Emmanuelle__Bustos — Mon, 28 Oct 2013 22:56:43 GMT

Ok, i been checkin and think i got enough to work with this thank so much!

Regards!