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
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 …