A #dataOp ramblings

Adventures in Business Intelligence

Google Spreadsheets Custom Connector

In the beggining I’ve been tasked to find a way of exporting data from Google Spreadsheets for a client. I’ve started researching for an already working solution and got to the following links: https://www.thebiccountant.com/2017/09/24/custom-connector-import-google-sheets-oauth2-powerbi/ https://www.skolenipowerbi.cz/l/google-drive-connector-kompletni-pruvodce/ https://github.com/fluf1024/PQGoogleSpreadsheet A lot of the work and heavy lifting has been done by them and the rest of this post will be about how to do it today, as some of their content is outdated and was not working when scheduling refreshes via the Power BI Enterprise Gateway.

Import data from another Power BI dataset

While I was working with a client they were trying to import data from another Power BI dataset to their file and they got this error: This got me thinking that when we try to connect to a running Analysis Services it asks us if we want to import ou connect live. We also know that Power BI is an Analysis Services. Could the server connection string helps us here?

Emacs Major Modes for Power BI

Emacs Major Modes for Power BI After many years of using Vim I finally understood the Emacs way. What I was trying to do all along was trying to make Vim behave exactly like Emacs. To celebrate this I made some bad Elisp code and shared it on GitHub. I based my code on the Simple Emacs Major Mode for Syntax Coloring by Xah Lee. Power Query Mode This mode gives you syntax coloring and code completion inside Emacs.

Diffing Power BI files

Do you really can diff Power BI files? While working with some customers a few times this question came up: “How can I diff my current file with a older one? I really want to compare my files, to know what changed from one to another." Many many people working with Power BI end up with a directory full of files looking like: “myPowerBIFileV1.pbix”, “myPowerbifilev2.pbix”… “myPowerbifilevN.pbix”. The scenario wull get worse if we have two or more people working in the same file, and until recently my answer was always the same: “You can’t”.

EzApi - Define flat file columns from connection string

So what’s new? Thanks to the contribution via GitHub from Hadi Fadl there’s a new method available for the flat file connection manager that adds the existing columns in the source file. This is very handy because in the past you would have to define each and every column that existed in the flat file. I’ve updated the nuget to version 0.8.93. Please give me your feedback of anything you might think should be improved.

EzApi - Adding Event Handlers

How to add event handlers to generated packages I had a request to give an example of adding event handlers to a generated SSIS package. The code is quite convoluted. There’s no method to initialize a DtsEventHandler, you need to cast it after adding to the package with a String describing the type of event. Here’s a list of the available events: Here’s a sample of how to do insert a ExecuteSQLTask to a event handler using a small console application:

Openssh Windows 10 Issues

Warning: Unprotected Private Key File As an avid user of OpenSSH on Windows after last update I’ve started seen this on my PowerShell: File System Security PowerShell Module Because file and folder permissions are not easy with PowerShell I’m using the File System Security PowerShell Module. You can install from the gallery using the command: Remember to use an elevated one :) Removing the Warning This warning happens when you’re not the only person that can see the file:

EzApi 0.8.92

New Release 0.9.82 My small contribution for the BI world has been this SSIS generation framework resurrection. Amazingly it has almost 1500 downloads @NuGet. We are using this @DevScope for creating our SSIS packages based on metadata and it’s on production on some of our clients. We can see the rest of the details at the project repository. The Future I finally managed to move to a new house and I will have (hopefully) more time to write on the blog and to continue to work on this side project.

Mass changing pivot table connection strings in Excel using PowerShell

The Problem As Business Intelligence consultants after a migration to a brand new server inevitably we get asked by the users: “Do why have to redo all my pivot tables in Excel again? All my workbooks stopped working…” Well, they can but they don’t need to. Where’s a simple code to replace the part that matters. This will iterate all the Excel files in the folder where the Powershell Script is located and and apply the substitution to all the OLEDBConnections that exist in each file:

Using Powershell to read and modify SSIS packages

Introduction In these days developing in SSIS, you should be using a generation framework (BIML or EzApi. The benefits era a bit obvious: Your packages will have consistent layout and logic It’s easy and fast to apply the same change to all packages No more mouse programming (almost :)) It’s the right way to do metadata driven ETL The problem In a client I had a project with 97 packages.