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 !!



SSRS – Shared Data Source : Oracle error ORA-03134

June 18, 2014

This error came up setting up a shared Data Source in SharePoint ?!

ORA-03134: Connections to this server version are no longer supported.

For a mix of older versions of Oracle  databases. You need to pick the most appropriate driver.

Solution : Install a lower version of the Instant Client Libr. (just downgrade to the lowest level of Database version)

The good news:

It is possible to access an Oracle 8 Server with Oracle Instant Client 10 (see Metalink Note 207303.1).
The bad news: There is no Oracle Instant Client version 9. The product was introduced with Oracle 10g.

Connections to Oracle 9.0.1 databases are not supported.
Connecting with a 10.2 Instant Client to server version 9.2.0.4 or higher is supported.

or avoid the problem

Don’t use a shared Data Source but a Report Data Source


SSRS – Report Builder 3.0 – Oracle Data Source Issue

June 18, 2014

How to solve Publish Reports via Report Builder 3.0 not able to run against the Oracle Data Source.

I had installed the x64 bit Oracle driver on the server, but was unable to get it to work using the Report Builder 3.0

image

When you open the Report builder and add a new Data Source for Oracle you can choose between the :

.NET Framework Data Provider for Oracle or a regular ODBC or OLE DB connection

As you can see the Oracle Data Source supports only till version 9i.

So if you want to use a more recent version of Oracle, you need to use the ODBC selection.

Anyhow the news here is that Report Builder 3.0 will ONLY recognize the 32bit drivers.

Lessons learned :

RB 3.0 Data Sources = x32 bit

SSRS Data Sources = x64 bit

If you publish reports to a SharePoint library make sure you have both version of the Oracle driver installed on the server side by side.

REMARKS:

1. The older Oracle versions have a different Join Types. The ANSI SQL based one, while MS SQL does not have by default.

So you take this in account when write your queries in the report Builder, which gives you an other challenge 🙂

2. Report Builder has a different way to access the Parameter values using a ‘:’ instead of an ‘@’ character in your Query statement

MS SQL =  … WHERE DatePart(year,sub.StartDate) = @Year

ORACLE = …. WHERE  To_Char( StartDate,’yyyy’)  = :Year