Pull data from an Analysis Services cube

[#1]

Hi..

I would like to report on data straight from our MS Analysis Services cubes. I have seen posts on this site suggesting exporting Fact and Dimension tables to CSV, importing to QVD and then creating routines to maintain data freshness. Also suggestions on creating connection and MDX to get data set to be used in QVD. A lot of additional legwork which seems counterproductive to Cube build process.

Is it possible to view AS cubed data in Qlikview as you would do in other tools i.e excel, proclarity.

responces much appreciated.

J

[#2 · Accepted solution]

There is no direct support for building or executing MDX-queries in QlikView. By using openrowset() in SQL, it is possible to encapsulate the MDX-query in a standard SELECT statement. In this example Microsoft OLEDB for Olap Services is used to query Analyis Services:

SQL SELECT * FROM
openrowset('MSOLAP.3','DATA SOURCE=localhost;Initial Catalog=Adventure Works DW',
'SELECT { [Measures].[Sales Amount], [Measures].[Tax Amount] } ON COLUMNS,
{ [Date].[Fiscal].[Fiscal Year].&[2002], [Date].[Fiscal].[Fiscal Year].&[2003] } ON ROWS
FROM [Adventure Works] WHERE ( [Sales Territory].[Southwest] )')

Note: Support for openrowset is disabled by default in SQL 2005 Server.

[#3]

As I thought.. Thankyou

[#4]

Hello Tom,

I am trying to build MDX-queries to access our cubes in Analysis Services. We have enabled openrowset in SQL Server but still I am getting anerror when trying to connect. I hope you can help.

Error: OLEDB connection failed

Here is my script:

CONNECT TO [Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=peb;Data Source=post-sql01;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error];


SQL SELECT * FROM OPENROWSET
('MSOLAP.4',
'Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=False;Data Source=post-sql01;Initial Catalog=peb;',
'SELECT {[Measures].[Market Volume]} ON COLUMNS, {[Date].[By Calendar Period].[Month].MEMBERS} ON ROWS FROM [DW]')

Sorry for replying on such an old post, but I performed a search on this topic and found this thread so i thought you may be the most helpful.

Thanks

Mike

[#5]

thank you for your answer..

it is solved...

[Anonymous]

Hi TMY,

I have a MDX query as below. How can i use this in QV to get the corresponding fields to create charts?

SELECT

{[Measures].[USD],

[Measures].[USD - Fct Budget],

[Measures].[USD - Fct Forecast]

}

DIMENSION

PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY

CrossJoin(

{[Dim Account Main3].[Account Key].Children},

{[Dim Activity Main].[Activity Key].Children},

{[DIM COST CENTRE MAIN].[Cost Centre Key].Children})

ON ROWS

FROM [IB Test]

;

Thnx & Rgds,

Nandha

[#7]

Hi Nandha,

Where you able to connect the SSAS to qlikview? I heed some help on this.

I am working on the connecting SSAS cube to Qlikview.

I took help of the community to execute the script. Most of the answers shows the adventure works sample query. Based on this here is my Facts and Dimensions.

Measures:

Total Qty, TEM, Fact Sales Count

Dimensions:

Dim Customer, Dim Time, Dim Product, DIM ALIGNMENT ZIP TERR

The above is the structure of my cube. It is a star schema.

Qlikview Script:

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CUBE NAME;Data Source=SERVER NAME(wher my cube is deployed);Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SERVER NAME (QV is residing);Use Encryption for Data=False;Tag with column collation when possible=False];

SQL SELECT * FROM OPENROWSET

('MSOLAP.5',

'Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=False;

Data Source=SERVER NAME(where my cube is deployed);Initial Catalog=CUBE NAME',

'SELECT {[Measures].[TOTAL QTY]} ON COLUMNS,

{[DIM PRODUCT].[PROD CD]} ON ROWS FROM [CUBE NAME]')

Error:

Connection failed:

SQLState: '42000'

SQL Server Error: 4060

Cannot open database "MEDINCQV" requested by the login. The login failed.

1) How to rectify this error. Is there any problem with my mdx QV Script?

2) Do I have any access issue?

3) Do I have to do settings from SSAS also?

NOTE: I do not want to use any third party application to achieve this.

Thanks in Advance.