We have great experence in defining data processing problems. Ordinary goals by our customers are to identify:

- Causes of bad performance

- Main system bottlenecks

- Improvement of roadmap steps

In general our projects customers' IT departments try to identify BI system bottlenecks on their own,  but without defining a final baseline for the findings and conclusions.

As a result customes' business departments have no clear understanding of the reasons for low performance and no roadmap how to increase BI system performance dramatically (in some cases by factor 10). One typical reason may be that all tests show great dispersion and vary in wide range from test to test.

At last, users reach the point of starting the selection process for a new BI system.

Therefore, the main question we need to answer in our investigations is, whether it is possible to increase the performance of the current BI sastem. The answer to this question, at the end, will decide the destiny of the companies' present BI system.

We start our investigation with an analysis of the current status of the BI system: complicity of architecture, flexibility of tuning, possibility of implementation of changes, number of users, load regimes (day, months, year)… 

We identify technological risks regarding appropriate load levels, consequences of system growth, and the complexity of implementing BI system changes.

Main subjects of investigation:

- Fault tolerance: In case of failures the system must not interrupt the critical work processes. We investigate incident statistics (if available) or collect such information ourselves by analyzing the major problem areas.

- 24 by 7 availability: If BI systems work in different time zones, a given number of users will require 24 hours by 7 days a week system availability. How do these systems run during a day, a  month, a year? Ordinarily such information is a subject of continiuous measurement to identify load peaks. In general this analysis results in a “camel” picture – two peaks per day (see next example).

- Scalability and expandability: In what range may the BI system be scaled or expanded. How complex are new functions and/or report implementations. What effects have such changes on the BI system performance. If we identify bottle necks, will we able to fix the problem? Some systems need very complex workarounds to implement changes.

- Interoperability: How do other systems in the corporate IT architecture affect the BI system in a range of possible load levels. Which interfaces are critical for the BI system performance? Neighboruring  IT systems may influence the BI system under investigation. For a better understanding and clearer definition of problems, a stand alone BI complex is the dream of any investigator.

- IT base architecture: BI systems can be installed on corporate virtualized architecture or as stand alone IT architecture. In the first case virtualized architecture can affect the  BI system performance, in some cases drammatically. IT architecture may well increase the negative properties of a BI system. To find out we need to test the system in different modes. In these cases virtualized architecture may be a strong limitation.  

We have three main groups of tests in the common investigation phase:

- Operation by time investigation. This is to find out the time of the BI system's load peaks and how strong this peaks are.

 

- Opearation by access points investigation. From some access points performance may be better than from others. To identy of acess points with bad performanceis necessary to analyse the cause. It may be a problem of the network or of  information security devices.

- Dedicated process investigation. Dedicated operations can have a good performance while common processes take too much time. In some cases we have to analyse USER BEHAVIOUR MODEL to find the bottleneck of the BI system.

 

In our investigations, we focus on identifying the high priority bottlenecks and on generating a set of actions, which may demonstrate a quick win effect, so that users gain confidence that this system can also be used in future in this company.

With the measurement results during the project's first stage, we check the following working hypotheses:

1. Resource lack on database server, application server or on client machine

2. High SQL query resources consumption because of query structure

3. High SQL query resources consumption because of non-optimal DB structure (bad indexes configuration and state, transactions deadlocking, or other)

To check this set of hypotheses and to identify the reason of big dispersion of the previous test results we typically provide three sets of test:

1. Servers resources availability test. The task of this series of the test is to identify periodical load peaks, which ordinarily appear during one given period (day, month, year). Tests of this series are focused on average load resource consumption estimation.

2. Top 10 query by duration test and analysis. Within this series of tests, we analyze BI szstem SQL queries for a given time interval (10 min) and estimate how the load level will change depending on the number of concurrent users.

3. Report generation procedure investigation. This series of tests focuses on momentary load peaks which can appear when most complex analytical reports are generated. In case of lack of time, we provide only those tests of highest priority. It may welll be necessary to investigate the BI system performance problem in more detail later based on the bottlenecks identified in this investigation

 

CONCLUSIONS

The test series provided in our investigation and the subsequent analysis typically shows important results answering many questions stated at the beginning of the project.

In general we find a reasonable ways to drammatically improve the current BI system performance. 

So, please, be not in a hurry to throght away your old BI system. It may work well for your for another era.