Пример #1
0
        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);
        }