Make the Most of Your CXO Integration Adapter by Running Stored Procedures Before Cube Processing
Some of the main EPM systems that we target with CXO, do not allow us to create direct connections, like we do for Essbase, SAP BW and Microsoft SSAS.
For these EPM systems, i.e. HFM, OneStream, Oracle EPM Cloud, we have created integration adapters.
Integration adapters are software modules that enable the following:
Metadata selection and extraction into SQL
Defining dataset and extraction of this data into SQL
SSAS cube creation and processing
Scheduling frequent incremental data extractions during the close cycle
The output is an MS SSAS cube with your EPM data that completely mimics your EPM system.
CXO can seamlessly connect to SSAS, so you can report on the EPM numbers.
Getting your EPM data into an SSAS cube “as-is” is the main objective for our adapters.
But, since we are building up an SQL database, could we do more than just create a copy of your EPM application?
Can we transform and enrich the EPM data before cube creation to optimize the cube and add valuable new insights?
Yes, we can!
Standard implantation – replicate EPM system "as-is"
Implementation with transformation and/or enriching, so the CXO cube has added features.
Q. What can we achieve with this capability?
1. Adding analytical insights to make your analysis more powerful – "attribute dimensions"
Your EPM application has a set of dimensions and within these dimensions you’ll find (multiple) hierarchies and members.
These members, like products, entities, channels and cost centers, can have properties or attributes, that are of analytical interest.
Entities can have a core activity or a strategic designation.
Cost Centers can have a value chain designation, like IT, HR or Operations.
Showing the totals for these properties and attributes on all levels can be powerful.
A suboptimal solution that is often implemented is creating more and more alternative hierarchies in EPM to group members together and calculate these totals that way.
A better way would be to derive real new dimensions, based on these attributes. Our adapters support that.
In the example above we see an entity-dimension with 4 base members and a Total.
With our adapters you could add attributes to these base entities, like "Core activity" and "B2B/B2C".
After transformation, by running a stored procedure before cube processing, "Core activity" and "B2B/B2C" will be real analytical dimensions that can be used like any other dimension from your EPM system.
In the example, on the right hand side, these new dimensions are on the rows and columns of a table. They can also be used in the Point of View, in drills, in stacked columns and so on.
The above example is small and simple, but you can imagine that implementing this, based on many members and a complex hierarchy, will create many new insights.
How do I start adding new analytical insights to my CXO cube?
Gather ideas for implementation
Contact CSM/AM to discuss options. PS can advise and help
2. Streamlining the SSAS cube by creating shared members
SSAS cube technology is powerful and flexible.
It has a smart caching mechanism for fast query response, and it allows partitioning to speed up processing by running processes in parallel, to name two benefits.
On the other hand, SSAS lacks shared member functionality.
Data for members that occur multiple times in a hierarchy, are stored multiple times. This grows the cube, slows down processing and can affect query response times.
Shared members are commonplace in EPM applications.
Most shared members, especially when they do not have a lot of data, are not an issue.
However, data dense members like “none” or “unspecified”, that occur multiple times can become a problem.
Our adapters, using a stored procedure that we have prepared, can target this issue and streamline your cube substantially.
This will reduce overall cube size, which will speed up cube processing and query processing.
Q. How do I know if I have this issue and if it needs fixing?
Is processing your cube taking a lot of time (> 30 minutes)?
Are many CXO reports (too) slow (> 10 seconds)?
Reach out to your CSM/AM to discuss the options.
We can help with diagnosing if shared members are an issue.
We can help to implement a solution.
Related Articles: