How to Create and Manage Cubes in Diligent

Overview

Cubes are the foundation of Diligent. A cube organizes your raw transaction data into a structured model that can be used in Excel or Power BI for reporting and analysis.

After you create a new project, the next major step is to create and manage cubes. Most of your time in Diligent will be spent working with cubes, loading data, configuring fields, and making updates.

Creating a New Cube

Quick Start: To create a new cube, go to Cube Management from the left navigation menu and select ✚ New Project Cube (or choose an existing one to edit), then follow the guided checklist where ✅ means the step is complete, 👍 indicates an optional step, and❗marks something that needs attention; once the cube has processed, it will appear on the Cube Management home page.

To begin, navigate to Cube Management from the left-hand menu and click New Project Cube on the Cube Management home page.

Step 1: Choose Cube Type

When you click Create New Cube, a pop-up window appears with cube type options:

• Profitability – Most common choice for end users. Use this for transactional-level data such as sales by customer, SKU, or order.

• Financial – Second most common. Designed for general ledger or trial balance data, supporting P&L, Balance Sheet, and Cash Flow reporting. Can also handle summarized loads (e.g., budgets).

• Consolidate Profitability (greyed out) – Used for combining multiple Profitability cubes into a single consolidated view. Typically managed by Blue Ops or admins.

• Consolidate Financial (greyed out) – Similar to above, but consolidates Financial cubes across projects or entities.

• Financial Summary (greyed out) – Used for high-level summary cubes, not for detailed transactional or ledger data.

Greyed-out types are not selectable because they require advanced setup, consolidations across multiple cubes, or Blue Ops intervention.

---

Step 2: Confirm Selection

After choosing your cube type and clicking Confirm, you’ll see the next setup screen:

• For Profitability cubes:

  • Enter a cube name (e.g., Customer Transactions 2025).

  • Upload your first data file (.XLS or .CSV).

  • Once uploaded, you’ll unlock further options for mapping, appending, and configuration.

• For Financial cubes:

  • Enter a cube name.

  • Select the source type:

    • Journal Entries – load data at the journal-entry level.

    • Period Balances – load balance data by account and period.

    • Period Activity – load activity detail by period.

  • Choose a Period Layout if applicable (Vertical or Horizontal).

  • Download a template or upload your own file to start building the base data.

Once you confirm and load data, the cube is created and ready for further configuration in Cube Management (mapping, calculations, etc.).

Step 3: Managing Cubes

Source Tab

The Source tab shows which data files are currently loaded into your cube and gives you options to manage them. You can load new data, append additional periods, replace existing files, or delete individual files or partial periods if corrections are required. When managing data, you’ll have the following options:

• Load – load new data if none exists

• Append – add new periods of data (must follow the same structure as prior loads)

• Delete – remove partial data from source file 

• Trash – remove entire source file

• Download sample – downloads a random sample for review

• Download entire table – downloads full table

As a best practice, always load complete months of data to avoid skewing year-to-date calculations. If your file is too large to open in Excel, you can also download a data sample directly from this tab to review field availability.

Mapping Tab

The Mapping tab is used to add structure, consolidations, and cleanup on top of your raw data. This is where you turn detailed, sometimes messy fields into something more consistent and useful for reporting.

Common uses include:

• Grouping detailed fields like SKUs into broader product families

• Cleaning up inconsistent naming (e.g., unifying “Wal-Mart” and “Walmart” into a single customer record)

• Adding new calculated columns to support analysis or hierarchy building

To update a mapping:

1. Download the current mapping table to Excel.

2. Edit existing columns or add new ones as needed.

3. Save your changes and re-upload the file using Replace Table.

4. Select Process Cube Data to apply updates to your cube.

Because mapping is often iterative, expect to revisit this tab multiple times as your data evolves or new requirements emerge.

Configuration Tab

The Configuration tab is where you manage key administrative and setup functions for your cube. This is where you tell Diligent how to interpret fields so that dates, numbers, and revenue are handled correctly in your reporting.

Key tasks include:

• Set field types – Assign each column a type (Date, Numeric, or Text) so it behaves correctly in pivot tables and calculations.

• Select the Primary Date Column – Generates all time series fields (week, month, quarter, year) automatically based on the transaction date you choose.

• Assign a Primary Revenue Field – Ensures counts only reflect records with valid revenue (e.g., customers with >$0 sales) instead of counting all line items.

• Confirm numeric fields – For values like Gross Sales, set the type to Numeric so they can be summed or used in calculations.

• Enable counts – Counts can be turned on through the standard measures column of each table.

• Add calculated or concatenated fields (optional) – Useful when a single field is not unique enough, or when you need to combine fields (e.g., Customer + Location) to support mapping or reporting.

Configuring fields correctly ensures your cube produces accurate, meaningful results. It’s best practice to complete this step immediately after loading data to avoid issues in downstream mapping and reporting.

Calculations Tab

The Calculations tab is where you can create simple measures to extend your analysis. These measures are built using DAX-style logic (Data Analysis Expressions), the same language used in Power BI and other tabular models. This makes them flexible but also means you should keep them straightforward unless you’re experienced with DAX.

Common simple measures include:

• Gross Profit = Revenue − COGS

• Gross Margin % = Gross Profit ÷ Revenue

• Average Order Value = Revenue ÷ Orders

• Average Selling Price = Revenue ÷ Units

• Average Ticket or other per-transaction averages

Best practices:

• Keep it simple. Focus on arithmetic or ratio-based calculations.

• Check field types. Ensure underlying fields are properly set in Configuration (e.g., Revenue and COGS marked as Numeric).

• Use clear names. Label measures so business users know exactly what they represent (e.g., “Gross Margin %” rather than “GM”).

• Validate results. Confirm outputs tie back to expected financials.

When to escalate to Blue Ops:

• Measures requiring conditional logic (e.g., IF statements, nested conditions)

• Time-aware metrics that go beyond standard periods (e.g., rolling 12-months with exceptions)

• Complex allocations, multi-table dependencies, or anything requiring advanced DAX

How to add a calculation:

1. Go to the Calculations tab.

2. Select your base fields and define the arithmetic expression.

3. Save the measure with a clear, descriptive name.

4. Click Process Cube Data to apply the new measure and make it available in Excel or Power BI.

Step 4: Processing Changes

Once you’ve finished setting up or editing your cube, you need to process it so changes are applied and available in Excel or Power BI. This step validates your work, updates calculations, and enables downloads.

How to process:

1. Click Process Cube Data at the top right of the Cube Management screen.

2. Watch for status indicators:

   • ✅ Green check – step is complete

   • ❗ Red exclamation – error that must be fixed before continuing

3. When processing is successful, a Download Cube button will appear.

Best practices:

• Always process after edits. Any change to configuration, mapping, or calculations requires reprocessing.

• Bundle updates. Make multiple edits, then process once to save time.

• Validate results. After processing, download a refreshed cube or simply refresh your existing Excel files to see updates.

• Check errors. If processing fails, return to the Cube Management home page for error indicators and troubleshoot from there.

Tips and Notes

• There is no strict order for working through tabs — you can revisit them anytime.

• “Actuals” scenarios are automatically created based on your project calendar and data load.

• YTD and TTM scenarios can be created separately in Project Management; once refreshed, they appear in all cubes.

• Use complete months of data for accuracy, and document mapping changes for auditability.

Related and Next up

• Before this: Creating a New Project
• Next article: Cube Management: Loading and Managing Data
• Also helpful: Access, Login, and Excel Add-in Installation

Cubes are where your project’s raw data is transformed into something structured, flexible, and ready for analysis. By following the creation and management steps — from loading files to mapping, configuring, and calculating — you ensure reporting is both accurate and repeatable. Once a cube is processed, you can use it immediately in Excel or Power BI to drive insights. Mastering cube management is the key to making Diligent work for you.