in BismNormalizer/BismNormalizer/TabularCompare/MultidimensionalMetadata/TabularModel.cs [131:407]
private void PopulateMeasures()
{
if (_amoDatabase.Cubes.Count > 0)
{
MdxScript mdxScript = _amoDatabase.Cubes[0].MdxScripts["MdxScript"];
if (mdxScript.Commands.Count > 0)
{
List<ParsedCommand> parsedCommands = new List<ParsedCommand>();
//Review all commands in MdxScripts and extract all MDX expressions found there
for (int i = 0; i < mdxScript.Commands.Count; i++)
{
if (mdxScript.Commands[i].Text != null)
{
List<string> expressions = new List<string>();
expressions.AddRange(ParseMdxScript(mdxScript.Commands[i].Text));
if (expressions.Count > 0)
{
string fullName = "";
if (mdxScript.Commands[i].Annotations.Contains("FullName")) fullName = mdxScript.Commands[i].Annotations["FullName"].Value.InnerText;
////yet another microsoft fudge
//if (fullName.Length > 1 && fullName.Substring(fullName.Length - 2, 2) == "]]") fullName = fullName.Substring(0, fullName.Length - 1);
string table = "";
if (mdxScript.Commands[i].Annotations.Contains("Table")) table = mdxScript.Commands[i].Annotations["Table"].Value.InnerText;
parsedCommands.Add(new ParsedCommand(expressions, fullName, table));
}
}
}
MeasureCollection kpiReferenceMeasures = new MeasureCollection();
//KPIs declared in MDX script
List<Microsoft.AnalysisServices.Kpi> kpisDeclaredInScript = new List<Microsoft.AnalysisServices.Kpi>();
if (_amoDatabase.CompatibilityLevel < 1103)
{
//This block only applies pre SP1
//Populate the KPI reference measures first - they start with "CREATE MEMBER CURRENTCUBE.Measures" (and use MDX) rather than "CREATE MEASURE"
foreach (ParsedCommand parsedCommand in parsedCommands)
{
foreach (string statement in parsedCommand.Expressions)
{
if ((statement.Length >= 35) && (statement.Substring(0, 35) == "CREATE MEMBER CURRENTCUBE.Measures."))
{
//Find table name/measure/expression
int openSquareBracketPosition = statement.IndexOf('[', 0);
int closeSquareBracketPosition = statement.IndexOf(']', openSquareBracketPosition + 1);
string statementMeasureName = statement.Substring(openSquareBracketPosition + 1, closeSquareBracketPosition - openSquareBracketPosition - 1);
int openExpressionQuotePosition = statement.IndexOf('\'', 0);
if (openExpressionQuotePosition != -1)
{
int closeExpressionQuotePosition = statement.IndexOf('\'', openExpressionQuotePosition + 1);
string statementMeasureExpression = statement.Substring(openExpressionQuotePosition + 1, closeExpressionQuotePosition - openExpressionQuotePosition - 1);
int associatedMeasureGroupEndPosition = statement.IndexOf("ASSOCIATED_MEASURE_GROUP", closeExpressionQuotePosition) + 24;
int openTableQuotePosition = statement.IndexOf('\'', associatedMeasureGroupEndPosition + 1);
int closeTableQuotePosition = statement.IndexOf('\'', openTableQuotePosition + 1);
string statementTableName = statement.Substring(openTableQuotePosition + 1, closeTableQuotePosition - openTableQuotePosition - 1);
kpiReferenceMeasures.Add(new Measure(this, statementTableName, statementMeasureName, statementMeasureExpression));
}
}
}
}
}
/* KPIs can be created in 2 different ways: either declared in the MDX script as "CREATE KPI" (in which case won't be populated
in _amoDatabase.Cubes[0].Kpis), or using the object model (in which case it will be in the AMO KPI collection).
-PRE -SP1 CAN BE EITHER!
-POST-SP1 ALWAYS IN SCRIPT
So we need to check both.
BUT WAIT, THERE'S MORE ... if created in script pre-sp1 will be MDX syntax, if created in script post sp1, will be dax syntax
AND ... post-sp1 replaces ']' characters with ']]'
Thank you Microsoft. You are the best.
*/
foreach (ParsedCommand parsedCommand in parsedCommands)
{
foreach (string statement in parsedCommand.Expressions)
{
if ((statement.Length >= 23) && (statement.Substring(0, 23) == "CREATE KPI CURRENTCUBE."))
{
int lastCharacterPosition = 0;
string kpiName;
string kpiExpression;
string goalName;
string goalExpression;
string statusName;
string statusExpression;
string trendName;
string trendExpression;
if (_amoDatabase.CompatibilityLevel < 1103)
{
int openSquareBracketPosition = statement.IndexOf('[', 0);
int closeSquareBracketPosition = statement.IndexOf(']', openSquareBracketPosition + 1);
kpiName = statement.Substring(openSquareBracketPosition + 1, closeSquareBracketPosition - openSquareBracketPosition - 1);
int goalEndPosition = statement.IndexOf("GOAL", closeSquareBracketPosition) + "GOAL".Length;
int goalOpenPosition = statement.IndexOf("Measures.[", goalEndPosition + 1) + "Measures.[".Length;
int goalClosePosition = statement.IndexOf("]", goalOpenPosition) - 1;
goalName = statement.Substring(goalOpenPosition, goalClosePosition - goalOpenPosition + 1);
int statusEndPosition = statement.IndexOf("STATUS", closeSquareBracketPosition) + "STATUS".Length;
int statusOpenPosition = statement.IndexOf("Measures.[", statusEndPosition + 1) + "Measures.[".Length;
int statusClosePosition = statement.IndexOf("]", statusOpenPosition) - 1;
statusName = statement.Substring(statusOpenPosition, statusClosePosition - statusOpenPosition + 1);
int trendEndPosition = statement.IndexOf("TREND", closeSquareBracketPosition) + "TREND".Length;
int trendOpenPosition = statement.IndexOf("Measures.[", trendEndPosition + 1) + "Measures.[".Length;
int trendClosePosition = statement.IndexOf("]", trendOpenPosition) - 1;
trendName = statement.Substring(trendOpenPosition, trendClosePosition - trendOpenPosition + 1);
}
else
{
ParseMeasureAndExpression(parsedCommand.FullName, statement, out kpiName, out kpiExpression, ref lastCharacterPosition);
lastCharacterPosition = statement.IndexOf("GOAL", lastCharacterPosition) + "GOAL".Length;
ParseMeasureAndExpression(parsedCommand.FullName, statement, out goalName, out goalExpression, ref lastCharacterPosition);
lastCharacterPosition = statement.IndexOf("STATUS", lastCharacterPosition) + "STATUS".Length;
ParseMeasureAndExpression(parsedCommand.FullName, statement, out statusName, out statusExpression, ref lastCharacterPosition);
lastCharacterPosition = statement.IndexOf("TREND", lastCharacterPosition) + "TREND".Length;
ParseMeasureAndExpression(parsedCommand.FullName, statement, out trendName, out trendExpression, ref lastCharacterPosition);
}
int statusGraphicEndPosition = statement.IndexOf("STATUS_GRAPHIC", lastCharacterPosition) + "STATUS_GRAPHIC".Length;
int statusGraphicOpenQuotePosition = statement.IndexOf('\'', statusGraphicEndPosition + 1);
int statusGraphicCloseQuotePosition = statement.IndexOf('\'', statusGraphicOpenQuotePosition + 1);
string mdxStmtStatusGraphic = statement.Substring(statusGraphicOpenQuotePosition + 1, statusGraphicCloseQuotePosition - statusGraphicOpenQuotePosition - 1);
int trendGraphicEndPosition = statement.IndexOf("TREND_GRAPHIC", lastCharacterPosition) + "TREND_GRAPHIC".Length;
int trendGraphicOpenQuotePosition = statement.IndexOf('\'', trendGraphicEndPosition + 1);
int trendGraphicCloseQuotePosition = statement.IndexOf('\'', trendGraphicOpenQuotePosition + 1);
string mdxStmtTrendGraphic = statement.Substring(trendGraphicOpenQuotePosition + 1, trendGraphicCloseQuotePosition - trendGraphicOpenQuotePosition - 1);
//Kpi kpiDeclaredInScript = new Kpi(mdxStmtKpiName, mdxStmtKpiName); //ok to use a guid as id because this is just a temporary store of the KPI in the kpisDeclaredInScript variable. It is referred to below without using the id/amo instance. This resolves issue where special characters are in the name (e.g. (, %, etc), which causes error if in the ID.
Microsoft.AnalysisServices.Kpi kpiDeclaredInScript = new Microsoft.AnalysisServices.Kpi(kpiName, Convert.ToString(Guid.NewGuid()));
kpiDeclaredInScript.Goal = goalName;
kpiDeclaredInScript.Status = statusName;
kpiDeclaredInScript.Trend = trendName;
kpiDeclaredInScript.StatusGraphic = mdxStmtStatusGraphic;
kpiDeclaredInScript.TrendGraphic = mdxStmtTrendGraphic;
kpisDeclaredInScript.Add(kpiDeclaredInScript);
}
}
}
//Note: here we are making the assumption that Measures are created in a FIXED way
// This routine will only find measures that have been created by following fixed pattern
// string.Format("CREATE MEASURE '{0}'[{1}]={2};", cmTableName, cmName, newCalculatedMeasureExpression.Text)
_measures.Clear();
_kpis.Clear();
foreach (ParsedCommand parsedCommand in parsedCommands)
{
foreach (string statement in parsedCommand.Expressions)
{
if ((statement.Length >= 14) && (statement.Substring(0, 14) == "CREATE MEASURE"))
{
//Find table name/measure/expression
string statementTableName = "";
if (parsedCommand.Table == "") //post SP1, there should always be a table/fullname and one single statement, so this check SHOULD be redundant
{
int openQuotePosition = statement.IndexOf('\'', 0);
int closeQuotePosition = statement.IndexOf('\'', openQuotePosition + 1);
statementTableName = statement.Substring(openQuotePosition + 1, closeQuotePosition - openQuotePosition - 1);
}
else
{
statementTableName = parsedCommand.Table;
}
//int closeSquareBracketPosition = statement.IndexOf(']', openSquareBracketPosition + 1);
int lastCharacterPosition = 0;
string statementMeasureName;
string statementMeasureExpression;
ParseMeasureAndExpression(parsedCommand.FullName, statement, out statementMeasureName, out statementMeasureExpression, ref lastCharacterPosition);
//check if it's a kpi measure
Microsoft.AnalysisServices.Kpi kpi = null;
if (_amoDatabase.CompatibilityLevel < 1103)
{
kpi = _amoDatabase.Cubes[0].Kpis.FindByName(statementMeasureName); //these are populated using the object model (pre SP1 only SOMETIMES)
}
// Could be declared in script (post-SP1, it should always be, pre SP1 SOMETIMES)
if (kpi == null)
{
//check if declared in MDX script instead (it normally would be)
foreach (Microsoft.AnalysisServices.Kpi kpiDeclaredInScript in kpisDeclaredInScript)
{
if (kpiDeclaredInScript.Name == statementMeasureName)
{
kpi = kpiDeclaredInScript;
break;
}
}
}
if (kpi == null)
{
// it's really a measure (not a KPI)
_measures.Add(new Measure(this, statementTableName, statementMeasureName, statementMeasureExpression.Trim()));
}
else
{
// it's really a KPI
//note: the kpiReferenceMeasures will be empty post SP1, but will fix it below. Can't fix it here because might not have all the measures populated yet (post sp1, kpi reference measures will be populated in _measures)
_kpis.Add(new Kpi(this,
statementTableName,
statementMeasureName,
statementMeasureExpression,
kpiReferenceMeasures.FindByName(kpi.Goal),
kpiReferenceMeasures.FindByName(kpi.Status),
kpiReferenceMeasures.FindByName(kpi.Trend),
kpi.StatusGraphic,
kpi.TrendGraphic
//,kpi
));
}
}
}
}
//post SP1, fix kpi reference measures
if (_amoDatabase.CompatibilityLevel >= 1103)
{
foreach (Kpi kpi in _kpis)
{
//get the AMO KPI
Microsoft.AnalysisServices.Kpi amoKpi = _amoDatabase.Cubes[0].Kpis.FindByName(kpi.Name); //these are populated using the object model - can also be declared as CREATE KPI in the MDX script, which will not be in this Kpi collection
if (amoKpi == null)
{
//check if declared in MDX script instead (it normally would be)
foreach (Microsoft.AnalysisServices.Kpi kpiDeclaredInScript in kpisDeclaredInScript)
{
if (kpiDeclaredInScript.Name == kpi.Name)
{
amoKpi = kpiDeclaredInScript;
break;
}
}
}
if (amoKpi != null)
{
// Now check _measures to get the kpi reference measures. Flag them as KPI reference measures and populate KPI reference measure properties
Measure kpiGoalReferenceMeasure = _measures.FindByName(amoKpi.Goal);
Measure kpiStatusReferenceMeasure = _measures.FindByName(amoKpi.Status);
Measure kpiTrendReferenceMeasure = _measures.FindByName(amoKpi.Trend);
//Flag the public KPI measures (like Measures.[M Goal] as IsKpiReferenceMeasure so doesn't show up on grid)
if (kpiGoalReferenceMeasure != null)
{
kpiGoalReferenceMeasure.IsKpiReferenceMeasure = true;
kpi.GoalMeasure = kpiGoalReferenceMeasure;
}
if (kpiStatusReferenceMeasure != null)
{
kpiStatusReferenceMeasure.IsKpiReferenceMeasure = true;
kpi.StatusMeasure = kpiStatusReferenceMeasure;
}
if (kpiTrendReferenceMeasure != null)
{
kpiTrendReferenceMeasure.IsKpiReferenceMeasure = true;
kpi.TrendMeasure = kpiTrendReferenceMeasure;
}
}
}
}
}
}
}