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

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

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

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

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 ?

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

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.

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

This is no good solution …

Solution :

I created my own solution …

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.

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

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

Bonus :

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

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

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

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 …

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

Start Recording and Refresh

Copy the Query to the Main Data Set

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

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

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

3. Create a PBI Report Builder Report Parameter

Select the values from the Parameter Data Set created earlier

4. Add a Parameter in the Main Sales Data Set

 Add the @xxx Report Parameter variable in the DAX Query

Run the report and test the Report Parameters

Enjoy !!

SSRS – Shared Data Source : Oracle error ORA-03134

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

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

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