/* * 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); } }