Athena Query Updates

There are some conditions that cause cartesian product effect in your joins. Those conditions are when multiple product users are mapped to the same product or element 4.

In Athena, run an Update to the Claimed and Shared Resource Query.

Claimed Resource Query

CREATE OR REPLACE VIEW "finopscenter_claimed_resource_base_view" AS 
SELECT
  crtbl."accountid"
, rescur."line_item_usage_account_name" "account_name"
, crtbl."resourceid" "resourceid"
, crtbl."claimedby"
, crtbl."element1" "element1_id"
, crtbl."element2map"
, crtbl."element3map"
, crtbl."element4map"
, crtbl."elementmap_element2id" "element2_id"
, crtbl."elementmap_element3id" "element3_id"
, crtbl."elementmap_element4id" "element4_id"
, crtbl."reference" "workload"
, crtbl."isactive"
, crtbl."year"
, crtbl."releasedby"
, CAST(from_iso8601_timestamp(crtbl."startdate") AS timestamp) "cr_start_date"
, CAST(from_iso8601_timestamp(crtbl."enddate") AS timestamp) "cr_end_date"
, date_trunc('day', rescur."line_item_usage_start_date") "usage_date"
, rescur."bill_payer_account_id" "payer_account_id"
, rescur."bill_billing_entity" "billing_entity"
, rescur."line_item_product_code" "product_code"
, rescur."line_item_operation" "operation"
, rescur."line_item_line_item_type" "charge_type"
, rescur."line_item_usage_type" "usage_type"
, rescur."pricing_unit" "pricing_unit"
, rescur."product_region_code" "region"
, rescur."line_item_line_item_description" "item_description"
, rescur."line_item_legal_entity" "legal_entity"
, rescur."pricing_term" "pricing_term"
, rescur."product_from_location" "product_from_location"
, rescur."product_instance_type" "instance_type"
, rescur."product_product_family" "product_family"
, rescur."product_servicecode" "service"
, rescur."product_to_location" "product_to_location"
, rescur."reservation_reservation_a_r_n" "reservation_a_r_n"
, rescur."savings_plan_savings_plan_a_r_n" "savings_plan_a_r_n"
, element_at(rescur.product, 'product_name') "aws_product"
, am."percentage" "percentage"
, date_parse(am."startdate", '%m-%d-%Y') "am_start_date"
, date_parse(am."enddate", '%m-%d-%Y') "am_end_date"
, am.accounttype "account_type"
, 'Claimed' spending_type
, rescur."savings_plan_savings_plan_effective_cost" savings_plan_effective_cost
, rescur."reservation_effective_cost" reservation_effective_cost
, rescur."line_item_usage_amount" "usage_quantity"
, rescur."line_item_unblended_cost" unblended_cost
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'Usage') THEN rescur.line_item_unblended_cost ELSE 0 END)) claimed_cost
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'Credit') THEN rescur.line_item_unblended_cost ELSE 0 END)) credit
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'Fee') THEN rescur.line_item_unblended_cost ELSE 0 END)) fee
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'SavingsPlanCoveredUsage') THEN rescur.line_item_unblended_cost ELSE 0 END)) savings_plan_coverage
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'Tax') THEN rescur.line_item_unblended_cost ELSE 0 END)) tax
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'RIFee') THEN rescur.line_item_unblended_cost ELSE 0 END)) ri_fee
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'DiscountedUsage') THEN rescur.line_item_unblended_cost ELSE 0 END)) discounted_usage
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'SavingsPlanNegation') THEN rescur.line_item_unblended_cost ELSE 0 END)) savings_plan_negation
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'SavingsPlanRecurringFee') THEN rescur.line_item_unblended_cost ELSE 0 END)) saving_plan_recurring_fee
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'Refund') THEN rescur.line_item_unblended_cost ELSE 0 END)) refund
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'SavingsPlanUpfrontFee') THEN rescur.line_item_unblended_cost ELSE 0 END)) saving_plan_upfront_fee
FROM
  (("cid_data_export"."cur2" rescur
INNER JOIN "finopscenterq_db"."claimed_resource_table" crtbl ON (rescur."line_item_resource_id" = crtbl."resourceid"))
INNER JOIN "finopscenterq_db"."account_mapping" am ON (
    crtbl.accountid = am.accountid 
    AND crtbl.elementmap_element4id = am.element4id
    AND date_trunc('day', rescur."line_item_usage_start_date") >= date_trunc('day', date_parse(am."startdate", '%m-%d-%Y'))
    AND date_trunc('day', rescur."line_item_usage_start_date") <= date_trunc('day', date_parse(am."enddate", '%m-%d-%Y'))
))
WHERE ((line_item_resource_id <> '') AND (date_trunc('day', from_iso8601_timestamp(crtbl."startdate")) <= date_trunc('day', rescur."line_item_usage_start_date")) AND ((crtbl."enddate" IS NULL) OR (date_trunc('day', from_iso8601_timestamp(crtbl."enddate")) >= date_trunc('day', rescur."line_item_usage_start_date"))))
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46

