This project has moved. For the latest updates, please go here.

Remove Measure

Dec 23, 2015 at 7:58 PM
Hey Everyone,

I am trying to simply delete measures from a tabular model in C#/AMO but I don't have C# experience and can't get it to work.

I think i'm just looking for the syntax to identify a measure, then delete it but I've pasted my full script below. It works if I loop through tables/columns to delete columns but I want to use the 2nd section to delete measures.

Thanks for anyone that can help !
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);

                   


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

                       //  -   Obtain table name in DSV
                       string mdatasourceTableName = tabularDatabase.Dimensions.GetByName(mtableName).ID;
                       
                       //  -   Obtain Column ID
                       // original string mdatasourceColumnName = tabularDatabase.Dimensions[mdatasourceTableName].Attributes.GetByName(mcolumnName).ID;
                       using (AMO.MdxScript modelMDxScript = tabularDatabase.Cubes[0].MdxScripts["MDXScripts"])
                       //  Removing Column, as attribute, from dimension
                       
                       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

    }
}
Dec 24, 2015 at 12:50 AM
From: DavidSquires
Hey Everyone,

I am trying to simply delete measures from a tabular model in C#/AMO but I don't have C# experience and can't get it to work.

I think i'm just looking for the syntax to identify a measure, then delete it but I've pasted my full script below. It works if I loop through tables/columns to delete columns but I want to use the 2nd section to delete measures.

Thanks for anyone that can help !
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);

                   


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

                       //  -   Obtain table name in DSV
                       string mdatasourceTableName = tabularDatabase.Dimensions.GetByName(mtableName).ID;
                       
                       //  -   Obtain Column ID
                       // original string mdatasourceColumnName = tabularDatabase.Dimensions[mdatasourceTableName].Attributes.GetByName(mcolumnName).ID;
                       using (AMO.MdxScript modelMDxScript = tabularDatabase.Cubes[0].MdxScripts["MDXScripts"])
                       //  Removing Column, as attribute, from dimension
                       
                       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

    }
}
Read the full discussion online.
To add a post to this discussion, reply to this email ([email removed])
To start a new discussion for this project, email [email removed]
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com
Dec 24, 2015 at 3:02 PM
Edited Dec 24, 2015 at 3:17 PM
Hi JPJ- I see you responded but it looks like it's just a copy of my question ? Thank you if you are able to help !