Tag: Dataviz

DAX, Power BI, Power BI Desktop, Power BI Service, Power Query

Analyse Your WordPress Website Stats 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 fulfil different needs. But not all of the plugins are well designed and secured.  One of the most interesting pieces of 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 a 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 the Jetpack plugin in your blog/website as WordPress stats is no longer maintained and you should not use it. Instead, you can install the Jetpack plugin
  • have Power BI Desktop installed on your machine (Download it from here, it’s free!)
  • have a Power BI account (Don’t have an account? Signup for it here, it’s free too!)
Continue reading “Analyse Your WordPress Website Stats in Power BI” →
BISM Tabular, DAX, Power BI, Power BI Desktop, Power Query, SQL Server Analysis Services - SSAS, SQLite

SSAS Tabular Model Documenter with Power BI without DMVs

SSAS Tabular Documenter with Power BITechnology is growing fast and we are enjoying it. We reshape our daily created data in a form that satisfies our needs. One of the technologies which is used more commonly these days is SQL Server Tabular Models, SSAS Tabular in short. Lots of industries decide to go with SSAS Tabular in their new projects and some defined new projects to slowly switch their existing SSAS Multidimensional to SSAS Tabular. I know, there is a big debate around SSAS Multidimensional vs. SSAS Tabular. But my aim is to prevent going through that sort of discussion. As the title implies, this post is about documentation that I believe is one of the most important parts of every project which is also sacrificed the most. In this post I explain how to document your SSAS Tabular model in Power BI Desktop and Excel. I know, there are some products you can find in the internet that can generate documentation in various formats like Word, PDF, HTML and so on. But, If you’re looking for a free and somehow more intuitive way of documenting your SSAS Tabular Models with Power BI then this article is for you.  Through this article, we create a documentation tool with Power BI. I call it SSAS Tabular Model Documenter. In this method we don’t use DMVs at all. For those who are not familiar with DMVs I shortly explain what DMVs are, if you’re already familiar with DMVs you can jump this section.

You can download a copy of SSAS Tabular Model Documenter in Power BI template format (pbit) at the end of this post. It is a Christmas present for you.

What are DMVs?

Dynamic Management Views, DMVs in short, are queries that retrieve metadata information about an instance of SQL Server Analysis Services. DMVs work on both SSAS Multidimensional and SSAS Tabular server modes. DMVs can be used to monitor server operations and health. The DMV query structure is very similar to T-SQL, therefore you use “SELECT” statement followed by “$System” which is an XMLA schema rowset. The DMV queries look like below:

SELECT * FROM $System.<schemaRowset>

So you can open SQL Server Management Studio, connect to an instance of SSAS (Tabular model for the sake of this post) and run the following query to get lots of information about tables in your Tabular model:

select * from $SYSTEM.TMSCHEMA_TABLES

Running DMVs in SSMSRead more about DMVs here.

Note:This method only works with SSAS Tabular 2016 and above.

Document SSAS Tabular without DMVs

In SSAS Tabular 2016 and above there is a tiny metadata database that can be loaded in Power BI Desktop or Excel to document the corresponding SSAS Tabular model. The database is a SQLite database. In the previous post I explained how to visualise SQLite data in Power BI. The requirements for this post are the same as the previous post, so I encourage you to check it out. Therefore, I just explain how to find the metadata file and how to build a model in Power BI Desktop. I also explain how to do the same in Excel for those of you who would like to add some annotations or comments to the outcomes.

Continue reading “SSAS Tabular Model Documenter with Power BI without DMVs” →

Power BI, Power BI Desktop

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” →

Pass Business Intelligence Virtual Chapter, Power BI, Power BI Desktop, Power BI Service, Presentations, Uncategorized

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

[gview file=”https://www.biinsight.com/wp-content/uploads/2016/10/Pass-DW-BI^LJ-Power-BI-Under-The-Hood-with-Soheil-Bakhshi-Presentation-2.pptx”]

Here is the PDF version of presentation:

[gview file=”https://www.biinsight.com/wp-content/uploads/2016/10/Pass-DW-BI^LJ-Power-BI-Under-The-Hood-with-Soheil-Bakhshi-Presentation.pdf”]