private static void KpiDrop_2012SP1(AMO.Database tabularDatabase,
string tableName,
string kpiName)
{
using (AMO.MdxScript mdxScript = tabularDatabase.Cubes[0].MdxScripts[MdxScriptStringName])
{
Regex kpiTypeExpression = new Regex(KpiPattern, RegexOptions.CultureInvariant | RegexOptions.IgnoreCase | RegexOptions.Multiline);
foreach (AMO.Command modelCommand in mdxScript.Commands)
{
if (kpiTypeExpression.IsMatch(modelCommand.Text))
{
string matchKpiName = kpiTypeExpression.Match(modelCommand.Text).Groups["kpiName"].Value.Replace("[", string.Empty);
matchKpiName = matchKpiName.Replace("]", string.Empty);
if (string.Compare(kpiName, matchKpiName, StringComparison.InvariantCultureIgnoreCase) == 0)
{
AMO.CalculationPropertyCollection modelCalculationProperties = mdxScript.CalculationProperties;
// Remove KPI properties
modelCalculationProperties.Remove(kpiName);
// To be sure that changing the command expression doesn't affect the search...
// The search is performed over the AMO command text --> modelCommand.Text
// But the removal of text is done over the copy --> measureRemovedCommands
bool found = false;
string measureRemovedCommands = modelCommand.Text.Replace(kpiTypeExpression.Match(modelCommand.Text).Groups[0].Value, string.Empty);
Regex commandTypeExpression = new Regex(MemberPattern, RegexOptions.CultureInvariant | RegexOptions.IgnoreCase | RegexOptions.Multiline);
// Note: Here we assume Goal, Status and Trend members were added following the
// defined practice for MS SQL Server 2012 Tabular models of giving them the
// names according to the following patterns:
// Goal 'Member' name in MdxScripts:
string goalMemberName = string.Format(CultureInfo.InvariantCulture, "[_{0} Goal]", kpiName);
// Status 'Member' name in MdxScripts:
string statusMemberName = string.Format(CultureInfo.InvariantCulture, "[_{0} Status]", kpiName);
// Trend 'Member' name in MdxScripts:
string trendMemberName = string.Format(CultureInfo.InvariantCulture, "[_{0} Trend]", kpiName);
// To be sure that changing the command expression doesn't affect the search...
// The search is performed over the AMO command text --> modelCommand.Text
// But the removal of text is done over the copy --> measureRemovedCommands
found = false;
measureRemovedCommands = modelCommand.Text;
foreach (Match match in commandTypeExpression.Matches(modelCommand.Text)) // search
{
string matchMemberName = match.Groups["memberName"].Value;
// Remove the Goal 'Member' from the list of commands in MdxScripts
// Remove the Status 'Member' from the list of commands in MdxScripts
if ((string.Compare(goalMemberName, matchMemberName, StringComparison.InvariantCultureIgnoreCase) == 0) ||
(string.Compare(statusMemberName, matchMemberName, StringComparison.InvariantCultureIgnoreCase) == 0))
{
measureRemovedCommands = measureRemovedCommands.Replace(match.Groups[0].Value, string.Empty); // removal
found = true;
}
}
if (found)
{
modelCommand.Text = measureRemovedCommands;
}
//Removing the Status member Calculation Property
modelCalculationProperties.Remove(statusMemberName);
//Removing the Goal member Calculation Property
modelCalculationProperties.Remove(goalMemberName);
}
}
}
}
}
private static void KpiDrop_2012RTM(AMO.Database tabularDatabase,
string tableName,
string kpiName)
{
// Major steps in dropping a KPI from a table in the database
//
// - Store the existing MdxScript in a 'temporary' variable
// - Remove the KPI element from the 'temporary' variable
// - Remove the Status element from the 'temporary' variable
// - Remove the Goal element from the 'temporary' variable
// - Remove the Trend element from the 'temporary' variable
// - Replace existing MdxScript with 'temporary' variable
// - Remove from Calculation Properties the KPI, Status, Goal and Trend elements
//
// Important Conceptual Note:
// In Tabular Models a KPI is a Measure that has been promoted to KPI. Deleting a KPI
// doesn't delete the undelying measure.
//
// Note: Measures are dynamic objects that live inside an MdxScript object in the cube
//
// IMPORTANT Note: Measures must not be created as native OLAP measure objects, from the MeasureGroup object.
// !! Native OLAP measures cannot be:
// • Referenced by DAX queries
// • Referenced by calculated columns
// • Referenced by other DAX measures
//
// Note: There are no validations for duplicated names, invalid names or
// similar scenarios. It is expected the server will take care of them and
// throw exceptions on any invalid situation.
//
// Note: In AMO, strings as indexers refer to the ID of the object, not the name
//
// Note: Only one DataSourceView is used in Tabular Models
// ==> tabularDatabase.DataSourceViews[0] represents the DSV of the model
//
// Note: Only one Cube is used in Tabular Models
// ==> tabularDatabase.Cubes[0] represents the cube of the model
//
// Note: Only one Commands script is used in Tabular Models to hold all ALL Measures and KPIs of the model
// ==> tabularDatabase.Cubes[0].MdxScripts["MdxScript"].Commands[1].Text contains ALL Measures and KPIs of the model
//
// Note: Microsoft design tools use the following pattern to keep track of the
// datasource matching elements:
// DataSourceView->TableName <---> Dimension.ID, MeasureGroup.ID
// DataSourceView->ColumnName <---> Dimension->ColumnID, MeasureGroup.DegeneratedDimension->CoumnID
// So far, this sample follows the same pattern.
//
// WARNING: Breaking the above pattern when creating your
// own AMO to Tabular functions might lead to
// unpredictable behavior when using Microsoft
// Design tools in your models.
using (AMO.MdxScript modelMdxScript = tabularDatabase.Cubes[0].MdxScripts[MdxScriptStringName])
{
AMO.Command modelCommand = modelMdxScript.Commands[1];
AMO.CalculationPropertyCollection modelCalculationProperties = modelMdxScript.CalculationProperties;
// Remove KPI properties
modelCalculationProperties.Remove(kpiName);
Regex commandTypeExpression = new Regex(KpiPattern, RegexOptions.CultureInvariant | RegexOptions.IgnoreCase | RegexOptions.Multiline);
// To be sure that changing the command expression doesn't affect the search...
// The search is performed over the AMO command text --> modelCommand.Text
// But the removal of text is done over the copy --> measureRemovedCommands
bool found = false;
string measureRemovedCommands = modelCommand.Text;
foreach (Match match in commandTypeExpression.Matches(modelCommand.Text)) // search
{
string matchKpiName = match.Groups["kpiName"].Value.Replace("[", string.Empty);
matchKpiName = matchKpiName.Replace("]", string.Empty);
if (string.Compare(kpiName, matchKpiName, StringComparison.InvariantCultureIgnoreCase) == 0)
{
measureRemovedCommands = measureRemovedCommands.Replace(match.Groups[0].Value, string.Empty); // removal
found = true;
break;
}
}
if (found)
{
modelCommand.Text = measureRemovedCommands;
}
else
{
throw new InvalidOperationException(Resources.KpiDoesNotExistInvalidOperationException);
}
commandTypeExpression = new Regex(MemberPattern, RegexOptions.CultureInvariant | RegexOptions.IgnoreCase | RegexOptions.Multiline);
// Note: Here we assume Goal, Status and Trend members were added following the
// defined practice for MS SQL Server 2012 Tabular models of giving them the
// names according to the following patterns:
// Goal 'Member' name in MdxScripts:
string goalMemberName = string.Format(CultureInfo.InvariantCulture, "[_{0} Goal]", kpiName);
// Status 'Member' name in MdxScripts:
string statusMemberName = string.Format(CultureInfo.InvariantCulture, "[_{0} Status]", kpiName);
// Trend 'Member' name in MdxScripts:
string trendMemberName = string.Format(CultureInfo.InvariantCulture, "[_{0} Trend]", kpiName);
// To be sure that changing the command expression doesn't affect the search...
// The search is performed over the AMO command text --> modelCommand.Text
// But the removal of text is done over the copy --> measureRemovedCommands
found = false;
measureRemovedCommands = modelCommand.Text;
foreach (Match match in commandTypeExpression.Matches(modelCommand.Text)) // search
{
string matchMemberName = match.Groups["memberName"].Value;
// Remove the Goal 'Member' from the list of commands in MdxScripts
// Remove the Status 'Member' from the list of commands in MdxScripts
// Remove the Trend 'Member' from the list of commands in MdxScripts
if ((string.Compare(goalMemberName, matchMemberName, StringComparison.InvariantCultureIgnoreCase) == 0) ||
(string.Compare(statusMemberName, matchMemberName, StringComparison.InvariantCultureIgnoreCase) == 0) ||
(string.Compare(trendMemberName, matchMemberName, StringComparison.InvariantCultureIgnoreCase) == 0))
{
measureRemovedCommands = measureRemovedCommands.Replace(match.Groups[0].Value, string.Empty); // removal
found = true;
}
}
if (found)
{
modelCommand.Text = measureRemovedCommands;
}
//Removing the Status member Calculation Property
modelCalculationProperties.Remove(statusMemberName);
//Removing the Trend member Calculation Property
modelCalculationProperties.Remove(trendMemberName);
//Removing the Goal member Calculation Property
modelCalculationProperties.Remove(goalMemberName);
}
}