CREATE OR REPLACE VIEW "finopscenter_claimed_resource_view_e1" AS 
SELECT
  base.*
, um."username" "user"
FROM
  ((FinOpsCenter_claimed_resource_base_view base
INNER JOIN "finopscenterq_db"."account_mapping" am ON (base.accountid = am.accountid))
INNER JOIN (
   SELECT
     username
   , element1id
   , year
   , element2id
   , element3id
   , element4map
   FROM
     (
      SELECT
        username
      , element1id
      , year
      , element2id
      , element3id
      , element4map
      , ROW_NUMBER() OVER (PARTITION BY element1id, year ORDER BY username ASC) rn
      FROM
        "finopscenterq_db"."user_mapping"
      WHERE ((element2id IS NULL) AND (element3id IS NULL) AND (element4map IS NULL))
   ) 
   WHERE (rn = 1)
)  um ON ((am."element1id" = um."element1id") AND (am."year" = um."year")))
WHERE ((base.element1_id = am.element1id) AND (base.year = am.year) AND (am.year = um.year))

CREATE OR REPLACE VIEW "finopscenter_claimed_resource_view_e2" AS 
SELECT
  base.*
, um."username" "user"
FROM
  (FinOpsCenter_claimed_resource_base_view base
INNER JOIN (
   SELECT
     username
   , element2id
   , year
   , element1id
   , element2map
   , element3id
   , element4map
   FROM
     (
      SELECT
        username
      , element2id
      , year
      , element1id
      , element2map
      , element3id
      , element4map
      , ROW_NUMBER() OVER (PARTITION BY element2id, year ORDER BY username ASC) rn
      FROM
        "finopscenterq_db"."user_mapping"
      WHERE ((element3id IS NULL) AND (element4map IS NULL))
   ) 
   WHERE (rn = 1)
)  um ON ((base."element2_id" = um."element2id") AND (base."year" = um."year")))
WHERE (base.element2map = um.element2map)

CREATE OR REPLACE VIEW "finopscenter_claimed_resource_view_e3" AS 
SELECT
  base.*
, um."username" "user"
FROM
  (FinOpsCenter_claimed_resource_base_view base
INNER JOIN (
   SELECT
     username
   , element3id
   , year
   , element1id
   , element2id
   , element3map
   , element4map
   FROM
     (
      SELECT
        username
      , element3id
      , year
      , element1id
      , element2id
      , element3map
      , element4map
      , ROW_NUMBER() OVER (PARTITION BY element3id, year ORDER BY username ASC) rn
      FROM
        "finopscenterq_db"."user_mapping"
      WHERE (element4map IS NULL)
   ) 
   WHERE (rn = 1)
)  um ON ((base.element3_id = um.element3id) AND (base.year = um.year)))
WHERE (base.element3map = um.element3map)

