Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
rajtechnocraft
Contributor III
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
Master
Master

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

Not applicable

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
Partner - Creator III
Partner - Creator III

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

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
Partner - Specialist
Partner - Specialist

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
Partner - Creator III
Partner - Creator III

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
Partner - Specialist
Partner - Specialist

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

dvasseur
Partner - Creator III
Partner - Creator III

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

Emmanuelle__Bustos
Partner - Specialist
Partner - Specialist

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

Regards!