/*
* Create table mining structure (for measuregroups)
*/
private TableMiningStructureColumn CreateMiningStructureColumn(MeasureGroup measureGroup)
{
TableMiningStructureColumn column = new TableMiningStructureColumn();
column.Name = measureGroup.Name;
column.SourceMeasureGroup = new MeasureGroupBinding(".", ((Cube)measureGroup.Parent).ID, measureGroup.ID);
return(column);
}
// 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 table mining structure (for measuregroups)
*/
private TableMiningStructureColumn CreateMiningStructureColumn(MeasureGroup measureGroup)
{
TableMiningStructureColumn column = new TableMiningStructureColumn();
column.Name = measureGroup.Name;
column.SourceMeasureGroup = new MeasureGroupBinding(".", ((Cube)measureGroup.Parent).ID, measureGroup.ID);
return column;
}
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);
}
}
