Create high quality Power BI solutions
Power BI is a great suite of tools that enables business users, or anyone for that matter, to create advanced analysis on nearly any type or structure of data. But what does it really take to build high quality, what I like to call, scalable solutions with Power BI? You may watch the full video of this tutorial at the bottom of this blog.
Unfortunately, there’s a little bit more to it than just some plug and play action. To me, one of the greatest things about Power BI is the fact that you can get going relatively quickly, without having to go through months of education and training.
There is though no getting around the fact that if you want to do game changing things with Power BI within your organisation, you are going to have to invest some time in really getting skilled up in these four areas;
- Data Modeling
- M (Power Query/PQL)
- DAX (Data Analysis Expressions)
- Data Visualisations in Reports & Dashboards
I’ll look to elaborate further to highlight what it will take to get going on all of these four ‘pillars’. There are multiples of books written on all of these individual topics alone. So they are deep and sometimes quite technical topics. The reality, is that we want to be doing amazing things with data, and doing those in Power BI means understanding these four pillars and putting them into practice effectively.
Data Modeling
Data Modeling within Power BI is about understanding how to build a schema of tables with connections that show the relationships between these tables. How you structure your ‘data models’ determines how you will eventually be able to slice and dice the data in your reports and dashboards.
A lot of data starts out in large flat files with multiple columns, like dates, products, product categories, customers, sales, margins, profits etc. The idea with data models is that wherever possible you separate out or segment details around the data set. For example, dates, customers, products and sales should all be separate tables and be linked together with relationships.
You do this as it is far more efficient and the ability to build out the richness of your data becomes far easier. Performance wise in Power BI, it also makes a huge difference.
For example, say you have 500 clients, but a sales table of 1 million rows. If you wanted to group your customers by their ‘loyalty’ profile, it is far easier to create a column on a table of 500 rows versus 1 million.
As I’ve mentioned, this is a detailed topic that has is origins from full scale data warehouses, but for Power BI users who want to stand up great solutions efficiently then just focus on segmenting each level of granularity of your data into separate tables wherever possible. It will make things far easy when you look to build out your reports and you’ll be surprised at how much more scalable it is to add things later.
M (Power Query/PQL)
It’s hard to know what to call the next section as there actually isn’t even an official name currently for the coding language which sits in behind the ‘query editor’ in Power BI. Within the Power BI of old (the excel add-ins), it was called ‘M’, also referred to as just Power Query, and more recently I’ve seen it called PQL which I quite like as it quite a close relation to SQL, and may in time become used just as much, especially as Power BI expands rapidly.
Anyways, ‘M’ is the automation engine which pulls data from an original data source and then allows you to automatically clean and manipulate it to whichever format you like. The key here is the automation piece. Being able to automatically pull multiples of datasets or tables into your data model and then being able to get it set up into the format you like is seriously awesome and saves you so much time it’s crazy.
This is what I mean when I talk over and over about scalability. If it use to take 4-8 hours a week just to get your data into the right format, and you can now do that in about 5 seconds, that leaves a lot more time to work on more analysis or to build out better reports.
As data volumes continue to multiply by the day, really understanding how to clean up all you datasets will be key. If you learn ‘M’ well, then you will see your output go up by around a factor of x5. I can confidently proclaim this just from personal experience. I have scars thinking back to the days of grinding away formatting data in excel, or writing VBA code to get it set up correctly.
You can almost totally skip over this part now if you can set up your data extracting, transformation and loading to automatically happen within the query editor of Power BI and with ‘M’ code.
DAX (Data Analysis Expressions)
Now DAX is seriously awesome, but also seriously complicated in some parts. You’ll likely get started on DAX, and think this isn’t too bad, and then you’ll get to something a little more advanced and then get stuck for hours. I wish it was easier to do great stuff, but unfortuantely there’s a bit more too it with DAX. Trust me here, it is absolutely worth it if you are willing to put the time in to learn some theory and also practice..a lot!
One thing also worth mentioning is that DAX is the language all up and down Microsoft’s database stack. So with your investment in learning the language you are actually learning how to use three significant analytical technologies including, SQL Server Analysis Services, PowerPivot and now Power BI.
It’s clear that DAX will be the core data language within Microsoft’s data tools, so an investment here will undoubtedly serve you well.
There’s already some quality books out there that go through the language in great detail. From beginner to advanced. My recommended educators are Rob Collie, Matt Allington, and the Italians (Marco Russo and Alberto Ferrari). The latter writing what could be called ‘the bible’ on DAX. Their books can all be found on Amazon, and have a good mix of beginner to advanced concepts. Keep these as your companion as you learn and use Power BI. They are invaluable.
One of the greatest differences between DAX and excel formulas, is that it is very difficult, if not impossible, to know what a formula result is returning unless you can evaluate the environment in which it is operating in. You must have a solid understanding of these three environments; evaluation (or initial) context, filter context and row context.
These bring a bit more complexity to your formulas but also a lot more power and scalability to your models, which you will discover very quickly.
Data Visualizatons In Reports & Dashboards
I will go far as to say that nothing you do behind the scenes in Power BI really matters if your visuals aren’t compelling and easy to consume. It is the missing piece of the puzzle I’m seeing in a lot of posted reports and dashboards on numerous showcase.
In saying that, it has a lot to with personal preference or consumer preference. I find myself in this position quite often. What I like to see may not be intuitive or summarised enough for someone else.
The great thing here now though is to make changes does not take much time at all. What use to take 100s of clicks in excel to format everything then get it into Powerpoint is now just a few clicks. Power BI does all the hard work on the visuals.
One significant downside to Power BI at the moment is the colours. You can’t set colours schemes. This is a must do as soon as possible for the Microsoft team as colours in my view make or break reports and presentations. It’s not that you can’t get the colours you want, it just slows down the report creation immensely having to manually change every colour in reports.
Visualising data is key, almost as important as the architecture behind it in my opinion. We will be focussing a lot on this going forward.
*****Related Links*****
How To Organize Your Power BI Data Model – Best Practice Tips
My Practice Tips When Using The Power BI Advanced Editor
Formatting DAX Code In Power BI
To sum it all up
If you add up all the time to be saved and benefits to be shared across an organisation, Power BI as an analytical platform is just an absolute no-brainer. The four pillars are the key to every data model that you create. Improving in these areas over time will enable anyone to create exceptionally high quality solutions that any enterprise would no doubt benefit from.
More details, dates & times and registration can be completed here – Steps To Mastering Power BI
All the best!
Sam
