Data Modelling - Working with data from 5 sources

Hello community,

back again with a new project. Thanks again a lot for all the help about a year ago. PowerBI up and running.

My new project is about sales analysis and I wasn't able to find some applicable references for setting up the right data model. I already setup something working yesterday, but performance is horrible. Few thousand lines are putting my computer for a minute on pause when refreshing or updating the pivot table. So I think I need to completely overthink my data model.

The facts:

Table 1: Forecast Volume

   Columns: Product Code, Customer Code, Distribution Channel, Period, Product Category, Volume

   Unique Identifier is a combination of Product Code, Customer Code and Distrubtion Channel.

Table 2: Actual Sales Price

   Columns: Product Code, Customer Code, Distribution Channel, Sales Price

Table 3: Budget Cost

   Columns: Product Code, Budgeted Cost

Table 4: Actual Cost

   Columns: Product Code, Actual Cost

Bonus Table 5: Sales Budget

   Columns: Product Code, Customer Code, Distribution Channel, Product Category, Period, Budget Volume, Budget Price

Goal:

Create an efficient data model which allows the following analysis:

• Calculate FC Sales and Cost with data in Table 1, 2 and 3 (This already was too much with current model.)
• Compare FC and Budget Sales with data in Table 1, 2, 5
• Compare FC and Budget sales and cost performance with data in Table 1, 2, 3, 4, 5

I am aware of star schema, header/details table, denormalization etc. and would consider myself an advanced beginner. But before I start testing through what is best fit for my project I wanted to get some generic tips what should work best. I tried to denormalize and merge data like actual sales price, budgeted/actual cost into the table with FC volume but this seems to cause performance issues. (Unless the performance issue is from all the power query steps needed to get the data into the data model.)

Many thanks in advance

D