Friday, July 15, 2016

Interview questions on SQL

Here are 5 interview questions on SQL, please try and share the answer in comments section

1. STudentDetails: StudentID, courseID, InstructorID, EnrollmentDate
Query to get the list of students who have enrolled to ALL COURSES available in 2016.


select StudentID from STudentDetails where EnrollmentDate>='2016-01-01 00:00:00' and EnrollmentDate<'2017-01-01 00:00:00'
and courseID

select distinct courseID from STudentDetails

2. Avg no. of students per course in 2016.
Eg : If 2 Courses and 10 students in C1 and 20 students in C2, then I would like to get avg number as 15.

select avg(count(StudentID))
from STudentDetails
where courseID in ('C1','C2')
group by courseID

3. Product : ID, Name, Unitcost
SalesRep: ID, Name
SAles : ProductID, salesrepId, custID, qty, sales_date

Query to get the list of productid, salesrepId where the Salesrep has sold a particular product in consecutive days.

S1 P1 01 Jan 2016
S1 P1 02 Jan 2016
S1 P2 03 Jan 2016
S1 p1 04 Jan 2016
S1 P1 05 jan 2016

Resuit set :
S1 | P1 01 Jan 2016 02 jan 2016
S1 | P1 04 Jan 2016 05 Jan 2016


select salesrepId,ProductID
    case(when sales_date<) as start_date,
    case(when ) as end_date
from SAles
group by salesrepId,ProductID,start_date,end_date

4. Design employee dimension as per SCD Type 2.

Employee :
    ID (auto-increment)
    EmpId
    Name
    City
    HQ,
    DeptID
    MangerID
    Bank Account
    StartDate

Dept:
    DeptID
    Name
   
ID,EmpId, Name, City, Startdate
1, 1, Mr. A, Hyd, 2016-01-01
2, 1, Mr. A, Pune, 2016-07-01

5. What is a role playing dimension?

Saturday, March 19, 2016

Getting started with Data Science : Python

There is lot of buzz about data science being a super cool stream. So, i decided to do a write up on how to get started with it. Without wasting much time, here you go with the details:

Step 1: Setting up your machine
Download Anaconda here and install it. I am sure this will be a pretty simple installation on any of the platform.

Step 2: Learn the basics of Python language

If you want to get started with data science in Python, you need to know atleast the basics of python. Atleast an hello world in python is sufficient to get started. You can get hang of the language later.

And trust me, Python is the most easiest langauge to learn. Python 2.7 will not be used post 2020 and hence its better you take things with Python 3.
Some referance to Python 3:
Free e-book on python: http://python.swaroopch.com/index.html

Step 3: Learn Scientific libraries in Python – NumPy, Matplotlib and Pandas


Basics for exploratory data analysis and data handling:
Numpy - Array like structure for scientific computing
Pandas - Data structure to easily manipulate data.
Matplotlib - One of the famous Plotting library for SciPy stack.


Step 4: Learn Scikit-learn and Machine Learning


Here the fun part begins:
Scikit learn library provide most of the machine learning algorithms  for Python.
Web site: http://scikit-learn.org/stable/


Step 5: Practice, practice and Practice

I would rather say this is the most important step which can distinguish you from the rest.

Conclusion:
You have to accept the fact that you wont turn into data scientist overnight, it will take time and dedication to become one.

There are frequent question like how much time it will take? 
The answer really depends on how much time you devote to this subject and how quickly you understand the concepts.

Fortunately there are many sites which can be used as testing ground for your machine learning skills like https://www.kaggle.com/competitions or http://datahack.analyticsvidhya.com/contest/all which keep doing this awesome competitions, in which you can participate and test your knowledge

Friday, March 4, 2016

Cheat sheet : Exploratory data analysis


Here is short version of exploratory data analysis

1. Variable Identification (categorical, continuous, etc)
2. Univariate Analysis
    a. categorical variable : Frequency of occurance (count). Bar chart for visualization
    b. continuous variable: Mean, media, mode, min and max. Histogram for visualization

Ref: https://www.youtube.com/watch?v=wFabyCP54YA

