Power BI – Accessing SSRS Datasets using a Web Connector

November 10, 2019

Let’s say you have spend a lot of time to build SSRS reports, and you want to re-use these data sets in Power BI.

Well this is possible even if you are running the older version of SSRS.

Even if this report is running in Native or Integrated SharePoint Mode, or is a Parametrized report

The SSRS report server has natively a web service interface, exposing a SOAP and URL Interface.


Open Power BI and GET DATA  from the Web Connector


Fill in your report URL link and add the rs:Format=XML parameter to get an XML output from you report



If all goes well you will get the Report XML output and see the SSRS TABLIX and FIELDS Smile


Enjoy !!

PowerShell Core – Power BI Gateway Management CmdLets

November 6, 2019

Keep in mind that these cmdlets are PowerShell CORE only !

First you need to check the availability of the PS CORE version on your system.

I had  6.1 preview 3 installed


So I opened the Cmd line using Admin privileges

Next started PWSH.exe

Next I run this command :

Install-Module -Name DataGateway


Next Check the available cmdlets

Get-Command -Module DataGateway*


Next I ran this command

Import-Module DataGateway


But it says my version of PS Core needs to be minimum 6.2.2 Sad smile

So first get an upgrade from here :



After upgrading your can check the version using



Next run these commands again :

import-module DataGateway
import-module DataGateway.profile


No errors now Smile

Run this command to login :


It opens the browser and return this message



Next run this command to get your Cluster ID


Next check the Gateway Status like this



So far so good Smile 

Enjoy !!

QlikView – How to Export the Full Data Model to Excel

February 22, 2019

Once you have a QlikView application built. Or someone else did it for you.

When you have a complex data model with a lot of related tables.


You would like to document it, and export all Tables / Fields + Data to Excel.

This is a script you will find on the QlikView help


Binary "c:\Application.qvw" ;

//Iterate through the loaded tables
For t = 0 to NoOfTables() - 1

//Iterate through the fields of table
 For f = 1 to NoOfFields(TableName($(t)))
   TableName($(t)) as Table,
   TableNumber(TableName($(t))) as TableNo,
   NoOfRows(TableName($(t))) as TableRows,
   FieldName($(f),TableName($(t))) as Field, 
   FieldNumber(FieldName($(f),TableName($(t))),TableName($(t))) as FieldNo
   Autogenerate 1;
   let vTable = TableName($(t)));
    Store $(vTable) into d:\Temp\$(vTable).csv](txt);
 Next f
Next t;

But the downside is that is will export ALL FIELDS and ALL DATA.

But some table will hold millions of records, so this is a bit over the top.



Solution :

Use this script to Export MAX X number of records

Binary "C:\Your Application.qvw" ;

// Export number of Rows
let vRows = 1000;

//Iterate through the fields of table
let vtables = NoOfTables();

for i=0 to $(vtables)-1

let vTable = TableName($(i));

// STORE $(vTable) into C:\Temp\Export\$(vTable).csv (txt,delimiter is '|');


LOAD *, RowNo()

RESIDENT $(vTable) where RowNo() < $(vRows) ;

	STORE ReducedTable into d:\Temp\Export\$(vTable).csv (txt,delimiter is '|');

DROP Table ReducedTable;

Sleep 500;


So you end up with the full data model tables and samples data in Excel


Enjoy !

PowerShell – Accessing Power BI Desktop Data Cube and more…

November 18, 2018

Recently I got interested in getting data out of a Power BI Data Model to push into an SQL database.

On my way I discovered a lot of interesting techniques and Tools for Power BI Desktop.

1. First of all you need to know that Power BI Desktop actually is a small Web Server running an SSAS Mini Cube.

2. Next you need to prepare PowerShell to access the PBI Cube.

Solution :

In order to start you first need to start PBI Desktop and open a PBIX file you created before.

Once started you need to find out on which Random Port is was started.

There are 4 way to get the job done.

1. Install Dax Studio which you need to have, no question about this !

    Even if you are not using it for getting the Random Port…

Look in the bottom right corner :


2. Command line tool NETSTAT

   Run NETSTAT –b command and look for Power BI


In this case it was Port 60520

3. Look for the msmdsrv.port.txt file.

Once PBI is started it will create some folders and files.

You can find those here :

%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\


If you have multiple PBIX files open you will see more Random created subfolders.

Go to the random created folder and next to the Data Subfolder.

Open the msmdsrv.port.txt file to get the Port number.


4. Using the Tabular Editor

But it needs some dependent MS AS AMO Libraries



Now that you know how to get access to the Web Server Port.

PowerShell 32 Bit

You can now test PowerShell connection to the Power BI Desktop you have open.

But you first need a Connection String to be used in PowerShell

FIRST USE the 32 Bit PowerShell if you are using a 32 Bit Office installation.

Later in this post I will explain how to get it running in a 64Bit PS even if you have a 32 Bit Office installed.

I suggest that you use EXCEL for this to copy and paste it Winking smile

Go to Data and choose From Analysis Services.


Follow these steps :





Copy it from here :


You can also get the Catalog GUID from the folder name where you could find the Port number.


Run this PowerShell Script, it uses a DMV Query to get hold of the PBI Cube Properties


