title: How to avoid joins between fact tables in a star schema?
body: <p>I'm trying to model my data warehouse using a star schema but I have a problem to avoid joins between fact tables.<br>
  To give a trivial idea of my problem, I want to collect all the events who occur on my operating system. So, I can create a fact table <code>event</code> with some dimensions like <code>datetime</code> or <code>user</code>. The problem is I want to collect different kinds of event: hardware event and software event.<br>
  The problem is those events have not the same dimensions. By instance, for a hardware event, I can have <code>physical_component</code> or <code>related_driver</code> dimensions and, for a software event, <code>software_name</code> or <code>online_application</code> dimensions (that is just some examples, the idea to keep in mind is the fact <code>event</code> can be specialized into some specific events with specific dimensions).<br>
  In a relational model, I would have 3 tables organized like that:
  <a href="https://i.stack.imgur.com/Bgy2P.png" rel="noreferrer"><img src="https://i.stack.imgur.com/Bgy2P.png" alt="enter image description here"></a>
  <br>The problem is : how to handle joins between fact tables in a star schema?<hr>
  I imagined 4 ideas but I'm not sure one of them are adapted to the situation.<br>
  <strong>The first one</strong> is to keep the model used in a relational database and add the dimension tables like that:<br>
  <a href="https://i.stack.imgur.com/e2ZHy.png" rel="noreferrer"><img src="https://i.stack.imgur.com/e2ZHy.png" alt="enter image description here"></a>
  <br>In this case, the problem is we still have join between fact tables and need to use <code>JOIN</code> SQL statement in all of our queries.
  <hr><strong>The second one</strong> is to create only 2 fact tables who will duplicate the shared dimensions (datetime and user) and to create a materialized view event who summarized all the events:
  <br>
  <a href="https://i.stack.imgur.com/ebcXa.png" rel="noreferrer"><img src="https://i.stack.imgur.com/ebcXa.png" alt="enter image description here"></a>
  <br>
  The problem here is: what happen if I want to make a query on the materialized view? According to what I read in the Oracle documentation, we don't have to make query directly on materialized view but we have to let the query rewrite process make its work.
  <hr><strong>The third one</strong> is to create only one fact table who will contain all the information possible for an event (hardware or software):<br>
  <a href="https://i.stack.imgur.com/FVRYj.png" rel="noreferrer"><img src="https://i.stack.imgur.com/FVRYj.png" alt="enter image description here"></a>
  <br>This time, the problem is my fact table will contain a lot of <code>NULL</code> value.
  <hr>And <strong>the last one</strong> is to create 3 fact tables (without materialized view this time) like this:<br>
  <a href="https://i.stack.imgur.com/RgX02.png" rel="noreferrer"><img src="https://i.stack.imgur.com/RgX02.png" alt="enter image description here"></a>
  <br>This time, the problem is all events are present in the fact table <code>event</code> and in its own table. Because we will store a huge quantity of data, I'm not sure this duplication is a good idea.
  <br>
  So what is the best solution? Or does it exist a fifth solution?</p>