Author: Vigneshwaran A

You can learn whatever you believe that you can

Power BI Dashboard Report

Overview:-

Power BI is a dashboard reporting tool, also an user friendly tool which can get inputs from various data sources (SQL Server, excel, csv, analysis services and text files) and generate a graphical and meaningful dashboards. In general

Report – is a single visual representation

Dashboard – is a two or more reports/visuals together in same page

Power BI Desktop has 3 tabs mainly Reports, Data and Model (as shown below)

i) Model – is a data source view diagram to view all imported tables objects from any one of the input data sources

ii) Data – place where you can preview the sample data of the imported table objects

iii) Reports – for creating dashboards/reports from the model table objects

Following are the other tabs which are used to support the Model, Data and Reports tab

  1. Data Sources – used to get the Input data for Dashboards/Reports
  2. Visualization – used to show the data in more readable and understandable formats
  3. Fields – contains the table objects along with Measures (names) and Values (sales, score)
  4. Filters – used to filter data on page level or visual level

Below is the sample SQL data consists of 3 tables which has employee’s Mathsmark, Interest and Sales data. Execute this script in your SQL Server Management Studio before start creating Power BI dashboard, it acts as a Data input for Power BI Dashboard.

Create Database Power_BI
GO

---Employee's Mathsmark---
drop table if exists Mathsmark
create table Mathsmark(
	EmpName Nvarchar(1000),
	Score Int
)
Insert into Mathsmark (EmpName,Score) 
values ('John',50),('Mac',35),('Lion',70),('Tiger',62),('Pigeon',23),('Alex',92),('Jack',84)

---Employee's Interest---
drop table if exists Interest
create table Interest(
	EmpName Nvarchar(1000),
	Games Nvarchar(1000)
)
Insert into Interest (EmpName,Games) 
values ('John','Cricket'),('Mac','Football'),('Lion','Hockey'),('Tiger','Football'),('Pigeon','Cricket'),('Alex','Chess'),('Jack','Football')