CREATE OR REPLACE VIEW "finopscenter_claimed_resource_view_e4" AS 
SELECT
  base.*
, um."username" "user"
FROM
  ((FinOpsCenter_claimed_resource_base_view base
INNER JOIN "finopscenterq_db"."account_mapping" am ON (base.accountid = am.accountid))
INNER JOIN (
   SELECT
     username
   , element4id
   , year
   , element1id
   , element2id
   , element3id
   , element4map
   FROM
     (
      SELECT
        username
      , element4id
      , year
      , element1id
      , element2id
      , element3id
      , element4map
      , ROW_NUMBER() OVER (PARTITION BY element4id, year ORDER BY username ASC) rn
      FROM
        "finopscenterq_db"."user_mapping"
   ) 
   WHERE (rn = 1)
)  um ON ((am."element4id" = um."element4id") AND (am."year" = um."year")))
WHERE ((base.element4map = am.element4map) AND (base.year = am.year) AND (am.year = um.year))

Shared Resources

CREATE OR REPLACE VIEW "finopscenter_shared_resource_base_view" AS 
SELECT
  date_trunc('day', rescur."line_item_usage_start_date") "usage_date"
, rescur."bill_payer_account_id" "payer_account_id"
, rescur."line_item_usage_account_id" "accountid"
, rescur."line_item_usage_account_name" "account_name"
, rescur."bill_billing_entity" "billing_entity"
, rescur."line_item_resource_id" "resourceid"
, rescur."line_item_product_code" "product_code"
, rescur."line_item_operation" "operation"
, rescur."line_item_line_item_type" "charge_type"
, rescur."line_item_usage_type" "usage_type"
, rescur."pricing_unit" "pricing_unit"
, rescur."product_region_code" "region"
, rescur."line_item_line_item_description" "item_description"
, rescur."line_item_legal_entity" "legal_entity"
, rescur."pricing_term" "pricing_term"
, rescur."product_instance_type" "instance_type"
, rescur."product_product_family" "product_family"
, rescur."product_servicecode" "service"
, rescur."product_to_location" "product_to_location"
, rescur."reservation_reservation_a_r_n" "reservation_a_r_n"
, rescur."savings_plan_savings_plan_a_r_n" "savings_plan_a_r_n"
, element_at(rescur.product, 'product_name') "aws_product"
, am."percentage" "percentage"
, date_parse(am."startdate", '%m-%d-%Y') "am_start_date"
, date_parse(am."enddate", '%m-%d-%Y') "am_end_date"
, am.accounttype "account_type"
, am.element1id "element1_id"
, am.element2map "element2map"
, am.element3map "element3map"
, am.element4map "element4map"
, am.element2id "element2_id"
, am.element3id "element3_id"
, am.element4id "element4_id"
, rescur."savings_plan_savings_plan_effective_cost" * (CAST(am."percentage" AS DOUBLE) / 100.0) "savings_plan_effective_cost"
, rescur."reservation_effective_cost" * (CAST(am."percentage" AS DOUBLE) / 100.0) "reservation_effective_cost"
, rescur."line_item_usage_amount" * (CAST(am."percentage" AS DOUBLE) / 100.0) "usage_quantity"
, rescur."line_item_unblended_cost" * (CAST(am."percentage" AS DOUBLE) / 100.0) "unblended_cost"
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'Usage') THEN (rescur.line_item_unblended_cost * (CAST(am."percentage" AS DOUBLE) / 100.0)) ELSE 0 END)) "shared_cost"
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'Credit') THEN (rescur.line_item_unblended_cost * (CAST(am."percentage" AS DOUBLE) / 100.0)) ELSE 0 END)) "credit"
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'Fee') THEN (rescur.line_item_unblended_cost * (CAST(am."percentage" AS DOUBLE) / 100.0)) ELSE 0 END)) "fee"
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'SavingsPlanCoveredUsage') THEN (rescur.line_item_unblended_cost * (CAST(am."percentage" AS DOUBLE) / 100.0)) ELSE 0 END)) "savings_plan_coverage"
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'Tax') THEN (rescur.line_item_unblended_cost * (CAST(am."percentage" AS DOUBLE) / 100.0)) ELSE 0 END)) "tax"
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'RIFee') THEN (rescur.line_item_unblended_cost * (CAST(am."percentage" AS DOUBLE) / 100.0)) ELSE 0 END)) "ri_fee"
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'DiscountedUsage') THEN (rescur.line_item_unblended_cost * (CAST(am."percentage" AS DOUBLE) / 100.0)) ELSE 0 END)) "discounted_usage"
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'SavingsPlanNegation') THEN (rescur.line_item_unblended_cost * (CAST(am."percentage" AS DOUBLE) / 100.0)) ELSE 0 END)) "savings_plan_negation"
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'SavingsPlanRecurringFee') THEN (rescur.line_item_unblended_cost * (CAST(am."percentage" AS DOUBLE) / 100.0)) ELSE 0 END)) "saving_plan_recurring_fee"
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'Refund') THEN (rescur.line_item_unblended_cost * (CAST(am."percentage" AS DOUBLE) / 100.0)) ELSE 0 END)) "refund"
, SUM((CASE WHEN (rescur.line_item_line_item_type = 'SavingsPlanUpfrontFee') THEN (rescur.line_item_unblended_cost * (CAST(am."percentage" AS DOUBLE) / 100.0)) ELSE 0 END)) "saving_plan_upfront_fee"
FROM
  (("cid_data_export"."cur2" rescur
LEFT JOIN "finopscenterq_db"."claimed_resource_table" crtbl ON (rescur."line_item_resource_id" = crtbl."resourceid"))
INNER JOIN "finopscenterq_db"."account_mapping" am ON (
    rescur."line_item_usage_account_id" = am.accountid
    AND date_trunc('day', rescur."line_item_usage_start_date") >= date_trunc('day', date_parse(am."startdate", '%m-%d-%Y'))
    AND date_trunc('day', rescur."line_item_usage_start_date") <= date_trunc('day', date_parse(am."enddate", '%m-%d-%Y'))
))
WHERE ((crtbl."resourceid" IS NULL) OR (CASE WHEN (crtbl."enddate" IS NULL) THEN (date_trunc('day', CAST(rescur."line_item_usage_start_date" AS timestamp)) < date_trunc('day', from_iso8601_timestamp(crtbl."startdate"))) ELSE ((date_trunc('day', CAST(rescur."line_item_usage_start_date" AS timestamp)) < date_trunc('day', from_iso8601_timestamp(crtbl."startdate"))) OR (date_trunc('day', CAST(rescur."line_item_usage_start_date" AS timestamp)) > date_trunc('day', from_iso8601_timestamp(crtbl."enddate")))) END))
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37

