I am playing a lot with DMX. I started to prefer to create mining models with code, not with BIDS. However, you can’t create a data source or a data source view with DMX. Therefore I decided to write AS stored procedures for these tasks. This way I can create everything from SSMS.
First you need to create a new database in AS. You can do it with SSMS. Then you create a class library with a public static (shared) method that will create the data source. Analysis Management Objects (AMO) can do this task. Note AMO create XMLA code by default. You can use the XMLA code in a SQL Server Agent scheduled Job, for example. But I want to update the AS database directly, therefore I will suspend creation of XMLA.
In the project, you have to add a reference to AMO. AMO class library is in the C:Program FilesMicrosoft SQL Server90SDKAssembliesMicrosoft.AnalysisServices.dll file. You will have to browse to find it, it is not enlisted between .NET class libraries.
When I executed my procedure for the first time, it was an endless execution. I had to restart AS in order to stop the execution. I spent couple of hours before I realized what went wrong. The problem is with locking. I realized I have to be in the context of another database when updating a database. Because of this fact I added a check for the database context using AdoMdServer, as I have shown in my previous blog.
Here is the C# code:
using AMO = Microsoft.AnalysisServices;
using ADOMD = Microsoft.AnalysisServices.AdomdServer;
public class ASProcs
public static void CreateDataSource(string relDbName, string asDbName)
// Call in DMX:
// Call ServerProcs.CreateDataSource(Existing_SQL_DatabaseName, Existing_AS_DatabaseName)
// Data Source will have the same name as Existing_SQL_DatabaseName
// Call ServerProcs.CreateDataSource(‘pubs’, ‘TestDB’)
AMO.Server currSvr = new AMO.Server();
// Don’t need the XMLA – will update directly
currSvr.CaptureXml = false;
// Check the context to avoid endless execution
currSvr.Connect(“DataSource=” + ADOMD.Context.CurrentServerID + “;”);
if (asDbName == ADOMD.Context.CurrentDatabaseName)
throw new ArgumentException(” Can’t update current database! “);
// Target database
AMO.Database targetDb = currSvr.Databases.GetByName(asDbName);
// Create a new data source
AMO.RelationalDataSource newRds = new AMO.RelationalDataSource(relDbName, relDbName);
“;Data Source=localhost” +
“;Initial Catalog=” + relDbName +
// Update the database
After you register the assembly on AS, you can call this procedure using DMX or MDX query. Note you have to register the assembly with the Unrestricted permission set, because the code is going outside AS (to SQL Server) and it has side effects – it changes the state of the AS database. So here is the DMX code:
— Create a data source
Call ServerProcs.CreateDataSource(‘AdventureWorksDW’, ‘TestDb’);
- Python for SQL Server Specialists Part 4: Python and SQL Server - April 24, 2018
- Python for SQL Server Specialists Part 3: Graphs and Machine Learning - April 11, 2018
- Python for SQL Server Specialists Part 2: Working with Data - March 22, 2018