Creating a project in SpagoBI Studio

Creating a project in SpagoBI Studio

SpagoBI Studio is an eclipse based BIRT reporting software and creating a project in SpagoBI studio is very much easy and recommended too as all our reports / documents will be organized in better way

Let’s start creating a project in SpagoBI Studio:

  1. Start SpagoBI studio
  2. Click File > New and select Project.
  3. Search for Report Project under (Business Intelligence and Reporting Tools).
  4. Assign Spago Tutorial
  5. Now, you should be able to see SpagoBI Tutorial under Project Explorer.
  6. Click Yes, to open Report Design Perspective.

Project Creation

Thanks & Regards
Venkatanaveen Dasari™

Creating a folder under Functionalities Tree in SpagoBI Server

Creating a folder under Functionalities Tree in SpagoBI Server

In general Spago BI uses its own functionalities tree which allows to better organize documents / reports by grouping them in folders. Although in built Folders are provided to organize (Analytical documents, Custom Documents and Audit & monitoring under Functional Tree) but most of the cases we like to maintain our reports to be placed under different folder.

Based on Role-Based permissions these folders are visible to user. This structure can be created or modified only by the administrator.

Let’s start creating a folder under Functionalities Tree:

  1. Login as biadmin.
  2. From left menu, Select Functionalities Management under Profile tab.
  3. By clicking on a node of the functionalities Tree a set of possible actions is shown:
  4. While clicking on Functionalities insert option fill in Label, Name & Description (option) on Functionalities Detail page.
    • Insert
    • Detail
    • Erase
    • Move up/down
  5. Assign roles as preferred and Save the page

Work Flow

Thanks & Regards
Venkatanaveen Dasari™

How to Replace Various Unwanted Characters during Reporting Using Jasper Studio

How to Replace Various Unwanted Characters during Reporting Using Jasper Studio

Reporting in Business Intelligence is the representation of Data in a more meaningful manner
so at times we need to follow some few formats to make our data to the End-User or Clients more meaningful to their Business

Now in some cases
characters like

commas (,), asterisk (*) and periods (.) are not permitted
and permitted characters can be Ampersand (&) , hyphens (-)

so what do we do here?

since jasper has various text functions one in particular that can be used here will be

the replaceAll() method

so this can be done on the particular columns you want make the required changes.

Now Let’s go to the field column you want and edit the Expression.

In my case:
I would be editing the First Name Column to the required format
so now we use the replaceAll() method

My requirement is to remove all commas and replace it with a [space]
so a simple

$F{firstname}.replaceAll(“,”,” “).trim()

can be used
Note: if you are wondering and new to Jasper why i used trim(), I used a trim() here because at times a name can be in the form

Helical,IT Solutions,

Now with the $F{firstname}.replaceAll(“,”,” “) it gives us the result Helical IT Solutions [space] this hereby create an extra space at the End so
a trim() method is used to remove unwanted spaces at the End.

OK now you have an idea on how to replace unwanted characters with the required characters
Now go ahead and replace * with –

and . with [space]

Did you get and Error?

Indeed yes you will get an Error if you
wrote your expression as

 $F{firstname}.replaceAll(“,”,” “).replaceAll(“*”,” “).replaceAll(“.”,” “).trim()

and if you didn’t well skip this part 😉

ok now if “.” is kept it actually treats it as a . and not as “.” the character same goes with the “*”

so the appropiate expression would be

$F{firstname}.replaceAll(“,”,” “).replaceAll(“[*]”,” “).replaceAll(“[.]”,” “).trim()


well there are various way also to express the . as character and [.] is one of them
and now you are good to go

– Izebhijie Sohail Ehizogie

Note: Quotations are in the form of quotation open and quotation close

Introduction To HBASE

Introduction To HBASE

HBASE is the database which stores huge amount of data and we can retrieve data in a random manner.

HBASE is distributed column-oriented database which is built on top of the haddop file system.Tables in it are sorted by row.

Column-oriented databses : Databases that store data tables as sections of columns of data , rather than row of data . It is suitable for Online Analytical Processing.

HBASE is a part of Hadoop eco system.

HBASE internally uses Hash tables and provides random access , and it stores the data in indexed HDFS files for faster lookups.

HBASE is schema – less. It doesn’t have the concept of fixed column schema.


1. Table is a collection of rows

2. Row is a collection of column – families.

3. Column family is a collection of columns

4. Column is a collection of key-value pairs.

HBASE is used whenever there is a need to write heavy applications.

HBASE is used whenever we need to provide random access to available data.


Rupam Bhardwaj

EDI – Electronic Data Interchange

Standardisation is needed in every field to create uniformity. Same is the case with data exchange,
Without a standard format, each company would send documents using its company-specific format and, much as an English-speaking person probably doesn’t understand Japanese, the receiver’s computer system doesn’t understand the company-specific format of the sender’s format .
A standard format decides in which format, with what indentation and language the data should be written and represented for universal understanding of the information as data. For data to interchange electronically it should be in format which is undstandable by the electronic devices.The electronic exchange of data adds speed, clarity and reduced errors in the data. These advantages of electronic data exhange has popularised the use of EDI in the buisness field to exchange data between buisness partners. EDI process had no paper no people iinvolved in its transmission and reception.

What is there in EDI document ?
An EDI document is comprised of data elements, segments and envelopes that are formatted according to the rules of a particular EDI standard.
Only thing to be kept in mind while creating EDI is “Adhere to strict formatting as instructed”
and please do not add you own formattings.These rules define exactly where and how each piece of information in the document will be found. That way, when the EDI translator on the receiving computer reads an incoming EDI purchase order, it will immediately understand where to find the buyer’s company name, the purchase order number, the items being ordered, the price for each item, etc. Then, that data will be fed into the receiver’s order entry system in the proper internal format without requiring any manual order entry.

Data elements:The piece of information in your EDI document is nothing but data elements.
Elements may differ from one another in their data types, as it can be numeric, text data or dates. Next after defining the data type there comes its size, ‘the data length’ . Next the code values must be considered if required, e.g if the currency is in your dollar you need to use the related function.

Segment: Elements together make segment.
For each type of business document, the EDI standard documentation defines:
1] The segments that may be included and which ones are mandatory, optional and/or conditional
(i.e. must be included only if another segment or element is included)
2] For each segment, the elements that may be included – for every piece of information in a paper
document there is a corresponding EDI element. These elements are defined in the standards
dictionary and each standard has its own dictionary
3] The required sequence of the segments and elements
4] How many times a segment may be repeated

After collecting all data in prescribed format , they form a complete electronic document. Next for transmission these segments should be put in envelopes.

Envelopes:There are three envelopes message envelope, group envelope and interchange envelope. EDI documents are exchanged using several envelopes. All transaction data are put in group envelope and exchanged via interchange envelope.


EDI is the most efficient way of data transmisssion with almost zero errors. There are ways to create EDI through data extraction and modification and putting it in the prescribed format for the transmission between several buisness parties and between machines as well. EDI is having lots of scope in the present as well as coming days. In next examples I’ll come up with EDI creating instructions and guides.


Beginners Guide in Understanding Different Types of Joins


When we decide that one table won’t be enough for getting our desired output then we try to attach one more table to the existing table is known as JOINS. Now Inner Joins in other words is doing the same thing as the definition of joins goes but with an additional advantage which is based on a particular key column from table 1 and similar key column in table 2 then if the condition exist then it clubs that table and solely ignores rows that does not satisfy the condition

Now practically [using PGAdmin]



As we defined joins this is also similar but with Left Joins it clubs the 1st table and 2nd Table based on the condition and also rows that does not satisfy the condition in the 2nd Table

Similarly it we can say for Right Joins but in vice versa manner


Real Time

person_disability table has 25 Rows and a column with personid
and person_employment has 19558 rows and also column with personid

Now lets see when we say


It returned 12663 after adding few filters why?
Because Based on the 1st Table(Left Table) the condition will execute.

I.e. the table person_employment total rows will execute
and only does rows in the person_disability table that satisfy the condition will show and also NULL rows will also show (because those rows does not satisfy the condition).


Now if we Right Join the Same table let’s see what happens?


Now it returned 25 rows after adding few filters why?

Because based on the 2nd Table (Right Table) the condition will execute.

I.e. the person_disability table total rows will execute
and only does rows in the person_employment table that satisfy the condition will show and also NULL rows will also show (because those rows does not satisfy the condition).

We can also attain a RIGHT JOIN Using a Left join by swapping the names of the table


In My next Blog we will discuss on CROSS JOIN

