Defining Relationships Between Entities In The Common Data Model To Automatically Create Relationships In Power BI

Following on from my last post on attaching manually-created Common Data Model folders in Power BI dataflows, I was asked whether defining relationships between entities in the model.json file of the CDM folder results in relationships being created between tables in a Power BI dataset. I’ve just tested it, and I can confirm it does.

Consider a CDM folder that contains two entities, Sales and Fruit. The Sales entity contains monthly sales data:

image

…and the Fruit entity contains a list of fruit sold:

image

Now –and this is important – let’s say you have defined the entities in the model.json so that the Sales entity  has attributes Month, Product and Sales:

image

…and the Fruit entity has a single attribute called Fruit:

image

Because the Sales entity and the Fruit entity use different names for the attributes that contain the names of the fruit sold, when the two entities are loaded into Power BI no relationships are created between the two tables. There’s no way that Power BI can guess that there should be a relationship between the Product and Fruit columns based on these names.

However, if you add a relationship definition (as documented here) to the model.json file like so:

image

Then, when you load the two entities as tables in the same Power BI dataset, you get a relationship created automatically:

image

As far as I can see it does this by adding a key definition to the Sales table in a similar way to the way the Table.AddKeyM M function/Remove Duplicates transformation does, as I blogged here.

Now if we could add DAX calculations (calculated columns and especially measures) to the definition of the model.json file, so they were automatically created when entities were imported from a dataflow, that would be really cool. I don’t see why this would not be possible because you could store all kinds of useful information – such as the DAX for the calculations – in the metadata record of an M query (you can access this yourself using the Value.Metadata M function) loaded into a dataset, and the Power BI engine could read it when the table is loaded and create the calculations from this.

6 responses

  1. Chris, this looks useful, does it require Power BI Premium or will it work for Pro? Maybe that’s two questions for Desktop and Service?

    • You do not need Premium to do this – you can attach an external CDM folder when your workspace is in shared capacity. After that, all your end users need is access to the workspace.

  2. Great job puzzling this out. I don’t think the CDM standard will include ability to define DAX calculations since CDM is a general entity specification used by multiple Microsoft services. Although, having the ability to define custom metadata in the spec and then stuffing DAX calculations in it and then auto-generating the Power BI model has some possibilities.

    Chris, do you think dataflows is the mechanism to accomplish the equivalent of cube write-backs? We’ve just been missing a way to write data from Power BI and now we have it and with multiple files in one folder you can even call it partition-based write-backs. For example, if you have a forecast dataset from source system and we create a forecast_override dataset from a spreadsheet that will allow what-if analysis and combining the two data sets for Power BI reporting will yield most of the benefit of writeback.

    • Interestingly the model.json files for dataflows created in Power BI do include the M code for queries used, so I don’t see why they couldn’t include DAX too.

      Re writeback… Hmm, interesting idea. Maybe it is possible but I would need to think about it.

  3. Pingback: Jump Start to Power BI Data Flows – datadriven.pro

  4. Pingback: Self-Service-Datenaufbereitung für Big Data in Power BI (Dataflows) – datadriven.pro

Leave a Reply to MikeAinOz Cancel reply

Fill in your details below or click an icon to log in:

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: