SSAS: Executing Arbitrary SQL queries

[Darren Gosbell]

I had a question a little while ago via my blog about possibly using a rowset action to execute a SQL query against a specified table. Although a rowset action will allow you to enter a SQL query, such a query is still executed against the current cube and only the subset of SQL supported by SSAS can be used. Basically the rowset action just returns a flattened result set.

However what would be possible would be to write a .Net stored procedure and use that to execute your SQL query. The code itself is really simple, the whole procedure only takes a few lines:

*

using System;using System.Data;using System.Data.OleDb;namespace ASSP{ public class SQLQuery { public static DataTable ExecuteSQL(string connectionString, string sql) { OleDbConnection conn = new OleDbConnection(connectionString); DataTable dt = new DataTable("Results"); OleDbDataAdapter da = new OleDbDataAdapter(sql, conn); da.Fill(dt); return dt; } }}*

And with this small amount of code you can execute any query that you like using the CALL statement

eg.

call assp.ExecuteSql( "provider=sqlncli;server=localhost;database=Advent ureWorksDW;trusted_connection=yes" ,"Select * from DimCurrency");*

Which is really cool if you want to do your own drillthrough or return an arbitrary recordset (assuming that your client application supports rowset actions). By you can actually execute more than just any query, you can actually execute any statement.

So you can paste the following set of statements into SSMS and run them.

call assp.ExecuteSql( "provider=sqlncli;server=localhost;database=Advent ureWorksDW;trusted_connection=yes" ,"Create TABLE myTable (id int)");GOcall assp.ExecuteSql( "provider=sqlncli;server=localhost;database=Advent ureWorksDW;trusted_connection=yes" ,"INSERT INTO myTable VALUES(1)");GOcall assp.ExecuteSql( "provider=sqlncli;server=localhost;database=Advent ureWorksDW;trusted_connection=yes" ,"SELECT * FROM myTable");GOcall assp.ExecuteSql( "provider=sqlncli;server=localhost;database=Advent ureWorksDW;trusted_connection=yes" ,"DROP TABLE myTable");Even though I can only see this technique being used for SELECT statements, you can basically do anything you like in the database provided you have the appropriate rights. Pretty powerful, initially I thought that this might be too powerful to put into the ASStoredProcedures Project on codeplex, but the more I think about it the more I think I was being paranoid, so I have checked this code into the ASStoredProcedure project. With the default deployment options a user can only perform operations that they already have rights to do anyway. It's really only if the assembly is deployed to run under the service account and if the service account has more rights than your end users that there is any risk of someone running a statement with elevated privileges.

Ultimately I'm not sure how directly useful this simple procedure will be, but I think it will provide a starting point from which more sophisticated routines can be developed using some of the other techniques demonstrated in other functions in ASSP. I'd be interested to hear comments from anyone who ends up adapting this routine.

Update 19 Jun: I removed the "ReadOnly=1" from the end of the connection string - as it was not actually doing anything

Technorati Tags: Analysis Services, Stored Procedures, ASSP





More...