» If are trying to build Entity dimension (for example), if your input file has duplicates (i.e. multiple rows for each forecast period) create brand new file with unique entity records specifically for build process.
I had input file size of 38GB need to build and load to ASO database. File contained same contract numbers for multiple periods (each forecast period is one row in the input file), it took more than 2 hours to build the contract numbers. So I created one file with unique contract numbers and created a new build rule. it took less than 10 minutes to build Entity dimension.
» Replace
Zeros to #Missing for all Measures column, this will reduce the Cube size and improve the load time.
»
Move "temp" tablespace to different Disk. Calculation may use "temp" tablespace for intermediate aggregation results, so allocating "temp" and "default" tablespaces on different physical devices may help the performance, although it is of less importance than for the data load.
I moved the temp tablespace location from D: to E: to allow the buffer commit and aggregation process to use different physical disks for reading and writing. This reduced the buffer commit from 15 minutes to 10 minutes.
» Try adding CALCPARALLEL. Sometimes it may improve the aggregation.
» I added "DLTHREADSPREPARE app_name 3" and "DLTHREADSWRITE app_name 3" to the Essbase.cfg to increase the number of read and write threads from the default of 1 to 3 but only for the app_name application. The additional threads reduced load time from 110 minutes to 57 minutes.
» Consider using
Hybrid Analysis to store the lower level of details of the largest dimension in the relational database.
» It is recommended to
RESTART the ASO application before the build process.Because the ASO cache is allocated on demand and not needed during dimension build, memory used by the ASO cache is freed for the build and restructuring processing.
» Data loads from multiple data sources or files should always be combined into a single ASO data load using an ASO load buffer. Multiple data loads are accumulated in the buffer and then moved to the final storage location.
» Cache size configured for the data load should also provide good aggregation performance; however, when increasing the number of calculation threads, you may also need to increase the cache size, because cache memory will need to be divided between more threads.
»
Sorting the input files to the same order as of the ASO cube dimension sometimes improves performance during build process.
Subscribe