CREATE OR REPLACE VIEW "finopscenter_shared_resource_view_e1" AS 
SELECT
  base.*
, 'Shared' spending_type
, um."username" "user"
FROM
  ((FinOpsCenter_shared_resource_base_view base
LEFT JOIN "finopscenterq_db"."claimed_resource_table" crtbl ON (base."resourceid" = crtbl."resourceid"))
INNER JOIN (
   SELECT
     username
   , element1id
   , year
   , element2id
   , element3id
   , element4map
   FROM
     (
      SELECT
        username
      , element1id
      , year
      , element2id
      , element3id
      , element4map
      , ROW_NUMBER() OVER (PARTITION BY element1id, year ORDER BY username ASC) rn
      FROM
        "finopscenterq_db"."user_mapping"
      WHERE ((element2id IS NULL) AND (element3id IS NULL) AND (element4map IS NULL))
   ) 
   WHERE (rn = 1)
)  um ON ((base.element1_id = um.element1id) AND (YEAR(base.am_start_date) = um."year")))

CREATE OR REPLACE VIEW "finopscenter_shared_resource_view_e2" AS 
SELECT
  base.*
, 'Shared' spending_type
, um."username" "user"
FROM
  ((FinOpsCenter_shared_resource_base_view base
LEFT JOIN "finopscenterq_db"."claimed_resource_table" crtbl ON (base."resourceid" = crtbl."resourceid"))
INNER JOIN (
   SELECT
     username
   , element2map
   , year
   , element1id
   , element2id
   , element3id
   , element4map
   FROM
     (
      SELECT
        username
      , element2map
      , year
      , element1id
      , element2id
      , element3id
      , element4map
      , ROW_NUMBER() OVER (PARTITION BY element2map, year ORDER BY username ASC) rn
      FROM
        "finopscenterq_db"."user_mapping"
      WHERE ((element3id IS NULL) AND (element4map IS NULL))
   ) 
   WHERE (rn = 1)
)  um ON ((base.element2map = um.element2map) AND (YEAR(base.am_start_date) = um.year)))

