It might just be that users are querying data that is not in the data cache. (The users might have queried different data sets compared to today as well)
How many pag files do you have on the system?
There are 4 pag files.. I have attached the screenshot above. But I'm not sure why data cache hit ratio is so less. We have data cache set to ~43 GB which is more than enough for 7.5 GB .pag files.
Sorry missed it. There is no need of giving 4.5 GB. Technically 0.125 of the total size of pag files.
A maximum of 50 % of size of the pag files. Beyond that performance improvement is not noticed.
This might also be a product bug of not providing accurate ratio. Have you noticed any delay with retrievals today compared to yesterday? If not it might be giving a wrong ratio.
Its a new application which is not being utilized to the fullest as of yet. That's why the Data cache has been set to far more than what is actually required(.125*total size of pag files), to accommodate the expected number of pag files which which will increase over next few months.
The only difference that I have noticed is, 3 days back the forecast copy from working to final was taking around 2.5 hours to complete. Yesterday it took more than 5 hours. I started looking at the statistics when I came to know about the delay of that script. The Hit ratio while the script was executing was 0.16 then it dropped to .13. Eventually the script completed after 5 hours. Today when I saw the statistics, the hit ratio dropped to .02. Now its back up to .13.
Whether it is .02 or .13.. in both the cases, based on the cache settings we have, we should not be seeing such low values on hit ratio for data cache. I fear, this could be one of the potential reasons why all of a sudden our calc script execution time has doubled.
A couple of things to bear in mind:
1. You said that you "have data cache set to ~43 GB which is more than enough for 7.5 GB .pag files." - no, not necessarily, and definitely not in your case; remember that the .pag files contain compressed data blocks, the data cache contains uncompressed data blocks. If you are using bitmap compression, and given your very low block density, the uncompressed size of your 7m blocks in those .pag files is much, much more than 43GB (it's in the terabytes!).
2. Data can only come from the cache if it's already been loaded in. As I understand it (it would be easy to test) Essbase doesn't fill up the cache as soon as the application starts - even if the uncompressed data files are smaller than the cache. So for example, the first query after a restart would never come from the cache, no matter how large it is. That goes back to MPavan's point about users querying different slices of data potentially driving the hit ratio down.
I think routinely recording the metrics on a cube is a great idea but I don't think reviewing them looking for a problem (unless you actually have a performance problem) is worthwhile. I'd treat your calc performance problem as a calc performance problem and not get too hung up on the data cache hit ratio. Not saying it's not related, but with your block count and density, you are unlikely to find large calcs (e.g. version copies, full aggregations) being able to keep the data cache hit ratio up.
That makes sense. Thanks Pawan and Tim for helping me out.