When you fetch data to your spreadsheet and notice a discrepancy, you'll want to review your dimension members and your spreadsheet to see what is causing the variation. Follow the steps below to tie out your data:
If you still notice issues after completing these steps, our support team is ready to help!
Drilldown in the Spreadsheet App is an important feature for finding discrepancies within data. Drilling into a value in your spreadsheet will show you the line items from your source system so you can see exactly which data is rolling up into a given fetched value.
Check the setup of your spreadsheet
The first thing to check when having tie-out issues is your spreadsheet. Cube uses the dimension member names in your rows and columns to locate data when fetching, so it can't properly pull your data if it doesn't recognize the dimension names. Use the Spreadsheet App to verify that your spreadsheet is set up correctly:
Validate dimensions
Use the validate function in your Spreadsheet App to ensure that Cube recognizes every dimension member within your fetch range. Your Spreadsheet App will list any dimensions flagged as unrecognizable so you can update them.
After updating any invalid dimension members, fetch again and see if your data ties.
Check your range for duplicates
If you have the same dimension in more than one row in your range when publishing, Cube will record all the values separately. Depending on the number of duplicates, this could create two or more entries, which can result in higher than expected values.
You can resolve this issue by removing the duplicate row or column from your range and re-publishing it, which will overwrite the previous publish.
Here's how to check if duplicate values were published:
- Log in to your Workspace and select the Data Exporter from the navigation bar.
- Use the filters to select the dimension member(s) that contain the suspected duplicate values.
- After confirming that duplicates exist, go back to your spreadsheet, remove the duplicate row or column from your range, and re-publish it to overwrite the previous publish.
You then remove the duplicative columns, reselect the range, and re-fetch the values to see if this solves the tie-out issues.
Take a look at date formats
You’ll want to ensure the spreadsheet didn’t convert any time dimensions on your sheet to a date because this can cause data to be fetched incorrectly and data not to tie.
For example, typing Jan-22 into a cell will convert it to a date of January 22nd for the current year, but in Cube, Jan-22 means January 2022.
Prevent date reformating by putting a single quote in front of your dates to force them to remain as expected: ‘Jan-22
This article lists all date formats recognized by the Spreadsheet App.
After reviewing the data formatting in your spreadsheet, fetch again and to see if your data ties.
Confirm range filters
Sometimes, filter selections or dimension hierarchy changes can cause data tie-out issues. For any dimensions used as a filter, rather than listed in the rows and columns of your spreadsheet, confirm the selections match the data you expect to see.
For example, if you are using a filter such as “All Departments,” you’ll also want to check that all of your Department dimension members are underneath it, as expected.
If a dimension member is missing or outside of the hierarchy, it will not be included in the "All Departments" rollup.
Correct your hierarchy by re-parenting dimension members to the appropriate locations and refreshing your Spreadsheet App to see the updates.
After reviewing your spreadsheet, you can fetch your data again. If you are still seeing data discrepancies, review your dimensions and source data connections in the Cube Workspace.
Review your dimensions
Data discrepancies may also occur when dimensions need review. If you can identify a particular dimension member that isn't tying out correctly, you can check it in your Workspace to ensure it was set up and mapped correctly.
Clear clearing accounts
If you use a Clearing Account as a default dimension member or in a mapping rule for scheduled imports, you'll want to make sure it is clear to avoid tie-out issues when fetching. You can quickly do this by fetching all full-year time dimensions (2022, 2023, 2024, etc.) for that account. If you find data here, you'll want to remap dimensions so they are mapped to the appropriate account.
Check time rollup types
Check the time rollup settings on your dimensions to ensure they are set properly. Dimensions configured with no time rollup setting will not show data aggregated across time (e.g., at the quarterly or yearly levels) and this can cause data discrepancies.
Verify your dimension hierarchy
Confirm that the dimensions you are fetching are in the expected location in your dimension hierarchy to avoid tie-out issues as data rolls up from child to parent dimension members.
For example, if you expect Accounts Receivable - Other to roll up into Accounts Receivable in the screenshot below, you'd need to move Accounts Receivable - Other underneath Accounts Receivable in the hierarchy.
See: How to Re-Parent or Re-Order a Dimension.
Check formulas and tags
If any formulas or tags are involved in your data fetch, double-check that the formulas are set up as expected so that the data fetches as expected.
Be sure to check downstream formulas. For example, if Net Income is off, and Net Income references Gross Profit, then you'll also want to confirm that Gross Profit is set up correctly:
Note: If you changed your dimension hierarchy or updated a formula, you can manually recalculate formulas to ensure they are ready to use.
Review dimension mapping
Stale or incorrectly mapped source system data can cause data tie-out issues. Confirm your dimension mappings between your source system and Cube to ensure data is being mapped and sign flipped as expected. Check mapping rules to ensure scheduled and manual imports are placing the data in the correct location.
This is especially relevant if you have scheduled imports that automatically map new dimensions to a clearing account. Ensure these dimensions have been mapped to the appropriate Cube dimension.
If the dimension settings are all as you would expect, move on to the final step.
Check to see if Cube data is stale
If you are fetching actuals data imported from an external source system, check the source system page in the Workspace to verify when the data was last updated in Cube. If the data is stale in Cube, you may have a discrepancy between what you're fetching and what you see in your source system.
If it has been a while since your last import, and you are comparing the data in Cube to your source system, run a fresh import to ensure Cube has the latest data from your source system.
You can also set up a scheduled import from your source system to always have fresh data in Cube.
Still having issues with your data tying out?
If you are still seeing discrepancies after trying the steps above, our support team is here to help! You can log a ticket or email support@cubesoftware.com. When you reach out to our support team, please share the workbook and dimensions where you're seeing the discrepancy and your balance sheet to help us quickly solve the issue for you.