A dashboard for the World Bank / IFC.
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.
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
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)
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
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.
(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.
.csv
and files to flat_files
:├── factors.csv
├── fig_ssa_addtional_details.csv
├── longevity_data.csv
├── portfolio_funding_data.csv
└── portfolio_volume.csv
In production the database will be maintained by the World Bank. For development purposes, one can create a database locally for testing.
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.
Data were emailed to developers on January 7, 2018. Data are downloadable (to authorized collaborators) at https://drive.google.com/open?id=1EtT8CmyL3XktXs49YXI-XhYtMcSfFhYY.
as_portfolio (with data).zip
file from the above URL into the data
directory of this repository..zip
file's contents in place. .zip
file.psql
to get into an interactive postgresql console.portfolio
database by running: CREATE DATABASE portfolio
.\connect portfolio;
portfolio
schema: create schema portfolio;
cd data
psql -d portfolio -f as_portfolio\ \(with\ data\).sql
cd ..
psql portfolio
\dt portfolio.*
should return: List of relations
Schema | Name | Type | Owner
-----------+--------------+-------+---------
portfolio | as_portfolio | table | joebrew
(1 row)
cd data
psql -d portfolio -f run_insert.sql
psql portfolio
) and confirm the presence of the as_results
relation: \dt portfolio.*
should return 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()
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()
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()
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()
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()
The portfolio_indicators
table needs to be set up as well.
> portfoliodash::create_portfolio_indicators_db()
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';
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
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
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)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.