How to Create New SSAS Cube from existing dynamically in C#

In this post I am going to share information about how to create a new cube programmatically in c# from existing cube by changing its name and data source name to point it to new data warehouse database.
In my case, I am having my main data warehouse database in which data comes at large and it has its cube and I have provided a facility to archive the 6 months old data to new year wise data warehouse database so accordingly new year wise cube is also get created pointing to this new year wise database.

So in above case I had decided that will create a new cube which will have same schema as my existing cube because the new database to which it is going to point will have same schema structure as like existing data warehouse database.

To achieve it I first tried to create an xmla script of cube using Scripter class and then edit its name and data source so accordingly I started to google about it but later I thought to try clone existing cube with the Microsoft.AnalysisServices.dll as I am already using it to process cube dynamically in C# and finally I succeeded to create a new cube from existing cube in c# so sharing the same below.

You can check the comments in below code to understand what the specific line of code does what. Still if you have any point to discuss or anything to add to make this code better feel free to share it in comment section.

Here is the code. I am sharing the function directly which will have two parameters. One is “newCubeDBName” it is the name of cube database which you are going to create and another is “dwDBNameToPoint” which is the source data warehouse database name to which new cube will get pointed.

 public void CreateNewCubeDatabase(string newCubeDBName, string dwDBNameToPoint)
        {
            Server server = null;
            Database database = null;
            Database cloneDatabase = null;
            DataSource cloneDataSource = null;           
            try
            {
                //you can keep this connection string in config and can take from there
                string ssasConStr = "Data source=YourSSASServerName;Initial Catalog=YourCubeDBName;Integrated Security=SSPI;";

                if (!string.IsNullOrWhiteSpace(ssasConStr))
                {
                    server = new Server();
                    server.Connect(ssasConStr);

                    ConnectionInfo conInfo = new ConnectionInfo(ssasConStr);

                    database = server.Databases.FindByName(conInfo.Catalog);

                    #region Check if already exist and if found remove it (or you can process it directly and exit rather creating new)

                    if (server.Databases.Contains(newCubeDBName))
                    {
                        server.Databases[newCubeDBName].Drop(); 
                    }

                    #endregion


                    #region Change Cube Database Id and name to new
                    cloneDatabase = database.Clone();
                    cloneDatabase.ID = newCubeDBName;
                    cloneDatabase.Name = newCubeDBName;

                    #endregion

                    #region Change Database Name in Cube Datasource's Connection String

                    cloneDataSource = database.DataSources[0].Clone();

                    OleDbConnectionStringBuilder oldCon = new OleDbConnectionStringBuilder(cloneDatabase.DataSources[0].ConnectionString);
                    oldCon["Initial Catalog"] = dwDBNameToPoint;
                    oldCon["Data Source"] = server.Name;
                    cloneDataSource.ConnectionString = oldCon.ConnectionString;
                    cloneDatabase.DataSources.Clear();
                    cloneDatabase.DataSources.Add(cloneDataSource);

                    #endregion
                    
                    //Now add this cloned cube databse in DB collection
                    if (!server.Databases.Contains(cloneDatabase))
                        server.Databases.Add(cloneDatabase);


                    //Update New db to the server (this will create a new DB)
                    cloneDatabase.Update(UpdateOptions.ExpandFull, UpdateMode.CreateOrReplace);

                    //process new cube db
                    cloneDatabase.Process(ProcessType.ProcessFull);
                    
                }
                else
                    throw new Exception("SSASConnectionString cannot be empty.");
            }
            catch
            {
                #region Drop newly create cube if error occurs in later flow

                if (server != null && server.Connected && server.Databases.Contains(newCubeDBName))
                {
                    server.Databases[newCubeDBName].Drop();
                }
                
                #endregion
                
                throw;
            }
            finally
            {
                #region Dispose created objects
                if (server != null)
                {
                    if (server.Connected)
                        server.Disconnect(true);

                    server.Dispose();
                }

                if (database != null) { database.Dispose(); }
                if (cloneDatabase != null) { cloneDatabase.Dispose(); }
                if (cloneDataSource != null) { cloneDataSource.Dispose(); }
                if (cubeDimDate != null) { cubeDimDate.Dispose(); }
                #endregion
            }

        }