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

You always need to add your measures

Same

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.

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.

@@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.

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! 🙂

Thank you. Glad it helped!

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.

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

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.

That is exactly my question as well...

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.

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

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.

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 -kzfaq.info/get/bejne/itSphdaAmbSugJc.html

@@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?

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

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

@@raghavverma120 Exactly this.

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

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

- 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.

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

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

@@Transformita Thank you

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

@@Transformita dont u normalize the tables ?

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

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

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

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.

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

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

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