Map a Source System to Cube

As your business grows and evolves and new line items are added to source systems, you'll need to map those new source dimensions into Cube or create mapping rules to ensure your information stays current and data can be successfully imported.

In this article, learn how to map your source dimensions:

Find your source system in the list on the Integrations page and click the three dots for More, then click Mapping.

Here, you can review or set mapping rules, search or filter by mapping status or date changed, and review mapping for each dimension member connected to that source system. The number at the top of each tab tells you how many dimension members are unmapped.

There are a few ways to map source system dimensions to Cube dimensions:

• Set up mapping rules to automatically map dimensions
• Manually edit dimension members on the mapping page
• Download an Excel file of dimension members to bulk map them

Mapping a QuickBooks Online (QBO) connection? QBO accounts without account numbers are considered parent accounts and cannot be mapped. Add an account number to the Account Number field in QBO to map in Cube. 

Create rules to automatically map dimensions

Use mapping rules to handle new source system dimensions during an import to ensure imports run smoothly and data comes into the correct location in Cube without manual effort.

Mapping rules can be created and managed from the mapping page or while scheduling imports. To open mapping rules, click the edit icon next to Mapping Rules at the top of the Mapping page. 

Or click the edit icon in the Mapping Rules section while scheduling an import. 

Choose from the following options for each dimension when setting rules:

• Map new source dimensions to an existing Cube dimension member.
• Create a new Cube dimension member for every new source dimension.
• Or choose none to not set a rule. This option requires manual intervention if a new dimension is found during an import. 

When mapping rules are set, Cube will map any unmapped dimension member (including ignored dimension members that now have data associated with them) according to the rules. Dimension members can always be remapped if needed using either method below.

Edit mapping

Click Edit Mapping, then find the dimension member(s) that need to be mapped. The fastest way is to click the number at the top of the page to filter to unmapped dimensions.

When mapping a dimension member, you can use a many-to-one or one-to-one mapping strategy to organize your data in Cube for effective reporting and planning. Alternatively, you can choose to ignore dimensions that don't have associated data. 

Sign flipping

Regardless of which mapping strategy you use, you’ll also be able to indicate whether the account dimension member should be flipped to the opposite sign when pulled into Cube by switching the toggle in the Flip Sign column.

For source system integrations, historic data for flipped accounts will be updated when the changes are saved. When flipping signs for data tables, the change will be saved and applied to the next upload. 

Many-to-one mapping or one-to-one mapping

If the Cube dimension member already exists, like in a many-to-one mapping setup, type the name into the dropdown or select it from the list:

To add a new dimension member, like in a one-to-one mapping setup, select Add Dimension from the dropdown:

Name the dimension members and set the parent and rollup type if needed. Then click Save.

Ignoring dimension members

Ignored dimension members are those that haven't had data associated with them in the past and won't have data associated with them in the future. These dimensions don't need to be mapped to Cube dimensions, saving you from creating a dimension member just to complete an import. 

Some source systems, such as QuickBooks Online, have data associated with deleted or deactivated dimensions. Deleted dimensions that contain data will need to be mapped and cannot be ignored. 

Ignore a dimension member by selecting Ignore Dimension from the dropdown. 

If Cube finds data associated with an ignored dimension member during a future sync with the source system, the dimension will automatically be mapped according to your mapping rules or updated to the unmapped status so it can be properly mapped before importing. 

3. Click Save Mapping. You'll receive a confirmation message that the save has been completed.

Bulk upload mapping

If you have many dimension members to map or remap, it may be faster to upload your mapping file to our bulk mapping feature. You can use a bulk mapping upload to change current mappings or map unmapped dimensions. 

Click the Upload Mapping icon to begin.

Follow the directions to create a file in the correct format, or click Download mapping to work from a file with all dimensions from your source system. 

When downloading the mappings, you'll receive an Excel file with directions in the first tab and your dimensions in the second tab. Use the same mapping strategies as above to set up your file:

Many-to-one mapping or one-to-one mapping

List the name of the dimension member in the Cube Dimension column. You can specify the dimension alone or the full dimension path.

Example:

In your mapping file:

Be sure the Cube dimension member you reference in your file exists in your Cube. If you need to add a dimension member, follow this guide. 

Ignoring dimension members

Bulk mapping can also be used to indicate that a dimension member should be ignored if it has not and will not have data associated with it. In the Cube Dimension column, write Ignored. 

Source dimensions that have any data associated with them, including those that were deleted or deactivated and still contain data, will need to be mapped during an import and cannot be ignored.

Flipping Signs

Indicate whether the account should be sign-flipped or not by entering Yes or No in column C.

Your file will look like the file below when you've completed your mapping and are ready to upload.

When your file is prepared, click Next in Cube to select and upload it. Cube will validate the mappings, and you can make corrections before submitting them. 

Click Transform to describe how the AI assistant should update your data by applying logic to any field in your columns. See changes and approve them before uploading.

Cube will confirm that the changes have been made and then you're ready to import your data.