## Adding columns for outline formulas

In this recipe, we will add columns to our `MEASURES`

table, so that we can later add a formula to the dimension's members. The importance of this is apparent when you consider that the
**Aggregate Storage** (**ASO**) model does not use the same syntax as the **Block Storage** (**BSO**) model for their outline formulas. The ASO outline uses **Multidimensional Expressions** (**MDX**), which is the standard syntax convention for OLAP applications. We can use our table for both BSO and ASO applications by adding an additional column for the ASO model's formulas.

### Getting ready

To get started, open SQL Server Management Studio, and add a database called `TBC`

. In this recipe, we are using T-SQL, but the PL-SQL equivalent for the examples has been included in the following code snippet. The `MEASURES`

dimension was created in the recipe *Setting up an Account or Measure dimension with parent-child reference* in Chapter 1. We need to complete step 1 of the aforementioned recipe before we continue.

### How to do it...

Execute the following script to add a column to the

`MEASURES`

table. Following the script is the screenshot of the table after the modification:--This is the syntax in T-SQL Alter Table MEASURES Add FORMULA_MDX VARCHAR(4000) NULL; --This is the syntax in PL\SQL Alter Table MEASURES ADD FORMULA_MDX VARCHAR2(4000) NULL; --Delete content of the table to avoid issues with executing this execise Delete From MEASURES;

Execute the following script to add a row with the new formula:

--This is the syntax for both T-SQL and PL\SQL INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,SKIP,UDA,FORMULA, COMMENT_ESSBASE, FORMULA_MDX) Values(0, 14, 'Ratios', 'Margin %', '', '+', 'T', 'X', '', '', '', '', 'Margin % Sales;', '', '[Measures].[Sales] / [Measures].[Margin];'); INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,SKIP,UDA,FORMULA, COMMENT_ESSBASE, FORMULA_MDX) Values(0, 15, 'Ratios', 'Profit %', '', '~', 'T', 'X', '', '', '', '', 'Profit % Sales;', '', '[Measures].[Sales] / [Measures].[Profit];');

### How it works...

In step 1, the column `FORMULA_MDX`

is added to the `MEASURES`

table. The script in step 2 adds the new rows with the `FORMULA_MDX`

column included. The objective of this recipe is to show you that the syntax is different every time you use a table for both an ASO and BSO set of applications, so you need to have two formula columns. You can see how different the syntax is in the following code snippet, but if you need a more detailed explanation on this, please visit: http://www.oracle.com/technetwork/middleware/bi-foundation/4395-calc-to-mdx-wp-133362.pdf. This is Oracle's white paper on *Converting Calc Formulas to MDX in an Essbase Outline*.

FORMULA |
FORMULA_MDX |
---|---|

Margin % Sales; |
Measures.Sales / Measures.Margin; |

Profit % Sales; |
Measures.Sales / Measures.Profit; |