/*
* Drop an existing mining model; used in order to avoid the crash if the user wants to add a model with the same name
*/
private void DropExistingMiningModels(MiningStructure objStruct, string sName)
{
foreach (MiningModel objModel in objStruct.MiningModels)
{
if (objModel.Name == sName)
{
objModel.Drop();
break;
}
}
}
/*
* Create mining model for the selected mining strucutre
*/
private void CreateModels(MiningStructure objStructure)
{
MiningModel ClusterModel;
MiningModel TreeModel;
MiningModelColumn mmc;
// Create the Cluster model and set the algorithm
// and parameters
DropExistingMiningModels(objStructure, sModelName);
ClusterModel = objStructure.CreateMiningModel(true, sModelName);
ClusterModel.Algorithm = MiningModelAlgorithms.MicrosoftClustering;// "Microsoft_Clustering";
ClusterModel.AlgorithmParameters.Add("CLUSTER_COUNT", 0);
ClusterModel.Update();
// The CreateMiningModel method adds
// all the structure columns to the collection
// Copy the Cluster model and change the necessary properties
TreeModel = ClusterModel.Clone();
DropExistingMiningModels(objStructure, sModelName + "Generation Trees");
TreeModel.Name = sModelName + "Generation Trees";
TreeModel.ID = sModelName + "Generation Trees";
TreeModel.Algorithm = MiningModelAlgorithms.MicrosoftDecisionTrees;// "Microsoft_Decision_Trees";
TreeModel.AlgorithmParameters.Clear();
TreeModel.Columns["Gender"].Usage = "Predict";
//TreeModel.Columns["PayChannels"].Usage = "Predict";
// Add an aliased copy of the PayChannels table to the trees model
mmc = TreeModel.Columns.Add("MaritalStatus");
mmc.SourceColumnID = "MaritalStatus";
mmc = mmc.Columns.Add("MaritalStatus");
mmc.SourceColumnID = "MaritalStatus";
mmc.Usage = "Key";
// Now set a filter on the PayChannels_Hbo_Encore table and use it
// as input to predict other channels
//TreeModel.Columns["PayChannels_Hbo_Encore"].Filter = "Channel=’HBO’ OR Channel=’Encore’";
// Set a complementary filter on the payChannels predictable
// nested table
//TreeModel.Columns["PayChannels"].Filter = "Channel<>’HBO’ AND Channel<>’Encore’";
objStructure.MiningModels.Add(TreeModel);
// Submit the models to the server
// ToDo: fix this
//TreeModel.Update();
}
/*
* Create mining structure based on selection
*/
public string CreateMiningStructure(List <string> inputColumns, List <string> predictColumns, string sAlgorithm, string sTableName, string sKeyColumn, string sStructureName, List <bool> lbPredictItems, int parOne, int parTwo)
{
try
{
// Connect to the Analysis Service server
Database currentDB = GetCurrentDatabase(sCatalog);
// create a new mining structure
MiningStructure currentStructure = CreateCustomMiningStructure(currentDB, sStructureName, sTableName, sKeyColumn, inputColumns, predictColumns, sAlgorithm, lbPredictItems);
// create a mining model for the selected structure
CreateCustomModel(currentStructure, sAlgorithm, sStructureName, sKeyColumn, predictColumns, lbPredictItems, parOne, parTwo);
// Process Database and structure
currentStructure.Process();
return("Success");
}
catch (Exception e)
{
return(e.Message);
}
}
public bool CreateMiningStructureIfCan()
{
try
{
// init server connection
Server svr = new Server();
svr.Connect("integrated security=SSPI;data source=" + sServer + ";persist security info=False;initial catalog=" + sCatalog);
// Connect to the Analysis Service server
Database currentDB = GetCurrentDatabase(sCatalog);
currentDB.Refresh();
// if current database doesn't exist, then create it
//if (currentDB == null)
// CreateNewDatabase();
// create a new mining structure
MiningStructure currentStructure = CreateMiningStructure(currentDB);
currentStructure.Refresh();
// create a mining model for the selected structure
CreateModels(currentStructure);
// Process Database and structure
currentStructure.Process();
//ProcessDatabase(myDB);
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
SMiningError = e.StackTrace;
}
return(false);
}
/*
* 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 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();
}
/*
* 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 model for the selected mining strucutre
*/
private void CreateModels(MiningStructure objStructure)
{
MiningModel ClusterModel;
MiningModel TreeModel;
MiningModelColumn mmc;
// Create the Cluster model and set the algorithm
// and parameters
DropExistingMiningModels(objStructure, sModelName);
ClusterModel = objStructure.CreateMiningModel(true, sModelName);
ClusterModel.Algorithm = MiningModelAlgorithms.MicrosoftClustering;// "Microsoft_Clustering";
ClusterModel.AlgorithmParameters.Add("CLUSTER_COUNT", 0);
ClusterModel.Update();
// The CreateMiningModel method adds
// all the structure columns to the collection
// Copy the Cluster model and change the necessary properties
TreeModel = ClusterModel.Clone();
DropExistingMiningModels(objStructure, sModelName + "Generation Trees");
TreeModel.Name = sModelName + "Generation Trees";
TreeModel.ID = sModelName + "Generation Trees";
TreeModel.Algorithm = MiningModelAlgorithms.MicrosoftDecisionTrees;// "Microsoft_Decision_Trees";
TreeModel.AlgorithmParameters.Clear();
TreeModel.Columns["Gender"].Usage = "Predict";
//TreeModel.Columns["PayChannels"].Usage = "Predict";
// Add an aliased copy of the PayChannels table to the trees model
mmc = TreeModel.Columns.Add("MaritalStatus");
mmc.SourceColumnID = "MaritalStatus";
mmc = mmc.Columns.Add("MaritalStatus");
mmc.SourceColumnID = "MaritalStatus";
mmc.Usage = "Key";
// Now set a filter on the PayChannels_Hbo_Encore table and use it
// as input to predict other channels
//TreeModel.Columns["PayChannels_Hbo_Encore"].Filter = "Channel=’HBO’ OR Channel=’Encore’";
// Set a complementary filter on the payChannels predictable
// nested table
//TreeModel.Columns["PayChannels"].Filter = "Channel<>’HBO’ AND Channel<>’Encore’";
objStructure.MiningModels.Add(TreeModel);
// Submit the models to the server
// ToDo: fix this
//TreeModel.Update();
}
/*
* Drop an existing mining model; used in order to avoid the crash if the user wants to add a model with the same name
*/
private void DropExistingMiningModels(MiningStructure objStruct, string sName)
{
foreach (MiningModel objModel in objStruct.MiningModels)
{
if (objModel.Name == sName)
{
objModel.Drop();
break;
}
}
}
/*
* 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();
}
/*
* 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;
}
/// <summary>
/// Load a SSAS project file into a SSAS Database
/// </summary>
/// <remarks>
/// TODO: Doesn't support Assemblies, or possibly some other types
/// </remarks>
/// <param name="ssasProjectFile">Path to the .dwproj file for a SSAS Project</param>
/// <returns>AMO Database built from the SSAS project file</returns>
public static Database DeserializeProject(string ssasProjectFile)
{
Database database = new Database();
XmlReader innerReader = null;
XmlNodeList nodeList = null;
XmlNodeList dependencyNodeList = null;
string fullPath = null;
// Verify inputs
if (!File.Exists(ssasProjectFile))
{
throw new ArgumentException(string.Format("'{0}' does not exist", ssasProjectFile));
}
// Get the directory to load all project files
FileInfo fi = new FileInfo(ssasProjectFile);
string ssasProjectDirectory = fi.Directory.FullName + "\\";
// Load the SSAS Project File
XmlReader reader = new XmlTextReader(ssasProjectFile);
XmlDocument doc = new XmlDocument();
doc.Load(reader);
// Load the Database
nodeList = doc.SelectNodes("//Database/FullPath");
fullPath = nodeList[0].InnerText;
innerReader = new XmlTextReader(ssasProjectDirectory + fullPath);
Utils.Deserialize(innerReader, (MajorObject)database);
// Load all the Datasources
nodeList = doc.SelectNodes("//DataSources/ProjectItem/FullPath");
DataSource dataSource = null;
foreach (XmlNode node in nodeList)
{
fullPath = node.InnerText;
innerReader = new XmlTextReader(ssasProjectDirectory + fullPath);
dataSource = new RelationalDataSource();
Utils.Deserialize(innerReader, (MajorObject)dataSource);
database.DataSources.Add(dataSource);
}
// Load all the DatasourceViews
nodeList = doc.SelectNodes("//DataSourceViews/ProjectItem/FullPath");
DataSourceView dataSourceView = null;
foreach (XmlNode node in nodeList)
{
fullPath = node.InnerText;
innerReader = new XmlTextReader(ssasProjectDirectory + fullPath);
dataSourceView = new DataSourceView();
Utils.Deserialize(innerReader, (MajorObject)dataSourceView);
database.DataSourceViews.Add(dataSourceView);
}
// Load all the Roles
nodeList = doc.SelectNodes("//Roles/ProjectItem/FullPath");
Role role = null;
foreach (XmlNode node in nodeList)
{
fullPath = node.InnerText;
innerReader = new XmlTextReader(ssasProjectDirectory + fullPath);
role = new Role();
Utils.Deserialize(innerReader, (MajorObject)role);
database.Roles.Add(role);
}
// Load all the Dimensions
nodeList = doc.SelectNodes("//Dimensions/ProjectItem/FullPath");
Dimension dimension = null;
foreach (XmlNode node in nodeList)
{
fullPath = node.InnerText;
innerReader = new XmlTextReader(ssasProjectDirectory + fullPath);
dimension = new Dimension();
Utils.Deserialize(innerReader, (MajorObject)dimension);
database.Dimensions.Add(dimension);
}
// Load all the Mining Models
nodeList = doc.SelectNodes("//MiningModels/ProjectItem/FullPath");
MiningStructure miningStructure = null;
foreach (XmlNode node in nodeList)
{
fullPath = node.InnerText;
innerReader = new XmlTextReader(ssasProjectDirectory + fullPath);
miningStructure = new MiningStructure();
Utils.Deserialize(innerReader, (MajorObject)miningStructure);
database.MiningStructures.Add(miningStructure);
}
// Load all the Cubes
nodeList = doc.SelectNodes("//Cubes/ProjectItem/FullPath");
Cube cube = null;
foreach (XmlNode node in nodeList)
{
fullPath = node.InnerText;
innerReader = new XmlTextReader(ssasProjectDirectory + fullPath);
cube = new Cube();
Utils.Deserialize(innerReader, (MajorObject)cube);
database.Cubes.Add(cube);
// Process cube dependencies (i.e. partitions
// Little known fact: The Serialize/Deserialize methods DO handle partitions... just not when
// paired with anything else in the cube. We have to do this part ourselves
dependencyNodeList = node.ParentNode.SelectNodes("Dependencies/ProjectItem/FullPath");
foreach (XmlNode dependencyNode in dependencyNodeList)
{
fullPath = dependencyNode.InnerText;
innerReader = ProjectHelper.FixPartitionsFileForDeserialize(ssasProjectDirectory + fullPath, cube);
Cube partitionsCube = new Cube();
Utils.Deserialize(innerReader, (MajorObject)partitionsCube);
MergePartitionCube(cube, partitionsCube);
}
}
return(database);
}
/// <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>
/// 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>
/// 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);
}
}
