Why Data Modeling Skills are essential for any BI Project?
When I started working with Power BI, I used to connect to all the data sources, merge them, and then work on the visual. The resulting dataset will be one huge table and the dataset would have no granularity and all the variables will be in the same level of detail. This typically helped address one BI Project and when a new business ask arises to add few new variables that were in different levels of detail, I had to gather all the data sources over again in a level of detail that is based upon the new ask. This was a bad practice and the dashboards couldn’t be scaled or improved based on new requests. Another problem is that extremely slow load times of the dashboards.
After working with Power BI for quite a few months, I realized that this is not working and started to focus on data modeling and it opened up a new world of relationships. The output was better performance and re-usable dataset regardless of what level of detail it is.
Any BI requests or a reporting task would be essentially about getting data from multiple sources and prepare insights for the target audience in an easily digestible format. The data sources can be spreadsheets, SQL servers, salesforce objects etc. Now if you extract data from all these sources separately and then merge them to a flat table, your source data for the BI tool may become too big and in turn will result in poor performance of the reports. The process of extracting all these data and then merging them can also be tedious even though you might have an ETL tool handy.
To address this issue, we can use a data model. Irrespective of any BI tool you use, there would be modeling capabilities with each. Tableau and Power BI are two common tools that I use and love the data modeling capabilities.
What is data modeling?
Data modeling is a process allowing each of the data sources to be imported separately and connect them using relationships without having to merge all the sources to a single table.
It is useful for
1. Eliminating redundant data.
2. Improve the efficiency of the dashboards.
3. Simplified querying.
4. Normalizing data sources.
For example, when you want to create a dashboard for insurance claims with doctor’s details, you do not have to extract data of insurance and doctors and merge them. With data modeling, Two or more tables can live separately and with common lookup tables, you can access any variables from any of the connected tables. They are all connected together through relationships.
The relationships can be
1:1 :Uncommon
1:*(1 to Many) / *:1 ( Many to 1) — Describes a relationship in which you have a unique value relating to many values in the other table.
For example: Product table can have unique product id and the sales table can have multiple rows of sales details of that specific product id. The association between these two tables 1 to Many.
*:* (Many to Many) — This is very uncommon relationship as many values can corresponds to many values and the matches would not be exact. Avoid Many to Many relationship.
Data Modelling can look overwhelming initally but it will be extremely useful when your project handles huge volume of data from multiple sources and when you care about the efficiency.
This is the microsoft’s link for learning data modeling and there are a lot of courses in Udemy and free videos in youtube focusing on data modeling.
https://docs.microsoft.com/en-us/learn/modules/design-model-power-bi/
