StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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