Grasping Star Schema

[Koppe008]

Greetings everyone! I'm seeking advice and best practices on optimizing my table management to achieve the desired outcome, as shown in this example screenshot: https://imgur.com/VednIpQ

I have been experimenting with different tables, such as creating a duplicate of my Products table and renaming it DimProducts, as demonstrated in the example. My main challenge lies in merging queries as new, like Products+Customers+Orders, and then opening the merged table to select the appropriate ID to insert into the "FactSales" table (e.g., EmployeeID).

However, my primary obstacle is synchronizing the date table with the OrderDate table in the "FactSales" Table. I encounter this error message: https://imgur.com/FMtLBxC. I've double-checked the "transform data" section to ensure all necessary dates are included, and it seems they are.

Another issue I face is that after merging data, I sometimes end up with 999+ rows, whereas I only require 518 rows. I've managed to resolve this issue by tweaking the settings, but I haven't been able to consistently reproduce the steps I've taken to fix it.

I hope my explanation is clear enough, as this is my first attempt at creating a Star Schema using PowerBI.

I am not sure how to add the open source data I have collected but it's from w3schools.

[amitchandak]

@Koppe008 , You can not have the order ID in Date table. You can create date table using calendar or calendar auto

example

Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "FY Year", if( Month(_max) <7 , year(_max)-1 ,year(_max))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

To me the first one seems fine. Unless you have reason to merge