title: Selecting data across day boundaries from Star schema with separate date and time dimensions
body: <p>What is the correct way to model data in a star schema such that a BI tool (such as PowerBI) can select a date range crossing multiple days?</p>
  
  <p>I've currently got fact tables that have separate date and time dimensions. My time resolution is to the second, date resolution is to the day. </p>
  
  <p>It's currently very easy to do aggregation providing the data of interest is in the same day, or even multiple complete days, but it becomes a lot more complicated when you're asking for, say, a 12 hour rolling window that crosses the midnight boundary. </p>
  
  <p>Yes, I can write a SQL statement to first pull out all rows for the entirety of the days in question, and then by storing the actual date time as a field in the fact table I can further filter down to the actual time range I'm interested in, but that's not trivial (or possible in some cases) to do in BI reporting tools.</p>
  
  <p>However this must be a frequent scenario in data warehouses... So how should it be done? </p>
  
  <p>An example would be give me the count of ordered items from the fact_orders table between 2017/Jan/02 1600 and 2017/Jan/03 0400.</p>
  
  <p>Orders are stored individually in the fact_orders table.</p>
  
  <p>In my actual scenario I'm using Azure SQL database, but it's more of a general design question.</p>
  
  <p>Thank you.</p>