Beginners Guide to Understanding Cross Join

Beginners Guide to Understanding Cross Join

In my previous Beginners Guide we talked about Types of joins i.e.

Inner Join, Left and Right Join but today we will be talking about cross join which happens to be more interesting to me than any other joins and happens to be helpful in real time for me.

What is a CROSS JOIN?

A CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables.

Why CROSS JOIN?

Let’s say we have two table employee table “emp1” and department table “dept” now lets see how many records do we have in the emp1 table:

emp1
and also how many records do we have in the dept table:

dept

and for the emp1 table you want to “associate” the location column from the
dept table to all the records in the emp1 table so we use CROSS JOIN

but really why ?
In realtime Sometimes you might want to associate dates with a particular table and its records this happens to be helpful

Now we can do that with the Keyword CROSS JOIN as shown below.
cross

And there you go hope you will have a better understanding now.

-Sohail I.E

Business Intelligence Vs Business Analytics

BIvsBA

 

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

BIvsBA

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).

References:

  1. https://www.quora.com/What-is-the-difference-between-business-intelligence-and-business-analytics-1
  2. https://blog.rjmetrics.com
  3. https://rapidminer.com

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.

Why?

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 ->

Crosstab_blog

and then

Crosstab_blog_6

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:

Crosstab_blog_1

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:

Crosstab_blog_2

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

Crosstab_blog_5

Then click Next.

The Next Dialogue Box is of your Choice

Then click Finish

For any formatting select Cross-tab

Crosstab_blog_4

As shown above

And then perform your formatting techniques.

 

final output-> Crosstab_blog_3

By Sohail Izebhijie

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]

 

 

How to restrict Multiselect input control values in jaspersoft :

How to restrict Multiselect input control values in Jaspersoft 

This blog will teach the reader , how to restrict multiselect input control values in jasper without hampering the performance .

DB : Postgres (foodmart)

Table : customer

Reporting tool : iReport

Server : Jasperserver 5.6

I/P Parameter : country

Requirement : Get All the informations from customer table where country name is either USA or Mexico or Canada

Condition :

  1. you can not select more than 2 value in “country” input parameter
  2. if more than 2 values are selected in “country” input parameter then the main report query should also not get executed. This will unnecessarily hamper the performance.

Solution :

  1. create multiselect input control named as “country”

select distinct country from customer

  1. create a sub report which will display all the informations from customer table on selection of “country” parameter

and give its print when expression as :

$P{country}.size() <= 2

  1. create a text box in the header , write a message init as :

“You can not select more than 2 values in country parameter”

And give its print when expression as :

$P{country}.size() > 2

  1. in the sub report , query will be :

select * from customer

where $X{IN,country,country}

limit 20

  1. publish the report on jasper server.

O/P

# 1 : when 2 values selected in country parameter

lenlt2#2 : when more than 2 values selected in country parameter

lengt3

Thanks,

Rupam Bhardwaj

Migrate oracle with pentaho

Steps to migrate Oracle with Pentaho

 

Step 1:-

Run script as DB admin.

Script is available in location:- biserver-ce\data\oracle10g.

Modify configuration file:-

  1. applicationContext-spring-security-hibernate.properties.

Location:-

pentaho-solutions\system\applicationContext-spring-security-hibernate.properties.

original code:-

jdbc.driver=org.hsqldb.jdbcDriver

jdbc.url=jdbc:hsqldb:hsql://localhost:9001/hibernate

jdbc.username=hibuser

jdbc.password=password

hibernate.dialect=org.hibernate.dialect.HSQLDialect

Modified code:-

dbc.driver=oracle.jdbc.OracleDriver

jdbc.url=jdbc:oracle:thin:@localhost:1521/sysdba

jdbc.username=hibuser

jdbc.password=password

hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

  1. hibernate-settings.xml

Location:- pentaho-solutions\system\hibernate\hibernate-settings.xml.

Original code

<config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file>

Modified code:-

Continue reading

Remove Repeated Column header, Page Header, Page Footer in CSV Export (JASPER)

