Data Warehouse Set-up

Obtain Permissions

To first gain access, you must be whitelisted and be give the appropriate permissions. Send your IP address to Jake. If you need to update your IP address at a later date, you can send a request to Karly or John.

Info

Starting March 1st, 2023, you can now use the VPN to access the databases without whitelisting your IP address. Please see below on how to connect to the VPN.

Azure Security Groups

To access our data warehouse or our PowerBI Dashboards, you will need to be added to a security group. To add someone to a security group please see External Access

Please contact Jacob Neely for the Security Groups

Web Portal

Test access your access by heading to Azure and logging in with your Special Olympics account. Once there, find Prod_v01xx SQL database and click on Query editor (preview) on the left menu. Continue as Active Directory authentication. There should be a green mark next to your username. From there you can test out a query.

Example:

SELECT TOP 10 *
FROM Core.Discipline

DataGrip

DataGrip is a database management environment for your computer. To set it up:

Send a request to Tim for a license or by submitting a helpdesk ticket.
Download DataGrip from Jetbrains to your computer and log in.
Enter in details to set up DataGrip.
Click on new project and create a name for your project (Example: “Prod”).
Go to Data Source Properties and click the + button to add a data source.
Add an Azure SQL data source.

Make sure you download missing driver files at the bottom of the page.

Enter the following credentials:

Input	Response
Host	soidatawarehouse.database.windows.net
Port	1433
Authentication	Azure Active Directory Password
Database	Prod_v01xx
User	USERNAME@specialolympics.org
Password	Your SO Microsoft password

Click on test the connection. Once successful, click Apply, then Ok.

On the left, Prod_v01xx should be available now. To see the tables, click on the number in the square and select all the tables desired.

Repeat this process to add "Dev".

DataGrip

IP White-listing

If you are an admin for our warehouse (Jake gives access to that), then you can add IP addresses to be whitelisted for the data warehouse. To add IPs:

Firewall1

Go to online Azure portal
Navigate to either dev or prod (whitelist is shared across both)
Click "Set server firewall"
Click + and Add a firewall rule
Gie a name and make start and end IP addresses the same value
Scroll to bottom and click save

How to show permissions

SELECT DISTINCT pr.principal_id, pr.name, pr.type_desc, 
    pr.authentication_type_desc, pe.state_desc, pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id;

To get my user name

SELECT CURRENT_USER;  
GO

How to grant user permissions to the azure database

To change user permissions

GRANT SELECT to soigms_read

Giving sql azure data warehouse permissions

USE Dev_v01xx;
GRANT CREATE TABLE TO [example@specialolympics.org]

USE Dev_v01xx;
GRANT INSERT TO [example@specialolympics.org]

USE Dev_v01xx;
GRANT ALTER TO [example@specialolympics.org]

GRANT SELECT ON SCHEMA :: [Core] TO [example@specialolympics.org]

Using the VPN

SOI enterprise VPN (Forticlient) should already be installed on your device and configured. If not, please reach out to the helpdesk. Additionally, you must have multifactor authentication set up (have OKTA verify on your phone). Again please reach out to the helpdesk for any problems on this.

Open the Forticlient application.
Enter your username (uun) and VPN password tap on Login. Your username does not include the email address. For example, it is kjerman and not kjerman@specialolympics.org. You then use your password for that account.
Upon logging in you should be prompted for multifactor authentication. A pop-up will show up and ask you if you want to do a push notification or answer a question. I have only gotten it to work when I do the push notification. If you select the push notification, then a push notification from OKTA will pop up on your phone. You must accept that notification.
When you are connected, it should look like this:
After authenticating you should be in our enterprise network, which will allow you to tunnel to our cloud resources. Using the command terminal putty or data grip.

Accessing the VM

The Special Olympics Virtual Machine (VM) is used for many DevOps pipelines including an automation of python pipelines that extracts data from external sources and loads them in the correct format into our data warehouse. Additionally, you can get a VM built for you to access a Windows computer for things like PowerBI. Please contact Jake for more information.

Using Remote Desktop Connection Application

Sign in to the Azure Portal at https://portal.azure.com/.
Select the "Virtual Machines" option from the left-hand menu. This will take you to the virtual machines' dashboard.
Click on the VM that you want to access. This will take you to the VM's overview page.
On the VM overview page, you'll need to note down the Public IP address of the VM. You can find this information in the "Essentials" section of the page.
On your local machine, search for "Remote Desktop Connection" in the search bar and open the application.
In the Remote Desktop Connection application, type the public IP address of the VM you noted down earlier in the "Computer" field.
When prompted, enter the username and password that you used to create the VM.
Click "Connect" to access your Azure VM.

SSH into the VM

First, you need to ensure that you have an SSH client installed on your local machine. If you are using Windows, you can install an SSH client like PuTTY. If you are using a Mac or Linux, the SSH client should already be installed on your machine.
Next, you need to navigate to the Azure portal (https://portal.azure.com/) and sign in to your Azure account.
Once you are signed in, click on the "Virtual Machines" option from the left-hand menu.
Find the Virtual Machine that you want to access and click on it to open the VM details page.
On the VM details page, click on the "Connect" button at the top of the page.
In the "Connect" blade that appears, select the "SSH" option.
In the "SSH" blade, you will see a command that you can use to connect to the VM using SSH. This command will include the username, IP address, and port number needed to connect to the VM.
Open your local SSH client (e.g. PuTTY) and enter the IP address and port number provided in the SSH command from the Azure portal.
You will then be prompted to enter the username for the VM. This is also provided in the SSH command from the Azure portal.
Finally, you will be prompted to enter the password for the VM. This is the password you set when you created the VM.
Once you enter the correct username and password, you should now be connected to the VM via SSH.

Naming Conventions

There are many ways to approach naming conventions for virtual machines (VMs), and the best approach will depend on the specific needs and goals of your organization. Some common considerations when designing a VM naming convention include:

Making the names easily recognizable and memorable, so that they can be quickly and accurately referenced by administrators and other IT personnel.
Ensuring that the names are unique and do not conflict with any existing names or naming conventions in use within your organization.
Using a standardized format for the names, so that they can be easily sorted, grouped, and organized according to different criteria.
Including relevant information in the names, such as the purpose or role of the VM, its location or network, and its operating system or other software.
Avoiding using special characters or other elements that may cause problems with certain systems or applications.
One possible naming convention for VMs could be to use a combination of letters and numbers that reflect the purpose and location of the VM. For example, a VM that serves as a file server in the New York office might be named "NY-FS-001," while a web server in the London office might be named "LON-WS-002." This approach allows for easy identification and organization of VMs, while also providing a clear and consistent naming structure.

Special Olympics naming convention is going to be based on subscription and server type and maybe workflow

Example:

SOI-MDATA-LUNIX-DEV SOI-MDATA-LUNIX-QA SOI-MDATA-LUNIX-PROD

SOI-APPS-WIN-DEV SOI-APPS-WIN-QA SOI-APPS-WIN-PROD

Additional access to SOI data warehouse

DBeaver

Use the instructions below to download a program that will allow you to connect to SOI's data warehouse. This will enable you to run SQL code on your own.

DBeaver Instructions

Example SQL Queries

The file below contains examples of basic SQL code to navigate SOI's data warehouse.

SQL Examples