Never forget to water the plants again: building a DIY automatic plant watering / irrigation system for <$5

Ok, trying something new here - as you might know, I have been having fun with home automation for a while now. Home Assistant is my platform of choice. I will start writing down the more interesting things I did to make my house "smarter" and publish them on this blog. This is the first of many (?) - we will see how it goes.

The problem

One thing we never seem to get right is when to water plants. We either overfeed them or forget them. In either case, they die quickly.

As part of my many home automation endeavors I wanted to build an automatic plant watering system.

The basic idea was: a water pump in a glass of water, a moisture sensor and whenever the soil the moisture sensor is measuring is dry, the water pump provides water until the soil is wet.

I found this kit of Aliexpress:  https://www.aliexpress.com/item/4001096063867.html?spm=a2g0s.9042311.0.0.66e74c4dDfCE0X, which has almost everything you need. Almost.

I say almost, because when you assemble this kit it does the opposite from what you need. Basically, when the sensor reports the soil is wet, the motor turns on and when the soil is dry, the motor turns off. Update: after posting this I figured out you could also solve this issue by connecting to the NC instead of the NO of the relay.

Also, it is not very clear how you need to connect this kit to make it work.

See the original kit in action below:

Putting it together

Here is what I did:

  • First, I used the USB cable that came with the kit to provide power (you can use another way of getting 5V of power)
  • Then, I added a 2N700 transistor to build a simple inverter (see http://www.learningaboutelectronics.com/Articles/Transistor-inverter-circuit.php). It takes the input from the moisture sensor and provides it to the relay, effectively reversing the effect: when the soil is dry, turn on the pump, when the soil is wet, turn off the pump. You can use the small potentiometer on the moisture sensor breakout board to adjust sensitivity to moisture. You need a 1M Ohm resistor as well.

This is the circuit on a breadboard:

These are the connections:

  • Moisture sensor: connect to two pin side of breakout board. Does not matter which way.
  • Moisture sensor breakout board:
    • VCC --> +5V
    • GND --> GND
    • D0 (digital output) --> input of 2N700 (middle)
  • 2N700 inverter:
    • Left --> GND
    • Middle --> D0 (digital output) of moisture sensor breakout board
    • Right --> 1M resistor --> +5V and to Input of relay
  • Relay:
    • VCC --> +5V
    • GND --> GND
    • IN --> Right output of 2N700
    • COM --> +5V
    • NO --> Red wire of water pump
  • Water pump:
    • Black --> GND
    • Red --> NO of Relay.

This is the final setup (before making it look pretty with a case and longer wires):

Power BI Pro Tip: show 0 instead of (Blank)

This one is easy once you know how to do it. It is also buried in the documentation. Kasper de Jonge mentioned it to me on Twitter and I thought it would not hurt to just write it down quickly (also to make it easier for myself to find it in the future):

Default behavior of Power BI is to show (Blank) when there is nothing, even when summing a numerical column that happens to be all empty. But what if you want to show 0 instead? Well, with COALESCE you can.

In this simple example I have a table showing sales per region for a company that is not doing so good - they are not selling anything.

Sales are null, maybe it's the Regions?

Assuming SalesAmount is a numerical column, if you make a visual with this you get:

Technically correct return value

Business users might just want to see 0 in this case, not the (more correct) (Blank). To make this happen, add a simple measure:

TotalSales = COALESCE(SUM('Sales'[SalesAmount]),0)

Creating a visual with that measure shows:

What your business user wants to see

The way this works is that the COALESCE function will just return the first thing it finds reading from left to write in the parameter list that is not null/blank. That happens to be 0. If for whatever reason you wanted to show -1 when there are no sales, you can do that as well, just change the 0 to -1.

Enjoy and don't forget to feel good about making your business users happy :)

Azure SQL firewall settings for Power BI refresh

I do not claim that what is included in this post is the perfect way of doing things. However, some things related to Azure SQL firewall settings for Power BI data refresh caught me by surprise recently so I figured I would just write it down...

The scenario

You have built a Power BI dashboard that takes data from Azure SQL and want to have this report automatically refreshed in the Power BI Service.

Data refresh scenarios in Power BI

There are two ways of refreshing data in Power BI: the classic Power BI refresh and the refresh using a Dataflow. I will discuss the options separately because they have separate impact on the firewall settings on your Azure SQL database.

'Classic' Power BI Refresh

What I mean with 'classic' Power BI Refresh is the refresh that you can configure without using a Dataflow. You will find it on your dataset settings:

Now for this to work you will need to enter the credentials and allow Power BI to reach the Azure SQL database through the firewall. This is where the fun starts. If you have a new Azure SQL database you will not have touched the firewall at all or allowed your office IP through to be able to access it from Power BI Desktop and build a report.

If you enter your credentials on the Dataset settings page in Power BI and try to refresh, you will get an error because Power BI is not allowed through the firewall. In order to allow this there are a couple of options:

  1. Allow Azure services and resources access to the Azure SQL Server. You will find this button in the Azure Portal on the Azure SQL Server firewall configuration. Set it to 'Yes' and your refresh works. This is what is recommended in the official documentation. Sometimes, however, this is not an option since it opens the Azure SQL Database for more than just Power BI - it would allow any Azure Service to communicate to the database after authentication. For example, Azure Data Factory could now be used against this Azure SQL Database. This might be just what you want, or not, it depends on the security rules in your organization. It is the easiest and my preferred solution.
  1. Alternatively, you could use VNets and the Power BI Gateway (in a VM) to pull this off without opening up the firewall to all Azure Services, as it explained here and here. This is more restrictive but has the downside of a more complex and less cost-effective solution since you have to set up a VNet and a Power BI Gateway in a VM.
  2. Lastly you could figure out the IP addresses used by Power BI and allow those to go through the firewall. Downside is that those IP addresses can change without notice and you would have go in and manually make changes for the refresh to work. I would not recommend doing it, but if you want to try here is a script.

Dataflows

If you use Dataflows you can still use the options described above. However, there is a third and more hacky way of doing things: a script or Azure Automation Runbook.

Azure publishes the IP address ranges used for some Azure Services here. Unfortunately, Power BI 'classic' refresh uses IP ranges that are not documented here. Note that it is published per cloud - so if you are not in the public cloud you will need to download a different file. With this file you can do a manual update of the firewall (similar to #3 above) or try to do it automatically like I did below. If the IPs change you can use this file to retrieve the changes and make updates accordingly.

For the automatic solution you could write a script or use Azure Automation. I wrote a very hacky script which downloads the file mentioned above for public cloud, parses it and adds the rules for Data Flows to the Azure SQL firewall. Again, it is very hacky, but it works. You can either run this script manually, schedule it or plug this in Azure Automation to run it automatically. You can get the script here or read it below. Remember to use this at your own risk. I am not responsible for you not being able to access your Azure SQL database anymore because of this script.

#DutchDataDude.com
#Code is provided as is.
Install-Module Az.Network -Scope CurrentUser

#CONFIG
$resourcegroup = "[ResourceGroupForAzureSQLDatabase]"
$server= "[AzureSQLServerName]"
$subscriptionId = "[SubscriptionID]"
$location = "[LocationForResourceGroupAndAzureSQLServer]"

#this is the URL for public clouds, for other clouds use appropriate URL
$url = "https://download.microsoft.com/download/7/1/D/71D86715-5596-4529-9B13-DA13A5DE5B63/ServiceTags_Public_20200420.json"

#source: https://gallery.technet.microsoft.com/scriptcenter/Start-and-End-IP-addresses-bcccc3a9
function Get-IPrangeStartEnd 
{ 
    <#  
      .SYNOPSIS   
        Get the IP addresses in a range  
      .EXAMPLE  
       Get-IPrangeStartEnd -start 192.168.8.2 -end 192.168.8.20  
      .EXAMPLE  
       Get-IPrangeStartEnd -ip 192.168.8.2 -mask 255.255.255.0  
      .EXAMPLE  
       Get-IPrangeStartEnd -ip 192.168.8.3 -cidr 24  
    #>  
      
    param (  
      [string]$start,  
      [string]$end,  
      [string]$ip,  
      [string]$mask,  
      [int]$cidr  
    )  
      
    function IP-toINT64 () {  
      param ($ip)  
      
      $octets = $ip.split(".")  
      return [int64]([int64]$octets[0]*16777216 +[int64]$octets[1]*65536 +[int64]$octets[2]*256 +[int64]$octets[3])  
    }  
      
    function INT64-toIP() {  
      param ([int64]$int)  
 
      return (([math]::truncate($int/16777216)).tostring()+"."+([math]::truncate(($int%16777216)/65536)).tostring()+"."+([math]::truncate(($int%65536)/256)).tostring()+"."+([math]::truncate($int%256)).tostring() ) 
    }  
      
    if ($ip) {$ipaddr = [Net.IPAddress]::Parse($ip)}  
    if ($cidr) {$maskaddr = [Net.IPAddress]::Parse((INT64-toIP -int ([convert]::ToInt64(("1"*$cidr+"0"*(32-$cidr)),2)))) }  
    if ($mask) {$maskaddr = [Net.IPAddress]::Parse($mask)}  
    if ($ip) {$networkaddr = new-object net.ipaddress ($maskaddr.address -band $ipaddr.address)}  
    if ($ip) {$broadcastaddr = new-object net.ipaddress (([system.net.ipaddress]::parse("255.255.255.255").address -bxor $maskaddr.address -bor $networkaddr.address))}  
      
    if ($ip) {  
      $startaddr = IP-toINT64 -ip $networkaddr.ipaddresstostring  
      $endaddr = IP-toINT64 -ip $broadcastaddr.ipaddresstostring  
    } else {  
      $startaddr = IP-toINT64 -ip $start  
      $endaddr = IP-toINT64 -ip $end  
    }  
      
     $temp=""|Select start,end 
     $temp.start=INT64-toIP -int $startaddr 
     $temp.end=INT64-toIP -int $endaddr 
     return $temp 
}


# Sign-in with Azure account credentials
Connect-AzAccount
$context = Set-AzContext -SubscriptionId $subscriptionId
$output = "$PSScriptRoot\servicetags.json"

#download the service tags and convert to Json
Invoke-WebRequest -Uri $url -OutFile $output

$j = Get-Content $output | ConvertFrom-Json

#remove all firewall rules for PowerQueryOnline
$existingrules = Get-AzSqlServerFirewallRule -ResourceGroupName $resourcegroup -ServerName $server
foreach ($rule in $existingrules) {
    if ($rule.FirewallRuleName -match "PowerQuery") {
        write-host "Removing rule " $rule.FirewallRuleName
        Remove-AzSqlServerFirewallRule -ResourceGroupName $resourcegroup -ServerName $server -FirewallRuleName $rule.FirewallRuleName -DefaultProfile $context
    }
}

$v = $j.values
foreach ($val in $v) {
    if ($val.name -match "PowerQuery") {
        $addresses = $val.properties.addressPrefixes;
        foreach($address in $addresses) {
            $split = $address.Split('/')
            $startend = Get-IPRangeStartEnd -ip $split[0] -cidr $split[1]
            $end = $startend.end
            $start = $startend.start
            $rulename = $val.name+":"+$start+"-"+$end

            #add rules
            write-Host "Adding " $rulename
            New-AzSqlServerFirewallRule -ResourceGroupName $resourcegroup -ServerName $server -FirewallRuleName $rulename -StartIpAddress $start -EndIpAddress $end -DefaultProfile $context
        }
    }
}

I hope this helps. Let me know if I missed anything!

Fixing ‘subscription is not registered with NRP’ error in Azure

I am writing this post not to claim that I know everything there is to know about Azure Networking. Not even remotely. I am writing this partly for myself and hopefully to help others. Today I hit the following error while trying to write a PowerShell script to automate some Azure Networking related tasks (more on that soon!): 'Subscription [subscriptionID] is not registered with NRP'. In this short post I will explain how to fix this error.

I searched online but could not find the solution anywhere. Then it hit me - NRP = Network(ing?) Resource Provider. The solution is straightforward:

  1. Make a note of the subscriptionID from the error message.
  2. In the Azure Portal go to Subscriptions and find the offending subscription.
  3. Click on Resource Providers.
  4. Search for 'Microsoft.Network'.
  5. You will see that the status for that provider is 'NotRegistered'. Select 'Microsoft.Network' and click 'Register'. Wait for it to say 'Registered' (if you are as inpatient as I am you can click 'Refresh' to get the latest status).
  6. Note that you do not need to register for 'Microsoft.ClassicNetwork'.

Done, now execute your script again and the error should disappear!

Working with aggregations in Power BI Desktop

One of the first things I did after joining the Power BI Desktop team is catching up to reality - the speed of innovation in this team is amazing and I had a lot of reading to do. One of the first things on my list were aggregations.

Aggregations bring me back to the good old SSAS Multidimensional days. The days that I invariably built the aggregations tree the wrong way around, SSAS would complain, I would scream and eventually give in.

You can imagine that I was curious but skeptical when I tried aggregations in Power BI Desktop. I was afraid of ending up in the same hate-but-need relationship that I had with SSAS multidimensional when it came to aggregations.

The good news? It is not like that. At all. Once you have aggregations working, they are great.

The not so good news? It took me longer than I am willing to admit getting them working - primarily due to data types and creation of the aggregated table. More details in this blog post.

Why care about aggregations?

I see aggregations as pre-calculations - you identify datasets that you would need to return often and by pre-calculating the results and storing them performance is improved, resulting in a better end-user experience. The difference is dramatic, especially when dealing with big data. If the detail table you are aggregating over contains billions of rows and your aggregation table just mere millions, you and your users will benefit greatly. You leave your source in DirectQuery mode so you are not importing the huge table to Power BI and create the aggregation table in Import mode so it is included in the Power BI data model.

Setting up aggregations

The documentation explains about everything there is to know about aggregations in Power BI Desktop. I suggest you read it before reading on. The devil, however, is in the detail, specifically with data types and creating the aggregation table itself. The documentation expects you to know how to create the aggregation table. I will explain how I did it later in this blog post.

Data types

First, the biggest problem I faced when working with aggregations: data types. Check your data types and check them again. Whenever you cannot select your detail column in the 'Manage Aggregations' screen you probably have a data type mismatch between the Aggregation Column and the Detail Column. I had, every time I ran into trouble here.

Aggregations in Microsoft Power BI Desktop

Creating the aggregation table

Creating the aggregation table can either be done by writing a query against the source doing a group by or something equivalent or doing that in PowerQuery. I chose the last option and it was simple if you remember to include all the metrics you want included in your aggregations (duh) as well as any dimension column! It is not enough to just include the key to your date table if you want the aggregation to work on Month or Year level. You will have to include those levels as well (just like in SSAS multidimensional).

Using the AdventureWorks sample DWH I created an aggregation table that summarizes SalesAmount from FactInternetSales by product category, product subcategory, productname, month, quarter, and year and includes a row count.

The quick-and-dirty PowerQuery query for this is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
  
let
Source = Sql.Databases(myserver),
AdventureWorksDW2017 = Source{[Name="AdventureWorksDW2017"]}[Data],
dbo_FactInternetSales = AdventureWorksDW2017{[Schema="dbo",Item="FactInternetSales"]}[Data],
#"Expanded DimDate(OrderDateKey)" = Table.ExpandRecordColumn(dbo_FactInternetSales, "DimDate(OrderDateKey)", {"EnglishMonthName", "CalendarQuarter", "CalendarYear"}, {"DimDate(OrderDateKey).EnglishMonthName", "DimDate(OrderDateKey).CalendarQuarter", "DimDate(OrderDateKey).CalendarYear"}),
#"Expanded DimProduct" = Table.ExpandRecordColumn(#"Expanded DimDate(OrderDateKey)", "DimProduct", {"EnglishProductName", "DimProductSubcategory"}, {"DimProduct.EnglishProductName", "DimProduct.DimProductSubcategory"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded DimProduct",{{"DimProduct.EnglishProductName", "ProductName"}}),
#"Expanded DimProduct.DimProductSubcategory" = Table.ExpandRecordColumn(#"Renamed Columns", "DimProduct.DimProductSubcategory", {"EnglishProductSubcategoryName", "DimProductCategory"}, {"DimProduct.DimProductSubcategory.EnglishProductSubcategoryName", "DimProduct.DimProductSubcategory.DimProductCategory"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded DimProduct.DimProductSubcategory",{{"DimProduct.DimProductSubcategory.EnglishProductSubcategoryName", "Subcategory"}}),
#"Expanded DimProduct.DimProductSubcategory.DimProductCategory" = Table.ExpandRecordColumn(#"Renamed Columns1", "DimProduct.DimProductSubcategory.DimProductCategory", {"EnglishProductCategoryName"}, {"EnglishProductCategoryName"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded DimProduct.DimProductSubcategory.DimProductCategory",{{"EnglishProductCategoryName", "Category"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns2", {"DimDate(OrderDateKey).CalendarYear", "ProductName", "Subcategory", "Category", "DimDate(OrderDateKey).CalendarQuarter", "DimDate(OrderDateKey).EnglishMonthName"}, {{"FactInternetSalesCount", each Table.RowCount(_), type number}, {"SalesAmount.1", each List.Sum([SalesAmount]), type number}}),
#"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"FactInternetSalesCount", Int64.Type}, {"SalesAmount.1", type number}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type",{{"SalesAmount.1", "SalesAmount"}, {"DimDate(OrderDateKey).CalendarQuarter", "Quarter"}, {"DimDate(OrderDateKey).EnglishMonthName", "Month"}, {"DimDate(OrderDateKey).CalendarYear", "Year"}})
in
#"Renamed Columns3"
  

Creating the aggregations

Once you have your aggregation table created, right click on it and choose Manage aggregations. You can also click the table you are aggregating (FactInternetSales in my example) but then you will have to manually select the Aggregation table.

This is how I set it up:

Aggregation columnSummarizationDetail tableDetail column
CategoryGroupByDimProductCategoryEnglishProductCategoryName
FactInternetSalesCountCount table rowsFactInternetSalesN/A
MonthGroupByDimDateEnglishMonthName
ProductNameGroupByDimProductEnglishProductName
QuarterGroupByDimDateCalendarQuarter
SalesAmountSumFactInternetSalesSalesAmount
SubcategoryGroupByDimProductSubcategoryEnglishProductSubcategoryName
YearGroupByDimDateCalendarYear

The biggest problem I faced when working with aggregations: data types. Check your data types and check them again.

Testing your aggregations

To check your aggregation table makes sense you can create two measures as follows:

FactInternetSalesAggRowCount = COUNTROWS(FactInternetSalesAgg)
FactInternetSalesRowCount = COUNTROWS(FactInternetSales)

In my sample I got from 60k rows in FactInternetSales (detail table) to 2k in FactInternetSalesAgg (aggregated table). Of course, I know 60k rows is not enough to warrant the use of aggregations in the first place.

To test if the aggregations work as expected, you can connect SQL Profiler to the diagnostics port of Power BI Desktop - to get the port, run the following command in an elevated prompt and look for the port used by msmdsrv.exe:

netstat -b -n

In SQL profiler, connect to Analysis Services and use localhost:[port recorded earlier] to start the trace. You should be tracing these events:

  • Queries Events\Query Begin
  • Query Processing\Vertipaq SE Query Begin
  • Query Processing\DirectQuery Begin
  • Query Processing\Aggregate Table Rewrite Query

Whenever your aggregations are used you will see the Vertipaq SE Query Begin event and whenever your aggregations are missed you will see DirectQuery Begin.

I made a column chart that includes SalesAmount for ProductCategory per CalendarYear, CalendarQuarter, EnglishMonthName and DateKey. Using the drill-down feature and keeping a close eye on the SQL Profiler I was able to verify that the aggregation worked for all of the levels in the drilldown except the DateKey, as it was not included in the aggregation table. See the images below. Rest assured, even if you did not include the granularity of the data in your aggregation table, you will get a result since Power BI will 'just' query the original data source.

Query hitting the aggregation table

See below for a graph and trace that hit the aggregation table. Notice the VertiPaq SE Query Begin event.

Query not using the aggregation table

See below for a graph and trace that does not use the aggregation table. Notice the DirectQuery Begin event.

It turns out the aggregations are working just fine. I hope this was helpful, let me know if you have any questions and ideas!