In CSV export, we want only column header once and data should be printed. We do not want page header, page footer and repeated columns header. So in order to achieve this do the following.

    1. Remove Page Header:
 
<property name="net.sf.jasperreports.export.csv.exclude.origin.band.(uniqueValue)" value="pageHeader"/>
    1. Remove Page Footer:

<property name="net.sf.jasperreports.export.csv.exclude.origin.band.(uniqueValue)" value="pageFooter"/>
    1. Remove Title Band:

<property name="net.sf.jasperreports.export.csv.exclude.origin.band.(uniqueValue)"value="title"/>
    1. Remove Repeated Column Header:

<property name="net.sf.jasperreports.export.xlsx.exclude.origin.keep.first.band.(sameValue)" value="columnHeader"/>
<property name="net.sf.jasperreports.export.xlsx.exclude.origin.keep.first.report.(sameValue)" value="*"/>

Some times we are using sub report to display page header and page footer. Do the following to remove the it in CSV Export.

    1. Remove Sub Report:

<property name="net.sf.jasperreports.export.xlsx.exclude.origin.report.(sameValue)" value="Actual Report Name not the sub report name"/>
<property name="net.sf.jasperreports.export.xlsx.exclude.origin.band.(sameValue)" value="detail"/>

This is not working for crosstab.

Thanks & Regards,
Fahad Anjum

Mobile business intelligence

 

Mobile Business Intelligence

Mobile business intelligence (BI) is the ability to access BI-related data such as KPIs, business metrics, and dashboards on mobile devices

BI Application Access
Mobile access to BI applications is typically accomplished in one of two ways:

  • Using a mobile-browser to access the application on the web
  • Using a native application that is designed for a specific mobile OS (such as iOS or Android)

Each approach has its benefits and disadvantages and, in the end, it depends on the use-case and scenario to determine which is best for any given business. In either case, mobile BI is one of the hottest and quickly evolving spaces in the software industry. Its promise attracts users and is fast gaining buy-in from leading organizations and executives from around the world. The reason behind this is simple: in the rapid-fire business world of the 21st century businesses and teams rely on real-time, on-demand access to business critical information.

Data access methods:
The two most popular data access options were:

  • To use the mobile browser to access data, similar to desktop computer, and
  • To create a native application designed specifically for the mobile device

Purpose-built Mobile BI apps
Mobile BI applications have evolved from being a client application for viewing data to a purpose-built application designed to provide information and workflows necessary to quickly make business decisions and take action.

Web Applications vs. Device-Specific Applications for Mobile BI
In early 2011 BI software market, small and large enterprise adopted either a purpose-built, device-specific application strategy (e.g. iPhone or Android apps) or a web application strategy (browser-based, works on most devices without an application being installed on the device).

Mobile BI applications can be defined as follows:

  • Mobile Browser Rendered App:Almost any mobile device enables Web-based, thin client, HTML-only BI applications. These apps are static and provide little data interactivity. Data is viewed just as it would be over a browser from a personal computer. Little additional effort is required to display data but mobile browsers can typically only support a small subset of the interactivity of a web browser.
  • Customized App: A step up from this approach is to render each (or all) reports and dashboards in device-specific format. In other words provide information specific to the screen size, optimize usage of screen real estate, and enable device-specific navigation controls. Examples of these include thumb wheel or thumb button for BlackBerry, up/down/left/right arrows for Palm, gestural manipulation for iPhone. This approach requires more effort than the previous but no additional software.
  • Mobile Client App: The most advanced, the client app provides full interactivity with the BI content viewed on the device. In addition, this approach provides periodic caching of data which can be viewed and analyzed even offline

Development
Developing a native mobile BI app poses challenges, especially concerning data display rendering and user interactivity. Developing mobile bi app is time consuming and requires more effort .

  • Custom-coded Mobile BI Apps
    For example, the iPhone apps require coding in Objective C while Android apps require coding in Java. In addition to the user functionality of the app, the app must be coded to work with the supporting server infrastructure required to serve data to the mobile BI app. While custom-coded apps offer near limitless options, the specialized software coding expertise and infrastructure can be expensive to develop, modify, and maintain.
  • Fixed-form Mobile BI Applications
    Business data can be displayed in a mobile BI client (or web browser) that serves as a user interface to existing BI platforms or other data sources, eliminating the need for new master sources of data and specialized server infrastructure. This option offers fixed and configurable data visualizations such as charts, tables, KPIs, and links, and can usually be deployed quickly using existing data sources.

