Microsoft Fabric: Incremental ETL for Warehouse using Data Pipeline, SQL Procedure

This video will discover the ways to run Incremental ETL(Data Load) using Data Pipeline and SQL Procedure in Warehouse. Warehouse Support read and write SQL to SQL procedure has been used. In this Video I have not used Data Flow Gen 2
00:00 Overview of Incremental ETL
03:00 Create Warehouse and Load Data
08:30 Create Data Pipeline for Incremental Data Load
19:30 Load Data Using SQL procedure and Pipeline
23:30 Load Data Incremental Data using pipeline again
27:30 Analyze Data in Power BI
CREATE TABLE [dbo].[incr_info]
(
[tablename1] [varchar](4000) NULL,
[collectdate] [datetime2](6) NULL
)
insert into [dbo].[incr_info] values('sales', '2018-01-01');
CREATE TABLE dbo.sales_stg
(
order_no bigint,
item_id bigint,
sales_date datetime2(6),
customer_id bigint,
city_id bigint,
qty float,
price float,
cogs float,
discount_percent float
)
;
CREATE TABLE dbo.sales
(
order_no bigint,
item_id bigint,
sales_date datetime2(6),
customer_id bigint,
city_id bigint,
qty float,
price float,
cogs float,
discount_percent float
)
;
CREATE PROCEDURE dbo.loadSales
AS
BEGIN
insert into dbo.sales(
order_no ,
item_id ,
sales_date ,
customer_id ,
city_id ,
qty ,
price ,
cogs ,
discount_percent
)
select A.* from
(select order_no ,
item_id ,
sales_date ,
customer_id ,
city_id ,
qty ,
price ,
cogs ,
discount_percent from dbo.sales_stg ) A
left Join dbo.sales f on a.order_no = f.order_no
where f.order_no is null ;
update dbo.incr_info
set collectdate = (select max(sales_date ) from dbo.sales_stg)
where tablename1 = 'sales';
end
GIT File link - github.com/amitchandakpbi/pow...
All files: github.com/amitchandakpbi/
Power BI - Azure Free Tier, Create Azure Data Lake Gen 2 and connect that in Power BI: • Power BI: Connect with...
Get Azure SQL DB and Configure it on Power BI: • Abstract Thesis 93: Po...
Official Document: learn.microsoft.com/en-us/fab...
Video from GuyinaCube: • Microsoft Fabric Launc...
Buy a Microsoft Fabric subscription: learn.microsoft.com/en-us/fab...
SKU: learn.microsoft.com/en-us/fab...
Licenses: learn.microsoft.com/en-us/fab...
Enable Fabric: learn.microsoft.com/en-us/fab...
🔵 What is Microsoft Fabric
Microsoft Fabric is an all-in-one analytics solution for enterprises that covers everything from data movement to data science, Real-Time Analytics, and business intelligence. It offers a comprehensive suite of services, including data lake, data engineering, and data integration, all in one place.
With Fabric, you don't need to piece together different services from multiple vendors. Instead, you can enjoy a highly integrated, end-to-end, and easy-to-use product that is designed to simplify your analytics needs.
How to enable Fabric, find details - learn.microsoft.com/en-us/fab...
Microsoft Fabric licenses details -learn.microsoft.com/en-us/fab...
Don't forget to like, comment, and subscribe for more great content!
▶️Please Subscribe: / @amitchandak
▶️Data:
• Covid 19: covid19.who.int/WHO-COVID-19-...
• Get The data and Pbix files on Github: github.com/amitchandakpbi/pow...
• DropBox files: www.dropbox.com/sh/90ycq7hpmn...
---------
▶️Follow me on:
• Medium: / amitchandak
• Twitter: / amitchandak78
• LinkedIn: / amitchandak78
---------
▶️My Other Videos:
• Mastering Power BI: • Power BI for Beginners...
• Expertise Power BI: • Expertise Power BI- Ho...
~-~~-~~~-~~-~
Please watch: "Microsoft Power BI Tutorial For Beginners✨ | Power BI Full Course 2023 | Learn Power BI"
• Microsoft Power BI Tut...
~-~~-~~~-~~-~

[@shafa7668]

By far the best series on Fabric on KZbin. Currently I am consuming anything and everything available on Fabric on KZbin . Your contents are the best. Keep up the good work.

[@AmitChandak]

Thanks a ton 🙏

[@arthurvanrijn68]

Very nice. Thank you.

[@AmitChandak]

Thank you too! 🙏

[@user-dy8xu7uj8k]

Hi Amit, Good Morning!, I have to convert the existing SQL server stored procedure into fabric environment, In my stored procedures there are CURSOR commands but fabric doesn't support CURSOR commands, in this case how do I proceed, is there any alternative.

[@AmitChandak]

I have tried something very similar, TSQL loops learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-loops Search for Azure Synapse solution without using cursor

[@user-lj9fk8dg9h]

Hello sir, I wanted to ask that:- How can we delete a table residing in Fabric warehouse from Pipeline. It allows to delete from a Lakehouse, but not from warehouse. Can you please assist here

[@AmitChandak]

There is delete files activity. Not sure , I saw delete table. But we can call procedure and that can do that

[@supreetkaur5362]

I am trying to create table in fabric warehouse but seems like I have no access to create how to fix it.

[@AmitChandak]

If you don't have admin privileges in the Workspace, it's important to note that an admin can set restrictions, limiting your ability to either read or read data. This could be why you're unable to create a table. Additionally, ensure that you're working with the warehouse, not just the SQL endpoint of the Lakehouse, as this can also affect your access and capabilities. Hope this helps clarify things! If you have further questions, feel free to ask

[@notoriousft]

How did you create on-prem Postgres connection? Cause currently data pipelines in Fabric don't let you connect via on prem data gateways. Are you using some cloud hosted version such as elephantsql?

[@AmitChandak]

For On-prem, I used only for SQL Server and that worked with Dataflow Gen 2 not with Data pipeline. Also over this weekend it was not working.