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