Cube Configuration: Comprehensive Guide

This article contains the following topics:

Feature Overview

This feature addresses common challenges faced when working with complex data cubes:

• Managing overwhelming amounts of data and unnecessary fields

• Creating customized views for different user roles

• Implementing advanced calculations within data cubes

• Reducing time spent on data analysis and report generation

• Maintaining data relevance across different user needs

Cube Configuration consists of three key components

1. Perspectives: Create customized views of your data cubes that simplify the data browsing experience. This allows users to see only relevant fields, dimensions, and measures - reducing complexity and improving usability.
2. Calculated Measures: Implement advanced DAX (Data Analysis Expressions) calculations directly in the platform, enabling tailored insights without extensive technical support.
3. Scenario Type Filtering: Filter out unnecessary scenario types to enhance data clarity and streamline reporting.

These components work together to provide a more streamlined, efficient approach to data cube management that improves both usability and data analysis outcomes.

Important Security Consideration

Cube Configuration is not primarily a security feature, though it may be used in ways that appear similar. If you're building perspectives to simplify views and hide certain data types for specific users, you should also consider security implications.

For example, if you choose to hide fields like account hierarchy or certain scenarios, the perspective will do so, but if security isn't enabled in addition to this, a user could potentially open a standard cube and access those data sets. Always implement proper security controls alongside Cube Configuration.

Who Can Use Cube Configuration?

Cube Configuration is available to:

• EBM Client Admins

• Users with access to the Administration page

Note that using the Calculated Measures feature requires knowledge of DAX (Data Analysis Expressions).

Why Cube Configuration Exists

We developed Cube Configuration in response to user feedback and growing demand for a more user-friendly tool to manage data cubes. Users previously faced challenges when creating and managing perspectives, which are essential for customizing data views and performing specific analyses.

Cube Configuration addresses these challenges by:

• Simplifying complex data cubes with numerous fields

• Enabling creation of customized views and calculations

• Streamlining workflows for data analysis and reporting

• Maintaining data relevance across different user roles

Getting Started with Cube Configuration

Enabling the Feature

1. Double-check that the feature isn't already enabled under Administration > System Configuration in Catalyst. If you don’t see the feature, you'll need us to enable it for you. Please reach out to our support team here.
   
   
   
   

2. Navigate to Cube Configuration
   

   • Go to Administration section in Catalyst

   • Look for the new Cube Configuration section under System Configuration

3. Select your cube
   
   

   • Use the dropdown to select which cube you want to configure

   • The dropdown includes all cube types enabled on your instance:

     • Standard cubes (e.g., Financial Tabular or Profitability Tabular)

     • Smartload Cubes (often named like Invoice_Detail_Consolidated)

4. Navigate between the three configuration tabs

   • Perspectives

   • Calculated Measures

   • Scenario Type

We recommend starting with building a Perspective first, then adding further customization using the other tabs. All three tabs relate to the same 'view,' so when you create a perspective, the calculated measures and scenario type configurations will correspond to that same perspective.

Working with Perspectives

Perspectives are customized, focused views of a data cube that simplify data exploration and reporting. They allow different users or roles to see only the data relevant to them while hiding unnecessary details.

Creating a New Perspective

1. Select the cube you want to configure

2. Click "Add Perspective" and give it a descriptive name

   Naming Tip: Choose clear, concise names for your perspectives (under 30 characters is recommended). This name will appear both as a tile in the cube list under Analysis and as a tab name when viewing in Excel. Descriptive yet brief names improve usability and organization.

3. The screen will refresh and display all available fields you can include or exclude

Managing Existing Perspectives

To edit, rename, or delete an existing perspective:

1. Navigate to the Perspectives tab

2. Select the cube your perspective was built from using the "Cube to Configure" dropdown

3. In the table below, you'll see each perspective in its own column

4. To make changes:

   • Edit fields directly by selecting/deselecting them

   • Click the three-dot ellipsis menu to delete or rename the perspective
     
     

5. After making changes, remember to click "Process Perspective" to apply them

Configuring Field Visibility

After creating a perspective, you'll see a list of all available fields for your selected cube:

Think of fields as folders - that's how Excel organizes them in the pivot table field list. Each field (folder) contains sub-items. You can:

