Clinical Study Activity Per Capita

Study Activity Dashboard

In this post, we will try to understand the clinical study activities across the globe. We will gather some inputs like population, GDP and health spending as % of GDP. Then we compare different countries by their involvement in clinical studies. The clinical study activity is based on the clinical site in that country for a particular study. It is important to keep in mind that those clinical sites or facilities may or may not have enrolled any participants. Also, the demographics data is for the year 2017 and we are considering all the clinical studies registered in the USA clinicaltrials.gov as of Aug 2019. Keeping all that in mind, we will try to get a sense of overall study activity and compare them for different countries. We will also look at the study activity from region level. So, just sit back and relax.

Figure 1.1

Figure 1.1 shows study activity per 100 K population of a country. Denmark tops the list with the highest number of studies per 100 K population. This is not the complete list and I have tried to display maximum I could fit in a picture. You would notice that there are countries with very small population and hence they have got a high activity per capita. Also, there are few countries with very large populations and have got a low ratio.

In figure 1.2 above, the countries are categorized under geographic region. It also shows the percentage share of the population and number of studies. The dashboard allows to drill down on a region and see the details by country. 
We will look at the study activity based on the GDP and health spending as % of GDP of countries in next post.
Keep thinking till next time. 

Clinical Intelligence Analytics - Sponsor Trends

Sponsor Trends Dashboard

In the last post here, we gained some insights into top performing sponsors and overall trend. In this post, we will look further deep into how the sponsor participation has changed in last few years. The Sponsor Trends Dashboard (figure 6.1) can answer some interesting questions and see what's going on in the clinical trials industry.

Figure 6.1

Here, we will try to analyze the insights gained from Sponsor Trends Dashboard in Figure 6.1 above. 
1. What type of study Sponsors have the highest or lowest share in study registration, and how has that changed over the last few years?
The chart in Quadrant 2 tells us about the share of studies by different types of sponsors. Clearly, Universities have the highest share and has maintained a steep rise in the last few years. The share has increased from 40% in 2005 to 50% in last 2 years. These Universities could be public or private, funded or not-funded but we do not have that information available as of now. 
Hospitals have also performed well in registering the studies. The share of studies registered by Hospitals have almost doubled since 2005. On the other hand, the share of Industry sponsored studies has declined consistently and it has reduced to just 17% now which was 37% in 2008. The share does not indicate if the segment has really grown or declined. The share of one segment, Universities for example,  could rise because other segments have declined. To see the trend, read the next question below.

2. Which type of sponsors have shown growth in study activity?
The chart in Quadrant 1 shows how the registered studies by different categories or types of sponsors have grown over the period.
The studies registered by Universities have grown at a continuous and rapid pace. Until 2008, the Industry segment and Universities were overlapping but after that the Universities completely outpaced the Industry Sponsors. The growth in studies registered by Industry sponsors has remained almost flat but Hospitals maintained a steady growth until 2016 after which the growth is negligible. 

3. How has the total numbers of sponsors changed over the past few years?
In the above 2 charts in Quadrants 1 and 2, we looked at the trend of the studies registered by sponsors but now in this chart on quadrant 3, we will look at the trend of actual number of sponsors.
The overall trend shows that the total number of primary sponsors who registered their studies has grown consistently. All 3 important categories, Universities, Industry Sponsors and Hospitals have grown consistently. All 3 types of sponsors have increased almost 2 times of 2008 levels.

4. Has the participation of sponsors from industry has increased?
The chart in quadrant 4 shows the participation trend over the period. Industry participation means that either the study is sponsored by a sponsor from industry or at least one of the collaborators is an industry sponsor. 
The chart shows that the industry participation has declined and reduced to just 22% from upper 40s in 2008. Between 2005 and 2008, the share of studies having industry participation remained in upper 40s but started declining consistently after 2008. However, it is not clear from this chart if the studies with industry participation has decreased or the studies without industry participation has increased. To figure that out, let's take a look at the trend chart that shows the number of studies with or without industry participation over the period of time.

Figure 6.2

The trend in chart (figure 6.2) shows that the number of studies where there is industry participation has remained nearly constant but the studies without any industry participation has increased from 6K levels in 2005 to 24K in 2018, which is four times growth.
As a food for thought, how do you think clinical research industry can increase the collaboration for the larger benefit to the whole community?
Do post your questions and comments.
Keep thinking!

Source data extracted from: https://aact.ctti-clinicaltrials.org 

Clinical Intelligence Analytics - Primary Sponsor

Primary Sponsor Dashboard

Primary Sponsor dashboard (Figure 5.1) provides us insights into the study activities by primary or lead study sponsors. Primary sponsor is an important stakeholder in the clinical trials that has the primary responsibility of initiating, study design and study conduct. In simple terms, we can say that the primary sponsor is the owner of the clinical study. The sponsor can be an individual, a company or an institution, and they can be from industry (commercial) like pharmaceutical or Biotech companies or public non-industry (non-commercial) institutions like government or research institutions. 

 In this dashboard we will look at various aspects of study activities of sponsors.

Figure 5.1

