Thursday, 19 April 2018

How to Select a Source Table Dynamically with a Parameter in Stored Procedure of SQL Server

How to Dynamically Select a Source Table with a Parameter and Load Data to the Final Reporting Table using a Stored Procedure in SQL Server
Scenario :
Suppose we have 4 source tables which have data for respective 4 years, descending from Current Year..CFY ( eg: 2018) , PFY1(eg: 2017), PFY2(eg: 2016), PFY3(eg: 2015).
Whenever the Source data for a specific Year is ready, we would like to load it to the Final Reporting Table by dynamically selecting the data from that respective source table based on the Parameter we Pass in to the SP.
Eg :  
Exec  [dbo].[SP_UpdateReportingData] 'PFY2'
In the above the SP will update the PFY2 (2016) data from the respective Source Table 
"[dbo].[TBL_MySample_Source_Data_PrevFY2]" Dynamically to the Final Reporting Table 
" [dbo].[TBL_MyFinal_Reporting_Data]"

=====================================================================
USE [TPREDDY_DB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
--===================================================================
-- Created By            : <tpreddy123>
-- Create On             : <19thAPR2018>
-- Last Modified By : <tpreddy143>
-- Last Modified On : <19thAPR2018>
-- Description           : <Consolidate and loads the specific Year data to                                                                      Reporting Table>
--===================================================================

CREATE PROCEDURE [dbo].[SP_UpdateReportingData] (@sYear as Varchar(10))    
    
AS    

DECLARE @Update_FY AS INT  
DECLARE @Tbl_Name  AS SysName   
DECLARE @DynSQL AS NVARCHAR(MAX)   

BEGIN    
--Storing the Fiscal Year value based on Parameter value
SET  @Update_FY=CASE WHEN @sYear='CFY' THEN  YEAR(GETDATE()) 
WHEN @sYear='PFY1' THEN  YEAR(GETDATE())-1 
WHEN @sYear='PFY2' THEN  YEAR(GETDATE())-2
WHEN @sYear='PFY3' THEN  YEAR(GETDATE())-3   
END 

--PRINT(@Update_FY)

--Storing the Table Name to SysName Variable
SET  @Tbl_Name=CASE WHEN @sYear='CFY' 
                                                       THEN  '[dbo].[TBL_MySample_Data_Cur_FY]'
WHEN @sYear='PFY1' 
                                                       THEN '[dbo].[TBL_MySample_Source_Data_PrevFY1]'
WHEN @sYear='PFY2' 
                                                       THEN '[dbo].[TBL_MySample_Source_Data_PrevFY2]'
WHEN @sYear='PFY3' 
                                                       THEN '[dbo].[TBL_MySample_Source_Data_PrevFY3]'  
END 

--Assigning the Dynamic SQL Statement to a Variable
Set @DynSQL='Select Distinct * From '+ @Tbl_Name

--Next Deleting the respective Year data from the Reporting Table
Delete from  [dbo].[TBL_TBL_MyFinal_Reporting_ Data]   
                                        Where YEAR(Trans_Date )=@Update_FY

-- Next loading the respective Year Source data from a Sample Source Data Table
   INSERT INTO [dbo].[TBL_MyFinal_Reporting_Data]    
   EXEC sp_executesql @DynSQL  
----Eg: SELECT DISTINCT* FROM [dbo].[TBL_MySample_Data_Cur_FY]   

END

GO


------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Saturday, 10 February 2018

How to Create Row Level Security to restrict Users to View the Power BI Report

Row Level Security to restrict Users to View the Power BI Report
Row Level Security is a Security feature in Power BI Reports, that restricts the users to view the data in the report based on the User Roles assigned to their particular User Group.
Scenario:
If you want to allow the end users to view only the Sales in their specific Sales Region data, instead of viewing all regions, we can restrict them by defining the 
User Roles and assigning the Users to that User Group 

Example :
Suppose we have a Power BI Report , where we have the data of various Sales Regions like North America, Africa, Europe.




Now we will define the user roles on top of this report, based on the Sales_Region column as follows..

1) Go to Report tab Modeling  and then select the User Roles