• Enable or disable entire folders of fields

• Select specific items within each field folder

• Customize exactly what appears in the Excel pivot table

Common Use Cases for Field Customization

Example 1: Streamlining Time Fields

• Deselect unnecessary time elements

• Keep only essential time fields like year, quarter, period, and date
  
  
  
  

Example 2: Reducing Measure Noise

• Focus on the Account Measures, Base Measures, and Calculated Measures sections

• Many standard measures in tabular cubes aren't regularly used in Excel reporting

• Select only those measures that are relevant to your specific reporting needs

Processing Your Perspective

Once you've selected the fields you want to include:

1. Click the "Process Perspective" button to push your changes to the cube

2. Your perspective is now ready to use

Accessing and Sharing Your Perspective

When you're done creating a perspective, it will appear under the Analysis > Data Cubes section where you can download it and open it in Excel. If you built this perspective for specific user groups or teams, you'll need to grant them access to this cube.

There are two ways to provide access:

Option 1: Individual User Access

• Navigate to Admin > User Permissions > User

• Select the specific user

• Go to Navigation > Analysis > Data Cubes > [Cube Name]

• Grant appropriate permissions

Option 2: Group Permission (Recommended)

• Navigate to Admin > Group Permissions

• Add a new group or edit an existing group

• Go to Navigation > Analysis > Data Cubes > [Cube Name]

• Grant appropriate permissions

Note: For this permission setting, both Read and Write permissions will work the same way. Once configured, users will be able to see this cube in Catalyst, and the file will work if shared with them.

Working with Calculated Measures

Note: When adding calculated measures, keep in mind that these changes will affect all cubes associated with the selected dataset (e.g., Profitability, Financial, etc.), not just the perspective you're currently editing. Be cautious when adding or removing measures, as it will apply to every cube using that dataset. Additionally, you must rebuild the cube in order to see your new measures reflected in Excel.

The Calculated Measures tab allows you to create custom calculations that will appear in the Calculated Measures folder in Excel's Pivot Table field list.

Creating a Calculated Measure

1. Name your measure: Give it a descriptive name that clearly indicates its purpose

2. Select a folder: Choose which folder it should appear in within the Calculated Measures section of the Excel pivot table field list
   
   
   
   

