SSAS: Executing Arbitrary SQL queries

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=AdventureWorksDW;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=AdventureWorksDW;trusted_connection=yes"
    ,"Create TABLE myTable (id int)");
GO
call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"INSERT INTO myTable VALUES(1)");
GO
call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"SELECT * FROM myTable");
GO
call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;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

Print | posted on Thursday, June 18, 2009 11:20 PM

Comments on this post

# re: SSAS: Executing Arbitrary SQL queries

Requesting Gravatar...
I was explaining that way of executing SQL queries during a seminar last week, I made a post about it, which was really similar almost at the same time :) The best thing about this way of retrieving rowset is that it can be interpreted by all the SSAS clients I know. Do you think this code will be integrated in ASSP soon?
Left by François JEHL on Jun 21, 2009 10:57 PM

# re: SSAS: Executing Arbitrary SQL queries

Requesting Gravatar...
Hi Francois, I have checked in this function into ASSP as a separate project so that it compiles as it's own DLL, so if you go to the "Source Code" tab you can download the latest change set which contains this code. I will talk to the rest of the team about putting out another release including this code.

Darren
Left by Darren Gosbell on Jun 22, 2009 7:34 AM

# re: SSAS: Executing Arbitrary SQL queries

Requesting Gravatar...
Thanks. The thing that surprised me when I first saw your post is that the code you used for your SP is quite similar to mine (which you can see on my personal page). I did not think I was able to produce any piece of code that could be enough "clean" to fit the ASSP requirements :) Thanks anyway for putting this one in ASSP. We should now notice MS that the Rowset action can now really be used to retrieve data based on an SQL query!
Left by François JEHL on Jun 23, 2009 11:59 PM

# re: SSAS: Executing Arbitrary SQL queries

Requesting Gravatar...
Hey, thanks for suggesting the alternative code.
Left by Blocked Drains on May 08, 2010 7:48 PM

# re: SSAS: Executing Arbitrary SQL queries

Requesting Gravatar...
Thanks! Brilliant work. Hope the point about alternative code will be developed furthermore.
Left by TubesFan on Dec 17, 2010 6:06 PM

# re: SSAS: Executing Arbitrary SQL queries

Requesting Gravatar...
Here are some other articles on SSAS Stored Procedures: http://ssas-wiki.com/w/Articles#.Net_stored_procedure
Left by Sam Kane on Feb 24, 2011 6:39 AM

# re: SSAS: Executing Arbitrary SQL queries

Requesting Gravatar...
Is the XMLA available to add this as an assembly in SSAS? Alternatively how can I compile the .NET solution to get the SSAS XMLA?
Left by Daniel Frank on Aug 31, 2017 11:47 PM

# re: SSAS: Executing Arbitrary SQL queries

Requesting Gravatar...
I have a powershell script which converts dlls to an XMLA statement so I just ran that over the last release of SQLQuery.dll and pushed it up to the related codeplex release here https://asstoredprocedures.codeplex.com/releases/view/574328
Left by Darren Gosbell on Sep 01, 2017 6:35 AM

Your comment:

 (will show your gravatar)