1. How many sponsors have registered clinical trials in the US? How many of them are from the Industry (Commercial)? How many studies have they registered? For how many studies did the sponsors from commercial sector have posted the results for the studies?
The tiles on the top provides few sponsors related summary metrics to answer some of the questions mentioned above.
There are a total of 313,345 studies registered by 28,068 lead or primary sponsors in the US till date. Out of 28,068 sponsors, 8,717 sponsors are from commercial sector with 81,169 studies registered and the remaining 19,351 sponsors are from non-commercial sector with 232,176 studies registered. 
There are 4,545 sponsors with at least one study result posted and 1,680 of them are from Industry. 

2. What percentage of studies were registered by sponsors from industry as compared to the non-industry? 
About 26% of studies were sponsored from the industry or commercial sector. A small percentage 3.31% and 1.16% are contributed by NIH (National Institute of Health) and US government respectively. The raw data did not provide further classification into the non-industry sector. The data is transformed to figure out if the non-industry sponsor is a Hospital or University/Institute/School. The shows that Universities have been a major source of sponsored studies with almost 45% share and Hospitals having 9% share.

3. What's the growth in number of studies sponsored by study as compared to the non-industry sector over the last few years?
Except 2008 and 2014, the number of studies registered by Industry sponsors have largely remained stable around 5,500 mark. In contrast, the studies registered by non-industry sponsors have grown rapidly. Notice the size of the steps in the chart. Also notice the increasing gap between the two lines.

4. What percentage of registered studies have some participation from industry?
The pie chart named Industry Participation shows the share of studies where either the primary sponsor is from industry or at least one of of the collaborators is from industry. About 33% of the studies has some participation from industry.
 
5. Who are the top performing industry sponsors?
The tabular chart shows the primary sponsors from industry sector ranked based on the number of studies registered. The chart also display metrics like number of countries they have recruited patients, number of recruiting facilities, studies in completed state, studies where the recruitment has not yet started, studies that are currently recruiting and studies where results are posted. The success ratio compares the studies that were registered minus the studies that have not yet started or are in progress with the completed studies.
GlaxoSmithKline(GSK) is the top performer with 3351 studies and 91% success ratio followed by Pfizer and Novartis. Pfizer has recruited patients in 105 countries. Sanofi is another sponsor that recruited patients in 107 countries. 

6. Who are the top performing sponsors from non-commercial sector?
National Institute of Health Clinical Center, National Cancer Institute and M.D Anderson Cancer Institute are top 3 performers. The non-industry sponsors have recruited patients in fewer countries as compared to the top sponsors from commercial sectors.
The dashboards can answer many other questions by simply slicing and dicing the data by different dimensions.
If you get any questions in your mind that you want to share, please post them in comments and I will try to address them.
Till next time.  

Clinical Intelligence Analytics - Study Trends

Study Trends

Study Trends dashboard (Figure 4.1) gives us insights into the tends in recruiting country, average registration to enrollment duration and average study duration over the past 2 decades.

Figure 4.1

The data legends are shared between the charts on the first row. Similarly, the legend is same for the 2 charts on the bottom row.
The data is aggregated on a study level and a study is classified as an International (Both US and non-US), US-only and non-US only study based on the countries of patient recruitment. The analysis excludes studies that does not have any recruiting country information which could be for various reasons such as the recruitment might not have started or the study may not have any enrollments yet.
An international study means that it has recruited patients in the US and at least one other country. 
A non-US only study means that the study has recruited the patients in countries other than the US and no patient was enrolled in the US.
For the other 2 charts on the bottom row, the numbers with a minus sign are positive numbers but are shown as negative numbers just for the display purpose as they are on the opposite side. However, this is not intended. The vertical lines are the average lines to show the distance.

The dashboard can provide insights into the following trends:

1. What's the share of studies by recruiting country?
55% of the studies registered in the US have recruited the patients only from countries other than the US. The share of studies that recruited the patients only inside the US is 39%. International studies are just 6%.


2. What is the trend in the study registration by their location of recruitment?
Now that we know something about the share of the studies based on where the patients were recruited, let's take a look at how it has changed over the last 2 decades. The share of international studies has reduced to less than 5% now from 15% in early 2000's. The trend of US-only studies have also declined sharply from lower 90% in early 2000's to 30-35% level now. The trend of non-US studies have increased consistently in past 2 decades from 44% in year 2005 to 66% in 2019.  
Remember that the share of the studies registered in the past years can change based on the studies that are still recruiting or may recruit in future. Hence, the current trend is the snapshot.

3. How has the average time taken study registration to enrollment changed over the last few years?
The chart shows the trend in the time taken from study registration to patient enrollment or study initiation. The chart also compares side-by-side the duration for studies sponsored by industry or non-industry sponsors.
There are many studies that were registered retrospectively, meaning the studies were registered after they were already started (the first patient was already enrolled). Such retrospective studies were excluded from the analysis. Only for the prospective studies, the registration to enrollment duration is calculated in days.  
It appears that it usually took longer, sometimes 1.5-3 times, for non-industry sponsored studies to begin a study after they are submitted. The trend for non-industry sponsored studies is following a parabolic curve. The average registration to enrollment duration for the non-industry sponsored studies is 125 days which is higher than the overall industry average of 107 days. For the most part, the yearly trend is close to the average line except in year 2010 having an average of 145 days, which is also the highest in recent years.
On the other hand, the industry sponsored studies are initiated quickly and the study initiation duration has improved slightly overall. The average for industry sponsored studies is around 80 days which is well below the industry average of 107 days. The average for the last few years has been consistently near the average line.   

