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:
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.
.csvand files to
├── 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).zipfile from the above URL into the
datadirectory of this repository.
.zipfile's contents in place.
psqlto get into an interactive postgresql console.
portfoliodatabase by running:
CREATE DATABASE portfolio.
create schema portfolio;
psql -d portfolio -f as_portfolio\ \(with\ data\).sql
\dt portfolio.*should return:
List of relations Schema | Name | Type | Owner -----------+--------------+-------+--------- portfolio | as_portfolio | table | joebrew (1 row)
psql -d portfolio -f run_insert.sql
psql portfolio) and confirm the presence of the
\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:
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:
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:
portfolios table is meant to store which projects are associated with which portfolios. To set it up initially, run the following in R:
portfolio_indicators table needs to be set up as well.
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
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
portfoliodash package contains utilities for accessing the database. For further information, read the documentation associated with
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.