OLAP Cube generation programmatically in C#

OLAP cube generation is divided into 6 major steps:

1) Connect to the Analysis Services :
    Create a connection of AnalysisServices server.

2) Create a Database :
    Create a database in AnalysisServices server and save it.

3) Create a DataSource :
    Add a datasource to the database and set its connection string.

4) Create a DataSourceView :
    a) Create a DataSet.
    b) Add Fact tables in DataSet.
    c) Add Dimension tables in DataSet and Relation between them.
    d) Create a DataSourceView based on the created DataSet.

5) Create the Dimension, Attribute, Hierarchy, and MemberProperty Objects :
    a) Add Dimension to the Database.
    b) Add Dimension Attributes.
    c) Set Attribute usage and source.

6) Create the Cube, MeasureGroup, Measure, and Partition Objects :
    a) Add Cube to the Database and set Cube source to the Data Source View.
    b) Add Measure Group to the Cube.
    c) Add Measure to the Measure Group and set Measure source.
    d) Generate Cube :
        A) Add Dimension to the Cube.
        B) Use Regular Relationship Between Dimension and FactTable Measure Group.
        C) Link TableKey in DimensionTable with TableKey in FactTable Measure Group.
    d) Save Cube and all major objects to the Analysis Services.


After creating the cube, process the cube programmatically else we can process it manually from AnalysisServices Server.


To test the above implementation :
1) Create a console application.
2) Add DLL reference of Microsoft.AnalysisServices and Microsoft.SqlServer.Smo
3) Add CubeGenerator.cs file in project (or copy the code snippet given at the end of this article).
4) Run the Console Application.
    Console window will displays various steps of cube generationg. Press key to exit the application.



We can see the newly generated cube in AnalysisServices server. Drag-n-Drop the Dimension tables and Fact table to see the Cube.




Note : The mentioned code here is self explainatory and commented to understand the cube generation logic.


Following is the complete code snippet to create the OLAP cube programmatically in C# ( for better readability click here ):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.AnalysisServices;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;

namespace OLAPCube
{
    class CubeGenerator
    {
        static void Main(string[] args)
        {
            BuildCube();
        }

        #region Cube Generation.

        private static void BuildCube()
        {
            try
            {
                Console.WriteLine("Cube creation process started.");
                Console.WriteLine("");

                string strDBServerName = "LocalHost";
                string strProviderName = "msolap";
                string strFactTableName = "FactResellerSales";
                string strDBName = "AdventureWorksDW";
                string strCubeDBName = "OLAPDB";
                string strCubeDataSourceName = "OLAPDS";
                string strCubeDataSourceViewName = "OLAPDSView";
                int intDimensionTableCount = 6;

                string[,] strTableNamesAndKeys = { { "DimCurrency", "CurrencyKey", "FactResellerSales", "CurrencyKey" },
                                                   { "DimEmployee", "EmployeeKey", "FactResellerSales", "EmployeeKey" },
                                                   { "DimProduct", "ProductKey", "FactResellerSales", "ProductKey" },
                                                   { "DimPromotion", "PromotionKey", "FactResellerSales", "PromotionKey" },
                                                   { "DimReseller", "ResellerKey", "FactResellerSales", "ResellerKey" },
                                                   { "DimSalesTerritory", "SalesTerritoryKey", "FactResellerSales", "SalesTerritoryKey" },
                                                 };


                Server objServer = new Server();
                Database objDatabase = new Database();
                RelationalDataSource objDataSource = new RelationalDataSource();
                DataSourceView objDataSourceView = new DataSourceView();
                DataSet objDataSet = new DataSet();
                Dimension[] objDimensions = new Dimension[intDimensionTableCount];

                //Connecting to the Analysis Services.
                objServer = (Server)ConnectAnalysisServices(strDBServerName, strProviderName);
                //Creating a Database.
                objDatabase = (Database)CreateDatabase(objServer, strCubeDBName);
                //Creating a DataSource.
                objDataSource = (RelationalDataSource)CreateDataSource(objServer, objDatabase, strCubeDataSourceName, strDBServerName, strDBName);
                //Creating a DataSourceView.
                objDataSet = (DataSet)GenerateDWSchema(strDBServerName, strDBName, strFactTableName, strTableNamesAndKeys, intDimensionTableCount);
                objDataSourceView = (DataSourceView)CreateDataSourceView(objDatabase, objDataSource, objDataSet, strCubeDataSourceViewName);
                //Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects.                
                objDimensions = (Dimension[])CreateDimension(objDatabase, objDataSourceView, strTableNamesAndKeys, intDimensionTableCount);
                //Creating the Cube, MeasureGroup, Measure, and Partition Objects.
                CreateCube(objDatabase, objDataSourceView, objDataSource, objDimensions, strFactTableName, strTableNamesAndKeys, intDimensionTableCount);

                objDatabase.Process(ProcessType.ProcessFull);

                Console.WriteLine("Cube created successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error -> " + ex.Message);
            }

            Console.WriteLine("");
            Console.WriteLine("Press any key to exit.");
            Console.ReadLine();
        }

