Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I agree with tobixen - you're on the right track.</p> <p>I would recommend that you read Ralph Kimball's book "The Data Warehouse Toolkit", particularly the chapter on retail sales - it goes in depth about a sales fact.</p> <p>The Date Dimension is like having a Calendar table - you can split based on quarters, fiscal months, and other things that are business specific to dates. I typically keep both the date key as well as a timestamp datatype, so we can do things with the Fiscal Calendar. I would actually have a separate Time dimension if you need to have your grain of the table at that level, with buckets for hours of the day, or minutes, etc. I doubt you need hourly though.</p> <p>Here's what I would do:</p> <p>Declare the Grain of your fact table:</p> <p>1 row per order line</p> <p>Note how the grain doesn't contain anything that doesn't uniquely identify the row.</p> <p>Dimensional Attributes of the order line:</p> <pre><code>Date Time (if needed, and bucketed by hour/minute etc) Product Customer </code></pre> <p>Degenerate Dimensions of the order line (these are codes that are related to the transaction):</p> <pre><code>Order Number Order Line Number </code></pre> <p>Some Sample Measures:</p> <pre><code>Item Price at time of Sale (optional, may be useful in some situations) Discount Amount Sale Dollars </code></pre> <p>This should answer all of those questions.</p> <p>For the totals, a simple COUNT / SUM after filtering on the attributes of the dimensions should work fine.</p> <p>You should consider that the customer dimension is one of the most difficult to model, Kimball devotes a whole chapter in his book to the customer dimension.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to model a fact table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USN West
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI wouldn't bother with a time dimension ... at least not in the initial design, unless there is a clear need for it. However, I think it makes sense to include the complete timestamp in the fact table - it doesn't cost much, and will allow for any kind of ad-hoc query later on, like "what hour of day do we have the most turnover", or "just around lunch time, which quarter of the hour do we have most number of sales?", and it will as well make it easier to create a time dimension later if it should be needed.
      singulars
      1. PO
      1. UStobixen
    2. CO@tobixen He's asking questions about Q1. Q1 is defined differently in pretty much every organization I've done work for - it makes sense to include a "quarter" attribute on a time dim / calendar table. He's also asking about "years", which can be "fiscal years", "tax years" etc. EDIT - after rereading your comment, I agree that a "time of day" type dimension is not needed, but I maintain my recommendation for a "date" dimension.
      singulars
      1. PO
      1. USN West
    3. COHaving a date dimension is considered "best business practice", and I never said otherwise :-) However, my experience at my former work place was that I could manage all business requirements using postgres' library of date and time functions, and that we lost significant performance by dragging in a date dimension. I don't know about the US, but in Europe the quarters and years are usually very strictly defined by the Gregorian calendar, only thing causing minor troubles was the time zones and DST.
      singulars
      1. PO
      1. UStobixen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload