Connecting KNIME to SQL Server 2012

 

I wanted to write a quick note for other KNIME users or anybody who is trying to read data from SQL Server into your KNIME workflow.  This writeup is intended to supplement KNIME’s database documentation.

Specifically I will cover trying to setup KNIME 3.2.2 running on Windows to be able to connect to SQL Server 2012 using SQL Server Authentication with a JDBC driver.

This write up assumes your ID and password is setup to use SQL Server Authentication, as opposed to Windows Authentication.  You should verify you can log in with another tool.  I used Excel.  If you cannot connect with another tool you will need to do some troubleshooting steps to resolve this issue first. Typical things to check is if you really have the right password, if you are being blocked by a firewall on the client or server side, if SQL Server is setup to allow remote connections, and if the servers are setup under a local Windows account (as opposed to running as LOCAL SYSTEM).  There are additional steps that are not covered in if you are required to use Windows Authentication.  This write up is for SQL Server Authentication only.

To get started first download the Microsoft JDBC Driver 6.0 for SQL Server

Now unzip the download and put the unzipped folder under the KNIME install folder. My KNIME install was under C:\Program Files\KNIME

I ended up copying the entire folder Microsoft JDBC Driver 6.0 for SQL Server under C:\Program Files\KNIME

In KNIME go to File -> Preferences menu option, then go to KNIME then Databases This is the place you will need to configure KNIME.   You will be adding a file so KNIME can make a JDBC connection to SQL Server.

Click the Add File button and navigate down to C:\Program Files\KNIME\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\jre7 and select the file called sqljdbc41.jar

Now click OK.

Now lets test out if we can connect. Start a new workflow and add a Database Reader node.

Configure the node as follows:

Select the Microsoft SQL Server JDBC driver from the drop down.

Then enter in your Database URL that follows the format of jdbc:sqlserver://<host>:<port>/<database_name>.

Enter your User ID, password and your SQL query. Make sure all your column names have a names. For example, if you have a query like SELECT COUNT (*) FROM YourTable you will get an error. Use SELECT COUNT(*) AS MyCount FROM YourTable and you will be OK.

Right click on the node and run it.

Troubleshooting voice over IP calls on Comcast internet.

 

I use Microsoft Skype for Business to make voice over IP calls for work.  I like this technology as it helps me maintain a flexible and mobile work style.  I was not happy when I noticed the call quality of my voice over IP calls was degrading over a period of a few months.  I wanted to share some troubleshooting steps I found useful:

1.  Try rebooting your Comcast modem. 

2.  Try connecting your laptop and use your cell phone’s mobile hotspot.  This can help you isolate if the issue is coming from your laptop/VoIP provider or with your ISP or internal network.  In my case, my call using my cell phone’s hotspot was perfect, so I focused my effort to my local network/ISP.

3.  Try checking your jitter and latency. 

I used this site to check my jitter and latency.  https://www.megapath.com/speedtestplus/ When I was having a problem my jitter was at 20 ms.   After the issue was resolved my jitter was 1ms

4.  Try testing your signal strength and quality with Comcast internet. 

Log on to your Comcast modem by typing 192.168.100.1 in your browser.  If you never logged on to your Comcast modem the default Id is admin and the default password is password.   

Now click on Connection then XFINITY Network.

Scroll down and find the Channel Bonding Values and find the values for SNR (Siginal to Noise Ratio) , Downstream Power Level and Upstream Power Level.

Signal to Noise Ratio (SNR)

If you have 256 QAM then it is recommended you are higher than 33 dB.
If you have 64 QAM then it is recommended you are higher than 27 dB.       If you have 64 QAM then it is recommended you are higher than 27 dB. 

Downstream Power Level

The recommended range is between -7 dBmV to +7 dBmV. 
Lower than -15 dBmV & Higher than +15 dBmV  is out of tolerance

Upstream Power Level

The recommended signal levels are +35 dBmV to +49 dBmV.   Above 52 dBmV is out of tolerance.

If any of your values are not with in the recommended range you should call up Comcast and ask if they can confirm the signal strength and quality on your line.   A level two Comcast technician has the ability to log on to your Comcast modem and see these signal strength metrics.  There is chance they will need to send a technician to check the coaxial cable outside of your home.  In my case the issue was caused by a sever kink in the coaxial cable outside.

5.  Working with Comcast technical support.

In my experience when I call Comcast technical support I am asked if I would like to participate in a brief survey after the call.   I find that answering “Yes” I often get connected to the “offshore team” as their accents a small increase in background static kind of give them away.  The offshore team tends to be much more helpful, pleasant, and overall much more capable technically.  When not agreeing to take the brief survey I tend to get an American call center agent who seem to be following a ridged script.  I find they tend to be unhelpful, lazy, and bureaucratic. On one call the America call center agent gave me information that was flat out not true and borderline on fraud when he told me he could not help me further but I could sign up for a line protection service for an additional monthly fee.   Do yourself a favor and answer “Yes” if you are asked to do a brief survey.

How To Manually Install Python Modules Without pip and On A Windows Server Not Connected To The Internet

 

1) Download the modules to your laptop.

2) Copy the .tar.gz files to any temporary directory you created on the Windows server you are installing on.

3) Unzip. You will need to unzip twice as these files are Tar-ed and Zipped. You know you done it correctly when you see the folder structure and a file called setup.py.

4) Start a windows command window and CD down to the directory with setup.py.

