Scenario:
Lets suppose we have an SQL Query, which is returning huge amount of data, which we want to limit while loading or Importing to Power BI using native query method.
SELECT
CL.[FiscalYear] "FY_Year",
CL.[FiscalQuarter] "QTR_Year",
RG.[Country] "CntryNam",
PR.[Prod_Name] "ProdNam",
SUM([COGS]) Gross_Sales,
SUM([Gross_Sales]) - (SUM([COGS])+SUM([Disc])) NetSales
FROM [dbo].[tbl_Calendar] CL
Inner Join
[dbo].[tbl_Sales] SL
ON SL.[Order_Date]=CL.Date_Id
Inner Join
[dbo].[tbl_Region] RG
ON SL.[Region_Id]=RG.[Region_Id]
Inner Join
[dbo].[tbl_Product] PR
ON SL.[Prod_Id] = PR.[Prod_Id]
Group By CL.[FiscalYear], CL.[FiscalQuarter],
RG.[Country],PR.[Prod_Name]
# Result:
In our scenario, we will be using the ROW_NUMBER generated in SQL Query result set and a Parameter in Power BI to limit the data load in Power Query, as discussed below.
1) First generate a ROW_NUMBER (as Row_ID) in SQL Query:
Select * From
(
Select
ROW_NUMBER() OVER ( ORDER BY SrcQry.[FY_Year]) AS Row_Id,
SrcQry.* From
(
-- Orginal/Base Query Start
SELECT
CL.[FiscalYear] "FY_Year",
CL.[FiscalQuarter] "QTR_Year",
RG.[Country] "CntryNam",
PR.[Prod_Name] "ProdNam",
SUM([COGS]) Gross_Sales,
SUM([Gross_Sales]) - (SUM([COGS])+SUM([Disc])) NetSales
FROM [dbo].[tbl_Calendar] CL
Inner Join
[dbo].[tbl_Sales] SL
ON SL.[Order_Date]=CL.Date_Id
Inner Join
[dbo].[tbl_Region] RG
ON SL.[Region_Id]=RG.[Region_Id]
Inner Join
[dbo].[tbl_Product] PR
ON SL.[Prod_Id] = PR.[Prod_Id]
Group By CL.[FiscalYear], CL.[FiscalQuarter],
RG.[Country],PR.[Prod_Name]
-- Orginal/Base Query End
) SrcQry
) IndexQuery
Where IndexQuery.Row_Id >=0
GO
# Result:
2) Next create a Power BI Parameter "LoadFirstN":
Create a Power BI Parameter of the type Decimal Number which will be used in Power Query to limit the Data Load, by passing it to the SQL Query.
It is an Optional Parameter, we can pass the values as per below:
- If we pass the value as 100 then it will allow the query to load the First 100 rows.
- If we pass the value as 0 then it will not return any rows (only headers) from Query.
- If we leave it blank then it will return all the rows from the Query.
3) Next create the required Variables in Power Query based on "LoadFirstN", which will using in defining the logic for Data Load.
vLoadOpera = if (LoadFirstN = null) then ">=" else if LoadFirstN=0 then "=" else "<=" ,
vLoadRows = if (LoadFirstN="" or LoadFirstN is null) then 1 else if LoadFirstN=0 then 0
else LoadFirstN
"vLoadOpera" is a Power Query variable used to define the logical operator to be applied in SQL Query.
"vLoadRows" is a Power Query variable used to define the logic for no. of rows to return from Query.
4) Finally, Pass the Variables and Parameters into the SQL Query in the Power Query:
let
vLoadOpera = if (LoadFirstN = null) then ">=" else if LoadFirstN=0 then "=" else "<=" ,
vLoadRows = if (LoadFirstN="" or LoadFirstN is null) then 1 else if LoadFirstN=0 then 0 else LoadFirstN,
vSrcQuery = "Select * From
(
Select
ROW_NUMBER() OVER ( ORDER BY SrcQry.[FY_Year]) AS Row_ID
SrcQry.* From
(
-- Orginal/Base Query Start
SELECT
CL.[FiscalYear] ""FY_Year"",
CL.[FiscalQuarter] ""QTR_Year"",
RG.[Country] ""CntryNam"",
PR.[Prod_Name] ""ProdNam"",
SUM([COGS]) Gross_Sales,
SUM([Gross_Sales]) - (SUM([COGS])+SUM([Disc])) NetSales
FROM [dbo].[tbl_Calendar] CL
Inner Join
[dbo].[tbl_Sales] SL
ON SL.[Order_Date]=CL.Date_Id
Inner Join
[dbo].[tbl_Region] RG
ON SL.[Region_Id]=RG.[Region_Id]
Inner Join
[dbo].[tbl_Product] PR
ON SL.[Prod_Id] = PR.[Prod_Id]
Group By CL.[FiscalYear], CL.[FiscalQuarter],
RG.[Country],PR.[Prod_Name]
-- Orginal/Base Query End
) SrcQry
) IndexQuery
Where IndexQuery.Row_ID" & vLoadOpera & Number.ToText(vLoadRows),
Source = Sql.Database(ServerName, Database,[Query=vSrcQuery]),
SelectCols = Table.SelectColumns(Source,{"FY_Year", "QTR_Year", "CntryNam", "ProdNam", "Gross_Sales", "NetSales"})
in
SelectCols
Notes:
ServerName : The Parameter for SQL Server Name.
Database : The Parameter for SQL Database Name.
vSrcQuery : The Power Query variable, which holds the source SQL Query.
# Result:
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.