Gaps in request during execution of Excel Macro Report from KYVOS OLAP cube using Analytics Service

[Aakriti_5457]

I encountered following problem while performing a comparison activity of executing Excel Macro on OLAP cubes in KYVOS and SSAS, using Analysis Services Driver in Excel.

There is significant difference in Excel Macro report execution time between the KYVOS and SSAS cubes. Further investigation revealed that there are some gaps in the request flow from Excel when communicating with the KYVOS cube, which are not present when interacting with the SSAS cube using same report with Marco. I also examined the response size and number of requests, but it did not explain the observed time difference.

I am seeking guidance on which direction to explore in order to address this issue . I would appreciate any assistance or suggestions you can provide.

[#2]

@Aakriti_5457 

To address the issue of gaps in the request flow and improve the execution time of the Excel Macro report on the KYVOS OLAP cube, you can explore the following directions:

1. Network and Connectivity: Check the network connectivity and ensure that there are no network-related issues causing delays or interruptions in the communication between Excel and the KYVOS cube. Verify the stability and speed of the network connection.
2. KYVOS Configuration: Review the configuration settings of the KYVOS cube and ensure that it is optimized for performance. Look for any specific settings related to request handling, caching, or query execution that may impact the response time. Consult the KYVOS documentation or reach out to their support for guidance on optimizing the cube performance.
3. Data Model and Cube Design: Examine the design of the KYVOS cube and the underlying data model. Check for any potential bottlenecks, such as inefficient hierarchies, large dimensions, or complex calculations, which may impact the query execution time. Optimize the cube design based on best practices for performance optimization.
4. Query Optimization: Analyze the queries generated by the Excel Macro report and review their performance against the KYVOS cube. Look for any opportunities to optimize the queries, such as reducing unnecessary calculations, aggregations, or filtering. Consider leveraging cube partitions, aggregations, and indexing techniques to enhance query performance.
5. Macro Code Analysis: Review the Excel Macro code and ensure that it is efficient and optimized. Look for any loops, unnecessary calculations, or suboptimal coding practices that may contribute to the performance gaps. Optimize the Macro code based on best practices to minimize execution time.
6. Analytics Service Settings: Explore the settings and configurations of the Analytics Service component in KYVOS. Check if there are any specific settings related to request handling, caching, or query execution that can be adjusted to improve performance. Consult the KYVOS documentation or reach out to their support for guidance on optimizing the Analytics Service.
7. Benchmarking and Profiling: Conduct benchmark tests and profiling of the KYVOS cube to identify specific areas of improvement. Use profiling tools to analyze the query execution and identify any performance bottlenecks. Compare the results with the SSAS cube to understand the differences and potential areas for optimization.
8. KYVOS Support: If the issue persists and you have exhausted all possible optimizations, consider reaching out to KYVOS support for further assistance. They may have specific insights into the behavior of their OLAP cube and can provide guidance or potential bug fixes to address the performance gaps.

By exploring these directions and optimizing various aspects, you can narrow down the cause of the gaps in request flow and improve the execution time of the Excel Macro report on the KYVOS OLAP cube. Created with the help of AI.

Was with my Latin before at the end :)).

Hope it helps!

[#3]

hi @NikolinoDE , Thanks for responding.

We have tried all the suggested request and I'm from Kyvos Expert team . The OLAP cube which I'm querying is also optimized and I'm using same macro with the cube on both platform.
So, it might be a issue related to excel .Can you pls suggest anything related to that.

[#4]

@Aakriti_5457 

If you have already optimized the OLAP cube and confirmed that the issue is specific to Excel, here are a few suggestions to address the issue:

1. Excel Version: Ensure that you are using the latest version of Excel, as newer versions often include performance improvements and bug fixes. If you are not already using the latest version, consider updating Excel to see if it resolves the issue.
2. Excel Add-ins: Check if there are any unnecessary or conflicting Excel add-ins installed. Sometimes, certain add-ins can impact the performance of Excel. Disable any add-ins that are not required for your analysis and see if it improves the execution time of the Macro.
3. Calculation Options: Excel has different calculation options that can affect the performance of formulas and macros. Make sure you have the appropriate calculation option selected for your needs. You can access these options by going to the Excel Options menu and navigating to the Formulas or Calculation section.
4. File Size and Formatting: Large file sizes and complex formatting, such as excessive conditional formatting, charts, or linked objects, can slow down the performance of Excel. Try to minimize the file size and simplify the formatting, especially in areas where the Macro interacts with the data.
5. Macro Optimization: Review your Macro code and ensure that it is efficient and optimized. Avoid unnecessary calculations, iterations, or resource-intensive operations within the Macro. Consider optimizing loops, using efficient data structures, and reducing redundant calculations to improve the overall performance.
6. Memory Management: Excel's performance can be impacted by the available memory on your computer. Ensure that you have enough free memory for Excel to operate smoothly. Close any unnecessary applications or processes that may be consuming memory. You can also try increasing the available memory for Excel in the Excel Options menu under Advanced settings.
7. Hardware and Resources: If possible, try running the Excel Macro on a different computer with higher system specifications. This can help determine if the issue is related to hardware limitations or resource constraints on the current computer.
8. Reinstall Excel: If none of the above suggestions work, you can try reinstalling Excel. This can help resolve any potential issues with the Excel installation that may be affecting its performance.

Maybe this steps helps…what I hope :)

[#5]

Thanks for your recommendations and we have checked all the above points .
We have also tried with the latest Excel version but still facing same difference even the driver used for connecting the both SSAS and Kyvos cube is same . Executed same pivot with macro on both the cubes but observing different behavior.
Does it require in any change code to handle this difference and make behavior similar to SSAS if yes then pls suggest . What would be the appropriate changes.

Again coping same details for your quick reference :
There is significant difference in Excel Macro report execution time between the KYVOS and SSAS cubes. Further investigation revealed that there are some gaps in the request flow from Excel when communicating with the KYVOS cube, which are not present when interacting with the SSAS cube using same report with Marco. I also examined the response size and number of requests, but it did not explain the observed time difference.