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.
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
Alright so now that we've got our data from DynamoDB accessible by Quicksight, it's time to create a dataset.
- Create a new data set from an Athena data source
- Select the catalog that you created in the Athena guide
- Select the table that we created in our DynamoDB guide
- Hit visualize
- Make sure to schedule a refresh of the data, this should align with your use case
So now we have a default analysis created from a dataset, the fun begins.
It's a good time to review our use cases for the dashboard, these will drive the visualisations that we create.
- To deep dive historical data and identify correlations
- To gain insight into current financial state
- 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.
- Date, value, description, category
- 12 month and 1 week view
Create weekly report
- Week in review dashboard
- Daily spending threshold
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
- List transactions, ordered by date
- Track income/expenditure in key areas
- Conditional formatting indicates 'Good', 'Bad' and 'Warning' levels of spending
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.
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.
To create a recurring report, navigate to the dashboard you published and select 'Share' > 'Email Report'
To create an alert you need to have a KPI visualisation, the rest is simple.
At this point you will have:
- Athena data source configured
- Quicksight configured
- Analysis and visualisations created
- Reporting and alerts configured
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.