Adding variables

To add a variable:

  1. On the Microsoft SQL Server computer, start SQL Server Management Studio.
  2. Go to the Tables folder under the appropriate database folder.
  3. Select the Variable table and edit the rows. At least two rows are returned: one with the MEM_NAME of Cost and one with Success. If you changed the name of the Cost variable, the new name appears instead.
  4. Click in the last row, which should be empty.
  5. In the MEM_ID column, specify the next largest positive integer. For example, the first row might have Cost with a MEM_ID of -2 and the second row might have Success with a MEM_ID of 0. If this was the case and you are adding the next row, you would specify 1.
  6. In the MEM_NAME column, specify the variable name and in the MEM_DESC column, specify a description, which is the text shown when you hover your mouse over the variable in reports.
  7. In the MEM_PID column, specify -1. If this variable is to be a child of another variable as part of the hierarchy, then put the parent's MEM_ID value in the MEM_PID column.
  8. In the MEM_ORDER column, you can change the numbers of any of the rows. This will affect the order that members are displayed.
  9. Do not specify values into the CALC and UNARY columns unless you want to customize a calculation. If you want a customized calculation, you can specify a member formula into the CALC column. This is in the form of standard MDX. The formula can be conditional and include any variable. For example, [Variable].&[1]-[Variable].&[7]. For more elaborate calculations see the calculations used in the ACCUMULATION table. If this is a parent of other variables, the UNARY column set to NULL or + will result in the value of the parent being the roll-up sum of the values of its children. To stop the roll-up, specify a value of ~ (tilde). (These are standard settings for Analysis Services.)
  10. In the ORIG_MEMBER column, specify the same number that is in the MEM_ID column.
  11. In the HIER column, specify the word Variables.
  12. Typically, do not specify a value for the MEM_OPTIONS column. If you want to specify the solve order of calculations you need to specify SOLVE_ORDER='<number>' where <number> is an integer and the highest number is done last. (These are standard options for Analysis Services.)
  13. In the RUNNINGTOTAL column, if you want measure values to accumulate over time, specify Y. If you do not, specify N. You can change this later in the Strategy Management client using Edit Variables in the Options menu.
  14. In the UNIT_ACCUM_METHOD, if you want the Unit Consolidation Method to be None, specify 2. If you want it to be Sum, specify 0. You can change this later in the Strategy Management client using Edit Variables in the Options menu.
  15. In the OBJECT_ACCUM_METHOD, if you want the Object Consolidation Method to be None, specify 2. If you want it to be Sum, specify 0. You can change this later in the Strategy Management client using Edit Variables in the Options menu.
  16. Do not specify a value in the RELATIONAL_ID column.
  17. In Analysis Services Analysis Manager or SQL Server Management Studio, process the Database.
  18. Exit the application.
You can change the description for a variable by modifying the MEM_DESC column.

You can delete variables from your plan but you must have two variables in your plan. One must be the Success measure variable with ID of zero.

You must process the database after making any changes.