Source: OneStream Champions
Do any of you run allocations in OS? Would you be able to share how you accomplish this?
Currently we manually reallocate data that comes into our income statement accounts under one UD2 to two UD2s based on the percentage split of units (which is a stat account in OS). This is loaded as a balanced journal entry every month but I’m wondering if there’s a more efficient, less manual way to accomplish it.
I tried reviewing OS academy, the design doc and golfstream and can’t find a good example of an allocation being run. Would any of you have a suggestion? For a simple allocation like this, would you recommend a CV form to allocate or would the Allocation journal entry type work better? Or maybe it’s a formula on the UD2 member itself that triggers the allocation?
I’ve also submitted a support ticket but was thinking that maybe some customer experience would be useful too.
One key aspect of designing an allocation solution in OneStream is the ability to re-run the allocation process step. You have a source to get the allocation data, and you have a destination you want it to go. If it is based off of Sales or Revenue (or Expenses) and those source values change, you will need to re-run it.
A good setup for this would be a Dashboard view, with a title bar that has informative labels such as the last period loaded, the last time the allocation was ran, and by whom. A Cube View (or 2) to show the source (from) and destination allocation. Maybe even a balance check to show the source values equal the destination values.
If you’d like, there is a customer I worked with that does a multi-step allocation process that starts with UD2, then allocations UD2 to UD3, then UD3 to UD4, and lastly UD4 to UD5. It is progressive and can be re-ran if source values change. For this customer they have a Dashboard view with buttons to step through the allocations. We are leveraging the UD7 dimension for the Allocation In and Out values as sort of a staging area for the data.
This is a complex case study and not the norm, but the fundamental process of sourcing the values, matching the amount in the destination allocation members, and having a process that can be re-ran as source values change.
Lastly - a Confirmation Rule can be applied to make sure Source = Destination intersections.
Hope this helps! Let me know and I can reach out to that customer and see if they can talk to you.
Hi all - thanks so much for your replies!
Michael - it’s lovely to hear from you again!!
I appreciate the input and the suggestions, but I think those might be a bit more than what I need at the moment. The JE is run after the import channel is closed so I’d set this process up the same so the allocation need only be run once (unless we re-open ERP but that’s unusual). And because we currently have it set up as a JE, I’m comfortable loading the allocation balance at the O#Forms member in order to call out that it’s an allocation. We also have a UD3 that’s basically a more detailed Origin dimension so it will be called out there too, negating the need for a detailed allocations breakdown.
It looks like the Allocation JE type and the an allocation Form would mean I’d have to define every single account we’d like to pick up to allocate instead of a member filter so I’m looking into a business rule to get the work done.
Michelle, would you be willing (if you’re able) to look at your formula/business rule and tell me how it begins? Is it an api.Data.Allocate or an api.Data.Calculate? I’m struggling on how to begin the calc because there’s no real sample for the api.Data.Allocate and it would seem like I’d have to make an api.Data.Calculate for every single account I want calculated which also doesn’t feel right to me.
Thanks again for everyone’s feedback!! Great to be able to get some information from other customers.
We had allocations built out in our UD4 members that run when we process the cube. It looks at certain accounts and allocates it based on percentages loaded into other accounts. This is not a part of our normal financial process, but is used by our Cost Accounting group to report their information. It is super quick and easy to run, but when we’ve had major changes (beyond just changes to the percentages), it was too difficult for me to be able to change. We ended up having to have consulting help to make those changes. I would rather have something we can maintain ourselves, but it works well otherwise.
I am assuming that the source data required for the allocation calculations are held in OneStream.
You can set up an “allocation” member on one of your dimensions and put a member formula for your calculations on that member.
Could be on any dimension, incl. UD2 if that works best for you. Example:
You can take your source data to be allocated from the member “TotalDataSource”, split the data by your allocation key, and make data destination = “Alloc” (you can split this in “AllocIn”, “AllocOut”, etc. if required).
With a solution like this, you can keep source data and allocated data on same member on all dimensions, except the dimension where your “Alloc” member is, but you can of course also choose other members on any dimension.
This ensuring your source data are untouched, and that the allocation data are kept separately.
You can then also use the dimension to report both incl. and excl. allocations.
Nicole - we have extensive mutli-pass allocations running and updating hourly with our ERP loads. We utilize a series of UD1 and UD8 to give us traceability into the in/out charges for the process and load our drivers in on a monthly basis. We utilize api.data.calculate for each calculation, with some generic IF statement logic sitting on top to skinny down the number of calcs to run.
Here’s a write-up I did about an allocation process I did for a customer. This could be set up to run off of your consolidation or kicked off with a Data Management Job.
Let me know if you have questions or need help executing if what you need is more complicated than what I’ve outlined. Shameless plug, I work for MorganFranklin Consulting and one of the things we do is help people with their OneStream implementation. I’d be happy to discuss what MFC could do for you.