Cube Management: Configure Fields and Data Types

Overview

The Configuration tab manages key administrative and setup functions for your cube. This is where you define data types, identify your primary date and revenue columns, choose visibility settings, and (optionally) create custom fields.

Most commonly, you’ll configure your source data here, but mapping files can also be configured when needed.

💡 You can move between all cube management tabs at any time — they don’t need to be completed in order. For example, you may configure fields, return later to update mappings, then come back to adjust configuration again.

Steps to Configure

1. Set Field Data Types

Before setting key fields, review and assign a data type for every column. While Diligent often auto-detects these, you should double-check and adjust as needed.

• String (Text): Use for IDs, names, or categories (e.g., Customer ID, Customer Name, SKU, Product Group).
• Numeric: Use for any field containing amounts or counts (e.g., Gross Sales, COGS, Quantity, Units Shipped). These can be summed or averaged in pivots.
• Date: Use for time-based fields (e.g., Transaction Date, Invoice Date, Order Date).

Additional field settings:

• Standard Measures: Optionally enable distinct counts (not commonly needed, but available).
• Aggregation Type: For numeric fields, specify Sum or Average depending on how the field should roll up.
• Format: Choose how values should display in reporting, such as:
  • Currency ($ actuals)
  • Percentages (%)
  • Quantities (raw values)
  • Thousands (auto-formatted for very large numbers in Excel/Power BI).

Once all fields are set, click Apply to save changes.

2. Select the Primary Date Column

Choose the date field that should drive reporting (most often Calendar or Transaction Date).
This selection automatically generates all time-series hierarchies (week, month, quarter, year) in the cube.

3. Assign the Primary Revenue Field

Identify the revenue column that determines valid transactions (e.g., Net Sales or Gross Sales).
This ensures customer counts and other distinct measures only reflect records with >$0 revenue, rather than counting every transaction line.

4. Set Field Visibility

For each column, you can toggle whether it is visible in the cube.
Hide fields that are duplicative, messy, or irrelevant so they don’t clutter pivot tables and reports.

5. Create Custom Columns (Optional)

The Configuration tab also allows you to create basic custom columns:

• Often used for concatenated fields (e.g., Customer + Location).
• Useful when a single field is not unique enough to support mapping or reporting.

📌 Note: Most users will only have access to Basic custom columns, but more advanced calculated custom columns are available if needed. If you need an Advanced Custom Column, reach out to Support or your Project Admin.

Best Practices

• Always confirm numeric fields are set to Numeric and text fields to String — mislabeling is a common source of reporting errors.
• Configure fields immediately after data load to prevent downstream issues in mapping or reporting.
• Choose the revenue field that best represents recognized revenue in your reporting.
• Hide fields you don’t need to reduce clutter and improve report performance.
• Bundle multiple edits and apply them together to avoid unnecessary processing time.
• After all changes are applied, click Process Cube Data. This step is critical — it pushes your configuration into the cube so the changes appear in Excel or Power BI.

Related and Next Up

• Before this: Cube Management: Mapping and Relationships
• Next article: Cube Management: Calculations and Adding Measures
• Also helpful: Creating and Managing Cubes

The Configuration tab is where you fine-tune how your cube handles data. Setting types, defining revenue and dates, and hiding unnecessary fields ensures cleaner pivots, faster analysis, and more accurate results. Once complete, remember to process the cube so your updates are carried through into Excel or Power BI.