It is great video . Describing enough all by itself
@robertbiddle77896 жыл бұрын
Hi all. The solution, scripts, and slide deck can be found on SQL Saturday site for this session that I've done in Orlando: www.sqlsaturday.com/318/Sessions/Details.aspx?sid=23151
@TAIMIUWU3 жыл бұрын
Great presentation and very clear video. please can get the code or script for execute sql task for "Get the Modified date". Thanks in advance
@robertbiddle77893 жыл бұрын
@@TAIMIUWU select isnull(LastSuccessfulModifiedDate, '1900-01-01') as LastSuccessfulModifiedDate, getdate() as CurrentDate from Meta.DataFlow where DataFlowName = 'Dim_Customer'
@vochau980626 күн бұрын
Hello, the site is out. Can you give me this solution as today I first watch it. Thank you
@MatheusPavanetti3 жыл бұрын
That's awesome !
@fatiflowe5 жыл бұрын
Hello this was great , But I'm a little bit Confused on why you didn't use the Dim_Date as a package in ur ssis Project .while creating the Fact table shouldn't we do a look up by the date dimension ?!
@ericsos1012 жыл бұрын
great video! so how did you get the source data?
@solsh64675 жыл бұрын
Where can I get those spread sheets you have used for mapping
@MrDavisv8 жыл бұрын
This was great but I'm confused on how your fact records are updated. In your sales package it's just an insert, correct?
@robertbiddle77896 жыл бұрын
Hi Davis, this is a good question and a common one. Depending on your source data would tell how you should handle Fact table updates and deletes. The pattern I typically recommend for this is to load your changes to a table and then apply a T-SQL Merge stored procedure. You could alternatively also use an update and insert statement. For some systems, the transaction records are write-once, meaning that to update the data, you have to back out the transaction (or negate it). In these cases, this would mean you would only insert to the Fact table. In many environments, deleting from Fact tables is not typically a good thing. Transactions should not be eliminated unless there was a major error in your source system.
@antoniosol98638 жыл бұрын
at 58:08 When you do Lookups againts the Dimension Tables. What is the purpose of this ? and What do you do with the Non-Matching Records ?
@robertbiddle77898 жыл бұрын
+Antonio Sol The purpose of the lookup transformation is to get the surrogate key from the dimensions. You can handle the non-matching records in a couple of different ways. Typically I will suggest that you use a Derived Column transformation later in the flow to convert all of the nonmatching records to have a surrogate key value of -1. This is the inferred member in all of your dimensions if you followed the methodology in the video. Effectively this will state those values are unknown, which is true as they do not match. A goal for a good performing fact table is that all joins are inner joins to the dimensions. Therefore we want all unknown or null lookups to go to an inferred member.
@robertbiddle77896 жыл бұрын
The lookups are meant to get the key values from the dimension tables. Because everything is on the same server in this particular example, it's less important to use lookup transformations and you can instead use T-SQL. However there are cases for using lookups as well. It's best to have all the tools available at your disposal. You don't want to do everything in T-SQL, nor do you want to do everything entirely with the SSIS components.
@robertbiddle77896 жыл бұрын
The non-matching records will effectively return a null key. The derived column transformation at the bottom of the fact package will handle the nulls, converting them to -1. If you do not set the lookup transformations to "ignore" and leave it as default then you will get a package error.
@vijaykumar12994 жыл бұрын
@@robertbiddle7789 Hi Robert Thank you for the Video.Lot of Helpful Information. My concern is once we insert -1 for non matching records what happens if we find a matching surrogate key in the future for the same 'AK' doesn't it create duplicate records in the fact table ?
@sureshabeyweera67333 жыл бұрын
Helll does anyone know how to make ETL using ssis from sql server to snowflake