Tag: Power BI Desktop

BISM Tabular, Business Intelligence - BI, Power BI, Power BI Desktop, Power Query, SQL Server Analysis Services - SSAS

DAX Measure Dependencies in SSAS Tabular and Power BI

DAX measures are the heart of every SSAS Tabular model, Power BI and Power Pivot solution. You write lots of DAX measures and you potentially reference some of them in other measures. So the number of DAX measures you write and reference them via other measures grow very quickly. Especially in complex solutions you may have hundreds of DAX measures. While your solution works perfectly, to make a minor change or adding a new measure to the solution or fixing a problem in your existing measures can be such a pain in the neck. In this post I’m going to take a step further and show you a simple way to get the whole data model dependencies then visualise the dependencies in Power BI. You can find the download link at the end of this post.

A simple search in Google brings you a bunch of useful articles talking about the subject. Some of the bests, in my mind, are as below:

In this post I use a DMV that gives us everything we want. ( Chris Webb already discussed the DMV here: Document Dependencies Between DAX Calculations). Running the DMV we can see what measures are references by other measures, what columns are referenced in the calculated columns and much more.

This is a very useful DMV that helps us getting a better understanding of the model we’re working on. We can also use this method for documentation.

How It Works

This method is fairly simple, you just need to run the following DMV on top of your SSAS Tabular model or your Power BI Desktop file and Import the results in Power BI.

SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

For Power BI you’ll need to find the local port number then you’re good to go. The only part that might not look very straightforward at first, would be finding the database in Power BI Desktop model.

An easy way, after you find the local port number of an opened Power BI Desktop file, is to find the database name from SQL Server Management Studio (SSMS) when connecting to the Power BI Desktop model:

  • Open SSMS
  • Select “Analysis Services” as “Server Type”
  • Type in “localhost:PORT_NUMBER” as “Server Name” then click “Connect”

Connect to Power BI Desktop Model from SSMS

Continue reading “DAX Measure Dependencies in SSAS Tabular and Power BI” →

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” →
Business Intelligence - BI, On-premises Data Gateway Enterprise Mode, On-premises Data Gateway Personal Mode, Power BI, Power BI Desktop, Power BI Service, SQL Server

Export Power BI Service Data to SQL Server

image

 

Update 2021 March:

You can now export the data directly from Power BI Desktop using my tool, Power BI Exporter. Read more here.

Update 2019 April:

If you’re interested in exporting the data model from Power BI Service to SQL Server check this out.

A while ago I wrote a blog post explaining how to Export Power BI Data to SQL Server with R. In that post I explained how to get the job done in Power BI Desktop using R scripts. In this post I explain how to export Power BI Service data to SQL server. YES! You can export data from Power BI service to a SQL Server database sitting in your on-premises environment. Keep reading to see how.

How It Works?

This is going to be a short post as I already covered the first part of the process in my other post on Export Power BI Data to SQL Server with R. So in this post I show you how to use the Power BI Desktop file you already created using the method explained in that blog post to export your Power BI Service data to an on-premises instance of SQL Server. All you need to do is to

  • Publish the existing Power BI Desktop solution to Power BI Service
  • Install On-premises Data Gateway in PERSONAL MODE

Note: R is NOT supported by the current version (Version Number: 14.16.6614.5) of the On-premises Data Gateway in Enterprise Mode.

After you successfully published the model to Power BI Service you’ll notice that you cannot refresh the model if you don’t install the On-premises Data Gateway in Personal Mode.

To see the dataset settings:

Continue reading “Export Power BI Service Data to SQL Server” →
Business Intelligence - BI, Power BI, Power BI Desktop

Four Different Ways to Find Your Power BI Desktop Local Port Number

Power BI Desktop Local Port

Update December 2023:

From the October 2023 release of Power BI Desktop onwards, there is a 5’th method to find the port number within the Power BI Desktop itself. This method is explained here.

About two years ago I wrote a blog post on how to connect to a Power BI Desktop model from different tools like SSMS, Excel, and SQL Server Profiler. A big chunk of that blog post was about different ways of finding Power BI Desktop local port. Having the port number is crucial if you want to connect to a Power BI Desktop model from any sort of tool for different purposes like monitoring in SQL Server Profiler or analysing the model in Excel. I’ve been asked several times here and there about different reasons people need to find the Power BI Desktop local port number, so I decided to dedicate a post to the Power BI Desktop local port number.

When you run Power BI Desktop, it runs a local instance of SSAS Tabular model in the background and it assigns a random port number to that local instance. Therefore, if you want to connect to the model to analyse it in Excel you need to know that port number. There are several ways to achieve the port number and this post will cover most of them.

As you may know, Power BI Desktop is now available as a Windows App that can be installed from Windows Store. In general, the main difference between the App and the downloaded edition from the Microsoft website is that you will get a notification whenever a newer version is available to download. Then you have to manually download and install the newer version on your machine, but, the App will be updated automatically. However, some other differences are out of the scope of this article. There is just one difference that is relevant to this article which is that Power BI Desktop creates its temporary folders in a different location than the Power BI Desktop App does.

The methods described in this post work for both the downloaded edition and the Store edition.

Different Methods to Find Power BI Desktop Local Port

· Using DAX Studio

· Tabular Editor

· Power BI Desktop Temporary Location

· Command Line (CMD)

The reason that I explain different methods is that not all methods are doable in all environments. You may work at a customer site that doesn’t allow you to install new software like DAX Studio on their machines. But you always have access on Power BI Desktop temporary files.

Continue reading “Four Different Ways to Find Your Power BI Desktop Local Port Number” →