C# SSIS Script Task AMO Delete SSAS Tabular Measure

Hey Everyone,

I'm mostly a SQL developer so bear with me but I'm trying to get a small C# script task in SSIS to delete a measure from a SSAS Tabular data model. An article I found gave a great example in how to do columns and that works great but I need to get rid of some measures as well.

You can see in the script below that I'm looping through dimensions & columns to delete those in the 1st section which works great, the 2nd section (indicated by "REMOVE MEASURE SECTION" is where I'm stuck, trying to delete measures from dimensions. Then the 3rd section is updating permissions on a tabular model. Thanks for anyone that can help me !

public void Main() { String ConnStr; String OLAPServerName; String OLAPDB; string TablesToRemoveColumnsFrom = (string) Dts.Variables["$Package::TablesToRemoveColumnsFrom"].Value; string TablesToRemoveMeasuresFrom = (string) Dts.Variables["$Package::TablesToRemoveMeasuresFrom"].Value; string ColumnsToRemove = (string) Dts.Variables["$Package::ColumnsToRemove"].Value; string MeasuresToRemove = (string) Dts.Variables["$Package::MeasuresToRemove"].Value; string[] tableArray = TablesToRemoveColumnsFrom.Split(';'); string[] MeasuretableArray = TablesToRemoveMeasuresFrom.Split(';'); string[][] columnArray = ColumnsToRemove.Split(';').Select(t => t.Split(',')).ToArray(); string[][] MeasurecolumnArray = MeasuresToRemove.Split(';').Select(t => t.Split(',')).ToArray(); string listOfUsersToAdd = (string) Dts.Variables["$Package::UsersToAddPermissionsFor"].Value; string[][] userArray = listOfUsersToAdd.Split(';').Select(t => t.Split(',')).ToArray(); string rolesToAddPermissionsTo = (string) Dts.Variables["$Package::RolesToAddPermissionsTo"].Value; string[] roleArray = rolesToAddPermissionsTo.Split(';'); OLAPServerName = (string) Dts.Variables["$Project::Connection_SSAS_ServerName"].Value;; OLAPDB = (string) Dts.Variables["$Package::NewCubeName"].Value; ConnStr = "Provider=MSOLAP;Data Source=" + OLAPServerName + ";"; Server OLAPServer = new Server(); OLAPServer.Connect(ConnStr); Console.WriteLine("ServerName : " + OLAPServerName); foreach(Database OLAPDatabase in OLAPServer.Databases) { if (OLAPDatabase.Name.ToString() == OLAPDB) { AMO.Database tabularDatabase = OLAPDatabase; int tableIndex = 0; int columnIndex = 0; string tableName; string columnName; while (tableIndex < tableArray.Length) { while (columnIndex < columnArray[tableIndex].Length) { tableName = tableArray[tableIndex].Trim(); columnName = columnArray[tableIndex][columnIndex].Trim(); // - Obtain table name in DSV string datasourceTableName = tabularDatabase.Dimensions.GetByName(tableName).ID; // - Obtain Column ID string datasourceColumnName = tabularDatabase.Dimensions[datasourceTableName].Attributes.GetByName(columnName).ID; // Removing Column, as attribute, from dimension tabularDatabase.Dimensions[datasourceTableName].Attributes.Remove(datasourceColumnName); columnIndex++; } columnIndex = 0; tableIndex++; } //Update cube with limited columns tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate); //REMOVE MEASURE SECTION int mtableIndex = 0; int mcolumnIndex = 0; string mtableName; string mcolumnName; while (mtableIndex < MeasuretableArray.Length) { while (mcolumnIndex < MeasurecolumnArray[mtableIndex].Length) { mtableName = MeasuretableArray[mtableIndex].Trim(); mcolumnName = MeasurecolumnArray[mtableIndex][mcolumnIndex].Trim(); string mdatasourceTableName = tabularDatabase.Dimensions.GetByName(mtableName).ID; using(AMO.MdxScript modelMDxScript = tabularDatabase.Cubes[0].MdxScripts["MDXScripts"]) modelMDxScript.CalculationProperties.Remove(mcolumnName); mcolumnIndex++; } mcolumnIndex = 0; mtableIndex++; } //Update cube with limited columns tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate); //Add Membership to new cube //string[] windowsUserOrGroupArray = listOfUsersToAdd.Split(','); Role role; int roleIndex = 0; int userIndex = 0; string roleName; string userName; while (roleIndex < roleArray.Length) { roleName = roleArray[roleIndex].Trim(); role = tabularDatabase.Roles.FindByName(roleName); while (userIndex < userArray[roleIndex].Length) { userName = userArray[roleIndex][userIndex].Trim(); role.Members.Add(new AMO.RoleMember(userName)); userIndex++; } userIndex = 0; roleIndex++; } //Update membership additions tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate); } } Dts.TaskResult = (int) ScriptResults.Success; } #region ScriptResults declaration /// <summary> /// This enum provides a convenient shorthand within the scope of this class for setting the /// result of the script. /// /// This code was generated automatically. /// </summary> enum ScriptResults { Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure };#endregion } } 

by SenatorSquires via /r/csharp

Leave a Reply