Diffing Analysis Service Tabular models

One of the problems we often face when diffing Analysis Services Tabular models in a collaborative development environment is that tables, columns, and relationships can often be reordered. This is because Visual Studio caches data to disk, and when the Model.bim is reloaded, the order from disk is used, and any extra items are appended to the end of the item array.

For example, if Alice loads Bob’s changes in Visual Studio, Alice’s cached data is used to determine the order, and then Bob’s changes are appended to the end of the array. So if Alice adds a single column, the diff will appear to contain all of Bob’s changes (reordering) as well as Alice’s changes.

To help with providing more accurate diffs, we can sort the item arrays deterministically (i.e. by name). When used as a git post-commit hook, this will take care of any diffs done in code review.

I created a tiny Node.js script to perform the reordering: Tabular Normalizer.

Data model impact analysis with Power BI

Our current set up with Power BI is to host Analysis Services databases, and point a Power BI report to that. Due to the de-coupling of the data model and the report, there is no current mechanism to identify what fields and tables in Analysis Services are being used by which Power BI reports.

I’ve written a PowerShell script that will help in understanding this relationship: https://github.com/avinmathew/extract-powerbi-fields

Point the script to a directory containing Power BI .pbix or .pbit files, and the script will iterate through each of the reports, open up the Layout file, and extract all used fields to a CSV file with three columns: Field, Table, File.

Due to the complexities of Layout structure with filters on visuals, I’ve taken a shortcut and used regex rather than JSON navigation to mop up any missing properties not found the the first two passes. This will result in the Table column in the CSV file being blank in these cases.

Using Leaflet in a Power BI Custom Visual

For some of the spatial visualisations I’ve been working with in Power BI, I’ve had to create Custom Visuals as the out-of-the-box and visuals in the AppSource don’t quite hit the mark. I’m quite fond of Leaflet for map rendering. Here’s how I got it working with a Power BI Custom Visual.

Create a new Custom Visual via the Command Line:

pbiviz new LeafletCustomVisual

In the newly created directory, install Leaflet via npm:

npm install --save leaflet

You need the geojson as the typings for Leaflet (in the next step), depends on the package being available.

Install typings for Leaflet:

npm install --save-dev @types/leaflet

As Power BI Custom Visuals don’t currently support modules, you will need to grab the Leaflet object from the window. Create an inject.js file with the contents:

var L = window.L;

Add the Leaflet package and inject.js file to pbiviz.json in the externalJS key:

"externalJS": [

Import the Leaflet stylesheet to style/visual.less :

@import (less) "node_modules/leaflet/dist/leaflet.css";

In src/visual.ts, change the constructor to:

private map: L.Map;

constructor(options: VisualConstructorOptions) {
console.log('Visual constructor', options);
this.target = options.element;
// Fill the target element with the Leaflet map
this.target.style.width = "100%";
this.target.style.height = "100%";

if (typeof document !== "undefined") {
this.map = L.map(this.target).setView([0, 0], 2);

You can find an example project on GitHub.

SecurePay SecureFrame example

I recently had to debug an application that uses SecurePay as an online payment system. Rather than using API integration, the application used SecureFrame, where SecurePay provides the payment page (e.g. via an iframe). I couldn’t find an example in the SecureFrame documentation, so here’s a minimal working example:

<!DOCTYPE html>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.2.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/crypto-js/3.1.2/components/core-min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/crypto-js/3.1.2/components/sha1-min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.11.2/moment.min.js"></script>
$(function() {
  var merchantId = "ABC0001";
  var password = "abc123";
  var txnType = "0";
  var primaryRef = "Test Reference";
  var amount = "100";
  var timestamp = moment.utc().format("YYYYMMDDHHMMSS");
  var fingerprint = CryptoJS.enc.Hex.stringify(
    CryptoJS.SHA1([merchantId, password, txnType, primaryRef, amount, timestamp].join("|"))
<form action="https://payment.securepay.com.au/test/v2/invoice" method="post">
  <input type="hidden" name="bill_name" value="transact">
  <input type="hidden" name="merchant_id">
  <input type="hidden" name="txn_type">
  <input type="hidden" name="primary_ref">
  <input type="hidden" name="amount">
  <input type="hidden" name="fp_timestamp">
  <input type="hidden" name="fingerprint">

Financial Comparison of Air Conditioners

I’ve been looking to get ducted AC installed at my residence and have had a few quotes come in. As the AC units, zoning and controls came with a similar set of features, I investigate the decision from an objective financial standpoint by ranking the offerings by comparing both the capital and operating costs. The rank* is calculated by:


capital_cost is the total installation price in dollars
number_of_years is the number of years the AC will be in operation
cost_per_unit_capacity is the electricity price (e.g. cents per kWh)
max_cooling_usage is the number of hours per year the AC will run on max cooling capacity
min_cooling_usage is the number of hours per year the AC will run on min cooling capacity
max_heating_usage is the number of hours per year the AC will run on max heating capacity
min_heating_usage is the number of hours per year the AC will run on min heating capacity
cooling_input_power is the rated input power in kW for cooling
heating_input_power is the rated input power in kW for heating
min_cooling_capacity is the minimum cooling capacity in kW
min_cooling_capacity is the minimum heating capacity in kW
min_eer is the min EER (Energy Efficiency Ratio) out of all the ACs being compared
current_eer is the EER of this particular AC
min_cop is the min COP (Coefficient Of Performance) out of all the ACs being compared
current_cop is the COP of this particular AC

I use the term rank rather than Total Cost of Ownership since I don’t bother discounting future cashflows nor take into account maintenance (since I couldn’t get a hold of spare parts price lists).

Most spec sheets also don’t provide an input power at min capacity, so the calculation tries to make an estimate based on the EER/COP, input power and min capacity. For those that do provide an input power at min capacity, the 2nd and 4th components within the parentheses can be made similar to the 1st and 4th components.

Ironically, I ended up choosing the AC ranked second, because of a qualitative aspect – the brand value that would have a bearing on price and availability of spare parts required outside the warranty period.