2nd openrowset is a config to do in your analysis services SQL. It is a must.
3rd Query like it follows.
OLEDBCONNECTTO[Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Data Source=blah.blah.blah.blah;Use Procedure for Prepare=1; Auto Translate=True;Packet Size=4096;Workstation ID=blahblah;Use Encryption for Data=False; Tag with column collation when possible=False] (XPasswordis XVGHHbFNULYYWZVMSTdKGZNP);
I will explain bellow the way to do it (because you can do it), but I have to ask you first if you really need to extract data from a cube. This question is because you usually (99% of times) have the same data in the cube and in the DW behind the cube, and if you use cubes to extract data into QlikView, you are using an unnecessary piece that will complicate future maintenance, that is very rigid and that usually hasn't all the detailed data.
People want to use their cubes because they have spent so many weeks (or months) and effort building them, that they think that work have to count for other projects. But usually you can have a QlikView application from the same DW with the same information (or more) just in some hours or days.
There are two main methods to get data from SSAS (SQL Server Analysis Services) cubes:
OpenRowset: You really connect to SQL Server and use the OpenRowset sentence to make SQL Server launch the MDX query to SSAS, and then getting back the results to QlikView in a Rowset object. This method allows full MDX syntax, with calculated members. The MDX query has to be written in the QlikView SQL sentence manually.
SSAS OLEDB connection: It is a MDX query directly launched to SSAS, but it allows only plain MDX, without calculated members. The MDX query has to be written in the QlikView SQL sentence manually, but it is easier than with OpenRowset.
QlikView has not any MDX wizard, you have to write the MDX sentence in the SQL part of the connection.
But be careful when working with SSAS cubes:
SSAS extractions are very slow, SSAS is an OLAP engine designed to aggregate many data and accumulate into some values. Not to return large datasets, like the relational database engine.
If you query many “theoretical” cells from a SSAS cube, it is extremely slow and you can get the famous error “The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.”. Or get a “Out of memory” error. This can happen with large cubes with large dimensions, where the possible theoretical combinations (the product of all members) are larger.
Usually the best approach is using the database (or DW) for transaction detail extractions, and the cube for complex calculations or special calculations on aggregated data (usually can be replicated into QlikView, but this way there is only one place to maintain this calculations).
But you can also think about this other points:
Usual MDX query is not complicated, it is similar to SQL, mainly for simple queries.
You can build dynamically any MDX query before querying to SSAS. i.e. I have done a SSAS extraction looping some dimensions to extract data for every dimension value, like doing slicing. Depending the combinations of MDX you need, you could use parameters in the QlikView script.