-By
Nitin Uttarwar
Helical It Solution

Reading multiple properties files in spring 3 web MVC

    Reading multiple properties files in spring 3 web MVC

Spring allows us to externalize string literals in its context configuration files into external properties files, in order to separate application specific settings from framework specific configuration. Spring will read all the properties files declared by PropertyPlaceholderConfigurer bean to resolve the placeholders at application’s start up time.

Simple Example

Declare a PropertyPlaceholderConfigurer bean in spring’s application context as follows:
That tells spring to load the properties file name project.properties in the class path. An exception will be thrown if spring could not find specified property file.
The project.properties file should contain the name and value pair for example
Name = xyz
Filepath = C:/Test/Setting.xml
Host = localhost
User = testuser

By default spring look for the properties files in the application directory. So if we specify

Then it will find project.properties file under WEB-INF directory of the application in case of spring MVC application.
We can use prefix classpath: to tell spring load properties file in the application’s classpath. For example:

In the case of spring application the project.properties file should be present in WEB/INF/classes directory or in source directory src in eclipse IDE.

Use the prefix file:/// or file: to load property file from file system.

Spring context load the project.properties file from D:/Applicatin/Config else throws the exception if file is not present in the specified path.
Loading multiple properties files
Spring allows us to specify multiple properties files for the PropertyPlaceholderConfigurer as follows

classpath:/processor.properties
classpath:/datasource.properties

</property

We can add as many as properties file in list as shown above. Spring context will load all the properties files specified in list as shown above at the application start up time.

Loading Properties files values in spring controller
Suppose you have properties file as follows
Name = xyz
Filepath = C:/Test/Setting.xml
Host = localhost
User = testuser

We can use the variable read from properties files in spring controller by declaring @Component annotation in controller class for example:
@Controller
@Component
public class Controller{
@Value(“${Name}”)
String Name;
@Value(“${Filepath }”)
String Filepath;
@Value(“${Host }”)
String Host;
@Value(“${User }”)
String User;
}

The value of properties file automatic assign to declared variable by spring framework we can use these variables inside the application.

Muqtar Ahmed
Helical IT Solutions

KPI used by Ecommerce Companies in Business Intelligence

Keeping a tab of KPI is a very good exercise and can help entrepreneurs in understanding the current state of business and the way forward.The general KPI used by Ecommerce companies are listed below (ofcourse there could be variations and a lot will also depend on the companies business goal as well). These KPI, in different charts and tables and visualizations, can be built using any of the Business Intelligence software (BI). This blog will be talking about KPI used by Ecommerce Companies in Business Intelligence

 

Ecommerce KPI

Ecommerce KPI

Generic Reports are
– Average number of items per purchase
– Percentage (%) of new customers to existing customers
– Frequency of sales transactions
– Average lifetime value of customers
– Shopping cart abandonment rate
– Checkouts per cart
– Average Days to Purchase
– Order session Percentage (%)
– Average Visits to Purchase
– Coupon conversion percentage
– Cart conversion rate
– Percentage (%) of returning customers
– Percentage (%) of canceled checkouts
– Average order size

Operational reports
– Stock Week Cover: Giving information about how much units we have in stock and for how long this will last
– Rate of Stock Turns
– Stock at the COGS value
– Stock at the SRP value
– Dispatch costs per warehouses

Performance Related Reports:-
– Gross Profit
– Gross Margin Percentage (%)
– Return Rate and Return Rate Percentage (%)
– AVG Order Value
– AVG Item per order
– Traffic: number of visits or visitors, it depends what do you like to measure
– Conversion Rate = Number of Dispatched orders / Visitors
– Price Point

Thanks
Nikhilesh
Helical IT Solutions