create fact table in sql using materialized view

Question

I have 4 dimensions ('T_DATE', 'CUSTOMERS_COPY', and 'ORDERS_COPY') and I'm trying to create a fact table so far this is what I got but I got an error ( ORA-00904: "ID_C": invalid identifier ) any one have any Idea ??

CREATE MATERIALIZED VIEW F_ORDERS
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS SELECT ID_o,
          ID_s,
          ID_T,
          ID_C,
          SUM(Qty) AS Qty,
          SUM(Amount-Sum) AS Amount
   FROM T_DATE TD, CUSTOMERS_COPY CC, ORDERS_COPY OC, T_SALESMAN TS

   GROUP BY ID_O, ID_S, ID_T, ID_C;
   
ALTER MATERIALIZED VIEW F_ORDERS ADD CONSTRAINT PK_F_ORDERS 
PRIMARY KEY(ID_O, ID_S, ID_T, ID_C);

it's a stare schema/,,,,,,,,,,,,,,

You are doing a cross join on 4 tables. This seems like a bad idea -- you probably want some join criteria. Also, I would suggest doing modern joins using the JOIN clause. — Hogan, Nov 10 '22 at 22:04
You mean _star_ schema? Why are you jumping straight into materialized views? Basically the error means `ID_C` is not a column in any of those tables. It would help if you included the table name when selecting the columns — Nick.Mc, Nov 10 '22 at 22:05
@Nick.McDermaid -- I don't think it can be a stare schema with that may cross joins. I would have to be a cross-eyed stare schema. — Hogan, Nov 10 '22 at 22:07
@Hogan :D you'e right it's a stare schema cause you stare at the monitor for hours while it tries to cross join all that data. Amal, before you start messing with materialized views and star schemas, you need to learn some SQL basics, i.e. joins. You can't just mush those tables up like that and expect a sensible answer. If each of your tables in that query had 100 rows, your result will have 100,000,000 (meaningless) rows — Nick.Mc, Nov 10 '22 at 22:09
Amal -- in seriousness I wrote an example of how to use a star schema below in an answer -- I hope this will get you on the right track. You can see how the "center" of the star in my example query is the order table. What is the center of your data model? — Hogan, Nov 10 '22 at 22:14
This is a fire and forget question, I doubt the poster is coming back. let alone understands what you've written. — Nick.Mc, Nov 14 '22 at 10:27

score 0 · Answer 1 · answered Nov 10 '22 at 22:12

With a star schema I would expect your query to look like this: (This assumes that the center of the star is an order)

   SELECT ID_o,
          ID_s,
          ID_T,
          ID_C,
          SUM(Qty) AS Qty,
          SUM(Amount-Sum) AS Amount
   FROM ordertable O
   LEFT JOIN T_DATE TD ON O.orderid  = TD.orderid 
   LEFT JOIN CUSTOMERS_COPY CC ON O.orderid = CC.orderid 
   LEFT JOIN ORDERS_COPY OC ON O.orderid = OC.orderid
   LEFT JOIN T_SALESMAN TS ON O.orderid = TS.orderid
   GROUP BY ID_O, ID_S, ID_T, ID_C;

create fact table in sql using materialized view

1 Answers1