Create the Usage Data Source

We will combine the pricing information with our Cost and Usage Report (CUR). This will give us a usage data source which contains a summary of your usage at an hourly level, with multiple pricing dimensions.

  1. Go to the Athena service page: Images/home_athena.png

  2. Run the following query to create a single pricing data source, combining the OD and SP pricing:

     CREATE VIEW pricing.pricing AS SELECT
     sp.col18 AS Region,
     sp.col9 AS OS,
     REPLACE(od.col19, '"') AS InstanceType,
     REPLACE(od.col36, '"') AS Tenancy,
     REPLACE(od.col9, '"') AS ODRate,
     sp.col4 AS SPRate
     FROM pricing.sp_pricedata sp
     JOIN pricing.od_pricedata od ON
     ((sp.col8 = REPLACE(od.col47, '"'))
     AND (sp.col9 = REPLACE(od.col48, '"')))
     WHERE od.col9 IS NOT NULL
     AND sp.col18 NOT LIKE 'Any'
     AND sp.col10 LIKE 'No Upfront'
     AND sp.col11 like '1'
  3. Next we’ll join the CUR file with that pricing source as a view. Edit the following query, replace costmaster.costmasterfile with your existing database name and tablename of your CUR, then run the rollowing query:

     CREATE VIEW costmaster.SP_Usage AS SELECT
     to_unixtime(costmaster.line_item_usage_start_date) AS EpochTime,
     SUM(costmaster.line_item_unblended_cost) AS ODPrice,
     SUM(costmaster.line_item_unblended_cost*(cast(pr.SPRate AS double)/cast(pr.ODRate AS double))) SPPrice,
     abs(SUM(cast(pr.SPRate AS double)) - SUM (cast(pr.ODRate AS double))) / SUM(cast(pr.ODRate AS double))*100 AS DiscountRate,
     SUM(costmaster.line_item_usage_amount) AS InstanceCount
     FROM costmaster.costmasterfile costmaster
     JOIN pricing.pricing pr ON (costmaster.product_location = pr.Region)
     AND (costmaster.line_item_operation = pr.OS)
     AND (costmaster.product_instance_type = pr.InstanceType)
     AND (costmaster.product_tenancy = pr.Tenancy)
     WHERE costmaster.line_item_product_code LIKE '%EC2%'
     AND costmaster.product_instance_type NOT LIKE ''
     AND costmaster.product_operating_system NOT LIKE 'NA'
     AND costmaster.line_item_unblended_cost > 0
     AND costmaster.line_item_line_item_type like 'Usage'
     GROUP BY costmaster.line_item_usage_account_id,
     ORDER BY costmaster.line_item_usage_start_date ASC,
     DiscountRate DESC

The code above will capture ONLY on-demand usage, as defined by costmaster.line_item_line_item_type like ‘Usage’. You can remove this to include Savings Plan usage, to see total commitment you should have, instead of additional commitment required.

  1. Verify the data source is setup by editing the following query, replace costmaster. with the name of the database and run the following query:

     SELECT * FROM costmaster.sp_usage limit 10;

You now have your usage data source setup with your pricing dimensions. You can modify the queries above to add or remove any columns you want in the view, which can later be used to visualize the data, for example tags.