Creating an Automated Personal Finances Dashboard with AWS - Part 5 (Quicksight)
Tags: aws, quicksight, athena, dashboard
Published: 2022-01-04 07:04:02

G'day everyone, in the last post DynamoDB was configured and was receiving realtime transaction history.

In this post we get down to the really good stuff, creating a personal finances dashboard!

Quick side note: The purpose of this guide is technical rather than financial, so the underlying financial data has been scrambled.

### Prerequisite: Getting DynamoDB data into Quicksight
For this section I will defer to a fantastic article from another dev.to poster: 
[Using Athena data connectors to visualize DynamoDB data with AWS QuickSight](https://dev.to/aws-builders/finally-dynamodb-support-in-aws-quicksight-sort-of-2lbl)

### Quicksight Configuration
Alright so now that we've got our data from DynamoDB accessible by Quicksight, it's time to create a dataset.

1. Create a new data set from an Athena data source
2. Select the catalog that you created in the Athena guide
3. Select the table that we created in our DynamoDB guide
4. Hit visualize
5. Make sure to schedule a refresh of the data, this should align with your use case


![Schedule data refresh](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ef1xayv470cpjf2qqs99.PNG)

So now we have a default analysis created from a dataset, the fun begins.


### Review Use Cases
It's a good time to review our use cases for the dashboard, these will drive the visualisations that we create.

1. To deep dive historical data and identify correlations 
2. To gain insight into current financial state
3. To forecast future financial state and identify trends

At a first glance I think the first two are achievable in a simplified state given our current technical capabilities, the forecasting aspect will need to be addressed in a future revision. Let's break down those first two with some ideas on how we will achieve them.

#### Use Case: Historical data
Create visualisations 
    - Date, value, description, category
    - 12 month and 1 week view

#### Use Case: Current financial state
Create weekly report
    - Week in review dashboard
Create alert
    - Daily spending threshold



### Create Visualisations
There is an endless amount of possibilities when it comes to visualising data, but only a few that are useful.
- Pie chart
    - Break down spending by category
- Line chart
    - Break down spending by month
- Table
    - List transactions, ordered by date
- KPI
    - Track income/expenditure in key areas
    - Conditional formatting indicates 'Good', 'Bad' and 'Warning' levels of spending

#### 12 Month Dashboard

![12 Month Dashboard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qptjq9pc3edq1leh1lz9.PNG)
In this image we can see a simple dashboard that I've created, I'll create another one for the 1 week view that we will use for the weekly report. Duplicating sheets and visualisations in Quicksight is quick and intuitive - you'll find yourself using it frequently as it also copies across the filters.

#### 1 Week Dashboard

![1 Week Dashboard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ne5acy96iwah4mmsdgx4.PNG)
Not pictured - I included a daily KPI for spending that we will use for our spending alert.


### Creating reports and alerts
Now that we've created a baseline 12 month and 1 week dashboard, we can publish the analysis. This allows us greater functionality in the alerting and reporting space. 

#### Reports
To create a recurring report, navigate to the dashboard you published and select 'Share' > 'Email Report'

![Report Step 1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mzc52pguc3lpksr1pdr3.PNG)
I've chosen to send a report weekly at 9am Monday morning

![Report Step 2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/njdaj8s36on7xcnese1o.PNG)
Formatted for desktop and selecting the weekly sheet

![Report Step 3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dbzg749g9h2l9k97gmqw.PNG)
Select the email recipients

#### Alerts
To create an alert you need to have a KPI visualisation, the rest is simple.

![Create Alert](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4kkqz8j3w5kldibqejz2.PNG)
I've created an alert that emails me when my spending goes over $70/day in this example

At this point you will have:
    - Athena data source configured
    - Quicksight configured
    - Analysis and visualisations created
    - Reporting and alerts configured


![Updated Diagram](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y1xm85nwi855nig066oo.png)

Awesome, the dashboard works - the next step is to wrap everything in CDK to make sure that we can continue developing the platform in an efficient way.