As xP&A 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 30 years of experience implementing “Enterprise xP&A” solutions, here’s how I would define the standards that distinguish scalable, reliable platforms from the rest.
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 below, is fundamentally inadequate for enterprise use for the following reasons:
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.
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:
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 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.
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.
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.
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:
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.
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:
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.
© 2024 Managility Pty Ltd All rights reserved.