Why build a Data warehouse?

Why build a Data warehouse?

Often when we talk about implementing BI, the first thing we need to look at is how and where is the data? Is it scattered? Is it easy to report on? With this we often need to know if we need to build a reporting database or a data mart or for that matter a data warehouse. We all know WHAT a data warehouse essentially is; here we are going to discuss the WHY.

Consolidate/Integrate Data:

Usually the data in an enterprise is scattered across the various applications and data sources that form the enterprise’s ecosystem. To aid better insights and business decisions, the business users need data from all the various subject areas to be accessible. DWH helps us to consolidate and integrate data from various sources and store in a single data model.


Cleanse data & Enhance data Quality:

DWH includes converting data from different sources into common formats, using common keys, structure, decimal/time formats. We can restructure the data and rename tables and fields so if makes more sense to the users. We can also use master data management to consolidate common data from various sources and build relationships.


Store Historical Data:

DWH is usually used to store huge amounts of historical data that the transactional systems do not /cannot retain. This helps analytics across different time periods, trend analysis.


Speed up Data Retrieval:

In a DWH, the data is structured in such a way that aids faster retrieval. The structure is designed for faster data access, drill down, filtering, and summarization. The transactional systems usually optimized for smaller transactions that inserts/update data in specific tables and access small amount of data. However, BI requires analyzing, aggregating a large amount of data quickly.DWH stores data to help such retrievals.


Unburden transactional Systems:

Executing resource intensive BI queries that access huge amount of data on transactional systems may affect these systems. It may even bring them down affecting business. Building a DWH separate from the transactional systems solves this problem.


Prepare / Pre-aggregate data:

Depending on the business needs, data can be pre-aggregated at various levels and stored in the DWH. This can save time and improve performance of the BI reports. The KPIs that help the business users can also be pre-calculated and stored. Users can then analyse these indicators across dimensions to gain knowledge and insights.


Support Self Service BI:

The DWH either uses dimensional model or easily feeds dimensional models, which can be used to build analytical cubes. These cubes can be used by business users to do Adhoc reporting, slice-n-dice the data and build their own reports.


Increased findability for Business users:

In a DWH, the data is stored in ways that is more meaningful to the business users. Also, the data relations can be easily established. The table names, field names are business user friendly. These make it easier for users to find the data they were looking for.


Helps Data mining / trend analysis / Predictive Analytics:

Once a DWH is created, it makes easier to analyse huge amounts of data and associations to uncover hidden patterns using data mining methodologies. The historical data in DWH can we analysed across time periods to discover trends / perform business predictions.
These are some major reasons / benefits of building a DWH. Though building DWH is a time consuming and complex process, it solves many problems and delivers effective BI.

Shraddha Tambe | Helical IT Solutions

D3 DayHour Charts integration with Jaspersoft

This blog talks about D3 DayHour Charts integration with Jaspersoft

All the reports are develop using ireport 5.5 professional and jasper server 5.5

As html component of jasper server does not load any scripts in the html component, we loaded the script in one of the decorator page (jsp page). The page is located at the location:


In the page we included the scripts which we want to load. We added the following code in the jsp page at line no 46:

<script type=”text/javascript” language=”JavaScript” src=”${pageContext.request.contextPath}/scripts/d3.v3.min.js”></script>

The script to be added should be kept at location:


Meaning of DayHour Chart:-

This chart represents functioning of any particular thing during different hour of different days. These graph can be used to view variations in different situations.

DayHour Jaspersoft Integration

Integration with JasperServer:

The data which we use for developing the calendar view can be fetched from any database. The data fetched from database is stored in a variable and is then accessed in the html component using the same variable. Applying this of process makes the report dynamic instead of static. Few parameters can also be added in the report which can be used in query and/or html component.

Generally for these type of charts we pass a variable which contains required data containing date, hour and a value associated with that particular date and hour. The string is created according to JSON format, so that when accessed in script tag, can be easily converted to JSON object using eval function.

Any variable/parameter can be accessed as shown below:

“<script> var arr =”+$V{variable1}+” </script>”

Parameter in query:

Select * from table_name

where date between $P{parameter1}  and $P{parameter2}


The sample code of static chart can be found at:


The steps on how to integrate it with jasperserver was discussed in my previous blog(D3 Integrating with Jasperserver).



Avi Dawra

Helical IT Solutions

SCD Type 1 Implementation on Pentaho Data Integrator

This blog will talk about SCD Type 1 Implementation on Pentaho Data Integrator

Slowly Changing Dimension Type 1 does not preserve any historical versions of the data .
This methodology overwrites old data with new data, and therefore stores only the most current information. In this article let’s discuss the step by step implementation of SCD Type 1 using Pentaho.
The number of records we store in SCD Type 1 does not increase exponentially as this methodology overwrites old data with new data
Create table in Database for source and target and Create connections for database.

Table Input Step:

SCD Penthao

Drag and Drop the table input in spoon workspace and give the connection, then click on get select statement.

Then Click on ok.

Database Lookup:-

Drag And Drop The Database Lookup. Double Click on Database Lookup And Set The Following properties  and then click on ok.:-

SCD Penthao 2

Filter Rows:-

Set The following properties  of filter rows and click on ok.

SCD Penthao 3

Select Values:-

Before you work on select values, connect the table output and specify all the database field on to the output step.

SCD Penthao 4

Table Output :-

SCD Penthao 5


Set The Following On Update  and click on ok.

SCD Penthao 6

Program should Like the Following Figure :-

SCD Penthao 7


For any other query related to Pentaho Data Integrator, get in touch with us at Helical IT Solutions

Bidyut Kumar

Helical IT Solutions

Jaspersoft Report Scheduler

This blog will talk about how to use Jaspersoft Report Scheduler, its configuration etc.

1.       Report Scheduler  :  It is an automated and iterative platform to email reports to anyone in PDF,CSV,Excel,….etc format.


2.       To  send email , it should be connected to outbound email server,  so for this it should be configured accordingly

a.       Go  to this  path : C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\WEB-INF\js.quartz.properties


You will find the js.quartz.properties file like below screenshot:

Jaspersoft Report Scheduler 1

b.Change the yellow mark values to your required input

c.Restart the Jasper Server


3. Now Go to Report Wizard and scheduled your required job or report :


Below mentioned are the steps to be followed for doing the same:

Jaspersoft Report Scheduler 2

b. After this go to “create schedule “ tab , you will find like this:

Jaspersoft Report Scheduler 3

Here you can set how frequently you want to schedule this report and for what timezone

c. Then go for the “output options” tab in this window only[in the 2nd right] Here you have to specify in what format you want to send the report

Jaspersoft Report Scheduler 4d. Then go for “notification” in this window. To whom all we want to send the reports, the same is to be specified in this window

Jaspersoft Report Scheduler 5

Now You have successfully scheduled the reports…!!!

4. If we want to stop the scheduler, then go to this location


Jaspersoft Report Scheduler 6

Remove the init-method=”start”

Restart the Jasperserver



Helical IT Solutions

Embedding BI Reports (Jasper Reports/ Jasper Server/ Pentaho Reports/ Pentaho Server)

There are many methods of embedding BI reports, this blog tries to explain the meaning of Embedded BI, different approaches which can be used, their respective advantages and disadvantages


A)     What is embedded BI?

Lets say you have developed your entire BI solution which includes reports, dashboards, mashups, analysis etc, the platform which is used can be anything like Jaspersoft or Pentaho. The process of integration of this BI solution with your existing application/software/portal is called Embedded BI.

Embedded BI provides a lot of advantages like ability to add more reports over and above the existing canned reports which your application might provide, the look and feel and customization of all the reports can be changed as per the software/user preference, better visualization, unified application for everything. Biggest advantage can be your product value will increase much more, hence resulting in much more sales and revenue.

a) Embedding using iFrame Method :-

iFrame method of embedding BI reports can be used for embedding Jasper server, Jasper reports and Pentaho server, Pentaho reports. The report pulled in this case is using iframe as a tag. Even though the report is external, but to an end customer he will not be able to recognize the same.

The advantages of using iFrame method is this method is fast and quick to implement. The disadvantages includes it will be difficult to manage user, also in this case there can be cross browser compatibility issues, security is a major issue here.


b) Embedding Using webservice:- Webservices can also be used to integrate reports, dashboard, interactive report or/and ad-hoc reports. We can use webservices to accept the information from the Jasper/Pentaho server, this Jasper server can be anywhere ( on Tomcat or any other server). Using webservices we will invoke the response from Jasper Server/Pentaho server. This response can be can be then embedded inside application/software. Jasper server/Pentaho server also supports RESTFUL service (JSON format responses). In this approach there are no or less security related issues.

The disadvantage of this approach is there are no interactive features available of the report which thus results in poor user experience. Also in order to handle and understand the responses from jasper server/Pentaho server, custom coding is required in the application. In case if we had put input controls in the report, the same functionality then needs to be implemented in the application via custom coding. Aside, having many webcalls at times can be resource intensive as well.

The advantage of this approach is webservices approach gives more seamless integration since there is no security issue and no thirdparty calls (like in the case of iframe). Also in this case there are no interbrowser compatibility issues (assuming it is handled by your developer team).

c) Embedding using Jasper/Pentaho Libraries:- In this method we create JRXML/Pentaho report (prpt) files using ireport/Pentaho report designer. Compile these reporting using Java API, and then use this compiled code to generate views using Jasper Server API calls

The advantage of this method is this gives the most seamless integration. Also since all the information is present as a part of the application/server, no third party calls are required.

Disadvantage of this approach is some interactivity is lost in this method. Also the input parameters are also required to be created in the own application via custom coding.


Please get in touch at nikhilesh@helicaltech.com for much more details about the respective approach, how to embed the same using these approaches, which was is the best suited to your needs and much more.

Tech Tips in Penaho Server – publishing password setting for server – Disable the folder names in Browser panel in the Server

Hi guys..

The below tips are very useful in Pentaho Server for BI developers.
1) publishing password setting for server
You need to publish the reports or schema(s)[developed in PSW] once you done with developing. For this you need set the publishing password for Pentaho server.
Go to this location 
<location of Pentaho>biserver-ce-4.8.0-stablebiserver-cepentaho-solutionssystempublisher-config.xml and edit the “publisher-config.xml”  file.

Give your publish password b/w  these tags  <publisher-password> </publisher-password>

<publisher-password>password</publisher-password>    ( password can be any name that you wish to give)

Snapshot is a worth more than text. find the image below.

Save the file. stop the server and start the server.

2) Disable the folder names in Browser panel of Pentaho Server
* What ever the folder you create in Browser panel of pentaho server, it directly stores   under pentaho-soulutions
i.,e in this location
<Pentaho location>Pentahobiserver-ce-4.8.0-stablebiserver-cepentaho-solutions
* You can disable all of your folders that comes with installation & the later created ones  which you don’t want to appear in the browser panel.
*   Inside every folder(pentaho default or the folders created by you) you can find “index.xml” file. You need to edit this file. You need to change visable tag as “false”.
Find the sanpshot.

You need not restart the server. Just you need to refresh the browser panel in the server.
You can observer the invisibility of the folder.

Sadakar Pochampalli.



Installing Jasper server with MySQL Database

This blog will help you understand how to install Jasper Server with MySQL database

Pre requisites
1. Install the Oracle/Sun Java JDK 1.6 or 1.7. Create and set the JAVA_HOME system
environment variable. i.e., Make sure Java path are set for your user profile or system
Please follow the link to get the software.

2. Jasperreports-server-cp-5.0.0-bin-zip
a. Download Jasperreports-server-cp-5.0.0-bin-zip from the below mentioned location.

3. MySQL database.
a. Please follow this link to get the software.
Note: We can use any other database as well. For demo purpose we are using mySQL.

4. Apache Tomcat Server
a. Please follow this link to get the software.

Follow below mentioned steps to install Jasper server CE 5.0.0 with mySQL database.

Step 1:- Extract all files from jasperreports-server-cp-5.0.0-bin.zip. Choose a destination, such as C:Jaspersoft on Windows. The directory, jasperreports-server-cp-5.0.0-bin, appears in the file location you choose.

