Import Preconverted Actuals to a Model

Import preconverted actuals and use them in a story.

Context

Note

The steps below are geared towards classic account models, and valid for both when creating a blank model or creating a model from a file, unless stated otherwise. In a model with measures, these steps don't apply. You can create a separate base measure to hold your preconverted currency data. See Work with Currencies in a Model with Measures for details.

For information about currency conversion in a classic account model, see Work with Currencies in a Classic Account Model.

Procedure

  1. Create a new model, either from scratch, or using a data file like an Excel spreadsheet for instance.
    Note
    The data should not yet contain preconverted actuals, because the model is not yet configured to accept preconverted actuals. If the Excel worksheet contains a column of preconverted actuals data, temporarily delete that column before importing the file.

    For more information on how to create a model, see Create a New Model.

  2. If you're creating a model using a data file, do the following steps. If you're creating a blank model, skip to step 3.
    1. Before saving the model, during the data preparation, select Enable Planning if you want the option to add currency conversion later in stories, and select a Date dimension.
    2. Select Create Model before doing any mapping.
    3. Switch to the Model Structure view.
    4. Select (Delete Facts), select all members, and then select OK.
      This temporarily clears the model data so that you can switch on currency conversion.
    5. Select the dimension that separates the data into different currencies, and then in the Dimension Settings panel, under System Properties, switch on Enable Currency.
      For example, this dimension could be an Organization dimension, or a generic Source Currency dimension.
  3. In the model preferences:
    1. If you're creating a model from scratch, switch Planning Capabilities on if you want the option to add currency conversion later, in stories.
    2. Under the Currency tab, toggle Currency Conversion on.
    3. Select a default currency.
      Note
      If you're creating a model using a data file, choose the currency that your preconverted actuals have been converted to.
    4. Select a currency rate table.
    5. Enable the Preconverted Actuals option.
  4. Select the Account dimension or create it if you started with a blank model and don't have one already, and choose values for the Account Type, Rate, and Units & Currencies properties, for all members in the dimensions.
    If you've created a model using a data file, set the value for Units & Currencies to Currency.
    Note
    If you have many members in the Account dimension, it can be impractical to set the Currency value for each member individually.

    An alternative method is to prepare your data file by adding Account Type, Rate Type, and Units & Currencies columns in your file, and then when you import the file, map those columns to the Account dimension. As long as the Update dimension with new values mapping option is selected (it's selected by default when importing data), you can load those values for all members at once. For more information, see Import and Prepare Fact Data for a Classic Account Model and Import and Prepare Fact Data for a Model with Measures.

  5. Add values for the organization dimension:
    • If you've created a blank model, add an organization dimension and enter organizational and Currency information.
    • If you've created a model using a data file, select the organization dimension and fill in the values for the Currency property for all members in the dimension.
    Note
    Once Currency Conversion is switched on, the model needs a currency dimension, which defines the organization of the data in the model. For example, the Organization dimension might include a list of regions, each of which corresponds to a particular currency. These currencies are the Local Currency (see step 11). For more information, see Work with Currencies in a Classic Account Model.
  6. Save the model.
  7. Switch to the Data Management view.
  8. Click Start of the navigation path Next navigation step Import DataEnd of the navigation path, choose the data file that contains the preconverted currency data, and select Import.
  9. Select the imported data from the Draft Sources list, and map the data to the model's dimensions.

    First, map the account dimension (must be actuals). After you've mapped the account dimension, the Measures card can be expanded to two cards: Local Currency (also known as source currency) and Default Currency. Drag the preconverted actuals data to the Default Currency card, and the standard measure data to the Local Currency card.

    See Combine Data with Your Acquired Data for details on mapping data.

  10. Select Finish Mapping, and then save your model.
    After creating the model, you'll typically create a story with a table to include the preconverted actuals.
  11. From the Stories start page, select Canvas.
  12. Add a table to the canvas.
  13. Choose your new model as the data source for the table, and select OK.
  14. Select the table, and then open the (Builder) panel.
  15. Under Start of the navigation pathTable Structure Next navigation step ColumnsEnd of the navigation path, select Add Measures/Dimensions.
  16. Select Cross Calculations.
  17. Point to the calculation you just added, and select (Manage Filters).
  18. In the Available Members list, you can select both the source currency and preconverted data members to include both in your table.
Related Information