SQL Server Analysis Services (SSAS) Cubes and QlikView

[#1]

A prospect insists that pulling data from the SSAS Cubes is the best idea rather than building the data model inside QlikView and replicating the transformations they applied. Their reasoning is that building the data model inside QlikView will create another validation headache. They also want to keep it as people don't want to stop using Excel PivotTables. I very much understand this. Given the situation, this is what I have proposed.

1. Export a snapshot of the Fact Table to CSV and Export to QVD.
2. Export a snapshot of the Dimension Tables to CSV and Export to QVD.
3. Setup Incremental Load for the Fact Table using MDX (read from Cube). This is done manually as QV cannot create MDX queries.
4. Setup Incremental Load for the Dimension Tables using SQL (read from DB).

Problems I foresee:

1. When the cube is rebuilt, the QV application has to be remapped (map new fields to existing fields).
2. In the wors case scenario where the cube does not resemble the previous cube, the QV application has to be built again. Not a lot of brain work, but more on leg work.

What other problems do you guys foresee given the situation? Any alternatives to propose?

- Kevin

[#2]

HI Kevin,

I had a similar issue and in the end I was able to refer back to the relational tables that were the source for the cube. At the time I did investigate linking QlikView directly to an Analysis services cube and this worked very well. For reference here is a sample script to extract data from the Microsoft FoodMart cube that is part of the Analysis Services tutorial.

// Connection string to standard SQL Server instance (note initial catalog to SQL database not required)
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];
// Query to extract MDX as relational data set
SQL SELECT * FROM OPENROWSET
('MSOLAP.3',
'Provide=MSOLAP.3;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]'
)

I hope this is of some help.

Rod

[#3]

H

rjager wrote:
HI Kevin,
I had a similar issue and in the end I was able to refer back to the relational tables that were the source for the cube. At the time I did investigate linking QlikView directly to an Analysis services cube and this worked very well. For reference here is a sample script to extract data from the Microsoft FoodMart cube that is part of the Analysis Services tutorial.
 <blockquote><pre>
// Connection string to standard SQL Server instance (note initial catalog to SQL database not required)
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];
// Query to extract MDX as relational data set
SQL SELECT * FROM OPENROWSET
('MSOLAP.3',
'Provide=MSOLAP.3;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]'
)

I hope this is of some help.

Rod

Hi Rod,

Thanks for the help. I think it would also depend on the extent of the transformation/aggregations the cube has. Anybody else have ideas?

Kevin

[brunomarques]

I've got around the problem of accessing SSAS MDX cubes developing my own tool to generate MDX queries. It's available at :

http://xthview.com/index.php/download/

Feel free to use it, feedback is welcome.

[#5]

One word for the tool, AWSOME !!!

[Anonymous]

Brun,

Pls let me know the step by step process of loading cube in this tool. Also can i extract the data from the cube which is loaded in this tool?

Thnx & Rgds,

Nandha

[Anonymous]

Hi Hleboeuf,

I have mappd the cube server with my local Server Management Studio. But i am not clear how i can link that to my MDX query generator.

When i type the server path in server information and click Connect button i am getting and error "InvalidArgument=Value of '0' is not valid for 'index' Parameter Name: index"

Can you pls help me here?

Rgds,

Nandha

[#8]

Hello Bruno,

I tried this tool but it always says "connection cannot be made ensure that server is running", even though my server is running.

Regards

Kavitha

[brunomarques]

You can find a brief manual here :

https://dl.dropbox.com/u/50769050/Qlikview%20MDX%20Query%20Tool%20Manual.pdf

You can execute queries, but the tool will not (by default) export all of the cube data!

[brunomarques]

The best troubleshooting would be to :

• Ensure that you can connect from your machine to the SSAS server using Excel Pivot tables.
• Ensure that you are using the same server name in the tool that you use on Excel.
• Make sure that the tool is not being blocked by some firewall.

The described error is associated with the fact that the tool cannot connect to the SSAS server, check for connectivity issues.