I used Sample Basic and zoomed in to all levels on Year, Measures, and Product. When I zoomed in on Market, Smart View never returned but rather just kept trying to zoom in for a while. After working on it for a bit, I decided to try the same thing in the classic Excel add-in and, guess what? The same sequence of operations is not even possible there as it easily shoots past the 65,535 rows supported by the classic add-in. I tried it in Dodeca and it returned a bit over 172,000 rows in 5 seconds, so it certainly isn't an issue with the Essbase server and large retrieves.
I next talked to one of my friends inside Oracle and found the source of the performance issue. If you have used the classic Excel add-in, you may remember seeing the Use Styles option which formats the cells returned by Essbase based on member cell or data cell attributes. Based on my VBA experience, I can tell you that doing lots of format operations in an Excel worksheet can be quite slow. In Smart View 11.1.2.1.102, there is a new option called Use Excel Formatting which also defaults to false. This setting is conceptually the opposite of Use Styles in the classic add-in. In other words, Smart View 11.1.2.1.102, along with all previous versions of Smart View, automatically formats the cells that are returned. In fact, you can see the formatting in the screenshot above.
To prevent this automatic formatting and speed up your retrievals, set the Use Excel Formatting option to true.
Once I made this change, the zoom in operation on Market did complete and brought back more than 172,000 rows.
Another thing you may note from the spreadsheet is that member labels are repeated. This is another difference between the classic add-in and Smart View. The Repeat Member Labels option has been renamed Suppress Repeated Members and the default behavior has changed. With Smart View, you must select the option to Suppress Repeated Members to see the same behavior you saw by default in the classic add-in. Frankly, I don't remember if this is something new in Smart View 11.1.2.1.102, but just be aware of it. In the screenshot below, I have set the Suppress Repeated Members setting to mimic the classic add-in.
Posts
9 comments:
I have been experiencing performance issues with the .102 patch. I applied the patch to APS, Essbase, and Smartview. Drill bottom on a large dimension (10,000 members) is taking quite a bit longer than it does through the add-in. I have tried your suggestions above and I am not seeing a significant improvement. Do you have any other suggestions?
Thanks, RB
My expectation is that it will not be as fast as the Excel add-in due to a difference in how the data is actually returned to the client. That being said, I would make sure your APS server is installed on a fast of a server as possible and that ample amounts of memory are allocated to WebLogic on that server (to prevent the server from thrashing memory).
In my testing, my entire stack is running on my laptop, but the laptop is a quad core Dell with 8 Gb of RAM and an SSD drive.
Tim
Also, I would make sure the client is a fairly powerful machine as well.. Compared to the classic add-in, which brought the data to the client in a highly optimized binary artifact, Smart View brings the data down to the client as xml and thus has to parse the xml and process the string values returned by the xml.
I have heard through the grapevine that the Smart View team is working on performance, but my expectation is that it will not be as fast as the classic add-in on a comparable machine. That being said, the new functionality makes Smart View more capable than the classic add-in, so the benefits may outweigh the costs.
Finally, if you need really fast operations into a spreadsheet against Essbase, perhaps you should give Dodeca a try.
Tim
Thank you for the information Tim! Much appreciated.
Thanks for the information Tim. To clarify, the performance issue wasn't a problem before the .102 patch. SV 11.1.2.1 worked great, no performance issues. Once we installed .102 patch we started seeing significant performance issues when drilling in on Sparse dimensions. The number of members in the dimension doesn't seem to make a difference. Drilling in on any sparse dimensions is very slow. Thanks again for your input.
I doesn't make sense that Smart View performance would get worse with the patch. Have you contacted Oracle support? I will think about the issue; if you find the reason for this performance issue, I would love to hear about it.
Tim
I have contacted Oracle and have an SR open with them. So far no progress has been made, but I will send an update once we figure out what is causing the issue.
Thanks!
Tim, had a SV perf issue and wanted to pick your brain.
Our Production environment is the following:
We have One Web Server with (Oracle HTTP Server), two application servers with the following products: Foundation, Reporting and Analysis (Financial Reporting) HFM Server and FDQM Server; each one of the Application Servers have all this products and both are working as HFM Cluster Nodes, Weblogic Servers of clusters and FDQM Load balance servers. Finally we have one RDBMS Server With SQLServer 2008 R2. All four servers are Windows 2008 R2 Standard Edition.
We made this installation and weeks later The company tell us that the Product must be in a Full SSL Scenario (Stage) we reconfigure all the environment on a Secure mode. All four Servers and the clients comunicate only with https protocol and everything have all the respective Certificates.
When users use Excel to connect to Smartview they experimented a very slow connection:
They use the following URL https://External_OHS_Virtualhost/workspace/SmartView, the System asks the user to logon with validate credencials and this process works with normal time delay, after that SmartView present the users the possible browse products, when we choose "Hyperion@ Financial Mangement, Fusion Edition" (HFMOffice Provider), Smartview takes about 3 or more minutes to chose as the registered HFM Machines/cluster, then we chose the HFM Application and in the last Connection procces Smartview takes again 3 or more minutes.
When users browse https://External_OHS_Virtualhost/workspace/, they can navigate on workspace without issues
The Smartview Log Does not chose any error
Users use Smartview without any delay on our test environment, which it is only one server with http protocols available only
We attach a word file with the pics of the process and we resalt the exacts steps of long delays, also we attach the registry of the shared Services
This would probably be a better question for the infrastructure experts.
We have run Dodeca using https through a proxy server and then https to our server and not seen a noticeable degradation in performance. My guess is that it is not a Smart View issue but rather a networking issue.
Tim
Post a Comment