Cube Configurator: Comprehensive Guide

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 Configurator 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 Configurator 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 Configurator.

Who Can Use Cube Configurator?

Cube Configurator is available to:

• EBM Client Admins

• Users with access to edit the Analysis page

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

Why Cube Configurator Exists

We developed Cube Configurator 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 Configurator 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 Configurator

Enabling the Feature

1. Double-check that the feature isn't already enabled under Analysis > Data Cube 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 Analysis in Catalyst

   • Look for the new Cube Configuration section under Data Cube 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

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 make it available

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

Working with Scenario Types

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

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 Configurator 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.

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

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

Issue: Perspective not showing up 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

Issue: Users cannot see or access the perspective

• Confirm you've granted appropriate permissions as outlined in the "Accessing and Sharing Your Perspective" section

• Verify the user is looking in the correct location (Analysis > Data Cubes)

• Check if the user is part of the correct permission group

Issue: Calculated measure not displaying correctly

• Check your DAX syntax for errors or invalid references

• Verify you selected the appropriate format for the expected output values

• Ensure the measure was processed after creation

• Test the measure with simple test data to isolate potential issues

Issue: Scenario types still appearing despite being excluded

• Make sure you processed the changes after configuration

• Verify you're using the correct perspective in your analysis

• Check if security settings might be affecting visibility

• Refresh the Excel connection to ensure it's using the latest cube definition

Issue: Performance issues with the perspective

• Review complex calculated measures that might be impacting performance

• Consider reducing the number of fields included in the perspective

• Check for circular references in DAX formulas

• Test with smaller data sets to identify performance bottlenecks

Conclusion

Cube Configurator 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 Configurator 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.