Microsoft Power BI is an intuitive and easy-to-use tool for business intelligence analysts to pick up and start using with minimal learning required. It makes building datasets and merging data incredibly simple and users quickly get the hang of importing data from many sources at once, joining them together and manipulating tables and columns.
However the ease of use can be deceiving. Without a clear plan or strategy in place, users can create a jenga-tower of interconnected pieces that gets more and more unstable over time.
This is where tools such as Tabular Editor start to be of use – analysing performance and highlighting where inconsistencies have bred or less-than-ideal construction has taken place. Think of it as a house-surveyor but for your MS Power BI datasets!
1. What is Tabular Editor?
Tabular Editor is a third-party software tool that can create and edit MS Power BI datasets outside of the MS Power BI Desktop tool. It can connect to datasets and ingest their metadata to summarise form and function as well as highlight any potential problems.
It includes a DAX (Data Analysis Expressions) editor where you can add or edit formulas to the model, as well as numerous tools for visualising data models, analysing queries and the speed of data access.
View of Tabular Editor
2. Why Should I Use It?
The more you use MS Power BI Desktop the more you will be tempted to add to a dataset. It is so simple to carry on adding more and more data to one model (as long as that data is related). At some point however you will reach a limit beyond which your model becomes unwieldy and slow to edit.
A data model in MS Power BI when edited in the desktop tools is a compressed in-memory database running on your PC or laptop. If you stretch the resources of your machine with too much data, too many calculated columns or too complex DAX statements – then the responsiveness of the software will degrade. You might be waiting minutes for a simple column rename operation.
At a simple level Tabular Editor might help because it works with the metadata and so you’ll find some editing tasks become immediately quicker again when you connect it to your dataset.
That’s not its main purpose though.
It exists to analyse performance of queries, and also achieve a couple of tasks that (so far) Microsoft have not implemented directly in MS Power BI Desktop.
The Model menu in Tabular Editor
Object Level Security and Perspectives are two features of MS Power BI that allow power users to control which consumers see which fields when they want to create or edit reports. However so far there are no methods for creating these in the MS Power BI software. Tabular Editor gives you access to those methods.
As well as this ‘extra’ functionality, Tabular Editor enables automated analysis of your data model which will highlight areas that have fallen foul of rules that you or your administrators have defined.
The Best Practice Analyser
These rules can be set at an organisation level and then used by all dataset designers to ensure that there is consistency of approach.
There are many other reasons to use Tabular Editor, but these are just a few to get us started. We’ll be looking in more detail at this and other tools over the coming months as we produce a series of blogs and webinars about Power BI and performance tuning.
If you’ve started using MS Power BI but hit a ceiling in your knowledge, or run up against a wall of performance problems, come to speak to Codestone and we’ll be more than willing to help knock down those walls and ceilings!
We’re experts in data, business intelligence and analysis solutions (not actual physical building work though…) and can help with advice or resource to get you to your dream home (of data).