CREATE OR REPLACE VIEW "finopscenter_shared_resource_view_e3" AS 
SELECT
  base.*
, 'Shared' spending_type
, um."username" "user"
FROM
  ((FinOpsCenter_shared_resource_base_view base
LEFT JOIN "finopscenterq_db"."claimed_resource_table" crtbl ON (base."resourceid" = crtbl."resourceid"))
INNER JOIN (
   SELECT
     username
   , element3id
   , year
   , element1id
   , element2id
   , element3map
   , element4map
   FROM
     (
      SELECT
        username
      , element3id
      , year
      , element1id
      , element2id
      , element3map
      , element4map
      , ROW_NUMBER() OVER (PARTITION BY element3id, year ORDER BY username ASC) rn
      FROM
        "finopscenterq_db"."user_mapping"
      WHERE (element4map IS NULL)
   ) 
   WHERE (rn = 1)
)  um ON ((base.element3_id = um.element3id) AND (YEAR(base.am_start_date) = um."year")))
WHERE (base.element3map = um.element3map)

CREATE OR REPLACE VIEW "finopscenter_shared_resource_view_e4" AS 
SELECT
  base.*
, 'Shared' spending_type
, um."username" "user"
FROM
  ((FinOpsCenter_shared_resource_base_view base
LEFT JOIN "finopscenterq_db"."claimed_resource_table" crtbl ON (base."resourceid" = crtbl."resourceid"))
INNER JOIN (
   SELECT
     username
   , element4map
   , year
   , element1id
   , element2id
   , element3id
   , element4id
   FROM
     (
      SELECT
        username
      , element4map
      , year
      , element1id
      , element2id
      , element3id
      , element4id
      , ROW_NUMBER() OVER (PARTITION BY element4map, year ORDER BY username ASC) rn
      FROM
        "finopscenterq_db"."user_mapping"
   ) 
   WHERE (rn = 1)
)  um ON ((base.element4map = um.element4map) AND (YEAR(base.am_start_date) = um.year)))

Resource View

CREATE OR REPLACE VIEW "finopscenter_resource_view_e1" AS 
SELECT
  accountid
, account_name
, resourceid
, claimedby
, element1_id
, element2map
, element3map
, element4map
, element2_id
, element3_id
, element4_id
, workload
, isactive
, "year"
, releasedby
, cr_start_date
, cr_end_date
, usage_date
, payer_account_id
, billing_entity
, product_code
, operation
, charge_type
, usage_type
, pricing_unit
, region
, item_description
, legal_entity
, pricing_term
, instance_type
, product_family
, service
, product_to_location
, reservation_a_r_n
, savings_plan_a_r_n
, aws_product
, percentage
, am_start_date
, am_end_date
, account_type
, "user"
, spending_type
, savings_plan_effective_cost
, reservation_effective_cost
, usage_quantity
, unblended_cost
, claimed_cost "cost"
, credit
, fee
, savings_plan_coverage
, tax
, ri_fee
, discounted_usage
, savings_plan_negation
, saving_plan_recurring_fee
, refund
, saving_plan_upfront_fee
FROM
  FinOpsCenter_claimed_resource_view_e1
