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;
}
}
/*---------------------------------
* 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);
}
/*
* 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();
}
public static bool UpdateMMTest(Microsoft.AnalysisServices.MiningModel mm, string strStockCode, DateTime dtTo)
{
mm.AlgorithmParameters.Clear();
// Default:10; 10:5 -> from 0 to (n-10)/5
//mm.AlgorithmParameters.Add("MINIMUM_SUPPORT", 10);
// 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.Update();
mm.Process(ProcessType.ProcessFull);
return(ADOMDLib.CheckResultTest(strStockCode, dtTo));
}
/*---------------------------------
* Description: Update data for Analysis DB and reprocess it
* ----------------------------------- */
public static void UpdateTrainDB(Microsoft.AnalysisServices.Server svr, string sStockCode, bool bAll, DateTime dtFrom, DateTime dtTo, bool bMulti)
{
Database db = svr.Databases.GetByName("Stock");
CreateDataAccessObjects(db, sStockCode, bAll, dtFrom, dtTo, bMulti, false);
Microsoft.AnalysisServices.MiningModel mm = db.MiningStructures.GetByName(sStockCode).MiningModels.GetByName(sStockCode);
// Max, Min Time Series
mm.AlgorithmParameters.Remove("MAXIMUM_SERIES_VALUE");
mm.AlgorithmParameters.Remove("MINIMUM_SERIES_VALUE");
mm.AlgorithmParameters.Add("MAXIMUM_SERIES_VALUE", MAXIMUM_SERIES_VALUE);
mm.AlgorithmParameters.Add("MINIMUM_SERIES_VALUE", MINIMUM_SERIES_VALUE);
mm.Update();
//db.MiningStructures.GetByName(sStockCode).Process(ProcessType.ProcessFull);
//mm.Process(ProcessType.ProcessFull);
db.Process(ProcessType.ProcessFull);
}
// 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);
}
/*
* Create mining model with custom fields and algorithm
*/
private void CreateCustomModel(MiningStructure objStructure, string sAlgorithm, string sModelName, string sKeyColumn, List <string> lPredictColumns, List <bool> lbPredictColumns, int parOne, int parTwo)
{
// drop existing model
if (objStructure.MiningModels.ContainsName(sModelName))
{
objStructure.MiningModels[sModelName].Drop();
}
// Detailed description of the model algorithms is here:
// http://msdn.microsoft.com/en-us/library/ms175595.aspx
// More customisation for these algorithms can be found here:
// http://msdn.microsoft.com/en-us/library/cc280427.aspx
// Also a model example can be found here:
// http://msdn.microsoft.com/en-us/library/ms345087(v=SQL.100).aspx
Microsoft.AnalysisServices.MiningModel myMiningModel = objStructure.CreateMiningModel(true, sModelName);
myMiningModel.Algorithm = sAlgorithm;
switch (sAlgorithm)
{
case MiningModelAlgorithms.MicrosoftClustering:
myMiningModel.AlgorithmParameters.Add("CLUSTERING_METHOD", parOne);
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 (lPredictColumns.Count != 0)
{
// predict columns
for (int i = 0; i < lPredictColumns.Count; i++)
{
Microsoft.AnalysisServices.MiningModelColumn modelColumn = myMiningModel.Columns.GetByName(lPredictColumns[i]);
modelColumn.SourceColumnID = lPredictColumns[i];
if (lbPredictColumns[i] == true)
{
modelColumn.Usage = MiningModelColumnUsages.PredictOnly;
}
else
{
modelColumn.Usage = MiningModelColumnUsages.Predict;
}
}
}
myMiningModel.Update();
}
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);
}
}
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);
}