Step 2 :- Copy the <database>_master.properties file for your database from sample_conf and paste it to buildomatic:
Copy from — <js-install>/buildomatic/sample_conf/
Paste to — <js-install>/buildomatic
For example, copy mysql_master.properties to <js-install>/buildomatic.

Step 3:- Rename the file you copied to default_master.properties.

Step 4:- Edit the default_master.properties file to add the settings for your database and application server.

Below mentioned is a sample property values MySql database.

MySQL appServerType=tomcat6 [tomcat7, tomcat5, jboss, jboss7, glassfish2,
glassfish3, skipAppServerCheck*]
appServerDir=c:\Program Files\Apache Software Foundation\Tomcat 6†


Set the following in the file
webAppNameCE = jasperserver

Place mysql-connector-java-5.1.10.jar driver in the following location
D:install SWjasperreports-server-cp-5.0.0-binbuildomaticconf_sourcedbmysqljdbc

Step 6:-
Run the js-install scripts.
a. Start your database server.
b. Stop your application server.
c. Open Command Prompt as Administrator on Windows.
d. Run the appropriate commands:

js-install-ce.bat : Installs JasperReports Server, sample data,
and sample databases (foodmart and sugarcrm)

js-install-ce.bat minimal : Installs JasperReports Server, but does not
install sample data and sample databases

If you installed the optional sample databases, complete the installation by executing this command: js-ant deploy-webapp-ce from buildomatic location in command prompt.

For example in the following location:
C:Program FilesApache Software FoundationTomcat 6.0webapps

Step 7:-
Run the tomcat server and type the jasper server url.
For example: http://localhost:2222/jasperserver/login.html

Helical IT Solutions (an Open source DWBI company providing services on Jaspersoft, Pentaho, Talend, Kettle, Data visualization) covered by a growing magazine.. Business for All

Helical Covered by Biz for All Magazine

Helical Covered by Biz for All Magazine



Among the many Information Technology startups, Helical IT Solutions, a Hyderabad based technology company providing services in open source data warehousing and business intelligence services, has been moving up the ladder with incredible pace and bagged a seed funding of INR 2.5 million from the Singapore based Citibank within six months of starting operations.


The company which started in Nov. 2012 has catered to the technology needs of clients in various domains including e-commerce, insurance, energy, social media analytics, healthcare and human resource while building and managing their Business Intelligence (BI) platforms.


The eight member strong team in Helical is quite excited with the fund inflow and plans to use it primarily for recruitment and partnering with leading BI vendors in the open source space such as Jaspersoft and Pentaho. “We want to focus and build up our capability of providing sector specific BI offering with this fund,” one of founders Nikhilesh Tiwari said. Specializing in Open source technologies, Helical has proved to be a great bet for its clients cutting down on their costs and adding to their efficiency and turnover. Their services include BI, ETL, Data warehousing and data modelling, data visualization to name a few. Helical IT Solutions is an open source Data warehousing and BI company based out of Hyderabad. The company works primarily on Jaspersoft and Pentaho BI suite and on talend, on Talend and Kettle. According to Gartner’s 2012 CIO survey, analytics and BI is the No. 1 technology priority for CIOs in 2012.



BI, analytics and PM software market was the second-fastest growing sector in the overall worldwide enterprise software market in 2011. “India is growing faster than any other country in BI sector in Asia Pacific with 36 per cent growth rate and the market for BI software in India is forecast to reach revenue of $81.5 million in 2012,” he said. Every 15 seconds, an open source BI s/w is downloaded and around 80 per cent of them need consultation and services as to how use them. With the angel investment, plans are underway to expand the team and hiring, develop infrastructure, SI partnership with Jaspersoft & Pentaho to get projects from even bigger clients and at a higher margin. “Building a parallel team after some time and start working towards building a product (we have energy domain, ecommerce domain primarily in mind),” he said about future plans. It is not that the company had walked a rosy path as due to lack of resources, they had to turn down many clients like Jabong.com, Mangaquest, Inspire edge IT solutions,  XiBoss.