        #region Connecting to the Analysis Services.
        /// <summary>
        /// Connecting to the Analysis Services.
        /// </summary>
        /// <param name="strDBServerName">Database Server Name.</param>
        /// <param name="strProviderName">Provider Name.</param>
        /// <returns>Database Server instance.</returns>
        private static object ConnectAnalysisServices(string strDBServerName, string strProviderName)
        {
            try
            {
                Console.WriteLine("Connecting to the Analysis Services ...");

                Server objServer = new Server();
                string strConnection = "Data Source=" + strDBServerName + ";Provider=" + strProviderName + ";";
                //Disconnect from current connection if it's currently connected.
                if (objServer.Connected)
                    objServer.Disconnect();
                else
                    objServer.Connect(strConnection);

                return objServer;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in Connecting to the Analysis Services. Error Message -> " + ex.Message);
                return null;
            }
        }
        #endregion Connecting to the Analysis Services.

        #region Creating a Database.
        /// <summary>
        /// Creating a Database.
        /// </summary>
        /// <param name="objServer">Database Server Name.</param>
        /// <param name="strCubeDBName">Cube DB Name.</param>
        /// <returns>DB instance.</returns>
        private static object CreateDatabase(Server objServer, string strCubeDBName)
        {
            try
            {
                Console.WriteLine("Creating a Database ...");

                Database objDatabase = new Database();
                //Add Database to the Analysis Services.
                objDatabase = objServer.Databases.Add(objServer.Databases.GetNewName(strCubeDBName));
                //Save Database to the Analysis Services.
                objDatabase.Update();                

                return objDatabase;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in Creating a Database. Error Message -> " + ex.Message);
                return null;
            }
        }
        #endregion Creating a Database.

        #region Creating a DataSource.
        /// <summary>
        /// Creating a DataSource.
        /// </summary>
        /// <param name="objServer">Database Server Name.</param>
        /// <param name="objDatabase">Database Name.</param>
        /// <param name="strCubeDataSourceName">Cube DataSource Name.</param>
        /// <param name="strDBServerName">DB Server Name.</param>
        /// <param name="strDBName">DB Name.</param>
        /// <returns>DataSource instance.</returns>
        private static object CreateDataSource(Server objServer, Database objDatabase, string strCubeDataSourceName, string strDBServerName, string strDBName)
        {
            try
            {
                Console.WriteLine("Creating a DataSource ...");
                RelationalDataSource objDataSource = new RelationalDataSource();
                //Add Data Source to the Database.
                objDataSource = objDatabase.DataSources.Add(objServer.Databases.GetNewName(strCubeDataSourceName));
                //objDataSource.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=AdventureWorksDW;Data Source=LocalHost;Integrated Security=True;";
                objDataSource.ConnectionString = "Provider=SQLNCLI.1; Data Source=" + strDBServerName + "; Initial Catalog=" + strDBName + "; Integrated Security=SSPI;";
                objDataSource.Update();

                return objDataSource;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in Creating a DataSource. Error Message -> " + ex.Message);
                return null;
            }
        }
        #endregion Creating a DataSource.

