Cube allows you to upload as much data from any source as you want via manual flat file.
This data can be used to replace or clear existing data in a particular time frame or for a scenario by using dimensions in your flat file that already contain data in Cube. Or you can use this method to bring new data into Cube.
Understanding the nuances of data preparation is essential to ensure seamless integration into your Cube. In this guide, we'll walk you through the step-by-step process of transforming your data into a state ready for upload. Feel free to create a copy of our template.
Understanding the initial state
Begin by examining your extracted file. Cube requires specific formatting to ingest your file into Cube properly. Here's what you need to consider:
- Essential columns: Cube requires a column representing every dimension category in your model, including the Scenario dimension, which might be missing in your file.
- Filled dimension fields: Ensure that every column representing a dimension is populated with data. Blank fields for elements like cost centers, regions, or vendors need to be filled.
- Account matching: Accounts should align with the named accounts in your Cube model. Mapping processes can help in achieving this alignment. This goes for all names in your flat file.
- Single value for balances: Cube needs a single ending balance for each account. Adjustments, sign flips, and aggregations might be necessary for debit and credit columns.
Transforming the file
The ideal state of your file should encompass the following:
- Dimension columns: Include columns for all dimensions, ensuring that the names of your source data match your Cube dimensions and dimension members exactly and filling previously empty fields (e.g., cost centers) with designated values.
- Scenario update: Add a Scenario column to account for actuals or another scenario.
- Format adjustments: Modify time and date formats for compatibility with Cube ingestion.
- Aggregated values: Prepare a single total value column after necessary aggregations and sign flips.
- Callout and include attributes: Cube allows up to 25 attributes to retain numbers or texts that add context to your values. In the example below, Memo and Transaction ID are called out as attributes to be added to Cube. Learn more about attributes.
Techniques for data manipulation
Every dataset is unique, and manipulations may vary. Here are some tips and tricks to streamline your data preparation:
-
X Lookup formula for mapping: Utilize an X Lookup formula to match source data values to Cube dimensions (e.g., account numbers from the raw extract to the named Cube dimensions).
=XLOOKUP('Example Extract from Source System'!C3,'Example Account Mappings'!A:A,'Example Account Mappings'!B:B,"",0,1) -
IF Statements for blank fields: Populate empty fields using simple IF statements (e.g., filling blank cost centers with "cost center - other").
=IF('Example Extract from Source System'!K3="","Vendor - Other",'Example Extract from Source System'!K3) - Template formulas: Employ spreadsheet formulas within a template for automated manipulation based on the latest extract.
Simplified data massaging techniques
The use of formulas and templates can significantly ease the data manipulation process:
- Automated manipulation: Develop a template where the latest extract can be dropped in, allowing formulas to process the data automatically. We recommend creating a drivers tab to help make this easier.
- CSV conversion: When the file is formatted correctly, save it as a CSV for seamless uploading into Cube.
Conclusion
Preparing data for Cube upload involves a few steps, from aligning dimensions to formatting values. Utilizing formulas, templates, and techniques highlighted here can simplify this process significantly. By following these guidelines, you can ensure your data is in the best possible state for successful integration into Cube.
When you're done massaging your flat file for Cube, you're ready to upload it. Just be sure you've created a data table first!