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