Data Modelling - Working with data from 5 sources

[danielboi]

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

[TomMartens · Accepted solution]

Hey @danielboi ,

from the information you provided my model would contain 4 dimension tables

• period
• customer
• distribution channel
• product

and 3 fact tables

• forecast volume
• actual sales price
• sales budget

I'm wondering why there is a column Product Category in both of the tables Forecast Volume and Sales Budget.

From my understanding based, on the columns names, the column Product Category groups different Product Codes. For this reason I would have created a Product Dimension table that will contain the following columns:

Product Code | Product Category | Actual Cost | Budgeted Cost

Then I would connect to the Product dimension table to the three fact tables, the Product Code column is used to create the relationships.

Hopefully, this provides some ideas helping you to proceed.

If not, provide a screenshot of your datamodel, the model view.
In addition to that consider creating an Excel file with sample data, one sheet for each of your five tables. Upload the Excel file to onedrive or dropbox and share the link. Describe the issues that you are facing based on the sample data you provided.

Regards,

Tom

[danielboi]

Danke dir Tom,

works like a charm and I finally learnt how to use RELATED and CROSSFILTER.

Have a nice weekend.

[TomMartens]

Perfect! Happy to hear it worked!

[danielboi]

Moin Moin Tom,

each table contains many more columns. High level of data redundancy, but I narrowed it down. Indeed no point to mention that for budget sales. Could be handy to create the dimension product to ensure it covers everything, but not part of my question.

You are right, product code differns from Product Catgory. Like Product code is 123 and product category is Mobile Phone.

Already restarted from the scratch and optimized steps before importing data to avoid transposing long tables etc to have the right headers.

I'll keep you posted!

Dan