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