5) Type: python setup.py install (do that for each module in their respective directory)

6) The install script will print out the status and indicate if the install was successful.

Curious Sequence

 

On a college visit with my high school son to Rose Hullman in Terre Haute, IN  I picked up a copy of the schools’s alumni magazine.  The magazine had a section of brain teasers and logic problems written by Herb Bailey. One of the brain teasers was interesting to me since it was a challenge to write an algorithm to identify a specific number sequence called a “curious” sequence.    The brain teaser was as follows:

To solve this brain teaser I had to fully understand the rules of the sequence.   After analyzing the sequence, I had an idea that a curious sequence can be defined by two characteristics.  First the sequence equals n+1 when summed up.   So for n =3 for example, 2+0+2+0 = 4.   The second part is that each position reveals the number of digest in the sequence when referencing as an array with an index.    This is demonstrated below.

Notice how if the sequence is treated as an array and how each element of the array can be referenced by an index number.    So for the first element of the array when the index=0, the value is 2.   2 also represent the number of zeros present in the array.   Moving to the next element where the index=1 shows a value of 0.  Interesting enough there are zero 1s in the sequence of 2020.   Pretty clever to say the least! 

Now that I got a clearer understanding of the problem the question becomes, can algorithm can be created that can identify all curious sequences when n=4, but also when n=5, n=6, n=6….?    As it turns out it is very possible to develop an algorithm that will identify curious sequences in a linear time complexity.  I used Perl to impliment a solution. The results for n=3 to n=9 are shown below.

Adding a custom icon to Oracle Endeca Information Discovery

Adding an icon for a user community in Oracle Information Discovery can done exclusively using functionality under Control Panel.  However, in many cases the icon you want to add is larger than the size provided in the default Endeca theme.   The steps below show how to modify the Endeca theme to accommodate a larger icon. 

1. Change the icon under Control Panel -> Communities -> Manage Pages (of your selected community) -> Settings then click on the Logo tab. Select your file, check Use Logo and then Save.

2. Go to your Community and look at your banner. Your image may be cutoff if it was larger than the defaul “Oracle” image. You made need to adjust the size of the Endeca theme to accommodate a larger image.  Notice below how the Oracle image invades the text and black bar.

3. You can adjust the banner size by going to C:\Oracle\Endeca\Discovery\2.3.0\endeca-portal\tomcat-6.0.29\webapps\endeca-theme and create a directory called _diffs. Go inside that directory and create a subfolder called css Now go back into the original css directory and copy the custom.css file into the css directory you just created in the _diffs folder. This is a Liferay best practice that supports upgrades.  You should have a directory structure as follows:

4. Edit custom.css in _diffs/css. I made changes to height and set it to 80px and also left and set it to 300px to move the “Endeca Information Discovery” text over.  

5. Restart your Studio service to pick up your changes.  You may need a few iterations of making changes and testing to get the sizes exactly what you want.  Remember to restart the Studio service have each change. 

Oracle Endeca Information Discovery – Changing a data source on a metrics bar component

 

I  was working with Oracle Endeca Information Discovery 2.3 and needed to change the data source on a few metric bar components.  The first thing I did was create an export of the community in a .lar file.  This was done under Control Panel –> Communities –> Actions –> Manage Pages –> Export/Import –> Export.

I tried changing the data souce under the Control Panel under Data Source Bindings.  I have used this method for other components so I figured the metrics bar component would be similar. 

When I went back to my metrics bar I saw I had a problem.  I got the dreaded “This component requires additional configuration”. warning.  

 

Inspecting the metrics bar component shows that the EQL is missing.

I restored my Endeca community using the import function and tried a different approach.  This time I went directly into the component and selected the drop down box and selected the new data source.  I hit Update data source button. I was disappointed to see the EQL disappeared.  I had an empty metrics bar component and the dreaded “This component required additional configuration”. 

I restored my Endeca community using the import function and tried a third approach.  This time I copied the EQL and saved it to a text file.  I also copied the Display Name of each of the metric bars and noted down the Format.

I updated the data source and the EQL disappeared.  This time I was prepared.   I re-entered my EQL and hit the Test EQL button.  Once that came back I hit the Load Metrics button.  Then I entered the Display Name and Format for each metric bar.  Success.  The data source was changed. The EQL and metrics bars were present for duty.  I did not miss seeing the “This component requires additional configuration”. warning.  

Oracle Endeca Information Discovery – Partition Transformation

 

I have been working with Oracle Endeca Information Discovery tool to join structured and unstructured data together in interesting business solutions. I wanted to share my experience with the Partition Transformation and help show how it can be used to conditionally split a pipeline in an Intergrator (CloverETL) graph.

The transformation has properties called Ranges and Partition key. Setting the Partition key is easy. Just pick the fields from a list. Setting the Ranges is not so easy to understand.

See the table below that shows an example of setting the Ranges property. Setting ranges requires using an awkward syntax that uses < and > to include and ( and ) to exclude. A comma defines the low end and high end of the range. A semi-colon defines separate ranges. Sounds confusing? It is. Fortunately, there is a much easier way using CTL2.

For the easy way, click on the Partition attribute that drops you into the CTL2 editor. You can refer to your input fields with a $ prefix. $DOC_TYPE was defined on the import port and is an input field. Then write a conditional statement. In my example below, all rows with a $DOC_TYPE = “Operation Instructions” will be sent to port 0. All others will be sent to port 1.