Also; - create hierarchies wherever suitable - only create relationships if you're actually using them (each relationship also uses memory) - as a general rule, just hide all fact tables. if they're a fact attribute you want to filter/slice by.. create a separate table for those. - even though you're naming stuff using natural business terms, you still need to be consistent across the model. e.g. is it Sales Amount Total, or Total Sales Amount? ... The most important thing is that a user needs to know where to find something and what it means - without needing you to explain it.
Thank you Ruth: I will give it a try to add some: 1: use separate tables for measures, see Sam McKay's video's. Enterprise DNA. If you have many... 2: In relationship view: Fact tables below, dimension on top, see book of Collie and Singh. (filters flow down, mostly...) 3 I use underscore and no spaces in names, this might avoid the quotes... 4: document (with remarks in measures) Question: is there a way to list all you measures in the model? Name and formula?
Excellent tips. Since you've asked for more here's one: I've already been able to change the column or measure name only in the graphics interface (in the values, legebd, or columns field). So, I have been able to give more friendly names to the measures and columns in the graphs at the time of formatting, without having to change the data model.
Thanks for sharing your videos, I learn from the tips and your practices. Keep making videos like this. By the way, as a football fan, I'll follow the results of the games for your dashboard in Power BI, that's fun.
Great video. I have some: use 1. //comment on measures after them. 2. Create an excel file with all measures and their descriptions (using dax studio/manual. 3. Make dates work (format as date table, sort by columns, hide other date columns), 4. Synonyms/linguistic schéma 5. Data viz tricks: edit interactions, alignment, report tooltip, check colour scheme.
Hi Ruth, Nice video, we do need to remember the end user. also I agree with Bart, on having a separate measure table. I've started to build my own tables for grouping name and there sorting order. It works for me Cheers Mathew
Nice series of videos. I agree with you on the business friendly names. Having said that, I would give a prefix "d_" and "f_" to dimension and fact tables respectively. The reason is simple, have all the "Dimension" and "Fact" tables being grouped together under "Field". As all tables will be sorted in ascending order. Having the prefix would "force" all dimension tables together. Also, I will create tables for measures (instead of having them sitting on everywhere). WHen this is in place, I could provide hide all fact tables. No confusion to users. Yeah! 😉
I have tables get sorted alphabetical (users don't know what a fact or dimension table is and they don't care either) and I NEVER use measure tables. I think that is a terrible practice. Measures should be stored where they "belong". As you see, the exact opposite!! 😂😂
@@CurbalEN yes it’s totally opposite. But I really like the idea of measures tables. Indeed i group measures into different tables for better experience
Hi Ruth. Great tips! What are your thoughts on connecting Power BI directly in an OLTP database? I'm a DBA, and now have to explain why that's not a good idea (at least, it seems like that to me). Do you have a video where you cover that kind of subject? Thanks!
Hi Fabricio! No, but I will soon ;) In the meantime, check this resource out (the challenges part), in my opinion the same challenges apply to power bi: docs.microsoft.com/en-us/azure/architecture/data-guide/relational-data/online-transaction-processing /Ruth
Dear Ruth, Once again a very useful great video! I would recommend not to use upper case in each word until there is no acronym. Sales territory but not Sales Territory if ST is not an acronym Cheers
I mean there is no use to put some letters in uppercase within a sentence/title if an acronym doesn't exist So, "United States of America" is ok but "United States Of America"(o is upper case) is not ok I would write "Sales territory" and not "Sales Territory" But it is my perfectionist side ;)
Ok, Fully not related to the topic. Where can I start practicing visualizations using PowerBI? I pretty much know the basic functionality(so not loooking for that).
Nice video! Great tips! Iam putting also -coments to my measures if needed and as well descritions to field descriptions. Also using X formulas instead of creating columns is good practice fot performannce. Putting majority of keys as texts eventhoug theirnare numbers. Creating seperate tables for measures. Also putting tables in diagram view always to star / snowflake schema.
Also;
- create hierarchies wherever suitable
- only create relationships if you're actually using them (each relationship also uses memory)
- as a general rule, just hide all fact tables. if they're a fact attribute you want to filter/slice by.. create a separate table for those.
- even though you're naming stuff using natural business terms, you still need to be consistent across the model. e.g. is it Sales Amount Total, or Total Sales Amount? ... The most important thing is that a user needs to know where to find something and what it means - without needing you to explain it.
Thanks for sharing, I need to make a new video :)
/Ruth
Ha!
Thank you Ruth: I will give it a try to add some:
1: use separate tables for measures, see Sam McKay's video's. Enterprise DNA. If you have many...
2: In relationship view: Fact tables below, dimension on top, see book of Collie and Singh. (filters flow down, mostly...)
3 I use underscore and no spaces in names, this might avoid the quotes...
4: document (with remarks in measures)
Question: is there a way to list all you measures in the model? Name and formula?
^^^ #1, #2
#3 is particularly pertinent specialy because the quotes in code. But with space is more visually appealing.
Thanks Jose:)
/Ruth
Thanks Jose:)
/Ruth
You can list measures using dmv. Google for pbi measure dependencies topics
Great tips, some real eye openers. Also your presentation style is awesome!
Thaaaanks! 😊
/Ruth
Excellent tips. Since you've asked for more here's one: I've already been able to change the column or measure name only in the graphics interface (in the values, legebd, or columns field). So, I have been able to give more friendly names to the measures and columns in the graphs at the time of formatting, without having to change the data model.
Thanks for sharing, I need to make a new video :)
/Ruth
Thanks for sharing your videos, I learn from the tips and your practices. Keep making videos like this. By the way, as a football fan, I'll follow the results of the games for your dashboard in Power BI, that's fun.
Then I hope it will work when I am ready! 😂😂😂
/Ruth
Great video. I have some: use 1. //comment on measures after them. 2. Create an excel file with all measures and their descriptions (using dax studio/manual. 3. Make dates work (format as date table, sort by columns, hide other date columns), 4. Synonyms/linguistic schéma 5. Data viz tricks: edit interactions, alignment, report tooltip, check colour scheme.
Fantastic!! Thanks :)))
Happy Friday!
/Ruth
Thanks Ruth for your great tips. ofcourse business term name in the model will help the natural query much better.
Exactly I forgot to say that!
/Ruth
Gracias Ruth, excelentes comentarios sobre como entregar algo muy entendible y de fácil uso a los consumidores de nuestros modelos!
Gracias Norberto! Cuáles son tus trucos? Estoy segura de que tienes muchos :)
/Ruth
My best practice is simply to hire this woman.
Thanks! :)
/Ruth
Do you have a link to the Radacad team's tool?
Hi Ruth,
Nice video, we do need to remember the end user. also I agree with Bart, on having a separate measure table.
I've started to build my own tables for grouping name and there sorting order. It works for me
Cheers
Mathew
I never use separate tables for measures. I think it is a better Experience to put the measures where they “belong”.
/Ruth
Nice series of videos.
I agree with you on the business friendly names. Having said that, I would give a prefix "d_" and "f_" to dimension and fact tables respectively. The reason is simple, have all the "Dimension" and "Fact" tables being grouped together under "Field". As all tables will be sorted in ascending order. Having the prefix would "force" all dimension tables together.
Also, I will create tables for measures (instead of having them sitting on everywhere). WHen this is in place, I could provide hide all fact tables. No confusion to users. Yeah! 😉
I have tables get sorted alphabetical (users don't know what a fact or dimension table is and they don't care either) and I NEVER use measure tables. I think that is a terrible practice. Measures should be stored where they "belong".
As you see, the exact opposite!! 😂😂
@@CurbalEN yes it’s totally opposite. But I really like the idea of measures tables. Indeed i group measures into different tables for better experience
Excelentes recomendaciones. Gracias Ruth !!!
Gracias Jonathan!!
/Ruth
Excelent Ruth!!!! , thanks a lot
Perfect :)))
Glad some of them helped!
/Ruth
Excellent tips! Thanks a lot!
You welcome Michael :)
Great Ruth,
You are bunch of information, thanks!!!!
//Nawaz
😂😂😂 I have all kinds of facts to share ;)
/Ruth
Really you have!!
keep sharing :-) :-)
//Nawaz
:)
/Euth
Great idea about short date be date time compression benefits 👍
Hi Ruth. Great tips!
What are your thoughts on connecting Power BI directly in an OLTP database? I'm a DBA, and now have to explain why that's not a good idea (at least, it seems like that to me). Do you have a video where you cover that kind of subject?
Thanks!
Hi Fabricio!
No, but I will soon ;)
In the meantime, check this resource out (the challenges part), in my opinion the same challenges apply to power bi:
docs.microsoft.com/en-us/azure/architecture/data-guide/relational-data/online-transaction-processing
/Ruth
@@CurbalEN Thank you so much for taking the time to answer me. I really appreciate it. I'm really looking forward to this video.
It is on my list, I will definable do a vid on that ;)
/Ruth
Hello, working through you data modeling playlist. Random question: what was the inspiration for the name 'Curbal'?
Hi,
Here is the explanation!
m.th-cam.com/video/DiE-STtiRFo/w-d-xo.html
/Ruth
Grymt. Tack så mycket =)))
Varsågoda!! ;)
/Ruth
finished watching
Dear Ruth,
Once again a very useful great video!
I would recommend not to use upper case in each word until there is no acronym.
Sales territory but not Sales Territory if ST is not an acronym
Cheers
Just curious, why not?
I would say no to acronyms ;)
/Ruth
I mean there is no use to put some letters in uppercase within a sentence/title if an acronym doesn't exist
So, "United States of America" is ok but "United States Of America"(o is upper case) is not ok
I would write "Sales territory" and not "Sales Territory"
But it is my perfectionist side ;)
Oh got you, I though it was a technical reason, but I get your humanistic reason and I agree with you :)
Thanks for sharing!
/Ruth
Ok, Fully not related to the topic. Where can I start practicing visualizations using PowerBI? I pretty much know the basic functionality(so not loooking for that).
Maybe this will help?
m.th-cam.com/video/pRxQyYEgfrI/w-d-xo.html
It is a 3-part series. There is more content on the channel, take a look.
/Ruth
Nice video! Great tips! Iam putting also -coments to my measures if needed and as well descritions to field descriptions.
Also using X formulas instead of creating columns is good practice fot performannce.
Putting majority of keys as texts eventhoug theirnare numbers.
Creating seperate tables for measures.
Also putting tables in diagram view always to star / snowflake schema.
Thanks for sharing, I need to make a new video :)
/Ruth
But I love how camelCasing looks :(
I agree however with business-friendly names. But you will pry my camelCased's VAR's in measures from my cold, dead hands! ;)
@@alfredsfutterkiste7534 you should! Almost nobobody will see them 🤫🤫