4. How has the average study duration changed over the last few years?
The non-industry sponsored studies takes longer to complete as compared to industry sponsored studies. The average study completion duration for non-industry sponsors is close to 3.5 years which is considerably much higher than the overall industry average of 2.6 years. On the other side, the average for industry sponsors is little above 2 years. The good news is that both type of sponsors have made a significant improvement is past 15 years to bring down the average completion duration to lower levels, possibly signalling great improvements in overall operational efficiency in study conduct.
With that positive note, see you till next time. 

Clinical Intelligence Analytics - Study

Study Dashboard

In study dashboard (Figure 3.1), we will look at certain aspects of study at aggregated level as well as at a study level. 

Figure 3.1

The study dashboard will try to answer following questions:

1. What is the average study completion duration for sponsors from Industry and non-Industry?

For all types of studies (All), the sponsors from Industry completed the studies in about 1.9 years. In comparison to that, sponsors from non-industry took almost 3 years to complete the study.

The observational studies (Obs) took longer to complete. The Industry sponsors with an average of 2.3 years performed fairly better than the non-industry sponsors with an average of 3.2 years.

For interventional type of studies (Int), the average study took 1.8 years for Industry sponsor as compared to 2.9 years for non-industry sponsors.

We may further want to look at the study duration by the phase of the study. Phase 3 studies are large scale and complex in nature and hence, it should take longer to complete when compared with phase 1 and phase 2 studies. Let's see what we find. Only interventional studies go through the drug development phases. If you take a look at Avg Study Duration by Phase chart, Phase 2 studies took longest among all the study phases with an average of 3.3 years. Phase 3 studies took an average of 2.9 years to complete where as phase 4 studies took 2.4 years. Early phase 1 studies took longer than the phase 1 studies.

2. What is the share of sponsors from industry and non-industry in interventional or observational studies?

Almost 80% of studies were interventional studies. 56% of 80% which 70% of total interventional studies were sponsored by non-industry sponsors. The industry sponsors have greater share in interventional studies as compared to its share in observational studies.

3. What percentage of studies were completed between 0 to 3 years or between 8 to 10 years?

40% of the studies were completed between 1 to 3 years. Around 29% studies were completed in less than 1 year. 

4. Which studies took the longest to complete?

There are 44 studies (0.02%) that took more than 30 years to complete. The study that took longest was sponsored by Johnson & Johnson to evaluate the efficacy of oral Levofloxacin in the treatment of chronic Bronchitis. This study took 63 years to complete starting in 1931 and completing in 1994 and has enrolled 367 patients. 

5. At study level, how many medical conditions a particular study is conducted?

See the tabular report to view the number of enrollment, medical conditions and the number of study sites and countries of subject recruitment.

6. In how many countries and facilities did a study recruited patients?

See the tabular report.

The dashboard will show the description of the selected study.

Clinical Intelligence Analytics - Trends by Study Attributes

In the last post, we looked at the growth trends of studies registered, initiated, completed and posted results over the period of last 20 years. We looked at yearly trends and then drilled down at quarterly and monthly trends and compared the growth in current year with the previous year.
In this post, we will see the growth trends of registered studies by study attributes like study type, study phase, Drug/Device and DMC flag over the period. I have filtered out the studies where the study attributes were not specified.

Figure 2.4

This dashboard (Figure 2.4) is an extension of growth trends:
1. Study Submission Trend by Study Type-
The chart shows the trend of studies submitted for interventional and observational types.
The share of interventional studies have decreased. In early 2000's, there were around 90-93% interventional studies and 7-10% observational studies. In last few years, the share of observational studies have increased to 21%.

2.  Study Submission Trend by FDA oversight-
The chart shows the trend of studies by the oversight of the FDA, if the study is for FDA regulated drug or a device. 
Until 2008, the share of the studies for FDA regulated devices was less than 10% which has increased to around 25% now.

3. Study Submission Trend by Study Phase-
 Phase 3 studies are important studies since sponsors apply for FDA approval after that. 
Study type NA are the studies that do not have a phase of a study, and I guess it is mainly for the device related studies. These studies share have increased significantly over the period and I think its because the device related studies share have increased as we observed in the previous chart.
There is a slight drop in the share of phase 1 studies. The share of Phase 2 studies in green has decreased from 43% in 2003 to 12% in 2018. Phase 3 studies have also followed the same trend with the share reducing to just 7% in 2018 from 25% in 2005. Phase 4 are post marketing studies and the share has reduced from 16% in 2005 to 7% in 2018.

