BLOGS

ByHariharan Rajendran

[Solved] Microsoft Access: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

This post helps you solve the below issue in Power BI.

Scenario: There will be an error when we connect with 32-bit Access Database from Power BI 64-bit desktop.

Error: “Microsoft Access: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine”.

Solution

To solve the above issue, follow the below steps.

  1. Visit https://www.microsoft.com/en-gb/download/details.aspx?id=13255
  2. Download 64-bit version of the software.
  3. Install on the machine where Power BI is installed.

Once completed the above steps, try to connect access DB from Power BI desktop. This time you will be able to connect without an issue.

Post your comments if you get any more issues in this scenario.

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 (EmpoweringEveryPerson.com (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 www.EmpoweringEveryPerson.com 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.

 

PROMOTE & PARTICIPATE

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

1. DEVELOPERS

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.

2. COMMUNITY LEADERS

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

3. END USERS / VOLUNTEERS

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

ByHariharan Rajendran

How to handle date in R

Date data type is very important for all programming languages. To handle the date in a proper way, we need to apply some formatting logic.

This is the case for R programming language as well. This post explains you, how we can handle the Date data in R. There are different functions available in R to handle date.

To get the today’s date, use Sys.Date()

Sys.Date()

> Sys.Date()

[1] “2018-05-22”

 

To get date and time, use date()

> date()[1] “Tue May 22 15:29:18 2018”

 

Check the below list of symbols to play with date and time format.

Symbol Meaning Example
%d day as a number (0-31) 01-31
%a
%A
abbreviated weekday
unabbreviated weekday
Mon
Monday
%m month (00-12) 00-12
%b
%B
abbreviated month
unabbreviated month
Jan
January
%y
%Y
2-digit year
4-digit year
07
2007

 

> today <- Sys.Date()> format(today, format=”%B %d %Y”)[1] “May 22 2018”

 

Date Conversion

In a real-time scenario, we will not get date as date data type. Always, we need to convert to proper date data type. To convert to date data type, we need to use as.Date() function.

Syntax

as.Date(x, “format”)

x – Field or column

format – use the above symbols to frame the proper format.

 

DateVec<- c(“01/05/2018”, “08/16/2018”)

DateVec

[1] “01/05/2018” “08/16/2018”

summary(DateVec)

Length     Class        Mode

2            character   character

dates <- as.Date(DateVec, “%m/%d/%Y”)

dates

[1] “2018-01-05” “2018-08-16”

summary(dates)

Min.      1st Qu.       Median         Mean      3rd Qu.         Max.

“2018-01-05” “2018-03-01” “2018-04-26” “2018-04-26” “2018-06-21” “2018-08-16”

ByDr. SubraMANI Paramasivam

Run Your Python Script

Use the below console to run your python scripts

# Assign value to the variable # a = 5 #Print
Print
ByHariharan Rajendran

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.
1. REST API Url
2. REST API Url with specific dataset id

The base URL of the REST API will be like below,
http:///Reports/api/v2.0

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

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