/// <summary>
/// Build mining database
/// </summary>
/// <param name="MainServerConnectionString">Main server connection string</param>
/// <param name="AnalysisServerConnectionString">Analysis services connection string</param>
/// <param name="TempServerConnectionString">Temp server connection string (contain views)</param>
/// <param name="strPrefix">Prefix</param>
/// <param name="dt_parametters">Decision Tree Parametters</param>
public static void BuildMiningDatabase(string MainServerConnectionString, string AnalysisServerConnectionString, string TempServerConnectionString, string strPrefix = "PF", DecisionTreeAlgorithmParameters dt_parametter = null)
{
if (dt_parametter == null)
dt_parametter = new DecisionTreeAlgorithmParameters();
string strMiningDBName = "Job Zoom Mining"; //Mining database name (Analysis Service)
string strMiningDataSourceName = "Data Source"; //Mining datasource name (Analysis Service)
string strMiningDataSourceViewName = "Data Source View"; //Mining datasource view name (Analysis Service)
string[] strFactTableNames = getAllMiningTableNames(TempServerConnectionString, strPrefix); //tables in datasource view to mining
string[,] strTableNamesAndKeys = { { "PivotProfile", "ProfileBasicId", "PivotProfile", "ProfileBasicId" }, };
int intDimensionTableCount = 0;
Server objServer = new Server();
Database objDatabase = new Database();
RelationalDataSource objDataSource = new RelationalDataSource();
DataSourceView objDataSourceView = new DataSourceView();
DataSet objDataSet = new DataSet();
Dimension[] objDimensions = new Dimension[intDimensionTableCount];
MiningStructure[] objMiningStructures = new MiningStructure[strFactTableNames.Length];
//Console.WriteLine("Mining creation process started.");
//Console.WriteLine("");
//Console.WriteLine("Step 1. Connecting to the Analysis Services.");
//Console.WriteLine("Step 1. Started!");
objServer = (Server)ConnectAnalysisServices(AnalysisServerConnectionString);
//Console.WriteLine("Step 1. Finished!");
//Console.WriteLine("");
//Console.WriteLine("Step 2. Creating a Database.");
//Console.WriteLine("Step 2. Started!");
objDatabase = (Database)CreateDatabase(objServer, strMiningDBName);
strMiningDBName = objDatabase.Name;
//Console.WriteLine("Step 2. Finished!");
//Console.WriteLine("");
//Console.WriteLine("Step 3. Creating a DataSource.");
//Console.WriteLine("Step 3. Started!");
objDataSource = (RelationalDataSource)CreateDataSource(objServer, objDatabase, strMiningDataSourceName, TempServerConnectionString);
//Console.WriteLine("Step 3. Finished!");
//Console.WriteLine("");
//Console.WriteLine("Step 4. Creating a DataSourceView.");
//Console.WriteLine("Step 4. Started!");
//objDataSet = (DataSet)GenerateDWSchema(strDBServerName, strDBName, strFactTableName, strTableNamesAndKeys, intDimensionTableCount);
objDataSet = (DataSet)GenerateDWSchema(TempServerConnectionString, strPrefix); //Get all mining views
objDataSourceView = (DataSourceView)CreateDataSourceView(objDatabase, objDataSource, objDataSet, strMiningDataSourceViewName);
//Console.WriteLine("Step 4. Finished!");
//Console.WriteLine("");
//Console.WriteLine("Step 5. Createing Mining Structures [with Decision Tree Algorithms]");
//Console.WriteLine("Step 5. Started!");
objMiningStructures = (MiningStructure[])CreateMiningStructures(objDatabase, objDataSourceView, strFactTableNames, new DecisionTreeAlgorithmParameters());
//objDatabase.Process(ProcessType.ProcessFull);
//Console.WriteLine("Step 5. Finished!");
//Console.WriteLine("");
//Console.WriteLine("Step 6. Export mining data to JobZoom Database (Database Engine)");
//Console.WriteLine("Step 6. Started!");
//Console.WriteLine("Preparing... Put website to maintenance mode");
//EXEC WEB SITE MAINTENANCE SERVICE METHOD
//Console.WriteLine("Preparing... Cleaning DecisionTreeNode and DecisionTreeNodeDistribution");
//Console.WriteLine("\nStep 6. Finished!");
//Console.WriteLine("");
exportMiningDataToDB(MainServerConnectionString, AnalysisServerConnectionString, strFactTableNames, strPrefix);
//Console.WriteLine("Export completed! Release website to continuing for using");
//WEBSITE CAN CONTINUE FOR USING
//Console.WriteLine("Process Full...");
//objDatabase.Process(ProcessType.ProcessFull);
//Console.WriteLine("Analysis Service Database created successfully.");
//Console.WriteLine("Step 7. Removing Analysis Database");
//Console.WriteLine("Step 7. Started!");
//Console.WriteLine(deleteDatabase(objServer, objDatabase.Name));
deleteDatabase(objServer, objDatabase.Name);
//Console.WriteLine("Removing Analysis Database completely ...");
//Console.WriteLine("\nStep 7. Finished!");
//Console.WriteLine("Press any key to exit.");
//Console.ReadLine();
}
/// <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);
}
}
/// <summary>
/// Create Mining Structures in Analysis Service
/// </summary>
/// <param name="objDatabase">Analysis Service Database instance</param>
/// <param name="objDataSourceView">Analysis Service DataSourceView instance</param>
/// <param name="strCaseTableNames">Array of mining tables</param>
/// <returns>Array of created Mining Structures</returns>
public static object[] CreateMiningStructures(Database objDatabase, DataSourceView objDataSourceView, string[] strCaseTableNames, DecisionTreeAlgorithmParameters dtParams)
{
MiningStructure[] miningStructures = new MiningStructure[strCaseTableNames.Length];
try
{
for (int i = 0; i < strCaseTableNames.Length; i++)
{
miningStructures[i] = (MiningStructure)GenerateMiningStructure(objDatabase, objDataSourceView, strCaseTableNames[i], dtParams);
}
return miningStructures;
}
catch (Exception ex)
{
throw new Exception("Error in Creating a Mining structure - GenerateMiningStructure. Error Message -> " + ex.Message);
}
}