public void CreateMiningModel()
{
//connecting the server and database
Server myServer = new Server();
myServer.Connect("DataSource=localhost;Catalog=FoodMart");
Database myDatabase = myServer.Databases["FoodMart"];
Cube myCube = myDatabase.Cubes["FoodMart 2000"];
CubeDimension myDimension = myCube.Dimensions["Customer"];
Microsoft.AnalysisServices.MiningStructure myMiningStructure =
myDatabase.MiningStructures.Add("CustomerSegement", "CustomerSegement");
//Bind the mining structure to a cube.
myMiningStructure.Source = new CubeDimensionBinding(".",
myCube.ID, myDimension.ID);
// Create the key column.
CubeAttribute customerKey = myCube.Dimensions["Customer"].Attributes["Customer"];
ScalarMiningStructureColumn keyStructureColumn =
CreateMiningStructureColumn(customerKey, true);
myMiningStructure.Columns.Add(keyStructureColumn);
//Member Card attribute
CubeAttribute memberCard =
myCube.Dimensions["Customer"].Attributes["Member Card"];
ScalarMiningStructureColumn memberCardStructureColumn = CreateMiningStructureColumn(memberCard, false);
myMiningStructure.Columns.Add(memberCardStructureColumn);
//Total Children attribute
CubeAttribute totalChildren = myCube.Dimensions["Customer"].Attributes["Total Children"];
ScalarMiningStructureColumn totalChildrenStructureColumn = CreateMiningStructureColumn(totalChildren, false);
myMiningStructure.Columns.Add(totalChildrenStructureColumn);
//Store Sales measure ToDo: fix this!
//Microsoft.AnalysisServices.Measure storeSales = myCube.MeasureGroups[0].Measures["Store Sales"];
//ScalarMiningStructureColumn storeSalesStructureColumn = CreateMiningStructureColumn(storeSales, false);
//myMiningStructure.Columns.Add(storeSalesStructureColumn);
//Create a mining model from the mining structure. By default, all the
//structure columns are used. Nonkey columns are with usage input
Microsoft.AnalysisServices.MiningModel myMiningModel = myMiningStructure.CreateMiningModel(true, "CustomerSegment");
//Set the algorithm to be clustering.
myMiningModel.Algorithm = MiningModelAlgorithms.MicrosoftClustering;
//Process structure and model
try
{
myMiningStructure.Update(UpdateOptions.ExpandFull);
myMiningStructure.Process(ProcessType.ProcessFull);
}
catch (Microsoft.AnalysisServices.OperationException e)
{
string err = e.Message;
}
}
// Mining sample model
private void CreateMarketBasketModel()
{
CubeAttribute basketAttribute;
CubeAttribute itemAttribute;
Server myServer = new Server();
myServer.Connect("DataSource=localhost;Catalog=FoodMart");
Database myDatabase = myServer.Databases["FoodMart"];
Cube myCube = myDatabase.Cubes["FoodMart 2000"];
CubeDimension myDimension = myCube.Dimensions["Customer"];
Microsoft.AnalysisServices.MiningStructure myMiningStructure =
myDatabase.MiningStructures.Add("MarketBasket", "MarketBasket");
myMiningStructure.Source = new CubeDimensionBinding(".", myCube.ID, myDimension.ID);
basketAttribute = myCube.Dimensions["Customer"].Attributes["Customer"];
itemAttribute = myCube.Dimensions["Product"].Attributes["Product"];
//basket structure column
ScalarMiningStructureColumn basket = CreateMiningStructureColumn(basketAttribute, true);
basket.Name = "Basket";
myMiningStructure.Columns.Add(basket);
//item structure column - nested table
ScalarMiningStructureColumn item =
CreateMiningStructureColumn(itemAttribute, true);
item.Name = "Item";
MeasureGroup measureGroup = myCube.MeasureGroups[0];
TableMiningStructureColumn purchases =
CreateMiningStructureColumn(measureGroup);
purchases.Name = "Purchases";
purchases.Columns.Add(item);
myMiningStructure.Columns.Add(purchases);
Microsoft.AnalysisServices.MiningModel myMiningModel = myMiningStructure.CreateMiningModel();
myMiningModel.Name = "MarketBasket";
myMiningModel.Columns["Purchases"].Usage = MiningModelColumnUsages.PredictOnly;
myMiningModel.Algorithm = MiningModelAlgorithms.MicrosoftAssociationRules;
}
private void RecurseMiningStructureColumnsAndProcessDataItemInLists(MiningStructureColumnCollection cols)
{
foreach (MiningStructureColumn col in cols)
{
if (col is ScalarMiningStructureColumn)
{
ScalarMiningStructureColumn scalar = (ScalarMiningStructureColumn)col;
foreach (DataItem di in scalar.KeyColumns)
{
ProcessDataItemInLists(di, "Mining Structure Column Key");
}
ProcessDataItemInLists(scalar.NameColumn, "Mining Structure Column Name");
}
else if (col is TableMiningStructureColumn)
{
TableMiningStructureColumn tblCol = (TableMiningStructureColumn)col;
RecurseMiningStructureColumnsAndProcessDataItemInLists(tblCol.Columns);
}
}
}
/*
* Create Scalar mining column
*/
private ScalarMiningStructureColumn CreateMiningStructureColumn(CubeAttribute attribute, bool isKey)
{
ScalarMiningStructureColumn column = new
ScalarMiningStructureColumn();
column.Name = attribute.Attribute.Name;
//cube attribute is usually modeled as discrete except for key column
column.Content = (isKey ? MiningStructureColumnContents.Key : MiningStructureColumnContents.Discrete);
column.IsKey = isKey;
//bind column source to a cube dimension attribute
column.Source = new CubeAttributeBinding(attribute.ParentCube.ID,
((CubeDimension)attribute.Parent).ID, attribute.Attribute.ID, AttributeBindingType.Name);
//Get the column data type from the attribute key column binding.
column.Type = MiningStructureColumnTypes.GetColumnType(attribute.Attribute.NameColumn.DataType);
return(column);
}
/*
* Create mining structure for selected database
*/
private MiningStructure CreateMiningStructure(Database objDatabase)
{
// drop the existing structures with the same name
DropExistingStructures(objDatabase, sStructureName);
// Initialize a new mining structure
MiningStructure currentMiningStruct = new MiningStructure(sStructureName, sStructureName);
currentMiningStruct.Source = new DataSourceViewBinding("Adventure Works DW");
DataSourceView currentDataSource = new DataSourceView("Adventure Works DW");
// Create the columns of the mining structure
// setting the type, content and data binding
// User Id column
ScalarMiningStructureColumn StructKey = new ScalarMiningStructureColumn("StructKey", "StructKey");
StructKey.Type = MiningStructureColumnTypes.Long;
StructKey.Content = MiningStructureColumnContents.Key;
StructKey.IsKey = true;
// Add data binding to the column
StructKey.KeyColumns.Add("dbo_DimCustomer", "CustomerKey", System.Data.OleDb.OleDbType.Integer);
// Add the column to the mining structure
currentMiningStruct.Columns.Add(StructKey);
// Generation column
ScalarMiningStructureColumn Gender = new ScalarMiningStructureColumn("Gender", "Gender");
Gender.Type = MiningStructureColumnTypes.Text;
Gender.Content = MiningStructureColumnContents.Discrete;
// Add data binding to the column
Gender.KeyColumns.Add("dbo_DimCustomer", "Gender", System.Data.OleDb.OleDbType.WChar);
// Add the column to the mining structure
currentMiningStruct.Columns.Add(Gender);
// Add Nested table by creating a table column and adding
// a key column to the nested table
/*
* TableMiningStructureColumn PayChannels = new TableMiningStructureColumn("PayChannels", "PayChannels");
* PayChannels.ForeignKeyColumns.Add("dbo_Customer", "TotalChildren", System.Data.OleDb.OleDbType.Integer);
*
* ScalarMiningStructureColumn Channel = new ScalarMiningStructureColumn("Channel", "Channel");
* Channel.Type = MiningStructureColumnTypes.Text;
* Channel.Content = MiningStructureColumnContents.Key;
* Channel.IsKey = true;
* // Add data binding to the column
* Channel.KeyColumns.Add("dbo_Customer", "FirstName", System.Data.OleDb.OleDbType.WChar);
* PayChannels.Columns.Add(Channel);
* currentMiningStruct.Columns.Add(PayChannels);
* */
// Add the mining structure to the database
objDatabase.MiningStructures.Add(currentMiningStruct);
currentMiningStruct.Update();
return(currentMiningStruct);
}
/*
* Create mining structure with cusomt fields
*/
private MiningStructure CreateCustomMiningStructure(Database objDatabase, string sStructName, string sTableName, string sKeyColumn, List <string> lsInputColumns, List <string> lsPredictColumns, string sAlgorithm, List <bool> lbPredictColumns)
{
// drop the existing structures with the same name
MiningStructure currentMiningStruct = objDatabase.MiningStructures.FindByName(sStructName);
if (currentMiningStruct != null)
{
currentMiningStruct.Drop();
}
// Initialize a new mining structure
currentMiningStruct = new MiningStructure(sStructName, sStructName);
currentMiningStruct.Source = new DataSourceViewBinding(objDatabase.DataSourceViews[0].Name);
// get data type for the selected column
string sQueryText = "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" +
sTableName + "' AND COLUMN_NAME = '" + sKeyColumn + "'";
// execute query
SQLManager manager = new SQLManager("MyDataBase");
DataTable objTable = new DataTable();
// get column data type
objTable.Load(manager.GetQueryResult(sQueryText));
string sDataType = objTable.Rows[0][0].ToString();
manager.CloseConnection();
// create key column
ScalarMiningStructureColumn StructKey = new ScalarMiningStructureColumn(sKeyColumn, sKeyColumn);
StructKey.Type = GetColumnStructureType(sDataType);
StructKey.Content = MiningStructureColumnContents.Key;
StructKey.IsKey = true;
StructKey.KeyColumns.Add("dbo_" + sTableName, sKeyColumn, GetColumnDataType(sDataType));
currentMiningStruct.Columns.Add(StructKey);
// input columns
for (int i = 0; i < lsInputColumns.Count; i++)
{
// get data type for the selected column
sQueryText = "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" +
sTableName + "' AND COLUMN_NAME = '" + lsInputColumns[i] + "'";
// get column data type
objTable = new DataTable();
objTable.Load(manager.GetQueryResult(sQueryText));
sDataType = objTable.Rows[0][0].ToString();
// Generation column
ScalarMiningStructureColumn Input = new ScalarMiningStructureColumn(lsInputColumns[i], lsInputColumns[i]);
Input.Type = GetColumnStructureType(sDataType);
if (Input.Type == MiningStructureColumnTypes.Long)
{
Input.Content = MiningStructureColumnContents.Continuous;
}
else
{
Input.Content = MiningStructureColumnContents.Discrete;
}
// Add data binding to the column
Input.KeyColumns.Add("dbo_" + sTableName, lsInputColumns[i], GetColumnDataType(sDataType));
// Add the column to the mining structure
currentMiningStruct.Columns.Add(Input);
manager.CloseConnection();
}
// add predict columns
// input columns
for (int i = 0; i < lsPredictColumns.Count; i++)
{
// if value = false (input & predict) then skip
if (lbPredictColumns[i] == false)
{
continue;
}
// get data type for the selected column
sQueryText = "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" +
sTableName + "' AND COLUMN_NAME = '" + lsPredictColumns[i] + "'";
// get column data type
objTable = new DataTable();
objTable.Load(manager.GetQueryResult(sQueryText));
sDataType = objTable.Rows[0][0].ToString();
// Generation column
ScalarMiningStructureColumn Input = new ScalarMiningStructureColumn(lsPredictColumns[i], lsPredictColumns[i]);
Input.Type = GetColumnStructureType(sDataType);
// for double and long set to continous, else is discrete
if (Input.Type == MiningStructureColumnTypes.Long || Input.Type == MiningStructureColumnTypes.Double)
{
Input.Content = MiningStructureColumnContents.Continuous;
}
else
{
Input.Content = MiningStructureColumnContents.Discrete;
}
// Add data binding to the column
Input.KeyColumns.Add("dbo_" + sTableName, lsPredictColumns[i], GetColumnDataType(sDataType));
// Add the column to the mining structure
currentMiningStruct.Columns.Add(Input);
manager.CloseConnection();
}
// Add the mining structure to the database
objDatabase.MiningStructures.Add(currentMiningStruct);
currentMiningStruct.Update();
return(currentMiningStruct);
}
/*
* Create Scalar mining column
*/
private ScalarMiningStructureColumn CreateMiningStructureColumn(CubeAttribute attribute, bool isKey)
{
ScalarMiningStructureColumn column = new
ScalarMiningStructureColumn();
column.Name = attribute.Attribute.Name;
//cube attribute is usually modeled as discrete except for key column
column.Content = (isKey ? MiningStructureColumnContents.Key : MiningStructureColumnContents.Discrete);
column.IsKey = isKey;
//bind column source to a cube dimension attribute
column.Source = new CubeAttributeBinding(attribute.ParentCube.ID,
((CubeDimension)attribute.Parent).ID, attribute.Attribute.ID, AttributeBindingType.Name);
//Get the column data type from the attribute key column binding.
column.Type = MiningStructureColumnTypes.GetColumnType(attribute.Attribute.NameColumn.DataType);
return column;
}
/*
* Create mining structure for selected database
*/
private MiningStructure CreateMiningStructure(Database objDatabase)
{
// drop the existing structures with the same name
DropExistingStructures(objDatabase, sStructureName);
// Initialize a new mining structure
MiningStructure currentMiningStruct = new MiningStructure(sStructureName, sStructureName);
currentMiningStruct.Source = new DataSourceViewBinding("Adventure Works DW");
DataSourceView currentDataSource = new DataSourceView("Adventure Works DW");
// Create the columns of the mining structure
// setting the type, content and data binding
// User Id column
ScalarMiningStructureColumn StructKey = new ScalarMiningStructureColumn("StructKey", "StructKey");
StructKey.Type = MiningStructureColumnTypes.Long;
StructKey.Content = MiningStructureColumnContents.Key;
StructKey.IsKey = true;
// Add data binding to the column
StructKey.KeyColumns.Add("dbo_DimCustomer", "CustomerKey", System.Data.OleDb.OleDbType.Integer);
// Add the column to the mining structure
currentMiningStruct.Columns.Add(StructKey);
// Generation column
ScalarMiningStructureColumn Gender = new ScalarMiningStructureColumn("Gender", "Gender");
Gender.Type = MiningStructureColumnTypes.Text;
Gender.Content = MiningStructureColumnContents.Discrete;
// Add data binding to the column
Gender.KeyColumns.Add("dbo_DimCustomer", "Gender", System.Data.OleDb.OleDbType.WChar);
// Add the column to the mining structure
currentMiningStruct.Columns.Add(Gender);
// Add Nested table by creating a table column and adding
// a key column to the nested table
/*
TableMiningStructureColumn PayChannels = new TableMiningStructureColumn("PayChannels", "PayChannels");
PayChannels.ForeignKeyColumns.Add("dbo_Customer", "TotalChildren", System.Data.OleDb.OleDbType.Integer);
ScalarMiningStructureColumn Channel = new ScalarMiningStructureColumn("Channel", "Channel");
Channel.Type = MiningStructureColumnTypes.Text;
Channel.Content = MiningStructureColumnContents.Key;
Channel.IsKey = true;
// Add data binding to the column
Channel.KeyColumns.Add("dbo_Customer", "FirstName", System.Data.OleDb.OleDbType.WChar);
PayChannels.Columns.Add(Channel);
currentMiningStruct.Columns.Add(PayChannels);
* */
// Add the mining structure to the database
objDatabase.MiningStructures.Add(currentMiningStruct);
currentMiningStruct.Update();
return currentMiningStruct;
}
/*
* Create mining structure with cusomt fields
*/
private MiningStructure CreateCustomMiningStructure(Database objDatabase, string sStructName, string sTableName, string sKeyColumn, List<string> lsInputColumns, List<string> lsPredictColumns, string sAlgorithm, List<bool> lbPredictColumns)
{
// drop the existing structures with the same name
MiningStructure currentMiningStruct = objDatabase.MiningStructures.FindByName(sStructName);
if (currentMiningStruct != null)
currentMiningStruct.Drop();
// Initialize a new mining structure
currentMiningStruct = new MiningStructure(sStructName, sStructName);
currentMiningStruct.Source = new DataSourceViewBinding(objDatabase.DataSourceViews[0].Name);
// get data type for the selected column
string sQueryText = "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" +
sTableName + "' AND COLUMN_NAME = '" + sKeyColumn + "'";
// execute query
SQLManager manager = new SQLManager("MyDataBase");
DataTable objTable = new DataTable();
// get column data type
objTable.Load(manager.GetQueryResult(sQueryText));
string sDataType = objTable.Rows[0][0].ToString();
manager.CloseConnection();
// create key column
ScalarMiningStructureColumn StructKey = new ScalarMiningStructureColumn(sKeyColumn, sKeyColumn);
StructKey.Type = GetColumnStructureType(sDataType);
StructKey.Content = MiningStructureColumnContents.Key;
StructKey.IsKey = true;
StructKey.KeyColumns.Add("dbo_" + sTableName, sKeyColumn, GetColumnDataType(sDataType));
currentMiningStruct.Columns.Add(StructKey);
// input columns
for (int i = 0; i < lsInputColumns.Count; i++)
{
// get data type for the selected column
sQueryText = "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" +
sTableName + "' AND COLUMN_NAME = '" + lsInputColumns[i] + "'";
// get column data type
objTable = new DataTable();
objTable.Load(manager.GetQueryResult(sQueryText));
sDataType = objTable.Rows[0][0].ToString();
// Generation column
ScalarMiningStructureColumn Input = new ScalarMiningStructureColumn(lsInputColumns[i], lsInputColumns[i]);
Input.Type = GetColumnStructureType(sDataType);
if (Input.Type == MiningStructureColumnTypes.Long)
Input.Content = MiningStructureColumnContents.Continuous;
else
Input.Content = MiningStructureColumnContents.Discrete;
// Add data binding to the column
Input.KeyColumns.Add("dbo_" + sTableName, lsInputColumns[i], GetColumnDataType(sDataType));
// Add the column to the mining structure
currentMiningStruct.Columns.Add(Input);
manager.CloseConnection();
}
// add predict columns
// input columns
for (int i = 0; i < lsPredictColumns.Count; i++)
{
// if value = false (input & predict) then skip
if (lbPredictColumns[i] == false)
continue;
// get data type for the selected column
sQueryText = "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" +
sTableName + "' AND COLUMN_NAME = '" + lsPredictColumns[i] + "'";
// get column data type
objTable = new DataTable();
objTable.Load(manager.GetQueryResult(sQueryText));
sDataType = objTable.Rows[0][0].ToString();
// Generation column
ScalarMiningStructureColumn Input = new ScalarMiningStructureColumn(lsPredictColumns[i], lsPredictColumns[i]);
Input.Type = GetColumnStructureType(sDataType);
// for double and long set to continous, else is discrete
if (Input.Type == MiningStructureColumnTypes.Long || Input.Type == MiningStructureColumnTypes.Double)
Input.Content = MiningStructureColumnContents.Continuous;
else
Input.Content = MiningStructureColumnContents.Discrete;
// Add data binding to the column
Input.KeyColumns.Add("dbo_" + sTableName, lsPredictColumns[i], GetColumnDataType(sDataType));
// Add the column to the mining structure
currentMiningStruct.Columns.Add(Input);
manager.CloseConnection();
}
// Add the mining structure to the database
objDatabase.MiningStructures.Add(currentMiningStruct);
currentMiningStruct.Update();
return currentMiningStruct;
}
public void AddMiningStructure()
{
Server srv = new Server();
srv.Connect("DataSource=CLARITY-7HYGMQM\\ANA;Initial Catalog=Adventure Works DW 2008");
Database db = srv.Databases["Adventure Works DW 2008"];
Cube myCube = db.Cubes["Adventure Works"];
CubeDimension myDimension = myCube.Dimensions.GetByName("Customer");
Microsoft.AnalysisServices.MiningStructure myMiningStructure = db.MiningStructures.Add("TestMining", "TestMining");
myMiningStructure.Source = new CubeDimensionBinding(".", myCube.ID, myDimension.ID);
// get current mining models
// Demo code
foreach (Microsoft.AnalysisServices.MiningStructure ms in db.MiningStructures)
{
Console.WriteLine(ms.Name);
foreach (Microsoft.AnalysisServices.MiningModel mm in ms.MiningModels)
{
Console.WriteLine(mm.Name);
}
}
CubeAttribute basketAttribute;
CubeAttribute itemAttribute;
basketAttribute = myCube.Dimensions.GetByName("Customer").Attributes[0];
itemAttribute = myCube.Dimensions.GetByName("Product").Attributes[0];
//basket structure column
ScalarMiningStructureColumn basket = CreateMiningStructureColumn(basketAttribute, true);
basket.Name = "Basket";
myMiningStructure.Columns.Add(basket);
//item structure column - nested table
ScalarMiningStructureColumn item = CreateMiningStructureColumn(itemAttribute, true);
item.Name = "Item";
MeasureGroup measureGroup = myCube.MeasureGroups[0];
TableMiningStructureColumn purchases = CreateMiningStructureColumn(measureGroup);
purchases.Name = "Purchases";
purchases.Columns.Add(item);
myMiningStructure.Columns.Add(purchases);
Microsoft.AnalysisServices.MiningModel myMiningModel = myMiningStructure.CreateMiningModel();
myMiningModel.Name = "MarketBasket";
myMiningModel.Columns["Purchases"].Usage = MiningModelColumnUsages.PredictOnly;
myMiningModel.Algorithm = MiningModelAlgorithms.MicrosoftAssociationRules;
try
{
myMiningStructure.Update(UpdateOptions.ExpandFull);
myMiningStructure.Process(ProcessType.ProcessFull);
}
catch (Microsoft.AnalysisServices.OperationException e)
{
this.sResult = e.StackTrace;
Console.WriteLine(e.StackTrace);
}
}
/*
* Create mining structures and models for olap
*/
public string CreateCubeMiningStructure(string sStructName, string sAlgorithm, int sCubeName, string sDimensionName, string sKeyColumn,
List <string> lsInputColumns, List <string> lsPredictColumns, List <string> lsMeasureInput, List <string> lsMeasurePredict, List <bool> lbPredictItems,
int parOne, int parTwo)
{
try
{
// connect to cube
Server objServer = new Server();
objServer.Connect("DataSource=" + sServer + ";Initial Catalog=" + sCatalog);
Database objDb = objServer.Databases[sCatalog];
Cube objCube = objDb.Cubes[sCubeName];
DataSourceView myView = objDb.DataSourceViews[0];
// create mining structure
CubeDimension objDimension = objCube.Dimensions.GetByName(sDimensionName);
// drop the existing structures with the same name
Microsoft.AnalysisServices.MiningStructure currentMiningStruct = objDb.MiningStructures.FindByName(sStructName);
if (currentMiningStruct != null)
{
currentMiningStruct.Drop();
}
Microsoft.AnalysisServices.MiningStructure myMiningStructure = objDb.MiningStructures.Add(sStructName, sStructName);
myMiningStructure.Source = new CubeDimensionBinding(".", objCube.ID, objDimension.ID);
/***************** Key column *****************/
// key column
CubeAttribute objKey = objCube.Dimensions.GetByName(sDimensionName).Attributes[0];
ScalarMiningStructureColumn objKeyColumn = CreateMiningStructureColumn(objKey, true);
objKeyColumn.Name = sKeyColumn;
myMiningStructure.Columns.Add(objKeyColumn);
/***************** Other columns *****************/
// create mining columns
for (int i = 0; i < lsInputColumns.Count; i++)
{
// get attribute
CubeAttribute objAttribute = new CubeAttribute();
objAttribute = objCube.Dimensions.GetByName(sDimensionName).Attributes[0];
// create mining column
ScalarMiningStructureColumn objColumn = CreateMiningStructureColumn(objAttribute, false);
objColumn.Name = lsInputColumns[i];
myMiningStructure.Columns.Add(objColumn);
/***************** Measure column *****************/
// create mining columns for measures
for (int j = 0; j < lsMeasureInput.Count; j++)
{
MeasureGroup objMeasureGroup = objCube.MeasureGroups[lsMeasureInput[j]];
TableMiningStructureColumn objMeasure = CreateMiningStructureColumn(objMeasureGroup);
objMeasure.Name = lsMeasureInput[j];
objMeasure.Columns.Add(objColumn);
myMiningStructure.Columns.Add(objMeasure);
}
}
/***************** Columns for prediction - will be updated in model *****************/
// create mining columns
for (int i = 0; i < lsPredictColumns.Count; i++)
{
// get attribute
CubeAttribute objAttribute = new CubeAttribute();
objAttribute = objCube.Dimensions.GetByName(sDimensionName).Attributes[0];
// if value = false (input & predict) then skip
if (lbPredictItems[i] == false)
{
continue;
}
else
{
// create mining column
ScalarMiningStructureColumn objColumn = CreateMiningStructureColumn(objAttribute, false);
objColumn.Name = lsPredictColumns[i];
myMiningStructure.Columns.Add(objColumn);
}
}
// update
myMiningStructure.Update();
/***************** Mining model *****************/
// create mining models
CreateMiningModel(myMiningStructure, sStructName, sAlgorithm, lsPredictColumns, lsMeasurePredict, lbPredictItems, parOne, parTwo);
// process
myMiningStructure.Process();
return("Success");
}
catch (Exception e)
{
return(e.Message);
}
}
/// <summary>
/// Generate a mining structure and full process it
/// </summary>
/// <param name="objDatabase">Analysis Service Database instance</param>
/// <param name="objDataSourceView">Analysis Service DataSourceView instance</param>
/// <param name="strCaseTableName">Mining table name</param>
/// <returns>Mining structure</returns>
public static object GenerateMiningStructure(Database objDatabase, DataSourceView objDataSourceView, string strCaseTableName, DecisionTreeAlgorithmParameters dtParams)
{
try
{
MiningStructure objMiningStructure = new MiningStructure();
objMiningStructure = objDatabase.MiningStructures.Add(objDatabase.MiningStructures.GetNewName(StringEncode(strCaseTableName)));
objMiningStructure.HoldoutMaxPercent = dtParams.HoldoutMaxPercent; // Percent for testing
objMiningStructure.Source = new DataSourceViewBinding(objDataSourceView.ID);
objMiningStructure.CaseTableName = strCaseTableName;
foreach (string name in getAllColumnName(objDataSourceView, strCaseTableName))
{
string colName = StringEncode(name);
ScalarMiningStructureColumn column = new ScalarMiningStructureColumn(colName, colName);
switch (colName)
{
case "ID":
// ProfileBasicId column
column.Type = MiningStructureColumnTypes.Long;
column.Content = MiningStructureColumnContents.Key;
column.IsKey = true;
// Add data binding to the column
column.KeyColumns.Add(strCaseTableName, name);
// Add the column to the mining structure
objMiningStructure.Columns.Add(column);
break;
case "ProfileBasicId":
case "JobPostingId":
case "UserId":
case "JobTitle":
case "JobName":
case "CompanyId":
case "CompanyName":
//column.Type = MiningStructureColumnTypes.Text;
//column.Content = MiningStructureColumnContents.Discrete;
break;
case "IsApproved":
default:
column.Type = MiningStructureColumnTypes.Boolean;
column.Content = MiningStructureColumnContents.Discrete;
// Add data binding to the column
column.KeyColumns.Add(strCaseTableName, name);
// Add the column to the mining structure
objMiningStructure.Columns.Add(column);
break;
}
}
MiningModel objMiningModel = objMiningStructure.CreateMiningModel(true, StringEncode(strCaseTableName));
//MiningModel objMiningModel = objMiningStructure.MiningModels.Add(objMiningStructure.MiningModels.GetNewName(strMiningStructureName));
objMiningModel.Algorithm = MiningModelAlgorithms.MicrosoftDecisionTrees;
objMiningModel.AllowDrillThrough = true;
objMiningModel.AlgorithmParameters.Add("SCORE_METHOD", dtParams.SCORE_METHOD);
objMiningModel.AlgorithmParameters.Add("COMPLEXITY_PENALTY", dtParams.COMPLEXITY_PENALTY);
objMiningModel.AlgorithmParameters.Add("SPLIT_METHOD", dtParams.SPLIT_METHOD);
objMiningModel.AlgorithmParameters.Add("MAXIMUM_INPUT_ATTRIBUTES", dtParams.MAXIMUM_INPUT_ATTRIBUTES);
objMiningModel.AlgorithmParameters.Add("MAXIMUM_OUTPUT_ATTRIBUTES", dtParams.MAXIMUM_OUTPUT_ATTRIBUTES);
objMiningModel.AlgorithmParameters.Add("MINIMUM_SUPPORT", dtParams.MINIMUM_SUPPORT);
int i = 0;
foreach(MiningModelColumn col in objMiningModel.Columns)
{
switch (col.Name)
{
case "IsApproved":
objMiningModel.Columns[i].Usage = "Predict";
break;
case "ID":
objMiningModel.Columns[i].Usage = "Key";
break;
default:
objMiningModel.Columns[i].Usage = "Input";
break;
}
++i;
}
//objMiningModel.Update(UpdateOptions.ExpandFull);
objMiningStructure.Update(UpdateOptions.ExpandFull);
Console.WriteLine("Processing mining model " + objMiningStructure.Name + "...");
objMiningModel.Process(ProcessType.ProcessFull);
Console.WriteLine("Process " + objMiningStructure.Name + " finished!");
return objMiningStructure;
}
catch (Exception ex)
{
throw new Exception("Error in Creating a Mining structure - GenerateMiningStructure. Error Message -> " + ex.Message);
}
}
/*---------------------------------
* Description: Create Mining Structure
* ----------------------------------- */
public static Microsoft.AnalysisServices.MiningStructure CreateMS(Microsoft.AnalysisServices.Server svr, string strStockCode, bool bAll, DateTime dtFrom, DateTime dtTo, bool bMulti)
{
Database db = svr.Databases.GetByName("Stock");
CreateDataAccessObjects(db, strStockCode, bAll, dtFrom, dtTo, bMulti, true);
Microsoft.AnalysisServices.MiningStructure ms = db.MiningStructures.FindByName(strStockCode);
if (ms != null)
{
// string message = "Bạn muốn tạo mới mô hình dự báo cho cổ phiếu này?";
// string caption = "Recreate New Mining Model";
// MessageBoxButtons buttons = MessageBoxButtons.YesNo;
// DialogResult result;
// // Displays the MessageBox.
// result = MessageBox.Show(message, caption, buttons);
// if (result == DialogResult.Yes)
// {
// ms.Drop();
// }
// else
// {
// //CreateMM(ms, strStockCode);
// return ms;
// }
ms.Drop();
}
// Add new Mining Structure
ms = db.MiningStructures.Add(strStockCode, strStockCode);
ms.Source = new DataSourceViewBinding(strStockCode);
ms.CaseTableName = strStockCode;
ScalarMiningStructureColumn intID = new ScalarMiningStructureColumn("ID", "ID");
intID.Type = MiningStructureColumnTypes.Long;
intID.Content = MiningStructureColumnContents.KeyTime;
intID.IsKey = true;
// Add data binding to the column.
intID.KeyColumns.Add(strStockCode, "ID", OleDbType.Integer);
// Add the column to the MiningStructure
ms.Columns.Add(intID);
ScalarMiningStructureColumn dblClosePrice = new ScalarMiningStructureColumn("ClosePrice", "ClosePrice");
dblClosePrice.Type = MiningStructureColumnTypes.Double;
dblClosePrice.Content = MiningStructureColumnContents.Continuous;
dblClosePrice.KeyColumns.Add(strStockCode, "ClosePrice", OleDbType.Double);
ms.Columns.Add(dblClosePrice);
// Don't use Open,High,Low for VNIndex, Compressed Date, and Multi Step Forecast
if (strStockCode.ToUpper() != "VNINDEX" && strStockCode.Length < 11 && !bMulti)
{
ScalarMiningStructureColumn dblOpenPrice = new ScalarMiningStructureColumn("OpenPrice", "OpenPrice");
dblOpenPrice.Type = MiningStructureColumnTypes.Double;
dblOpenPrice.Content = MiningStructureColumnContents.Continuous;
dblOpenPrice.KeyColumns.Add(strStockCode, "OpenPrice", OleDbType.Double);
ms.Columns.Add(dblOpenPrice);
ScalarMiningStructureColumn dblHighPrice = new ScalarMiningStructureColumn("HighPrice", "HighPrice");
dblHighPrice.Type = MiningStructureColumnTypes.Double;
dblHighPrice.Content = MiningStructureColumnContents.Continuous;
dblHighPrice.KeyColumns.Add(strStockCode, "HighPrice", OleDbType.Double);
ms.Columns.Add(dblHighPrice);
ScalarMiningStructureColumn dblLowPrice = new ScalarMiningStructureColumn("LowPrice", "LowPrice");
dblLowPrice.Type = MiningStructureColumnTypes.Double;
dblLowPrice.Content = MiningStructureColumnContents.Continuous;
dblLowPrice.KeyColumns.Add(strStockCode, "LowPrice", OleDbType.Double);
ms.Columns.Add(dblLowPrice);
//ScalarMiningStructureColumn lngVolume = new ScalarMiningStructureColumn("Volume", "Volume");
//lngVolume.Type = MiningStructureColumnTypes.Long;
//lngVolume.Content = MiningStructureColumnContents.Continuous;
//lngVolume.KeyColumns.Add(strStockCode, "Volume", OleDbType.Double);
//ms.Columns.Add(lngVolume);
}
ms.Update();
CreateMM(ms, strStockCode, bMulti);
return(ms);
}
