Fetching is a crucial process that updates your spreadsheet with the latest data from Cube, ensuring your analyses are based on the most current information.
This guide is a deep dive into essential best practices to enhance your fetching experience and maintain data integrity.
We'll cover:
- Tips to ensure your Cube data is up to date and ready to use
- Considerations and best practices for fetching in your spreadsheets
Just want the basics about fetching? Check out this quick start guide.
Prepare your data
Before fetching data to your spreadsheet, review your Cube data and dimensions to be sure the data fetched to your spreadsheet is current and complete.
Ensure source data is up to date
Before fetching data, ensure that your Cube data is up to date. Check the last import date for source data in your Workspace and run source system or flat file imports to refresh as necessary. Remember, fetching is only as effective as the data it's pulling from.
Want to skip manual imports and keep your connected source system data current? Set up scheduled imports.
If you already use scheduled imports, ensure auto-mapped dimensions, such as those mapped to a clearing account, are updated to map to the correct Cube dimension. Learn more about updating mapping.
Actualize and update scenarios
Review the scenarios you will use in your workbook from the dimensions page of your Workspace. Merge or create new scenarios to integrate actuals with budgets or forecasts to keep your analysis grounded in the most current and relevant data. This practice is particularly valuable when you need to adapt your plans based on actual business performance, allowing for more informed decision-making and strategic adjustments.
By actualizing your scenarios before fetching, you ensure that the data you work with in your spreadsheet reflects the most up-to-date and accurate picture of your financial situation.
Learn more about scenario management best practices.
Review tags
In Cube, tags allow you to organize and analyze your financial data more flexibly. Before initiating a fetch in your spreadsheet, it's advisable to review and, if necessary, add new tags through the Cube Workspace.
Tags allow you to segment and categorize your data more effectively, catering to specific reporting needs or analytical perspectives. For instance, tagging expenses with categories like 'Fixed' or 'Variable' can offer insightful breakdowns for cost analysis. Regularly updating your tags ensures that your fetched data is current and aligned with your evolving business analysis strategies, leading to more insightful and actionable financial reporting.
Best practices in your spreadsheets
Now, your data is all set to use in your workbook. Whether you are fetching data into a new ad hoc report or an existing template, managing ranges and their settings will provide a more effective experience.
Effective range management
Properly managing ranges is key to successful fetching. Below are a few tips.
Streamline range navigation
Choose clear, descriptive names for your ranges so you can easily identify them later. This is especially useful for collaboration in workbooks with multiple ranges that are shared across teams.
If you have ranges that should be used for a particular purpose, such as fetching or publishing, include that in the range name. You can also restrict fetching or publishing in range settings.
Learn more about how to fetch to multiple ranges simultaneously and save some time when refreshing data in a large workbook.
Modify ranges
If you return to a routinely used template, you may need to add rows or columns to account for new line items or dimension members. You can insert new rows or columns in the middle of a range and add the new dimension names. Ensure the new dimension members follow the rows and columns outlined in your range definition; this may involve expanding hidden columns or rows.
When you've finished adding these new row or column dimensions, fetch to the range. The range will update to account for the new additions and fetch the data to your spreadsheet.
Tip: If the new dimension members in your new rows or columns fall into your filters, you won't be able to fetch with the existing range. Instead, reselect the range.
Fetch tag values
Fetch values rolled up into the tags you've created in your Workspace by locating them under their parent dimension member when building your range.
Learn more about using tags in your planning.
Exclude zero rows
Use the option to exclude zero rows during fetching to keep your data concise and focused only on relevant entries.
This setting automatically excludes any dimension members without values when fetching data.
Preserve spreadsheet formulas and formatting
Especially if you are working in a template, check out these tips for fetching data from Cube without altering the formatting and formulas that already exist in your spreadsheet.
Formulas
Often-used formulas are likely already in Cube, so these calculated values will be fetched with the rest of your data. Learn more about moving formulas to Cube.
However, your spreadsheet may have additional formulas to support data validation, manipulate dates as part of your driver setup, or formulas specific to your analysis. These formulas should remain in your spreadsheet, and you want to ensure these cells are not located in a range designated for fetching Cube data.
Creating a publish-only range for formula cells, like in our allocation template, or avoiding formula cells altogether when building ranges is useful to ensure they are not overwritten.
Learn more about creating multiple ranges.
Formatting
The cell formatting, including color and conditional formatting, will be retained when fetching. Font color is not retained by default. Instead, the fetched values are either black or blue to indicate which values can be changed.
- Black - indicates a value that cannot be changed through publishing because it is a calculated value, write-protected, or a parent dimension member.
- Blue - indicates a value that can be changed through publishing.
At any time, you can adjust your Spreadsheet App settings to retain font color instead.
Following these best practices ensures a smoother and more efficient data-fetching process in Cube. Regularly update your data, manage your ranges effectively, and make the most of Cube's features to maintain accurate and insightful financial analyses. Remember, the goal is to make data fetching a seamless part of your financial reporting routine.