Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
As I thought.. Thankyou
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
thank you for your answer..
it is solved...
Thnx & Rgds,
Nandha
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.