Analyse Your WordPress Website Stats in Power BI

Analyse WordPress Website in Power BI

WordPress is one of the most popular open source website making tools which is vastly used by many bloggers including myself. WordPress developers provide tons of custom plugins to fulfill different needs. But not all of the plugins are well designed and secured.  One of the most interesting information anyone who owns a website/blog needs is their website statistics. In this post I explain how to analyse your WordPress website stats in Power BI. Before I start, I want to point out that there is a WordPress stat already available in WordPress Admin Dashboard which provides very informative information about your blog like Total Views, Today Views, Best Ever Views and so forth. You can also install the WordPress app on your mobile device to easily access your website stats. But, the stats WordPress gives me in not enough. I want more. I need more detailed analysis on

  • Current Month vs. Last Month
  • Current Year Vs. Last Year
  • Most Popular Day of Week
  • Most Popular Month of Year
  • Top 10 Posts

and so on.

As non of the above analysis are available in the normal stats , I decided to build my own version of “WordPress Website Stats Analysis in Power BI”. This gives me the flexibility of creating as much analysis as I need , and… it is so much fun.

If you own a WordPress blog or any other sort of websites or if you’re just curious to learn how to use a website API in Power BI, then this post is for you.

I managed to create a Power BI Desktop template that you can download and use it for free. You’re welcome to modify it based on your needs. You can find the download link at the button of the page.

How It Works

To be able to analyse your WordPress stats in Power BI you need to own a WordPress blog or website. Then a WordPress API key is assigned to your account. The key was included in your WordPress Welcome Email. You can use that API key in Power BI Desktop to create your customised reports and analyse your blog/website stats then you can publish the model into Power BI Service that is accessible anytime anywhere. You can also create your own dashboard in Power BI Service.  Moreover, you can setup Schedule Refresh for the dataset to refresh your dashboards and reports automatically.

Requirements

You’ll need to

  • own a WordPress blog/website and have the API assigned to your account
  • install Jetpack plugin in your blog/website as WordPress stats is no longer maintained and you should not use it. Instead, you can install Jetpack plugin
  • have Power BI Desktop installed on your machine (Download it from here, it’s free!)

Continue reading “Analyse Your WordPress Website Stats in Power BI”

Visualise SQLite Data in Power BI

SQLite + Power BI

As the name implies, SQLite is a light weight transactional SQL database engine. It is open-source and free for use either for personal commercial. SQLite is widely used in mobile apps and many other desktop applications that need an internal light weight free SQL database. In this post I explain how to visualise SQLite data in Power BI.

Requirements

To be able to go through the process you need to meet the following requirements:

  • Latest Version of Power BI Desktop (Current version: 2.52.4921.682 64-bit (November/2017))
  • Install SQLite ODBC driver: Make sure you install 64 bit version if your Power BI Desktop is 64 bit version like mine!

Note: You may install both x32 and x64 bit versions of the driver if your other applications are in x32 bit.

  • An existing SQLite database

Note: In case you just want to try this and you don’t currently have a SQLite database you can download a SQLite version of “Internet Sales” of AdventrueWorksDW2016 that I made available for you.

How it works

Like any other Power BI cases, it’s really easy to load data from an existing SQLite database to a Power BI Desktop model. You just need to use ODBC data connection and connect to a predefined “ODBC DNS” OR use a “Connection String”. I’ll explain both methods. After you load data to your Power BI Desktop, you create the relationships then you’re good to go and create flashy reports. Continue reading if you’re interested to an step-by-step guide to visualise SQLite data In Power BI.

Importing SQLite Data to Power BI Using ODBC DSN

  • Open the correct version (x32, x64) of ODBC after you downloaded and installed SQLite ODBC Driver
  • Click “Drivers” tab and make sure SQLite ODBC Driver(s) successfully  installed

ODBC Drivers Continue reading “Visualise SQLite Data in Power BI”

Webinar Materials: Power BI Under the Hood

Pass DW BI VC Power BI Under the Hood with Soheil BakhshiI’d like to thank you all for attending the webinar held on 30th September 2016. I talked about some amazing under cover aspects of Power BI Desktop model. In this session you learnt:

If you’ve missed the webinar you can watch it online here:

Download the Power Point presentation file here.:

Download (PPTX, 1.91MB)

Here is the PDF version of presentation:

Download (PDF, 534KB)

Webinar Materials: Visualising Azure SQL DW with Power BI

Power BI Azure SQL DW PassIn the previous post I announced that I will speak in “Visualising Your Azure SQL Data Warehouse with Power BI” webinar on 23 Jan 2016. The webinar host was Pass Business Intelligence Virtual Chapter. It was such an amazing experience for me to speak in the webinar and I would like to thank all 105 attendees. The attendees showed their enthusiasm by asking lots of questions during the webinar.

In this webinar I demonstrated:

  • How to install Azure SQL DW in Azure Portal
  • How to configure firewall settings from Azure Portal and SQL Server Management Studio (SSMS) 2016
  • How to connect directly from Azure SQL DW to Power BI Service and the other way around
  • How to visualise you Azure SQL DW data warehouse data with Power BI Desktop (both Data Import and DirectQuery scenarios)
  • Comparing the features of different scenarios that helps you finding the best for your use cases

and much more…

You can see and download the session materials as follows.

Session Materials

Watch Visualising Your Azure SQL Data Warehouse with Power BI on YouTube

Continue reading “Webinar Materials: Visualising Azure SQL DW with Power BI”

Webinar: Visualising Your Azure SQL Data Warehouse with Power BI

Pass BI Virtual Chapter Visualizing Azure SQL Data Warehouse Power BI

It’s such an honor that I’m invited to speak in Pass Business Intelligence Virtual Chapter webinar which will be held on 23 Jan 2016.  I would be very happy to have you all in the webinar.

You can register here.

You can register and subscribe for Pass upcoming events here.

Agenda:

  1. Introduction to Power BI
  2. Introduction to Azure SQL Data Warehouse
  3. Requirements
  4. Demo
    • Azure SQL Data Warehouse Installation/Configuration
    • Connecting Directly from Azure SQL Data Warehouse to Power BI Service
    • Creating Simple Reports on Power BI Service
    • Visualising Azure SQL Data Warehouse Using Power BI Desktop
  5. Conclusion

Continue reading “Webinar: Visualising Your Azure SQL Data Warehouse with Power BI”