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;
}
}
public CubeInfo()
{
g = 0; h = 0; f = 0;
parent = null;
position = new Vector3(1, 1, 1);
color = Color.white;
cubAtt = CubeAttribute.CanMove;
}
/*
* Create Scalar mining column
*/
private ScalarMiningStructureColumn CreateMiningStructureColumn(CubeAttribute attribute, bool isKey)
{
ScalarMiningStructureColumn column = new
ScalarMiningStructureColumn();
column.Name = attribute.Attribute.Name;
//cube attribute is usually modeled as discrete except for key column
column.Content = (isKey ? MiningStructureColumnContents.Key : MiningStructureColumnContents.Discrete);
column.IsKey = isKey;
//bind column source to a cube dimension attribute
column.Source = new CubeAttributeBinding(attribute.ParentCube.ID,
((CubeDimension)attribute.Parent).ID, attribute.Attribute.ID, AttributeBindingType.Name);
//Get the column data type from the attribute key column binding.
column.Type = MiningStructureColumnTypes.GetColumnType(attribute.Attribute.NameColumn.DataType);
return(column);
}
public static List <CubeAttribute> GetCubeAttributesWithSlice(string sQuerySubcubeVerbose, Microsoft.AnalysisServices.MeasureGroup mg)
{
List <CubeAttribute> list = new List <CubeAttribute>();
int iDimensionIndex = 0;
foreach (string sLine in sQuerySubcubeVerbose.Replace("\r\n", "\n").Replace("\r", "\n").Split(new char[] { '\n' }))
{
if (string.IsNullOrEmpty(sLine.Trim()))
{
break; //stop when we get to a blank line
}
System.Text.RegularExpressions.Match match = _regexVerboseCubeDimension.Match(sLine);
string sCubeDimensionName = match.Groups["dimension"].Value;
CubeDimension cd = mg.Parent.Dimensions.FindByName(sCubeDimensionName);
if (cd == null)
{
continue;
}
string sCubeDimensionID = cd.ID;
string sAttributes = match.Groups["attributes"].Value;
string[] arrAttributes = sAttributes.Split(new char[] { ' ' });
for (int iAttribute = 0; iAttribute < arrAttributes.Length && iAttribute < cd.Attributes.Count; iAttribute++)
{
string sAttributeSlice = arrAttributes[iAttribute];
if (sAttributeSlice == "0" || sAttributeSlice == "1" || sAttributeSlice == "*")
{
continue;
}
CubeAttribute ca = cd.Attributes[iAttribute];
list.Add(ca);
}
iDimensionIndex++;
}
return(list);
}
private void buttonOK_Click(object sender, EventArgs e)
{
if (this.InvokeRequired)
{
//important to show the notification on the main thread of BIDS
this.BeginInvoke(new MethodInvoker(delegate() { buttonOK_Click(sender, e); }));
}
else
{
try
{
EnvDTE.Window w = projItem.Open(BIDSViewKinds.Designer); //opens the designer
IDesignerHost designer = (IDesignerHost)w.Object;
IComponentChangeService changesvc = (IComponentChangeService)designer.GetService(typeof(IComponentChangeService));
changesvc.OnComponentChanging(this.cloneCube, null);
dirtiedDimensions = new List <Microsoft.AnalysisServices.Dimension>();
//enable attributes
foreach (TreeNode nodeDimension in treeViewAggregation.Nodes)
{
foreach (TreeNode nodeAttribute in nodeDimension.Nodes)
{
if (!nodeAttribute.Checked)
{
CubeAttribute ca = (CubeAttribute)nodeAttribute.Tag;
ca.AttributeHierarchyOptimizedState = OptimizationType.FullyOptimized;
//if you're trying to enable indexes, make sure the dimension hierarchy is optimized, since you can't make the cube attribute optimized if the dimension attribute isn't
if (ca.Attribute.AttributeHierarchyOptimizedState == OptimizationType.NotOptimized)
{
if (!dirtiedDimensions.Contains(ca.Attribute.Parent))
{
foreach (EnvDTE.ProjectItem pi in projItem.ContainingProject.ProjectItems)
{
if (!(pi.Object is Microsoft.AnalysisServices.Dimension))
{
continue;
}
if ((Microsoft.AnalysisServices.Dimension)pi.Object != ca.Attribute.Parent)
{
continue;
}
bool bIsOpen = pi.get_IsOpen(EnvDTE.Constants.vsViewKindDesigner);
EnvDTE.Window win = null;
if (bIsOpen)
{
foreach (EnvDTE.Window w2 in projItem.DTE.Windows)
{
if (w2.ProjectItem != null && w2.ProjectItem.Document != null && w2.ProjectItem.Document.FullName == pi.Document.FullName)
{
win = w2;
break;
}
}
}
if (win == null)
{
win = pi.Open(EnvDTE.Constants.vsViewKindDesigner);
if (!bIsOpen)
{
win.Visible = false;
}
}
IDesignerHost dimdesigner = (IDesignerHost)win.Object;
IComponentChangeService dimchangesvc = (IComponentChangeService)dimdesigner.GetService(typeof(IComponentChangeService));
dimchangesvc.OnComponentChanging(ca.Attribute.Parent, null);
//perform the update
ca.Attribute.AttributeHierarchyOptimizedState = OptimizationType.FullyOptimized;
dimchangesvc.OnComponentChanged(ca.Attribute.Parent, null, null, null); //marks the dimension designer as dirty
}
dirtiedDimensions.Add(ca.Attribute.Parent);
}
else
{
ca.Attribute.AttributeHierarchyOptimizedState = OptimizationType.FullyOptimized;
}
}
}
else
{
CubeAttribute ca = (CubeAttribute)nodeAttribute.Tag;
ca.AttributeHierarchyOptimizedState = OptimizationType.NotOptimized;
//if you're trying to disable indexes, make sure the hierarchy isn't set to optimized
foreach (CubeHierarchy ch in ca.Parent.Hierarchies)
{
foreach (Microsoft.AnalysisServices.Level l in ch.Hierarchy.Levels)
{
if (l.SourceAttributeID == ca.AttributeID && ch.Enabled)
{
ch.OptimizedState = OptimizationType.NotOptimized;
}
}
}
}
}
}
changesvc.OnComponentChanged(this.cloneCube, null, null, null); //marks the cube designer as dirty
this.DialogResult = DialogResult.OK;
this.Close();
}
catch (Exception ex)
{
if (!string.IsNullOrEmpty(ex.Message))
{
MessageBox.Show("Error saving: " + ex.Message);
}
}
}
}
private void FillTree()
{
Dictionary <string, long> dictHierarchyCardinality = new Dictionary <string, long>();
try
{
AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
restrictions.Add(new AdomdRestriction("CATALOG_NAME", this.liveDB.Name));
restrictions.Add(new AdomdRestriction("CUBE_NAME", this.liveCube.Name));
restrictions.Add(new AdomdRestriction("HIERARCHY_VISIBILITY", 3)); //visible and non-visible hierarchies
foreach (System.Data.DataRow r in adomdConnection.GetSchemaDataSet("MDSCHEMA_HIERARCHIES", restrictions).Tables[0].Rows)
{
dictHierarchyCardinality.Add(Convert.ToString(r["HIERARCHY_UNIQUE_NAME"]), Convert.ToInt64(r["HIERARCHY_CARDINALITY"]));
}
}
catch { }
StringBuilder sbExport = new StringBuilder();
sbExport.Append("Cube Dimension Name").Append("\t").Append("Attribute Name").Append("\t").Append("Number of Slices on this Attribute").Append("\t").Append("Attribute Cardinality").Append("\t").Append("Related Partition Count").Append("\t").Append("Currently Effective Optimized State").Append("\t").Append("Recommendation").AppendLine();
foreach (CubeDimension cd in cloneCube.Dimensions)
{
TreeNode parentNode = treeViewAggregation.Nodes.Add(cd.Name);
parentNode.Tag = cd;
parentNode.ImageIndex = parentNode.SelectedImageIndex = 0;
bool bAllIndexesChecked = true;
foreach (CubeAttribute ca in cd.Attributes)
{
if (ca.AttributeHierarchyEnabled && ca.Attribute.AttributeHierarchyEnabled)
{
CubeAttribute caSliced = null;
foreach (CubeAttribute sliced in dictHitIndexes.Keys)
{
if (sliced.Parent.ID == ca.Parent.ID && sliced.AttributeID == ca.AttributeID)
{
caSliced = sliced;
break;
}
}
string sNodeName = ca.Attribute.Name;
int iIndexHits = 0;
if (caSliced != null)
{
iIndexHits = dictHitIndexes[caSliced];
sNodeName += " (Index hits: " + iIndexHits.ToString("g") + ")";
}
TreeNode attributeNode = parentNode.Nodes.Add(sNodeName);
attributeNode.Tag = ca;
attributeNode.ImageIndex = attributeNode.SelectedImageIndex = 1;
attributeNode.Checked = (caSliced == null);
bAllIndexesChecked = bAllIndexesChecked && attributeNode.Checked;
bool bInEnabledHierarchy = false;
foreach (CubeHierarchy ch in ca.Parent.Hierarchies)
{
foreach (Microsoft.AnalysisServices.Level l in ch.Hierarchy.Levels)
{
if (l.SourceAttributeID == ca.AttributeID && ch.Enabled && ch.OptimizedState == OptimizationType.FullyOptimized)
{
bInEnabledHierarchy = true;
}
}
}
OptimizationType optimized = ca.Attribute.AttributeHierarchyOptimizedState;
if (optimized == OptimizationType.FullyOptimized)
{
optimized = ca.AttributeHierarchyOptimizedState;
}
if (optimized == OptimizationType.NotOptimized && bInEnabledHierarchy)
{
optimized = OptimizationType.FullyOptimized;
}
string sRecommendation = "";
if (optimized == OptimizationType.FullyOptimized && iIndexHits == 0)
{
attributeNode.ForeColor = Color.Black;
sRecommendation = "Disable";
attributeNode.BackColor = Color.Green;
attributeNode.ToolTipText = "Currently indexed but the index was not used during the profiler trace. If left checked, BIDS Helper will disable the indexes when you click OK.";
}
else if (optimized == OptimizationType.NotOptimized)
{
attributeNode.ForeColor = Color.DarkGray;
attributeNode.ToolTipText = "Indexes not currently being built.";
if (iIndexHits > 0)
{
sRecommendation = "Enable";
attributeNode.ForeColor = Color.Black;
attributeNode.BackColor = Color.Red;
attributeNode.ToolTipText = "Currently not indexed but the queries observed during the profiler trace would have used the index if it had been built. If left unchecked, BIDS Helper will re-enable indexing when you click OK.";
}
}
else
{
attributeNode.ForeColor = Color.Black;
attributeNode.ToolTipText = "Indexes are being built and are used during the queries observed during the profiler trace.";
}
long? iCardinality = null;
string sAttributeUniqueName = "[" + ca.Parent.Name + "].[" + ca.Attribute.Name + "]";
if (dictHierarchyCardinality.ContainsKey(sAttributeUniqueName))
{
iCardinality = dictHierarchyCardinality[sAttributeUniqueName];
}
int iPartitions = 0;
try
{
foreach (MeasureGroup mg in liveCube.MeasureGroups)
{
if (mg.Dimensions.Contains(cd.ID))
{
try
{
RegularMeasureGroupDimension rmgd = mg.Dimensions[cd.ID] as RegularMeasureGroupDimension;
if (rmgd == null)
{
continue;
}
if (!AggManager.ValidateAggs.IsAtOrAboveGranularity(ca.Attribute, rmgd))
{
continue;
}
iPartitions += mg.Partitions.Count;
}
catch { }
}
}
}
catch { }
sbExport.Append(cd.Name).Append("\t").Append(ca.Attribute.Name).Append("\t").Append(iIndexHits).Append("\t").Append(iCardinality).Append("\t").Append(iPartitions).Append("\t").Append(optimized.ToString()).Append("\t").Append(sRecommendation).AppendLine();
}
}
parentNode.Checked = bAllIndexesChecked;
}
treeViewAggregation.Sort();
sExport = sbExport.ToString();
}
public List <M2MMatrixCompressionStat> BuildQueries(Cube cube)
{
List <M2MMatrixCompressionStat> listStats = new List <M2MMatrixCompressionStat>();
foreach (MeasureGroup mg in cube.MeasureGroups)
{
if (mg.IsLinked)
{
continue;
}
Dictionary <MeasureGroup, List <ManyToManyMeasureGroupDimension> > dictM2M = new Dictionary <MeasureGroup, List <ManyToManyMeasureGroupDimension> >();
foreach (MeasureGroupDimension mgd in mg.Dimensions)
{
if (mgd is ManyToManyMeasureGroupDimension)
{
ManyToManyMeasureGroupDimension m2mmgd = (ManyToManyMeasureGroupDimension)mgd;
if (!dictM2M.ContainsKey(m2mmgd.MeasureGroup))
{
dictM2M.Add(m2mmgd.MeasureGroup, new List <ManyToManyMeasureGroupDimension>());
}
dictM2M[m2mmgd.MeasureGroup].Add(m2mmgd);
}
}
if (dictM2M.Count > 0)
{
//there are m2m dimensions used by this data measure group
foreach (MeasureGroup intermediateMG in dictM2M.Keys)
{
if (intermediateMG.IsLinked)
{
continue;
}
try
{
List <CubeAttribute> commonDimensions = new List <CubeAttribute>();
foreach (CubeDimension cd in cube.Dimensions)
{
if (mg.Dimensions.Contains(cd.ID) && intermediateMG.Dimensions.Contains(cd.ID))
{
if (mg.Dimensions[cd.ID] is RegularMeasureGroupDimension &&
intermediateMG.Dimensions[cd.ID] is RegularMeasureGroupDimension)
{
//it's a common dimension
RegularMeasureGroupDimension rmgdData = (RegularMeasureGroupDimension)mg.Dimensions[cd.ID];
MeasureGroupAttribute mgaData = GetGranularityAttribute(rmgdData);
RegularMeasureGroupDimension rmgdIntermediate = (RegularMeasureGroupDimension)intermediateMG.Dimensions[cd.ID];
MeasureGroupAttribute mgaIntermediate = GetGranularityAttribute(rmgdIntermediate);
CubeAttribute ca = mgaData.CubeAttribute;
if (mgaData.AttributeID != mgaIntermediate.AttributeID)
{
if (IsParentOf(mgaIntermediate.Attribute, mgaData.Attribute))
{
ca = mgaIntermediate.CubeAttribute;
}
}
commonDimensions.Add(ca);
}
}
}
//fine while we're just doing this for SQL server
MeasureGroupHealthCheckPlugin.sq = "[";
MeasureGroupHealthCheckPlugin.fq = "]";
DsvTableBinding oTblBinding = new DsvTableBinding(intermediateMG.Parent.DataSourceView.ID, MeasureGroupHealthCheckPlugin.GetTableIdForDataItem(intermediateMG.Measures[0].Source));
string sFactQuery = "(" + MeasureGroupHealthCheckPlugin.GetQueryDefinition(intermediateMG.ParentDatabase, intermediateMG, oTblBinding, null) + ")";
List <string> listCommonDimensionsSeen = new List <string>();
string sCommonDimensions = "";
string sCommonDimensionsJoin = "";
foreach (CubeAttribute ca in commonDimensions)
{
RegularMeasureGroupDimension rmgd = (RegularMeasureGroupDimension)intermediateMG.Dimensions[ca.Parent.ID];
if (rmgd is ReferenceMeasureGroupDimension)
{
if (mg.Dimensions.Contains(((ReferenceMeasureGroupDimension)rmgd).IntermediateCubeDimensionID) &&
mg.Dimensions[((ReferenceMeasureGroupDimension)rmgd).IntermediateCubeDimensionID] is RegularMeasureGroupDimension)
{
continue; //skip reference dimensions in the intermediate measure group because it won't change the cardinality
}
else
{
throw new Exception(rmgd.CubeDimension.Name + " dimension in intermediate measure group " + intermediateMG.Name + " is not supported by BIDS Helper M2M Matrix Compression");
}
}
MeasureGroupAttribute mga = rmgd.Attributes[ca.AttributeID];
foreach (DataItem di in mga.KeyColumns)
{
if (di.Source is ColumnBinding)
{
if (!listCommonDimensionsSeen.Contains("[" + ((ColumnBinding)di.Source).ColumnID + "]")) //if this column is already mentioned, then don't mention it again
{
listCommonDimensionsSeen.Add("[" + ((ColumnBinding)di.Source).ColumnID + "]");
if (sCommonDimensionsJoin.Length == 0)
{
sCommonDimensionsJoin += "WHERE ";
}
else
{
sCommonDimensionsJoin += "\r\nAND ";
sCommonDimensions += ", ";
}
sCommonDimensionsJoin += "f.[" + ((ColumnBinding)di.Source).ColumnID + "] = s.[" + ((ColumnBinding)di.Source).ColumnID + "]";
sCommonDimensions += "[" + ((ColumnBinding)di.Source).ColumnID + "]";
}
}
}
}
List <string> listM2MDimensionsSeen = new List <string>();
string sM2MDimensions = "";
string sM2MDimensionsOrderBy = "";
foreach (ManyToManyMeasureGroupDimension m2mmgd in dictM2M[intermediateMG])
{
if (intermediateMG.Dimensions[m2mmgd.CubeDimensionID] is RegularMeasureGroupDimension)
{
RegularMeasureGroupDimension rmgd = (RegularMeasureGroupDimension)intermediateMG.Dimensions[m2mmgd.CubeDimensionID];
if (rmgd is ReferenceMeasureGroupDimension)
{
continue; //won't change
}
if (rmgd is ReferenceMeasureGroupDimension)
{
if (mg.Dimensions.Contains(((ReferenceMeasureGroupDimension)rmgd).IntermediateCubeDimensionID) &&
mg.Dimensions[((ReferenceMeasureGroupDimension)rmgd).IntermediateCubeDimensionID] is ManyToManyMeasureGroupDimension)
{
continue; //skip reference dimensions in the intermediate measure group because it won't change the cardinality
}
else
{
throw new Exception(rmgd.CubeDimension.Name + " dimension in intermediate measure group " + intermediateMG.Name + " is not supported by BIDS Helper M2M Matrix Compression");
}
}
MeasureGroupAttribute mga = GetGranularityAttribute(rmgd);
foreach (DataItem di in mga.KeyColumns)
{
if (di.Source is ColumnBinding)
{
if (!listM2MDimensionsSeen.Contains("[" + ((ColumnBinding)di.Source).ColumnID + "]")) //if this column is already mentioned, then don't mention it again
{
listM2MDimensionsSeen.Add("[" + ((ColumnBinding)di.Source).ColumnID + "]");
if (sM2MDimensions.Length > 0)
{
sM2MDimensions += " + '|' + ";
sM2MDimensionsOrderBy += ", ";
}
sM2MDimensions += "isnull(cast([" + ((ColumnBinding)di.Source).ColumnID + "] as nvarchar(max)),'')";
sM2MDimensionsOrderBy += "[" + ((ColumnBinding)di.Source).ColumnID + "]";
}
}
}
}
}
string sSQL = @"
SELECT (SELECT COUNT(*) FROM " + sFactQuery + @" x) OriginalRecordCount
, COUNT(MatrixKey) MatrixDimensionRecordCount
, SUM(cast(KeyCount AS FLOAT)) CompressedRecordCount
FROM (
SELECT DISTINCT COUNT(*) KeyCount
, MatrixKey = (
SELECT " + sM2MDimensions + @" AS [data()]
FROM " + sFactQuery + @" f
" + sCommonDimensionsJoin + @"
ORDER BY " + sM2MDimensionsOrderBy + @"
FOR XML PATH ('')
)
FROM " + sFactQuery + @" s
GROUP BY " + sCommonDimensions + @"
) SUBQ
";
M2MMatrixCompressionStat stat = new M2MMatrixCompressionStat();
stat.IntermediateMeasureGroup = intermediateMG;
stat.DataMeasureGroup = mg;
stat.SQL = sSQL;
listStats.Add(stat);
}
catch (Exception ex)
{
M2MMatrixCompressionStat stat = new M2MMatrixCompressionStat();
stat.IntermediateMeasureGroup = intermediateMG;
stat.DataMeasureGroup = mg;
stat.Error = ex.Message + "\r\n" + ex.StackTrace;
listStats.Add(stat);
}
}
}
}
return(listStats);
}
/// <summary>
/// Recursive function. Adds nodes to the tree view control.
/// Adds nodes accourding to attribute relationships
/// </summary>
private void AddTreeViewNodeChildren(TreeNode node, CubeAttribute cubeDimAttr , MeasureGroupDimension mgDim)
{
bool bIsAtOrAboveGranularity = ValidateAggs.IsAtOrAboveGranularity(cubeDimAttr.Attribute, mgDim);
if (!bIsAtOrAboveGranularity)
{
node.ForeColor = belowGranularityColor;
}
else if (mgDim is ReferenceMeasureGroupDimension)
{
ReferenceMeasureGroupDimension refDim = (ReferenceMeasureGroupDimension)mgDim;
if (refDim.Materialization == ReferenceDimensionMaterialization.Indirect)
{
node.ForeColor = nonMaterializedColor;
}
}
else if (cubeDimAttr.Attribute.Usage == AttributeUsage.Parent)
{
node.ForeColor = parentChildAttributeColor;
}
foreach (AttributeRelationship attRel in cubeDimAttr.Attribute.AttributeRelationships)
{
CubeAttribute childAttr = cubeDimAttr.Parent.Attributes.Find(attRel.AttributeID);
if (childAttr == null) break;
TreeNode childNode = node.Nodes.Add(childAttr.AttributeID, childAttr.Attribute.Name);
if (!childAttr.AttributeHierarchyEnabled)
{
childNode.NodeFont = new Font(treeViewAggregation.Font, FontStyle.Italic);
childNode.ForeColor = Color.Gray;
}
childNode.Tag = attRel;
AddTreeViewNodeChildren( childNode, childAttr,mgDim);
}
}
/*
* Create Scalar mining column
*/
private ScalarMiningStructureColumn CreateMiningStructureColumn(CubeAttribute attribute, bool isKey)
{
ScalarMiningStructureColumn column = new
ScalarMiningStructureColumn();
column.Name = attribute.Attribute.Name;
//cube attribute is usually modeled as discrete except for key column
column.Content = (isKey ? MiningStructureColumnContents.Key : MiningStructureColumnContents.Discrete);
column.IsKey = isKey;
//bind column source to a cube dimension attribute
column.Source = new CubeAttributeBinding(attribute.ParentCube.ID,
((CubeDimension)attribute.Parent).ID, attribute.Attribute.ID, AttributeBindingType.Name);
//Get the column data type from the attribute key column binding.
column.Type = MiningStructureColumnTypes.GetColumnType(attribute.Attribute.NameColumn.DataType);
return column;
}
/*
* Create mining structures and models for olap
*/
public string CreateCubeMiningStructure(string sStructName, string sAlgorithm, int sCubeName, string sDimensionName, string sKeyColumn,
List<string> lsInputColumns, List<string> lsPredictColumns, List<string> lsMeasureInput, List<string> lsMeasurePredict, List<bool> lbPredictItems,
int parOne, int parTwo)
{
try
{
// connect to cube
Server objServer = new Server();
objServer.Connect("DataSource=" + sServer + ";Initial Catalog=" + sCatalog);
Database objDb = objServer.Databases[sCatalog];
Cube objCube = objDb.Cubes[sCubeName];
DataSourceView myView = objDb.DataSourceViews[0];
// create mining structure
CubeDimension objDimension = objCube.Dimensions.GetByName(sDimensionName);
// drop the existing structures with the same name
Microsoft.AnalysisServices.MiningStructure currentMiningStruct = objDb.MiningStructures.FindByName(sStructName);
if (currentMiningStruct != null)
currentMiningStruct.Drop();
Microsoft.AnalysisServices.MiningStructure myMiningStructure = objDb.MiningStructures.Add(sStructName, sStructName);
myMiningStructure.Source = new CubeDimensionBinding(".", objCube.ID, objDimension.ID);
/***************** Key column *****************/
// key column
CubeAttribute objKey = objCube.Dimensions.GetByName(sDimensionName).Attributes[0];
ScalarMiningStructureColumn objKeyColumn = CreateMiningStructureColumn(objKey, true);
objKeyColumn.Name = sKeyColumn;
myMiningStructure.Columns.Add(objKeyColumn);
/***************** Other columns *****************/
// create mining columns
for (int i = 0; i < lsInputColumns.Count; i++)
{
// get attribute
CubeAttribute objAttribute = new CubeAttribute();
objAttribute = objCube.Dimensions.GetByName(sDimensionName).Attributes[0];
// create mining column
ScalarMiningStructureColumn objColumn = CreateMiningStructureColumn(objAttribute, false);
objColumn.Name = lsInputColumns[i];
myMiningStructure.Columns.Add(objColumn);
/***************** Measure column *****************/
// create mining columns for measures
for (int j = 0; j < lsMeasureInput.Count; j++)
{
MeasureGroup objMeasureGroup = objCube.MeasureGroups[lsMeasureInput[j]];
TableMiningStructureColumn objMeasure = CreateMiningStructureColumn(objMeasureGroup);
objMeasure.Name = lsMeasureInput[j];
objMeasure.Columns.Add(objColumn);
myMiningStructure.Columns.Add(objMeasure);
}
}
/***************** Columns for prediction - will be updated in model *****************/
// create mining columns
for (int i = 0; i < lsPredictColumns.Count; i++)
{
// get attribute
CubeAttribute objAttribute = new CubeAttribute();
objAttribute = objCube.Dimensions.GetByName(sDimensionName).Attributes[0];
// if value = false (input & predict) then skip
if (lbPredictItems[i] == false)
continue;
else
{
// create mining column
ScalarMiningStructureColumn objColumn = CreateMiningStructureColumn(objAttribute, false);
objColumn.Name = lsPredictColumns[i];
myMiningStructure.Columns.Add(objColumn);
}
}
// update
myMiningStructure.Update();
/***************** Mining model *****************/
// create mining models
CreateMiningModel(myMiningStructure, sStructName, sAlgorithm, lsPredictColumns, lsMeasurePredict, lbPredictItems, parOne, parTwo);
// process
myMiningStructure.Process();
return "Success";
}
catch (Exception e)
{
return e.Message;
}
}
// Compare if attributeA is included in AttributeB
private static Boolean IsRedundantAttribute(MeasureGroup mg1, string DimId, string AttributeA, string AttributeB, Boolean CompareEstimatedCount, long OriginalEstimatedCount)
{
if (mg1 == null)
{
return(false);
}
long lngMembersDelta;
float flDeltaRatio;
float flPonderatedDeltaRatio;
long lngEstimatedCountToSendRecursively;
MeasureGroupDimension dimension = mg1.Dimensions.Find(DimId);
if (dimension == null)
{
return(false);
}
CubeAttribute cubeattribute = dimension.CubeDimension.Attributes.Find(AttributeB);
if (cubeattribute == null)
{
return(false);
}
// Check if AttributeA is included in AttributeB by
// standing on AttributeB and recursively searching for AttributeA in all its child relationships
foreach (AttributeRelationship attrRel in cubeattribute.Attribute.AttributeRelationships)
{
CubeAttribute childAttr = cubeattribute.Parent.Attributes.Find(attrRel.AttributeID);
if (attrRel.AttributeID == AttributeA)
{
// if CompareEstimatedCount is turn off, attribute is definitely included
if (!CompareEstimatedCount)
{
return(true);
}
else
{
// Else calculate the delta between Estimated Counts
if (OriginalEstimatedCount > 0)
{
lngMembersDelta = (OriginalEstimatedCount - childAttr.Attribute.EstimatedCount);
}
else
{
lngMembersDelta = (cubeattribute.Attribute.EstimatedCount - childAttr.Attribute.EstimatedCount);
}
// Calculate the ratio between the delta and the child estimated count.
flDeltaRatio = (float)lngMembersDelta / (float)childAttr.Attribute.EstimatedCount;
// Ponderate delta ratio by multiplying it by intMembersDelta
flPonderatedDeltaRatio = flDeltaRatio * (float)lngMembersDelta;
// Testings on different scenarios demostrated that if flPonderatedDeltaRatio > 1000 both attributes have much different cardinality.
if (flPonderatedDeltaRatio < 1000)
{
// if AttributeA is included in AttributeB and besides their cardinality are similar
// then A is definitely included in B.
return(true);
}
}
}
if (childAttr.Attribute.AttributeRelationships.Count > 0)
{
// If in first iteration, OriginalEstimatedCount is null and base Estimated Count es get from AttributeB
// if second or later iteration, it pushes the OriginalEstimatedCount
if (OriginalEstimatedCount == -1)
{
lngEstimatedCountToSendRecursively = cubeattribute.Attribute.EstimatedCount;
}
else
{
lngEstimatedCountToSendRecursively = OriginalEstimatedCount;
}
if (IsRedundantAttribute(mg1, DimId, AttributeA, childAttr.AttributeID, CompareEstimatedCount, lngEstimatedCountToSendRecursively))
{
return(true);
}
}
}
return(false);
}
/*
* Create mining structures and models for olap
*/
public string CreateCubeMiningStructure(string sStructName, string sAlgorithm, int sCubeName, string sDimensionName, string sKeyColumn,
List <string> lsInputColumns, List <string> lsPredictColumns, List <string> lsMeasureInput, List <string> lsMeasurePredict, List <bool> lbPredictItems,
int parOne, int parTwo)
{
try
{
// connect to cube
Server objServer = new Server();
objServer.Connect("DataSource=" + sServer + ";Initial Catalog=" + sCatalog);
Database objDb = objServer.Databases[sCatalog];
Cube objCube = objDb.Cubes[sCubeName];
DataSourceView myView = objDb.DataSourceViews[0];
// create mining structure
CubeDimension objDimension = objCube.Dimensions.GetByName(sDimensionName);
// drop the existing structures with the same name
Microsoft.AnalysisServices.MiningStructure currentMiningStruct = objDb.MiningStructures.FindByName(sStructName);
if (currentMiningStruct != null)
{
currentMiningStruct.Drop();
}
Microsoft.AnalysisServices.MiningStructure myMiningStructure = objDb.MiningStructures.Add(sStructName, sStructName);
myMiningStructure.Source = new CubeDimensionBinding(".", objCube.ID, objDimension.ID);
/***************** Key column *****************/
// key column
CubeAttribute objKey = objCube.Dimensions.GetByName(sDimensionName).Attributes[0];
ScalarMiningStructureColumn objKeyColumn = CreateMiningStructureColumn(objKey, true);
objKeyColumn.Name = sKeyColumn;
myMiningStructure.Columns.Add(objKeyColumn);
/***************** Other columns *****************/
// create mining columns
for (int i = 0; i < lsInputColumns.Count; i++)
{
// get attribute
CubeAttribute objAttribute = new CubeAttribute();
objAttribute = objCube.Dimensions.GetByName(sDimensionName).Attributes[0];
// create mining column
ScalarMiningStructureColumn objColumn = CreateMiningStructureColumn(objAttribute, false);
objColumn.Name = lsInputColumns[i];
myMiningStructure.Columns.Add(objColumn);
/***************** Measure column *****************/
// create mining columns for measures
for (int j = 0; j < lsMeasureInput.Count; j++)
{
MeasureGroup objMeasureGroup = objCube.MeasureGroups[lsMeasureInput[j]];
TableMiningStructureColumn objMeasure = CreateMiningStructureColumn(objMeasureGroup);
objMeasure.Name = lsMeasureInput[j];
objMeasure.Columns.Add(objColumn);
myMiningStructure.Columns.Add(objMeasure);
}
}
/***************** Columns for prediction - will be updated in model *****************/
// create mining columns
for (int i = 0; i < lsPredictColumns.Count; i++)
{
// get attribute
CubeAttribute objAttribute = new CubeAttribute();
objAttribute = objCube.Dimensions.GetByName(sDimensionName).Attributes[0];
// if value = false (input & predict) then skip
if (lbPredictItems[i] == false)
{
continue;
}
else
{
// create mining column
ScalarMiningStructureColumn objColumn = CreateMiningStructureColumn(objAttribute, false);
objColumn.Name = lsPredictColumns[i];
myMiningStructure.Columns.Add(objColumn);
}
}
// update
myMiningStructure.Update();
/***************** Mining model *****************/
// create mining models
CreateMiningModel(myMiningStructure, sStructName, sAlgorithm, lsPredictColumns, lsMeasurePredict, lbPredictItems, parOne, parTwo);
// process
myMiningStructure.Process();
return("Success");
}
catch (Exception e)
{
return(e.Message);
}
}
