Create QuickSight Datasets

Verify SPICE capacity

The majority of the Datasets created for this dashboard will be in SPICE (Super-fast, Parallel, In-memory Calculation Engine) Query mode, and therefore the SPICE capacity should be verified before taking the next steps.

  1. Click the top right user menu and choose Manage QuickSight

  2. Click SPICE capacity from the left. The following information displays:

    • The Total SPICE capacity section displays the total amount of used and unused SPICE capacity. A bar graph shows how much of this storage space is in each of the following categories for this AWS account in the AWS Region that’s currently selected in QuickSight:

      • Purchased SPICE capacity – This is the additional SPICE capacity you previously purchased.

      • Free SPICE capacity bundled with QuickSight – This is the total default capacity associated with your paid users.

    Hover over any section of the meter to see details on that capacity type.

    • The SPICE usage section displays the total amount of the used and unused SPICE capacity. A bar graph shows how much of this storage space is in each of the following categories for this AWS account in the AWS Region that’s currently selected in QuickSight:

      • Used SPICE capacity – This is the used portion of the default SPICE capacity that you get for each user.

      • Unused SPICE capacity – This is the unused portion of the default SPICE capacity that you get for each user.

      • Releasable unused capacity – This is the purchased capacity that isn’t in use, and so can be released to reduce costs.

  3. Click purchase more capacity if you are running low on Unused capacity NOTE: Purchased SPICE capacity can be released after purchase if it is not being used.

Create Datasets

  1. Go to the QuickSight service homepage inside your account. Be sure to select the correct region from the top right user menu or you will not see your expected tables

    Image of Admin drop down menu in right hand corner of QuickSight Portal. Displays how to change region from this menu.

  2. From the left hand menu, choose Datasets

    Image of left hand QuickSight menu with Datasets highlighted.

  3. Click New dataset displayed in the top right corner

    Image of QuickSight Datasets page with New dataset button in the top right corner highlighted.

  4. Choose Athena as your Data Source

    Image of Sources available to create a new data set with. Athena is highlighted.

  5. Enter a data source name of Cost_Dashboard and click Create data source

    Image showing the pop up box that appears after choosing your data source. The Data source name is Cost_Dashboard and this is highlighted. Also highlighted is the Create data source button in the bottom right hand corner of this pop up.

  6. Select the database which holds the views you created (reference Athena if you’re unsure which one to select), and the summary_view table, then click Edit/Preview data

    Image of screen that appears after clicking Create data source. The Database selection is highlighted as being “athenacurncfn_gov_reporting” and the Table selection is highlighted as being “summary_view”. The button “Edit/Preview data” is highlighted as the next action.

  7. Select SPICE to change your Query mode

    Image showing the Query mode selection in the top left hand corner of theEdit dataset view. SPICE is highlighted.

  8. Select Save

    Image showing the Edit dataset view. The Save button in the top right corner is highlighted.

  9. Select the summary_view dataset

    Image of QuickSight Datasets page with summary_view dataset highlighted.

  10. Click Schedule refresh

    Image of the window that appears when clicking on a dataset Name. Schedule Refresh is highlighted.

  11. Click Create

    Image of the window that appears when clicking Schedule Refresh in the previous step. Create button in the bottom right hand corner is highlighted.

  12. Enter a daily schedule, in the appropriate time zone and click Create

    Image of the window that appears when clicking Create in the previous step. Time zone, Number of Repeats & Starting Time fields are highlighted.

  13. Click Cancel to exit

  14. Click x to exit

  15. Repeat steps 3-14 to create data sets with the remaining Athena views. Select each of the following views as the table in step 6 & step 9:

  • ec2_running_cost
  • compute_savings_plan_eligible_spend
  • s3_view

Make sure to reuse the existing Athena datasource by scrolling to the bottom on the Datasource create/select page when creating a new Dataset Image of a reusable Datasource

When this step is completed, your Datasets tab should have 4 new SPICE labelled Datasets.

  1. Select the summary_view Data Set

  2. Click Edit Data Set

  3. Click Add Data

  4. Choose your ri_sp_mapping view and click Select

  5. Click the two circles to open the Join configuration, then select Left to change your join type

  6. Click Add a new join clause twice so you have 3 join clauses to configure in total. Configure the 3 join clauses as below, then click Apply

    • ri_sp_arn = ri_sp_arn_mapping
    • payer_account_id = payer_account_id_mapping
    • billing_period = billing_period_mapping

  7. Click Add Data

  8. Choose your account_map view and click Select

  9. Click the two circles to open the Join configuration, then select Left to change your join type

  10. Configure the join clause to linked_account_id = account_id, then click Apply

  11. Click Save

  12. Select the s3_view Dataset

  13. Click Edit Data Set

  14. Click Add Data

  15. Choose your account_map view and click Select

  16. Click the two circles to open the Join configuration, then select Left to change your join type

  17. Configure the join clause to linked_account_id = account_id, then click Apply

  18. Click Save

  19. Select the ec2_running_costs Dataset

  20. Click Edit Data Set

  21. Click Add Data

  22. Choose your account_map view and click Select

  23. Click the two circles to open the Join configuration, then select Left to change your join type

  24. Configure the join clause to linked_account_id = account_id, then click Apply

  25. Click Save

  26. Select the compute_savings_plan_eligible_spend Dataset

  27. Click Edit Data Set

  28. Click Add Data

  29. Choose your account_map view and click Select

  30. Click the two circles to open the Join configuration, then select Left to change your join type

  31. Configure the join clause to linked_account_id = account_id, then click Apply

  32. Click Save

  33. Click New dataset displayed in the top right corner of QuickSight

  34. Choose Athena as your Data Source

  35. Enter a data source name of customer_all and click Create data source

  36. Select the database which holds the views you created (your databasename should begin with athenacurcfn), and the Table that reflects the name of your Cost & Usage Report, then click Edit/Preview data

  37. Keep the Query mode as Direct query

  38. In the Name your data set field at the top of the page, rename the data set to customer_all

  39. Repeat steps 22-26, but configure this join clause in step 25:

    • line_item_usage_account_id = account_id

This completes the QuickSight Data Preparation section. Next up is the Import process to generate the QuickSight Dashboard.