PowerBI Best Practice: Views for non-database data

Marco Russo has shared bunch of very valuable PowerBI best practice tricks for designing your PowerBI model here: http://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/?platform=hootsuite

One of them being never to directly import a database table, but to use a view instead as a kind of abstraction layer. It separates your business logic (that determines how the tables should be structured that will be passed into the data model) from your actual/incoming data (your source systems, whose table structure you cannot change and which might change itself over time).

This is really important in my eyes, as it will be only a question of time when your underlying data will change and the last thing you want to do is to edit all datamodels or even reports in your (multiple and possibly decentralized) PowerBI files!

But what do you do if your data don’t come from databases or you have no possibility to create views on them? Although you cannot rely on a external centralized abstraction layer, you can create your own in the query editor (or Power Query, if you do it in Excel). Thereby avoiding that changes in the datasources affect your datamodel with all your measures in them (or even worse: the fields in your reports): Create an alias-query between your incoming data and the query that will load to the datamodel. This query will assign names and formats to your columns that will stick to your data-model-tables. Then reference this table and do all the other transformations that might be necessary before loading to the datamodel. That way you reduce the efford it takes when your underlying data changes.

Now we combine this with Chris Webb’s tip on how to avoid (database) connection duplication and get:

PowerBI Best Practice – views for non-DB-data in action:

  1. Access all external data sources only once
  2. Plan your alias-query after them
  3. But create this query only once you need it. Renaming and reformatting will cost performance and if the data comes in fine currently, no need to touch anything

So this was to make you aware, that the time will come when you have to deal with changing data sources and how to prepare now. But most of all it was about making you consider:

Don’t be a slave of your incoming data!

Take ownership of the tables in your data model now!

Your business logic and the particularities of DAX should determine how you pass your data into the model. Use the query editor (or Power Query) to transform your incoming data in order to make your DAX-life easier. The query editor wasn’t designed to push your data through the shortest way, but to transform the incoming data into a shape that really suits you.

Enjoy & stay queryious 🙂

Leave a Reply