3. Bi-variate Analysis
    a. Continuous & Continuous: Scatter plot to find out Correlation
Correlation varies between -1 and +1.

-1: perfect negative linear correlation
+1:perfect positive linear correlation and
0: No correlation

    b. Categorical & Categorical:
a. Two-way table: Have count and count% as metric
b. Stacked Column Chart:
c. Chi-Square Test: Need to read more on this but
Probability of 0: It indicates that both categorical variable are dependent
Probability of 1: It shows that both variables are independent.
c. Categorical & Continuous:
a. Z-Test/ T-Test:
b. ANOVA:  It assesses whether the average of more than two groups is statistically different.

Ref: https://www.youtube.com/watch?v=IA0unflfvQE
https://www.youtube.com/watch?v=zdU8C8QEHH0

..To be continued...

Ref: http://www.analyticsvidhya.com/blog/2016/01/guide-data-exploration/

Tuesday, February 23, 2016

Starting with scikit-learn

If you are an aspiring Data analyst / Data scientist, this is the correct place to be.

Here is an awesome step-by-step documentation scikit-learn community has created.
http://scikit-learn.org/stable/tutorial/basic/tutorial.html

I will be providing some videos in the blog post later on how to get started.

Some of the existing videos which you can follow:
http://scikit-learn.org/stable/presentations.html#videos

Here are some FAQs:
Using my data with scikit-learn?
http://stackoverflow.com/questions/21492726/using-prepared-data-for-sci-kit-classification

Importing a CSV data to scikit-learn
http://stackoverflow.com/questions/11023411/how-to-import-csv-data-file-into-scikit-learn/11035992#11035992


Sunday, February 21, 2016

How to become data analyst / data scientist

If you are starting from scratch then this post is for you and interested in Python:

Step 1: Setting up your machine
Download anaconda and use spyder or Ipython for trying out small things.
Python is installed into this package itself.

Step 2: Basic of Python as a programming lang
 learn: Lists, Tuples, Dictionaries, List comprehensions, Dictionary comprehensions

Step 3:
Learn these libraries in Python – Pandas, NumPy, SciPy and Matplotlib

Step 4:
Start Machine learning with Scikit-learn

Step 5:
The thing which will differentiate you from other data scientist is practice.


Some nice blogs to follow:
 1. http://www.datasciencecentral.com/
 2. http://www.kdnuggets.com/
 3. http://www.analyticsvidhya.com/ [Indian community which came up very fast]

To start practising models, participate in competitions from:
1. https://www.kaggle.com/
2. http://www.analyticsvidhya.com/

Friday, February 19, 2016

Installing Jenkins

Jenkins has two ways to install
1. Installing a war file
2. Installing jenkins as a service

In my case, it is redhat distribution and there are simple steps to install Jenkins as a service

Thats it!!!
http://<IP address>:8080 is the place where you will get the default installation screen


Monday, October 12, 2015

Landing page in Pentaho User Console

Problem: After login to Pentaho User Console, you need to redirected to a customized page instead of Home page of Pentaho. (In this case i have created my own page)

Solution:
Go to /tomcat/webapps/pentaho/index.jsp and change

<META HTTP-EQUIV="refresh" CONTENT="0;URL=./Home">
to
<META HTTP-EQUIV="refresh" CONTENT="0;URL=/pentaho/content/pentaho-cdf/RenderXCDF?solution=mydashboard&path=/company&action=homedashboard.xcdf&template=companyview">



Refernce: http://forums.pentaho.com/showthread.php?177028-Redirect-to-dashboard-after-login

Monday, September 28, 2015

Installing Pentaho BI server on Amazon EC2

Steps to install Pentaho BI server on Amazon EC2 box:

  1. Bring up amazon ec2 box
  2. Download pentaho community latest version from http://community.pentaho.com/.
    At the time of writing this blog, Pentaho 5.3 and we download it using this command

    wget http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/5.3/biserver-ce-5.3.0.0-213.zip
  3. Unzip the folder 

Pentaho: Moving from http to https



