After your Cube is set up and dimensions established, you're ready to Cubify your existing workbooks or create new templates.
What does "Cubify" mean?
Cubifying is the process of ensuring that your workbooks align perfectly with Cube's data structure. In simpler terms, it's about matching the dimension names in your workbook to those used in Cube.
Here's why it's vital:
The Cube spreadsheet add-on acts as a bridge between your workbook and the Cube cloud. When you command the add-on to exchange data — pushing data to Cube or fetching from it — the Spreadsheet add-on scans the selected rows and columns in your workbook, looking specifically for dimension names. These names are then communicated to the Cube cloud for either storage or retrieval of associated values.
If there's a mismatch or unfamiliar dimension names, the Cube cloud struggles to pinpoint the exact location of the data value you're trying to access or save. This is why it's essential to 'Cubify' — it ensures seamless communication between your workbooks and the Cube cloud, making your data operations smooth and error-free.
Cubify your spreadsheets
In this article, you'll learn how to Cubify your existing spreadsheets. To illustrate this, see the video below on Cubifying a Budget vs. Actuals report. If you like our template, download it here.
1. Export your dimensions
You will need the exact names of your dimensions to Cubify your report. From your Cube Web portal, click the download icon to Export Dimensions from each of your top-level dimensions.
2. Create a Drivers tab (optional)
If this is a frequently used template, consider using your Cube dimensions to create Drivers tab. This will help you easily filter the data by month or dimension and communicate the information this workbook contains if you share it with non-Cube users.
3. Update spreadsheet rows and columns
Update the rows and columns in your spreadsheet to reflect the naming of your Cube dimensions, attributes, formulas, and/or tags.
- Cube Dimensions: To accurately fetch the data, rows and column names must match the naming conventions used for mapping your source data to Cube.
Use the downloaded exports from Step 1 or have your Cube Web Portal as a guide. - Attributes: Attributes in a spreadsheet are extra details stored at the row-level dimensions. They provide additional information or a more specific level of detail to a range you've created.
Unlike top-level dimensions, attributes are not used for analyzing or manipulating data. Instead, they provide helpful information associated with each entry when viewing data at a detailed level using Drilldown.
For example, if you want to include details like Employee ID, Annual Salary, and Location for a particular expense item (e.g., Salary Expense). - Formulas: Determine which formulas to leave in your sheet and which to update to Cube Formulas.
As a rule, Cube recommends using Cube Formulas for repeated calculations in your reporting and planning cycles, for example, calculating Gross Profit, Gross Margin, or Net Revenue.
One-off or irregularly used formulas are best left in your spreadsheet. - Tags: Depending on how you set up your Cube, you may have created Tags to track alternative hierarchies, projects, teams, etc.
Tags serve as great filters or may be used for reporting independently. Update your spreadsheet accordingly based on how Tags are used in your Cube.
4. Select and validate ranges
Open the Spreadsheet add-on, and using Select, highlight the range of your spreadsheet.
Verify that you updated your rows and column headers using Validate to check for unrecognized dimensions. The Spreadsheet add-on will list any dimensions that need to be corrected.
5. Review formulas and error detection
Updating the rows and columns in previous steps may have disrupted existing formulas in your workbook. Review them and add any new formulas needed to complete your planning and analysis.
You will also want to review or add formulas for error detection throughout your workbook to ensure the balances are correct.
Use your spreadsheet with Cube
You can now use the Spreadsheet add-on to fetch and publish your data in this Cubified workbook!
Learn more about using the Spreadsheet add-on or about maintaining your data in Cube.