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.
=Previous(sum(Fields!Sales.Value,”Day”),”Year”)
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.
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.
Screenshots.
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.
Screenshots.
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.
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
This post explains you very basic error that you get rarely and solution to that error,
Error: “Windows could not start the SQL Server on Local Computer. For more information, review the System Event log”.
The below error message will appear when your SQL Server evaluation period is expired.
Check the information in Event Viewer.
Solution
Buy the server and use the product key.
You can visit Maintenance in SQL Server installation and can upgrade.
This simple post highlights the top level updates on components in SQL Server Management Studio.
The latest release of SQL Server Management Studio v17 RC3 has updates on below section compare than the earlier version.
This blog post explains the issue that we usually face while dropping the users from the database and how can we resolve the issue.
We can create a user for database easily by any of the following user types,
If we want to drop the user then we can write drop statement but it will throw a below error in case the user owned any of the schemas.
Msg 15138, Level 16, State 1, Line 20
The database principal owns a schema in the database, and cannot be dropped.
To resolve the issue, Follow the below steps.
Step 1: Check the schema that owned by the user.
SELECT *
FROM sys.schemas s
WHERE s.principal_id = USER_ID(‘Username’)
Step 2: Change the schema back to dbo.
ALTER AUTHORIZATION ON SCHEMA::<schemaname> TO dbo;
Step 3: Drop the user
DROP USER <username>