4. Study Submission Trend by DMC-
The DMC flag tells if the study has a Data Monitoring Committee appointed or not. There were many studies that did not mention if they have DMC or not. As I mentioned in the beginning, the chart is the representation of only studies that has the data and others were filtered out.
The share of DMC appointed studies increased in the first few years and then start to drop until 2008 after which it picked up again a bit and maintaining the 40% level until 2013 but falling down a bit to 34% in 2018.
Feel free to share your thoughts.
See you soon in the next post.
  

Clinical Intelligence Analytics - Growth Trends

Trial Growth Trends Dashboard

Growth Trends Dashboard provides insights into the growth trends of clinical trials over a period of time. This helps us gauge how the industry is growing. 

Below (Figure 2.1) is a snapshot of the dashboard without any data filters. Figure 2.2 is another representation to view the Year over Year growth. Year 2019 is the current year and hence the decline should not be viewed as negative growth. We still have few months remaining in 2019.

Figure 2.1

Figure 2.2

The Dashboard has the same summary tiles on top of the dashboard except a new metric to measure average number of days from registration to study initiation or enrollment. The average for prospectively registered studies is 107 days, which means that it takes 107 days on an average for a study to enroll its first patient after it has been registered.

Broadly, there are 4 times that are important milestones from study registration to the posting of results. The trends in this dashboard shows these 4 important milestones. The Growth Trends Dashboard can answer some of the following questions:
1. How the study registration in the US has grown over the last 20 years?
Registering the study is the first step and hence, it helps us in understanding how the industry is growing. A decline in study registration would mean less successful drugs or devices reaching the market. With patented drugs losing patent protection, a declining product pipeline can negatively impact the growth of the industry. CRO(Clinical Research Organisations) conduct the clinical trials on behalf of the sponsors and hence a decline in study registration can put brakes on the growth of CRO industry.
The study registration trend is increasing consistently except in year 2005 and 2008 that saw a sudden jump. International Committee of Medical journal Editors(ICMJE) began requiring trial registration as a condition of publication in September 2005. This explains the jump in 2005. The jump in year 2008 could be related to the recession period.
Beyond 2008, there has been consistent growth sometimes in double digits. The outlook for the industry as a whole looks positive.

2. Has the growth in the studies initiated been consistent?
Sponsors take lot's of efforts in initiating a study. It's a commitment they make in terms of money and efforts and hence it is an important indicator to see the growth in study initiation. The average duration for a study to go from registration to initiation is 326 days.
The growth in number of studies initiated over the last 20 years is also consistent until 2016. The growth has declined in last 2 years around -6.5% YOY. Year 2019 may also end up following the declining trend. The rate of YOY growth has consistently gone down from 42% in 2002 to -6.5% in 2018. If the trend continues, the coming years may prove to be difficult for the industry and companies need to start looking at alternatives to increase the product pipeline.
3. Are studies being completed growing consistently?
Completing the study is like clearing a big hurdle in drug development. Conducting a study is lengthy, costly, complex and risky and hence a successful completion is a big milestone and a big relief to sponsors.
The growth in studies completed over the last 20 years has been positive and consistent until last year 2018 when the growth was negative. The rate of growth declined consistently after 2007 with a recovery in 2014. The rate of growth came down to 1% in 2017 and then dropped to -8.5% in 2018. The current year 2019 doesn't look to be making any recovery. With just few months remaining, 2019 is 64% away from last year 2018. 
4. Are sponsors submitting study results growing?
ClinicalTrials.gov launched the results database in September 2008 and hence sponsors started posting the results after that. 
The trend shows that the posted results increased at a very high rate until 2014 recovering 39% in 2017 but again declined 20% in 2018. Current year 2019 seems to be matching up but it is yet to be seen if it will end with a positive growth.
If you notice, I compared the current year 2019 with last year 2018 to get an understanding of the current year performance. I did get some fair idea but I wanted to go one level down and see the current year has performed on a quarterly and monthly basis as compared to the last year. I wanted to see if there is any trend where there is high activity in certain months, so I created a dashboard which can provide some insights into understanding the current year performance in Figure 2.3.

Figure 2.3

The four tabular reports on the top row compares the monthly comparisons of current year months with previous year months. The increase column is the difference and appear in green or red based on whether the change is positive or negative. The bar charts on the bottom row compares the same metrics on quarter basis. Remember, August is the current month and hence the third quarter will not have complete values.
Let's take a look at all 4 metrics one by one and make some observations.
1. Study Submissions:
Current year has performed well so far and the number of studies have maintained a positive growth when compared with the same period of last year. Observe that last August had the highest number of studies submitted and unless there is a trend to submit the studies in the last few days of the month, we can expect August to play fairly well but may not be able to exceed. The lead current year has maintained in the first 2 quarters may help to gain a positive growth this year in number of studies submitted.
2. Studies Initiated:
So far, not a single month with a positive growth and the current year months have unperformed with a large margin. There are no months in the remaining period with unusually high or low activity and so if the trend continued, we may see a large drop in the 2019 study initiations as compared to 2018. I will keep an eye on that in the coming months and share with the readers here.
3. Studies Completed:
The monthly and quarterly numbers looks disappointing similar to study initiation. December month has high numbers possibly due to year end activities so we can expect some improvement in filling in the gap but the overall outlook doesn't look promising. 2019 may end up with a significant drop as compared to 2018.
4. Study postings:
The first two quarters have performed well and the current quarter also looking good. Hopefully, 2019 would see some positive growth. 
With that positive note, see you till next time. 

Clinical Intelligence Analytics - Summary

Clinical Summary Dashboard

Summary Dashboard gives an overall picture of clinical trials in a summarized form at a high level. 

Below (Figure 1.1) is a picture of the dashboard without any data filters. If I make any selection or filter, I will call it out and mention it.

Figure 1.1

The Dashboard tries to answer some basic questions to start with, and then we start looking at things from different angles and dimensions. 

The summary Dashboard can answer some of the following questions:

1. How many studies have been registered so far in the US?

This the total number of studies registered in the database. As on 09-Aug-2019, there are 313,345 studies registered in the US.

2. How many studies did actually started?

A study is considered to be started when it enrolls its first patient. It's an important milestone in the entire clinical trial process. 291,364 out of 313,345 which is around 93% of the studies did actually start by enrolling a patient.

3. How many studies did actually completed? 

Another important milestone is when a study completes and the patients who participated in the study stops receiving the drug. Of the 291,364 studies that started, 167,511 studies were completed, which is 58%. 

4. For how many studies did the sponsors posted the results?

Once the study is completed, the data collected is analyzed and the results are posted. Results Database was initiated in 2008 whereas the study registration began in 2000, so there might be studies without posted results. 38,127 studies have results posted out of 167,511 completed studies which is 23%.    

5. How many studies are currently recruiting patients?

A study recruits patients as study facility location called study sites. These study locations could be in many countries. 54,968 studies are presently enrolling patients including the ones that are enrolling only by invitation. This means 19% studies of the 291,365 studies that were started are still recruiting patients. 

6. How many patients were recruited in the past?

There were few enrollment values such as 99999999 that were converted to 0. About 490 Million people participated in the clinical trials across the globe from 210 countries. As per the recent United Nations estimate, the would population is 7.7 billion. We can say that 6.4% of world population has participated in clinical trials of the US. Other countries may have their own clinical trials registry.

7. What is the average duration of study completion?

Duration is the difference in the start and completion date of the study. 2.62 years is the average duration for a study to complete. Phase 3 trials are large scale studies and takes longer than phase 1 and 2, but we will look at that later. We can say that it will take around 8 years for a new drug to complete all the phases of clinical trials before it can apply to FDA for approval. Add another year or 2 for pre-clinical testing of the drug on laboratory animals. Now I understand how lengthy, complex and risky is the entire process of drug development. I will not go into the drug pricing strategies but you got a sense why those drugs are so un-affordable, even with insurance sometimes.

8. How many total sponsors registered the studies?

28,068 sponsors from both industry and non-industry (government agencies etc) have registered studies. We will see the ratio and proportions later.

9. What share of registered studies were interventional or observational?

Almost 80% of the studies are interventional studies where some kind of therapy is given to the participant.

10. What percentage of registered studies were sponsored by industry and non-industry sponsor?

Non-Industry sponsors leads with 3/4th of studies sponsored by them.

11. Top 10 sponsors who registered the most studies?

GSK is the leader. Pfizer and Astrazeneca are other sponsors from pharmaceutical industry following with a close margin. National Cancer Institute, a non-industry sponsor, is at a second position. 

12. What are the top medical conditions for which the studies were registered?

Surprised to see obesity at number 2. Asthma and depression are in top 10. I never thought they were so important but I guess our lifestyle changes are responsible for their growth. Breast cancer is the most studied medical condition.

13. What is percentage share by the overall status of the study?

Only a small portion of the studies were withdrawn and suspended, however, 5.65% studies were terminated which could be a cause of concern.

14. What are the top 10 countries that recruited the patients?

US has recruited about 18% of the total participants. 

Taiwan at no 2 surprised me. I found it enrolled around 67 million participants for an observational study but Taiwan's total population is around 25 million. The enrollment number is an outlier to me, however, I did not change it. Cambodia too has a study that enrolled 15 million participants for an observational patient registry sponsored by non-industry sponsor French National Institute. 

Since observational studies enroll large population of participants, let's look at who are the top countries who recruited for interventional studies only (Figure 1.2). Almost 82 million participants have been enrolled so far and US once again leads the chart with a contribution of little over 13% and China closing the gap at second position.   

Figure 1.2

You can always slice and dice the analysis to look at things from different perspective in a dashboard. It's real fun to create your own questions in your mind and then try to find out the answers yourself.

See you till next time.

Qlikview is now a Leader

Hi,

As I was expecting, Qlikview joined the leaders quadrant in Gartner's magic quadrant for BI 2011.
Qlikview is cited as a self contained BI Platform and the strengths being interactive, great visualization and end user friendliness and satisfaction. I am very happy about it.
But I am more focused on seeing the challenges ahead. It will be interesting to see how Qlikview maintain that position and stand in the competition.
The challenges cited by Gartner are
1. Lack of expansive product strategy
2. Limited metadata management
3. Lack of broad (high volume) BI deployments
4. Lack of Microsoft office integration
5. Poor Performance when data volume and number of Users increases.

The findings are not new and Qliktech surely needs to seriously think about these shortcomings.
I want to discuss further on the above points in detail.

1. Lack of expansive product strategy : To compete with large vendors like Oracle, it becomes very important to have a competent product expansion strategy. Oracle has very aggressive product strategy and has a vision to integrate its various offerings like Oracle BI, Hyperion Essbase, Oracle Enterprise performance management and more importantly their pre built analytic models popularly known as BI Applications. Though Qliktech has already taken one step in this direction by targeting application vendors like Salesforce and can offer pre built models for Salesforce customers but this is not enough. Qliktech has to work agressively in developing such pre built models for other but big applications. EPM is one area which is still untouched and lack of vision in this area can be disastrous and will simply throw Qliktech out of competition. Vendors not only should now think of Softwares but also start thinking about offering Hardware configured for optimum and enhanced performance. Oracle has got its popular Oracle Exadata, its database pre configured with HP's hardware and is agressively promoting it.

2. Limited metadata management : Qlikview offers limited metadata management capabilities and the primary reason I see is because Qlikview is focussed on small scale or much smaller than average size deployments, it did not see much relevance of metadata management. This can be dangerous to them as well as their clients as when they grow, they will start seeing the need for it and would require the investment they tried to save at the beginning. Even if Qliktech decides to go for building its capabilities in metadata management, the basic problem for them will be to start believing in OLAP dimensional building which will be against their basic principles. Qliktech market its product as a non OLAP tool which actually is not and treat the underlying data as a cloud in the memory. Hence when it will see the need for conforming dimensions to do cross functional analysis, it may become a matter of choice rather than a matter of capability.

3. Lack of broad (high volume) BI deployments: For Qliktech as mentioned above and as cited in Gartner's report, the major challenge will be to deploy large scale applications. As of now they have proved their capabilities in small or much smaller than average scale deployments and I think that is what Qlikview was made for. One of the Qliktech's selling point is that Qlikview do not require a datawarehouse. Now this same selling point will stop them to move ahead or prove their capabilities in average and large scale deployments.
For those who want to know why, please read one of my earlier post here
This again will depend on reviewing its sales strategy and making corrections to their basic beliefs which is not going to be an easy task. If they do not start using the terms datawarehouse and OLAP, it will difficult to maintain the Leaders position.

4. Lack of Microsoft office integration: This is something I have mentioned in one of my post in Year 2008 read here. It seems Qliktech is least bothered. Its current capabilities are very basic in terms of simple export to MS Excel. In coming releases if it do not develop such capabilities, it will he hard for Gartner or Forrester to give a space to Qliktech in their reports and compare Qlikview with Oracle or IBM. There are many more such features which I have mentioned in my post earlier. Some of them which are important according to me are building connectors for their proprietary QVD and QVW files so that their models can be available to other applications, SQL generation queries to help developers in debugging etc.

5. Poor Performance when data volume and number of Users increases: This is again linked to point number 3 above.

Feel free to post your comments or thoughts.

Till next time

Manohar Rana

Enhance Business Intelligence Performance

Hi All,

In any Business Intelligence Implementation, the key factor is the performance. Performance factor always plays a key role in User accepting or liking the application.
We should do everything possible to enhance the performance and here are some tips some of which are very generic and can be used in any BI Implementation.
From a solution perspective:
1. Use of Datawarehouse: Though a datawarehouse is not compulsory for any BI Implementation, we cannot simply think about a BI solution without a datawarehouse because of the advantages it offers in terms of performance and simplicity. This is important for small implementations who sometimes neglect and underestimate the use of datawarehouse.
From a BI Tool Perspective:
1. For every tool it is important to reduce the size of the application by removing the unnecessary objects.
2. Try to create different database connections for different set of users based on the priority.
3. Try to create a seperate database connection for populating any session level variables.
4. Try to make the best use of system Cache. If the tool allows to cache the results of the queries, use it and if possible pre populate the queries which are very frequent and used mostly.
5. Minimise the calculations happening at the BI level by pre calculating them in datawarehouse.
From a database perspective:
1. The most important thing is to perform every possible calculation you can do in database. We very frequently neglect this saying this is a small thing and cal be calculated or performed at BI level. We should avoid this and if something is possible in ETL or database, do it here even it cost you adding a few extra columns or tables.
2. If you can create a perfect star models, nothing like that.
3. Try to use the database techniques like Partitioning and indexing to enhance the performance of database queries.

There may be several other tips and techniques which we can follow to improve performance and if you have any, please feel free to share.
Till next time.

Regards
Manohar Rana

Qlikview Personal Edition

Hi,

Qlikview is the emerging name in the Business Intelligence world and becoming very popular among the user community due to its easy to use interface, fast deployment and very strong visualisation.

Qliktech released its version 9 recently with a new Edition called Qlikview 9Personal Edition having all the developer features.

