I was recently at a client who is migrating to OAC. We brought one of their applications into OAC. It is a BSO application. On-Prem the run some currency calculations and then aggregate the cube. The resulting cube is about 300gig and takes over 4 hours to calculate. They hate to make changes as they take so long.
So, I suggested we look at making it hybrid. They can still run their calculations and gain the speed of not having to do aggregations.
For our test, first we loaded data into the non-hybrid cube in OAC and just ran the aggregation calculation. After 3 hours, it had filled the disk, the calculation failed and we had to clear the data (Not sure why the disk filled, we are talking with Oracle about that)
So for my Hybrid version, I took all of the sparse dimensions and make them dynamic, not that I needed it, but I also set the HYbridBSOInCalcScript FULL. We loaded the same data file and tried to retrieve from Smart view with some large retrieval templates they use on-prem. Retrieval time was ok, but not stellar. Their largest sheet that takes about 20 seconds on-prem took about a minute to refresh.
They were ok with that, but I was not. Looking at their outline and the retrievals, I discovered one dimension that is 17 levels deep that is used at the top two or three generations in a lot of reporting. On a whim, I changed that dimension back to stored and wrote a calculation script to just aggregate that one dimension, fixing on level 0 of all of the other dimensions. That aggregation took 27 seconds. When we refreshed the excel reports, the time was about the same as on-prem, maybe a little faster.
Since then, they have run a number of other calculations, make adjustments in the cube and are no longer fearful of kicking off a calculation.
Is Hybrid worth it. I think they would say YES
ODI in the hybrid database world – Amazon Redshift – AWS CLI
-
Written on June 5, 2023 by Rodrigo Radtke de Souza Hi all, probably this is
the last post of this series on how to load data from on-premises databases
to ...
1 year ago
No comments:
Post a Comment