Databases
StackOverflow2013
Postsdbo
POCalculate computed columns during ETL or during insert to data warehouse
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCalculate computed columns during ETL or during insert to data warehouse
    primarykey
    data
    text
    <p>I was hoping someone could point me to some best practices regarding when to calculate a computed value which is stored in a data warehouse.</p> <p>Consider the following example,</p> <pre><code>CREATE TABLE ( MyFactID INT NOT NULL IDENTITY(1, 1), OrderDimID INT NOT NULL, -- FK To OrderDimension StartDate DATETIME NOT NULL, CompletedDate DATETIME NULL, ElapsedCalendarTimeInMinutes INT NULL, ElapsedBusinessTimeInMinutes INT NULL ) </code></pre> <p>In this example, Elapsed calendar time in minutes would be the time (in minutes) from Start to End date. Then, our business time reflects the working time that was available during those calendar days.</p> <p>Currently, we are calculating this during ETL and inserting both dates. I'm wondering if this is the correct place to perform this operation.</p> <p>Some other thoughts were to:</p> <p>A) Use indexed views by only storing the start and end date in the fact table, then creating a view which calculates the elapsed time in minutes and has a computed column which uses a function to figure out the business days.</p> <p>B) Use an After trigger to update the elapsed calendar time and business time after an insert occurs which inserts or updates the completed date to a non-null value.</p> <p>I feel that this should be done in the DB so that if any changes are made to the end date or calculation of the business time, it would reflect. Doing it during ETL seems like it would be prone to problems.</p> <p>Any thoughts on this are appreciated!</p> <p><strong>Update:</strong> There are at least 6 columns determined in this way. We have business minutes, hours, and days (days are 12 hours for <em>our</em> business); then we have client minutes, hours, and days (determined via lookup table for the client's working hours); then we have simply calendar minutes, hours, and days (though these are not being stored; only minutes). Since this is a DW, I would have expected all the data to be present and not require calculation. To me, it seems like more work to ensure the ETL is correct and applied everywhere than to create a view overtop of the base data to get the computed information.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMPavlak
    1. USMPavlak
    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
    1. VO
      singulars
      1. POCalculate computed columns during ETL or during insert to data warehouse
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy would you do it in the DB? Once the data reaches the DB at the end of the ETL process, you shouldn't be modifying it - the only thing that changes data in the warehouse should be the ETL (which, incidentially, is why you don't typically need the DB to enforce constraints in a warehouse as well)
      singulars
      1. POCalculate computed columns during ETL or during insert to data warehouse
      1. USN West
    2. COI would want to do it in the DB so that we only have to insert the minimal amount of information (eg, start and end datetimes) and have the rest be calculated consistently in the DB so that everywhere that business time is used will go thru the same function. Using ETL forces us to ensure that loading all the tables use the same process, while creating a view overtop of the base data allows us to drop and recreate the view if logic changes.
      singulars
      1. POCalculate computed columns during ETL or during insert to data warehouse
      1. USMPavlak
    3. CO"Using ETL forces us to ensure that loading all the tables use the same process" - I would argue this is a good thing, since you're enforcing rules. Views can be fine, but will not perform as well. You can also do the ETL inside the database as "ELT" (extract - load - transform).
      singulars
      1. POCalculate computed columns during ETL or during insert to data warehouse
      1. USN West
 

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