$Port = "60520"
$Catalog = "cff5b9a8-2f87-416c-97fd-8eccd5962d51"

$Query = 'Select * from $SYSTEM.DISCOVER_PROPERTIES'

$connectionString = "Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhost:$Port;Initial Catalog=$Catalog"
$connection = New-Object -TypeName System.Data.OleDb.OleDbConnection

$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $query
$adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object -TypeName System.Data.DataSet

$dataset.Tables[0] | Format-Table # export-csv $filename -notypeinformation


To list data from a Table use the EVALUATE command

$Query = 'EVALUATE Tickets'


PowerShell 64 Bit

If you are running a 32 Bit Office, you need to install the OLAP Drivers for 64 bit

(will be done when you install DAX Studio).


Or manually register the MSOLAP.DLL from the Power BI install folder.


Tools :

Power Pivot :

You can connect to a Power BI Desktop model using Power Pivot for example.



Dax Studio :

You can run commands directly against your Power BI Desktop.

Use DAX Commands


Or use a DMV Query to select the data from the PBI Meta Data or the Data Set.

// Select data using DMV queries


// Or select all DATA from the PBI Data Set using this command



For more info on DMV Queries see here

Power BI Helper :


More Tools you can find here

Enjoy !

PowerBI – Using FreshDesk Web Services API

November 7, 2018

We started exploring the use of the FreshDesk cloud-based customer support platform.

Which is one of the modules of the FreshWorks Suite.

You can start off with a free subscription, which is good enough to start building you BI tool.


Once you are happy you can switch over to a paid subscription giving you extra features.


One of them is extensive reporting,

After exploring the Power BI integration. I found out that all of the third party solution provided did not work Sad smile

And one if the solutions required that you have a Power BI Pro licence…


So I ended up making my own Power BI Dashboard using the FreshDesk REST Web Services API.

See here for the API documentation : https://developers.freshdesk.com/api/

The Web Service API looks very straight forward and works with any REST compatible application.

Implementing this in Power BI, was challenging.

But as you can see here, you can do all you need in your Free Power BI Desktop too.

So this will save you some licenses money too Winking smile

You can start off with a free subscription, which is good enough to start building your BI Dashboards.



Shows that Power BI is the right marriage for the 2 platforms, O365 and FreshDesk Winking smile

In case you need to run this as offline data source or want to schedule reports.

You can use PowerShell to extract the data, and use this as a data source.

This will save you quite some money uplifting your subscription.


Example :

If you have 25 Agents and you need to schedule reports. Your subscription is $19 / user / month more expansive.

This is a burden of an additional $5,000 subscription cost.

Other paid service provider is FreshInsights / Radiare



If you run the Invoke-WebRequest command against your URI in Powershell.

Like this :

$Data = Invoke-WebRequest -uri $URL -Headers $Headers -Method GET -ContentType application/json 


You can see in the response header information the API Version and the remaining calls you have

In this case 2996 out of 3000 per hour !

This is a limit set by Freshdesk to reduce the maximum API calls per hour.

Steps to get started using Power BI using the Web Service API :

1. create a free trial subscription

2. Copy your Web Service API Key


3. Open Power BI and do a Get DATA Web Data



Paste in the web service URL for Tickets for example.

4. Connect to FreshDesk using the API Key and password


PS : To use the Power BI Service and Scheduled REFRESH,

        you need to use the Anonymous Authentication !

5. Next built your Queries and transformations to extract the data


6. Build your Visuals and Measures


Tips and Tricks

An EXTRA Bonus is that you can incorporate this in your SharePoint Online site.

No license needed see here on how to.


Schedule a Refresh using Power BI Service

Recently Microsoft changed the Power BI service policy.

Where you can’t schedule a refresh when using a public web service ?


But no worries there is a solution for this too Winking smile


Need to get started building your own interactive Dashboard, drop a message here.

SharePoint–Online Publish a Power BI report (Personal Ed.)

January 5, 2018

Ever wanted to publish a Power BI report to SharePoint Online ?

Which is not out of the box functionality (It is for the PRO version of Power BI)


Solution :

Step 1. Get the link of the report

In your Power BI environment go to the report to publish –> File –> Publish to Web


Next copy the link code ….


Step 2. Create a Web Part Page


Add a Page Viewer Web Part


And add the Report link from Step 1.


Step 3. Save the page and see the result




Of course this does not give you all the full features of the Power BI Pro version.

But can be good enough to do some prototyping or publishing BI information.


Bonus is the fact that it will update automatically based on the Power BI Schedule frequency Smile


PS : Keep in Mind that your Tenant Admin needs to give you permission to Publish to the Web





SharePoint–Online Publish a Power Pivot Excel file using a SalesForce Datasource

January 5, 2018

I was wondering if it was possible to use the (older) Power Pivot that had a SalesForce Datasource.

And publish it to SharePoint Online ?

Step 1. Connect to SalesForce using Power Query

Once the data source is filtered, formatted, …


Safe the Excel file… it stores automatically the data as a Power Pivot Data source.

Step 2. Create a Report Library

In your O365 SharePoint Online create a Report LibrarySs


Step 3. Save the file to the Library



Step 4. Next Create a Web Part Page


And add a Business Data WebPart


Point to the Excel in the Report Library.


Enjoy !