11.8 Modelling Best Practices
- Use a star schema: facts in the centre, dimensions around.
- Use one-to-many, single-direction relationships wherever possible.
- Always build a dedicated, marked date table and turn off Auto date/time.
- Hide foreign keys in the fact table (users should filter using the dimension columns).
- Remove unused columns and rows in Power Query – smaller model, faster report.
- Use integer keys and correct data types; avoid high-cardinality text keys where possible.
- Prefer measures over calculated columns for aggregations.
- Give tables and columns business-friendly names (
Customer Name, notcust_nm). - Set Summarize by = None for numeric attributes like Year and IDs.
- Keep a consistent number format and put measures in display folders with descriptions.
Mini-exercise
Load the Blinkit tables. Build the star schema (रचना), create the DAX date table above, mark it as a date table, set Month Name to sort by Month Number, create the inactive Delivered Date relationship, and hide all ID columns in Orders.
Ravindra Bagale's Tip
Friends, many students let Autodetect relationships create links on columns with similar names, like "ID" to "ID". Turn it off for new projects (File › Options › Data Load) and create each relationship deliberately. Review Model view after every new table. Clear?
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students Autodetect relationships ला सारख्या नावांच्या columns वर links बनवू देतात, जसं "ID" ते "ID". नव्या projects साठी ते बंद करा (File › Options › Data Load) आणि प्रत्येक relationship विचारपूर्वक स्वतः बनवा. प्रत्येक नवा table जोडल्यानंतर Model view तपासा. समजलं का?
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students Autodetect relationships को मिलते-जुलते नाम वाले columns पर links बनाने देते हैं, जैसे "ID" से "ID". नए projects के लिए इसे बंद करो (File › Options › Data Load) और हर relationship सोच-समझकर ख़ुद बनाओ. हर नई table के बाद Model view check करो. समझ आया?
Thodkyaat sangaycha tar (quick recap)
Star schema vapra, relationships single direction theva, Date table continuous theva aani Mark as date table karayla visru naka. Samjla ka? He concepts pudhe pratyek module madhe lagtil. Aata pudhe jaauya hierarchies aani groups kade.