Data Manager

From the Data Manager you can manipulate data in bulk from Excel. This allows things as loading data from external sources, clearing data out of the database, extracting data to send to external sources, and running business rules to modify data.

Data can be loaded from an Excel table as shown above, or extracted into a table as shown above. To load data there must be a column for every dimension in the model. Additional columns are allowed. Additional columns provide a good way to perform transformations with formulas to derive the correct member values or numbers to load.

Allows a table to be loaded into the database from Excel. The data can be transformed with the full functionality of Excel with formulas like vlookup before loading. All security is fully enforced on the data load, so you can only load data to members you currently have write access to. When a load is attempted to a member that is not available to write to, the member will be highlighted in orange so you know why it is being skipped. Any invalid members encountered will be highlighted in red.

  • Current Worksheet – the data table to load is the entire currently selected worksheet. Row 1 of the worksheet must contain all dimension names displayed in the Control Panel. Any columns with other names will be skipped. This is useful when member names and values are being derived from intermediate results with formulas to perform data transformations.
  • Name Range – the data table to load is the block of cells contained in the named range.
  • Clear a data region based on the data to be loaded – this option provides the capability to clear a slice of the database as part of loading data. This is useful for scenarios where you receive data from another system such as a Ledger, and you are performing multiple loads of the same data during a reporting cycle. In this case, the data table you load may have contained data for one account in one entity in the first load, but in the second load there is no data for the same account. In this case, after the second load by default the account missing data in the second load would still contain the data from the first load. With this option, you could have the data from first load completely cleared out automatically by the second load. The dimensions you select for this option indicate which dimension you want to clear all the data from. So for this example, you would just need to select the Account dimensions to have all accounts cleared of data for any combination of other dimension members found in the data table being loaded.
  • Sum Values – this defaults to checked. When checked, if a load table contains multiple records with the same set of members, the values for all of the records will be added together and the total will be submitted to the database as a single value.
  • Skip Empty Values – this defaults to unchecked. When checked empty cells for numeric values are skipped, so existing data remains unchanged. When unchecked empty cells are loaded as a numeric value of zero. Note: To be skipped a cell must be empty. Cells containing empty strings are always loaded as value of zero.

For Data Load additional table layouts are supported. Dimension members can also be located on column headers to support things such as Time Pariods being in the column headers and all other members being on the rows. When using this table format there is no VALUE column. You can place as many dimension in the column headers desired, but at least one dimension must be located on the rows.

Sample layout:

Time Jan 2009 Feb 2009 Mar 2009
Account Scenario Entity TimeDataView
ExtSales Actual LE18 PERIODIC 100 125 175
ExtSales Actual LE14 PERIODIC 100 125 175
ExtSales Actual LE9 PERIODIC 100 125 175

Allows data to be cleared from the application. Only data that you have write access to can be cleared. To clear data you select a slice of data in the application, by selecting dimensions and members within the dimensions.

(Note: While Clear Data is powerful, it can also be easy to clear more data than you intended. An easy alternative method to clear allows you see exactly what data you are clearing and only clear exactly the data desired with great precision. For the alternative method, first extract all the data you want to clear, then review the records of data to insure it is exactly what you intended. Then change the Value of all desired records to 0, and use the Data Manager Load to clear just that data.)

Allows data to be extracted from the application into data tables. To extract data you select a slice of data in the application, by selecting dimensions and members within the dimensions.

  • Suppress if zero – records with a value of zero will not be included in the extract table.
  • Resolve Send To members – When checked members in the extract table will be returned as the member they would be sent from based on a reverse lookup of Send To definitions in the Database Locks setup. When unchecked members in the extract table will be returned as the base level members that the data resides in. Sometimes this is useful for moving data around within the application or for interfacing data to other systems.
  • Extract to current worksheet - Extract records to the current worksheet. All existing information in the current worksheet is deleted.

Select the rule to run, and then select dimensions and members for the slice of the application the rule will be run against.

Define the portion of the Model you want to retrieve Audit Log information for. When you click OK, an Audit Log Worksheet will be created containing a table of audit records. You can also perform a Rollback to values contained in the Audit Log with the right click menu. Note: You can only perform a roll back for values you have write access to.

  • canvasplanning/userguide/datamanager.txt
  • Last modified: 2022/02/22 14:43
  • by pcevli