3. Select the output format: Choose how you want your measure to display (currency, percentage, etc.)
   
   

   • None: No specific format is applied, and the value is displayed as-is without any special formatting.

   • %: The value is displayed as a percentage (e.g., 0.25 will be shown as 25%).

   • Qty Act: Displays the value as an actual quantity, likely in units or counts, without any specific scaling or formatting.

   • Qty (000'S): Displays the quantity in thousands (e.g., 500,000 will be shown as 500).

   • $ Act: Displays the value as an actual dollar amount, typically without any scaling (e.g., $500).

   • $ (000'S): Displays the dollar amount in thousands (e.g., $500,000 will be shown as $500).

    

4. Click "Add New" to open the measure editor

5. Write or paste your DAX formula Data Analysis Expressions (DAX) is a formula language used for business intelligence and data analytics, allowing organizations to gain insights through interactive reports, dashboards, and visualizations.
   
   

DAX Formula Examples

Here are a few commonly used DAX formulas that might be helpful. Keep in mind that some of these may already be available in your existing measure fields, so it's a good idea to verify before creating duplicates.

Year-to-Date Total:

YTD = 
CALCULATE(
    SUM([Amount]),
    DATESYTD('Date'[Date])
)

Year-over-Year Growth:

YoY Growth = 
DIVIDE(
    SUM([Amount]),
    CALCULATE(
        SUM([Amount]),
        SAMEPERIODLASTYEAR('Date'[Date])
    ),
    0
) - 1

Running Total:

Running Total = 
CALCULATE(
    SUM([Amount]),
    FILTER(
        ALL('Date'),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

Some great public DAX formula repositories for finance and accounting professionals include SQLBI's DAX Patterns, which offers various financial analysis templates, and the DAX Guide for quick reference on functions. The Power BI Community Forum is useful for real-world DAX examples, while Microsoft's Power BI Documentation provides a comprehensive overview of DAX. DAX Formatter by SQLBI is essential for formatting DAX code, and RADACAD's blog offers tutorials tailored to financial modeling. Additionally, GitHub hosts a variety of DAX examples specific to finance and accounting. For more DAX formulas you can reach out to your EBM support team for a library of common expressions. For more DAX formulas and assistance, you can reach out to our EBM support team for a library of common expressions.

Processing Your Calculated Measures

After creating your calculated measures:

1. Save the measure

2. Click "Process" to save the change and then rebuild the cube to make it available

3. Your calculated measure will now appear in Excel under the folder you specified

Working with Scenario Types

Note: Changes made in the Scenario Type Filtering section will impact all cubes associated with the chosen dataset (e.g., Profitability, Financial, etc.), not just the perspective you're editing. This means that your updates will apply universally to all cubes tied to that dataset. Please be mindful of these changes, as they will affect data visibility across all related cubes.

The Scenario Type tab allows you to control which scenario types are available in your cubes.

Filtering Scenario Types

1. View the list of available scenario types for your cube

2. Select which scenario types you want to exclude

3. If you don't select any, all scenario types will be available

Processing Your Scenario Type Filters

After filtering your scenario types:

1. Click "Process" to save the change and then rebuild the cube to make your changes available in Excel

2. Your excluded scenario types will no longer be available in Excel in your filters

Common Use Cases

Financial Reporting Focus:

• Include only Actuals and Budget scenario types

• Hide Forecast or Planning scenarios

Budgeting Process:

• Include Budget and Forecast scenarios

• Hide historical Actuals

Cube Configuration Real-World Use Cases

General Simplification

Role-Specific Perspectives:

• Create perspectives tailored to different roles within your organization, such as the Sales team, Marketing, HR, or the Finance team.

• For example, a perspective for Accounts Payable might include only relevant fields like Vendor, Invoice Amount, and Due Date, while excluding unnecessary fields like Sales Data.

Sales Teams

Pipeline Analysis:

• Create a perspective focused on opportunity tracking and conversion metrics

• Include calculated measures for win rates and average deal size

• Filter to show current quarter and previous quarter comparisons

• Result: Sales managers can quickly identify pipeline health and forecast accuracy without digging through irrelevant data

Sales Performance Dashboard:

• Create a perspective with rep-level performance indicators

• Include calculated measures for quota attainment and year-over-year growth

• Filter to show actual sales against targets

• Result: Clear visibility into individual and team performance metrics for coaching and recognition

Finance Teams

Quarterly Financial Review:

• Create a perspective that focuses only on key financial metrics

• Include calculated measures for variance analysis

• Filter to show only Actuals and Budget scenarios

• Result: Simplified financial reporting that highlights the most critical performance indicators

Budget Planning:

• Create a perspective with forward-looking metrics

• Include calculated measures for growth projections

• Filter to show Budget and Forecast scenarios

• Result: A focused environment for budget planning without distraction from historical data

Accounting Teams

Month-End Close:

• Create a perspective with reconciliation-focused fields

• Include calculated measures for balance validation

• Filter to show only Actuals scenarios

• Result: Simplified view that helps accountants focus on month-end close activities

FP&A Teams

Business Performance Analysis:

• Create a perspective with KPIs across multiple dimensions

• Include calculated measures for trend analysis

• Filter to show Actuals, Budget, and Forecast scenarios

• Result: Comprehensive view of business performance that enables deeper analysis

Executive Leadership

Strategic Decision Support:

• Create a perspective with high-level organizational metrics

• Include calculated measures for business unit comparison

• Filter to show year-to-date performance against strategic goals

• Result: Executive dashboards that provide quick insights without overwhelming detail

Marketing Teams

Campaign ROI Analysis:

• Create a perspective focusing on marketing spend and resulting revenue

• Include calculated measures for customer acquisition cost and lifetime value

• Filter to show performance by channel and campaign

• Result: Clear visibility into marketing effectiveness to optimize future investments

Excel Power Users

Advanced Pivot Table Analysis:

• Create a perspective with detailed dimensional analysis

• Eliminate erroneous or non-essential fields

• Include custom calculated measures specific to business needs

• Configure scenario types based on analysis requirements

• Result: Optimized pivot table experience with only relevant fields and measures

Tips and Best Practices

1. Start Simple: Begin with a basic perspective that includes only the most essential fields, then add more as needed.

2. Name Strategically: Use clear, descriptive names for your perspectives and calculated measures that indicate their purpose and intended audience.

3. Document Your Work: Maintain documentation about each perspective's purpose, included fields, and target users to simplify future maintenance.

4. Test Thoroughly: After creating a perspective, download the cube and test it in Excel with realistic data scenarios to ensure it meets requirements.

5. Consider User Workflow: Design perspectives that align with specific user workflows and reporting needs rather than trying to create one-size-fits-all solutions.

6. Review Regularly: Schedule periodic reviews of your perspectives to ensure they remain aligned with evolving business needs and reporting requirements.

7. Limit Calculated Measures: Only create calculated measures that provide clear value and cannot be easily replicated in Excel to avoid maintenance overhead.

8. Balance Performance and Functionality: Complex perspectives with many calculated measures may impact performance, so test with production-level data volumes.

9. Collaborate with End Users: Involve stakeholders in the design process to ensure perspectives meet their actual needs and improve adoption.

10. Implement Proper Access Controls: Use the permission settings described in the "Accessing and Sharing Your Perspective" section to ensure the right users have access to the right perspectives.

Troubleshooting & FAQs

Setup & Configuration

Perspective not appearing in Data Cubes list

• Ensure you clicked "Process Perspective" after making changes

• Refresh your browser and check again

• Verify you have proper permissions to view the perspective

Enabling Cube Configuration

• Access the Cube Configuration through Administration > System Configuration

• Contact your system administrator if you don't see this option

Creating team-specific perspectives

• Configure permissions settings as described in "Accessing and Sharing" section

• Set up appropriate security groups for controlled access

• Test access with team representatives before full deployment

Access & Permissions

User access to Cube Configuration

• Cube Configuration is located in the Administration page under System Configuration

• To grant access, create a user group (e.g., "Access to Cube Configuration") and add users to it

  • Detailed instructions: Manage Users with Groups in Catalyst

• Alternatively, grant individual permissions by editing user profiles

  • Detailed instructions: Managing Users and Permissions in Catalyst

Users unable to view perspectives

• Confirm appropriate permissions have been granted

• Verify users are looking in the correct location (Analysis > Data Cubes)

• Check if users belong to the correct permission group

Managing multiple teams with different requirements

• Create separate perspectives for teams with significantly different needs

• Use security filters for teams requiring different data visibility

• Document perspective assignments for each team

Data & Calculations

Calculated measures displaying incorrectly

• Check DAX syntax for errors or invalid references

• Verify appropriate format selection for expected output values

• Ensure the measure was processed after creation

• Test with simple data to isolate potential issues

Adding calculated measures

• Basic calculations require minimal DAX knowledge

• Complex calculations may require DAX documentation review

• Begin with templates or examples when possible

Visibility & Display

Scenario types appearing despite exclusion

• Process changes after configuration

• Verify correct perspective usage in analysis

• Check if security settings affect visibility

• Refresh Excel connections to use latest cube definition

Changes not reflected in Excel

• Process perspective after making changes

• Refresh data connections in Excel (Data > Refresh All)

• Close and reopen Excel workbooks

• Clear client cache for persistent issues

Performance & Maintenance

Perspective performance issues

• Review complex calculated measures

• Reduce number of included fields

• Check for circular references in DAX formulas

• Test with smaller data sets to identify bottlenecks

Reverting to previous configurations

• Use version history to view previous configurations

• Roll back when needed

• Maintain documentation of major changes

Conclusion

Cube Configuration transforms how you manage and interact with data cubes in the Catalyst platform. By creating tailored perspectives, implementing calculated measures, and filtering scenario types, you can deliver more focused, relevant data experiences to different user groups while reducing complexity.

The real value of Cube Configuration lies in its ability to bridge the gap between complex data structures and actionable business insights. By carefully designing perspectives that align with specific business needs and user workflows, you can significantly improve data adoption and decision-making processes across your organization.

Remember that effective cube configuration is an iterative process. Start with basic perspectives, gather user feedback, and refine your approach as you learn what works best for your specific use cases and user groups. For additional assistance or to provide feedback on this feature, please contact EBM Support or your EBM account representative.