README.md

Portfolio dashboard

A dashboard for the World Bank / IFC.

To-do

Overall status

This dashboard is far from being finished. Its overall content is based on a somewhat complex excel file. In addition to trying to emulate the excel file's visualizations closely, the dashboard should have user- and group-specific features and associated privileges. This will require not only typical R shiny programming, but also substantial interaction with the database.

Tasks

Developer's guide

Installing this package

Clone this repository, so that you can make modifications to some of the underlying code, data, and credentials. Do so like this:

$ git clone https://github.com/databrew/portfoliodash

Building the app as a package

Having now set up your credentials, document and install the package from within R like this (make sure you're in the "portfoliodash" directory):

library(devtools)
document('.')
install('.')

You'll now have the package on your system. You can confirm this by running:

library(portfoliodash)

Connecting to the database

Next cd into the portfoliodash directory. You'll note a credentials/credentials.yaml file. This should have one of the w following formats:

Format 1: for running on WB servers:

dbname: portfolio
host: "w0lxsfigssa01"
port: 5432
user: "rscript"
password: <PASSWORD GOES HERE>

Format 2: for running on AWS servers:

host: "databrewdb.cfejspjhdciw.us-east-2.rds.amazonaws.com"
port: 8080
dbname: portfolio
user: "worldbank"
password: <PASSWORD GOES HERE>
psql --host=databrewdb.cfejspjhdciw.us-east-2.rds.amazonaws.com --port=8080 --username=worldbank --dbname=dev

Copying data from database to local

pg_dump -h figssamel1.cosjv4zx2mww.us-east-1.rds.amazonaws.com -U postgres -f ~/Desktop/dump.sql ARL

If you're using a version control system (like git), be careful: the credentials/credentials.yaml file is not explicitly git-ignored, so you should be sure not to git add it, less you risk exposing your credentials to people who shouldn't have them.

Getting data onto local database

(The below only applies to database managers, not an R-only programmer or someone just running the app.)

You've now set up the package, but you also need to set up the data on which this package relies. There are two data dependencies: some flat files, and a PostgreSQL database.

Flat files

├── factors.csv
├── fig_ssa_addtional_details.csv
├── longevity_data.csv
├── portfolio_funding_data.csv
└── portfolio_volume.csv

The database

In production the database will be maintained by the World Bank. For development purposes, one can create a database locally for testing.

Production

The production database is expected to be named "portfolio", and to have the following 7 relations:

                 List of relations
  Schema   |         Name         | Type  |  Owner  
-----------+----------------------+-------+---------
 portfolio | as_portfolio         | table | joebrew
 portfolio | as_results           | table | joebrew
 portfolio | portfolio_indicators | table | joebrew
 portfolio | portfolio_projects   | table | joebrew
 portfolio | portfolio_users      | table | joebrew
 portfolio | portfolios           | table | joebrew
 portfolio | users                | table | joebrew

If you don't have a "portfolio" database set up, or if you do but need to create one of the above relations, continue reading.

Setting up the database from scratch.

Data were emailed to developers on January 7, 2018. Data are downloadable (to authorized collaborators) at https://drive.google.com/open?id=1EtT8CmyL3XktXs49YXI-XhYtMcSfFhYY.

Setting up the as_portfolio table
             List of relations
  Schema   |     Name     | Type  |  Owner  
-----------+--------------+-------+---------
 portfolio | as_portfolio | table | joebrew
(1 row)
Setting up the as_results table
                 List of relations
  Schema   |         Name         | Type  |  Owner  
-----------+----------------------+-------+---------
 portfolio | as_portfolio         | table | joebrew
 portfolio | as_results           | table | joebrew

Then, populate the as_results relation with a csv sent by Soren.

> library(portfoliodash)
> portfoliodash::populate_as_results()
Setting up the users table

The users table contains information on application users: id, email address, upi, etc. To create an initial users table, run the following in R:

> portfoliodash::create_users_db()
Setting up the portfolio_projects table

The portfolio_projects table is meant to store portfolios are associated with which projects. Each portfolio is associated with > 0 projects. To set up an initial table in R, run:

> portfoliodash::create_portfolio_projects_db()
Setting up the portfolio_users table

The portfolio_users table is meant to store which portfolios are associated with each user (a user having between 0 and inf portfolios). To create an initial table, run the following in R:

> portfoliodash::create_portfolio_users_db()
Setting up the portfolios table

The portfolios table is meant to store which projects are associated with which portfolios. To set it up initially, run the following in R:

> portfoliodash::create_portfolios_db()
Setting up the portfolio_indicators table

The portfolio_indicators table needs to be set up as well.

> portfoliodash::create_portfolio_indicators_db()
Inspecting the database

The database is now set up and ready for use. To inspect the entire schema run the following from the psql console:

select table_schema, table_name, column_name, data_type from information_schema.columns where table_schema = 'portfolio';
Creating a dump

Having now created the database from scratch, consider generating a dump using the pg_dump utility (for the purposes of backup or upload to AWS servers):

pg_dump -d portfolio -f /path/to/local/destination.sql
Getting data onto AWS database

This section only applies to the person managing the AWS database. Post-dump (ie, the above steps), data can be uploaded to the AWS database.

psql --host=figssamel1.cosjv4zx2mww.us-east-1.rds.amazonaws.com --port=5432 --username=postgres --dbname=ARL 
psql --host=databrewdb.cfejspjhdciw.us-east-2.rds.amazonaws.com --port=8080 --username=worldbank --dbname=ARL 
\i /path/to/dump/portfolio.sql

Create a user named worldbank, and grant privileges.

create role worldbank with password '<PASSWORD HERE>' login;
grant rds_superuser to worldbank;

Ctrl+d to log out, and then log in as worldbank to confirm it's working:

psql --host=portfolio.cfejspjhdciw.us-east-2.rds.amazonaws.com --port=8080 --username=worldbank --dbname=portfolio 

Other utilities

The portfoliodash package contains utilities for accessing the database. For further information, read the documentation associated with credentials_extract, credentials_connect and get_data. Here is a typical use case:

# Get credentials
creds <- credentials_extract()
# Connect to the database
co <- credentials_connect(creds)
# Get data
up <- get_data(query = 'SELECT * FROM portfolios.user_portfolio', connection_object = co)


databrew/portfoliodash documentation built on May 3, 2019, 1:47 p.m.