2) Next Define the Team Name in Manage Roles and apply the required Filters on required Tables.
Here I have created to two Teams with below Filters on the Table vSalesByRegion :
Asia Team :
[Region_Name] = "Asia"

WorldWide_Team :
OR ([Region_Name]="Europe",[Region_Name]="North America")





3) Next validate the Roles by viewing the Report by defined Team Roles
View as Roles : Asia_Team:

Now the Report will shows only the Data for Asia Region as follows..

View as Roles : WorldWide_Team:
Now the Report will shows Data for Europe and North America Regions as follows..
4) Finally Publish the Report to Power BI Service and add the Users to the User Role Teams
Once we Publish Report to Power BI Service , we have to add the Users to the specific User Role Teams based on their Sales Region and requirement as follows..

In Power BI Service we will see the following things in the Work Space :
a) Dashboards
b) Reports
c) Datasets

Go to Datasets , then select our report Dataset on which we want apply the Row Level Security, as follows..

Click on the ...3dot icon next to the Dataset and then click on SECURITY option.
Next select the User Role Team ( Eg : Asia_Team) to add MEMBERS.
Next add the People or groups who belongs to this role, by specifying their email address.
Finally click Add.

Now People or groups who belongs to this role, will have access to view the data as per that Role definition we defined in Power BI Desktop Report.


Creating a Schedule Refresh Plan :
We can schedule the Power BI Reports to refresh automatically from Power BI Service

  • In the Work Space > Data sets , select your Report dataset and then click on ..3dot icon and then click on Schedule Refresh option.
  • On the Scheduled refresh page, select New scheduled refresh plan. 
  • On the New Scheduled Refresh Plan page, enter a description and set a schedule for when you want your data model to be refreshed. 

------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

How to create and use Dynamic Query Parameters in Power BI Report

Creating and using Dynamic Query Parameters as a Filters in Power BI Report
Scenario:
If you want to allow the end users to Filter and view the Sales in their specific Sales Region data, instead of displaying all regions, we can do it by creating dynamic Parameter in the Report.
We can create a dynamic Query Parameter as follows..
Example :
Suppose we have a Power BI Report , where we have the data of various Sales Regions like North America, Africa, Europe.

 Now we will create a Dynamic Query Parameter based on the Sales_Region of the Query.
1) Go to Edit Queries

2) Create a Query List from Region_Name Column for using in Parameter
From Query Editor > Select column Region_Name from base Table and then R-click and choose the option as Add as new Query

Next Remove the Duplicates from the List, and rename the List

Now a unique List (DistRegion) has been ready to use in Parameter

3) Create a Dynamic Query Parameter using the above List
Go to Home > Manage Parameters > New Parameter

Next create a Parameter and select the suggested values from Query
Now the Parameter appears as follows

4) Apply Filter to the base Table using Parameter
Select the Column Region_Name then got Text Filters > Equals..

Next pass the Parameter as a value

Now the Parameter with the default value "Europe" has been applied to the Report. Please note that this Parameter value will apply dynamically based on user input.

Now Save, Close & Apply the Query Editor Window.

5) Save the Report as Template and Share that Template to the End Users
Once we created and apply the Dynamic Query Parameter, Save as the Report as Power BI Template, which we will share to the end users. This will prompt the users to Select their Sales Region to Load the Report.

Now, when a User opens the Template it will ask to select their Sales Region to View the Report 

Now if we select a Sales Region as Asia, the we will see only the "Asia" Region data as follows..

Important Notes:
Here user can choose and view any Region Data with no Restriction.This will NOT provide a Row Level Security.

If you want to Restrict the users to view only a specific Region data, we should Create and Define the Roles in Power BI Desktop then assign the People to those Roles in Power BI Service.We can also setup the Schedule Refresh in Power BI Service.





------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts