Top four mistakes people make getting their data into Power BI
It’s no surprise that we are huge Power BI fans over here at Tugger. What’s not to love about code-free data analytics! Power BI has really opened up the world of business intelligence to start-ups and SMEs. It’s no longer for giant conglomerates with entire data-departments, it’s helping a heap of all-size business.
But enough gushing, there is a downside - there are common mistakes that can turn your beautiful reporting dream into a nightmare. But fear not, we got you! In this article, we highlight the top mistakes people make when getting their data into Power BI.
This is one of our more technical articles as we delve into data modelling specifics. If you're planning to use Tugger to get your data into Power BI, it’s worth noting that we’ll handle the data modelling for you, so you won’t have these issues. However, it’s still worth a read because, once your data is in Power BI, you do have the option to make adjustments, which you may want to do if processes change within your business.
And any data model will only be as good as the data in the data source, so it’s a little reminder to keep things neat in your system.
1. Not caring about data relationships in data modelling
Your data model is what Power BI uses to understand your data and build your reports. It’s your data’s structure, it’s where you define what the data is showing. I like to visualise it as a flow chart. Your data model brings depth to your data by outlining how your data points work together - the relationship between them if you like. Without outlining these relationships, your data is flat and can become meaningless.
It’s crucial that you define these relationships correctly. If your data modelling is wrong, then all your reporting will be too, and misleading reporting is worse than no reporting. But still, forgetting about these relationships is a surprisingly common Power BI mistake.
So what’s the solution? Something that will help with your advanced data modelling, is to check out the star schema approach. No, we didn’t just make that up. Star schema is a modelling approach in which you classify your data tables as either ‘dimension’ or ‘fact.’
Dimension tables = business entities. This can include people, places, and concepts e.g. time.
Fact tables = observations or events. This can be stock, orders, exchange rates.
This is a brief mention, but if you’re concerned about your data modelling, digging deeper into this will help you find a best practice approach for mapping out data relationships.
It’s also worth making sure that as often as possible, your data relationships are one way, rather than both - directional, meaning they go in both directions. This is something Power BI themselves recommend because having the relationship go both ways can hinder query performance and cause ambiguity over what the data is showing. Basically, it can start to get your data in a bit of a muddle.
2. Using confusing or messy table and column names
Ever look at an Excel spreadsheet and wonder what a column is showing, flip to the column header and you're still none the wiser? Often, we have abbreviations for things that mean something to us at the time, or maybe to us alone, this isn’t great in the long run. The more you can keep the table and column headers in your data model simple and descriptive the better. This is one you’ll really thank us for down-the-line.
3. Not sorting your data before you transfer it
Power BI lets you connect directly to data sources, which is a much-used feature. But the issue is, your data can easily get confused. You’re just sending Power BI a mass of data without any structure (see common mistake one).
Whereas, if you load your data from an app, or an ETL tool (ahem, like Tugger) then your data has to go into a data warehouse, and for this, your data needs dimension and fact tables which means it's set-up specifically for reporting.
The data warehouse requiring this structure means your data modelling is already streets ahead of simply dumping your data across straight from your system, where Power BI can struggle to make complete sense of it. And you end up sending so many tables that the model becomes huge with too many relationships. It is crucial that you only include the tables and columns that are necessary and support your business needs and what you want to report on. Otherwise, it just makes everything more complicated and confusing.
4. Not using folders for your measures
OK, this one is more about sorting your data once it’s in Power BI, than sorting it to get it across but never the less it’s on topic while we’re shining a light on the importance of neat and tidy data modelling.
To explain this, we’ll take a step back. DAX (Data Analysis Expressions) is a data modelling language that allows for complicated calculations in Power BI. Measures in Power BI are used for advanced calculations as they evaluate DAX to give out the results from complex queries.
The more you can use measures to create complex calculations, the more advanced your Power BI reports will become. Measures can do all sorts of calculations to advance and deepen your reporting.
So why is it a mistake not to put these measures in folders? Simple, as you grow your reporting capabilities, the more you can keep your data tidy and organised, than the lesser the risk of mistakes, misreporting or simply confusion down-the-line.
So there we have it, a whistle stop tour on the four most common mistakes we see when people try to load their data into Power BI. As well as highlighting these mistakes, and how to avoid them, we hope this article has got you thinking about just how important getting your data into Power BI is, and why it’s worth getting support from a developer, or a tool before you attempt it. This is the first, and more important step in your Power BI journey, and what you put in now, will pay you back in spades once you’re seeing pin perfect business reporting.