public static void ShowAggsReport(AggregationDesign aggDesign, string sCorrectAggregationDesignName)
{
List <Agg> aggs = ListAggs(aggDesign, sCorrectAggregationDesignName, aggDesign.ParentCube.Name + " - " + aggDesign.Parent.Name + " measure group");
List <AggDimension> aggDimensions = ListAggDimensions(aggDesign, sCorrectAggregationDesignName, aggDesign.ParentCube.Name + " - " + aggDesign.Parent.Name + " measure group");
ShowWarningsReport(aggs, aggDimensions);
}
private static List <AggDimension> ListAggDimensions(AggregationDesign aggDesign, string sCorrectAggregationDesignName, string sReportTitle)
{
int iHighlight = 0;
List <AggDimension> aggs = new List <AggDimension>();
foreach (Aggregation agg in aggDesign.Aggregations)
{
iHighlight++;
foreach (MeasureGroupDimension mgd in agg.ParentMeasureGroup.Dimensions)
{
AggregationDimension ad = agg.Dimensions.Find(mgd.CubeDimensionID);
if (ad == null)
{
try
{
ad = agg.Dimensions.Add(mgd.CubeDimensionID);
aggs.Add(new AggDimension(ad, sCorrectAggregationDesignName, sReportTitle, (iHighlight % 2 == 0)));
}
finally
{
try
{
agg.Dimensions.Remove(ad);
}
catch { }
}
}
else
{
aggs.Add(new AggDimension(ad, sCorrectAggregationDesignName, sReportTitle, (iHighlight % 2 == 0)));
}
}
}
return(aggs);
}
/// <summary>
/// Merge a cube containing only Partitions and AggregationDesigns with
/// a 'base cube' that contains all Measure Groups present in the 'partition cube'.
/// </summary>
/// <param name="baseCube">A fully populated cube</param>
/// <param name="partitionCube">A cube containing only partitions and aggregation designs</param>
private static void MergePartitionCube(Cube baseCube, Cube partitionCube)
{
MeasureGroup baseMG = null;
foreach (MeasureGroup mg in partitionCube.MeasureGroups)
{
baseMG = baseCube.MeasureGroups.Find(mg.ID);
// Heisenberg principle in action with these objects; use 'for' instead of 'foreach'
if (mg.Partitions.Count > 0)
{
for (int i = 0; i < mg.Partitions.Count; ++i)
{
Partition partitionCopy = mg.Partitions[i].Clone();
baseMG.Partitions.Add(partitionCopy);
}
}
// Heisenberg principle in action with these objects; use 'for' instead of 'foreach'
if (mg.AggregationDesigns.Count > 0)
{
for (int i = 0; i < mg.AggregationDesigns.Count; ++i)
{
AggregationDesign aggDesignCopy = mg.AggregationDesigns[i].Clone();
baseMG.AggregationDesigns.Add(aggDesignCopy);
}
}
}
}
public static void ShowAggsSimilaritiesReport(MeasureGroup mg, string sCorrectAggregationDesignName, Boolean bCountMembers)
{
AggregationDesign aggDesign = mg.AggregationDesigns.GetByName(sCorrectAggregationDesignName);
List <SimilarAgg> aggs = ListSimilarAggs(aggDesign, sCorrectAggregationDesignName, aggDesign.ParentCube.Name + " - " + aggDesign.Parent.Name + " measure group", bCountMembers);
ShowReport(aggs);
}
private static List <SimilarAgg> ListSimilarAggs(AggregationDesign aggDesign, string sCorrectAggregationDesignName, string sReportTitle, Boolean bCountMembers)
{
Boolean bIncluded;
List <SimilarAgg> aggs = new List <SimilarAgg>();
foreach (Aggregation agg in aggDesign.Aggregations)
{
bIncluded = false;
foreach (Aggregation agg2 in aggDesign.Aggregations)
{
if (agg.Name != agg2.Name)
{
if (IsAggregationIncluded(agg, agg2, bCountMembers))
{
aggs.Add(new SimilarAgg(agg, agg2, sCorrectAggregationDesignName, sReportTitle, bCountMembers));
bIncluded = true;
}
}
}
if (!bIncluded)
{
aggs.Add(new SimilarAgg(agg, null, sCorrectAggregationDesignName, sReportTitle, bCountMembers));
}
}
return(aggs);
}
/// <summary>
/// For every selected partition sets the AggregationDesignID property
/// to a current aggregation design.
/// Meaning current aggregation desing applies to partitions selected.
/// </summary>
private void buttonOk_Click(object sender, EventArgs e)
{
int i = 0;
AggregationDesign aggD = mg1.AggregationDesigns.GetByName(strAggDes);
foreach (Partition part in mg1.Partitions)
{
if (listBox1.SelectedIndices.Contains(i))
mg1.Partitions[i].AggregationDesignID = aggD.ID;
else
if (mg1.Partitions[i].AggregationDesignID == aggD.ID)
mg1.Partitions[i].AggregationDesignID = null;
i++;
}
foreach (int index in listBox1.SelectedIndices)
{
mg1.Partitions[index].AggregationDesignID = aggD.ID;
i++;
}
this.Close();
}
private static List<AggDimension> ListAggDimensions(AggregationDesign aggDesign, string sCorrectAggregationDesignName, string sReportTitle)
{
int iHighlight = 0;
List<AggDimension> aggs = new List<AggDimension>();
foreach (Aggregation agg in aggDesign.Aggregations)
{
iHighlight++;
foreach (MeasureGroupDimension mgd in agg.ParentMeasureGroup.Dimensions)
{
AggregationDimension ad = agg.Dimensions.Find(mgd.CubeDimensionID);
if (ad == null)
{
try
{
ad = agg.Dimensions.Add(mgd.CubeDimensionID);
aggs.Add(new AggDimension(ad, sCorrectAggregationDesignName, sReportTitle, (iHighlight % 2 == 0)));
}
finally
{
try
{
agg.Dimensions.Remove(ad);
}
catch { }
}
}
else
{
aggs.Add(new AggDimension(ad, sCorrectAggregationDesignName, sReportTitle, (iHighlight % 2 == 0)));
}
}
}
return aggs;
}
public AggregationPerformanceTester(AggregationDesign aggD, bool TestAgg, bool TestNoAggs, bool TestWithoutSomeAggs)
{
_currentAggD = aggD;
_currentMGPath = aggD.ParentServer.ID + "." + aggD.ParentDatabase.ID + "." + aggD.ParentCube.ID + "." + aggD.Parent.ID + ".";
_testAgg = TestAgg;
_testNoAggs = TestNoAggs;
_testWithoutSomeAggs = TestWithoutSomeAggs;
_totalIterations = (TestAgg ? 1 : 0) + (TestNoAggs ? 1 : 0) + (TestWithoutSomeAggs ? 1 : 0);
}
private static List<Agg> ListAggs(AggregationDesign aggDesign, string sCorrectAggregationDesignName, string sReportTitle)
{
List<Agg> aggs = new List<Agg>();
foreach (Aggregation agg in aggDesign.Aggregations)
{
aggs.Add(new Agg(agg, sCorrectAggregationDesignName, sReportTitle));
}
return aggs;
}
private void UpdateAggCountInListBox(TreeNode nd)
{
listBoxReport.Items.Clear();
if (nd.Parent != null && nd.Parent.Parent != null && nd.Parent.Parent.Tag != null && nd.Parent.Parent.Tag is MeasureGroup)
{
listBoxReport.Items.Add("Aggregation count");
AggregationDesign aggdes = ((MeasureGroup)nd.Parent.Parent.Tag).AggregationDesigns.GetByName((string)nd.Tag);
listBoxReport.Items.Add(aggdes.Aggregations.Count);
}
}
private static List <Agg> ListAggs(AggregationDesign aggDesign, string sCorrectAggregationDesignName, string sReportTitle)
{
List <Agg> aggs = new List <Agg>();
foreach (Aggregation agg in aggDesign.Aggregations)
{
aggs.Add(new Agg(agg, sCorrectAggregationDesignName, sReportTitle));
}
return(aggs);
}
public void Init(AggregationDesign aggD)
{
this.aggD = aggD;
lblServer.Text = aggD.ParentServer.Name;
lblDatabase.Text = aggD.ParentDatabase.Name;
lblCube.Text = aggD.ParentCube.Name;
lblMeasureGroup.Text = aggD.Parent.Name;
lblAggDesign.Text = aggD.Name;
lblStatus.Text = "";
InitASSPLabel();
}
//clicked on this menu option from the Aggregation Designs node under a measure group
private void exportToASQLTableToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
AggregationDesign aggr = ((MeasureGroup)treeView1.SelectedNode.Parent.Parent.Tag).AggregationDesigns.GetByName(treeView1.SelectedNode.Tag.ToString());
PopupExportTableForm(aggr);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
}
}
public void Init(AggregationDesign aggD)
{
this.aggD = aggD;
lblServer.Text = aggD.ParentServer.Name;
lblDatabase.Text = aggD.ParentDatabase.Name;
lblCube.Text = aggD.ParentCube.Name;
lblMeasureGroup.Text = aggD.Parent.Name;
lblAggDesign.Text = aggD.Name;
lblStatus.Text = "";
InitASSPLabel();
}
public void Init(string strAggDesign,
MeasureGroup mg,
string[,] inDimAttributes,
string[] inDimNames ,
string[] inDimIDs)
{
this.Text = this.Text + " Aggregation Design: " + strAggDesign;
mg1 = mg;
aggDes = mg.AggregationDesigns.GetByName(strAggDesign);
dimAttributes = inDimAttributes;
dimNames = inDimNames;
dimIDs = inDimIDs;
DataTable myTable = new DataTable("Aggregations");
DataColumn colItem = new DataColumn("Name", Type.GetType("System.String"));
myTable.Columns.Add(colItem);
colItem = new DataColumn("Aggregations", Type.GetType("System.String"));
myTable.Columns.Add(colItem);
colItem = new DataColumn("Type", Type.GetType("System.String"));
myTable.Columns.Add(colItem);
DataView myDataView = new DataView(myTable);
dataGrid1.DataSource = myDataView;
DataRow NewRow;
foreach (Aggregation agg in aggDes.Aggregations)
{
NewRow = myTable.NewRow();
NewRow["Aggregations"] = ConvertAggToSting(agg);
NewRow["Name"] = agg.Name;
myTable.Rows.Add(NewRow);
}
AddGridStyle();
PopulateTreeView();
checkBoxRelationships.Checked = true;
sychContr = SynchControls.Unknown;
int i = 0;
foreach (DataRow dRow in myDataView.Table.Rows)
{
dataGrid1.CurrentRowIndex = i;
dataGrid1_Click(null, null);
i++;
}
myDataView.AllowNew = false;
}
private void saveModifiedMeasureGroups()
{
try
{
this.Cursor = Cursors.WaitCursor;
foreach (TreeNode nd in treeView1.Nodes[0].Nodes[0].Nodes)
{
if ((nd.Tag is MeasureGroup) && (nd.Text.EndsWith(MODIFIED_SUFFIX)))
{
MeasureGroup cloneMG = ((MeasureGroup)nd.Tag);
MeasureGroup realMG = realCube.MeasureGroups[cloneMG.ID];
//1. Update existing agg designs and add new ones
foreach (AggregationDesign aggDesign in cloneMG.AggregationDesigns)
{
AggregationDesign realAgg = realMG.AggregationDesigns.Find(aggDesign.ID);
if (realAgg == null)
{
realAgg = realMG.AggregationDesigns.Add(aggDesign.Name, aggDesign.ID);
}
aggDesign.CopyTo(realAgg);
}
//2. fix AggregationDesignID on partitions
foreach (Partition part in cloneMG.Partitions)
{
realMG.Partitions[part.ID].AggregationDesignID = part.AggregationDesignID;
}
//3. remove deleted agg designs... do this last so no partition will be invalid
for (int i = 0; i < realMG.AggregationDesigns.Count; i++)
{
AggregationDesign aggDesign = realMG.AggregationDesigns[i];
if (cloneMG.AggregationDesigns.Find(aggDesign.ID) == null)
{
realMG.AggregationDesigns.RemoveAt(i);
i--;
}
}
//no need to run an Update statement from within BIDS
}
}
this.IsDirty = false;
this.Cursor = Cursors.Default;
}
catch (Exception ex)
{
MessageBox.Show("Error during save: " + ex.Message + "\r\n" + ex.StackTrace);
}
}
private void PopupExportTableForm(AggregationDesign aggrD)
{
//MeasureGroup cloneMG = null;
//if (mg != null)
// cloneMG = this.cloneDB.Cubes[mg.Parent.ID].MeasureGroups[mg.ID];
AggManager.ExportTable form1 = new AggManager.ExportTable();
form1.Init(aggrD);
DialogResult res = form1.ShowDialog(this);
if (res != DialogResult.OK)
{
return;
}
}
/// <summary>
/// Create aggregation design
/// </summary>
/// <param name="mg"></param>
/// <param name="sqlHelper"></param>
/// <param name="asMeta"></param>
/// <returns></returns>
public AggregationDesign CREATE_AGGREGATION_DESIGN(MeasureGroup mg, DB_SQLHELPER_BASE sqlHelper
, AS_METADATA asMeta)
{
DataTable agg_design_list = null;
AggregationDesign agg_design = null;
try
{
agg_design_list = asMeta.GET_SSAS_AGGREGATION_DESIGN_SET(sqlHelper, mg.ID);
foreach (DataRow measure in agg_design_list.Rows)
{
String AggregationDesignName = measure["aggregation_design_name"].ToString();
//agg_design=AggregationDesignName;
String AggregationName = measure["aggregation_name"].ToString();
String DimensionID = measure["dimension_id"].ToString();
String AttributeID = measure["attribute_id"].ToString();
if (mg.AggregationDesigns.Find(AggregationDesignName) == null)
{
mg.AggregationDesigns.Add(AggregationDesignName);
}
agg_design = mg.AggregationDesigns[AggregationDesignName];
Aggregation agg = agg_design.Aggregations.Find(AggregationName);
if (agg == null)
{
agg = agg_design.Aggregations.Add(AggregationName, AggregationName);
}
AggregationDimension agg_dim = agg.Dimensions.Find(DimensionID);
if (agg_dim == null)
{
agg.Dimensions.Add(DimensionID);
}
agg.Dimensions[DimensionID].Attributes.Add(AttributeID);
}
}
catch (Exception ex)
{
sqlHelper.ADD_MESSAGE_LOG(ex.Message.ToString(), MESSAGE_TYPE.AGGREGATION_DESIGN, MESSAGE_RESULT_TYPE.Error);
throw (ex);
}
return(agg_design);
}
private void cmdDeleteAggDes_Click(object sender, EventArgs e)
{
try
{
TreeNode nd = treeView1.SelectedNode;
if (MessageBox.Show("Would you like to delete Aggregation design:" + nd.Text + "?", "Delete Aggregation design", MessageBoxButtons.OKCancel) == DialogResult.Cancel)
{
return;
}
//Delete aggregation design from the measure group
MeasureGroup mg = (MeasureGroup)nd.Parent.Parent.Tag;
AggregationDesign aggD = mg.AggregationDesigns.GetByName(nd.Text);
foreach (Partition pt in mg.Partitions)
{
if (pt.AggregationDesignID == aggD.ID)
{
pt.AggregationDesignID = null;
}
}
mg.AggregationDesigns.Remove(aggD.ID);
//Remove agg design from the tree
treeView1.SelectedNode = treeView1.SelectedNode.Parent.Parent;
treeView1.SelectedNode.Nodes.RemoveAt(0);
CreateNode(treeView1.SelectedNode.Nodes, TagAggdesigns, TagAggdesigns, ImgListMetadataFolderIndex);
treeView1.SelectedNode.Nodes[0].Expand();
if (!treeView1.SelectedNode.Text.EndsWith(MODIFIED_SUFFIX))
{
treeView1.SelectedNode.Text = treeView1.SelectedNode.Text + MODIFIED_SUFFIX;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
}
}
/// <summary>
/// Split out the Partitions and AggregationDesignss from a base cube
/// into their own cube for deserialization into a Partitions file
/// </summary>
/// <param name="baseCube">Cube to split</param>
/// <returns>Cube containing only partitions and aggregations</returns>
private static Cube SplitPartitionCube(Cube baseCube)
{
Cube partitionCube = new Cube();
foreach (MeasureGroup mg in baseCube.MeasureGroups)
{
MeasureGroup newMG = new MeasureGroup(mg.Name, mg.ID);
if ((mg.Partitions.Count == 0) && (mg.AggregationDesigns.Count == 0))
{
continue;
}
partitionCube.MeasureGroups.Add(newMG);
// Heisenberg principle in action with these objects; use 'for' instead of 'foreach'
if (mg.Partitions.Count > 0)
{
for (int i = 0; i < mg.Partitions.Count; ++i)
{
Partition partitionCopy = mg.Partitions[i].Clone();
newMG.Partitions.Add(partitionCopy);
}
}
// Heisenberg principle in action with these objects; use 'for' instead of 'foreach'
if (mg.AggregationDesigns.Count > 0)
{
for (int i = 0; i < mg.AggregationDesigns.Count; ++i)
{
AggregationDesign aggDesignCopy = mg.AggregationDesigns[i].Clone();
newMG.AggregationDesigns.Add(aggDesignCopy);
}
}
}
return(partitionCube);
}
private static List<SimilarAgg> ListSimilarAggs(AggregationDesign aggDesign, string sCorrectAggregationDesignName, string sReportTitle, Boolean bCountMembers)
{
Boolean bIncluded;
List<SimilarAgg> aggs = new List<SimilarAgg>();
foreach (Aggregation agg in aggDesign.Aggregations)
{
bIncluded = false;
foreach (Aggregation agg2 in aggDesign.Aggregations)
{
if (agg.Name != agg2.Name)
{
if (IsAggregationIncluded(agg, agg2, bCountMembers))
{
aggs.Add(new SimilarAgg(agg, agg2, sCorrectAggregationDesignName, sReportTitle, bCountMembers));
bIncluded = true;
}
}
}
if (!bIncluded)
aggs.Add(new SimilarAgg(agg, null, sCorrectAggregationDesignName, sReportTitle, bCountMembers));
}
return aggs;
}
Boolean AddAggregationToAggDesign(AggregationDesign aggDesign, string instr, string aggName)
{
try
{
Aggregation agg;
agg = aggDesign.Aggregations.Find(aggName);
if (agg != null)
aggName = "Aggregation " + aggDesign.Aggregations.Count.ToString();
if (aggName == "")
aggName = "Aggregation " + aggDesign.Aggregations.Count.ToString();
agg = aggDesign.Aggregations.Add(aggName, aggName);
string a1;
int dimNum = 0;
int attrNum = 0;
bool newDim = true;
for (int i = 0; i < instr.Length; i++)
{
a1 = instr[i].ToString();
switch (a1)
{
case ",":
dimNum++;
attrNum = -1;
newDim = true;
break;
case "0":
break;
case "1":
if (newDim)
{
agg.Dimensions.Add(dimIDs[dimNum]);
newDim = false;
}
agg.Dimensions[dimIDs[dimNum]].Attributes.Add(dimAttributes[dimNum, attrNum]);
break;
default:
break;
}
attrNum++;
}
return true;
}
catch (Exception ex)
{
MessageBox.Show("Problem saving " + aggName + ": " + ex.Message);
throw new Exception(""); //blank exception means not to report it
}
}
public static void Validate(AggregationDesign aggDesign, string sCorrectAggregationDesignName)
{
List<AggValidationWarning> masterWarnings = CheckAggDesign(aggDesign, sCorrectAggregationDesignName, aggDesign.ParentCube.Name + " - " + aggDesign.Parent.Name + " measure group");
ShowWarningsReport(masterWarnings);
}
void backgroundThread_DoWork(object sender, DoWorkEventArgs e)
{
try
{
if (cube != null)
{
foreach (MeasureGroup mg in cube.MeasureGroups)
{
if (mg.IsLinked)
{
continue;
}
long lngMeasureGroupRowCount = 0;
foreach (AggregationDesign aggD in mg.AggregationDesigns)
{
bool bPartitionUsesAggD = false;
foreach (Partition p in mg.Partitions)
{
if (p.AggregationDesignID == aggD.ID)
{
bPartitionUsesAggD = true;
break;
}
}
if (!bPartitionUsesAggD)
{
continue;
}
this.aggD = aggD;
tester = new AggregationPerformanceTester(aggD, chkTestAgg.Checked, chkTestNoAggs.Checked, chkWithoutIndividualAggs.Checked);
tester.OnProgress += new ProgressChangedEventHandler(tester_OnProgress);
tester.StartTest();
listPerf.AddRange(tester.Results);
missingPerf.AddRange(tester.MissingResults);
if (tester.Results.Length > 0)
{
lngMeasureGroupRowCount += tester.Results[0].PartitionRowCount;
foreach (AggregationPerformanceTester.AggregationPerformance aggP in listPerf)
{
if (aggP.MeasureGroupName == mg.Name)
{
aggP.MeasureGroupRowCount = lngMeasureGroupRowCount;
}
}
}
sErrors += tester.Errors;
if (tester.Cancelled)
{
break;
}
}
}
}
else
{
tester = new AggregationPerformanceTester(this.aggD, chkTestAgg.Checked, chkTestNoAggs.Checked, chkWithoutIndividualAggs.Checked);
tester.OnProgress += new ProgressChangedEventHandler(tester_OnProgress);
tester.StartTest();
listPerf.AddRange(tester.Results);
missingPerf.AddRange(tester.MissingResults);
sErrors = tester.Errors;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
}
try
{
HideForm();
}
catch { }
}
/// <summary>
/// Create measure group
/// </summary>
/// <param name="dsv"></param>
/// <param name="sqlHelper"></param>
/// <param name="asMeta"></param>
/// <param name="cube"></param>
/// <param name="is_rolap"></param>
/// <param name="measure_group_id"></param>
public void CREATE_MEASURE_GROUP(DataSourceView dsv
, DB_SQLHELPER_BASE sqlHelper
, AS_METADATA asMeta
, Cube cube
, int is_rolap_cube
, String measure_group_id = null)
{
try
{
DataTable MGSet = asMeta.GET_SSAS_MEASURE_GROUPS_SET(sqlHelper, is_rolap_cube);
String DSVSchemaName = "";
foreach (DataRow row in MGSet.Rows)
{
String measureGroupID = row["measure_group_id"].ToString();
String measureGroupName = row["measure_group_name"].ToString();
String DependedFactTable = row["depended_fact_table"].ToString();
String KeyNotFound_Action = row["key_not_found_action"].ToString();
int is_rolap_mg = Convert.ToInt16(row["is_rolap_mg"].ToString());
MeasureGroup newMG = AS_API.ADD_MEASURE_GROUP(sqlHelper, cube, measureGroupName, measureGroupID, is_rolap_mg, KeyNotFound_Action);
DataTable dimUsageSet = asMeta.GET_SSAS_DIM_USAGE_SET(sqlHelper, measureGroupID);
DataTable CoreMeasureSet = asMeta.GET_SSAS_CORE_MEASURES_SET(sqlHelper, measureGroupID);
foreach (DataRow measure in CoreMeasureSet.Rows)
{
measureGroupID = measure["measure_group_id"].ToString();
measureGroupName = measure["measure_group_name"].ToString();
String MeasureId = measure["measure_id"].ToString();
String MeasureName = measure["measure_name"].ToString();
String MeasureDataType = measure["measure_data_type"].ToString();
String DBColumn = measure["db_column"].ToString();
DSVSchemaName = measure["dsv_schema_name"].ToString();
String AggregationFunction = measure["aggregation_function"].ToString();
String DisplayFolder = measure["display_folder"].ToString();
String FormatString = measure["format_string"].ToString();
AS_API.ADD_MEASURE_TO_MEASURE_GROUP(
sqlHelper,
newMG,
DSVSchemaName,
DBColumn,
MeasureName,
MeasureId,
DisplayFolder,
FormatString,
AggregationFunction,
true,
MeasureDataType,
MeasureDataType);
}
foreach (DataRow dimUsage in dimUsageSet.Rows)
{
String DimUsageType = dimUsage["dim_usage_type"].ToString();
String InternalDimID = dimUsage["internal_dim_id"].ToString();
String InternalDimAttrID = dimUsage["internal_dim_attrid"].ToString();
DSVSchemaName = dimUsage["dsv_schema_name"].ToString();
String factFKDimColumnName = dimUsage["fact_fk_dim_column_name"].ToString();
String DataType = dimUsage["fact_fk_dim_column_data_type"].ToString();
String DimensionID = dimUsage["dimension_id"].ToString();
String AttributeID = dimUsage["attribute_id"].ToString();
String InternalMeasureGroupID = dimUsage["internal_measure_group_id"].ToString();
switch (DimUsageType.ToLower())
{
case "regular":
DataItem factDataItem = AS_API.CREATE_DATA_ITEM(
sqlHelper,
dsv,
DSVSchemaName,
factFKDimColumnName,
AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME(DataType));
AS_API.ADD_DIM_USAGE_REGULAR_RELATIONSHIP(
sqlHelper,
cube,
newMG,
factDataItem,
DimensionID,
AttributeID);
break;
case "reference":
AS_API.ADD_DIM_USAGE_REFERENCE_RELATIONSHIP(newMG,
DimensionID,
AttributeID,
InternalDimID,
InternalDimAttrID);
break;
case "manytomany":
AS_API.ADD_DIM_USAGE_MANY_RELATIONSHIP(newMG, InternalMeasureGroupID, DimensionID);
break;
case "fact":
AS_API.ADD_DIM_USAGE_FACT_RELATIONSHIP(newMG, InternalDimAttrID, DimensionID);
break;
default:
break;
}
}
AggregationDesign agg_design = CREATE_AGGREGATION_DESIGN(newMG, sqlHelper, asMeta);
CREATE_CUBE_PARTITION_FOR_MEASURE_GROUP(sqlHelper, asMeta, cube, is_rolap_cube, newMG, agg_design, is_rolap_mg, DependedFactTable, DSVSchemaName);
newMG.Update();
}
}
catch (Exception ex)
{
sqlHelper.ADD_MESSAGE_LOG(ex.Message.ToString(), MESSAGE_TYPE.MEASURE_GROUP, MESSAGE_RESULT_TYPE.Error);
throw (ex);
}
}
public static void ShowAggsReport(AggregationDesign aggDesign, string sCorrectAggregationDesignName)
{
List<Agg> aggs = ListAggs(aggDesign, sCorrectAggregationDesignName, aggDesign.ParentCube.Name + " - " + aggDesign.Parent.Name + " measure group");
List<AggDimension> aggDimensions = ListAggDimensions(aggDesign, sCorrectAggregationDesignName, aggDesign.ParentCube.Name + " - " + aggDesign.Parent.Name + " measure group");
ShowWarningsReport(aggs, aggDimensions);
}
private static List<AggValidationWarning> CheckAggDesign(AggregationDesign aggDesign, string sCorrectAggregationDesignName, string sReportTitle)
{
List<AggValidationWarning> masterWarnings = new List<AggValidationWarning>();
//check for m2m agg problems
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
if (aggDim.Attributes.Count > 0 && aggDim.MeasureGroupDimension is ManyToManyMeasureGroupDimension)
{
ManyToManyMeasureGroupDimension m2mDim = (ManyToManyMeasureGroupDimension)aggDim.MeasureGroupDimension;
MeasureGroup intermediateMG = m2mDim.MeasureGroup;
List<MeasureGroupAttribute> missing = new List<MeasureGroupAttribute>();
foreach (MeasureGroupDimension commonDim in intermediateMG.Dimensions)
{
RegularMeasureGroupDimension regCommonDim = commonDim as RegularMeasureGroupDimension;
if (commonDim.CubeDimensionID != aggDim.CubeDimensionID || regCommonDim == null)
{
if (!aggDim.ParentMeasureGroup.Dimensions.Contains(commonDim.CubeDimensionID)) continue; //this isn't a shared dimension
MeasureGroupDimension dataMeasureGroupDim = aggDim.ParentMeasureGroup.Dimensions[commonDim.CubeDimensionID];
if (dataMeasureGroupDim is ManyToManyMeasureGroupDimension) continue; //this shared dimension is m2m on the data measure group so don't include it
//this is a common dimension and the granularity attribute on the intermediate measure group needs to be in the agg
bool bFoundGranularityAgg = false;
MeasureGroupAttribute mga = GetGranularityAttribute(regCommonDim);
AggregationDimension aggCommonDim = agg.Dimensions.Find(commonDim.CubeDimensionID);
if (aggCommonDim != null)
{
if (aggCommonDim.Attributes.Find(mga.AttributeID) != null)
{
bFoundGranularityAgg = true;
}
}
if (!bFoundGranularityAgg && mga != null)
{
missing.Add(mga);
}
}
}
string sWarning = "This aggregation contains many-to-many dimension [" + m2mDim.CubeDimension.Name + "]. It will not be used unless it also contains ";
for (int i = 0; i < missing.Count; i++)
{
MeasureGroupAttribute mga = missing[i];
if (i > 0) sWarning += " and ";
sWarning += "[" + mga.Parent.CubeDimension.Name + "].[" + mga.Attribute.Name + "]";
}
if (missing.Count == 0)
sWarning = "";
else
sWarning += ". ";
sWarning += "The many-to-many dimension [" + m2mDim.CubeDimension.Name + "] itself should not be included in the aggregation to workaround a bug.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
//check for non-materialized reference dimensions
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
if (aggDim.Attributes.Count > 0 && aggDim.MeasureGroupDimension is ReferenceMeasureGroupDimension)
{
ReferenceMeasureGroupDimension refDim = (ReferenceMeasureGroupDimension)aggDim.MeasureGroupDimension;
if (refDim.Materialization == ReferenceDimensionMaterialization.Indirect)
{
string sWarning = "This aggregation contains a non-materialized reference dimension [" + refDim.CubeDimension.Name + "] which is not supported.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
//check whether all measures are semi-additive
bool bAllMeasuresAreSemiAdditive = true;
foreach (Measure m in aggDesign.Parent.Measures)
{
if (m.AggregateFunction == AggregationFunction.Count || m.AggregateFunction == AggregationFunction.DistinctCount || m.AggregateFunction == AggregationFunction.Sum || m.AggregateFunction == AggregationFunction.Min || m.AggregateFunction == AggregationFunction.Max || m.AggregateFunction == AggregationFunction.None)
{
bAllMeasuresAreSemiAdditive = false;
break;
}
else if (m.AggregateFunction == AggregationFunction.ByAccount)
{
//if it's a ByAccount measure, we need to check the list of AggregationFunctions on each account
foreach (Account acct in aggDesign.ParentDatabase.Accounts)
{
if (acct.AggregationFunction == AggregationFunction.Sum) //Sum is the only additive AggregationFunction allowed in account intelligence
{
bAllMeasuresAreSemiAdditive = false;
break;
}
}
}
}
//if all measures are semi-additive, find the Time dimension the semi-additive behavior operates on (which we think is the first Time dimension)
if (bAllMeasuresAreSemiAdditive)
{
CubeDimension semiAdditiveDim = null;
MeasureGroupDimension semiAdditiveMgDim = null;
foreach (CubeDimension cd in aggDesign.ParentCube.Dimensions)
{
MeasureGroupDimension mgd = aggDesign.Parent.Dimensions.Find(cd.ID);
if (mgd != null && mgd.Dimension.Type == DimensionType.Time)
{
semiAdditiveDim = mgd.CubeDimension;
semiAdditiveMgDim = mgd;
break;
}
}
if (semiAdditiveDim == null || semiAdditiveMgDim == null || !(semiAdditiveMgDim is RegularMeasureGroupDimension))
{
//TODO: should we warn about this?
}
else
{
foreach (Aggregation agg in aggDesign.Aggregations)
{
AggregationDimension semiAdditiveAggDim = agg.Dimensions.Find(semiAdditiveDim.ID);
MeasureGroupAttribute granularity = GetGranularityAttribute((RegularMeasureGroupDimension)semiAdditiveMgDim);
if (semiAdditiveAggDim == null || semiAdditiveAggDim.Attributes.Find(granularity.AttributeID) == null)
{
string sWarning = "This measure group contains only semi-additive measures. This aggregation will not be used when semi-additive measure values are retrieved because it does not include the granularity attribute of the semi-additive dimension ([" + semiAdditiveDim.Name + "].[" + granularity.Attribute.Name + "]). (The Exists-with-a-measure-group function can still run off this aggregation, though.)";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
//check for aggs on parent-child attributes
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
foreach (AggregationAttribute attr in aggDim.Attributes)
{
if (attr.Attribute.Usage == AttributeUsage.Parent)
{
string sWarning = "This aggregation contains [" + aggDim.CubeDimension.Name + "].[" + attr.Attribute.Name + "] which is a parent-child attribute. This is not allowed. The aggregation should include [" + aggDim.CubeDimension.Name + "].[" + aggDim.Dimension.KeyAttribute.Name + "] instead.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
//check for aggs on AttributeHierarchyEnabled=false attributes
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
foreach (AggregationAttribute attr in aggDim.Attributes)
{
if (!attr.CubeAttribute.AttributeHierarchyEnabled)
{
string sWarning = "This aggregation contains [" + aggDim.CubeDimension.Name + "].[" + attr.Attribute.Name + "] which is not enabled as an attribute hierarchy. This is not allowed.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
//find a list of ALTER statements that alter the DEFAULT_MEMBER property in the calc script
List<string> attributesWithDefaultMemberAlteredInCalcScript = new List<string>();
System.Text.RegularExpressions.Regex regEx = new System.Text.RegularExpressions.Regex(@"ALTER\s+CUBE\s+(CURRENTCUBE|\[?" + aggDesign.ParentCube.Name + @"\]?)\s+UPDATE\s+DIMENSION\s+(.+?)\s*\,\s*DEFAULT_MEMBER\s+", System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Multiline);
foreach (MdxScript script in aggDesign.ParentCube.MdxScripts)
{
if (script.DefaultScript)
{
StringBuilder sCommands = new StringBuilder();
foreach (Command cmd in script.Commands)
{
sCommands.AppendLine(cmd.Text);
}
foreach (System.Text.RegularExpressions.Match match in regEx.Matches(sCommands.ToString()))
{
try
{
attributesWithDefaultMemberAlteredInCalcScript.Add(match.Groups[2].Captures[0].Value.ToLower());
}
catch { }
}
break;
}
}
//build list of cube dimension attributes that have default members, are not aggregatable, or are marked as AggregationUsage=Full
foreach (MeasureGroupDimension mgDim in aggDesign.Parent.Dimensions)
{
if (mgDim is ManyToManyMeasureGroupDimension) continue; //don't suggest adding any m2m dimensions
CubeDimension cd = mgDim.CubeDimension;
foreach (CubeAttribute ca in cd.Attributes)
{
if (!ca.AttributeHierarchyEnabled) continue;
if (!IsAtOrAboveGranularity(ca.Attribute, mgDim)) continue;
foreach (Aggregation agg in aggDesign.Aggregations)
{
AggregationDimension aggDim = agg.Dimensions.Find(cd.ID);
if (ca.Attribute.Usage == AttributeUsage.Parent)
{
if (!(mgDim is RegularMeasureGroupDimension)) continue;
if (!IsAtOrAboveGranularity(cd.Dimension.KeyAttribute, mgDim)) continue;
//if this is a parent-child attribute and the key isn't in the agg, then check whether the parent-child attribute has a DefaultMember or is aggregatable
if (aggDim == null || aggDim.Attributes.Find(cd.Dimension.KeyAttribute.ID) == null)
{
string sWarning = "";
if (!string.IsNullOrEmpty(ca.Attribute.DefaultMember) || attributesWithDefaultMemberAlteredInCalcScript.Contains(((string)("[" + cd.Name + "].[" + ca.Attribute.Name + "]")).ToLower()))
{
sWarning += "has a DefaultMember";
}
if (!ca.Attribute.IsAggregatable)
{
if (!string.IsNullOrEmpty(sWarning)) sWarning += " and ";
sWarning += "is not aggregatable";
}
if (!string.IsNullOrEmpty(sWarning))
{
sWarning = "This aggregation should probably contain [" + cd.Name + "].[" + cd.Dimension.KeyAttribute.Name + "] because [" + cd.Name + "].[" + ca.Attribute.Name + "] is a parent-child attribute which " + sWarning + ".";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
//for non-parent-child attributes...
else if (aggDim == null || aggDim.Attributes.Find(ca.AttributeID) == null)
{
string sWarning = "";
if (!string.IsNullOrEmpty(ca.Attribute.DefaultMember) || attributesWithDefaultMemberAlteredInCalcScript.Contains(((string)("[" + cd.Name + "].[" + ca.Attribute.Name + "]")).ToLower()))
{
sWarning += "has a DefaultMember";
}
if (!ca.Attribute.IsAggregatable)
{
if (!string.IsNullOrEmpty(sWarning)) sWarning += " and ";
sWarning += "is not aggregatable";
}
if (ca.AggregationUsage == AggregationUsage.Full)
{
if (!string.IsNullOrEmpty(sWarning)) sWarning += " and ";
sWarning += "is marked as AggregationUsage=Full";
}
if (aggDim != null && AggContainsChild(aggDim, ca.Attribute)) continue; //if this attribute is redundant, then no need to warn about it
if (!string.IsNullOrEmpty(sWarning))
{
sWarning = "This aggregation should probably contain [" + cd.Name + "].[" + ca.Attribute.Name + "] which " + sWarning + ".";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
}
//look for aggs with redundant attributes
foreach (Aggregation agg in aggDesign.Aggregations)
{
bool bHasRedundancy = false;
foreach (AggregationDimension aggDim in agg.Dimensions)
{
foreach (AggregationAttribute attr in aggDim.Attributes)
{
if (AggContainsParent(aggDim, attr.Attribute))
{
bHasRedundancy = true;
break;
}
}
if (bHasRedundancy) break;
}
if (bHasRedundancy)
{
string sWarning = "This aggregation contains redundant attributes which unnecessarily bloat the size of the aggregation.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
//check for aggs on below granularity attributes
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
foreach (AggregationAttribute attr in aggDim.Attributes)
{
if (!IsAtOrAboveGranularity(attr.Attribute, aggDim.MeasureGroupDimension))
{
string sWarning = "This aggregation contains [" + aggDim.CubeDimension.Name + "].[" + attr.Attribute.Name + "] which is below granularity. This is not allowed.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
return masterWarnings;
}
void HandleTraceEvent(MyTraceEventArgs e)
{
if (e.EventClass == TraceEventClass.QueryEnd &&
(e.EventSubclass == TraceEventSubclass.MdxQuery ||
e.EventSubclass == TraceEventSubclass.DAXQuery) &&
e.DatabaseName == liveDB.Name)
{
iQueries++;
}
else if (e.EventClass == TraceEventClass.GetDataFromAggregation && e.DatabaseName == liveDB.Name && e.ObjectPath.StartsWith(sCubePath))
{
string sMeasureGroupID = e.ObjectPath.Substring(sCubePath.Length);
string sPartitionID = sMeasureGroupID.Substring(sMeasureGroupID.IndexOf('.') + 1);
sMeasureGroupID = sMeasureGroupID.Substring(0, sMeasureGroupID.IndexOf('.'));
MeasureGroup mg = cloneCube.MeasureGroups.Find(sMeasureGroupID);
if (cloneMG == null) //if checking for hit aggs on entire cube
{
if (mg == null)
{
return;
}
}
else
{
if (mg == null || sMeasureGroupID != cloneMG.ID)
{
return;
}
}
MeasureGroup liveMG = this.liveCube.MeasureGroups.Find(sMeasureGroupID);
if (liveMG == null)
{
return;
}
Partition livePartition = liveMG.Partitions.Find(sPartitionID);
if (livePartition == null)
{
return;
}
string sAggID = e.TextData.Split(new char[] { '\r', '\n' })[0];
if (livePartition.AggregationDesign == null)
{
return;
}
Aggregation liveAgg = livePartition.AggregationDesign.Aggregations.Find(sAggID);
if (liveAgg == null)
{
return;
}
//found this aggregation on the live cube... now find the equivalent agg in the cloned (local) cube
AggregationDesign cloneAggDesign = mg.AggregationDesigns.Find(liveAgg.Parent.ID);
if (cloneAggDesign == null)
{
return;
}
Aggregation cloneAgg = cloneAggDesign.Aggregations.Find(sAggID);
if (cloneAgg == null)
{
return;
}
lock (dictHitAggs)
{
if (dictHitAggs.ContainsKey(cloneAgg))
{
dictHitAggs[cloneAgg]++;
}
else
{
dictHitAggs.Add(cloneAgg, 1);
}
}
iAggHits++;
}
}
/// <summary>
/// Helper function receives a string in format like 0001000,001001
/// translates string into aggregation and adds it to aggegation design
/// </summary>
Boolean AddAggregationToAggDesign(AggregationDesign aggDesign, string instr, int aggNum, string strAggPrefix)
{
int originalAggNum = aggNum;
try
{
Aggregation agg;
string aggName = strAggPrefix + aggNum.ToString();
while (aggDesign.Aggregations.Find(aggName) != null)
{
aggName = strAggPrefix + (++aggNum).ToString();
}
agg = aggDesign.Aggregations.Add(aggName, aggName);
string a1;
int dimNum = 0;
int attrNum = 0;
bool newDim = true;
for (int i = 0; i < instr.Length; i++)
{
a1 = instr[i].ToString();
switch (a1)
{
case ",":
dimNum++;
attrNum = -1;
newDim = true;
break;
case "0":
break;
case "1":
if (newDim)
{
agg.Dimensions.Add(dimIDs[dimNum]);
newDim = false;
}
agg.Dimensions[dimIDs[dimNum]].Attributes.Add(dimAttributes[dimNum, attrNum]);
break;
default:
break;
}
attrNum++;
}
return(true);
}
catch (Exception ex)
{
MessageBox.Show("Error saving aggregation #" + (originalAggNum + 1) + ": " + ex.Message);
throw new Exception(""); //blank exception means not to report again
}
}
private void testAggregationPerformanceToolStripMenuItem1_Click_1(object sender, EventArgs e)
{
Server s = new Server();
try
{
AggregationDesign aggD = ((MeasureGroup)treeView1.SelectedNode.Parent.Parent.Tag).AggregationDesigns.GetByName(treeView1.SelectedNode.Tag.ToString());
if (aggD.Parent.IsLinked)
{
MessageBox.Show("This measure group is linked.");
return;
}
string serverName = "";
string databaseName = "";
if (aggD.ParentServer != null)
{
// if we are in Online mode there will be a parent server
serverName = aggD.ParentServer.Name;
databaseName = aggD.ParentDatabase.Name;
s.Connect(aggD.ParentServer.ConnectionString);
}
else
{
// if we are in Project mode we will use the server name from
// the deployment settings
DeploymentSettings deploySet = new DeploymentSettings(mProjItem);
serverName = deploySet.TargetServer;
databaseName = deploySet.TargetDatabase; //use the target database instead of selectedCube.Parent.Name because selectedCube.Parent.Name only reflects the last place it was deployed to, and we want the user to be able to use the deployment settings to control which deployed server/database to check against
s.Connect("Data Source=" + serverName);
}
Database db = s.Databases.FindByName(databaseName);
if (db == null)
{
MessageBox.Show("Database " + databaseName + " isn't deployed to server " + serverName + ".");
return;
}
Cube cube = db.Cubes.Find(realCube.ID);
if (cube == null)
{
MessageBox.Show("Cube " + realCube.Name + " isn't deployed to database " + databaseName + " on server " + serverName + ".");
return;
}
MeasureGroup liveMG = cube.MeasureGroups.Find(aggD.Parent.ID);
if (liveMG == null)
{
MessageBox.Show("Measure group " + aggD.Parent.Name + " in cube " + realCube.Name + " isn't deployed to database " + databaseName + " on server " + serverName + ".");
return;
}
AggregationDesign liveAggD = liveMG.AggregationDesigns.Find(aggD.ID);
if (liveMG == null)
{
MessageBox.Show("Agg design " + aggD.Name + " in measure group " + aggD.Parent.Name + " in cube " + realCube.Name + " isn't deployed to database " + databaseName + " on server " + serverName + ".");
return;
}
AggregationPerformanceProgress progressForm = new AggregationPerformanceProgress();
progressForm.Init(liveAggD);
progressForm.ShowDialog(this);
if (progressForm.Results.Count > 0)
{
OpenAggPerfReport(progressForm.Results, progressForm.MissingResults, progressForm.chkWithoutIndividualAggs.Checked);
}
else if (progressForm.Started)
{
if (string.IsNullOrEmpty(progressForm.Errors))
{
MessageBox.Show("No processed aggregations found in agg design " + liveAggD.Name + " in measure group " + liveMG.Name + " in cube " + liveMG.Parent.Name + " on database " + liveMG.ParentDatabase.Name + " on server " + liveMG.ParentServer.Name + ".");
}
else
{
MessageBox.Show(progressForm.Errors);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
}
finally
{
try
{
s.Disconnect();
}
catch { }
}
}
/// <summary>
/// CREATE_CUBE_PARTITION_FOR_MEASURE_GROUP
/// </summary>
/// <param name="sqlHelper"></param>
/// <param name="asMeta"></param>
/// <param name="cube"></param>
/// <param name="is_rolap_cube"></param>
/// <param name="mg"></param>
/// <param name="aggregation_design"></param>
/// <param name="is_rolap_mg"></param>
/// <param name="depended_fact_table"></param>
/// <param name="DSVSchemaName"></param>
public void CREATE_CUBE_PARTITION_FOR_MEASURE_GROUP(
DB_SQLHELPER_BASE sqlHelper
, AS_METADATA asMeta
, Cube cube
, int is_rolap_cube
, MeasureGroup mg
, AggregationDesign aggregation_design
, int is_rolap_mg
, String depended_fact_table
, String DSVSchemaName)
{
DataTable partition_date_filter = asMeta.GET_SSAS_PARTITION_SET(sqlHelper, mg.ID);
String aggregation_design_id = null;
if (aggregation_design != null)
{
aggregation_design_id = aggregation_design.ID.ToString();
}
if (partition_date_filter != null && partition_date_filter.Rows != null && partition_date_filter.Rows.Count > 0)
{
String factFKDimColumnName = partition_date_filter.Rows[0]["fact_fk_dim_column_name"].ToString();
int month_volumn_per_partition = Convert.ToInt32(CONFIGURATION_HELPER.GET_METADATA_PROPERTY("month_volumn_per_partition"));
int year_volumn_per_cube = Convert.ToInt32(CONFIGURATION_HELPER.GET_METADATA_PROPERTY("year_volumn_per_cube"));
int partitionCount = 0;
partitionCount = year_volumn_per_cube * 12 / month_volumn_per_partition;
for (int i = 1; i <= partitionCount + 1; i++)
{
String partitionSelectQuery = "";// String.Format("SELECT * FROM {0} WHERE 1=1 ", DSVSchemaName);
//if rolap cube, and current mg is molap, then add where 1=2 filter, select * from tb_name where 1=1 and 1=2
if (is_rolap_cube == 1 && is_rolap_mg == 0)
{
partitionSelectQuery = partitionSelectQuery + " AND 1=2";
}
//if rolap cube, then no need additional date column filter, if molap , then need date column filter,
//select * from tb_name where 1=1 and dateid>=20100101 and dateid<20100201
if (is_rolap_cube == 0)
{
partitionSelectQuery = partitionSelectQuery + " " + asMeta.GET_SSAS_PARTITION_FILTER(factFKDimColumnName, i, month_volumn_per_partition);
}
AS_API.CREATE_MOLAP_PARTITION(
sqlHelper,
mg,
"DW_DataSource",
mg.ID.ToString() + "_" + i.ToString(),
DSVSchemaName,
partitionSelectQuery,
aggregation_design_id,
is_rolap_mg,
depended_fact_table
);
//if rolap cube, then only need one partition
if (is_rolap_cube == 1)
{
break;
}
}
}
else
{
sqlHelper.ADD_MESSAGE_LOG(String.Format("Create cube partition-> No partition date column been detected for mg {0}", mg.ID), MESSAGE_TYPE.PARTITION, MESSAGE_RESULT_TYPE.Warning);
AS_API.CREATE_MOLAP_PARTITION(
sqlHelper,
mg,
"DW_DataSource",
mg.ID.ToString() + "_1",
DSVSchemaName,
"",
aggregation_design_id,
is_rolap_mg,
depended_fact_table
);
}
}
public void Init(AggregationDesign aggrD)
{
this.aggrDesign = aggrD;
}
public void Init(MeasureGroup mg,
string strParition,
EnvDTE.ProjectItem projItem)
{
try
{
bool IsOnlineMode = false;
Cube selectedCube = projItem.Object as Cube;
string serverName = "";
string databaseName = "";
string connectionString = "";
if ((selectedCube != null) && (selectedCube.ParentServer != null))
{
// if we are in Online mode there will be a parent server
serverName = selectedCube.ParentServer.Name;
databaseName = selectedCube.Parent.Name;
IsOnlineMode = true;
connectionString = selectedCube.ParentServer.ConnectionString;
}
else
{
// if we are in Project mode we will use the server name from
// the deployment settings
DeploymentSettings deploySet = new DeploymentSettings(projItem);
serverName = deploySet.TargetServer;
databaseName = deploySet.TargetDatabase; //use the target database instead of selectedCube.Parent.Name because selectedCube.Parent.Name only reflects the last place it was deployed to, and we want the user to be able to use the deployment settings to control which deployed server/database to check against
connectionString = "Data Source=" + serverName;
}
mg1 = mg;
part1 = mg.Partitions.FindByName(strParition);
aggDes = part1.AggregationDesign;
this.Text = " Aggregation sizes for partition " + strParition;
//lblSize.Text = part1.EstimatedRows.ToString() + " records"; //base this not on estimated rows but on actual rows... see below
lablPartName.Text = strParition;
txtServerNote.Text = string.Format("Note: The Partition size details have been taken from the currently deployed '{1}' database on the '{0}' server, " +
"which is the one currently configured as the deployment target.", serverName, databaseName);
txtServerNote.Visible = !IsOnlineMode;
//--------------------------------------------------------------------------------
// Open ADOMD connection to the server and issue DISCOVER_PARTITION_STAT request to get aggregation sizes
//--------------------------------------------------------------------------------
AdomdConnection adomdConnection = new AdomdConnection(connectionString);
adomdConnection.Open();
partitionDetails = adomdConnection.GetSchemaDataSet(AdomdSchemaGuid.PartitionStat, new object[] { databaseName, mg1.Parent.Name, mg1.Name, strParition });
DataColumn colItem1 = new DataColumn("Percentage", Type.GetType("System.String"));
partitionDetails.Tables[0].Columns.Add(colItem1);
AddGridStyle();
dataGrid1.DataSource = partitionDetails.Tables[0];
long iPartitionRowCount = 0;
if (partitionDetails.Tables[0].Rows.Count > 0)
{
iPartitionRowCount = Convert.ToInt64(partitionDetails.Tables[0].Rows[0]["AGGREGATION_SIZE"]);
}
lblSize.Text = iPartitionRowCount + " records";
double ratio = 0;
foreach (DataRow row in partitionDetails.Tables[0].Rows)
{
ratio = 100.0 * ((long)row["AGGREGATION_SIZE"] / (double)iPartitionRowCount);
row["Percentage"] = ratio.ToString("#0.00") + "%";
}
CurrencyManager cm = (CurrencyManager)this.BindingContext[dataGrid1.DataSource, dataGrid1.DataMember];
((DataView)cm.List).AllowNew = false;
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message);
try
{
this.Close();
}
catch { }
}
}
public static void Validate(AggregationDesign aggDesign, string sCorrectAggregationDesignName)
{
List <AggValidationWarning> masterWarnings = CheckAggDesign(aggDesign, sCorrectAggregationDesignName, aggDesign.ParentCube.Name + " - " + aggDesign.Parent.Name + " measure group");
ShowWarningsReport(masterWarnings);
}
private static List <AggValidationWarning> CheckAggDesign(AggregationDesign aggDesign, string sCorrectAggregationDesignName, string sReportTitle)
{
List <AggValidationWarning> masterWarnings = new List <AggValidationWarning>();
//check for m2m agg problems
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
if (aggDim.Attributes.Count > 0 && aggDim.MeasureGroupDimension is ManyToManyMeasureGroupDimension)
{
ManyToManyMeasureGroupDimension m2mDim = (ManyToManyMeasureGroupDimension)aggDim.MeasureGroupDimension;
MeasureGroup intermediateMG = m2mDim.MeasureGroup;
List <MeasureGroupAttribute> missing = new List <MeasureGroupAttribute>();
foreach (MeasureGroupDimension commonDim in intermediateMG.Dimensions)
{
RegularMeasureGroupDimension regCommonDim = commonDim as RegularMeasureGroupDimension;
if (commonDim.CubeDimensionID != aggDim.CubeDimensionID || regCommonDim == null)
{
if (!aggDim.ParentMeasureGroup.Dimensions.Contains(commonDim.CubeDimensionID))
{
continue; //this isn't a shared dimension
}
MeasureGroupDimension dataMeasureGroupDim = aggDim.ParentMeasureGroup.Dimensions[commonDim.CubeDimensionID];
if (dataMeasureGroupDim is ManyToManyMeasureGroupDimension)
{
continue; //this shared dimension is m2m on the data measure group so don't include it
}
//this is a common dimension and the granularity attribute on the intermediate measure group needs to be in the agg
bool bFoundGranularityAgg = false;
MeasureGroupAttribute mga = GetGranularityAttribute(regCommonDim);
AggregationDimension aggCommonDim = agg.Dimensions.Find(commonDim.CubeDimensionID);
if (aggCommonDim != null)
{
if (aggCommonDim.Attributes.Find(mga.AttributeID) != null)
{
bFoundGranularityAgg = true;
}
}
if (!bFoundGranularityAgg && mga != null)
{
missing.Add(mga);
}
}
}
string sWarning = "This aggregation contains many-to-many dimension [" + m2mDim.CubeDimension.Name + "]. It will not be used unless it also contains ";
for (int i = 0; i < missing.Count; i++)
{
MeasureGroupAttribute mga = missing[i];
if (i > 0)
{
sWarning += " and ";
}
sWarning += "[" + mga.Parent.CubeDimension.Name + "].[" + mga.Attribute.Name + "]";
}
if (missing.Count == 0)
{
sWarning = "";
}
else
{
sWarning += ". ";
}
sWarning += "The many-to-many dimension [" + m2mDim.CubeDimension.Name + "] itself should not be included in the aggregation to workaround a bug.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
//check for non-materialized reference dimensions
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
if (aggDim.Attributes.Count > 0 && aggDim.MeasureGroupDimension is ReferenceMeasureGroupDimension)
{
ReferenceMeasureGroupDimension refDim = (ReferenceMeasureGroupDimension)aggDim.MeasureGroupDimension;
if (refDim.Materialization == ReferenceDimensionMaterialization.Indirect)
{
string sWarning = "This aggregation contains a non-materialized reference dimension [" + refDim.CubeDimension.Name + "] which is not supported.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
//check whether all measures are semi-additive
bool bAllMeasuresAreSemiAdditive = true;
foreach (Measure m in aggDesign.Parent.Measures)
{
if (m.AggregateFunction == AggregationFunction.Count || m.AggregateFunction == AggregationFunction.DistinctCount || m.AggregateFunction == AggregationFunction.Sum || m.AggregateFunction == AggregationFunction.Min || m.AggregateFunction == AggregationFunction.Max || m.AggregateFunction == AggregationFunction.None)
{
bAllMeasuresAreSemiAdditive = false;
break;
}
else if (m.AggregateFunction == AggregationFunction.ByAccount)
{
//if it's a ByAccount measure, we need to check the list of AggregationFunctions on each account
foreach (Account acct in aggDesign.ParentDatabase.Accounts)
{
if (acct.AggregationFunction == AggregationFunction.Sum) //Sum is the only additive AggregationFunction allowed in account intelligence
{
bAllMeasuresAreSemiAdditive = false;
break;
}
}
}
}
//if all measures are semi-additive, find the Time dimension the semi-additive behavior operates on (which we think is the first Time dimension)
if (bAllMeasuresAreSemiAdditive)
{
CubeDimension semiAdditiveDim = null;
MeasureGroupDimension semiAdditiveMgDim = null;
foreach (CubeDimension cd in aggDesign.ParentCube.Dimensions)
{
MeasureGroupDimension mgd = aggDesign.Parent.Dimensions.Find(cd.ID);
if (mgd != null && mgd.Dimension.Type == DimensionType.Time)
{
semiAdditiveDim = mgd.CubeDimension;
semiAdditiveMgDim = mgd;
break;
}
}
if (semiAdditiveDim == null || semiAdditiveMgDim == null || !(semiAdditiveMgDim is RegularMeasureGroupDimension))
{
//TODO: should we warn about this?
}
else
{
foreach (Aggregation agg in aggDesign.Aggregations)
{
AggregationDimension semiAdditiveAggDim = agg.Dimensions.Find(semiAdditiveDim.ID);
MeasureGroupAttribute granularity = GetGranularityAttribute((RegularMeasureGroupDimension)semiAdditiveMgDim);
if (semiAdditiveAggDim == null || semiAdditiveAggDim.Attributes.Find(granularity.AttributeID) == null)
{
string sWarning = "This measure group contains only semi-additive measures. This aggregation will not be used when semi-additive measure values are retrieved because it does not include the granularity attribute of the semi-additive dimension ([" + semiAdditiveDim.Name + "].[" + granularity.Attribute.Name + "]). (The Exists-with-a-measure-group function can still run off this aggregation, though.)";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
//check for aggs on parent-child attributes
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
foreach (AggregationAttribute attr in aggDim.Attributes)
{
if (attr.Attribute.Usage == AttributeUsage.Parent)
{
string sWarning = "This aggregation contains [" + aggDim.CubeDimension.Name + "].[" + attr.Attribute.Name + "] which is a parent-child attribute. This is not allowed. The aggregation should include [" + aggDim.CubeDimension.Name + "].[" + aggDim.Dimension.KeyAttribute.Name + "] instead.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
//check for aggs on AttributeHierarchyEnabled=false attributes
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
foreach (AggregationAttribute attr in aggDim.Attributes)
{
if (!attr.CubeAttribute.AttributeHierarchyEnabled)
{
string sWarning = "This aggregation contains [" + aggDim.CubeDimension.Name + "].[" + attr.Attribute.Name + "] which is not enabled as an attribute hierarchy. This is not allowed.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
//find a list of ALTER statements that alter the DEFAULT_MEMBER property in the calc script
List <string> attributesWithDefaultMemberAlteredInCalcScript = new List <string>();
System.Text.RegularExpressions.Regex regEx = new System.Text.RegularExpressions.Regex(@"ALTER\s+CUBE\s+(CURRENTCUBE|\[?" + aggDesign.ParentCube.Name + @"\]?)\s+UPDATE\s+DIMENSION\s+(.+?)\s*\,\s*DEFAULT_MEMBER\s+", System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Multiline);
foreach (MdxScript script in aggDesign.ParentCube.MdxScripts)
{
if (script.DefaultScript)
{
StringBuilder sCommands = new StringBuilder();
foreach (Command cmd in script.Commands)
{
sCommands.AppendLine(cmd.Text);
}
foreach (System.Text.RegularExpressions.Match match in regEx.Matches(sCommands.ToString()))
{
try
{
attributesWithDefaultMemberAlteredInCalcScript.Add(match.Groups[2].Captures[0].Value.ToLower());
}
catch { }
}
break;
}
}
//build list of cube dimension attributes that have default members, are not aggregatable, or are marked as AggregationUsage=Full
foreach (MeasureGroupDimension mgDim in aggDesign.Parent.Dimensions)
{
if (mgDim is ManyToManyMeasureGroupDimension)
{
continue; //don't suggest adding any m2m dimensions
}
CubeDimension cd = mgDim.CubeDimension;
foreach (CubeAttribute ca in cd.Attributes)
{
if (!ca.AttributeHierarchyEnabled)
{
continue;
}
if (!IsAtOrAboveGranularity(ca.Attribute, mgDim))
{
continue;
}
foreach (Aggregation agg in aggDesign.Aggregations)
{
AggregationDimension aggDim = agg.Dimensions.Find(cd.ID);
if (ca.Attribute.Usage == AttributeUsage.Parent)
{
if (!(mgDim is RegularMeasureGroupDimension))
{
continue;
}
if (!IsAtOrAboveGranularity(cd.Dimension.KeyAttribute, mgDim))
{
continue;
}
//if this is a parent-child attribute and the key isn't in the agg, then check whether the parent-child attribute has a DefaultMember or is aggregatable
if (aggDim == null || aggDim.Attributes.Find(cd.Dimension.KeyAttribute.ID) == null)
{
string sWarning = "";
if (!string.IsNullOrEmpty(ca.Attribute.DefaultMember) || attributesWithDefaultMemberAlteredInCalcScript.Contains(((string)("[" + cd.Name + "].[" + ca.Attribute.Name + "]")).ToLower()))
{
sWarning += "has a DefaultMember";
}
if (!ca.Attribute.IsAggregatable)
{
if (!string.IsNullOrEmpty(sWarning))
{
sWarning += " and ";
}
sWarning += "is not aggregatable";
}
if (!string.IsNullOrEmpty(sWarning))
{
sWarning = "This aggregation should probably contain [" + cd.Name + "].[" + cd.Dimension.KeyAttribute.Name + "] because [" + cd.Name + "].[" + ca.Attribute.Name + "] is a parent-child attribute which " + sWarning + ".";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
//for non-parent-child attributes...
else if (aggDim == null || aggDim.Attributes.Find(ca.AttributeID) == null)
{
string sWarning = "";
if (!string.IsNullOrEmpty(ca.Attribute.DefaultMember) || attributesWithDefaultMemberAlteredInCalcScript.Contains(((string)("[" + cd.Name + "].[" + ca.Attribute.Name + "]")).ToLower()))
{
sWarning += "has a DefaultMember";
}
if (!ca.Attribute.IsAggregatable)
{
if (!string.IsNullOrEmpty(sWarning))
{
sWarning += " and ";
}
sWarning += "is not aggregatable";
}
if (ca.AggregationUsage == AggregationUsage.Full)
{
if (!string.IsNullOrEmpty(sWarning))
{
sWarning += " and ";
}
sWarning += "is marked as AggregationUsage=Full";
}
if (aggDim != null && AggContainsChild(aggDim, ca.Attribute))
{
continue; //if this attribute is redundant, then no need to warn about it
}
if (!string.IsNullOrEmpty(sWarning))
{
sWarning = "This aggregation should probably contain [" + cd.Name + "].[" + ca.Attribute.Name + "] which " + sWarning + ".";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
}
//look for aggs with redundant attributes
foreach (Aggregation agg in aggDesign.Aggregations)
{
bool bHasRedundancy = false;
foreach (AggregationDimension aggDim in agg.Dimensions)
{
foreach (AggregationAttribute attr in aggDim.Attributes)
{
if (AggContainsParent(aggDim, attr.Attribute))
{
bHasRedundancy = true;
break;
}
}
if (bHasRedundancy)
{
break;
}
}
if (bHasRedundancy)
{
string sWarning = "This aggregation contains redundant attributes which unnecessarily bloat the size of the aggregation.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
//check for aggs on below granularity attributes
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
foreach (AggregationAttribute attr in aggDim.Attributes)
{
if (!IsAtOrAboveGranularity(attr.Attribute, aggDim.MeasureGroupDimension))
{
string sWarning = "This aggregation contains [" + aggDim.CubeDimension.Name + "].[" + attr.Attribute.Name + "] which is below granularity. This is not allowed.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
return(masterWarnings);
}
/// <summary>
/// Helper function receives a string in format like 0001000,001001
/// translates string into aggregation and adds it to aggegation design
/// </summary>
Boolean AddAggregationToAggDesign(AggregationDesign aggDesign, string instr, int aggNum, string strAggPrefix )
{
int originalAggNum = aggNum;
try
{
Aggregation agg;
string aggName = strAggPrefix + aggNum.ToString();
while (aggDesign.Aggregations.Find(aggName) != null)
{
aggName = strAggPrefix + (++aggNum).ToString();
}
agg = aggDesign.Aggregations.Add(aggName, aggName);
string a1;
int dimNum = 0;
int attrNum = 0;
bool newDim = true;
for (int i = 0; i < instr.Length; i++)
{
a1 = instr[i].ToString();
switch (a1)
{
case ",":
dimNum++;
attrNum = -1;
newDim = true;
break;
case "0":
break;
case "1":
if (newDim)
{
agg.Dimensions.Add(dimIDs[dimNum]);
newDim = false;
}
agg.Dimensions[dimIDs[dimNum]].Attributes.Add(dimAttributes[dimNum, attrNum]);
break;
default:
break;
}
attrNum++;
}
return true;
}
catch (Exception ex)
{
MessageBox.Show("Error saving aggregation #" + (originalAggNum + 1) + ": " + ex.Message);
throw new Exception(""); //blank exception means not to report again
}
}
private void PopupExportTableForm(AggregationDesign aggrD)
{
//MeasureGroup cloneMG = null;
//if (mg != null)
// cloneMG = this.cloneDB.Cubes[mg.Parent.ID].MeasureGroups[mg.ID];
AggManager.ExportTable form1 = new AggManager.ExportTable();
form1.Init(aggrD);
DialogResult res = form1.ShowDialog(this);
if (res != DialogResult.OK) return;
}
void backgroundThread_DoWork(object sender, DoWorkEventArgs e)
{
try
{
if (cube != null)
{
foreach (MeasureGroup mg in cube.MeasureGroups)
{
if (mg.IsLinked) continue;
long lngMeasureGroupRowCount = 0;
foreach (AggregationDesign aggD in mg.AggregationDesigns)
{
bool bPartitionUsesAggD = false;
foreach (Partition p in mg.Partitions)
{
if (p.AggregationDesignID == aggD.ID)
{
bPartitionUsesAggD = true;
break;
}
}
if (!bPartitionUsesAggD) continue;
this.aggD = aggD;
tester = new AggregationPerformanceTester(aggD, chkTestAgg.Checked, chkTestNoAggs.Checked, chkWithoutIndividualAggs.Checked);
tester.OnProgress += new ProgressChangedEventHandler(tester_OnProgress);
tester.StartTest();
listPerf.AddRange(tester.Results);
missingPerf.AddRange(tester.MissingResults);
if (tester.Results.Length > 0)
{
lngMeasureGroupRowCount += tester.Results[0].PartitionRowCount;
foreach (AggregationPerformanceTester.AggregationPerformance aggP in listPerf)
{
if (aggP.MeasureGroupName == mg.Name)
{
aggP.MeasureGroupRowCount = lngMeasureGroupRowCount;
}
}
}
sErrors += tester.Errors;
if (tester.Cancelled) break;
}
}
}
else
{
tester = new AggregationPerformanceTester(this.aggD, chkTestAgg.Checked, chkTestNoAggs.Checked, chkWithoutIndividualAggs.Checked);
tester.OnProgress += new ProgressChangedEventHandler(tester_OnProgress);
tester.StartTest();
listPerf.AddRange(tester.Results);
missingPerf.AddRange(tester.MissingResults);
sErrors = tester.Errors;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
}
try
{
HideForm();
}
catch { }
}
