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