In this post, I will be discussing as an example how an automobile manufacturing company could utilize QuickSight to analyze their sales data and make better decisions. We will also learn how to best optimize the QuickSight operational cost structure by using SPICE engine to ingest source data at certain recurring intervals from Athena queries. This has two major advantages : dashboards and analyses load quickly as the data source is within SPICE. Secondly, cost of data ingestion is also brought down as Athena is queried only to refresh the data load in SPICE.
We will look at a sales dashboard, created using data-sets prepared from data in refined zone in a DataLake created using LakeFormation. A Data engineering pipeline writes data to this refined zone with year and month partitions every hour.
In case you wish to build a similar thing and follow along, below is the link to raw datasets:
https://github.com/koushik-bitzop/data-sets/tree/master/sales2016-2018
Creating a SPICE based Athena Data-set:
Select Athena as the data set source:
Select use custom query.
Select Edit/Preview data and then choose data source as SPICE and click on Finish.
Once query successfully ran and you could see the data, click on the Save and Visualise.
In case you want to add any calculated fields or change data types you could do that in the red highlighted section shown above.
I have discussed in detail here in my previous articles Visualizing Multiple Datasets in AWS QuickSight and Adding User-Interactivity to AWS QuickSight Dashboards.
Refresh Schedule for Data-sets:
Depending on how frequent new data is arrived you could schedule the refresh. For every refresh an Athena query is executed and the results are imported into SPICE.
Note:
- In this example, Quicksight SPICE pull data refresh is whole data, not incremental.
- It is not possible to pass quicksight pass pushdown predicates (variables) from filters in dashboard to Athena. So if you want to look at a rolling window of data such as past 24 hours or past one month or past 6 months, we can use a WHERE clause in the Athena source query to fetch just those records. Also, if the data is partitioned by year and month, only required data is scanned thereby further saving on costs.
A lowdown on QuickSight Operating cost with this architecture:
We are looking at two main cost components:
- Athena – S3 data scan costs
- QuickSight Infrastructure costs
Athena – S3 data scan costs:
Athena pricing for successful queries:
1TB scan = 5$
S3 storage cost not included.
| No. of queries | Data scanned in S3 | Scheduled Refresh | Total Data scanned (monthly) | Bill estimated (monthly) | Bill estimated (annual) |
| 1 | 150 to 210 KB | Hourly | 1*24*30*210KB = 0.0001512TB | 0.000756$ | 0.009072$ |
Above numbers are a bit low to make an inference. Let us say, you have 4 such queries (each query is scanning around 150 to 200 MB) powering the dashboard and SPICE ingests this data once every hour.
| No. of queries | Data scanned in S3 | Scheduled Refresh | Total Data scanned (monthly) | Bill estimated (monthly) | Bill estimated (annual) |
| 4 | 150 to 200 MB | Hourly | 4*24*30*300MB = 576GB or 0.57TB | $2.88 | $34.56 |
In case, we do not use SPICE to load this data from Athena in an hourly fashion and instead use Athena query as the direct source, then cost of the dashboards would increase proportionately with each query. So as an example, if the dashboards are being viewed at a rate of 1000 views per hour (and each dashboard has 4 source queries), then the cost above would be multiplied by a staggering 1000 times! and the annual bill would be an eye popping $ 34,560.
QuickSight infrastructure cost (Standard Edition):
No charge for readers. $9 for Author with annual subscription.
| User type | No. of users | Bill estimated (monthly) | Bill estimated (annual) |
| Author | 1 | $9 | $108 |
| Reader | 3 | $0 | $0 |
| Total | $9 pm | $108 pa |
Note: For Enterprise edition, Readers are billed $0.30 for a 30-minute session up to a maximum charge of $5/reader/month for unlimited use. Authors are billed $18 with annual subscription.
For SPICE additional capacity $0.25/GB/standard and $0.38/GB/enterprise.
So overall we can see that using SPICE with a periodic data refresh causes the costs to be optimized in a smart way. That’s it folks. I hope it was helpful. For any queries, drop them in the comments section.
This story is authored by Koushik. Koushik is a software engineer and a keen data science and machine learning enthusiast.
Comments