-
Notifications
You must be signed in to change notification settings - Fork 30
How to Create BFD Insights Quicksight
Billie Rose edited this page Sep 5, 2023
·
1 revision
Note: The prod version of each analysis is listed here because it is the only environment for
which we do this analysis. To set up for another environment, replace prod with the name of your
environment, such as prod or prod-sbx. Note that for Athena table names, replace any - with
_, such as prod_sbx instead of prod-sbx. (Athena doesn't like hyphens in table names).
- Go to QuickSight. It's an AWS service but for some reason it uses a different email-based permissions. You may need to request access.
- Create the dataset by going to Datasets on the left menu and choosing the New Dataset button on
the right.
- Choose Athena to open the "New Athena data source" modal.
- Name your data source. Example:
bfd-prod - Athena Workgroup:
bfd - Create Data Source.
- Name your data source. Example:
- "Choose Your Table" modal
- Catalog:
AwsDataCatalog - Database:
bfd-insights-bfd-prod - Table:
new_benes_by_day - Select
- Catalog:
- "Finish dataset creation" modal
- Select "Import to SPICE for quicker analysis"
- Check "Email owners when a refresh fails"
- Select "Edit/Preview data"
- "Data Prep"
- Select "Add Calculated Field"
- Name:
yearMonth - Calculation:
formatDate({day}, 'yyyy-MM') - Save
- Name:
- Select "Add Calculated Field"
- Name:
daily_running_count_total - Calculation:
runningSum(sum({bfd_new_benes}), [{day} ASC]) - Save
- Name:
- Select "Add Calculated Field"
- Name:
daily_running_count_ab2d - Calculation:
runningSum(sum({ab2d_new_benes}), [{day} ASC]) - Save
- Name:
- Select "Add Calculated Field"
- Name:
daily_running_count_bb2 - Calculation:
runningSum(sum({bb2_new_benes}), [{day} ASC]) - Save
- Name:
- Select "Add Calculated Field"
- Name:
daily_running_count_bcda - Calculation:
runningSum(sum({bcda_new_benes}), [{day} ASC]) - Save
- Name:
- Select "Add Calculated Field"
- Name:
daily_running_count_dpc - Calculation:
runningSum(sum({dpc_new_benes}), [{day} ASC]) - Save
- Name:
- Select "Add Calculated Field"
- Name:
daily_running_count_bulk - Calculation:
runningSum(sum({bulk_new_benes}), [{day} ASC]) - Save
- Name:
- Select "Add Calculated Field"
- Name:
monthly_running_count_total - Calculation:
runningSum(sum({bfd_new_benes}), [{yearMonth} ASC]) - Save
- Name:
- Select "Add Calculated Field"
- Name:
monthly_running_count_ab2d - Calculation:
runningSum(sum({ab2d_new_benes}), [{yearMonth} ASC]) - Save
- Name:
- Select "Add Calculated Field"
- Name:
monthly_running_count_bb2 - Calculation:
runningSum(sum({bb2_new_benes}), [{yearMonth} ASC]) - Save
- Name:
- Select "Add Calculated Field"
- Name:
monthly_running_count_bcda - Calculation:
runningSum(sum({bcda_new_benes}), [{yearMonth} ASC]) - Save
- Name:
- Select "Add Calculated Field"
- Name:
monthly_running_count_dpc - Calculation:
runningSum(sum({dpc_new_benes}), [{yearMonth} ASC]) - Save
- Name:
- Select "Add Calculated Field"
- Name:
monthly_running_count_bulk - Calculation:
runningSum(sum({bulk_new_benes}), [{yearMonth} ASC]) - Save
- Name:
- Select "Publish & Visualize"
- Select "Add Calculated Field"
- Choose Athena to open the "New Athena data source" modal.
- Analysis screen.
- Create Calculated Fields that Require Aggregation Functions (must be added to the Analysis,
not the Dataset)
- Select "Add" then "Add Calculated Field"
- Name:
monthly_unique_count_total - Calculation:
sum({bfd_new_benes}, [yearMonth]) - Save
- Name:
- Select "Add" then "Add Calculated Field"
- Name:
monthly_unique_count_ab2d - Calculation:
sum({ab2d_new_benes}, [yearMonth]) - Save
- Name:
- Select "Add" then "Add Calculated Field"
- Name:
monthly_unique_count_bb2 - Calculation:
sum({bb2_new_benes}, [yearMonth]) - Save
- Name:
- Select "Add" then "Add Calculated Field"
- Name:
monthly_unique_count_bcda - Calculation:
sum({bcda_new_benes}, [yearMonth]) - Save
- Name:
- Select "Add" then "Add Calculated Field"
- Name:
monthly_unique_count_dpc - Calculation:
sum({dpc_new_benes}, [yearMonth]) - Save
- Name:
- Select "Add" then "Add Calculated Field"
- Name:
monthly_unique_count_bulk - Calculation:
sum({bulk_new_benes}, [yearMonth]) - Save
- Name:
- Select "Add" then "Add Calculated Field"
- Create the "All APIs" sheet. (Use the + symbol to add a new sheet)
- Add a new visualization of type "Stacked area line chart" and configure:
- X axis:
day - Value:
daily_running_count_total - Title:
Unique Medicare Enrollees (All APIs) - X-axis title:
Date - Y-axis title:
Cumulative Unique Medicare Enrollees
- X axis:
- Add a new visualization of type "KPI" and configure:
- Value: bfd_new_benes (Sum)
- Title:
Unique Medicare Enrollees (All APIs)
- Add a new visualization of type "Stacked area line chart" and configure:
- Create the "Bulk APIs" sheet. (Use the + symbol to add a new sheet)
- Add a new visualization of type "Stacked area line chart" and configure:
- X axis:
day - Value:
daily_running_count_bulk - Title:
Unique Medicare Enrollees (Bulk APIs) - X-axis title:
Date - Y-axis title:
Cumulative Unique Medicare Enrollees
- X axis:
- Add a new visualization of type "KPI" and configure:
- Value: bulk_new_benes (Sum)
- Title:
Unique Medicare Enrollees (Bulk APIs)
- Add a new visualization of type "Stacked area line chart" and configure:
- Create the "AB2D" sheet. (Use the + symbol to add a new sheet)
- Add a new visualization of type "Stacked area line chart" and configure:
- X axis:
day - Value:
daily_running_count_ab2d - Title:
Unique Medicare Enrollees (AB2D) - X-axis title:
Date - Y-axis title:
Cumulative Unique Medicare Enrollees
- X axis:
- Add a new visualization of type "KPI" and configure:
- Value: ab2d_new_benes (Sum)
- Title:
Unique Medicare Enrollees (AB2D)
- Add a new visualization of type "Stacked area line chart" and configure:
- Create the "BB2" sheet. (Use the + symbol to add a new sheet)
- Add a new visualization of type "Stacked area line chart" and configure:
- X axis:
day - Value:
daily_running_count_bb2 - Title:
Unique Medicare Enrollees (BB2) - X-axis title:
Date - Y-axis title:
Cumulative Unique Medicare Enrollees
- X axis:
- Add a new visualization of type "KPI" and configure:
- Value: bb2_new_benes (Sum)
- Title:
Unique Medicare Enrollees (BB2)
- Add a new visualization of type "Stacked area line chart" and configure:
- Create the "BCDA" sheet. (Use the + symbol to add a new sheet)
- Add a new visualization of type "Stacked area line chart" and configure:
- X axis:
day - Value:
daily_running_count_bcda - Title:
Unique Medicare Enrollees (BCDA) - X-axis title:
Date - Y-axis title:
Cumulative Unique Medicare Enrollees
- X axis:
- Add a new visualization of type "KPI" and configure:
- Value: bcda_new_benes (Sum)
- Title:
Unique Medicare Enrollees (BCDA)
- Add a new visualization of type "Stacked area line chart" and configure:
- Create the "DPC" sheet. (Use the + symbol to add a new sheet)
- Add a new visualization of type "Stacked area line chart" and configure:
- X axis:
day - Value:
daily_running_count_dpc - Title:
Unique Medicare Enrollees (DPC) - X-axis title:
Date - Y-axis title:
Cumulative Unique Medicare Enrollees
- X axis:
- Add a new visualization of type "KPI" and configure:
- Value: dpc_new_benes (Sum)
- Title:
Unique Medicare Enrollees (DPC)
- Add a new visualization of type "Stacked area line chart" and configure:
- Create the "Monthly" sheet. (Use the + symbol to add a new sheet)
- Add a new visualization of type "KPI" and configure:
- Value: bfd_new_benes (Sum)
- Title:
Total Unique Medicare Enrollees
- Add a new visualization of type "KPI" and configure:
- Value: ab2d_new_benes (Sum)
- Title:
AB2D Total Unique Medicare Enrollees
- Add a new visualization of type "KPI" and configure:
- Value: bb2_new_benes (Sum)
- Title:
BB2 Total Unique Medicare Enrollees
- Add a new visualization of type "KPI" and configure:
- Value: bcda_new_benes (Sum)
- Title:
BCDA Total Unique Medicare Enrollees
- Add a new visualization of type "KPI" and configure:
- Value: dpc_new_benes (Sum)
- Title:
DPC Total Unique Medicare Enrollees
- Add a new visualization of type "Table" and configure:
- Group by:
yearMonth - Value:
monthly_running_count_* - Title:
Cumulative Unique Medicare Enrollees - Group-by column names:
- yearMonth ->
Month
- yearMonth ->
- Value column names:
- monthly_running_count_ab2d ->
AB2D - monthly_running_count_bb2 ->
BB2 - monthly_running_count_bcda ->
BCDA - monthly_running_count_dpc ->
DPC - monthly_running_count_bulk ->
Bulk APIs - monthly_running_count_total ->
All APIs
- monthly_running_count_ab2d ->
- Group by:
- Add a new visualization of type "Table" and configure:
- Group by:
yearMonth - Value:
monthly_unique_count_* - Title:
New Unique Medicare Enrollees - Group-by column names:
- yearMonth ->
Month
- yearMonth ->
- Value column names:
- monthly_unique_count_ab2d ->
AB2D - monthly_unique_count_bb2 ->
BB2 - monthly_unique_count_bcda ->
BCDA - monthly_unique_count_dpc ->
DPC - monthly_unique_count_bulk ->
Bulk APIs - monthly_unique_count_total ->
All APIs
- monthly_unique_count_ab2d ->
- Group by:
- Add a new visualization of type "KPI" and configure:
- Create the "Daily" sheet. (Use the + symbol to add a new sheet)
- Add a new visualization of type "KPI" and configure:
- Value: bfd_new_benes (Sum)
- Title:
Total Unique Medicare Enrollees
- Add a new visualization of type "KPI" and configure:
- Value: ab2d_new_benes (Sum)
- Title:
AB2D Total Unique Medicare Enrollees
- Add a new visualization of type "KPI" and configure:
- Value: bb2_new_benes (Sum)
- Title:
BB2 Total Unique Medicare Enrollees
- Add a new visualization of type "KPI" and configure:
- Value: bcda_new_benes (Sum)
- Title:
BCDA Total Unique Medicare Enrollees
- Add a new visualization of type "KPI" and configure:
- Value: dpc_new_benes (Sum)
- Title:
DPC Total Unique Medicare Enrollees
- Add a new visualization of type "Table" and configure:
- Group by:
day - Value:
daily_running_count_* - Title:
Cumulative Unique Medicare Enrollees - Group-by column names:
- day ->
Date
- day ->
- Value column names:
- daily_running_count_ab2d ->
AB2D - daily_running_count_bb2 ->
BB2 - daily_running_count_bcda ->
BCDA - daily_running_count_dpc ->
DPC - daily_running_count_bulk ->
Bulk APIs - daily_running_count_total ->
All APIs
- daily_running_count_ab2d ->
- Group by:
- Add a new visualization of type "Table" and configure:
- Group by:
day - Value:
*_new_benes(Sum) - Title:
New Unique Medicare Enrollees - Group-by column names:
- day ->
Date
- day ->
- Value column names:
- ab2d_new_benes ->
AB2D - bb2_new_benes ->
BB2 - bcda_new_benes ->
BCDA - dpc_new_benes ->
DPC - bulk_new_benes ->
Bulk APIs - bfd_new_benes ->
All APIs
- ab2d_new_benes ->
- Group by:
- Add a new visualization of type "KPI" and configure:
- Create Calculated Fields that Require Aggregation Functions (must be added to the Analysis,
not the Dataset)
- Create the dashboard.
- While still on the analysis screen, in the upper-right, click Share > Publish Dashboard. Title it "BFD Unique Medicare Enrollees".
- The default options should otherwise be fine, so click Publish Dashboard.
- Make the dashboard public.
- While still on the dashboard screen, in the upper right, click Share > Share dashboard.
- On the left, there is a toggle under "Enable access for" labeled "Everyone in this account". Turn it on.
- On the left, there is also a toggle labeled "Discoverable in QuickSight". Turn that one on also.
- Set the SPICE refresh.
- Return to the main QuickSight index.
- Select "Datasets" on the left.
- Click on the dataset you created in step 2 above.
- Click on the Refresh tab.
- In the upper right, click on "Add New Schedule".
- The defaults should be what you want.
- "Full Refresh"
- Timezone should be "America/New_York"
- Start time should be " 11:59 PM"
- Frequency "Daily"
- Save.
- The defaults should be what you want.
- Home
- For BFD Users
- Making Requests to BFD
- API Changelog
- Migrating to V2 FAQ
- Synthetic and Synthea Data
- BFD SAMHSA Filtering