Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rajtechnocraft
New Contributor III

Qlikview connectivity to MS BI OLAP CUBE

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

9 Replies
suniljain
Honored Contributor

Qlikview connectivity to MS BI OLAP CUBE

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

Not applicable

Re: Qlikview connectivity to MS BI OLAP CUBE

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

dvasseur
Contributor II

Re: Qlikview connectivity to MS BI OLAP CUBE

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

Not applicable

Re: Qlikview connectivity to MS BI OLAP CUBE

Hi

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

Emmanuelle-Bustos
Valued Contributor

Re: Qlikview connectivity to MS BI OLAP CUBE

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?

dvasseur
Contributor II

Re: Qlikview connectivity to MS BI OLAP CUBE

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

Emmanuelle-Bustos
Valued Contributor

Re: Qlikview connectivity to MS BI OLAP CUBE

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

dvasseur
Contributor II

Re: Qlikview connectivity to MS BI OLAP CUBE

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

Emmanuelle-Bustos
Valued Contributor

Re: Qlikview connectivity to MS BI OLAP CUBE

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

Regards!

Community Browser