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.
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.
Execute the following script to add a column to the
MEASUREStable. 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];');
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.