MicroStrategy Report Optimization

In today’s digital world, information is the real power!  But the problem which is being faced by organizations is that they get buried under too much of information. Thus they face lots of difficulties in making sense of it for the utilization of it in order to realize their goals without any special tools. Business intelligence software helps all scale of businesses whether it is small, medium or large to take better decisions by accessing and properly analyzing big data.

Before going further it is important to define what is MicroStrategy. It is example of such an enterprise business intelligence application software vendor which is highly beneficial for business organization to optimize their performance. The key features of MicroStrategy platform are –

• Interactive dashboards in MicroStrategy
• Includes scorecards
• Highly formatted MicroStrategy reporting
• Ad hoc query
• Auto alerts
• Report distribution is automatic

As we know, the two key factors which decide the success of BI application software implementation are

• Data quality
• Performance

For better understanding we can compare MicroStrategy and Business Objects :

Each and every user always wants to quickly (in seconds) run the reports otherwise the interest in using the reports is lost. But generally people face challenges regarding performance of the reports while designing and developing MicroStrategy. In this post, we will be discussing about the solution which can be implemented to improve the performance.

There are some fundamental steps for optimization of Report such as –

1. In order to improve performance one can use reports catching in case we     don’t want to show any live data in our report. This can be enabled either at Project Level or Report Level.
2. We can use templates for caching purposes. For Eg one can create a template having the common set of attributes and it can be used as a shortcut in the report. Better performance can also be achieved by cached templates.
3. To increase performance we can also use Intelligent as they have the loaded data and the reports created from these cubes is capable of fetching data much faster.
4. VLDB properties can also be used in order to optimize report.

Further in order to enhance the performance and quality of the reports produced we can follow the fundamental paths.

Getting familiar with the symptoms

In order to optimize the report one must know about the flow of the execution of report generation in detail and it is as follow-

1. Request sending from user to the Microstrategy web.
2. Now from the computer system of the user, the request is transferred to the Intelligence Server. This server performs all the operations for generating the report.
3. This Server makes a search operation to try finding the report in the memory. If in case it finds the report in the memory then it sends the result of the report to the Micro strategy web.
4. If the Intelligence Server finds no such report in the memory then it pings the metadata to obtain the description of the report.
5. SQL optimization is  obtained by its machine with the help of the description obtained from the metadata.
6. The SQL data is run against the data warehouse by the Query machine. And this compiled data is then sending back to the Intelligence Server.
7. Now on the report, calculations are performed by the analytical machine.
8. The report is then collected by the Intelligence server and then transferred to the MicroStrategy web and this will finally send it to the user.

Understanding the cause of the low performances

Since as we can see that this is quite long, so only it can appear as obvious that it can take a longer time to give the report. But if we observe the SQL, we will find a large number of SQL passes on the fact table. And the fact table is also large enough which allows the process to be slow. If the table size becomes smaller in size, then the time taken by the passes can be made lesser. If we suppose that we have 100 passes and time taken per pass is 2 seconds then the total delay will be 200 seconds and if the time is made double, it will take 400 seconds. If the number of metrics used is reduced then also the time will be reduced simultaneously because it has been found that the number of metrics is directly proportional to the time.  The metrics were creating the fact table and with larger metrics fact table became larger which finally increased the number of those SQL passes and hence delay was increased.

The optimum solution for better outcome

The solution for this is to revolve the fact table by changing the rows into columns. As a result of this conversion, the number metrics will be reduced resulting in a view of a smaller fact table which finally allowed a fewer number of SQL passes to pass through it. Hence, the delay is reduced and the throughput will be increased. So using the same table with the same indexes will now allow you to get a Microstrategy report in a small amount of time. It took twenty seconds now to generate the complete record from the request till delivery which took five minutes in the prior process.