Enterprise Planning System or Shopping List Editor

As xP&A (eXtended Planning & Analytics) and write-back solutions in Power BI gain traction, more vendors are entering the field, each claiming to offer “enterprise” solutions. Some even tout their products as the “Ferrari” of the industry, though the comparison often feels more like a toy car stacked up against the real thing.

True enterprise-grade xP&A solutions require specific core functionalities to meet rigorous demands, especially when supporting over 100 concurrent users.

Based on my 25+ years of experience implementing “Enterprise xP&A” solutions, here’s how I would define the standards that distinguish scalable, reliable platforms from the rest.

Properly Structured Data Model

Effective xP&A requires a properly structured?multi-dimensional data model?— the backbone of any robust analytics platform. The solution must accommodate connected dimensional models with efficient data storage and retrieval.

A denormalized, single-table approach that some vendors are using for write-back tables, as illustrated above, is fundamentally inadequate for enterprise use for the following reasons:

• Performance Issues:?Inefficient string storage and repeated data elements severely limit scalability.
• Change Management Risks:?Any change to a dimensional hierarchy (e.g., name changes or moving elements within the hierarchy) will cause duplications and conflicting hierarchies, leading to the collapse, like “a house of cards,” of the entire application.

Model Size and Volume

As outlined above, the best approach for an enterprise model will use a transaction table/dimensions star schema. This typically includes between?5 and 10 dimensions per model, with many of them having to support?1,000+ members?(at Acterys, our customers commonly use models with a few hundred thousand elements in dimensions). This means an enterprise system that deserves the name needs to handle?10 million+ base-level transactions.

For example, a common sales-orientated model will include 1,000+ products x 5 scenarios x 50 measures with 4 years of monthly data (48) x 1,000 outlets. That equals around 12 million populated cells if we consider that every data point is populated, which is likely in that example.

Most xP&A solutions will also include multiple models of similar size to cover other functional areas like?finance, HR, or manufacturing.

Given this, claims by some vendors that “we can do everything in a single Power BI visual” become quite unrealistic. A Power BI visual can handle up to 30,000 data points with relatively limited memory available. Considering the data volumes required in enterprise applications, this is obviously not a great idea if all the processing needs to be done in that limited environment.

Even worse, if users leave the page with the visual or interrupt an ongoing process, the entire operation can fail, often resulting in incomplete data within your model — with limited transparency on what was affected or why. A true nightmare for any CFO.

Comprehensive Client-Server Architecture

A client-server architecture is the most effective way to manage enterprise xP&A requirements. In this setup, only data changes are transmitted to the server, which processes them in real time and returns the results to clients.

This architecture ensures:

• Speed and Efficiency:?Processing is faster and more reliable than client-based approaches.
• Real-Time Collaboration:?Users avoid overwriting each other’s work, ensuring everyone is planning with the most current data.

?

Plan Entry Options

A key feature of an xP&A system is what options users have to enter their content. ?Most important are obviously numbers. With that the system needs to support entry on any level of the model. For example, on the most detailed leaf level but equally so on any aggregation with the option to specify how the value is to be broken down. This could be based on existing distributions like the previous year, the existing distribution on that node but also the option to allow user to set their own logic. Entry on aggregates also known as “splashing” brings me back to the capabilities of the model as this can mean millions of write back transactions that the system needs to handle. For example, just a simple new assumption of expense increases across the board can mean that 24m cells in the planning system (200 expense accounts x 12 months x 100 cost centres x 100 organizational entities) need to be changed. A true enterprise system needs to handle that in seconds. Other entry modes to consider are options to select multiple cells for entry, fill values (e.g. until the end of the year) and more advanced concepts like goal-seek, optimizations etc. In addition to numbers enterprise systems also need to deal with unstructured content like comments, file attachments, etc.

Complex Calculation Logic and Real-Time Processing

xP&A models require real-time updates, enabling users to see immediate results when they input data. This approach ensures that any?concurrent?updates from colleagues are instantly reflected, preventing conflicts or overwrites caused by outdated or missing entries.

Real-time updates foster seamless collaboration, as team members work with the most current data available, enhancing decision accuracy and operational efficiency. In fast-paced environments, this capability is essential for maintaining data integrity across the organization and ensuring all users, regardless of location, are aligned on the latest insights and projections.

Governance and User Rights Management

Governance must provide granular permissions (write/read/none) that can be set at the level of individual dimension members across all dimensions, not limited to just the scenario level. This approach ensures precise control over who can access and modify specific data, enabling organizations to maintain security and compliance down to the most detailed data points.

The second critical aspect of governance is a robust audit logs system. Effective audit logs should transparently record every transaction and user action within the platform, enabling a complete and traceable history of changes. Additionally, these logs should allow for roll-back capabilities, supported by flexible filtering options, so administrators can quickly isolate specific events, monitor compliance, and restore previous states as necessary.

This dual approach to governance — detailed permissions and comprehensive auditing — ensures data integrity, security, and transparency throughout the xP&A process.

Speed and Real-Time Updates

Fast, real-time updates are essential in any enterprise xP&A application. Without them, users risk basing decisions on outdated data or inadvertently overwriting entries.

• A robust system must: Handle real-time input from a large number of concurrent users, enabling synchronized updates across the system. Support high-speed data processing to ensure minimal lag and allow for immediate decision-making.

Smart Multi-front-end Approach

Power BI is a fantastic tool for xP&A requirements, but it is not a “one-size-fits-all solution.”

Particularly in finance, users will require a flexible cell-based approach. However, this approach shouldn’t lead to the dreaded “spreadsheet hell” caused by excessive offline exports to Excel from a Power BI visual.

Acterys supports a smart Excel link that allows users to work with the flexibility of a spreadsheet while maintaining a dynamic, governed link to the data. No data is stored in the spreadsheet itself; instead, authenticated users can access data that automatically updates to the latest version or applies dynamic filters, ensuring control and accuracy.

Equally important here are integration options like the system running within existing productivity and collaboration solutions (e.g. MS Office and Teams) and that it supports a robust API so that organizations can integrate the planning system in other existing applications.

Evaluating an Enterprise xP&A Solution

A true enterprise xP&A demands robust data models and governance, a client-server-based architecture that enables real-time updates even with large data volumes, and essential logic and workflow capabilities. It must also support flexible, cell-based analysis in a smart and efficient way.

When evaluating an xP&A solution for your organization, I strongly recommend taking the following steps:

• Conduct a Trial with Realistic Data Volumes and Logic:?Use scenarios and data that closely match your organization’s environment. Testing with multi-dimensional data and complex calculations will reveal the model’s true capabilities.
• Simulate Concurrent User Access:?Enterprise-grade applications must handle multiple users working simultaneously. Simulate expected user numbers to ensure the system can handle real-world traffic without performance issues.
• Ensure Robust Governance and Auditing:?Verify that the solution provides fine-grained access controls at the individual dimension level. A comprehensive audit trail will be vital for tracking changes and meeting compliance requirements.

Lastly, involve all user roles that will engage with the system to ensure it meets diverse needs across your organization.

By following these steps, you can ensure the xP&A solution you choose not only meets your current needs, but also positions your organization for future growth and adaptability.

Get Smart About xP&A. Get Smart XL.

If you’re looking to elevate your xP&A solution to enterprise standards, consider Acterys Smart XL, a powerful tool that bridges Excel’s familiarity with Power BI’s capabilities. Smart XL offers a seamless integration, allowing users to work within an Excel-like interface right in Power BI, reducing the learning curve and boosting adoption. With Smart XL, your organization gains:

• Streamlined Data Centralization:?Consolidate siloed data into a unified Power BI framework, minimizing risks and improving efficiency.
• Enhanced Collaboration:?Real-time data updates keep everyone in sync, from analysts to executives, ensuring faster, data-driven decisions.
• Simplified Scaling:?Smart XL supports high data volumes and complex operations without compromising speed or flexibility.

Ready to experience a scalable, enterprise-grade xP&A solution??Discover how Smart XL can empower your team to make smarter, faster decisions with ease.

?