Data modelling is nothing but a process through which data is stored structurally in a particular format where the relationship between different tables enables decision making. As your business evolves, it is essential to customize your data modelling accordingly. Thus, it is essential that you keep them updating over time. The best practice here is to store your data models in as easy-to-manage repository such that you can make easy adjustments on the go.
Why you need Data Modelling?
Let’s take an example: Say, you have already sourced Sales information of your organization and you stored it in “Sales” table sliced by various categories and sub-categories. Likewise, you have sourced all expense information from “Expense” Table sliced by the same categories and sub-categories as used in the Sales table.
Now, you need to show a visualization using a simple filter on the performance (Net Sales – Net Expenses) of a particular category or sub-category. How will you do that?
In such a scenario, you may need to create a data model which has Category and sub-category data in a Table. Then establish a relationship (One to many) between “Category/sub Category” – “Sales” Table & between “Category / Subcategory” – “Expense” Table.
It is also important to mention that the data model created at this stage is different from the data model/schema that is available at source data/transaction systems. Typically, as noted earlier, the data model at source system is a relational data model and is designed to be great at getting data into a database in a way that maintains integrity and stores every piece of non-key data. However, data model developed for reporting and analytics have a completely different objective and it needs to be in de-normalized form - designs are generally in the form of a star schema.
Below is a sample example of Data model for a simple FP&A Dashboard. In reality, depending on the complexity of the business, data models are organized. Typically, it is best to start simple and scale it up as we increase the complexity.
Tools used for Data Modelling
You can directly create the Data Model in Power BI. Alternatively, if you have a lot of reports, creating a data model would lead to a situation where there would be multiple data models in Power BI. In that stage, probably it would be a good idea to move the entire data model to SSAS.