When you fetch data to your spreadsheet and notice a discrepancy, you'll want to review your dimensions 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 add-on 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 you’ll want to check is your spreadsheet. Cube uses the dimension 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 features in the Spreadsheet add-on to verify that your spreadsheet is set up correctly.
Validate dimensions
Use the validate function in your Spreadsheet add-on to ensure that Cube recognizes every dimension within your fetch range. Your Spreadsheet add-on will list any dimensions flagged as unrecognizable so you can update them.
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. This could create two or more entries depending on the number of duplicates and values that are higher than expected when fetching.
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:
- First, log in to your Web portal and select the Data Exporter from the navigation bar.
- Use the filters to select the dimensions of the suspected duplicate value.
- 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, re-select the range, and re-fetch the values to see if this solves the tie out issues.
Take a look at date formats
We all know spreadsheets love to try and force values to be dates. You’ll want to ensure the spreadsheet didn’t convert any time dimensions on your sheet to a date.
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 add-on.
Confirm range filters
For any dimensions used as a filter, instead of being 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 dimensions live underneath that dimension as expected.
If a dimension is missing, it will not be included in the All Departments rollup.
Correct your hierarchy by re-parenting dimensions to the appropriate locations and refreshing your Spreadsheet add-on to see the updates.
After reviewing your spreadsheet, you can fetch your data again. If you are still seeing data discrepancies, you can review your dimensions and source data connections in the Cube web portal.
Review your dimensions
Data discrepancies may also occur when dimensions need review. If you can identify a particular dimension that isn't tying out correctly, you can check that dimension in your Web portal to ensure it was set up and mapped correctly.
Clear clearing accounts
If you use a Clearing Account as a default dimension for scheduled imports, you'll want to make sure it is clear. 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 at time rollups (e.g. at the quarterly or yearly levels).
Verify your dimension hierarchy
Confirm that the dimensions you are fetching in the expected location in your dimension hierarchy.
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 the formulas are set up 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
Confirm your dimension mappings between your source system and Cube to ensure data is being mapped as expected.
This is especially relevant if you have scheduled imports that automap 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 looking at actuals data imported from an external source system, take a look at your source system page in the Web portal to verify when the data was last updated in Cube.
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 still are 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 you're seeing the discrepancy in and your balance sheet to help us quickly solve the issue for you.