This content has been marked as final. Show 9 replies
I'd be curious to find if there is a solution to this problem. We are on HFM and SV 126.96.36.199 and have the same problem (Win7/Office 2010 machines). It's not consistent when it happens and if you hit refresh a couple more times (or use the F2 method) all of the cells will eventually populate. Additionally, it never puts anything into the SV error log to help with figuring out the problem.
Using the HSGetValue formula is very inefficient as it makes many more calls to the database then a simple retrieve. Therefore it is likely that you're running out of ports during the refresh phase.
I would recommend avoiding the use of the formula as I always found that although it looks pretty and seems straightforward, it caused more problems then it solved.
When you say "simple retrieve" are you referring to an ad hoc analysis? Or is there some other function that can be use to retrieve data from the cube? I'm hoping it's the latter, in which case can you please elaborate?
Relying only on ad hoc is not an option for us; we have too many canned reports that are reliant upon pulling data into a pre-formatted template. As odd as it may sound, we actually need (and expect) Hyperion to work.
Call it Free Form reporting if you like.
If you have the Excel Formatting option checked your canned reports will be ok won't they? What exactly is stopping you from doing that?
As odd as it may sound, we actually need (and expect) Hyperion to work.Well it can be argued it's caused by restrictions in the OS but I guess it's open to argument. Which I don't intend to do!
We had one similar issue where HSGetValue was having two times Account# as it was referring to another column to get the value for accounts. Even in that case, when we hit F2 the value got populated.
Question: Are there any dynamic link eg. Account# & A12 and A12 is a link to an external sheet?
I agree with Steve here.
Use HSGetValue if you have a few cells that need data with lots of formatting around them. If you have an entire grid of HSGetValue cells then you're using this technology in a way it wasn't intended and you should be using free form mode.
Addressing the issue here by turning off autocalc is not the answer and really only moves the problem.
Your spreadsheet should favor rows over columns. Minimize the use of columns and maximize your use of rows. This will help the effieincy of the sheet and might help the hsgetvalues.
If you refresh the sheet several times and the error is still there, or there intermittantly, then you're asking Excel to do something it can't and need to review the design of the sheet.
If you simply must have many HSGetValues cells in perhaps a highly formatted report, then consider spreading those retrieval cells across several background sheets that the top sheet then refers to. Create a simple VBA to refresh the background sheets one at a time. This spreads out the function work for excel and gives smartview time to address all requests.