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