Earlier Qlikview use to provide only 15 days of evaluation period for people who want to give it a try which is really a very small duration to try or evaluate a Business Intelligence Tool.
This limits the user to use it beyond that period.

Now with version 9, Qliktech allows to download the personal edition from their website for free and use the product for personal use for an unlimited period of time and does not require any key.

So, that in real sense a very good news for the student community as well as for startup businesses or small growing businesses to use Qlikview for their personal use.

As small business generally has lot of data in flat files or excel files, Qlikview becomes a very handy Business Intelligence Software as Qlikview is very easy and effective when you have lot of excel files or combination of flat files, desktop database and relational databases.

Now you can develop or get developed Business Intelligence applications and do your analysis to know more about your business. You can export the reports/charts to excel,pdf,gif or png images and email it to your business partners or other members of your Business.
You can also copy/send the complete Qlikview document to another machine where Qlikview free personal edition is installed.

And in future as your business grow and you have the right budget, you can get your personal edition converted to a licenced version without affecting your Qlikview Business Intelligence Application that you had created.

But there are some limitations of Qliview personal edition. As your Qlikview is not a licensed one, you cannot ask for any support from Qliktech which I think is not a big issue. You can manage your application without any support which generally is required for big deployments.

Another limitation is that you cannot use an application developed in Licensed version in your personal edition however an application developed in personal edition can be used with a licenced version.
The application developed in a licenced version when opened in personal edition does not load the data hence a reload of the Qlikview application with the fresh data is required which effectively means that the person having the personal edition should have all the data(flat files or excel files) or access to relational databases to reload the application.

Another limitation is that you cannot publish your dashboards over the web to share it with other users as the personal edition does not provide the web component which allows the users to access the application over the web using Internet browsers.
However, you can use utilities like desktop sharing, remote login or web conferences to allow someone to use that application but in that case you will have to expose your complete application. The person accessing the application through remote login can delete the application or make any modifications.

These limitations may not be relevant to most of the Students or Business Owners to use Qlikview personal edition for learning Qlikview or Using for your analysis.

Replacing scattered excel files with one single source of information will be a great benefit in terms of managebility, reusability and productivity which will save lot of your time and efforts.
Top class visualisation in reports and dashboards and ease of perfoming analytic operations like drill down, Top N analysis, Pivoting,identifying problems etc will be an added advantage.

From Qliktech's perspective, they have made a very strategic move and will give them benefits in the coming years and will also help them in growing their prospects base and users base. This will definately make Qliktech's reputation much better in the market and help in developing Brand.

Qliktech's major install base or user base is small business or large business with small deployments and this move will make its presence more stronger in this area. Also, the buyers risk on investment will be greatly reduced.

Not many companies provide free unlimited period evaluation versions of their Busniess Intelligence softwares. I know only one name i.e Oracle. Even SAP Business Objects and IBM Cognos does not provide more than 30 days evaluation.

I am hopeful that Qliktech very soon will appear in Gartner's Business Intelligence Magic Quadrant and will give a direct competition to the bigger vendors.

Manohar Rana

Learn Qlikview

I have posted a tutorial for how to create quick dashboard in Qlikview at
Manohar Rana: Getting Started with Qlikview

You amy also like to read detailed comparison of Qlikview with others tools at

Qlikview Vs Others

You can also refer to the complete tutorial provided by Qliktech at

Qliktech Tutorial

Qlikview Vs Others

Hi,
I have worked on Qlikview version 7 and 8 for one year and now presently working on Oracle BIEE(Siebel Analytics) and Hyperion Essbase system 9. Have done dozens of POC and implementations and Qlikview has gained the same respect everywhere. Personally I just love this tool.

I have mentioned some of the important areas where Qlikview is lacking and you will notice that in the coming releases, Qliktech will try to touch all these areas showing some improvements.

I would like to share some pros and cons:
1. The biggest disadvantage is the amount or size of data. If you have data huge data in hundreds of GB, then qlikview may not be a good choice.Qlikview was having alimit of 2 Billion rows in a table to be loaded before the release of version 9 but now in version 9 it is not there and the size of data directly depends on system RAM. But even now I did not see a guide telling the RAM requirements for a specified amount of data and how does it increase as the amount of data size grows.
still there are some workarounds in certain cases. Design OLAP cubes or Data Marts in your database and keep the logic simple in Qlikview. Rather than creating a huge application, divide into smaller applications and use navigation. But if a single Datamart is huge, Qlikview is not for you.

2. Now speaking for small data sizes, I have observed that it is not the not the pill which cures a patient but it is the diagonosis of a doctor. Dont rapidly start making applications. They may be ready in few days but to manage them could be very difficult and time consuming. plan your needs, take some time to think on it and discuss. when plan is clear in your mind, start working on it.
3. For organisations having data growing rapidly, give a thought to create a datawarehouse with marts designed as per your needs. this will have two advantages. Your qlikview application would be simple and if tommarrow you plan to implement some other BI tool, this will be of great help.
4. few days back, I just heard about Qlikview providing alert capability. i am not sure if alerts can be sent to hand held devices and cell phones. Also check if these alerts can be designed by business users using some wizard or interface. If alerts can be created using API programming, it will really not be useful. I have seen a wizard based alerts available in Qlikview to define an event and sending the content via email.

5. Proprietary files -Another major disadvantage with all the tools storing data in their proprietary files like Qlikview, Essbase and Cognos. Tomorrow if you plan to have another tool for some reporting or if you plan to have another application talk to these cubes(Qliktech may not like to refer its files as cubes which I beleive is nothing else), then there could be a seroius problem. Major vendors generally provides connectors for major Multidimensional sources like Essbase and Cognos but for tools like Qlikview, it Could be difficult.

some of the features where Qlikview lacks are:
1. Alerts- Capability to create alerts and delivers it to not only Email but blackberries, hand held devices, mobile phones etc. In version 9, I heard something for this has come. Also Qlikview do not have any integration with BPEL.
2. Multi user development environment- This feature allows multiple developers work on a single project and the utility synchronises the peices of project each developer is working with the main project.Qlikview completely lacks this feature and until version 9, I did not hear anything about this feature coming up.
3. Connect and extract data from multi dimentional objects- I guess for SAP BW, the connector is available but not for Hyperion Essbase, Cognos Cubes and Microsoft cubes.
4. Export data or metadata to XML- The reports cannot be exported to XML format which is one of the major disadvantage. We can use the XML reports as a data source with another softwares which understands XML.
5. XBRL- Qlikview does not support XBRL. Those who do not understand what is XBRL and why it is required and useful, please read this http://www.xbrl.org/WhatIsXBRL/
6. Seperate component for operational reporting or production reporting or Popularly known as Pixel perfect reporting (for example Printing Salary Cheques) and report bursting like BI publisher(previously XML publisher) or SQR reporting in Hyperion Essbase. But we can argue here in the sense that Qlikview is an analytical tool and is not made for such needs but definately it restricts you somewhere and do not provide you with an option.
7. Integration with Microsoft Office tools- Qlikview just exports the data into an excel file or exports a report object to a png file or using OCX you can do something to make it work with MS office tools(I never tried this). It nowhere stands in comparison with Hyperion Add-In or Cognos Add-In. This is one area which is very important from business users point of view and developing this feature may not be that easy for Qliktech.
8. Support for advance features like embedded browser(available in hyperion Interactive reporting), flickers(rolling messeges) etc as an standard options.
9. Metadata Management- There is no concept of Physical model, Business Model and Presentation Model. There are no subject areas. The End users had access to all the tables loaded to the Qlikview and the matter becomes difficult when you have large number of tables involved. For end users to create a report themselves, they need to identify the tables and columns from this long list which can make things difficult for them. The solution to this could be to create a seperate Qlikview document for every subject area. This greatly effects managebility and reusability. Those who have worked with Oracle BIEE BI Administrator or Cognos Framework manager or Business Objects Universe will understand the importance of subject areas or Packages.
10. Impact Analysis- If you want to make a change in the Qlikview script or datasource and want to know the impact of this change on report objects beforehand, you could be in problem and may have to check each and every report manually and document it.
11. Alternate Hirarchies- No concept of Alternate Hirarchies.
12. SQL Generation- If you want to see the SQL generated behind a query, you can not see that in Qlikview whereas other tools like OBIEE, Cognos, BO and Essbase allows you to see the SQL/MDX generated for a query. This feature may not be relevant to business users but for developers this helps a lot in their day to day activities like debugging or data validation.
13. Resolving circular loops and traps: The only way to resolve circular loopes in Qlikview is to create an alias table wheare as in BO and Cognos you can define contexts which allows you to resolve the circular loops without creating any alias table. When it come to resolving traps like Chasm and Fan traps, Qlikview is helpless.
some more issues -
1. Security- Qlikview does not have a graphical interface to create roles, users and privilages assigning to roles. Also can users be allowed to log in on certain weekdays only. Also can users change their passwords themselves using some interface. Does the password expires automatically after certain no of days and user will be asked to change it.
2. I am not sure if Qlikview can use user directory for external authentication like LDAP database or NTLM. Also can it be used with existing SSO(Single Sign On).
3. Does Qlikview has feature to automatically end the session if it is not being used to avoid any session hacking.
4. Is there any graphical interface to monitor the sessions in use. Check the queries they have fired, previous requests, kill any current requests, monitor time etc.

There are some good features in Qlikview as well:
1. The ability to consolidate the data coming from various sources(databases and flat files) which is in a common format in the data cloud(Qliktech uses this term). this is fantastic. In other OLAP tools sometimes it becomes difficult to join and RDBMS and a flat file data.

2. Many people criticise Qlikview for its associative logic which says the feild names with same names are joined automatically but the designer has the liberty to remove or edit those joins. Even Siebel analytics makes the joins automatically in BI Administrator and Hyperion Essbase sensing same field names.
It helps the designer to see the qualified members for joins and make amendments after verifying them.


Another post regarding Gartners BI Magic Quadrant 2009.
Any comments on this.