How to create a Power BI Dataflow Programmatically

well, you must be wondering what is this all about. Why would someone want to create a Power BI Dataflow programmatically when you can do it very easily using Power BI service? But there are scenarios where we must do little extra to achieve certain challenges. Let me explain what my scenario was.

I wanted to implement a Power BI template which analyze data generate from a product. This product is a PaSS product and it uses SQL server as the back end system.Each customer has its own database and database schema is almost same for all the customers .

clip_image002

The problem I had was, although all the database had same schema when it comes to transnational tables, there was a one important table with different schema for each database. That table was “Customer” table. In this product, “Customer” table is created by product it-self and all its attributes are configurable by users. In other words, if the “Customer” table of database1 have an attribute called “EmailAddress”, database2 might have the same attribute as “Email” or may be “EmailAddr”. Now in this case, my Power BI template will not work across all the databases as each database have different schema for same “Customer” table. That is where my Dataflow generator comes to play.

By the way, did you know that you can create a Power BI Dataflow by uploading a JSON file? Well you can, only thing you need to do is go to “Import Model” section and provide a valid JSON.

clip_image004

As the first step, I needed to create a template JSON file.I did not want to write everything from the scratch. Therefore, I created a Dataflow using Power BI service and the export as a JSON. This is the JSON I’m going to modify using .Net code to cater my requirement.

clip_image005

Once exported, I got a JSON file like below and in it, I had to change highlighted areas programmatically. First section is query section and it contains the source SQL statement of the Dataflow. Second section is attribute section which represent name and data type of selected attributes. .

image

I created the windows form application shown below to generate Customer Dataflow entity. When I want to rollout a Power BI report, all I have to do is to map database field name to entity type and generate a JSON and then upload it to Power BI service.

clip_image009

In document attribute, I changed “Source” to “Query” and added a native SQL query I created dynamically based on the attribute selected. As you can see, in the select statement attributes are separated from “#lf ,”.

clip_image011

I’m not planning to show all the codes I written in this application show in here. If you think this a valid scenario for you and my badly written code will helps you, comment here and I can share my code with you. Once I export the modify the JSON string, I can upload it to PowerBI using “Import model” option. However, if you do not set “allowNativeQueries” property within the JSON to “false”, Power BI server will throw an exception as shown below when trying to import the Dataflow. If you get this error, make sure you refresh the browser before re importing the corrected JSON file. You can set this property manually by opening the JSON file or within your application before importing.

clip_image013
clip_image015

As I said before, this is not a common scenario. But this can be useful when someone want to enforce some business logic to Dataflow creation or when bringing flex fields into a Dataflow. Again, if anyone need the code, just comment below. Thanks you for reading and stay safe!

2 thoughts on “How to create a Power BI Dataflow Programmatically

  1. Robert Maes October 16, 2020 / 5:50 am

    Hi,

    I’d love to have a look at your code. I tried to do the same. Although I was able to manipulate existing queries I could not create new queries. It keeps giving me an error about an unknown ID. Did you solve this problem?

    Like

  2. leventozyildirim December 25, 2020 / 12:55 pm

    Hey, I’m also interested pushing the business logic to dataflow and dynamically getting / transforming fields for different use cases. Can you please share the code?

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s