1 2 3 4 5 6 7 8 9 10 Next »

Remove ABC Column in Table

Create Date: January 07, 2019 at 12:55 PM         Tag: TABLEAU         Author Name: Sun, Charles

Remove ABC Column in Table

Create a calculated field with Blank space in codes like " " .

Drop the calculated field on text.

Case statement with "CONTAINS" function

IF CONTAINS([SERVICE_DESCRIPTION], "BL1") then "BL1"

else ""

end

 

New Comment

set up odbc connection to SQL Server in R

Create Date: November 09, 2018 at 12:34 PM         Tag: R         Author Name: Sun, Charles

SQL Server RODBC Connection

install.packages("RODBC")
library(RODBC)
dbhandle <- odbcDriverConnect('driver={SQL Server};server=mysqlhost;database=mydbname;trusted_connection=true')  //or ;uid=;pwd= instead of trusted_connection=true
res <- sqlQuery(dbhandle, 'select * from information_schema.tables')

 Accessing Database (MS SQL Server/Oracle/MySQL) from R!

Accessing Oracle Database using RJDBC Package

Below is my database hosted on AWS Cloud. See here how to setup Oracle Db on cloud (free tier).

11

R Packages required:-  install.packages(“RJDBC”)

Also download Oracle JDBC Drivers from here Link. After downloading, copy file location.

location.png

Commands:

1

2

3

driver <- JDBC("oracle.jdbc.driver.OracleDriver"," <paste JDBC Driver file location>")

 

dbConnect(driver , "jdbc:oracle:thin:@Host:Port:ServiceName or SID", "<username>", "<password>")

code1

R users have few more choices to access their Oracle Database: Using RODBC and ROracle packages.

 

MySQL Database

Here is my database hosted on AWS Cloud. See here how to setup MySQL Db on cloud (free tier).

1

a) Accessing MySQl Database using ODBC:

R Packages required:-  install.packages(“RODBC”)

For Windows: Go to start, search ODBC. Follow below steps of setup.

1

Click on “Add”.

2

Select MySQL ODBC Driver. If not present, then download using this link.

3

Provide your server details, username & password. Test your connection. Note down Data Source name.

4

Commands:

1

2

3

my_conn <- odbcConnect("<ODBC Data Source Name >")

 

sqlQuery(my_conn, paste("<Query>"))

code

b) Accessing MySQL DB using RMySQL Package:

R Packages required:-  install.packages(“RMySQL”)

Commands:

1

2

3

4

5

my_conn <- dbConnect(MySQL(), user="<username>", password="<password>", database= "<Database name>", host="<your host>")

 

result<- dbSendQuery(my_conn, "<Query>")

 

dbFetch(result)

code 2.png

Accessing SQL Server Database using ODBC

Here is my SQL Server database hosted locally.

server.png

a) Accessing SQL Server using ODBC:

For Windows: Go to start, search ODBC.

1

Follow below steps to setup ODBC datasource.

output_KUL4JC.gif

Commands:

1

2

3

my_conn <- odbcConnect("<Data Source Name>", uid="<username>", pwd="<password>")

 

sqlQuery(my_conn, "<Query>")

code2.png

b) Accessing SQL Server DB using RJDBC Package:

Suppose SQL Server database hosted on cloud. We can use below commands to connect.

R Packages required:-  install.packages(“RJDBC”) & Download SQL JDBC Drivers file from internet.

1

2

3

4

5

6

driver <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver",

"<location of SQL JDBC Drivers file on computer>")

 

conn <- dbConnect(driver , "jdbc:sqlserver://<serverName>", "<userID>", "<password>")

 

result <- dbGetQuery(conn, "<Query>")
New Comment

Extensions for Tableau Dashboards

Create Date: November 06, 2018 at 04:54 PM         Tag: TABLEAU         Author Name: Sun, Charles

Extensions for Tableau Dashboards

Tableau made a good progress on extending its dashboard functions, like Write-Back.

this extension can do simple update google doc files and database tables.

there are about 20 extensions from the above link. a good resource.

New Comment
1 2 3 4 5 6 7 8 9 10 Next »