AX1479

 

Driver table names and file group variables

When configuring Save Type 3 or Save Type 1 for a driver file, the driver table name should meet the following requirements:

• Each save process should use a unique table name. This is an absolute requirement for Save Type 3, and a recommendation for Save Type 1.

• The table names should include some indicator of the file group that it belongs to.

• The table names should be defined as file group variables instead of "hard-coded" within the driver file. The GetFileGroupVariable function can be used to return the appropriate table name for each save process.

Driver table names

When using Save Type 3, each driver sheet that is set up to save to the database must have a unique table name for the Save Type 3 configuration. The name must be unique not only within the file (meaning each sheet must save to a unique table name), but it must be unique across the Axiom Software system. If two sheets in the same file save to the same table name, or if two different files in the system save to the same table name, then the last sheet or file that is processed will overwrite the previous instance of the table.

For example, imagine that you have two driver files, one for your North American business units and one for your European business units. Both files have a Statistics sheet that you want to save to the database. Each of these sheets must be assigned a unique table name in the Save Type 3 settings, such as StatisticsNA and StatisticsEU.

When using Save Type 1, it is recommended to use a unique table name for each save process in the drivers, although it is not a requirement in this case. Using unique table names for each distinct sheet of data in the drivers makes it easier to set up the save processes and keep track of which data is being saved where. However, if it is appropriate for your system design, you can have multiple Save Type 1 processes in the same driver file or in different driver files that save to the same target driver table. To continue the previous example, you could have one Statistics table where you save the North American statistics from one driver file and the European statistics from another driver file. The data would have to share the same structure and include a grouping column where each row of data was identified as belonging to either North America or Europe.

Additionally, because file groups and their driver files are often cloned to create new file groups, it is recommended to dynamically construct the driver table names using some indicator of the file group that it belongs to. That way when the file group is cloned, new driver tables will be created for the new file group and the old tables will be left as is for use in the old file group. For example, the full table names from the previous example could be something like BGT19_StatisticsNA and BGT19_StatisticsEU, where the drivers belong to the Budget 2020 file group. The best way to enforce this dynamic naming convention is to use file group variables to define the driver table names.

Using file group variables

Ideally, your driver table names should be defined using table variables for the file group. You can then reference the table variables in the save-to-database settings using the GetFileGroupVariable function. Use of table variables allows you to manage these tables within the file group properties and also enables rapid development of planning scenarios using the file group scenarios feature. For more information, see the following topics:

• File group variables
• File group scenarios

NOTE: If you have enabled the file group option to restrict saves to the target tables of table variables, then the designated table name in the save-to-database settings must match a table listed in the Table Variables tab for the file group. If it does not, the save-to-database process will fail.

The following is an example of how you can use table variables to create dynamic driver table names, and then reference those variables in the Save Type 3 settings:

• In the file group settings, you can create a file group variable such as {ShortFGName}. This variable could reference the short version of the file group year and resolve to something like BGT20 (for a file group named Budget 2020). This variable could be used in several places, such as in the file group tab prefix, as well as in table variables for your drivers.

  

• When defining the table variables for the driver tables, use the ShortFGName variable as part of the table variable. For example, a driver table that holds escalator values might be a table variable named GlobalEscalators. This variable is defined as {ShortFGName}_Escalators, which resolves to BGT20_Escalators.

  

• In the Save Type 3 settings for the driver file, to define the table name, use the function GetFileGroupVariable to return the value for the GlobalEscalators variable.

  

Now the table name is dynamic, and it will change as the file group's variables change. If this file group is cloned and the year is changed to 2021, then the table name will resolve to BGT21_Escalators to keep the table name unique.

NOTE: The same approach applies to Save Type 1 driver files. You can use the GetFileGroupVariable function to return the expected name of the table, and then reference that name in the Save2DB tag to determine the destination table for the save-to-database.