Subscribe - It's FREE!!

Stay Connected Here

Stay Updated With Us Here



Google

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


Share with WhatsApp


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
            }

        }

 



If you enjoyed this post take 5 seconds to share it! Be Socialable. :-)

Share with WhatsApp

Posts To Read Next

Process SSAS Cube programmatically in C# using Microsoft.AnalysisServices Dll

Post on How to process SSAS Cube dynamically in C# with or without change in Dimension data using Microsoft.AnalysisServices


Top 10 Visual Studio things which can boost developers coding speed

Visual Studio 2012 provides some coding features by which you can code faster if use them properly. This post will cover top 10 things among them to boost your development speed.


Visual Studio 2008 Shell and TFS integration

Visual Studio 2008 Shell and TFS integration is the problem for all newbies of BIDS and TFS. Here is the solution.


Assembla - Free and private repository to manage your source code online with SVN subversion hosting

With Assembla you can share source code with others online. Free & Private source code repository with SVN Subversion, Git & Perforce Hosting.


How to call click or any event only once in jQuery

Know how to execute an click event or any event only once for any element in jQuery. Perform action only once and even not required to unbind event.


Your opinion is valuable for us! Comments, suggetions are welcome.


Submit your Email Id to stay updated with us and get notified with our new posts. It's FREE!
We know this popup is disturbing you!
But We would greatly appreciate if you share us with your friends below!

It will not take more than 2 seconds but will motivate us greatly to write more,share more!

x