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

What is b_id stands for? Is there any relation between product_id and b_id? are you willing to keep b_id and product_id value in same column in your new fact table? — mkRabbani, Jun 02 '19 at 13:30
@mkRabbani. those are just unique ID's present in database. I donot wish to preserve this in my facttable — biggboss2019, Jun 02 '19 at 13:41
Then what new columns you are planning to have in your new fact table? is there will be any indication for data belongs to db A or db B? — mkRabbani, Jun 02 '19 at 13:43
@mkRabbani. I would like to create one column call "Revenue" and other column to be "DB Source" to keep track of where the data belong to — biggboss2019, Jun 02 '19 at 13:46
that's make some sense. But may be you are transferring thousands of from each table and on that case is there any row wise reference of data? if no reference available, what will be your future use of data from the new fact table? — mkRabbani, Jun 02 '19 at 13:52
@mkRabbani.. I am planning on creating single revenue report from both databases. Even-though, they are separate in my DB's — biggboss2019, Jun 02 '19 at 13:55
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/194358/discussion-between-mkrabbani-and-biggboss2019). — mkRabbani, Jun 03 '19 at 08:26

score 2 · Accepted Answer · answered Jun 02 '19 at 14:59

2

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 …

answered Jun 02 '19 at 14:59

Luuk

12,245
5
22
33

Thanks! How would I insert the view in the Fact table then ? – biggboss2019 Jun 02 '19 at 16:01
1

A view is basically the same as its definition, there is no need to put them in a separate table, if onder of the source tables (in this case A_Product, A_Orderdetails and B_Amount) change their content, this is automatically shown by the view (Revenu) – Luuk Jun 02 '19 at 17:36

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

1 Answers1