Friday, April 18, 2008

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.
}
}


17 comments:

Balaji said...

Hi Avinash,
I able to create cube dynamically
with your code but how do i refresh
datasourceview for new changes done in datasource.can u help me regarding this.

Giorgio said...

Hi! Great script!
how can I create a dimension from a field in the table of measures and add to the cube?

Anonymous said...

Who knows where to download XRumer 5.0 Palladium?
Help, please. All recommend this program to effectively advertise on the Internet, this is the best program!

Unknown said...

very very good

thankful

AG said...

This code works like a charm. I just had to change Provider=SQLOLEDB in the connection string. I am about to create many cubes programatically and this is very helpful to me.
Thank you so much Avinash!!

Anonymous said...

congratulations...
But, How I save the .cube file?
How I link to devexpress, for example?

Thanks!

Chakresh sahu said...

Dear Avinash
This program is awesome. It is very good contribution by you.

Anonymous said...

Hello Avinash... very super script really.
Do you know how to create the cube in xmla structure.
If you post that method also i would be thank ful..

Regards
Mihir

Anonymous said...

Hi,
This is very helpful piece of code. However, I cannot process the cube. After long investigation it turns out the reason is I have tables (FACT and DIM) in a different schema than dbo. So, when I pass the table names, it takes it as "NewSchema.FactTable" and I get "Invalid object specified, as the internal query is set to "Select * from "[Newschema.Facttable]" ". I've checked when I set the select command I am specifying as "select * from NewSchema.FactTable", but somehow internally square brackets are introduced at wrong place. HELP!

Anonymous said...

Thanks, but these cubes have a file size limit of 2gb, How can we enable to create cubes over this size ?

Anonymous said...

Hello Avinash,
I think the sample code given by you works only when you have an analysis server like SQLSERVER installed on your machine. In my current task, there is no server available, instead I have to create an offline .cub file from a .db file which contains Facttable and other dim tables using C#. Can you please let me know on how to achieve this? Thanks in advance.

Unknown said...

Hi Avinash,

I have to create a cube,dimensions,measures using the datatable columns.

can you please guide me.

Thanks in adavnce.

Regards,
Lokesh

Unknown said...

I noticed you have comments to add a hierarchy to the cube. Did you - or do you have a working sample of this?

Thanks!!

Anonymous said...

This works great.

I also need to add a hierarchy and have noticed you have comments to add a hierarchy to the cube. Did you - or do you have a working sample of this?

Thanks!!

Ashraf shaban said...

really great work
i just need to know how to add linked objects to cube through code

Unknown said...

Hi, How to create a hierarchy for Date dimension?

Satish said...

Hi Avinash, I dont have any fact and dimension tables in database. I have only table name Product, can I create cube? If yes, then can you provide an example for that!!

Note: I dont want to create fact and dimension tables, only I want to use Product table and its data to create cube.

Google