Author Archives: Dr. SubraMANI Paramasivam

ByDr. SubraMANI Paramasivam

Empowering Every Person in the planet, with “Awareness Enabled Reports”

Following Satya’s quote “Empowering Every Person in the Planet”, in 2016 Microsoft Inspire conference, have certainly left my mind with unstoppable beats. I certainly understood what Satya meant, but I still thought about doing something like this, with my own upgraded version of Data Awareness Programme (which I started in 2014). But at that time, all I had was Power View, Power Map, Power Pivot as Excel add-ons and tried my best, to do the awareness programmes in remote villages, by mingling with the villagers and collecting their own data and showing some visuals back to them. I did this to improve their life style, find more time for personal and have better earnings. Though the main target was students, I hoped this message would spread to their friends, family and others in the remote places.

Following the release of full version of Power BI, I now have a fully working site, with living “Awareness Enabled Reports”, from sleeping open data sources (taken from various Gov/Non-Gov sites).

I managed to get this far, with a simple equation of A + B + C + D = E ( (EEP)). Let me explain this in detail.

Following this, I managed to extract data from various open data sources and identified the most global challenges, that we are encountering and/or going to be a major threat in near future.

With our all time favourite reporting tool, “Microsoft Power BI“, published some “Awareness Enabled Reports” to site and categorized them with regional, national and global challenges, for easy manoeuvring within the EEP site.


This EEP site currently has 3 simple goals.

  1. View the “Awareness Enabled Reports” worldwide in any devices, by categorizing Regional or Global challenges.
  2. Submitting another “Awareness Enabled Report” as a Developer with some guidance. Also listed, Global Challenge Topics to select from open data sources and provided some tips to convince the selection committee and finally submitting the story.

  1. Promote EEP
    1. Provided options to Promote EEP as  a Developer, User Group Leader & End user.

Below screenshot shows categorization of reports by UK.



As per above equation, ‘D’ is the support that we need from you, to promote in any of the following ways.


Develop “Awareness Enabled Reports” for all listed Global Challenge topics and submit your data story to EEP site and once published, tweet / share / post in social media and spread the awareness.


A request to all Community group leaders, to spend at least a minute by starting or ending your user / local / online group sessions by introducing / re-introducing, this website and showcasing the opportunity to all attendees, to build and submit their own data story with “Awareness Enabled Reports“.


Every time you see a new “Awareness Enabled Report“, do tweet / share / post in social media and support to spread the awareness.


Thanks in advance for your support and thanks for your time reading through this far, to create awareness with “Awareness Enabled Reports“.

ByDr. SubraMANI Paramasivam

Run Your Python Script

Use the below console to run your python scripts

# Assign value to the variable # a = 5 #Print
ByDr. SubraMANI Paramasivam

Power BI Report using SSRS Shared Datasets

Creating a Power BI report is very easy, and it supports loads of data connectors. When we start creating a report against any relational database, we must choose any of the below query modes,
1. Import
2. Direct Query or Connect Live

For example, when you try to create a report against SQL Server database, then you need to choose any of the above methods based on the requirement.

If you take SSRS report server, we have below elements available
1. Paginated Reports
2. Mobile Reports
3. KPIs
4. Data Sources
5. Datasets

If you are SSRS administrator, then you can easily manage all these stuff in a centralized place.

Is it possible to use the SSRS dataset to create a Power BI Report?
Yes, it is possible. You must have the latest version of Power BI Report Server.
We have REST APIs and is supported in Power BI Report Server, which is an extended version of SSRS 2017. Check REST API on SSRS 2017.

We need to use the OData Feed data connector to connect with SSRS datasets.
Below is the information required to create a report.
2. REST API Url with specific dataset id

The base URL of the REST API will be like below,

ByDr. SubraMANI Paramasivam

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.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.

ByDr. SubraMANI Paramasivam

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 Edge Table for relationships

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



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

