HI Friends,
IS Anyone of you have extracted SSAS Cube data in QLikview ?. I am trying to extract the SSAS Cube data in Qlikview and refer the below syntax given in the QV community but it is not allowing us to extract the data.
CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=biserver;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=biserver;Use Encryption for Data=False;Tag with column collation when possible=False];
SQL SELECT * FROM OPENROWSET
('MSOLAP.4',
'Provide=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=False;Data Source=biserver;Initial Catalog=Analysis Services Tutorial;',
'SELECT {[Measures].[Unit Price]} ON COLUMNS, {[Customer].[Customer].MEMBERS} ON ROWS FROM [Adventure Works DW]'
)
Based on the above example i have created the below sample cube and try to load the cube data but it is not working.
SQL SELECT * FROM OPENROWSET('MSOLAP.4','Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=False;Data Source=localhost(Server name , here we created the cube in local machine);Initial Catalog=Analysis Services Project_testing;Trust Server Certificate=False;','select Measures.Name on Columns,[Sales Person].Bonus on rows from TestCube');
Measures = Bonus, Sales Quota
Dimension = Territory Id , Name
Data source = localhost
Sql Server version = SQL Server 2008 R2
Initial Catelog = Analysis Services Project_testing (SSAS project name)
Cube name = TestCube
table name = Sales Person(Dimension = Territory Id , Name) and Territory ( Measures = Bonus , Sales Quota)
can anyone help me to understand the syntax of the Openrow query like
1. If i have to see the data based on one dimension Name and one measures Bonus what would be my query?
2. Is the below query correct ?
select Measures.Name on Columns,[Sales Person].Bonus on rows from TestCube'.
Can anyone please let me know if you have worked in SSAS cube in Qlikview and successfully extracted the data in QLIKView.
