There was an issue reported from user that a small sheet with 15 rows and 6 columns from reporting cube (ASO) is taking around 6 minutes to retrieve and getting timed-out
We tried from our end couple of times and identified that it was sporadic and tried all possible options to fix it. Sometimes, it retrieves very quickly and sometimes it takes a huge amount of time.
Option 1: Outline size was around 200MB and we compacted the outline which brought down the outline size to 22 MB. But, this didn't work
Option 2: We cleared aggregations (We were very sure that this will not improve performance). It didn't work
Option 3: We have a database where we capture all our metadata and data loads with start time, end time and elapsed time. We noted down the times when the retrievals were faster and looked in to the database to find what process were run before the retrievals.
We found that whenever a dimension build process is completed, the retrievals are faster but after that the retrieval times increased gradually. We have a process which runs every 10 mins which does currency conversion in Planning (BSO) and refreshes a replicated partition to push data to reporting (ASO) cube. We kept retrieving after each refresh is completed and could see that the retrieval times were increasing gradually. We have also noticed that the Plan to report process time was also increasing gradually
We identified that the Plan to Report process is the issue. After lot of digging and analysis, we identified that every time a partition is refreshed a new slice gets created in reporting (ASO) cube and if a user retrieves any combination which needs to check in both main slice and incremental slice and then has to do some internal processing to get you the right numbers
As a solution to the issue (It's not an issue but rather essbase behavior), we have to merge the slices. This improved the retrieval times from 6 minutes to 30 seconds. This has also reduced our Plan to Report process and the timings were very consistent at 6 minutes which was taking somewhere between 6 mins - 15 mins depending the number of slices
We also had to look from a performance standpoint to decide which type of "slice merge" we had to choose
Merge to the slice was the way to go. merge takes around 25 seconds with / without the cube aggregation
It was very difficult to identify the problem as what was causing the retrieves to be slower but now everything works as normal
We tried from our end couple of times and identified that it was sporadic and tried all possible options to fix it. Sometimes, it retrieves very quickly and sometimes it takes a huge amount of time.
Option 1: Outline size was around 200MB and we compacted the outline which brought down the outline size to 22 MB. But, this didn't work
Option 2: We cleared aggregations (We were very sure that this will not improve performance). It didn't work
Option 3: We have a database where we capture all our metadata and data loads with start time, end time and elapsed time. We noted down the times when the retrievals were faster and looked in to the database to find what process were run before the retrievals.
We found that whenever a dimension build process is completed, the retrievals are faster but after that the retrieval times increased gradually. We have a process which runs every 10 mins which does currency conversion in Planning (BSO) and refreshes a replicated partition to push data to reporting (ASO) cube. We kept retrieving after each refresh is completed and could see that the retrieval times were increasing gradually. We have also noticed that the Plan to report process time was also increasing gradually
We identified that the Plan to Report process is the issue. After lot of digging and analysis, we identified that every time a partition is refreshed a new slice gets created in reporting (ASO) cube and if a user retrieves any combination which needs to check in both main slice and incremental slice and then has to do some internal processing to get you the right numbers
As a solution to the issue (It's not an issue but rather essbase behavior), we have to merge the slices. This improved the retrieval times from 6 minutes to 30 seconds. This has also reduced our Plan to Report process and the timings were very consistent at 6 minutes which was taking somewhere between 6 mins - 15 mins depending the number of slices
We also had to look from a performance standpoint to decide which type of "slice merge" we had to choose
- Merge all slices to main cube
- Merge incremental slices to one slice. Since, it is creating a new slice, everything went fine as
- Merge was taking more time as we have aggregations on the cube. We had to drop the aggregations to make the merge to cube faster which was not an option
- We tried merging without the aggregations and was taking more than a minute
Merge to the slice was the way to go. merge takes around 25 seconds with / without the cube aggregation
It was very difficult to identify the problem as what was causing the retrieves to be slower but now everything works as normal
Comments
Post a Comment