Business Intelligence Vs Business Analytics



What’s the difference between Business Analytics and Business Intelligence? The correct answer is: everybody has an opinion, but nobody knows.

For example, when SAP says “business analytics” instead of “business intelligence”, it’s intended to indicate that business analytics is an umbrella term including data warehousing, business intelligence, enterprise information management, enterprise performance management, analytic applications, and governance, risk, and compliance.

But other vendors (such as SAS) use “business analytics” to indicate some level of vertical/horizontal domain knowledge tied with statistical or predictive analytics.

At the end of the day, there are two things worth differentiating:

  1. The first is the business aspect of BI — the need to get the most value out of information. This need hasn’t really changed in over fifty years (although the increasing complexity of the world economy means it’s ever harder to deliver). And the majority of real issues that stop us from getting value out of information (information culture, politics, lack of analytic competence, etc.) haven’t changed in decades either.
  2. The second is the IT aspect of BI — what technology is used to help provide the business need. This obviously does change over time —sometimes radically.


Quick Comparison


We know that Analytics refers to the skills, technologies, applications and practices for continuous iterative exploration and investigation of data to gain insight and drive business planning. Analytics consists of two major areas: Business Intelligence and Business Analytics.

What is often overlooked is how the two differ based on the questions they answer:

  1. Business Intelligence : traditionally focuses on using a consistent set of metrics to measure past performance and guide business planning. Business Intelligence consists of querying, reporting, OLAP (online analytical processing), and can answer questions including “what happened,” “how many,” and “how often.”
  2. Business Analytics : goes beyond Business Intelligence by using sophisticated modelling techniques to predict future events or discover patterns which cannot be detected otherwise. Advanced Analytics can answer questions including “why is this happening,” “what if these trends continue,” “what will happen next” (prediction), “what is the best that can happen” (optimization).


The Evolution of Business Intelligence vs Business Analytics

In the past, BI has been used to talk about the people, processes and applications used to access and extrapolate meaning from data, for the sake of improving decisions and understanding the effectiveness of targeted decisions. But this is where BI as a baseline failed; something that runs entirely off of static, historic data severely limits a user’s ability to make predictive decisions and forecast for the future market. When an emergent situation arises on a Friday afternoon, the user doesn’t greatly benefit from looking at metrics collected prior to the introduction of that situation.

The rapid growth and demand for BA comes from this failing, and is in a way the evolved form of BI solutions. In a business world whose speed is ever-increasing, the user needs to be able to interact with information at the speed of business, not looking back over his or her shoulder at what happened in the past. BI setups alone do not support the occurrence of users asking and answering questions in the face of marketplace events as they happen. A company that is data-driven sees their data as a resource, and uses it to hedge out competition. The more current the data the user has, the better jump he or she has on the competitor, who may or may not have become a threat in a time so recent that traditional BI data reporting wouldn’t even take them into consideration.

Many companies are commonly implementing advanced analytics on top of their data warehouses, to bridge the gap between BI and current day needs. Perhaps this is the origin of the confusion between terms, as organizations pick and choose from different combinations of services and have no real understanding of what to call these mashups.

Equally relevant is the fact that more and more people are being asked to interpret data in roles that are not strictly analytical. Product managers, marketers and researchers are moving towards data as a way to formulate strategies, and traditional BI platforms make it difficult to push data into real-time situations and what-if scenarios.  With the importance of data-driven decisions increasingly becoming a realization for less tech-savvy branches of company teams, the need for more user-friendly and faster producing platforms also grows. Moreover, delivering the data that supports these decisions to a broader company team demands a more visual form of modeling tool, to improve understanding across all departments. Charts and graphs showing BA findings are quicker and more impacting than written out statistics and excel sheets full of data.

Data interpretation and the manipulation method of choice change as the market demands. While having a set of established methods is important to the effectiveness of a company’s strategy, it’s understanding the need for flexibility in the face of these changes that can be a company’s most valuable asset.

To summarize,

WHAT is happening to your business = Business Intelligence (For Visibility)
WHY it is happening, WHAT WILL likely happen in future = Business Analytics (For Investigation, Prediction & Prescription).



Beginner’s Guide to Crosstab in Business Intelligence Using Jaspersoft IReport

What is Cross Tab?

A Table is just of 2 Dimension but at times we do need our table to be represented in a 3 dimensional manner or in a Dynamic Manner.


Take for example a table with 2 data set i.e. in the row we have countries and in the column we have months of the year

Now at times our data set will have an extra data which needs to be represented in the table where then can we keep these column or even row?

So here Cross tab Helps in those situation to make the report more meaningful

Now we can have Rows as “Country” and a sub row “State” grouped by the country.

To get a better understanding I’ll be taking an Example to represent my data in-form of a cross-tab.

From the following

Tables :

  • sales_fact_1997 sf7
  • customer
  • time_by_day

Fields like country, state, month, store_sales and etc if required,
should be represented in Cross Tab.


Open Jasper-soft I Report

-> Click on File->New->Report->Select Any Template of your choice

->Remove the Bands you don’t require

in My case i just need the Title and Summary Band so i wont remove them

Connect to the required Tool/Database where your Query will be retrieved from

After connection

Enter your query by clicking on ->


and then


After entering your query select Read Fields and Click OK.

->Go to the palette section and Drag and Drop the cross tab to which ever band you want
(In my case “Summary” Band)

Now select Main Report Data-set and Click Next

Now In Your Row Group 1 select what do you want as the Group i.e. the 1st Dimension (in My Case Country)

Now in Row Group 2 select what do you want as the Group i.e. the 3rd Dimension which is a Sub-Group of the First Group (in My Case State_province)

As shown below:


Then Click Next

Now in Your Column Group 1 Select what should be your Column Group which is your 2nd Dimension (month of year)

As shown below:


Then click Next

Now in measure what will you like to calculate in your table? So you input in the Measure Field (in my case Store Sales)

And what function will you like to perform (in my case SUM of store sales)

As shown below


Then click Next.

The Next Dialogue Box is of your Choice

Then click Finish

For any formatting select Cross-tab


As shown above

And then perform your formatting techniques.


final output-> Crosstab_blog_3

By Sohail Izebhijie

KPIs Staffing & Recruitment companies should track

KPIs Staffing & Recruitment companies should track


Key Performance indicators (KPIs) are business metrics which are used to evaluate factors that are crucial to the success of the organization. This article talks about most commonly used metrics and KPI’s which staffing companies needs to track in order to grow their business. Not only we have highlighted the KPIs but also we have enlisted teamwise KPIs.  User should have the option to put input parameters like client name, open job/closed job, time duration etc and based on those input selections the data should reflect. All of this data can come from typical ATS (applicant tracking system), excel, CRM and other softwares used by the staffing company.

KPIs for Staffing Firm Management: Below listed are the metrics which can be used by the senior management of staffing firms to understand the day to day operations, understand risks and take appropriate actions etc. These metrics can be according to client wise or time duration or job profile wise etc.

  • Time-to-hire and Cost-to-hire
  • Team Performance v/s targets
  • Job order at risk
  • Resumes Sent to the client
  • Response Rate
  • Candidate outreach Stage
  • Candidates Engaged vs Presented vs Hired
  • Client wise Job order openings and revenue
  • Candidate count Source wise
  • Timeline Management
  • Sendout per employee
  • Newly candidates vs Existing Candidates
  • Total cost per project


KPI to be shared with Staffing Firms end Clients: Ideally there are certain metrics which should be shared with the client. This will give the freedom to client to understand the kind of work which happens behind the scene for a single recruitment.


  • Retention
  • Resume Received by client
  • Number of Placements
  • Average time per order
  • Target benchmark alerting
  • Response Rate
  • Conversion Rate
  • Open Job vs. Closed Job


Sourcing Member: Sourcing members are responsible for scouting and uploading the CVs for relevant profiles. Below are some of the metrics which can help sourcing members to more efficiently do their job

  • Total Number of CV uploaded timewise/clientwise/job_profile wise
  • No. of fit resumes vs unfit resumes of the total number of CVs uploaded
  • Old versus new CVs
  • No. of submitted candidates to client and hired
  • Target and achieved


Account Managers of Staffing Firms: Account managers are generally responsible for approaching candidates, speaking with them, shortlisting, setting up interview etc.

  • No. of follow-up done
  • Response and declined rate
  • Calls made and appointments scheduled
  • Target and achieved

There are a lot of staffing and recruitment companies with very low entry barrier, hence a new vendor can come whenever he wants and can snatch the client. Usage of data analysis platform and sharing the same with their end client can not only make a staffing and recruitment company standout, but sharply monitoring the same can also help in more efficiently and cost affectively running the company.

