there are multiple ways to transfer data from BSO to ASO.
data file..extract from BSO and load into ASO..
PBCS way of "Smart" push..
Aggregation time high in BSO when::
1.when you have stored members in upper levels and hierarchies are bigger
2.number of dimensions are higher like 10-20 dimensions..
these cases BSO performance is not that good.. aggregation for never use combinations and no data combinations is difficult..
ASO engine is prepared for straight aggregations.(FAST cant stress enough). ASO straight aggregations are so much faster that BSO will not even come close... The ability of ASO to capture the combinations that are being requested ... so that only those intersections are pre-aggregated (takes small amount of time) comes really handy to match BSO "stored" performance. These are some of the reasons why you have ASO reporting cube in PBCS.
aggregated BSO cubes tend to have large size (disk). The same aggregations supported through ASO results cube size to tiny compared to ASO (ex. 40Gig vs 3Gig)
Even in on-premise..for example in workforce rollups..are a pain as the number of employees goes beyond a certain range.. the full aggregations take a long time.
BSO does have more calculation capabilities due to which it survived..
there are limitations to ASO cube.. you can only load at level 0.. aggregations are never stored (except in the aggregate views.. not accessible from behind the scenes).
Thanks Sirini for the detailed reply. So...
When we use ASO cubes, there's no need to run aggregations in BSO? this means while preparing budget in BSO users can't see values at total levels. Rather the consultant will develop separate forms in ASO and attach aggregations there, so that whenever user wants to see total figures he comes to the forms built in ASO plan and run aggregations? Like you gave example of workforce, it means to see the aggregated data the same kind of forms should have been developed in ASO so that aggregated figures can been seen quickly. What I understood is users are preparing budget in forms built in BSO cubes but just to see totals they go in replica kind of forms built in ASO. If this is the case then I would make a form specific aggregation rule that will probably have less members in FIX and can run quickly instead of making duplicate forms and developing aggregation business rules there. Any comments on this?
I would appreciate if you can share some real scenario in which you used ASO and how. Thanks again.
The implementation details drive how you will endup... as it is subjected to interpretation I will avoid comments.. in EPBCS case you may not even have a choice of changing design much... ASO query tracking mechanism allow to select aggregate views (close to aggregations in BSO.. but much faster aggregation). But looks like you already have a good idea about how to balance between ASO and BSO..so no additional comments needed from me...
There are too many variations on workforce...worked with a simple one.. ..all employer side of expenses are at employee level. (level 0).. aggregation = pain.
on my current project we have two BSO cubes.
One is revenue, one is workforce.
Both having drivers elements that are used to build financial plans.
Both then use a Business Rule to move the worked figures into the organisations Chart of Account structure, when they are satisfied that their iterative process of finding the 'right' numbers is complete.
Singularly they only present the income and expense side of the plan, respectfully.
Both aggregate for a single version / scenario / year combination in ~ 15 mins.
Using Smart Push which runs in typically ~ 15 seconds per database the users transfer the Chart of Account data across to the ASO database.
All end user reporting is driven from the ASO cubes.
Some form content is driven from ASO cubes related to Chart of Accounts.
This is not the right way to use ASO, but just one way to use ASO.
So what I understood is you have two BSO Cubes: one for revenue and other for opex. Then the final figures from these two BSO Cubes go to a third Cube (Financials). I believe the Financials Cube is also a BSO because when final figures are brought here, we need perform various calculation as below that can't be done in ASO:
- Make closing balances for balance sheet accounts;
- Balancing the total debit and total credit side by putting difference in cash/overdraft (this would need aggregation to calculate this difference)
- There could be other calculations as well like crediting prepayment accounts by the debit amounts of expense in opex accounts.
A user will finally place it's reliance on the whole chart of account (Trial balance) by looking at total levels of Entity, Department, Location, Project and from Account dimension at total assets, total liabilities, total sales, total expenses etc... To verify this, you have created a similar Trial Balance Form in ASO cube. User goes there and run aggregations in ASO to see data at high levels. If something seems wrong he again comes back to Trial Balance in BSO amends the figures. This is how the ASO plan is being used?
If this is the case, then why don't we stay in BSO only, because anyway we are doing aggregations there (As mentioned in step 2) plus just to see the totals user has to go to other forms in ASO cube doesn't convince me to make a duplicate form in ASO again each form in BSO. Adding last thing: if parents in sparse dimension are store and once we have run aggregations, BSO is as good as ASO (with not a big difference anymore)? Will appreciate your comments on this.... Thanks.
ASO in a nutshell -> it aggregates quickly. If you look at a total in ASO it is correct. It gives you a chance to capture the position in a BSO cube at a point in time.
BSO, may be work in progress, may be un-aggregated.
I am not trying to convince you on this, if your BSO cubes are constantly aggregated quickly and you have no need to freeze the plan at a point in time (yes I know you can do this by copying to a different version also) then probably no point in investing in ASO.
Note I am not suggesting duplicating forms, but more ASO being useful for reporting, if form aggregation is your issue then better is to have your forms kick of partial aggregations based on input where parent levels will be relied on in subsequent forms later on, you might want to google Cameron Larkspur's very useful blogs on this.
Note also in my scenario income and expenditure do not exist together in a single BSO cube, the ASO cube 'consumes' the end result of both