Modifying and Formatting Data Cubes in Excel

Overview

Data cubes allow you to explore, filter, and analyze your organization’s financial and operational data directly in Excel. Each cube connects live to your Diligent project, so any updates to data or mappings are automatically reflected the next time you refresh.

Accessing Existing Cubes

When a project begins, you’ll typically receive a pre-built Excel databook from your Blue Ops team. This file contains formatted cubes tailored to your project’s reporting needs.

1. Open the file and select Enable Editing and Enable Content if prompted.
2. You’ll be prompted to sign in with your MSFT 365 SSO credentials.
   • If you aren’t prompted, or login fails, see our Excel Login Troubleshooting Guide.
3. Once logged in, all cube data is live and ready to refresh as needed.

Tip: If you don’t yet have a Diligent user account, reach out to your Blue Ops representative to request access.

Modify an Existing Cube

If you want to analyze data differently, it’s often easiest to copy an existing tab:

1. Right-click the sheet tab → Move or Copy → Create a copy.
2. Open the PivotTable Field List and adjust fields to your needs.
3. Drag in new dimensions or measures to drill deeper, or remove fields to simplify the view.

Example: You might copy a “Revenue by Channel” tab to create a “Top Customers” report. Just add Customer under Channel in the field list and filter by your desired period.

Creating a New (Blank) Cube

You can start from scratch in two ways:

Option A: Insert from the Excel Add-In

1. In Excel, go to the EBM OfficeBridge ribbon → Insert Cube(s).
2. Choose from available cube types, such as:
   • Profitability – customer or product-level transaction data.
   • Financial – trial balance and summarized financial data.
3. Once selected, a blank cube sheet will appear, ready for customization.

Option B: Download from the Diligent Portal

1. Log in to the Diligent website.
2. Select the Project you want to access from the Project Selector.
3. Select Cube Management to find all the Cubes from that project.
4. Click Download Cube icon to pull it down to your machine.
5. The system will export a live, connected Excel file you can open and begin modifying.

Getting Started: Essential Fields

Every cube requires three key components to produce meaningful results:

1. Amount – Drag Amount into Values to summarize numeric data.
2. Account – Choose from the Profitability Account list to define the type of metric (e.g., Revenue, COGS, Gross Profit).
3. Time Period (Scenario) – Add Scenario or Scenario Short to your filters, columns, or rows to define which period(s) you’re analyzing.
   • Scenario Short (e.g., “2025A”) helps conserve column space.

Once these three fields are defined, remove unnecessary subtotals and grand totals. You can adjust layout and formatting in PivotTable Options or the Design ribbon to match your workbook style.

After these steps, your table should resemble a clean summary by account and time period, ready for deeper slicing.

Sample Data Cuts: Step-by-Step Instructions

The following examples show how to transform a basic cube into useful analyses. Each example assumes you’ve already dropped Amount, Account, and Scenario Short as described above.

1. Time Series Revenue Trends by Channel

To analyze revenue over time by channel or end market:

1. Add Channel to your Rows area.

2. Ensure Scenario Short is in Columns.

3. Filter Profitability Account to “Sales.”

4. Sort the most recent period from Largest to Smallest to see top-performing channels.

Default View: Channels will appear in alphabetical order.
Sorted View: Right-click any revenue cell in the most recent period → Sort → Largest to Smallest for a performance-based ranking.

Default Sorting: Alphabetical

Resorted: Revenue Largest to Smallest in TTM period

2. Time Series Revenue & Margin Trends by Channel

To view both revenue and gross profit (or margin %):

1. Modify your Account filter to include both Sales and Gross Profit.

2. Create a manual Gross Margin % column beside the cube using a simple formula:

   =GrossProfit / Revenue

   • This keeps the cube dynamic—when refreshed, your formula updates automatically.

3. Format the calculated field as a percentage for readability.

Note: For presentation or formatting reasons, manual calculations are often easier to maintain than complex pivot-table value fields.

3. Calculating Year-over-Year (YoY), Quarter-over-Quarter (QoQ), or Month-over-Month (MoM) % Change

To calculate a percentage change directly in the pivot:

1. Add a second Amount to the Values area.

2. Right-click on that second Amount field → Value Field Settings.

3. In the Show Values As tab, choose the desired calculation, such as % Difference From.

4. Set Base Field = Scenario (Short) and Base Item = Previous.

5. Rename this new field (e.g., “YoY % Change”).

Shortcut: You can also right-click any value → Show Values As → % Difference From → (Base Field: Scenario Short, Base Item: Previous).

4. Calculating Revenue Concentration

To measure each channel’s contribution to total revenue:

1. Right-click the Amount field → Show Values As → % of Parent Row Total.

2. The resulting table will display each channel’s share of total revenue.

3. You can also drill down further by adding Customer or Product beneath Channel to view their individual contributions.

5. Drilling Down into Revenue Trends to Understand Drivers

Use this when you notice performance changes and want to find the underlying cause.

Example: Suppose revenue drops from 2019 to the TTM period across all channels.

1. Filter the Channel field to “Retail.”

2. Add Customer (or Parent Customer) beneath Channel in your Rows.

3. Sort the YoY % Change field from Lowest to Highest.

   • Dormant or lost customers will appear first, helping you identify key drivers of the decline.

4. To isolate a specific customer:

   • Right-click the customer name → Filter → Keep Only Selected.

   • Add Product under Rows to analyze which products they stopped purchasing.

In this example, you might see that Parent Customer 161 reduced purchases of products 103 and 370—revealing the specific loss behind the revenue drop.

6. Identifying Top 20 Products or Customers

To spotlight top-performing customers or products:

1. Right-click a field label (e.g., Customer or Product).

2. Choose Filter → Top 10….

3. Adjust the dropdown to Top 20 Items by Amount (or another count as needed).

Alternative: Blue Ops can add a Top Customer hierarchy column directly in your cube to categorize “Top Customers” vs “All Other.” This helps automate future refreshes and ensures consistency across analyses.

Next Up

If you encounter login issues, missing data, or refresh errors, visit our Troubleshooting Cube Connections guide or contact Diligent Support through the Submit a Request form.

Next: Learn how to configure cube fields, data types, and counts in Cube Management: Configure Fields and Data Types.

Once you’ve defined the Amount, Account, and Scenario fields, you can create virtually any analytical view from a single cube connection. Pivot tables make it easy to track trends, analyze margins, identify key customers or products, and explore performance by channel or geography.

Remember:

• Sorting and filtering determine the story your data tells.

• Manual calculations (like gross margin) can complement cube data.

• Each cube refresh will automatically update your Excel views.