-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy path90-test-cdw.Rmd
117 lines (74 loc) · 5.46 KB
/
90-test-cdw.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
# (APPENDIX) Appendix {-}
# Test your database set up {#test-cdw}
## Test your connection
First, let's make sure your connection works. You'll need the user name and password you received when you got access to the data warehouse. Here are the steps to see if that connection works:
1) Navigate to C:\\ProgramData\\Microsoft\\Windows\\Start Menu\\Programs\\Administrative Tools. Double click odbcad64. It's the application highlighted in the following image:
<p><center>
`r knitr::include_graphics("images/syswow64.png")`
</p></center>
2) You'll now have the ODBC Data Source Administrator open. Click the "User DSN" tab and then click the "Add.." button.
<p><center>
`r knitr::include_graphics("images/odbc.png")`
</p></center>
3) Scroll down to close to the bottom of the list of drivers. There you'll find "Oracle in instantclient_21_3". Double click this.
<p><center>
`r knitr::include_graphics("images/oracle.png")`
</p></center>
4) You'll now be in a window that asks you for some information to configure this driver. Fill out the Data Source Name as "URELUAT" and find "URELUAT" in the dropdown menu for TNS Service Name. Enter your user name (the one given to you by IST, which is probably the same as your CalNet ID) as the User ID. Click "Test Connection".
<p><center>
`r knitr::include_graphics("images/configuration.png")`
</p></center>
5) Enter your password (given to you by IST) in the Password field. Click "OK".
<p><center>
`r knitr::include_graphics("images/test.png")`
</p></center>
6) You should now get a pop up message letting you know that the connection was successful. Click "OK" three times to close out the ODBC Source Administrator. If you did not get this message and instead got an error message, do not continue with the rest of these instructions until your connection is successful. You may have the wrong password, or there may be other connection issues.
<p><center>
`r knitr::include_graphics("images/successful.png")`
</p></center>
## Opening RStudio
If your connection was successful, let's move on to making sure you can connect from within RStudio -- this will ensure that the Discovery Engine is able to query the data warehouse.
First, open RStudio. If you don't have an icon on your desktop, you can find the program by searching for "rstudio" in your Start menu. You'll also find a copy at C:\\Program Files\\RStudio\\bin. If you want a shortcut on your desktop, simply drag and drop the rstudio application.
When RStudio is open, you'll see a few different panes on the screen. The console is where you can type commands and see output. We only need to worry about the console right now. It's this part of the screen:
<p><center>
`r knitr::include_graphics("images/console.png")`
</p></center>
You also want to make sure you're using the correct version of R. Here are the steps to do so:
1) Go to the Tools dropdown menu at the top of the screen, and select Global Options. You'll see a box that looks like this:
<p><center>
`r knitr::include_graphics("images/options.png")`
</p></center>
2) The top box tells you the R version you're using. Click on the "Change" button. Then select the option that says "Choose a specific version of R" and highlight "[64-bit] C:\\Program Files\\R\\R-4.1.2"
<p><center>
`r knitr::include_graphics("images/version.png")`
</p></center>
3) Click "Ok" twice. You may be prompted to restart the program. If so, go ahead and do so.
## Testing getcdw
Now we can run a test query from the data warehouse. Start by loading the library called `getcdw`:
```{r}
library(getcdw)
```
Copy the above into your console and press enter. It will appear that nothing happened, but that actually means everything is fine.
Now all we need to do is run a simple query to test the connection. We'll do this by entering a SQL query into the console and seeing if it returns valid data (which would mean that you are able to connect to the database). When you do so, you'll be prompted for some information, please read the instructions below and make sure you understand what you're supposed to enter:
```{r, eval = FALSE}
get_cdw("select entity_id from cdw.d_entity_mv where entity id = 1234")
```
When you do this for the first time, you will be prompted for *three* pieces of information:
1) Your "UID": This is your username to log-in to the database. Unless you were told otherwise, this should be the same as your CalNet ID.
<p><center>
`r knitr::include_graphics("images/UID.png")`
</center></p>
2) Your "PWD": This is the password to log-in to the database. You will have received the password from IST and may be a random string of letters and numbers.
<p><center>
`r knitr::include_graphics("images/PWD.png")`
</center></p>
3) Finally, you'll be prompted for a "secret passphrase." Instead of having you remember the password given to you by IST, getcdw allows you to pick your own secret passphrase (this can be anything, from a single word to a memorable sentence, or whatever you think is easy for you to remember but not easy for someone else to guess).
<p><center>
`r knitr::include_graphics("images/passphrase.png")`
</center></p>
After this step, you will only ever be required to enter your secret passphrase -- you will no longer be prompted for your database UID or PWD. (But you can always reset them if necessary, using reset_credentials(dsn = "URELUAT").)
If everything is working properly, you should see something like this:
```{r, echo = FALSE}
get_cdw("select entity_id from cdw.d_entity_mv where entity_id = 1234")
```
If that's what you see, you are all set!