Get in touch with us to see a data analysis and BI in action. Learn how you can turn your ATS (Applicant Tracking System) data into goldmine.

Reach out to [email protected]

Read the blog to learn about the Staffing & Recruitment Analytics solution by Helical

Importance of Business Intelligence in Travel Industry

Importance of Business Intelligence in Travel Industry


The travel industry is highly complex with multiple players and systems interacting with each other on real time basis for smooth functioning of the business. The various players and systems include Travel Management Companies, Global Distribution System Providers, Call Centers, Travel Agencies, etc. Due to these complex systems, a huge amount of data is generated continuously. But, there are big voids in data collection and this poses as a big challenge for the travel industry. Travel companies are hence finding it very difficult to run targeted campaigns; they are neither unable to offer personalized products to customers nor utilize Predictive Analytics. However, introduction of new technologies is slowly changing the way travel organizations collect and use data.

         Business Intelligence and Analytics play a key role in addressing many revenue impacting and operational inefficiencies. When the data is combined with multiple external sources like data from travel companies, online portals, private websites and from social media, the intelligence obtained is significantly gives greater insights into customer behavior patterns. Such kinds of insights help organizations analyze trends and customer preferences – their likes & dislikes and sentiments. This would then act as an extremely powerful tool for devising business strategies and discovering hidden sales opportunities.

For example, when an Airline route suddenly starts showing negative revenues while operating which has always been profitable before, Business Analytics is capable of providing insights. Data from travel companies may reveal increased competition in the sector. Online portals like Ibibo, MakemyTrip will provide data in the form of user comments and blogs, which when analyzed, can provide results from sentiment analysis. It can reveal the brand equity and impression that customers have about the organization. If the outcomes are not favorable, organizations can put in extra efforts to analyze the reasons behind it and devise an improvement plan. The processed data can also be presented in the form of reporting dashboards showing factors affecting customer sentiments.

Predictive Analytics in Travel

Suppose a person is travelling for an International Vacation to Singapore. He booked his tickets using one of the online portals like MakeMyTrip. Thanks to the power of predictive analytics, the person might receive an exclusive offer from his favorite airline for the ideal route along with an option to include a hotel and perhaps best restaurants in Singapore for someone traveling on an expense account.

KPIs for Travel Industry

The following are the most generic and key categories for Travel Organizations:

  • Spend and Savings :  Spend Under Contract, Booking Visibility, Payment Visibility, Reaalized Negotiated Savings, Contract Competitiveness, Cost of Managed Travel.
  • Traveler’s Behavior and Policy : Cabin Non-compliance, Lowest Logical Airfare (LLA) Non-compliance, Advance Booking Non-compliance, Online Adoption Rate, Hotel Visibility, Hotel Quality.
  • Suppliers : Traveler Satisfaction, Contract Support
  • Process : Re-booking Rate, Reimbursement Days
  • Traveler’s Safety : Location Insights, Profile Competition
  • Corporate Social Responsibility (CSR) : Carbon Visibility, Rail vs Air
  • Data Quality : Data Quality


Benefits of Using BI in Travel Industry

  • Enhance customer segmentation
  • Increase revenue
  • Targeted offers and promotions
  • Benchmark against industry standards
  • Reduce operational cost
  • Competitor insights
  • Increase inventory utilization
  • Improve customer service


Some of the other areas where BI can be applied to Travel Industry are

  • Capacity Planning
  • Transporters Performance Evaluation
  • Mode-Cost Analysis
  • Supplier Compliance Analysis
  • Routing and Scheduling
  • Driver Performance Analysis


A Travel Domain company “CTI Travel Ltd” had implemented business intelligence in their system. This upgraded system had benefited them in various ways like :

  • Real time tracking on supplier
  • Sales team got benefited in finding new business leads, profitable and under-performing clients.
  • Improved client experience and customer centric services
  • Helped in finding the gaps using the real time data
  • High quality Reports using real time data
  • Better Financial Management
  • Improved complicated processes
  • Improved decision making processes by management / teams / individuals


With rich experience in various domains including travel (empowering travel management software of IBNTech) get in touch with us at Helical IT to find out more about how a BI solution can benefit your company. Reach out to us at [email protected]