(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
FROM People AS FromName, People As ToName , RELATIONSHIP

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

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

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

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

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

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

FROM People AS FromName, People As ToName , RELATIONSHIP
WHERE MATCH (FromName-(Relationship)->ToName)

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

Share your comments below. Thank you

ByDr. SubraMANI Paramasivam

Try SQL Server 2016 Release Candidate 3 in Azure Virtual Machine today

Don’t waste your monthly credits and if you have some you can always spin it up with Azure virtual machines to try the SQL Server 2016 RC3 Evaluation on Windows Server 2012 R2.

This is a great and quick fun as you don’t need to manually download the ISO file and keep installing them like I mentioned in this article with step by step instructions.

As you can see in the below Azure portal screen, I am using New => Virtual Machine and then clicked on “See all”, to see all available options and also to search on what I need to search for. The prebuilt images are already available for all of us to try and you can see the highlights that I have made.


Then I got the below screen to confirm on what I was going to create and enable me to create it either with Resource Manager or Classis Deployment Model. In newer version Resource Manager is chosen as default for all deployment models.


In below screen I have given basic options  with Name, username, my subscription, resource group, location. Once you have chosen all your settings till 5, then you will get your fully functional SQL Server 2016 RC 3 virtual machine in 10 to 15 minutes. How cool is that.


ByDr. SubraMANI Paramasivam

Step by step instruction on installation of SQL Server 2016 RC3

Following the download of my ISO file from my earlier article, I am going to show you the step by step instructions on installation of SQL Server RC3.

Step 1 is to run the exe file and you get to the Planning tab. Below screenshot, shows the options available in next Installation tab. You can then click on first option which is “New SQL Server stand-alone installation or add features to an existing installation”.


Step 2: Here you have option to evaluate the free edition or enter the product key that you purchased already. Here I chose the Evaluation which is the default screen and then I clicked on next.


Step 3: In the License Terms screen, you have full terms that you can read through to accept the license from Microsoft before start installing.


Step 4: I got error message in the product updates section, as I did not had internet connection in my virtual machine as I had problems in bridging the connection during this setup process. You can click on “Check again” button if you have got the same error screen and still have the internet connection. Then you can click on Next or you can do the product updates later and you can directly click on Next button.


Step 5: Then the system starts installing the necessary setup files  and rules. In my below screen you can see the warning messages that I got. As long as it is not failing, we are good to go. So click on Next.


Step 6: Here you will get the options to choose the features. As I am doing main testing on R, SSRS, Polybase, Analysis services, I have ensured to check them all here. You can check/uncheck based on what you need to install.


Step 7: Here I got one task failed for not having Oracle JRE 7 Update 51 to support the polybase installation. After installing this seperately, I was able to continue my installation. Note: till this setup is not installed, the Next button won’t be enabled. Once I have this installation done, I made sure to click on “Re-run” button to test the latest and then this enables me to click on Next button. Here, no need to cancel and then no need to go through all above 6 steps.


Step 8: Then I get the option to configure the name of the instance and I chose it to reflect the RC3 here.


Step 9: As this is the setup in my standalone laptop, I chose the SQL Server as standalone polybase enabled instance.


Step 10: In Server configuration section, it shows all the relevant account names that will be created and the type of startup services (Manual or Automatic). When it is set to automatic, this will then start the services whenever you system is booted. If it is Manual, then you have to start it manually via the services or configuration manager.


Step 11: In the database engine configuration, I have chosen to opt for Mixed Mode to keyin my preferable password, and this also helps me to recover the instance if my current user gone cranky. Also I have added the current user YSMUser1 from the “Add Current User” button. I also have option to choose the data directories, tempdb and filestream.

In here, you also have the new feature of trying to have more than one tempdb and you can get it configured in this place.


Step 12: In Analysis Services Configuration tab, I have chosen to use the Multidimensional and Data Mining server mode and also added the current user to get admin rights.


Step 13: In reporting services configuration, I then have option to choose whether to install and configure or just install only. You still can configure at the later stage, however in regards to Reporting services, I always choose to get it configured as well as you can see below.


Step 14: For the distributed replay controller, I again choose my current user to give the admin permissions to manage it.


Step 15: Then you get to the place where you have option to choose where your controller name, working and result directories should be placed. I have not made any changes here and left it to reflect the default installation folder.


Step 16: This is very new specifically for SQL Server 2016 and you have to accept to download Microsoft R Open and install and also to agree to accept patches and updates for the software. This feature enables us to work on R side from Microsoft itself.


Step 17: I then get this screen to choose the installation path for the Microsoft R Open and Microsoft R Server.


Step 18: I have provided below install path for Microsoft R Open and Microsoft R Server to be installed.


Step 19: This is the last part where you can see the summary of installation on what necessary information you have provided to verify before installation. You still have option to go back to any configuration and make changes and then click on Next.


Step 20: This is the final screen where it says the installation was successful and advise to restart to complete the installation process.


Have good findings in your SQL Server 2016 journey.