How to merge facts from two different database into Fact table in Datawarehouse?

Question

I have two different database A and B. I would like to create Fact table in my Datawarehouse which consists of facts from two databases.i.e I would like to create single fact from both databases. I would like to create "Revenue" column in my fact table that will consists of revenue from DB-A, which is Quantity*Product_retailprice, and for DB-B it will be Billed amount.

I am new to Datawarehouse, please provide suggestion/explanation if something like this can be achieved.

My attributes in databdases looks like following:

Database A
A_Product -> ProductID, Product_retail_price
A_Orderdetails -> OrderID,ProductID,Quantity

Database B
B_amount ->B_id, Billedamount

Answer 1

I would copy B_amount to 'Database A', and create a view:

CREATE VIEW Revenu AS (
   SELECT 
      A1.ProductID, 
      A1.Product_retail_price, 
      A2.OrderID, 
      A2.Quantity,
      A1.Product_retail_price * A2.Quantity AS Revenue, 
   FROM A_Product AS A1
   INNER JOIN A_Orderdetails AS A2 ON A2.ProductID = A1.ProductID
   UNION ALL
   SELECT 
      B_id,
      0,
      0,
      0,
      Billedamount
   FROM B_Amount
)

After that you can do: SELECT * FROM Revenu WHERE …