        #region Creating a DataSourceView.
        /// <summary>
        /// Creating a DataSourceView.
        /// </summary>
        /// <param name="strDBServerName">DB Server Name.</param>
        /// <param name="strDBName">DB Name.</param>
        /// <param name="strFactTableName">FactTable Name.</param>
        /// <param name="strTableNamesAndKeys">Array of TableNames and Keys.</param>
        /// <param name="intDimensionTableCount">Dimension Table Count.</param>
        /// <returns>DataSet instance.</returns>
        private static object GenerateDWSchema(string strDBServerName, string strDBName, string strFactTableName, string[,] strTableNamesAndKeys, int intDimensionTableCount)
        {
            try
            {
                Console.WriteLine("Creating a DataSourceView ...");
                //Create the connection string.
                string conxString = "Data Source=" + strDBServerName + "; Initial Catalog=" + strDBName + "; Integrated Security=True;";
                //Create the SqlConnection.
                SqlConnection objConnection = new SqlConnection(conxString);
                DataSet objDataSet = new DataSet();
                //Add FactTable in DataSet.
                objDataSet = (DataSet)FillDataSet(objConnection, objDataSet, strFactTableName);
                //Add table in DataSet and Relation between them.
                for (int i = 0; i < intDimensionTableCount; i++)
                {
                    //Retrieve table's schema and assign the table's schema to the DataSet.
                    //Add primary key to the schema according to the primary key in the tables.
                    objDataSet = (DataSet)FillDataSet(objConnection, objDataSet, strTableNamesAndKeys[i, 0]);
                    objDataSet = (DataSet)AddDataTableRelation(objDataSet, strTableNamesAndKeys[i, 0], strTableNamesAndKeys[i, 1], strTableNamesAndKeys[i, 2], strTableNamesAndKeys[i, 3]);
                }

                return objDataSet;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in Creating a DataSourceView - GenerateDWSchema. Error Message -> " + ex.Message);
                return null;
            }
        }
        /// <summary>
        /// Fill the DataSet with DataTables.
        /// </summary>
        /// <param name="objConnection">Connection instance.</param>
        /// <param name="objDataSet">DataSet instance.</param>
        /// <param name="strTableName">TableName.</param>
        /// <returns>DataSet instance.</returns>
        private static object FillDataSet(SqlConnection objConnection, DataSet objDataSet, string strTableName)
        {
            try
            {
                string strCommand = "Select * from " + strTableName;
                SqlDataAdapter objEmpData = new SqlDataAdapter(strCommand, objConnection);
                objEmpData.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                objEmpData.FillSchema(objDataSet, SchemaType.Source, strTableName);

                return objDataSet;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in Creating a DataSourceView - FillDataSet. Error Message -> " + ex.Message);
                return null;
            }
        }
        /// <summary>
        /// Add relations between DataTables of DataSet.
        /// </summary>
        /// <param name="objDataSet">DataSet instance.</param>
        /// <param name="strParentTableName">Parent Table Name (Dimension Table).</param>
        /// <param name="strParentTableKey">Parent Table Key.</param>
        /// <param name="strChildTableName">Child Table Name (Fact Table).</param>
        /// <param name="strChildTableKey">Child Table Key.</param>
        /// <returns>DataSet instance.</returns>
        private static object AddDataTableRelation(DataSet objDataSet, string strParentTableName, string strParentTableKey, string strChildTableName, string strChildTableKey)
        {
            try
            {
                objDataSet.Relations.Add(strChildTableName + "_" + strParentTableName + "_FK", objDataSet.Tables[strParentTableName].Columns[strParentTableKey], objDataSet.Tables[strChildTableName].Columns[strChildTableKey]);

                return objDataSet;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in Creating a DataSourceView - AddDataTableRelation. Error Message -> " + ex.Message);
                return null;
            }
        }


        /// <summary>
        /// Creating a DataSourceView.
        /// </summary>
        /// <param name="objDatabase">DB instance.</param>
        /// <param name="objDataSource">DataSource instance.</param>
        /// <param name="objDataSet">DataSet instance.</param>
        /// <param name="strCubeDataSourceViewName">Cube DataSourceView Name.</param>
        /// <returns>DataSourceView instance.</returns>
        private static object CreateDataSourceView(Database objDatabase, RelationalDataSource objDataSource, DataSet objDataSet, string strCubeDataSourceViewName)
        {
            try
            {
                DataSourceView objDataSourceView = new DataSourceView();
                //Add Data Source View to the Database.
                objDataSourceView = objDatabase.DataSourceViews.Add(objDatabase.DataSourceViews.GetNewName(strCubeDataSourceViewName));
                objDataSourceView.DataSourceID = objDataSource.ID;
                objDataSourceView.Schema = objDataSet;
                objDataSourceView.Update();

                return objDataSourceView;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in Creating a DataSourceView - CreateDataSourceView. Error Message -> " + ex.Message);
                return null;
            }
        }
        #endregion Creating a DataSourceView.

