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; } }
/* * Create mining model */ private void CreateMiningModel(Microsoft.AnalysisServices.MiningStructure objStructure, string sName, string sAlgorithm, List <string> lsAtrPredict, List <string> lsMeasurePredict, List <bool> lbPredictItems, int parOne, int parTwo) { Microsoft.AnalysisServices.MiningModel myMiningModel = objStructure.CreateMiningModel(true, sName); /* Notes: * Each mining column must have its' input and predict columns * Input and key columns are added automatically when they are created in the mining structure * Predict columns can be added in the mining model * An input column can be also a predict column */ myMiningModel.Algorithm = sAlgorithm; switch (sAlgorithm) { case MiningModelAlgorithms.MicrosoftClustering: myMiningModel.AlgorithmParameters.Add("CLUSTERING_METHOD", parOne); if (parTwo > 0) { myMiningModel.AlgorithmParameters.Add("CLUSTER_COUNT", parTwo); } break; //case MiningModelAlgorithms.MicrosoftTimeSeries: // myMiningModel.AlgorithmParameters.Add("PERIODICITY_HINT", "{12}"); // {12} represents the number of months for prediction // break; case MiningModelAlgorithms.MicrosoftNaiveBayes: break; case MiningModelAlgorithms.MicrosoftDecisionTrees: myMiningModel.AlgorithmParameters.Add("SCORE_METHOD", parOne); myMiningModel.AlgorithmParameters.Add("SPLIT_METHOD", parTwo); break; } /***************** Predict columns *****************/ // add optional predict columns if (lsAtrPredict.Count != 0) { // predict columns for (int i = 0; i < lsAtrPredict.Count; i++) { Microsoft.AnalysisServices.MiningModelColumn modelColumn = myMiningModel.Columns.GetByName(lsAtrPredict[i]); modelColumn.SourceColumnID = lsAtrPredict[i]; if (lbPredictItems[i] == true) { modelColumn.Usage = MiningModelColumnUsages.PredictOnly; } else { modelColumn.Usage = MiningModelColumnUsages.Predict; } } } myMiningModel.Update(); }
/*--------------------------------- * Description: Update Mining Model and process StockPredict DB with new MAX, MIN when MAX,MIN are changed by Technology Analysis (IdentifyTrend) * ----------------------------------- */ public static bool UpdateMMbyAnalysis(string sStockCode) { Server svr = ConnectServer(str_Con_Svr); Database db = svr.Databases.GetByName("StockPredict"); Microsoft.AnalysisServices.MiningStructure ms = db.MiningStructures.FindByName(sStockCode); Microsoft.AnalysisServices.MiningModel mm = ms.MiningModels.FindByName(sStockCode); mm.AlgorithmParameters.Remove("MAXIMUM_SERIES_VALUE"); mm.AlgorithmParameters.Remove("MINIMUM_SERIES_VALUE"); // Max, Min Time Series mm.AlgorithmParameters.Add("MAXIMUM_SERIES_VALUE", MAXIMUM_SERIES_VALUE); mm.AlgorithmParameters.Add("MINIMUM_SERIES_VALUE", MINIMUM_SERIES_VALUE); mm.Update(); mm.Process(ProcessType.ProcessFull); // Update parameters into StockForecastModel try { SqlConnection conn = new SqlConnection(str_Con_SQL); conn.Open(); SqlCommand cmdUpdate = conn.CreateCommand(); cmdUpdate.CommandText = "Update StockForecastModel Set MAXIMUM_SERIES_VALUE=" + MAXIMUM_SERIES_VALUE.ToString().Replace(',', '.') + ", MINIMUM_SERIES_VALUE=" + MINIMUM_SERIES_VALUE.ToString().Replace(',', '.') + " Where StockCode='" + sStockCode + "'"; cmdUpdate.ExecuteNonQuery(); cmdUpdate.Dispose(); conn.Close(); } catch (Exception ex) { MessageBox.Show("Không thể cập nhật StockForecastModel!"); return(false); } return(true); }
// 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; }
/*--------------------------------- * Description: Create Mining Model * ----------------------------------- */ public static void CreateMM(Microsoft.AnalysisServices.MiningStructure ms, string strStockCode, bool bMulti) { if (ms.MiningModels.ContainsName(strStockCode)) { ms.MiningModels[strStockCode].Drop(); } Microsoft.AnalysisServices.MiningModel mm = ms.CreateMiningModel(true, strStockCode); mm.Algorithm = MiningModelAlgorithms.MicrosoftTimeSeries; InitialParameters(strStockCode); // 0.1:0.05 -> from 0 to (1-0.1)/0.05=18 mm.AlgorithmParameters.Add("COMPLEXITY_PENALTY", COMPLEXITY_PENALTY); // {5,20,60}, 0:0.1 -> from 0 to (1-0.1)/0.05=18 mm.AlgorithmParameters.Add("PERIODICITY_HINT", "{5,20,60}"); mm.AlgorithmParameters.Add("AUTO_DETECT_PERIODICITY", AUTO_DETECT_PERIODICITY); // Defeult: 1, 10 mm.AlgorithmParameters.Add("HISTORIC_MODEL_COUNT", HISTORIC_MODEL_COUNT); mm.AlgorithmParameters.Add("HISTORIC_MODEL_GAP", HISTORIC_MODEL_GAP); // Max, Min Time Series mm.AlgorithmParameters.Add("MAXIMUM_SERIES_VALUE", MAXIMUM_SERIES_VALUE); mm.AlgorithmParameters.Add("MINIMUM_SERIES_VALUE", MINIMUM_SERIES_VALUE); mm.AllowDrillThrough = true; mm.Columns["ID"].Usage = MiningModelColumnUsages.Key; mm.Columns["ClosePrice"].Usage = MiningModelColumnUsages.Predict; if (strStockCode.ToUpper() != "VNINDEX" && !bMulti) { mm.Columns["OpenPrice"].Usage = MiningModelColumnUsages.Input; mm.Columns["HighPrice"].Usage = MiningModelColumnUsages.Input; mm.Columns["LowPrice"].Usage = MiningModelColumnUsages.Input; //mm.Columns["Volume"].Usage = MiningModelColumnUsages.Input; } mm.Update(); // Update parameters into StockForecastModel UpdateForecastModel(strStockCode); }
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); } }
public static void ProcessUpdateMMTest(Server svr, string strStockCode, bool bCon) { Database db = svr.Databases.GetByName("StockPredict"); Microsoft.AnalysisServices.MiningStructure ms = db.MiningStructures.FindByName(strStockCode); string strMsg, strCap; MessageBoxButtons buttons = MessageBoxButtons.OK; if (ms == null) { strMsg = "Cấu trúc dự báo cho cổ phiếu này không tồn tại!"; strCap = "Mining Structure"; // Displays the MessageBox. MessageBox.Show(strMsg, strCap, buttons, MessageBoxIcon.Error); return; } Microsoft.AnalysisServices.MiningModel mm = ms.MiningModels.FindByName(strStockCode); if (mm == null) { strMsg = "Mô hình dự báo cho cổ phiếu này không tồn tại!"; strCap = "Mining Model"; // Displays the MessageBox. MessageBox.Show(strMsg, strCap, buttons, MessageBoxIcon.Error); return; } // Check exist table: CR ADOMDLib.ExistExpandTable(strStockCode, "_CRT", bCon); // Initial parameters DefaultParam(strStockCode); // Get ToDate from StockForecastModel SqlConnection cn = new SqlConnection(str_Con_SQL); cn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "SELECT ToDate FROM StockForecastModel WHERE StockCode='" + strStockCode + "'"; SqlDataReader rdr = cmd.ExecuteReader(); rdr.Read(); DateTime dtTo = rdr.GetDateTime(0); rdr.Close(); cn.Close(); // Loop mining while (AUTO_DETECT_PERIODICITY < 0.95) { while (COMPLEXITY_PENALTY < 0.95) { while (HISTORIC_MODEL_COUNT < 3) { while (HISTORIC_MODEL_GAP < 15) { if (!UpdateMMTest(mm, strStockCode, dtTo)) { // Update parameters into StockForecastModel UpdateForecastModel(strStockCode); return; } HISTORIC_MODEL_GAP++; } HISTORIC_MODEL_GAP = i_Save_HMG; HISTORIC_MODEL_COUNT++; } HISTORIC_MODEL_COUNT = 1; COMPLEXITY_PENALTY += 0.05; } COMPLEXITY_PENALTY = 0.05; AUTO_DETECT_PERIODICITY += 0.05; } // Get the best parameters UpdateMMTest(mm, strStockCode, dtTo); GetBestParamTest(strStockCode); UpdateForecastModel(strStockCode); }
public static void ProcessUpdateMM(Server svr, string strStockCode, bool bCon) { Database db = svr.Databases.GetByName("StockPredict"); Microsoft.AnalysisServices.MiningStructure ms = db.MiningStructures.FindByName(strStockCode); string strMsg, strCap; MessageBoxButtons buttons = MessageBoxButtons.OK; if (ms == null) { strMsg = "Cấu trúc dự báo cho cổ phiếu này không tồn tại!"; strCap = "Mining Structure"; // Displays the MessageBox. MessageBox.Show(strMsg, strCap, buttons, MessageBoxIcon.Error); return; } Microsoft.AnalysisServices.MiningModel mm = ms.MiningModels.FindByName(strStockCode); if (mm == null) { strMsg = "Mô hình dự báo cho cổ phiếu này không tồn tại!"; strCap = "Mining Model"; // Displays the MessageBox. MessageBox.Show(strMsg, strCap, buttons, MessageBoxIcon.Error); return; } // Check exist table: CR ADOMDLib.ExistExpandTable(strStockCode, "_CR", bCon); // Initial parameters and get count of IDs int iCount_ID = InitialParameters(strStockCode); // Loop mining while (AUTO_DETECT_PERIODICITY < 0.95) { while (COMPLEXITY_PENALTY < 0.95) { while (HISTORIC_MODEL_COUNT < 3) { while (HISTORIC_MODEL_GAP < 15) { HISTORIC_MODEL_GAP++; if (!UpdateMM(mm, strStockCode, false)) { // back to previous //HISTORIC_MODEL_GAP--; //UpdateMM(mm, strStockCode, true); // Update parameters into StockForecastModel UpdateForecastModel(strStockCode); return; } } HISTORIC_MODEL_GAP = i_Save_HMG; HISTORIC_MODEL_COUNT++; } HISTORIC_MODEL_COUNT = 1; COMPLEXITY_PENALTY += 0.05; } COMPLEXITY_PENALTY = 0.05; AUTO_DETECT_PERIODICITY += 0.05; } // Get the best parameters // Get the best parameters UpdateMM(mm, strStockCode, false); GetBestParam(strStockCode); UpdateForecastModel(strStockCode); }
/*--------------------------------- * 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); }