MS SQL Server

SAMEPERIODLASTYEAR in SSRS Report builder

This article explains how to use SAMEPERIODLASTYEAR function in SSRS to achieve the same result as like in Power BI.

The challenging part is we don’t have SAMEPERIODLASTYEAR function in SSRS so we need to look for another function which should give the same result.

We have “Previous” function in SSRS, we are going to use that function to achieve the result. Let us get started.

First, let see the result of Power BI – SAMEPERIODLASTYEAR

SAMEPERIODLASTYEAR = CALCULATE(sum(DemoSales[Sales]),

SAMEPERIODLASTYEAR(DemoSales[SalesDate]))

Let us replicate the same result in SSRS.

First, understand the functionality of “Previous” function.

It will give the result of the previous row as the line below.

We need to tune the previous function to get the result as same as SAMEPERIODLASTYEAR in Power BI.

Follow the below steps.

Create a Parent Group called Year using “Year” Column
Create a Child Group called Month using “Month” Column
Create a child group called “Day” using “Day” column.

Change the previous function expression as like below.

=Previous(sum(Fields!Sales.Value,”Day”),”Year”)

That’s it. We achieve the result as same as Power BI.

Warning:

The result of the previous function will work only on the current data displayed. For example, if you create a parameter for year and filter for the year 2019 then it will not show any value for previous value.

ByHariharan Rajendran

SharePoint

Extract Data from SharePoint List using SSIS

Microsoft released a new task called “OData Source” which helps us to connect to online SharePoint and extract the lists.

Follow the below steps to configure and extract the data.

Make sure to use the latest version of SQL Server Data Tools to work with SQL Server integration Projects.
Use the Data Flow Task
You can see “OData Source” task. Drag and drop into the data flow canvas.
This OData Source task required a connection manager. Click New on the OData Connection Manager.
You need to pass the below information to successfully connect with SharePoint Online.
1. Service document location – https://<yoursharepointsitename>/_vti_bin/listdata.svc
2. Authentication Type: Microsoft Online Services
3. UserName: Your email address to connect SharePoint Online
4. Password: your password.
Test the connection.
On the OData Source Editor, Choose the SharePoint list on the collection section.
Check the columns and use any destination where you want to store the data.

Screenshots.

ByHariharan Rajendran

Extract Data from SharePoint excel using SSIS

There is no direct way of extracting the SharePoint Excel data to SQL Server using SSIS. However, we can process multiple steps to achieve the result.

This post explains that how we can download the excel files from SharePoint and store it in a local drive and then process the stored excel from SSIS.

To download the files easily, we can use the tool called “SSIS Integration Toolkit for Microsoft SharePoint”.

Follow the below steps.

Visit – https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-sharepoint/download
Download 64 bit of Toolkit
Install it on your system where you have installed the SQL Server Data Tools
Create a new SQL Server Integration Projects
Drag and Drop the data flow task into the control flow
You could see “SharePoint Source” and “SharePoint Destination” tools under Common folder.
Before Drag and drop the “SharePoint Source” task, you should create a SharePoint connection Manager.
Configure SharePoint Connection Manager for SharePoint on-premises or online.
Test the connection once.
Drag and drop the “SharePoint Source”. Point the created SharePoint connection and Choose the source list as “Documents” or the library where you have stored the excel files.
Configure the Download Destination.
Once excel files are downloaded then you can use that excel files to process further using excel source tasks.

Screenshots.

ByHariharan Rajendran

Python scatter plot in SQL Server 2017