        #region Creating a Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects.
        /// <summary>
        /// Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects.
        /// </summary>
        /// <param name="objDatabase">DB instance.</param>
        /// <param name="objDataSourceView">DataSource instance.</param>
        /// <param name="strTableNamesAndKeys">Array of Table names and keys.</param>
        /// <param name="intDimensionTableCount">Dimension table count.</param>
        /// <returns>Dimension Array.</returns>
        private static object[] CreateDimension(Database objDatabase, DataSourceView objDataSourceView, string[,] strTableNamesAndKeys, int intDimensionTableCount)
        {
            try
            {
                Console.WriteLine("Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects ...");

                Dimension[] objDimensions = new Dimension[intDimensionTableCount];
                for (int i = 0; i < intDimensionTableCount; i++)
                {
                    objDimensions[i] = (Dimension)GenerateDimension(objDatabase, objDataSourceView, strTableNamesAndKeys[i, 0], strTableNamesAndKeys[i, 1]);
                }

                ////Add Hierarchy and Level
                //Hierarchy objHierarchy = objDimension.Hierarchies.Add("ProductByCategory");
                //objHierarchy.Levels.Add("Category").SourceAttributeID = objCatKeyAttribute.ID;
                //objHierarchy.Levels.Add("Product").SourceAttributeID = objProdKeyAttribute.ID;
                ////Add Member Property
                ////objProdKeyAttribute.AttributeRelationships.Add(objProdDescAttribute.ID);
                //objDimension.Update();

                return objDimensions;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects. Error Message -> " + ex.Message);
                return null;
            }
        }
        /// <summary>
        /// Generate single dimension.
        /// </summary>
        /// <param name="objDatabase">DB instance.</param>
        /// <param name="objDataSourceView">DataSourceView instance.</param>
        /// <param name="strTableName">Table name.</param>
        /// <param name="strTableKeyName">Table key.</param>
        /// <returns>Dimension instance.</returns>
        private static object GenerateDimension(Database objDatabase, DataSourceView objDataSourceView, string strTableName, string strTableKeyName)
        {
            try
            {
                Dimension objDimension = new Dimension();

                //Add Dimension to the Database
                objDimension = objDatabase.Dimensions.Add(strTableName);
                objDimension.Source = new DataSourceViewBinding(objDataSourceView.ID);
                DimensionAttributeCollection objDimensionAttributesColl = objDimension.Attributes;
                //Add Dimension Attributes
                DimensionAttribute objAttribute = objDimensionAttributesColl.Add(strTableKeyName);
                //Set Attribute usage and source
                objAttribute.Usage = AttributeUsage.Key;
                objAttribute.KeyColumns.Add(strTableName, strTableKeyName, OleDbType.Integer);

                objDimension.Update();

                return objDimension;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects - GenerateDimension. Error Message -> " + ex.Message);
                return null;
            }
        }
        #endregion Creating a Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects.