UNION ALL SELECT
  accountid
, account_name
, resourceid
, null
, element1_id
, element2map
, element3map
, element4map
, element2_id
, element3_id
, element4_id
, 'Unallocated'
, null
, null
, null
, null
, null
, usage_date
, payer_account_id
, billing_entity
, product_code
, operation
, charge_type
, usage_type
, pricing_unit
, region
, item_description
, legal_entity
, pricing_term
, instance_type
, product_family
, service
, product_to_location
, reservation_a_r_n
, savings_plan_a_r_n
, aws_product
, percentage
, am_start_date
, am_end_date
, account_type
, "user"
, spending_type
, savings_plan_effective_cost
, reservation_effective_cost
, usage_quantity
, unblended_cost
, shared_cost "cost"
, credit
, fee
, savings_plan_coverage
, tax
, ri_fee
, discounted_usage
, savings_plan_negation
, saving_plan_recurring_fee
, refund
, saving_plan_upfront_fee
FROM
  FinOpsCenter_shared_resource_view_e1

CREATE OR REPLACE VIEW "finopscenter_resource_view_e2" AS 
SELECT
  accountid
, account_name
, resourceid
, claimedby
, element1_id
, element2map
, element3map
, element4map
, element2_id
, element3_id
, element4_id
, workload
, isactive
, "year"
, releasedby
, cr_start_date
, cr_end_date
, usage_date
, payer_account_id
, billing_entity
, product_code
, operation
, charge_type
, usage_type
, pricing_unit
, region
, item_description
, legal_entity
, pricing_term
, instance_type
, product_family
, service
, product_to_location
, reservation_a_r_n
, savings_plan_a_r_n
, aws_product
, percentage
, am_start_date
, am_end_date
, account_type
, "user"
, spending_type
, savings_plan_effective_cost
, reservation_effective_cost
, usage_quantity
, unblended_cost
, claimed_cost "cost"
, credit
, fee
, savings_plan_coverage
, tax
, ri_fee
, discounted_usage
, savings_plan_negation
, saving_plan_recurring_fee
, refund
, saving_plan_upfront_fee
FROM
  FinOpsCenter_claimed_resource_view_e2
UNION ALL SELECT
  accountid
, account_name
, resourceid
, null
, element1_id
, element2map
, element3map
, element4map
, element2_id
, element3_id
, element4_id
, 'Unallocated'
, null
, null
, null
, null
, null
, usage_date
, payer_account_id
, billing_entity
, product_code
, operation
, charge_type
, usage_type
, pricing_unit
, region
, item_description
, legal_entity
, pricing_term
, instance_type
, product_family
, service
, product_to_location
, reservation_a_r_n
, savings_plan_a_r_n
, aws_product
, percentage
, am_start_date
, am_end_date
, account_type
, "user"
, spending_type
, savings_plan_effective_cost
, reservation_effective_cost
, usage_quantity
, unblended_cost
, shared_cost "cost"
, credit
, fee
, savings_plan_coverage
, tax
, ri_fee
, discounted_usage
, savings_plan_negation
, saving_plan_recurring_fee
, refund
, saving_plan_upfront_fee
FROM
  FinOpsCenter_shared_resource_view_e2

CREATE OR REPLACE VIEW "finopscenter_resource_view_e3" AS 
SELECT
  accountid
, account_name
, resourceid
, claimedby
, element1_id
, element2map
, element3map
, element4map
, element2_id
, element3_id
, element4_id
, workload
, isactive
, "year"
, releasedby
, cr_start_date
, cr_end_date
, usage_date
, payer_account_id
, billing_entity
, product_code
, operation
, charge_type
, usage_type
, pricing_unit
, region
, item_description
, legal_entity
, pricing_term
, instance_type
, product_family
, service
, product_to_location
, reservation_a_r_n
, savings_plan_a_r_n
, aws_product
, percentage
, am_start_date
, am_end_date
, account_type
, "user"
, spending_type
, savings_plan_effective_cost
, reservation_effective_cost
, usage_quantity
, unblended_cost
, claimed_cost "cost"
, credit
, fee
, savings_plan_coverage
, tax
, ri_fee
, discounted_usage
, savings_plan_negation
, saving_plan_recurring_fee
, refund
, saving_plan_upfront_fee
FROM
  FinOpsCenter_claimed_resource_view_e3
UNION ALL SELECT
  accountid
, account_name
, resourceid
, null
, element1_id
, element2map
, element3map
, element4map
, element2_id
, element3_id
, element4_id
, 'Unallocated'
, null
, null
, null
, null
, null
, usage_date
, payer_account_id
, billing_entity
, product_code
, operation
, charge_type
, usage_type
, pricing_unit
, region
, item_description
, legal_entity
, pricing_term
, instance_type
, product_family
, service
, product_to_location
, reservation_a_r_n
, savings_plan_a_r_n
, aws_product
, percentage
, am_start_date
, am_end_date
, account_type
, "user"
, spending_type
, savings_plan_effective_cost
, reservation_effective_cost
, usage_quantity
, unblended_cost
, shared_cost "cost"
, credit
, fee
, savings_plan_coverage
, tax
, ri_fee
, discounted_usage
, savings_plan_negation
, saving_plan_recurring_fee
, refund
, saving_plan_upfront_fee
FROM
  FinOpsCenter_shared_resource_view_e3

CREATE OR REPLACE VIEW "finopscenter_resource_view_e4" AS 
SELECT
  accountid
, account_name
, resourceid
, claimedby
, element1_id
, element2map
, element3map
, element4map
, element2_id
, element3_id
, element4_id
, workload
, isactive
, "year"
, releasedby
, cr_start_date
, cr_end_date
, usage_date
, payer_account_id
, billing_entity
, product_code
, operation
, charge_type
, usage_type
, pricing_unit
, region
, item_description
, legal_entity
, pricing_term
, instance_type
, product_family
, service
, product_to_location
, reservation_a_r_n
, savings_plan_a_r_n
, aws_product
, percentage
, am_start_date
, am_end_date
, account_type
, "user"
, spending_type
, savings_plan_effective_cost
, reservation_effective_cost
, usage_quantity
, unblended_cost
, claimed_cost "cost"
, credit
, fee
, savings_plan_coverage
, tax
, ri_fee
, discounted_usage
, savings_plan_negation
, saving_plan_recurring_fee
, refund
, saving_plan_upfront_fee
FROM
  FinOpsCenter_claimed_resource_view_e4
UNION ALL SELECT
  accountid
, account_name
, resourceid
, null
, element1_id
, element2map
, element3map
, element4map
, element2_id
, element3_id
, element4_id
, 'Unallocated'
, null
, null
, null
, null
, null
, usage_date
, payer_account_id
, billing_entity
, product_code
, operation
, charge_type
, usage_type
, pricing_unit
, region
, item_description
, legal_entity
, pricing_term
, instance_type
, product_family
, service
, product_to_location
, reservation_a_r_n
, savings_plan_a_r_n
, aws_product
, percentage
, am_start_date
, am_end_date
, account_type
, "user"
, spending_type
, savings_plan_effective_cost
, reservation_effective_cost
, usage_quantity
, unblended_cost
, shared_cost "cost"
, credit
, fee
, savings_plan_coverage
, tax
, ri_fee
, discounted_usage
, savings_plan_negation
, saving_plan_recurring_fee
, refund
, saving_plan_upfront_fee
FROM
  FinOpsCenter_shared_resource_view_e4