---Employee's Sales---
drop table if exists Sales
create table Sales(
	EmpName Nvarchar(1000),
	SalesNo Int
)
Insert into Sales (EmpName, SalesNo)
values ('John',550),('Mac',305),('Lion',730),('Tig
er',610),('Pigeon',260),('Alex',950),('Jack',870)

In this blog, we will see how to create Power BI dashboard with Line Chart, Pie Chart, Stacked Column Chart, Matrix, Table and Donut Chart, Let’s see step by step procedure for creating the Dashboard.

Step 1:- Importing of SQL Data to Power BI Desktop, select “SQL Server” from “Data” tab and give your Servername and Database as “Power_BI” and click “OK”

Step 2:- Check all 3 table check boxes and click “Load”, you can also view the preview data by selecting the table object

After clicking the “Load” button, you can view the data model of “Power_BI” database in the “Model” tab, objects relationship are created automatically by the Power BI tool, all the table objects fields in displayed in right side “Fields” tab

You can also view table’s preview data and objects names, fields in “Data” tab

Step 3:- Select “Line Chart” in Visualizations tab and check the fields in “Mathsmark” table and also sort the line chart by “EmpName” field

Step 4:- Select “Pie Chart” in Visualizations tab and check the “Games” fields in “Interest” table and drag “EmpName” field to values tab, it will display the no. of employees interested in which games

Step 5:- select “Stacked Column Chart” in Visualizations tab and check “Sales” table fields, as mentioned in above here I’ve sorted the data by “EmpName” Field

Step 6:- select “Matrix” in Visualizations tab and check the fields (EmpName, Score and SalesNo)

Step 7:- select “Table” in Visualizations tab and check the fields (EmpName and Games) from “Interest” table

Step 8:- select “Donut Chart” in Visualizations tab and check the “EmpName” field from “Interest” table and “SalesNo” field from “Sales” table

Now we will see how to filter data in dashboards/reports, in Power BI we have 3 level of filtering (Report/Visual, Page and All Page), please note that the report/visual level filter will appear only after selecting page level filter.

here we have used the page level filter in which (EmpName =”Jack”) is excluded, now the dashboard is showing all Employee’s data expect “Jack”

Happy Learning !! Please share it with your friends If you like this post.

Power BI Desktop Installation Guide

Overview:-

Power BI is a dashboard reporting tool which uses the various data sources (SQL Server, excel, csv, analysis services and text files) as input for reports/dashboards.

Benefits of Power BI:-

  1. Dashboard Reporting
  2. Easy and User Friendly
  3. Minimal Coding and no technical expertise is required
  4. Can use wide variety of data source
  5. Cloud Reporting (so you can access reports in mobiles)

You can download the Power BI Desktop setup file (PBIDesktopSetup_x64.exe) from any one of the below links,

https://powerbi.microsoft.com/en-us/downloads/
https://www.microsoft.com/en-us/download/details.aspx?id=58494

Power BI Installation Procedure:-

Step 1:- Double click the “PBIDesktopSetup_x64.exe” Setup file and Select Language and then click “Next”

Step 2:- Click “Next”

Step 3:- Accept the Terms and click “Next”

Step 4:- Change the default Installation Drive (If needed) and then click “Next”

Step 5:- Check the checkbox (if you need a desktop shortcut) and click “Install”

Step 6:- Installation is in progress, please wait for it to complete

Step 7:- Check the “Launch” checkbox and click “Finish”

Power BI Desktop will Appear, now you have successfully installed the Power BI Desktop in your Machine.

Happy Learning, To Explore and Create your first Power BI Dashboard, Please click here

How to identify the list of folders and files which has been modified on given date using SQL Server

In some situations you may face scenarios like “you need to know all the files and folders modified recently or modified after the specific date range to check and remove unnecessary files\folders from the Drive”, for this scenario you can make use of this script.

Pre-requisites:-

  1. PowerShell
  2. Access to “xp_cmdshell”

The below is the SQL stored procedure which uses 2 parameters (“Drive\Folder path” and “Date”), Date filter is an optional parameter, if you don’t supply any values, by default it will consider the files\folders which are modified from Yesterday (please note that you can change the default date filter value based on your requirement)

--EXEC [SP_Get_Directory_FilesInfo]  'D:\temp\scripts\','2020-04-01'
--EXEC [SP_Get_Directory_FilesInfo]  'D:\temp\scripts\'
Create or Alter PROCEDURE [dbo].[SP_Get_Directory_FilesInfo] 
(@path nvarchar(4000),@yyyymmdd date=NULL) AS 
BEGIN

declare @pscript nvarchar(4000)

IF @yyyymmdd IS Null
	SET @yyyymmdd= cast(DATEADD(DD,-1,getdate()) as date)

if right(@path,1)='\'
	set @path = substring(@path,1,len(@path)-1)

--To avoid using of system drive--
IF (Charindex('C:\',@path)>0  or charindex('c$',@path)>0) BEGIN
	Select 'Alert Message!! C-Drive is not supported' Return;
END

--PS Script--
set @pscript ='powershell.exe -ExecutionPolicy Unrestricted -command "Get-ChildItem -Recurse -Path "' + @path +
'" | Where-Object LastWriteTime -ge "' + CAST(@yyyymmdd  AS VARCHAR) +
'" | select-Object FullName, @{Name="''LastWriteTime''"; Expression={$_.LastWriteTime.ToString("''yyyy-MM-dd HH:mm:ss''")}}, Mode | Export-CSV -NoTypeInformation -path "' + @path + '\Outputfile.csv"" '

Exec xp_cmdshell @pscript, NO_OUTPUT;

drop table if exists tblcsv_data
create table tblcsv_data (aa nvarchar(max),bb nvarchar(max), mode nvarchar(100))

declare @c nvarchar(4000), @d nvarchar(4000)
set @c = @path + '\Outputfile.csv'

EXEC('
BULK INSERT tblcsv_data 
FROM ''' + @c +'''
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ''","'',  --CSV field delimiter
    ROWTERMINATOR = ''\n'',   --Use to shift the control to next row
    TABLOCK
);
')

set @d = 'del '+ @c
Exec xp_cmdshell @d, NO_OUTPUT;

delete from tblcsv_data where aa like '%Outputfile.csv%'

drop table if exists tbl_FilesInfo
create table tbl_FilesInfo (id bigint identity(1,1), 
FileNames nvarchar(max), 
LastModified Datetime,
isDirectory bit null
)

insert into tbl_FilesInfo (FileNames,LastModified,isDirectory)
select replace(aa,'"',''),
replace(bb,'"',''),
case when mode='d-----"' then 1 else 0 end isDirectory
from tblcsv_data

if not exists (select 1 from tbl_FilesInfo) Begin
	Select 'Please Check!! There are no files having Date Range greater than [' + CAST(@yyyymmdd  AS VARCHAR) + ']' as Status
	return;
	END

select  FileNames,
LastModified,
isDirectory
from tbl_FilesInfo
order by isDirectory desc,
LastModified desc

END

Sample Input Folder:-

Execution of SP with 2 Parameters:-

Execution of SP without Date Parameter:-

In addition to the above, here is the some example of PS scripts which you can directly use it in PowerShell and get the same desired results.

#PS Script 1 output will be displayed in powershell window
Get-ChildItem -Recurse -Path "C:\temp\script" | Where-Object LastWriteTime -ge "2021-04-02" | select-Object FullName, @{Name="''LastWriteTime''"; Expression={$_.LastWriteTime.ToString("''yyyy-MM-dd HH:mm:ss''")}}, @{Name = "Isdirectory"; Expression = {$_.Mode -replace "d-----","1" -replace "-a----","0"}}

#PS Script 2 output will be saved in .csv file
#If you want to save results to text file, please change export file name as Outputfile.txt
Get-ChildItem -Recurse -Path "C:\temp\script" | Where-Object LastWriteTime -ge "2021-04-02" | select-Object FullName, @{Name="''LastWriteTime''"; Expression={$_.LastWriteTime.ToString("''yyyy-MM-dd HH:mm:ss''")}}, @{Name = "Isdirectory"; Expression = {$_.Mode -replace "d-----","1" -replace "-a----","0"}} | Export-CSV "C:\temp\script\Outputfile.csv"

If you enjoyed this blog post, please feel free to share it with your friends!

Converting Row values into String with delimiter in SQL Server

In this post we are going to see how we can convert the Row values into comma separated String. Different SQL versions to achieve this (mentioned below), lets discuss one by one.

  1. Using Coalesce and Substring Function
  2. Using Replace Function
  3. Using Stuff Function
Using Coalesce and Substring Function

In this example, we have converted the column values (RowNO, RowValues) from RowsIntoString table into String value with comma delimiter using coalesce and substring SQL functions.

drop table if exists RowsIntoString

create table RowsIntoString (RowNo int, RowValues nvarchar(100))
insert into RowsIntoString (RowNo, RowValues) 
values (1,'first'),(2,'second'),(4,'third'),(4,'fourth'),(5,'fifth'),(7,'sixth'),(7,'seventh')
,(7,'eighth'),(9,'nineth'),(10,'tenth'),(11,'eleventh')

select * from RowsIntoString

---using Coalesce---
DECLARE @CSkeycol VARCHAR(MAX), @CSkeycol2 VARCHAR(MAX)
select @CSkeycol=COALESCE(@CSkeycol + ', ', '')+cast(RowNo as nvarchar) from RowsIntoString
select @CSkeycol as RowsIntoStringValue

select @CSkeycol2=COALESCE(@CSkeycol2 + ''', ''', '')+RowValues from RowsIntoString
select ''''+@CSkeycol2+'''' as RowsIntoStringValue

---using Substring---
declare @tmp varchar(MAX)=''
declare @tmp2 varchar(MAX)=''
select @tmp = @tmp + cast(RowNo as nvarchar) + ', ' from RowsIntoString
select SUBSTRING(@tmp, 0, LEN(@tmp)) as RowsIntoStringValue

select @tmp2 = @tmp2 + RowValues + ''', ''' from RowsIntoString
select ''''+SUBSTRING(@tmp2, 0, LEN(@tmp2)-2) as RowsIntoStringValue
Using Replace Function

In this example, we have converted the multi row string values into string value with comma delimiter using Replace SQL function.

declare @stg varchar(MAX)
set @stg='second
third
fourth
fifth
sixth
seventh'
select '''' + replace(@stg, char(10),''', ''') + '''' as RowsIntoStringValue
set @stg='1
2
3
4
5
6
7'
select replace(@stg, char(10),', ') as RowsIntoStringValue
Using Stuff Function

In this example, we have Concatenated the Rowvalues with comma which is having same RowNo (in other words, concatenating the RowValues column if RowNo column has more than one values) using Stuff SQL functions.

select * from RowsIntoString where RowNo in (4,7)

select distinct t1.RowNo,
  STUFF(
         (SELECT ', ' + RowValues
          FROM RowsIntoString t2
          where t1.RowNo = t2.RowNo
          FOR XML PATH (''))
          , 1, 1, '')  AS RowValues
from RowsIntoString t1
where RowNo in (4,7)
There is another post written by Latheesh which details another way with string_AGG.

Thanks for reading this blog post, If you enjoyed this, please share it with your friends!

Update() Trigger Function in Table

Update() function returns “True” in any case the Insert/update attempt is successful on the supplied column, the significance of this function is to trigger the action/code only if the supplied column is get updated or inserted successfully.

In the below example, I’ve used the Update() function for identifying any changes in “CodeValue” column of table “Configtbl” and ignored the column changes if its value is same.

“Configtbl_Audit” table will get loaded based on the changes in “CodeValue” column in “Configtbl”

drop table if exists Configtbl
drop table if exists Configtbl_Audit

create table Configtbl(
	 Code nvarchar(10) not null,
	 CodeValue decimal(10,2) not null
)
insert into Configtbl(Code,CodeValue) values ('VA1111',25.99),('VA1118',19.99)

create table Configtbl_Audit(
	 Code nvarchar(10) not null,
	 Old_Value decimal(10,2) not null,
	 New_Value decimal(10,2) not null,
	 DateModified datetime not null
)
 
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'TRG_UPD_NEW_OLD_VALUE' AND type = 'TR')  
   DROP TRIGGER TRG_UPD_NEW_OLD_VALUE 
GO 

CREATE TRIGGER TRG_UPD_NEW_OLD_VALUE  
ON Configtbl
FOR UPDATE   
AS   

declare @oldvalue decimal(10,2),@newvalue decimal(10,2), @Code nvarchar(10)
IF (UPDATE (CodeValue))  
BEGIN
	select @oldvalue = CodeValue , @Code = Code from deleted

	insert into Configtbl_Audit(Code, Old_Value, New_Value, DateModified)
	select @Code, @oldvalue, CodeValue, getdate()
	from inserted 
	where code=@Code
	and @oldvalue <> CodeValue

END;  
GO 

Output:-

View of Data in Configtbl and Configtbl_Audit tables before doing any updates:

After updating “Code” column in Configtbl table:

After updating “CodeValue” column in Configtbl table:

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!