Fact & Dimension tables in Power BI | Data Modelling

In this video we will cover how to create FACT & DIMENSION tables in Power BI. We will define what Fact & Dimension tables are and how we can go abouts creating them from out Data.
-
-
0:00 Introduction
0:27 Star Schema Theory
2:54 How to build a model
-
-
-
-
-
-
-
-
Search Tags: Microsoft PowerBI, Microsoft Power BI, PowerBI, Power BI, PowerBI for Desktop, PowerBI Pro, PowerBI Online, PowerBI Tutorial, Power BI Tutorial, The Best PowerBI Design, Inspiring and Amazing PowerBI Design, PowerBI Dashboard, PowerBI Data Visualization, Power BI Data Analytics, Power BI Analysis, PowerBI Report and Dashboard Design, Easy PowerBI Design, Easy PowerBI Data Visualization, Easy PowerBI Tutorial, Power BI End to End Tutorial, PowerBI End to End Design, Quick and Easy, Power BI Tutorial From Beginner to Pro Desktop to Dashboard, PowerBI Data Modeling, PowerBI Measures, What is PowerBI, How to use PowerBI, Is PowerBI easy to learn, PowerBI vs Tableau, PowerBI Online vs Tableau Online, Power BI Tutorial for Beginners - Basics and Beyond, Power BI Tutorial for Beginners - Getting Started, Creating a Power BI report in under 5 mins, Analyze & Visualize Data with Power BI, Mastering Microsoft Power BI - Introduction to Power BI, Power BI Tutorial For Beginners | Introduction to Power BI, PowerBI Navigator, PowerBI Navigation, Data modelling in Power BI Desktop, Power BI Tutorial Videos, Power BI Advanced Tutorial, PowerBI for Freelancers, Power BI Slicers vs Filters, Drilling into drillthrough in Power BI Desktop, Power BI - Dashboards vs. Reports, Power BI Bookmarks, Selections and Toggles, Power BI Beginner Tutorial, How to build Power BI Dashboards, Design, Publish and Share a Sales Dashboard in Power BI, Power BI Desktop for Beginners: Create your first Power BI report and dashboard in 10 minutes, PowerBI For Newbies, PowerBI Ninja Tutorial, How to Design Visually Stunning Power BI Reports, PowerBI Basic Dashboard and Design, Microsoft Power BI: Building spectacular Power BI dashboards, PowerBI Infograph, Easily create infographics in Power BI with infographics desginer, Power BI Dashboard & Reports - Sales Analysis, Top 5 DAX tricks for super effective Power BI dashboards, Power BI visualization best practices, How to create PowerBI Dashboard Reports, Top 10 Most Effective Power BI Dashboard Tips & Tricks In, Design, Publish and Share a Sales Dashboard in Power BI, Power BI - Dashboards vs. Reports, How to Create Beautiful Power BI Dashboards Using the Power Pattern, PowerBI Tips & Tricks for Stunning Power BI Reports!, Power BI Dashboard & Reports - Inventory Analysis, PowerBI for Sales and Marketing, PowerBI for Supply Chain, PowerBI for Inventory, PowerBI for Supply Chain, PowerBI for Purchasing, PowerBI for Warehouse, PowerBI for Logistics, PowerBI for HR, PowerBI for Team Performance and Dashboard, PowerBI for Managers, PowerBI for Employees Team Performance, PowerBI for Excel Users, PowerBI PowerPivot, Import Data to PowerBI, PowerBI to Stores, The Best PowerBI Design, Build Top Amazing PowerBI Design, How to Design Visually Stunning Power BI Reports, Power BI Report Design Tips And Inspiration, Design a Navigation Panel - Power BI Desktop, Power BI Tutorial | Creating Landing Page using Dashboard and Tiles, Power BI Custom Visuals - Infographic Designer, Basic Dashboard in PowerBI, The Best PowerBI, PowerBI Design, Microsoft PowerBI, Free PowerBI Design and Downloads, Free download PowerBI PBIX File, The Microsoft PowerBI Desktop, Power BI App Navigation, Designing Impactful Reports in Power BI (Free Webinar), Free Download, Free Design, PowerBI Template and pbix, Power BI - Do it Yourself Tutorial - Dashboard Design, Create a custom KPI Card in a Power BI report, Introduction to Power BI Designer, 7 Worst Power BI Dashboard Mistakes & How to Avoid Them [PBIX examples included], Data Modeling with Power BI and Power Query, PowerBI M Language, PowerBI Best Practices, PowerBI Desktop, User and Developer, Visually appealing powerbi, PowerBI Trending

[@MD-cu6wq]

This is the best demo - nobody explains that the original values need to be removed from the master data table. This is not obvious to newbies. I am working with non financial data so need to add my own measures. Thank you!!!

[@Oli9995]

Omg yes. I've been looking for this. How to convert denormalised table into facts and dimensions.

[@oscarmacho2798]

You always need to add your measures

[@kristinajensen711]

Thank you, and just for this video but for the whole channel. I stumbled upon it yesterday (looking for non-pretensions explanation of variables in DAX measures) and I am a fan! I watched all your videos, some more than once - and you brought back some joy , enthusiasm and HOPE that I could understand and learn Power BI. Thank you a million again and please keep up the fantastic job!

[@amid7757]

The best demo I've ever listenned. Thank you so muchh! God bless ya!

[@user-sl4ux8hi3d]

Thank you Power BI Guy, this video is helping more than you can imagine :)

[@krishnakantapanda6835]

That was super super super helpful.... finally I could confidently say that I have clearly understood fact vs dim, foreign vs primary key and star schema concept❤

[@RedCouchChess]

I hope your day is filled with laughter and joy. Thank you for the service you provide to us unqualified data analysts :)

[@alimirazimzadeh544]

I have seen over 100 videos, and this is my favorite

[@JackWebb-pd3dy]

Thankyou for the video, really well explained and great demonstration of the model in effect. At the end you mention a custom date table but then video cuts out - do you have one explaining this or a video you'd recommend?

[@solomonidowu8284]

Hi. You did a wonderful job here. Thank a lot.

[@tlee7028]

Thank you! This is what I was looking for !

[@houseandhobby6971]

New terminology for classical db concepts. I'm so old. We just covered the concept of normalizing a database in this video. The original term for dim table is parent table. For fact table, child table was the original term, but a specific type of child table that stores observations rather than child entities (in turn pointing to parent entities). Foreign key term still lives on. I would have used Excel (old school, manual, slower). Dim tables are about entities. Fact tables about data observations of the entities. Good normalization results in a hierarchy of dim tables (entities) thanks to common attributes across entities. Nice vid, just chatting some here. Thx

[@raideryvs5595]

In a star schema, the dimensions generally undergo denormalization. For instance, consider an employee dimension where employees are associated with a department. In a normalized data model, distinct tables for employees and departments exist, connected through a foreign key relationship. However, in a pure star schema, only the employee table is present, and department data is duplicated for each employee. This optimization enhances data retrieval speed, and the additional storage overhead is deemed acceptable, especially when contrasted with the space consumed by the fact table.

[@fahadamir6349]

superb brother, MashaAllah you did a great job its really helpful

[@hardik7385]

Amazing crisp and informative

[@MegaAbgt]

What a fast and elegant tutorial. I try to find a power bi job but very hard to find one as a newbie.

[@saru8773]

Same

[@cumeh821]

Thank you for this video. So helpful.

[@dixitmca]

Best Demo ..thank you.

[@anthonyceze5589]

Excellent job, you just gave me the best lecture and gift of the month...thanks a lot bro

[@Transformita]

You're most welcome! Glad it was of benefit. I've released similar videos and topics, hopefully they help. Don't forget to like and subscribe. It's appreciated! 🙂

[@asaravana6701]

Very insightful information sir thank you now i am getting some clarity on this concepts

[@dixitmca]

please keep posting wonderful videos

[@akthar3]

its a great video showing how the the context data is moved to a dimension table where a key is created in the dimension table and added to the fact table as a foreign key. A question of concern , should i be doing that to flattened sql server table (the main raw data source) with over 10 million rows ? - this is how the data comes to us from another team - 1 single flat table s that needs to split into several fact tables and dimensions. As I plan to do all etl in the sql server , should i materialise the fact and dimension tables (create physical static tables ) or just create views ? Should i be creating the key in the dimension table or view and then updating it or joining it to the fact table ? The processing may become a little too much for the Azure sql server capacity ? I hope my question is not too confusing . Thanks you

[@PersonalityDev-T]

Thanks much . very good explanation

[@Renotalks]

Thanks man appreciate your efforts

[@sidharthmandal9957]

Hey man, really loved this tutorial. Learned a lot. Can i have the data set plz? I want to have handson on it.

[@akkialekhya7638]

This is so helpful to me.tq u

[@bchi49]

Thank you. Just was what I was looking for. I have one to many table relationships, I can do the same on the many side.

[@Transformita]

Glad it was helpful! If you like my content, consider subscribing 🙏, I'll have more videos on this topic that go into bridging tables etc. for modelling.

[@akthar3]

if your facts are descriptive comments (eg 500 characters) along with numerical values and measures associated to the comments ..do you keep them both in the same fact table ?

[@SDDEditzPro]

We can use reference option instead of duplicate for creating dimension tables. Right?

[@nathanmutala2941]

merci, ce video m'a beaucoup aidé.

[@subhashisnayak819]

Amazing you are.. i subscribed for more videos

[@fmufafa]

You are just the best.

[@shimamoayeri8778]

verrrry good. thanks

[@HoaNguyen-ji5fz]

I love it. Thanks

[@ishneetsinghcheema6727]

You are the mannn!! Thanks so much. Wish you all the best for futures releases.

[@Transformita]

Thank you. Glad it helped!

[@SSRealEstates83]

I have a question here,when we duplicate,remove other columns, creating inder...then after when we merge...the number of rows will get increase or not???please answer.... Thank you

[@kartikeyatiwari6678]

Awesome 🎉

[@novandc9223]

thankyou good explanation

[@dakota3436]

Awesome, thank you! I have one question. If you were to update the data from your original source file by copy/pasting over an older export or changing the source file, all of the dims you created would still function correct?

[@Transformita]

Yes, as long as the column headers/structure is the same. The actual values in the cells don't matter. Think of Power BI as columns and Excel as cells.

[@bensix6496]

Very helpful thanks - question though by creating the dimension table on the fly, when I have a new country if I don’t refresh my DimCountry first the new Country Index doesn’t flow back in the source data. Is there a way to sequence or am I missing something? Thanks in advance

[@felixfranke2838]

That is exactly my question as well...

[@noneB974]

How would you achieve same results for a model with 2 fact tables where you have to make a common dimension from both to use that common dimension for slicing?

[@cparker4486]

Good explanation and instruction on how to carry this out. Two things: (1) Why is it important to do this? How does it improve performance? It appears the file still has to load the same amount of data. (2) Was it important to move the shipping method into a separate table because it was only one column?

[@user-re5uv2mm2h]

Same question. Hoping he would response. Our team is migrating reports from Tableau to PowerBi. When I create my data source in Tableau, I usually create one table before putting it in Tableau, but now in PowerBI they are telling me to separate the columns first. Is it better to separate the facts and dimension than create the visualization on one table? Would that greatly affect the performance? I imagine I will just need to do that if I will be using two different data source though. Please enlighten us.

[@Transformita]

@@user-re5uv2mm2h Sorry for the delayed response. In summary, you're massively reducing the size of the dataset by splitting your dimensions. Let me explain. Lets say you have one wide table with 1 million rows with each repersenting a sale made. You have the following columns product name | sub-category | location |store name etc. The product name, location etc. is going to be repeated 1 million times as strings (text). .In general with relationtional databases, int type (numbers) are far more compressed then strings. e.g. The product "phone" was sold and appears 300,000 times in your sales table when it's one table. By splitting your product detail into it's own table, you have 3 rows, each repersenting your 3 products - "Phone", "Laptop", "Tablet" and an ID for each. After this has been modelled, your fact table now has the number "1" appear 300,000 times not the word "phone". From a performance perspective this is significantly more compressed Hope that helps.

[@user-of9ku1qw3s]

Brilliant!

[@nidhisingh4973]

Thank you so much this video. Got a good understanding of building fact and dimension tables from raw data. Just want to know, do we create dimension and fact tables directly in power bi in real world project?

[@awosusiomowaare8696]

would the row_ID be in the dimension or in the fact table?

[@JonathanHansen-inmk]

Great demo. I really appreciated your clear explanations of how to create dim tables. I did have a question about grouping by zip and city though. If you remove duplicates on postal code you could potentially lose some cities since there are some postal codes with multiple cities in them. Can you remove duplicates bas on city and postal code?

[@zakariaelaaliji7747]

Thank you, but what if we want to bring data from multiple sources and formats ??1!

[@gagansingh3481]

Could you please make a video on Market Basket Analysis....

[@jamesadam7113]

Thank you for this! I've watched other videos and they've not shown how to create a key / index column and I've been using the oroginal columns as a key. Is there any benefit to using a index column over the original columns with relationships.

[@Transformita]

- It's faster. A JOIN on an integer is much quicker than a JOIN on a string field or combination of fields. It's more efficient to compare integers than strings. 2 - It's simpler. It's much easier to map relations based on a single numeric field than on a combination of other fields of varying data types. 3 - It's data-independent. If you match on the ID you don't need to worry about the relation changing. If you match on a name, what do you do if their name changes (i.e. marriage)? If you match on an address, what if someone moves? 4 - It's more efficient If you cluster on an (auto incrementing) int field, you reduce fragmentation and reduce overall size of the data set. This also simplifies indexes needed to cover your relations.

[@rasmusbendtsen5952]

Thank you for a great video. I have a question. When creating the costumerID and removeing the duplicates, how would you handle it, if two people from different costumer segments, happen to have the same name?

[@raghavverma120]

In the real word scenario.. already there would be a customer table with unique customer id/ email id/ username.. Since it was a flatfile, so it was implied that customers cannot have same name.. else instead of customer name, username would be there

[@raghavverma120]

Else.. just dont delete duplicates.. just use a key to join tables

[@Transformita]

@@raghavverma120 Exactly this.

[@ryanjohnston9642]

Very interesting data model! I'm new to PowerBI and have a follow-up question... When you add the index column to a DIM query, if the source data changes such that a new ZIP Code is added (or a new client), will these index columns not change when you refresh the source data for the query? Will these index values not change and perform as relative-references? I'm concerned that the data will get mixed around when the DIM table merges into the FACT table mid-query. Or am I overthinking this? Will these relative references update safely so that everything merges correctly? Or maybe the issue is that everybody using the fact table needs to understand that Client ID# is a relative reference that can't be relied upon to stay the same from one cycle of reporting to the next.

[@SSRealEstates83]

Hi ...After duplicating the main table is it mandatory to remove the steps till end from applied steps.what will happen if we won't delete that steps from the applied steps .thank you

[@Transformita]

I suggest doing so to improve refresh times. Each applied step has to be computed therefore by not removing it, you are proccessing far more data in your transformations

[@mikesmith-nh1jw]

Great video just one question. Wouldnt it be better to 'reference' the source data or cleaned up copy of the source data rather than 'duplicate' as then you wouldnt have to remove the applied steps later ?

[@DmytriiKutsiianov]

I would really appreciate if someone could help me fix the following issue: When I click the "Close&Apply" button in Power Query Editor after adding the dimnsion tables, all the data gets loaded into Power BI with no errors. However, when I look at my dimension tables in data view in Power BI the order of the ROWS is changed. For example, in Power Query Editor, index 1 in the SensorName_Dim table corresponded to "Engine Speed" and index 2 corresponded to "Coolant Temp". After loading data into power BI the order of the sensor names got messed up so that Engine Speed no longer corresponded to index 1 and Coolant Temp no longer corresponded to index 2 . Does someone know how to fix this issue and preserve the order of rows when loading data into Power BI?

[@felixfranke2838]

Excellently explained, thank you. Question: If in my original data, which I refresh, a new customer shows up, or a new shipping method: How will that be related to the correct line in the facts table? We do not have an index for that. Wouldn't it be better to use data which are in the original source to connect the tables?

[@Transformita]

Thanks. - Because your referencing tables, PBI is dynamic and the new ID's will get populated and reflect in the fact table when it's refreshed. It's not static. - The example in this video is purely to show the concept of a fact and dim table. In the real world these ID'would already be at source where they should be and without a doubt always use those.

[@kalliduspre-sales1799]

Hey, great video Power BI Guy. Right at the end you say you need to create a custom date table. Do you have a video which covers this and explains why you need that?

[@Transformita]

Not yet!, I’ll make one soon 👍🏼

[@cecegaming1119]

Thank you for this demo, but I have a question. When I create a fact table, it causes a lot of duplicates, and I cannot load it into Power BI. It's taking a lot of time. I've tried removing duplicates in the dimension, but the issue persists. What could be the solution? Thank you.

[@user-sp4sf1rd1c]

If I have two datasets, should I create two star Schema? If yes, how can I manage Dim tables and Facts tables from both datasets?

[@S3Kglitches]

best example

[@karmasakal9752]

Is it normal that my row entry number has increased by adding up relationships ? My dataset does not allow me to proceed exactly the way you did. I created unique IDs for all my entries in the dataset and merged the unique IDs with the duplicated unique IDs. Is it possible that the way I did it does not make sense logically and somehow increased my row number ? My data does not allow me to remove duplicates for a certain characteristic or I am mistaken idk. Thanks for the support guys I would be glad to explain my problem if there is some interest on solving the issue.

[@souzaeq]

Question: For the next step, I create a flat custom date for the dates interval I have and use it as DimTable (date, year, quarter, month, day) linking Dimdate to wich date in orders, shipping or order date? I assume Order that comes first, right? These would be the next step you said? Really nice video, thanks for sharing.

[@Transformita]

Are you referring to a custom date table and a relationship between the two. You can create many relationships between the dates. But only one will be active and the others inactive. Look into active and inactive relationships.

[@souzaeq]

@@Transformita many thanks! Do you have videos on that?

[@cesarbarreradeleon4323]

where can i get the data simple ?

[@busta0079]

Thank you. Can you explain relationships in football data please

[@oluwatuyiayomide5385]

Some of the data in the fact table already have unique ID, do we need to start creating unique ID again with index number

[@Transformita]

No you would not need to re-create them. I did so as an example for this video. Relational databases will have these already

[@oluwatuyiayomide5385]

@@Transformita Thank you

[@charanmarc5376]

Thanks Man! Appreciate it !!!

[@ericaleverson9430]

Wouldn't we run into issues using the customer name as a unique column when different customers might have the same name?

[@Transformita]

Yes you would, you would need some sort of unique identifier in that case. Real world databases have identifiers/keys for customers etc.

[@vishalmane3139]

Whats the difference between custom column and calculated column?

[@Transformita]

Custom column is in Power Query, calculated column in in the normal desktop view. Functionality wise they are the same, the former using M in Power Query and DAX in Desktop

[@derbysutter2144]

Hey, I have a pressing question. Can this method be used with multiple fact tables? Or just with 1?

[@Transformita]

yes it can be. This is the ideal, but in the real world you can get multiple fact tables. Guyinthecube have done a vid on this -kzbin.info/www/bejne/ip_cg6aFabN9hsU

[@ztymedina]

@@Transformita Hi can you show details on how to do it? because i've seen the video but it does not show steps by steps.. let say if i have 4 different fact tables, then how i'm supposed to do? Do i need to consolidate all fact tables first, then delete duplicate value to create a dimension table? or how is it exactly?

[@raghavverma120]

Can you please share this flat file

[@eddarelmbuluyo8702]

Great Sir. it's possible to have the file, the dataset? thanks

[@sidharthmandal9957]

Did you get the data set? If yes, can you also send me ?

[@saadfayyaz894]

Love from Pakistan😊

[@ankurarora7632]

Please share the dataset

[@mar25947]

Short n sweet! not 2 hrs long lol

[@vishalmane3139]

Dude if u can have a vfieo on normalize the table that will be good too upto 3nf.. alot of youtube vdieos ending up confusing more.

[@adeelharal7106]

Please share dataset file

[@souzaeq]

I have a fact that is built this way: each event takes a different quantity of days. Like event 1 takes 6 days, so the sale is for the interval of six days. Do I need to break that into daily revenue and cost by dividing the total amount by 6 and then transforming a daily and revenue? In case I need to divide the value to split among the days, wich time dax or transformation in power query should I use like to divide the total revenue by the number of days and then create a line for each recurrence. Like: USD 100/ 4 days, is USD 25/ day, how can I automatically generate starting from current information as 4/jan/2023 (end date) with 100 USD to 1/jan/2023 with USD 25, 2/jan/2023 with USD 25, 3/jan/2023 with USD 25 and 4/jan/2023 with USD 25 ?

[@vladx3539]

what about the date?

[@pravarshakollu7646]

can we create dimensions tables and fact table in power bi?

[@Transformita]

That's what I'm doing in the video. General rule of thumb - Do transformations as close to source/upstream as possible.

[@pravarshakollu7646]

@@Transformita how to identify fact tables and dimensions in our given data

[@vishalmane3139]

creating an index column was necessary ?

[@Transformita]

Yes it’s necessary/recommended. Real world databases will have these and the correct term would be primary, foreign, surrogate keys

[@vishalmane3139]

@@Transformita dont u normalize the tables ?

[@vishalmane3139]

what if we have multiple tables

[@Transformita]

Hey, I'm not sure I'm understanding your question. There's multiple tables in this example.

[@vishalmane3139]

@@Transformita im not able to break a dataset in dim and fact tables. Ive inported many dataset instead of one

[@richardpearcephotography599]

What happens if you have two or more unrelated customers with the same name? John Smith vs John Smith.

[@Transformita]

In a real world scenario you tend to have the data structured from a source system/db that has a unique identifier already. When the two accounts sign up to x organisation, they would get an accountid for instance.

[@user-re5uv2mm2h]

Why didn't you create a DimDate?

[@Transformita]

I forgot in this video if I'm honest 😅 you should always have a seperate date table in Power BI

[@leandro0505]

Where can i get the example file?

[@TheCantonese]

[16:02] Oscar Wong Best practice is 1 to * (dimension table to fact table) this is 100% sure the best way BUT question it's possible to have * to * or * to 1 (between dimension table and the fact table) in a BI star or snowflake model?

[@s.ifreecoachinginstitute0077]

Good

[@nathanmutala2941]

merci, ce video m'a beaucoup aidé.

[@nathanmutala2941]

merci, ce video m'a beaucoup aidé.