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
Power BI Bookmark and Selection
Microsoft Power BI recently released new features as part of October release. In that, the below two features are very important to consider for our reporting solutions.
- Bookmarking
- Selection Pane.
First, let me explain the selection pane feature. It is useful to show and hide any report elements in the report.
If we see selection pane alone, then we can’t identify the importance of that but, this selection pane will combine with the bookmark and do magic.
Please check the below report and play with different chart types.
Take your time and think that how this report works. Actually, it is a simple when you see the report but in the backend it using the selection and bookmark features.
Before explaining how the report is built, let me explain what bookmark feature is.
Bookmark
It is a preview feature which means it is not generally available. Microsoft Power BI team announced this feature during Data Insight Summit. They have created a hype to the feature.
We can create a bookmark for the interesting stats. For example, if you want to create a data story or you want to present the report to the business users to show the sales and revenue information.
At that time, you may need to show metrics for last year sales and this year sales for comparison and same for revenue or you may need to highlight a specific visual (it can be done with spotlight, refer here).
With the single report you need to filter the data during the presentation but instead, you can create a bookmark for each one of the results and can show them easily.
Learn more about Bookmarking feature in Power BI site.
Let me explain how the report is built with the help of bookmark and selection pane. Follow the below steps to reproduce the report.
- Create a report with pie charts
- Create a bookmark as pie
- Add the donut charts on the same page where pie charts are placed
- Create a bookmark as Donut
- Add column charts on the same page where pie and donut charts are placed
- Create a bookmark as Column
- Open the selection pane and hide the donut and column charts and update the pie bookmark
- Choose Donut bookmark and hide pie and column charts and update the bookmark
- Same for column bookmark
- Add the slicer on top and added three images (pie, donut & column chart icons)
- On each image, on Link option choose bookmark type and select the relevant bookmarks
That’s it. Please let me know your comments below also share if you have any other report logics with these features.
Power BI Spotlight
It is one of the features recently released by Microsoft Power BI Team.
Actually it a very simple feature but more effective. You can check the spotlight for any visuals or elements that you added in your report.
You can see this option when you click “…” (Three dots) on any visual’s top right side.
It enables you to highlight only the specific report element from your report. During the presentation, if you want to highlight any visuals or elements then spotlight can be used.
It also can be added as a separate bookmark. Refer bookmark feature in Power BI.
Explore Power BI Report Server
Microsoft Power BI team releases a Power BI Report as generally available. We can get this report server as part of Power BI premium but still, we can use the trial version for 180 days.
This post explains to you that how we can setup the Power Bi report server. You can download the Power Bi Report Server here.
Components
It contains two components
- PBIReportServer.exe
- PBIDestopRS (32 & 64 bit)
Installation
It is easy to install Power BI Report server in your system. Just follow the below screenshots and click Next and complete the installation.
Once the installation is completed, we need to configure the Report Server which is nothing but our regular SSRS report server configuration but for Power BI.
Configure as usual and test the web service and web portal URL.
To start developing the reports, need to install the Power BI Desktop RS which is same like regular Power Bi desktop but with few changes.
We can save the report in our on-premises portal with the PBIDesktop RS tool.
Check the general information about Power BI Report Server.
| Report Server | Power BI Report Server |
| Instance ID | PBIRS |
| Product Version | 14.0.600.286 |
| Service | Power BI Report Server |
| Log on Account | NT SERVICE\PowerBIReportServer |
Azure data Sync Update
Microsoft Azure recently released clear road map of Azure Data sync. It replaces the SQL data sync service in classic portal.
If any of the customers are using SQL Data Sync then they need to migrate those sync groups to new portal.
Following the announcement, Azure classic portal not allowing us to create sync and even the SQL databases also. Microsoft Azure team trying to migrate all the services to new portal and in one point of time, Microsoft will remove the classic portal.
As like the previous service, this migrated service also still in preview.
R Packages in Power BI
Power BI service supporting many R packages as of now and it will increase in future. There is a misunderstanding that we can use these packages in Power BI Desktop R visual.
We can’t use all the packages in the Power BI site because it includes the packages which can be used for R custom visuals development.
Check the list of packages that Power BI Service supports, here.
Let us take an example,
There is a package called “timevis” which is part of Power BI supported packages.
We can use this package to build a visual to display a time range information. I used this package in R studio and got the below visual.
You can use the below R script and try it by yourself. This package requires many dependent packages which you can identify while installing the R scripts. If any dependent packages are missing then you should install those missing packages and finally run the R script.
library(timevis)
input<- data.frame(
id = 1:4,
content = c(“Apple”, “Orange”, “Mango”, “Banana”),
start = c(“2017-01-10”, “2017-01-11”, “2017-01-20”, “2017-02-14 15:00:00”),
end = c(“2017-02-13”, NA, “2017-02-04”, NA)
)
timevis(input)
It is easy to identify which packages are supported in Power BI from the list. Prepare your R script from any of the packages and make sure to check the supported package list and run the script in R studio.
If your package producing a visual and make sure that visual is displayed under plot section of R studio. If you see the visuals in plot section then those packages can be used in Power BI.
In our example, the visual is displayed under viewer section which means it is using HTML viewer which is not yet supported by R visual in Power BI.
Azure SQL Data Sync in Azure New Portal
Azure Data Sync is the feature to synchronise the data between Azure SQL databases or Azure SQL Database to On-premises databases.
Microsoft Azure team released Azure Data Sync option in new portal.
Check the below road map of Azure Data Sync update from Microsoft.
- Migrate existing sync groups to new portal – Starting from June 1, 2017
- Data Sync in Azure new portal – June 15, 2017
- SQL Data Sync retired from classic portal – July 1, 2017
- SQL Data Sync retired – September 1, 2017
While writing this post, we got data sync option available in the new portal.
Follow the below steps to check the new option in new portal.
- Go to new portal, portal.azure.com
- Go to your Azure SQL Database
- Check for the option “Sync to other databases”
Analyzing Your WordPress Site with Power BI
Power BI is very famous for self-service BI which includes a variety of data sources. As it supports almost all famous data sources, sometimes we are getting a question that Power BI does have any data source for WordPress blog posts?
I could see most of them have this common questions. To answer this question, if I search Power BI desktop or online services, I couldn’t identify the dedicated WordPress data source.
But it is possible to connect WordPress with Power BI.
I take this opportunity to show how to connect your WordPress account with Power BI. We need to follow the below steps to visualise your post count, author, category and tag information in Power BI visualisation.
Pre-Requisites
It requires a plugin which should be installed on your WordPress blog.
- Visit your blog
- Go to your blog plugins section and click add new.
- Type “WP REST API” in the search box and you could see the below plugin.
- Activate the plugin
It captures all your blog information in JSON file format and through API we can access the JSON file from Power BI.
Once the above configuration is successfully completed then follow the below steps in Power BI Desktop.
We are going to use Web data source to connect WordPress.
Getting the URL of your blog with plugin extension is a bit tricky part but easy. The format of the URL should be like below.
Blog URL + /wp-json/wp/v2/ +Section
In my case, http://myblog.com/wp-json/wp/v2/ is the URL. Here section is nothing but tables which holds the specific information.
The most common tables,
- Posts
- Users
- Categories
- Tags
The framed URL will give you only 10 records as a result. To get all the records from WordPress we need to follow the below strategy.
WordPress stored all the information in pages so we need to play with pages to get all the records.
Modify your URL as like below,
http://myblog.com/wp-json/wp/v2/posts?page=1&per_page=100
In the above URL, I added the page and per page property. We can increase the page count based on the number of posts that we need to access. For example, if you have 200 posts in your blog then you need to extract two times. 100 is the maximum number of posts so we can change only the page to extract all the posts.
Once the above URL is passed in Power BI Web source as like below then you can see 100 records.
You can see an option called “To Table” which converts your list of records into the table. We need to click the “<-||->” symbol to expand.
Again, use the web data source and pass the URL and do the same procedures to expand the columns in table format.
Once you extracted all the posts, you could see multiple queries created for each 100 records. The next important step, we need to append all the queries into a single query.
Use “Append Queries” and add all the queries.
Finally, you will get a single query with all the records on it. You can use this query for visualisations.
Repeat the same steps for all other tables’ users & category.
http://myblog.com/wp-json/wp/v2/users
http://myblog.com/wp-json/wp/v2/categories
http://myblog.com/wp-json/wp/v2/tags
Once you added all the tables, need to prepare the relationship between the tables. Each table has a common id column with matching with Posts table.
Finally, you can start building the report with the extracted tables.
