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.
Power BI Report Server
Microsoft SQL Server Reporting Services is the product which provides solutions to all the business enterprises. While it was in peak, Microsoft released another new BI tool which is nothing but Power BI.
Now Power BI is leading the Business Intelligence Industry. As it is a cloud-based service, business users were not able to use that tool for their business solutions because of security concerns. They were waiting for on-premises based Power BI server.
Now the wait is over. Microsoft released Power BI Report server which can be installed and configured for on-premises business solutions.
Power BI Report server is available for preview, you can download, install and experience the on-premises based Power BI Report.
As it is very new, it supports only analysis services as a source but in future, we can expect the same source connectors as like in Power BI cloud services.
This blog post explains to you that how you can experience the Power BI Report server using the Azure VM template.
Follow the below steps to spindle up Azure VM with Power BI Report Server on it.
Step 1: Login Azure portal and add a new Virtual machine
Step 2: Search for Power BI and it will show the below template
Step 3: Click create
Step 4: Add the VM name and credentials and choose which AS server you want to use from the drop down.
Step 5: By default, it using the below settings. In case, if you get an error message then change the name of the storage.
Step 6: At last, you need to click the purchase button. It will create and deploy the Power BI machine.Once the machine is deployed successfully, connect the machine and you can see the Power BI.
Once the machine is deployed successfully, connect the machine and you can see the Power BI Report Server.It also adds an option to create Power BI Report from the portal itself.
Azure Cloud Shell
This post just to shows that how to configure the Azure Cloud Shell and its options.
Microsoft Azure recently announced this new feature, it helps us to run the Azure CLI commands directly on the portal itself.
It is required to configure the storage for one time.
Once the above configuration is completed successfully, you can see below the window.
In future, it is going to support power shell as well.
Check the Azure CLI commands in Microsoft documentation.
Windows could not start the SQL Server – Error & Solution
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.
Monitor Power BI Services Health
It always mandatory to monitor the services that we engaged with. It helps us to understand the status of the services and some additional information.
This is very much suitable for cloud based services like Azure because those services are managed and maintained by Microsoft Azure team.
To monitor the services, you should have admin access. Let us take a scenario with office 365.
Follow the below steps to monitor the Power BI Services,
- Connect O365 admin center
- Expand Health and choose service Health. It will list out all the services. If the services are working good then you can see a green tick mark near each service name.
- Identify the services that are showing any advisory.
- Check the advisory and understand the status details.
Multi-factor Authentication for Power BI – O365
It is easy to enable the multi-factor authentication for a user. To enable, administrator permission is required. Once we enable the multi-factor authentication to the user then the user should pass through the authentication wherever that user wants to access.
For example, if you are Office 365 admin and you are enabling multi-factor authentication for a user. That user should go through the second authentication to login to email, power bi or whatever services that are connected with O365.
Follow the below steps to enable Multi-factor Authentication,
- Go to office 365 Admin Portal
- Expand users -> Active users
- Select the user and click multifactor authentication in more settings
- Select the user and click enable
- Go to service settings and choose the appropriate options.
- Connect app.powerbi.com, sign-out and sign-in again. We can choose the method of authentication, either by phone call or sms.
Access Report Manager outside of Network
Nowadays, most of the organisations are using Azure Virtual Machine to host SQL Server Reporting services. By default, we can access the reporting service manager with-in same network. It won’t be possible to access the reporting services outside of network without setting up below configuration.
This is not only for SSRS in Azure VM. The same setup is applicable for the on-premises server.
Follow the below steps,
Step 1: Go to virtual machine resource group and select Network Security Group.
Step 2: Create a new inbound security role with service as HTTP, it will take 80 as a port.
Step 3: Create a same inbound rule inside the virtual machine. Use the below power shell script to create a rule.
New-NetFirewallRule -DisplayName “Report Server (TCP on port 443)” -Direction Inbound –Protocol TCP -LocalPort 80
Step 4: Enable DNS if not configured already. Click Public IP in Virtual Machine overview section in the portal. Set up the DNS name. It will look like below.
Step 5: Make sure report server is configured and working without an issue.
Step 6: Pass the DNS name with extension in your local machine as like below.
Step 7: Pass the credentials and it will show your reports.
Share your comments below.
Connect Azure Virtual Machines on a Same Domain
Connecting multiple machines on the same domain would be one of the requirement to all the network administrator to set up the environment for any scenario.
This post explains this scenario how administrators can they do this setup easily.
Requirement:
- Azure Virtual machine with Domain controller
- Virtual Network
- Another VMs
I pretend that you have a machine with a domain controller on it.
While creating another machine, you need to choose the first machine’s virtual network instead of creating a new one as like below.
Next, you can identify the IP address by typing ipconfig in command line tool.
Also, you can ping the machines each other. In case, if you are not able to ping then turn off your firewall.
Then, try to add the domain and it will throw an error message as like below.
To rectify the above issue, you need to add the domain control IP address on DNS Server. Go to Azure Portal and Network Interface.
In DNS Server add the domain controller IP address in custom and restart the machine.
Again log into the machine and now try to add the domain and this time you can add successfully. Share your comments, if any issues.