Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Koppe008
New Member

Grasping Star Schema

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.

1 REPLY 1
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors