Writing nested DB functions

With nested DB functions, you must ensure that the inner DB returns a valid argument to the outer DB.

Before you begin

The following procedure, which steps you through the creation of the preceding rules statement example, illustrates one good approach to creating nested DB functions.

Procedure

  1. In the Server Explorer, right-click the Purchase cube, and click Edit Rule.

    The Rules Editor should already contain a statement to calculate Price/Kg - USD, which you created earlier in this section.

  2. Insert a pound symbol (#) at the beginning of the existing statement.

    The pound symbol (#) is the comment character for rules.

  3. Create the following text in the Rules Editor on a new line beneath the existing statement. You can enter the text manually or use the toolbar buttons. 
    ['Purchase Cost - LC']=N:['Quantity Purchased - Kgs']*['Price/Kg-LC'];
#['Price/Kg - USD']=['Price/Kg - LC']\DB('MarketExchange',!market,!date);
['Price/Kg - USD']=['Price/Kg - LC']\
  4. Click the Insert Cube Reference button.

    The Insert Cube Reference dialog box displays.

  5. In the Select Cube list, select the CurrencyExchangeRate cube.

    The dimensions for the CurrencyExchangeRate cube appear in the Dimension list.

  6. Click OK.
  7. Insert a semi-colon (;) at the end of the statement.

    The rule statement should now appear as follows.

    ['Purchase Cost - LC']=N:['Quantity Purchased - Kgs']*['Price/Kg-LC'];
#['Price/Kg - USD']=['Price/Kg - LC']\DB('MarketExchange',!market,!date);
['Price/Kg - USD']=['Price/Kg - LC']\DB('CurrencyExchangeRate',!Currency,!date);

    Note that the DB function includes the argument !Currency, a reference to the current element of the Currency dimension. However, the rule statement is being written for the Purchase cube, which is composed of the dimensions FishType, Market, Date, and PurMeas. The Purchase cube does not include the Currency dimension; the DB formula as it now exists cannot resolve all arguments and displays an error message if you attempt to save the rule.

    Line 3: Syntax error on or before: !Currency,!date); invalid string expression Rule could not be attached to the cube, but changes were saved.

    You must replace !Currency with an argument that resolves to a dimension element of the Purchase cube.

  8. Select the text !Currency in the Rules Editor.
  9. Click Insert Cube Reference.

    The Insert Cube Reference dialog box displays.

  10. In the Select Cube list, select the Currency cube.

    The dimensions for the Currency cube appear in the dialog.

  11. Click the Subset Editor button for the Market Currency dimension.

    The Subset Editor opens with the sole element of the MarketCurrency dimension.

  12. Select the MarketCurrency element and click OK.
  13. Click OK in the Insert Cube Reference dialog box.

    The complete rule statement should appear as follows.

    ['Purchase Cost - LC']=N:['Quantity Purchased - Kgs']*['Price/Kg-LC'];
#['Price/Kg - USD']=['Price/Kg - LC']\DB('MarketExchange',!market,!date);
['Price/Kg - USD']=['Price/Kg - LC']\DB('CurrencyExchangeRate',DB('Currency', !market,'MarketCurrency'),!date);
  14. Click Save.
  15. Open the Jun -16 view of the Purchase cube to confirm that the new rule statement calculates values for Price/Kg - USD.
    Screen showing the Jun-16 view of the Purchase cube in the Cube Viewer.

    This model using nested DB functions is more complicated than the first model used to calculate Price/Kg - USD, but it has the significant advantage of storing exchange rates for each currency in just one place and using a lookup cube to determine the correct currency for each market.