what to do if the store_table (dimension table) is a slowly changing dimension for which we can create a surrogate key, but for sales_table (fact table) the surrogate key is not provided to identify stores (as these keys have no meaning in the real world, imagine an external data provider giving us sales data based on store IDs). How can we use this concept then?
How do we write sql script for dimension table in oracle that has a surrogate key? I am going to use informatica tool to load the dimension tables. Please let me know. TIA.
If a previously inserted store ID changes location, you would want to have a process that ingests the file and UPDATES the already existing store ID. This model doesn't make sense because you now have two store IDs of S1001. Are they two different locations? Did one store close?
I think you are forgetting that even though there is a change in the record, of course old and new record still bear the same natural key S1001, the surrogate key of the record with updated attribute will be a different integer (SK). So, if old record is 2, the new record could be 7.
I had a question in interview on this He asked me that the fact table has a composite PK which itself is unique and strong then why we need a SK again to uniquely identify a record...it created a lot of confusion to me and I was not able to answer it that time.
Surrogate keys are ideal for dimension tables but not ideal all the time for fact tables. But there are three mainly reasons when you need a SK in fact tables. 1. Sometimes business rules of organization legitimately allow to keep two identical rows into a fact table. It is not quite often but there is a possibility. As a engineer, you would definitely add some timestamp to identify those two rows as unique but occasionally you are forced to accept this undesirable input. 2. It is feasible for some ETL technique. Let's say you want to update rows in fact table and you want to design your ETL processes in a single transaction like insert updated rows as new rows in step 1 and delete old rows in step 2. This technique has significant improvements in load balancing, recovery and audit purposes. For this technique, you do require a surrogate key as there is a chance that composite key can be duplicate for new records as well old records. 3. From similar ETL perspective, it's easy to determine at which point your job suspended or crashed and you can resume from that point or run process entirely. A sequential surrogate key can help to make this task very straight forward by getting max surrogate key from fact table.
So what happens to the original primary key would it still exist on dimension table and if it does, what is going to be its significance now that surrogate key is also present?
Your videos are awesome. There is little request if you arrange your videos in the playlist in proper order that would more helpful to your viewers. Initially all video are randomly arranged in your playlist.
Can I just say that the natual key in a dimension table from a DW correspond to the PK column to that same table from the database? And the SK column is the PK of the dimension table, so i will only exists on the DW. Is my thought correct?
Good that you asked it. With "Identity Column", if you are referring Primary Key - You are correct. Surrogate key is added as a primary key column in dimensional tables.
@@aroundBI Please let me know that if we add same value record in primary key column (like S1001) .it should not be possible because it has to be unique.So why do we need surrogate key in this case.
I would just like to point out at 4:47: for SCD Type-2 if another row is added with a similar storeID, then a new surrogate_key should not be generated because S1001 SK should already be present in main SK Table. If the PK was a composite key (storeID,streetname) then a new surrogate should be generated. Rest of the stuff is very helpful. Thanks for the upload.
good explanations but can you please bring someone else who does not have an accent. I barely could understand you. I could not understand you at all brother.
