Databases
StackOverflow2013
Postsdbo
POStart and end period in each fact within a data warehouse
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStart and end period in each fact within a data warehouse
    primarykey
    data
    text
    <p>I have been asked to add a new table to our data warehouse. Currently, we separate our facts into monthly, quarterly, and yearly tables, with time dimensions for each. Each fact record has one time value. The data is generated in the source system by start and end period, and the end date becomes the time dimension value of the fact record. The flow of the fact into either the month, quarter, or year fact table tells one how to understand the dates in the records and how to use them.</p> <p>I've been asked to have the new table contain start and end dates in each record. I have been told that this violates a data warehousing principle, but it better represents the way the data is generated and allows for more flexible querying of the data, e.g. for rolling periods, etc.</p> <p>I'm no data warehouse expert. I understand that a single time dimension per fact is a principle. My question is, what are the ramifications of breaking that principle? In other words, what are the arguments against doing so? What problems might I face in the future in doing so? It seems to me that having the start and end periods for each fact does better represent the data, but I admit that I don't know enough to fully evaluate the implications of this design choice. Can anyone perhaps provide some prespective?</p> <p>Edit: I appreciate these answers. They at least tell me it isn't as bad a practice as I was led to believe. I will clarify one thing about the dates: They don't represent a period of validity, but rather a period of aggregation. Thus, a fact record may represent an average of pounds used of a certain ingredient as calculated for an arbitrary period of months. Don't know if this makes any difference, but there it is.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USHenry
    1. USHenry
    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. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POStart and end period in each fact within a data warehouse
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POStart and end period in each fact within a data warehouse
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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