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