AS Stored Procedure: Creating a Data Source

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 System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.OleDb;

using AMO = Microsoft.AnalysisServices;

using ADOMD = Microsoft.AnalysisServices.AdomdServer;

namespace ServerProcs

{

    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

        // Example:

        // 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);

            if (targetDb.DataSources.Contains(newRds.ID))

            {

                targetDb.DataSources.Remove(newRds.ID);

            }

            newRds.ConnectionString =

                “Provider=SQLOLEDB” +

                “;Data Source=localhost” +

                “;Initial Catalog=” + relDbName +

                “;Integrated Security=SSPI”;

            targetDb.DataSources.Add(newRds);

            // Update the database

            targetDb.Update(AMO.UpdateOptions.ExpandFull);

        }

    }

}

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’);