Reference : http://pentaho-bi-suite.blogspot.in/2015/05/self-signed-ssltls-configuration-how-to.html

Pentaho User Console: Change default port

Reference : https://help.pentaho.com/Documentation/5.3/0P0/000/050

Having ALL data with muli select

Solution:
http://forums.pentaho.com/showthread.php?193329-ALL-Condition-with-Multiselect-dropdown-replacing-ALL-in-query

Friday, July 24, 2015

Sparkl : Creating Effective Dashboards on Pentaho

With the introduction of Sparkl pluign which works with Pentaho 5 and above, it has become very easy to create Dashboards which can be quickly created.

To understand what sparkl is, you should consider reading : http://pedroalves-bi.blogspot.it/2013/10/sparkl-pentaho-application-builder.html
http://forums.pentaho.com/showthread.php?151358-Sparkl-A-Pentaho-Application-Builder

How to work with sparkl : http://fcorti.com/pentaho-sparkl/

Getting some useful tips to create your first production dashboard: http://diethardsteiner.github.io/review/2014/08/10/Pentaho-Sparkl-Tips.html

If you wish to check how to debug the kettle endpoints: http://forums.pentaho.com/showthread.php?156393-Debugging-Sparkl-applications-Pentaho-JCR-Repository-Synchronizer-real-world-example

Documentation to develop plugin: http://wiki.pentaho.com/display/ServerDoc2x/Developing+Plugins

Thursday, July 2, 2015

Creating Pentaho report using Rest API


Reference: http://woodlouse.blogspot.fr/2013/02/how-to-create-pentaho-report-using-rest.html

Use of dynamic variables to form URL
http://www.nathanhoeft.com/2012/03/24/couchdb-and-pentaho-data-integration/

Sunday, June 21, 2015

How to enable compression on Tomcat?

Problem Statement: My web page is loading very slow.
Solution: Check out the reason for the same on Pagespeed insights tool from google here

The most probable solution which is offered today for Mobile and Desktop is to enable compression.
And if you are using Tomcat then this is a cake wake.

Here is how to enable compression on Tomcat:
1. Go to the path where you have installed Tomcat i.e cd tomcat/conf
2. Open server.xml and search for the pattern :
<Connector URIEncoding="UTF-8" port="8080" protocol="HTTP/1.1"
               connectionTimeout="20000"
               redirectPort="8443" />


3. Change this to :
<Connector URIEncoding="UTF-8" port="8080" protocol="HTTP/1.1"
                connectionTimeout="20000"
                compression="on"
                compressionMinSize="512"
                compressableMimeType="application/json,application/xml,image/gif,image/png,text/css,text/html,text/javascript,text/plain,text/xml"
               redirectPort="8443" />

4. That's it. Now restart tomcat and check out the page speed again using google tool to analyse it.

I am working on Pentaho systems which uses tomcat server and got the solution from http://jira.pentaho.com/browse/BISERVER-10222


Monday, June 1, 2015

Hide files in Pentaho User Console

Hello Friends,

There are situations when you need to hide the files generated by Pentaho.
Pentaho by default generates three types of files to be displayed in Pentaho User console:

.cda - The data access file, which is of no use to business user
.cdfde - Framework File, again of no use to be business user
.wcdf - Dashboard file, which you most likely need to keep
Use case: When you give a multiple dashboards to business user, he should be able to see only the dashboard files. Something like the image below:


To achieve this go as admin -> browse files -> select a file -> click on properties -> hidden





To show the hidden files, go as admin, click on Menu-> show hidden files as show:


Reference:
http://pentaho-bi-suite.blogspot.in/2014/04/hide-cda-and-tmpcda-files-in-pentaho-ce.html
http://forums.pentaho.com/showthread.php?153655-Pentaho-5-How-to-unhide-folder

Saturday, May 30, 2015

Welcome to the world of Analytics

Hello World,

We are out here to explore and learn new things in the world of Analytics.
To start with we have chosen to work on Pentaho, an open source business intelligence tools which is a life saver for many companies struggling with cost. For more details on Pentaho you can click here.

Gradually we will be covering some data mining and machine learning problems as well