Qlikview connectivity to MS BI OLAP CUBE

[rajtechnocraft]

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

[suniljain]

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

[#3]

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]

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

[#5]

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]

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]

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]

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

[dvasseur]

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

[Emmanuelle__Bustos]

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

Regards!