        #region Creating the Cube, MeasureGroup, Measure, and Partition Objects.
        /// <summary>
        /// Creating the Cube, MeasureGroup, Measure, and Partition Objects.
        /// </summary>
        /// <param name="objDatabase">DB instance.</param>
        /// <param name="objDataSourceView">DataSourceView instance.</param>
        /// <param name="objDataSource">DataSource instance.</param>
        /// <param name="objDimensions">Dimensions array instance.</param>
        /// <param name="strFactTableName">FactTable Name.</param>
        /// <param name="strTableNamesAndKeys">Array of Table Names and Keys.</param>
        /// <param name="intDimensionTableCount">DimensionTable Count.</param>
        private static void CreateCube(Database objDatabase, DataSourceView objDataSourceView, RelationalDataSource objDataSource, Dimension[] objDimensions, string strFactTableName, string[,] strTableNamesAndKeys, int intDimensionTableCount)
        {
            try
            {
                Console.WriteLine("Creating the Cube, MeasureGroup, Measure, and Partition Objects ...");
                Cube objCube = new Cube();
                Measure objSales = new Measure();
                Measure objQuantity = new Measure();
                MdxScript objTotal = new MdxScript();
                String strScript;

                Partition objPartition = new Partition();
                Command objCommand = new Command();
                //Add Cube to the Database and set Cube source to the Data Source View
                objCube = objDatabase.Cubes.Add("SampleCube");
                objCube.Source = new DataSourceViewBinding(objDataSourceView.ID);
                //Add Measure Group to the Cube
                //MeasureGroup objMeasureGroup = objCube.MeasureGroups.Add("FactSales");
                MeasureGroup objMeasureGroup = objCube.MeasureGroups.Add(strFactTableName);

                //Add Measure to the Measure Group and set Measure source
                objSales = objMeasureGroup.Measures.Add("Amount");
                objSales.Source = new DataItem(strFactTableName, "SalesAmount", OleDbType.Currency);

                objQuantity = objMeasureGroup.Measures.Add("Quantity");
                objQuantity.Source = new DataItem(strFactTableName, "OrderQuantity", OleDbType.Integer);

                ////Calculated Member Definition
                //strScript = "Calculated; Create Member CurrentCube.[Measures].[Total] As [Measures].[Quantity] * [Measures].[Amount]";
                ////Add Calculated Member
                //objTotal.Name = "Total Sales";
                //objCommand.Text = strScript;
                //objTotal.Commands.Add(objCommand);
                //objCube.MdxScripts.Add(objTotal);

                for (int i = 0; i < intDimensionTableCount; i++)
                {
                    GenerateCube(objCube, objDimensions[i], objMeasureGroup, strFactTableName, strTableNamesAndKeys[i, 3]);
                }

                objPartition = objMeasureGroup.Partitions.Add(strFactTableName);
                objPartition.Source = new TableBinding(objDataSource.ID, "dbo", strFactTableName);

                objPartition.ProcessingMode = ProcessingMode.Regular;
                objPartition.StorageMode = StorageMode.Molap;
                //Save Cube and all major objects to the Analysis Services
                objCube.Update(UpdateOptions.ExpandFull);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in Creating the Cube, MeasureGroup, Measure, and Partition Objects. Error Message -> " + ex.Message);
            }
        }
        /// <summary>
        /// Generate cube.
        /// </summary>
        /// <param name="objCube">Cube instance.</param>
        /// <param name="objDimension">Dimension instance.</param>
        /// <param name="objMeasureGroup">MeasureGroup instance.</param>
        /// <param name="strFactTableName">FactTable Name.</param>
        /// <param name="strTableKey">Table Key.</param>
        private static void GenerateCube(Cube objCube, Dimension objDimension, MeasureGroup objMeasureGroup, string strFactTableName, string strTableKey)
        {
            try
            {
                CubeDimension objCubeDim = new CubeDimension();
                RegularMeasureGroupDimension objRegMGDim = new RegularMeasureGroupDimension();
                MeasureGroupAttribute objMGA = new MeasureGroupAttribute();
                //Add Dimension to the Cube
                objCubeDim = objCube.Dimensions.Add(objDimension.ID);
                //Use Regular Relationship Between Dimension and FactTable Measure Group
                objRegMGDim = objMeasureGroup.Dimensions.Add(objCubeDim.ID);
                //Link TableKey in DimensionTable with TableKey in FactTable Measure Group
                objMGA = objRegMGDim.Attributes.Add(objDimension.KeyAttribute.ID);

                objMGA.Type = MeasureGroupAttributeType.Granularity;
                objMGA.KeyColumns.Add(strFactTableName, strTableKey, OleDbType.Integer);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in Creating the Cube, MeasureGroup, Measure, and Partition Objects - GenerateCube. Error Message -> " + ex.Message);
            }
        }
        #endregion Creating the Cube, MeasureGroup, Measure, and Partition Objects.

        #endregion Cube Generation.
    }
}