As you know, Microsoft released the latest version of SQL Server which is SQL Server 2017. If you worked with SQL Server 2016 then you could realize that Microsoft SQL Server is not just a relational database anymore because it started to support big data and so many options to handle the non-relational data.
R Language is very popular to work with data science-related projects. It was integrated with SQL Server 2016 and we can run the R Scripts in SQL Server Management Studio itself.
It helps us to avoid the data movement between the relational database to R server and process. The same way, Microsoft now introduced Python integration with SQL Server. These 2 languages are coming from machine learning services in SQL Server.
To run Python scripts in SQL Server management studio, you need to enable the external script stored procedure.
Run the below command in your SSMS and see whether you are getting the “hello world” as an output.
In case, you are getting an error message then the configuration part was not properly done.
execute sp_execute_external_script
@language = N’Python’,
@script = N’
print(“hello world”)’
The below script is to generate the scatter plot.
DECLARE @Query nvarchar(max) = N’SELECT Year, Sales from [dbo].[SalesByYear]’

execute sp_execute_external_script

@language = N’Python’,

@script = N’

import matplotlib.pyplot as myplot

X = myplot.figure()

myplot.scatter(InputDataSet.Year,InputDataSet.Sales)

myplot.xlabel (“Year”)

myplot.ylabel (“Sales”)

myplot.title (“Sales by Year”)

myplot.savefig (“D:\Win – 8\myfig.png”)

‘,

@input_data_1 = @Query
Check the below step by step procedure to create a scatter plot using python.

ByHariharan Rajendran

SQL Server 2017

Graph database in SQL Server 2017

As you know Graph Database is one of the latest features from SQL Server 2017.
Let us first understand the purpose of Graph database. We have a relational database which handles most of the scenarios but as we are started to handle big data and complex scenario our database also should be capable enough to handle those scenarios.
Yes. Graph database handles those complex scenarios easily which I have explained. As part of graph database, Microsoft team introduced two different tables.
1. Node
2. Edge
Check out the below explanation of those tables and how to work with graph database.
Use the below scripts

Use SQL2017
—Create Main table
CREATE TABLE People (
ID INT PRIMARY KEY,
Name NVARCHAR(25)
) AS NODE;

–Create Edge Table for relationships
CREATE TABLE RELATIONSHIP (
TYPE NVARCHAR(25)
) AS EDGE;

—Insert values to People
INSERT INTO People VALUES (1, ‘David’)
INSERT INTO People VALUES (2, ‘John’)

SELECT * FROM People;

SELECT * FROM RELATIONSHIP;

–Create relationships
INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘David’),
(SELECT $NODE_ID FROM People where Name = ‘John’), ‘Father’);

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘John’),
(SELECT $NODE_ID FROM People where Name = ‘David’), ‘Son’);

—Cartesian Product result
–No need to use joins since nodes and edges are interconnected in structure
SELECT FromName.Name, RELATIONSHIP.TYPE, ToName.Name
FROM People AS FromName, People As ToName , RELATIONSHIP

–Proper Result
SELECT FromName.Name, RELATIONSHIP.TYPE, ToName.Name
FROM People AS FromName, People As ToName , RELATIONSHIP
WHERE MATCH (FromName-(Relationship)->ToName)

–more Records
INSERT INTO People VALUES (3, ‘Nancy’)

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘Nancy’),
(SELECT $NODE_ID FROM People where Name = ‘David’), ‘Daughter’);

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘David’),
(SELECT $NODE_ID FROM People where Name = ‘Nancy’), ‘Father’);

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘John’),
(SELECT $NODE_ID FROM People where Name = ‘Nancy’), ‘Brother’);

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘Nancy’),
(SELECT $NODE_ID FROM People where Name = ‘John’), ‘Sister’);

SELECT FromName.Name, RELATIONSHIP.TYPE, ToName.Name
FROM People AS FromName, People As ToName , RELATIONSHIP
WHERE MATCH (FromName-(Relationship)->ToName)

SELECT FromName.Name, RELATIONSHIP.TYPE, ToName.Name
FROM People AS FromName, People As ToName , RELATIONSHIP
WHERE MATCH (FromName-(Relationship)->ToName)
AND FromName.Name = ‘Nancy’

Share your comments below. Thank you

ByHariharan Rajendran

17051, Error