Freigeben über


Executing multiple DMX statements from SSMS

Many people have asked how to execute multiple DMX statements in SSMS, and my answer has always been "Analysis Services doesn't support IMultipleResults, so you can't execute multiple statements in a single document in SQL Management Studio." Well it turns out I was wrong, and I was wrong - in a sense, at least.

If you typed in multiple DMX statements seperated by semicolons and tried to execute them, what I stated above is actually true - it won't work. However, usually the reason people want to do this is that they want to script a CREATE MINING MODEL, INSERT INTO, and possibly a SELECT statement together. If you were to use the above method, assuming it would work, it actually would mean that all these statements would get executed in parallel - which wouldn't work or be what you wanted anyway. Because you have to create the model before training, and train before selecting, the statements need to be executed serially. 

So what you want to do is to use the SQL Management Studio keyword "GO". When you type GO on its own line in SQL Management studio, it doesn't send the "GO" to the server, rather it sends the query up to that point, waits for a return and then sends the next statement serially (up to the next GO or the end) - exactly the behavior you want, and it works as well!

For example you can have a .DMX file that looks somewhat like this:

CREATE

MININGMODEL [MyModel]
(
MyKeyColumn
TEXTKEY ,
MyInputColumn
TEXTDISCRETE
) USINGMicrosoft_Clustering
;
GO

INSERT

INTO [MyModel] (MyKeyColumn, MyInputColumn)
OPENQUERY ([MyDataSource],'SELECT MyKey,MyInput FROM MyTable')
;
GO

SELECT

* FROM [MyModel]. CONTENT

And execute the entire file by pressing F5 to create, train, and retrieve content from your model in one fell swoop.