public static List <DimensionUsage> GetDimensionUsage(Cube c) { List <CubeDimension> listCubeDimensions = new List <CubeDimension>(); foreach (CubeDimension cd in c.Dimensions) { listCubeDimensions.Add(cd); } List <DimensionUsage> dimUsage = new List <DimensionUsage>(); foreach (MeasureGroup mg in c.MeasureGroups) { System.Diagnostics.Trace.Write("mg " + mg.Name); foreach (MeasureGroupDimension mgdim in mg.Dimensions) { System.Diagnostics.Trace.Write(",mgdim " + mgdim.Dimension.Name); if (mgdim is ReferenceMeasureGroupDimension) { ReferenceMeasureGroupDimension refMgDim = (ReferenceMeasureGroupDimension)mgdim; getReferencedMeasureGroupAttributeUsage(dimUsage, mg, refMgDim); } else if (mgdim is DegenerateMeasureGroupDimension) { DegenerateMeasureGroupDimension degMgDim = (DegenerateMeasureGroupDimension)mgdim; getFactMeasureGroupAttributeUsage(dimUsage, mg, degMgDim); } else if (mgdim is ManyToManyMeasureGroupDimension) { ManyToManyMeasureGroupDimension m2mMgDim = (ManyToManyMeasureGroupDimension)mgdim; getManyToManyMeasureGroupAttributeUsage(dimUsage, mg, m2mMgDim); } else if (mgdim is DataMiningMeasureGroupDimension) { DataMiningMeasureGroupDimension dmMgDim = (DataMiningMeasureGroupDimension)mgdim; getDataMiningMeasureGroupAttributeUsage(dimUsage, mg, dmMgDim); } else if (mgdim is RegularMeasureGroupDimension) { RegularMeasureGroupDimension regMDdim = (RegularMeasureGroupDimension)mgdim; getRegularMeasureGroupAttributeUsage(dimUsage, mg, regMDdim); } if (listCubeDimensions.Contains(mgdim.CubeDimension)) { listCubeDimensions.Remove(mgdim.CubeDimension); } } } //add any cube dimensions which aren't related to any measure groups foreach (CubeDimension cd in listCubeDimensions) { DimensionUsage du = new DimensionUsage(string.Empty, null, cd, cd.Dimension); dimUsage.Add(du); } return(dimUsage); }
/// <summary> /// Add reference dim usage /// </summary> /// <param name="measureGroup"></param> /// <param name="referenceDimID"></param> /// <param name="referenceDimAttrID"></param> /// <param name="interDimID"></param> /// <param name="interDimAttrID"></param> internal static void ADD_DIM_USAGE_REFERENCE_RELATIONSHIP( MeasureGroup measureGroup, String referenceDimID, String referenceDimAttrID, String interDimID, String interDimAttrID) { MeasureGroupDimension regDim = measureGroup.Dimensions.Find(referenceDimID); if (regDim != null) { measureGroup.Dimensions.Remove(regDim); } ReferenceMeasureGroupDimension regMgDim = new ReferenceMeasureGroupDimension(); regMgDim.CubeDimensionID = referenceDimID; regMgDim.IntermediateCubeDimensionID = interDimID; MeasureGroupAttribute mgAttr = regMgDim.Attributes.Add(referenceDimAttrID); mgAttr.Type = MeasureGroupAttributeType.Granularity; regMgDim.IntermediateGranularityAttributeID = interDimAttrID; regMgDim.Materialization = ReferenceDimensionMaterialization.Regular; measureGroup.Dimensions.Add(regMgDim); }
/* * Referenced Col1 - Ref Dim Attrib Name * Col2 - Intermediate Dim Name * Col3 - Intermediate Dim Attib Name * Col4 - Path * ?? Materialized */ private static void getReferencedMeasureGroupAttributeUsage(List <DimensionUsage> dimUsage, MeasureGroup mg, ReferenceMeasureGroupDimension refMgDim) { DimensionUsage usage = new DimensionUsage("Referenced", mg, refMgDim.CubeDimension, refMgDim.Dimension); string tableId = string.Empty; usage.Column1Name = "Reference Dimension Attribute"; foreach (CubeAttribute a in refMgDim.CubeDimension.Attributes) { if (a.Attribute.Usage == AttributeUsage.Key) { usage.Column1Value = a.Attribute.Name; break; } } usage.Column2Name = "Intermediate Dimension"; usage.Column2Value = refMgDim.IntermediateDimension.Name; usage.Column3Name = "Intermediate Dimension Attribute"; usage.Column3Value = refMgDim.IntermediateGranularityAttribute.Attribute.Name; // not currently exposed on the report due to space limitation // the string (Materialized) is added after the dim name instead. usage.Column4Name = "Materialized"; usage.Column4Value = refMgDim.Materialization.ToString(); usage.Materialized = (refMgDim.Materialization == ReferenceDimensionMaterialization.Regular); dimUsage.Add(usage); }
private static List <DimensionUsage> RecurseTabularRelationships(Dimension dMG, MeasureGroup mgOuter, bool bIsBusMatrix) { List <DimensionUsage> list = new List <DimensionUsage>(); foreach (Microsoft.AnalysisServices.Relationship relOuter in dMG.Relationships) { bool bFound = false; MeasureGroup mgFrom = dMG.Parent.Cubes[0].MeasureGroups[relOuter.FromRelationshipEnd.DimensionID]; DimensionAttribute daFrom = dMG.Attributes[relOuter.FromRelationshipEnd.Attributes[0].AttributeID]; Dimension dTo = dMG.Parent.Dimensions[relOuter.ToRelationshipEnd.DimensionID]; DimensionAttribute daTo = dTo.Attributes[relOuter.ToRelationshipEnd.Attributes[0].AttributeID]; CubeDimension dToCube = dMG.Parent.Cubes[0].Dimensions[relOuter.ToRelationshipEnd.DimensionID]; foreach (MeasureGroupDimension mgdOuter in mgFrom.Dimensions) { ReferenceMeasureGroupDimension rmgdOuter = mgdOuter as ReferenceMeasureGroupDimension; if (rmgdOuter != null && rmgdOuter.Materialization == ReferenceDimensionMaterialization.Regular && rmgdOuter.RelationshipID == relOuter.ID) { //active relationships have a materialized reference relationship bFound = true; break; } } string sActiveFlag = "Active"; if (!bFound) { sActiveFlag = "Inactive"; if (bIsBusMatrix) { continue; //don't show inactive relationships in bus matrix view } } DimensionUsage usage = new DimensionUsage(sActiveFlag, mgOuter, dToCube, dTo); usage.Column1Name = "Foreign Key Column"; usage.Column1Value = daFrom.Name; usage.Column2Name = "Primary Key Column"; usage.Column2Value = daTo.Name; bool bFoundVisibleAttribute = false; foreach (DimensionAttribute da in dTo.Attributes) { if (da.AttributeHierarchyVisible) { bFoundVisibleAttribute = true; break; } } if (bFoundVisibleAttribute) //only if the To end has visible attributes should we show it as a dimension { list.Add(usage); } if (bIsBusMatrix) { //recurse if it's the bus matrix view list.AddRange(RecurseTabularRelationships(dTo, mgOuter, bIsBusMatrix)); } } return(list); }
static void Main(string[] args) { // Hold the Calculated Member info here String cms = null; //Connection vars String ConnStr = null; String OLAPServerName = null; String OLAPDB = null; String OLAPCube = null; bool shallipause = false; // do u want me to wait at the end of the run? bool includeschema = false; // shall i include the SQL Server schema names in the Schema XML syntax output? bool boolM2M = true; // Include Many-to-many dimensions? bool boolAll = false; // Set to false by command-line switch to inlude Al member String schemaname = null; // for testing ... /* * OLAPDB = "AdventureWorks"; * OLAPCube = "Adventure Works"; * OLAPServerName = "localhost"; */ // process command line switches try { foreach (string arg in args) { switch (arg.Substring(0, 2).ToUpper()) { case "/S": //Server name REQUIRED OLAPServerName = arg.Substring(2); break; case "/C": //Cube Name REQUIRED OLAPCube = arg.Substring(2); break; case "/D": // source SSAS Database name where the cube is located REQUIRED OLAPDB = arg.Substring(2); break; case "/M": // include M:M dims OPTIONAL boolM2M = true; break; case "/N": // what do you want to call the resulting Mondrian schema? OPTIONAL schemaname = arg.Substring(2); break; case "/P": // do you want to pause at the end of the migration tool run? OPTIONAL shallipause = true; break; case "/A": // shall I include the SQL Server schema from the source DB? OPTIONAL includeschema = true; break; case "/L": // shall I include the All member? OPTIONAL boolAll = true; break; case "/?": case "/H": case "/HELP": outputhelp(); break; default: break; } } } catch (Exception e) { Console.WriteLine("Sorry, the command line is very simplistic for this small program and requires no spaces between switch and value."); outputhelp(); } // Set the output Mondrian schema name here if (schemaname == null) { schemaname = OLAPDB; } Server OLAPServer = new Server(); ConnStr = "Provider=MSOLAP;Data Source=" + OLAPServerName + ";"; try { OLAPServer.Connect(ConnStr); } catch (Exception e) { Console.WriteLine("Problem connecting to SSAS"); Console.WriteLine(e.Message); Console.WriteLine(); outputhelp(); return; } // For Virtual Cubes, keep track of dims, meas and cubes List <String> Cubes = new List <String>(); Dictionary <String, List <String> > VCubeDims = new Dictionary <String, List <String> >(); Dictionary <String, List <String> > VCubeMeas = new Dictionary <String, List <String> >(); Console.WriteLine("<Schema name=\"" + schemaname + "\">"); // Database foreach (Database OLAPDatabase in OLAPServer.Databases) { if (OLAPDB == "" || OLAPDatabase.Name.ToString() == OLAPDB) { // Cube foreach (Cube OLAPCubex in OLAPDatabase.Cubes) { if (OLAPCube == "" || OLAPCubex.Name == OLAPCube) { List <String> dnames = new List <String>(); List <String> d2 = new List <String>(); // List of dimension names to be shared throughout XML Dictionary <String, String> fks = new Dictionary <String, String>(); // List of FKs for dimensions List <String> ignoredim = new List <String>(); Hashtable refdims = new Hashtable(); Hashtable specialFK = new Hashtable(); // Check for incompatible dimension types foreach (MeasureGroup OLAPMeasureGroup in OLAPCubex.MeasureGroups) { foreach (MeasureGroupDimension mgdim in OLAPMeasureGroup.Dimensions) { // If this is a reference dim, we'll need to store the snowflake joins if (mgdim is ReferenceMeasureGroupDimension) { ReferenceMeasureGroupDimension refdim = (ReferenceMeasureGroupDimension)mgdim; try { refdims.Add(mgdim.Dimension.Name, refdim.CubeDimension.Dimension.KeyAttribute.KeyColumns[0].ToString()); refdims.Add(mgdim.Dimension.Name + "XX", refdim.IntermediateGranularityAttribute.Attribute.KeyColumns[0].ToString()); string [] temps = splitField(refdim.IntermediateCubeDimension.Dimension.KeyAttribute.KeyColumns[0].ToString()); // Need to add special FK to satisfy Mondrian Snowflake schema specialFK.Add(mgdim.Dimension.Name, temps[1]); } catch (Exception e) { }; } if (boolM2M && mgdim is ManyToManyMeasureGroupDimension) { ignoredim.Add(mgdim.CubeDimension.Name); } if (mgdim is DataMiningMeasureGroupDimension) { ignoredim.Add(mgdim.CubeDimension.Name); } } } foreach (CubeDimension OLAPDimension in OLAPCubex.Dimensions) { // Set this to set table name = a for snowflake bool boolSnow = false; string snowstring = " "; // Check to see if we should skip this dim if (ignoredim.Contains(OLAPDimension.Name)) { continue; } // We can't handle composite keys in Mondrian, so I'm just taking the first key column string[] myspl = splitField(OLAPDimension.Attributes[0].Attribute.KeyColumns[0].ToString()); // Mondrian only has 2 types of Dimensions: StandardDimension and TimeDimension String dimtype = "StandardDimension"; if (OLAPDimension.Dimension.Type.ToString().Equals("Time")) { dimtype = "TimeDimension"; } String dname = "visible=\"" + OLAPDimension.Visible + "\" type=\"" + dimtype + "\" highCardinality=\"false\" name=\"" + OLAPDimension.Name + "\">"; dnames.Add(dname); // if not already set, set the foreign key for this Dimension string fstring = myspl[1]; if (specialFK.ContainsKey(OLAPDimension.Name)) { fstring = specialFK[OLAPDimension.Name].ToString(); } fks.Add(dname, fstring); d2.Add(OLAPDimension.Name); Console.WriteLine("<Dimension " + dname); // if this is a reference dim, then we need to build the JOIN syntax if (refdims.ContainsKey(OLAPDimension.Name.ToString())) { boolSnow = true; string[] ms = splitField(refdims[OLAPDimension.Name].ToString()); string[] ks = splitField(refdims[OLAPDimension.Name + "XX"].ToString()); // Turn attributes into default hierarchy Console.WriteLine("<Hierarchy name=\"Default\" visible=\"true\" hasAll=\"true\" primaryKey=\"" + specialFK[OLAPDimension.Name].ToString() + "\" primaryKeyTable=\"a\">"); Console.WriteLine("<Join leftAlias=\"a\" leftKey=\"" + ks[1] + "\" rightAlias=\"b\" rightKey=\"" + ms[1] + "\">"); if (includeschema) { Console.WriteLine("<Table name=\"" + myspl[0] + "\" schema=\"" + sschema + "\" alias=\"a\"></Table>"); Console.WriteLine("<Table name=\"" + ks[0] + "\" schema=\"" + sschema + "\" alias=\"b\"></Table>"); } else { Console.WriteLine("<Table name=\"" + myspl[0] + "\" alias=\"a\"></Table>"); Console.WriteLine("<Table name=\"" + ks[0] + "\" alias=\"b\"></Table>"); } Console.WriteLine("</Join>"); } else // if not a snowflake ... { // Turn attributes into default hierarchy Console.WriteLine("<Hierarchy name=\"Default\" visible=\"true\" hasAll=\"true\" primaryKey=\"" + myspl[1] + "\">"); if (includeschema) { Console.WriteLine("<Table name=\"" + myspl[0] + "\" schema=\"" + sschema + "\"></Table>"); } else { Console.WriteLine("<Table name=\"" + myspl[0] + "\"></Table>"); } } foreach (CubeAttribute OLAPDimAttribute in OLAPDimension.Attributes) { String mystring = OLAPDimAttribute.Attribute.KeyColumns[0].ToString(); string [] mysplit = mystring.Split('.'); String ss1 = convertDataType(OLAPDimAttribute.Attribute.KeyColumns[0].DataType.ToString()); string[] splitNameColumn = OLAPDimAttribute.Attribute.NameColumn.ToString().Split('.'); String ss2 = "Regular"; // Convert SSAS DimType to Mondrian LevelType, but ONLY if it is a TimeDim if (OLAPDimension.Dimension.Type.ToString().Equals("Time")) { ss2 = convertLevelType(OLAPDimAttribute.Attribute.Type.ToString()); } if (boolSnow) { snowstring = " table=\"b\" "; } Console.WriteLine("<Level name=\"" + OLAPDimAttribute.Attribute.Name + "\" visible=\"" + OLAPDimAttribute.AttributeHierarchyVisible + "\"" + snowstring + " column=\"" + mysplit[1] + "\" type=\"" + ss1 + "\" levelType=\"" + ss2 + "\" hideMemberIf=\"Never\">"); Console.WriteLine("</Level>"); } Console.WriteLine("</Hierarchy>"); //Dimension Hierarchy foreach (CubeHierarchy OLAPDimHierarchy in OLAPDimension.Hierarchies) { string [] mysplit = splitField(OLAPDimHierarchy.Hierarchy.Levels[0].SourceAttribute.KeyColumns[0].ToString()); string allmember = null; if (boolAll) { allmember = "allMemberName=\"" + OLAPDimHierarchy.Hierarchy.AllMemberName + "\""; } // check for snowflake if (boolSnow) { Console.WriteLine("<Hierarchy name=\"" + OLAPDimHierarchy.Hierarchy.Name + "\" visible=\"" + OLAPDimHierarchy.Visible + "\" hasAll=\"true\" " + allmember + " primaryKey=\"" + specialFK[OLAPDimension.Name].ToString() + "\" primaryKeyTable=\"a\">"); string[] ms = splitField(refdims[OLAPDimension.Name].ToString()); string[] ks = splitField(refdims[OLAPDimension.Name + "XX"].ToString()); Console.WriteLine("<Join leftAlias=\"a\" leftKey=\"" + ks[1] + "\" rightAlias=\"b\" rightKey=\"" + ms[1] + "\">"); if (includeschema) { Console.WriteLine("<Table name=\"" + myspl[0] + "\" schema=\"" + sschema + "\" alias=\"a\"></Table>"); Console.WriteLine("<Table name=\"" + ks[0] + "\" schema=\"" + sschema + "\" alias=\"b\"></Table>"); } else { Console.WriteLine("<Table name=\"" + myspl[0] + "\" alias=\"a\"></Table>"); Console.WriteLine("<Table name=\"" + ks[0] + "\" alias=\"b\"></Table>"); } Console.WriteLine("</Join>"); } else //else not a snowflake join { Console.WriteLine("<Hierarchy name=\"" + OLAPDimHierarchy.Hierarchy.Name + "\" visible=\"" + OLAPDimHierarchy.Visible + "\" hasAll=\"true\" " + allmember + " primaryKey=\"" + mysplit[1] + "\">"); if (includeschema) { Console.WriteLine("<Table name=\"" + mysplit[0] + "\" schema=\"" + sschema + "\"></Table>"); } else { Console.WriteLine("<Table name=\"" + mysplit[0] + "\"></Table>"); } } foreach (Level OLAPDimHierachyLevel in OLAPDimHierarchy.Hierarchy.Levels) { string[] ksplit = splitField(OLAPDimHierachyLevel.SourceAttribute.KeyColumns[0].ToString()); String leveltype = "Regular"; // Convert SSAS DimType to Mondrian LevelType, but ONLY if it is a TimeDim if (OLAPDimension.Dimension.Type.ToString().Equals("Time")) { leveltype = convertLevelType(OLAPDimHierachyLevel.SourceAttribute.Type.ToString()); } string dt1 = convertDataType(OLAPDimHierachyLevel.SourceAttribute.KeyColumns[0].DataType.ToString()); Console.WriteLine("<Level name=\"" + OLAPDimHierachyLevel.Name + "\" visible=\"" + OLAPDimHierarchy.Visible + "\" " + snowstring + " column=\"" + ksplit[1] + "\" type=\"" + dt1 + "\" levelType=\"" + leveltype + "\" hideMemberIf=\"Never\">"); Console.WriteLine("</Level>"); } Console.WriteLine("</Hierarchy>"); } Console.WriteLine("</Dimension>"); } // NEXT OLAPDimension //Measure Group int i2 = 0; foreach (MeasureGroup OLAPMeasureGroup in OLAPCubex.MeasureGroups) { string[] mysplit = splitField(OLAPMeasureGroup.Measures[0].Source.Source.ToString()); Cubes.Add(OLAPMeasureGroup.Name); // for VCube Console.WriteLine("<Cube name=\"" + OLAPMeasureGroup.Name + "\" visible=\"true\" cache=\"true\" enabled=\"true\">"); if (includeschema) { if (OLAPMeasureGroup.Measures[0].Source.Source.ToString().Contains("_")) { sschema = ((ColumnBinding)OLAPMeasureGroup.Measures[0].Source.Source).TableID.Split('_')[0]; } Console.WriteLine("<Table name=\"" + mysplit[0] + "\" schema=\"" + sschema + "\"></Table>"); } else { Console.WriteLine("<Table name=\"" + mysplit[0] + "\"></Table>"); } int ii = 0; foreach (String d in dnames) { Console.WriteLine("<DimensionUsage source=\"" + d2[ii] + "\" foreignKey=\"" + fks[d] + "\" " + d + "</DimensionUsage>"); if (!VCubeDims.ContainsKey(OLAPMeasureGroup.Name)) { List <String> t = new List <String> { d2[ii] }; VCubeDims.Add(OLAPMeasureGroup.Name, t); } else { VCubeDims[OLAPMeasureGroup.Name].Add(d2[ii]); } ii++; } // Measures int i3 = 0; //cms = null; foreach (Measure OLAPMeasure in OLAPMeasureGroup.Measures) { String mtl = OLAPMeasure.AggregateFunction.ToString(); if (mtl != null) { mtl = mtl.ToLower(); } // Convert the aggregation types switch (mtl) { case "distinctcount": mtl = "distinct count"; break; case "none": case "byaccount": mtl = "sum"; break; case "averageofchildren": mtl = "avg"; break; case "lastnonempty": mtl = "sum"; break; } string[] mn = splitField(OLAPMeasure.Source.Source.ToString()); bool isl = false; String omeasure = null; // Check to see if there is a measure expression in the measure definition // If so, we can make it a calculate measure try { omeasure = OLAPMeasure.MeasureExpression; if (omeasure.Length > 0) { isl = true; } } catch (Exception e) {} if (isl) { // This measure has a measure expression, so let's add it to the string of calculated measures (cms) cms += "<CalculatedMember name=\"" + OLAPMeasure.Name + "\" dimension=\"Measures\" visible=\"" + OLAPMeasure.Visible + "\">" + System.Environment.NewLine + " <Formula> <![CDATA[" + omeasure + "]]> </Formula>" + System.Environment.NewLine + " <CalculatedMemberProperty name=\"FORMAT_STRING\" value=\"" + OLAPMeasure.FormatString + "\"> </CalculatedMemberProperty>" + System.Environment.NewLine + "</CalculatedMember>" + System.Environment.NewLine; } else if (mn.Count() > 1) { // This measure does not have an expression, so just spit out the measure def without making it a calculated measure Console.WriteLine("<Measure name=\"" + OLAPMeasure.Name + "\" column=\"" + mn[1] + "\" formatString=\"" + OLAPMeasure.FormatString + "\" aggregator=\"" + mtl + "\">"); Console.WriteLine("</Measure>"); if (!VCubeMeas.ContainsKey(OLAPMeasureGroup.Name)) { List <String> t = new List <String> { OLAPMeasure.Name }; VCubeMeas.Add(OLAPMeasureGroup.Name, t); } else { VCubeMeas[OLAPMeasureGroup.Name].Add(OLAPMeasure.Name); } } } i2++; i3++; // Output the Calculated Members for Measures here if (cms != null) { Console.WriteLine(cms); } Console.WriteLine("</Cube>"); } // MeasureGroups } // Cube } } } // Wrap it into a single Virtual Cube Console.WriteLine("<VirtualCube enabled=\"true\" name=\"" + schemaname + "\">"); // Iterate through cubes for the purposes of outputing the virtual cube(s) foreach (String cube in Cubes) { try { foreach (String vcd in VCubeDims[cube]) { Console.WriteLine("<VirtualCubeDimension cubeName=\"" + cube + "\" name=\"" + vcd + "\"></VirtualCubeDimension>"); } } catch (Exception e) { }; } foreach (String cube in Cubes) { try { foreach (String vcm in VCubeMeas[cube]) { Console.WriteLine("<VirtualCubeMeasure cubeName=\"" + cube + "\" name=\"[Measures].[" + vcm + "]\" visible=\"true\"></VirtualCubeMeasure>"); } } catch (Exception e) { }; } if (cms != null) { Console.WriteLine(cms); } Console.WriteLine("</VirtualCube>"); Console.WriteLine("</Schema>"); if (shallipause) { Console.ReadKey(); } }
/* * Referenced Col1 - Ref Dim Attrib Name * Col2 - Intermediate Dim Name * Col3 - Intermediate Dim Attib Name * Col4 - Path * ?? Materialized */ private static void getReferencedMeasureGroupAttributeUsage(List<DimensionUsage> dimUsage, MeasureGroup mg, ReferenceMeasureGroupDimension refMgDim) { DimensionUsage usage = new DimensionUsage("Referenced",mg, refMgDim.CubeDimension, refMgDim.Dimension); string tableId = string.Empty; usage.Column1Name = "Reference Dimension Attribute"; foreach (CubeAttribute a in refMgDim.CubeDimension.Attributes) { if (a.Attribute.Usage == AttributeUsage.Key) { usage.Column1Value = a.Attribute.Name; break; } } usage.Column2Name = "Intermediate Dimension"; usage.Column2Value = refMgDim.IntermediateDimension.Name; usage.Column3Name = "Intermediate Dimension Attribute"; usage.Column3Value = refMgDim.IntermediateGranularityAttribute.Attribute.Name; // not currently exposed on the report due to space limitation // the string (Materialized) is added after the dim name instead. usage.Column4Name = "Materialized"; usage.Column4Value = refMgDim.Materialization.ToString(); usage.Materialized = (refMgDim.Materialization == ReferenceDimensionMaterialization.Regular); dimUsage.Add(usage); }
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); }