129 How do you load a fact table
ฝัง
- เผยแพร่เมื่อ 4 ต.ค. 2024
- How do you load a fact table ?
Download the file\script used in the Video from below link
drive.google.c...
SSIS Tutorials: • SSIS Tutorials
SSIS real time scenarios examples: • SSIS real time scenari...
SSIS Interview questions and answers: • SSIS Interview questio...
How do you load a fact table
How do you populate a fact table in SQL Server?
Which table should be loaded first fact or dimension?
How do you create a fact table?
If you have any questions or suggestions please comment on the video or write to me at “aqil33@gmail.com”
very well explained with examples. really very impressed with this practical example on how to load Fact Load Aqil!
Thank you Abhilash.
Awesome job. I love how well you make your videos. Thank you for the great job. I have learned so much from you. You are the best in youtube
ha ha. Thanks for your comments. Good to know that you liked the video.
Excellent videos now much cleared about dimension and fact loading thanks for the great videos ssis
You are most welcome Anand Reddy.
Nice explanation 👏👏 some times I feel sproc much easier when I have more than 50+ columns
Yes you are correct, its always easy to update the code in sp instead of modifying the SSIS package.
Hi Akhil, Your SQL Video series are outstanding, kindly make a video on Performence tuning on SQL Server.
Hi Aqil, Thanks for the information very helpful, my question: After i run the package the data is inserted into FAct.Orders table but 2 columns ( CustomerSk and ProductSk) are both NULL except EmployeeSK
Awesome Job Brother, Thank a lot for sharing the script
You are most welcome Brother.
I have a question. The output when writing to a destination table, does it need to be in the same order as that table or it’s ok to just match by name column? I have had issues on the destination and the data didn’t load properly
The order is not important, we just need to map the input columns with destination columns.
HI Aqil. Thenks for such helpful videos.
Just one question. What if something is deleted from Source table and should be cleaned in Fact table? How should we handle the situation?
I have made a video on how to handle deleted records in incremental load, you can take a look at that and can try to implement the same way.
th-cam.com/video/bZaizKvRA8o/w-d-xo.html
@@learnssis Thank you!! I'll try to do it right ))
Thank you for your good explanation
If the dimensions are loaded first
, then the fact table,
Is it necessary to insert zero sk to the dimensions?
If we won't insert the zero sk then we might miss some records to be inserted to fact table as there won't be any match for those records.
My answer is:
For example, loading the fact table every 1 hour and dimensions every 1 week
So we need to insert zero sk
But in addition price changes and quantity changes we need to consider sk changes
To update zero value sk
I think the conditional split can have another condition to check for sk changes
thanks
Hello Aqil, Great job. But I have a question, I still don't understand why we need to insert a surrogate key with a zero value into the dimension tables? can you please explain it more?
If in your Fact Table you have foreign key on the SurrogateKey For Dim table, then it will only allow the values into the SurrogateKey those values already exists in the Dim table. Thus if a match not found for a record in the Dim table then it will try to insert a value 0 into the Fact table for the SurrogateKey column and because there is a Foreign key on that column thus the insert will fail, if you don't have foreign keys created in the Fact table then you don't need to insert the value 0 to the Dim table.
Question:
set @cnt = (select count(*) from [Dimension].[Date] where DateKey=19000101)
if(@cnt=0)
begin
delete from [Dimension].[Date] where DateKey=0
end
Note: Should not it be : delete from [Dimension].[Date] where DateKey=19000101
I think you are right, at the moment I am out of station and will check it once I will be back.
Hi Aqil, Thanks a lot. This video is extremely helpful. I am not clear why we updated SK columns at the end for the matches records, but not just the columns we included in BSM calculations. Because we only split the data based on that condition and if there was any change in those? In real life time scenarios, don't we expect if any of those key, SK columns change, it should come with a new OrderId? So, my question is, why are we updating the dimension key columns also?
My other question is, what if the fact table is also SCD2 and if we want to insert a new record instead of updating the existing ones, how could we handle that scenario? Is it similar to SCD2 Dimension load video you made where we need to create 1 fact. order_insert and 1 fact. order_update tables to stage that data?
Thanks in advence!
Hi, yes for your question one instead of updating the SK columns if there is a change then we can insert new records. I think was required in our project thus I did way.
If the Fact table is also SCD2 then yes you can handle is same way how you can handled the Dim table for SCD2. Thanks.
Is it possible to trasfer data directly from [dbo].orders to [Fact].orders as both are having same columns???
Yes, you can do that.
Hi Aqil, thanks for the video. When creating the tables i get this error 'The specified schema name "Fact" either does not exist or you do not have permission to use it.' from SSMS,how can i change the permision
execute this query on the database
create schema Fact
@@learnssis Thank you so much
hey Aqil, I have a question. If we want to create dimensions and fact using ssis in a same project do we create different packages or we can do it in one.
I am new to it and currently working on the project where I am loading data into dimensions and facts . I have covered your "Loading data into dimension table employee" video.
Please guide me and can u please provide the scripts to load data into other dimension tables.
Hi, its upto you if you want to write code for all Dim in a single package or want to create multiple packages. If you have written the code to load data into Dimension employee table then same logic will be used for all other tables as well, you can write the code accordingly for other tables as well.
@@learnssis thank you so much. If we want to create all dimensions and facts in a same package, will we use sequence container to contain every dimension and fact?
@@haisimyasin5877 Yeah its better to create separate sequence container for every Dim and Fact table, because it can be easy for you to disable them if required.
Why did you use Conditional Split?
I used conditional split transformation to split the data into 2 outputs based on the condition that I have written in conditional split transformation.
Need one help can u create a stored procedure to save the information of file in database like the scenario is we have one request I'd on that basis we have to fetch information like request I'd location file name download percentage u can create a table and have those columns included and fetch the information from that table through stored procedure
In below video I have shown how to import a CSV file into a MySQL table
th-cam.com/video/cWA9OSuLPuI/w-d-xo.html
And I have already created the video on "how to create stored procedure", thus I think you should be able to do it.
@@learnssis through query I am saying based on the conditions I provided.
I didn’t understood the purpose of inserting 0 to dim table.
If in your Fact Table you have foreign key on the SurrogateKey For Dim table, then it will only allow the values into the SurrogateKey those values already exists in the Dim table. Thus if a match not found for a record in the Dim table then it will try to insert a value 0 into the Fact table for the SurrogateKey column and because there is a Foreign key on that column thus the insert will fail, if you don't have foreign keys created in the Fact table then you don't need to insert the value 0 to the Dim table.
@@learnssis no new record cannot be added in fact table if we dont load 0 in dimension table?
By unmatched record we mean the newly inserted record in table ?
Also can u please explain why you used date dimension i am confused about it?
Thankk you
Un grand merci. Tout est bien expliqué.
Merci beaucoup pour ton soutien.
Hi sir
How to connect to a SFTP server via an SSIS script task package
Hi Naveen, actually at the moment I don't have access to any FTP or SFTP server so it will be hard for me to test it. I will see if any public FTP or SFTP server is available so that I can test it.
Ok sir but I use SFTP server sir
How to use the WinSCP .NET assembly inside an SSIS Scripting Task, as this is what WinSCP also recommends as the way to achieve SFTP using WinSCP in SSIS.