asked on July 21, 2021
Hello
At the moment we are using the Flexmonster pivot table as a trial version. Our goal is to migrate our quality reports from MS web components to another solution that supports pivot table in a web site. Data are provided by Microsoft Analysis Services.
I have set up connections via XMLA and via Flexmonster Accelerator too, both are running. If the Accelerator is used without the cache option, the performance is not significantly higher than the OLAP connection. With a lot of data and with enabled cache, the requests are faster via Accelerator. This service has also some limits, sometimes it stumbles on a request which has passed before:
| ERROR | The query result is too large at Flexmonster.Accelerator.Controllers.FlexmonsterProxyController.DiscoverMembers(DiscoverMembersArgs args) in C:\Work\git\PIVOT\projects\Datasource Proxies\Microsoft SSAS\Flexmonster.Accelerator\Flexmonster.Accelerator.Core\Controllers\FlexmonsterProxyController.cs:line 598
The biggest issue is a large dimension with 4.5 million data points. When I add this field to the table, the complete dimension is loaded regardless of filters. The Accelerator stops after a while with an OutOfMemery exception (CACHE_MEMORY_LIMIT=0, CACHE_ENABLED=true). I have increased the process priority and the server has enough (free) memory. The OLAP connection needs 15 minutes to load all data. A subquery does not help, or I did not use the right query.
Perhaps the architecture of the data cube is not optimal, with the old web components or with an Excel pivot table this issue does not exist. What suggestions or advice do you have?
Best Regards
⋅ July 22, 2021
Hello,
Thank you for reaching out to us.
When using Microsoft Analysis Services, a new query is formed on every change in the report (for example, expand, filter, etc.). This means that only the part of the data needed for the requested representation (expand, filter, etc.) is loaded at a time. You have the ability to load just part of the data necessary for the specified report.
Being a client-side component, Flexmonster relies on resources available to the browser, which affects the loading time and the maximum size of the data that can be handled on every particular machine. This means that the client machine’s RAM determines how much data can be loaded at once, and CPU capabilities affect how much time is spent on the data analysis. Although the lazy loading technique is used in the case of Microsoft Analysis Services, some machines may just not handle loading a large dataset from a certain point.
With this in mind, our team suggests trying the following:
subquery:
Flexmonster first requests all unique entries of a field to display them in the filter pop-up. Then the query for displaying the specified report is initiated, and the required filters are applied.
This can be adjusted by specifying the subquery property in the Data Source object.
As a result, only the specified members will be loaded as opposed to loading all of them.
Here is an example of how subquery could be used to decrease the server's response: https://jsfiddle.net/flexmonster/3dmbzyp5/.
Flexmonster provides the possibility to set default report settings, specifying how to display the data initially.
This way, you can provide the desired starting points for the reports (for example, specifying which fields to display, applying an initial filter to the fields, etc.) and, in return, decrease the size of the response from the server.
We have a section in our documentation with examples on how to configure the report. Please see https://www.flexmonster.com/doc/available-tutorials-report/.
We hope this helps.
Feel free to reach out if further questions arise.
Kind regards,
Vera
⋅ July 23, 2021
Hello,
Thank you for your reply. I haven’t been able to solve the problem yet.
Resources
Maybe a client-side component needs local resources too. The Accelerator who runs out of memory is on the same server as the SSAS. With disabled cache the Accelerator has delivered all data. How exactly can I increase the cache size of the Accelerator? When I chose a CACHE_MEMORY_LIMIT=5000 it should be 5000 MB (5'242'880'000). The Accelerator shows another value in the logs: | TRACE | CACHE: Memory Limit Exceeded - 2320396700 / 947912704.
Setting specific reports
We already use specific reports. Many users have their own report with the required values. If they need more data, they can use additional fields as a feature of pivot tables. As a first step I will create the new reports in the same manner as the existing reports, it means I use the same fields and filters. In a later step, I could implement some additional configuration which are supported in your pivot table.
Using subqueries
Of course, it is the best way to load only the necessary data. I have tried out some different ways to define a subquery. I have used a time range, or a specific value how it used in your example and the table shows the data from the subquery. But only this special data field, serial numbers of units, are always fully loaded even this field is already placed in the Slice.
Do you support customer also how they can create subqueries for their specific data? Only with the Premium Edition or with the Standard Edition too?
My problem is to show our users the Flexmonster pivot table as a preview with this issue. When I cannot be sure that data of all dimension will be available (in an adequate load time), the users will not accept this solution.
Best regards
⋅ July 26, 2021
Hello,
Thank you for your reply.
About cache size limit:
We checked on our end and found the cause of the difference in the cache size limit values.
Our team provided the fix with today's minor release (see release notes).
This is available in the latest version of the Accelerator. Here is our updating to the latest version guide for assistance.
Could you please let us know if updating the Accelerator resolves the issue?
Regarding configuring subqueries:
We would like to explain that we do not specialize in Microsoft Analysis Services configuration. Still, our team is ready to help out with any Flexmonster-related questions. When it comes to Microsoft Analysis Services configuration, such as configuring cubes, defining subqueries, etc., we recommend referring to the Microsoft Analysis Services documentation.
Should any additional questions arise, feel free to reach out.
Kind regards,
Vera
⋅ July 29, 2021
Hello,
Thanks for your answer.
New Accelerator version 2.9.4
The fix solved the issue with CACHE_MEMORY_LIMIT setting:
| TRACE | CACHE: Memory - 3396676420 / 5242880000
My problem still exists because each time the full dimension was loaded.
SSAS - Configuring a secure HTTPS connection
Just a little comment to the registration process of the server certificate. On a Windows Server OS the single quotes for the appid parameter are not necessary:
netsh http add sslcert ipport=0.0.0.0:50005 certhash=0102030405060708090A appid={00112233-4455-6677-8899-AABBCCDDEEFF}
see also
netsh http add sslcert /?
Best regards
⋅ July 29, 2021
Hello,
Thank you for your reply.
Flexmonster loads unique entries of each dimension for all features to work properly (for example, filtering). One way to limit the number of members would be to define a subquery. Aside from defining a subquery, perhaps it could work to create a subcube of your cube containing only the necessary prefiltered dimensions and connect Flexmonster to it. This could help to reduce the query response significantly. Here is a link to the corresponding documentation: https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/mdx/building-subcubes-in-mdx-mdx?view=asallproducts-allversions.
About the single quotes for the appid parameter, thank you for sharing this with us.
Our team will update the documentation accordingly.
We hope this helps. Feel free to contact us in case of further questions.
Kind regards,
Vera