Power BI – Export Data Set to CSV file overcome +150000 record limit

January 3, 2020

Let’s say you have a Data Flow to Consolidate multiple Data Sources

image

And next created a PBI Desktop Data set based on these Data Flows.

image

Transformed it using a PBI Power Query and Published it in the PBI Service

image

And put in place a schedule to refresh the data periodically.

To access the data is either using PBI Desktop or use it in Excel (Pivot table)

Downside is that the export data option in PBI Desktop controls are limited to 150000 records ?

image

Or Analyze in Excel, you will have to download and .ODC file and open it…

But it will not download the complete data set in a tabular layout Sad smile

Alternatively you can use Dax Studio.

But the downside is that you need to have PBI Desktop open and Refresh the Data Set each time.

image

You can’t schedule a job to download the dataset or a subset using a DAX query.

This is no good solution … Sad smile

Solution :

I created my own solution … Smile

Which is an application to download all the data directly from the PBI Data Set without opening the PBI Desktop Client !

It is Not depending on the PBI Desktop

Including DAX Query options to filter the data.

And does NOT get cut off by the 150000 record limits.

Reuses the same ODC connection.

image

When first time run it will ask for authentication if it is not run on a prior installed PBI Client.

image

I have also Command Line version that is scheduled to Export all the data a CSV file, containing +/- 280.000 records

image

Bonus :

Other benefits are that all of the features of the PBI Services remain available like Power Query in Data Flows

image

And as well the Scheduling Engine of PBI Services

The data set contains as well all the custom built Measures when downloading !

No need for Power BI Premium Smile

Use cases are that I needed to extract the data to do an ETL upload in a Data Warehouse.

Or Ad-Hoc analysis on the full data set using other tools


Power BI – Report Builder using Multi Value Parameters in DAX

December 20, 2019

How to create a Multi Value Parameter Report in Power BI Report Builder

You can download PBI Report Builder here

This builder looks very familiar to the SSRS Report Builder you are used to from MS Reporting Services

Building a single value parameter in DAX is easy.

But creating a Multi Value Parameter requires quite some steps …

image

Solution :

These are the steps to follow :

1. Create a Data Set with the SLICER value in PBI Desktop

2. Copy the PBI Query

Run the Performance Analyzer in PBI Desktop

image

Start Recording and Refresh

image

Copy the Query to the Main Data Set

image

And apply the Filters in the next step …


3. Create new Parameter referencing to the Main Data Set Query Filter

To create a Parameter in the RBI Report Builder you first have to create a new Data Set

image

The same way go to the Performance Analyzer and copy the Query from the corresponding Slicer

image

And add it to the Filter Data Set and test the DAX.

image

3. Create a PBI Report Builder Report Parameter

image

Select the values from the Parameter Data Set created earlier

image


4. Add a Parameter in the Main Sales Data Set

image

 Add the @xxx Report Parameter variable in the DAX Query

Run the report and test the Report Parameters

image

Enjoy !!



Office365 – How to Block Self Service Purchase Apps

December 5, 2019

Microsoft is enabling all O365 user to use self service Apps.

This means everyone on your tenant can start purchasing certain apps by default …

If you want disable this behaviour than you need to use PowerShell.

SOLUTION :

1. Download and install the PS MSCommerce Module

Install-Module -Name MSCommerce 

image

2. Next import the module and connect

Import-Module -Name MSCommerce

Connect-MSCommerce

image

3. Run these commands to see the status

Get-MSCommercePolicy -PolicyId AllowSelfServicePurchase 

Get-MSCommerceProductPolicies -PolicyId AllowSelfServicePurchase 

image

4. To disable 1 or all the products run this commands

Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId CFQ7TTC0KP0P -Enabled $False  
Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId CFQ7TTC0L3PB -Enabled $False  
Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId CFQ7TTC0KP0N -Enabled $False 

5. To disable all in Batch run this command

Get-MSCommerceProductPolicies -PolicyId AllowSelfServicePurchase | `

Where { $_.PolicyValue -eq “Enabled”} | `

forEach { 

Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId $_.ProductID -Enabled $false  }

image

You can check the status again :

image

Enjoy !


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.

SOLUTION :

Open Power BI and GET DATA  from the Web Connector

image

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

image

image

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

image

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

image

So I opened the Cmd line using Admin privileges

Next started PWSH.exe

Next I run this command :

Install-Module -Name DataGateway

image

Next Check the available cmdlets

Get-Command -Module DataGateway*

image

Next I ran this command

Import-Module DataGateway

image

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

So first get an upgrade from here :

https://github.com/PowerShell/PowerShell

image

After upgrading your can check the version using

$PSVersionTable

image

Next run these commands again :

import-module DataGateway
import-module DataGateway.profile

image

No errors now Smile

Run this command to login :

Login-DataGateway

It opens the browser and return this message

image

image

Next run this command to get your Cluster ID

Get-DataGatewayCluster

Next check the Gateway Status like this

Get-DataGatewayClusterStatus

image

So far so good Smile 

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 :

image

2. Command line tool NETSTAT

   Run NETSTAT –b command and look for Power BI

image

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\

image

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.

image

4. Using the Tabular Editor

But it needs some dependent MS AS AMO Libraries

image

image

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.

image

Follow these steps :

image

image

image

image

Copy it from here :

image

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

image

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

CLS

$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
$adapter.Fill($dataset)

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

image

To list data from a Table use the EVALUATE command

$Query = 'EVALUATE Tickets'

image


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).

clip_image002

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

image

Tools :

Power Pivot :

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

image

image

Dax Studio :

You can run commands directly against your Power BI Desktop.

Use DAX Commands

image

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

// Select data using DMV queries

SELECT * FROM $System.DISCOVER_PROPERTIES

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

EVALUATE Tickets

image

For more info on DMV Queries see here

Power BI Helper :

image

More Tools you can find here

Enjoy !


PowerBI – Number and Currency Formatting

October 31, 2017

After looking for a solution why my Number Formatting did not want to work out…

After creating a new Measure it returned the classing US based formatting.

image

While I wanted to have the EU formatting in my table.

Power BI Desktop Client Regional Setting :

Even after changing the Power BI Desktop Client settings, it did not take effect Sad smile

image

Solutions :

Go to Control Panel –> Region –> FORMAT

image

If this format is not in line with your region it will overrule the Power BI Regional settings ?

image

So after applying the correct region format settings on OS level, everything appeared as expected Smile

PS : These figures are just demo based Winking smile

Enjoy!