Best Practices for Power BI modelling
HTML-код
- Опубликовано: 6 окт 2024
- Ok, time for another Power Bi video and this time we are going to talk about Power BI naming conventions and other power bi modelling tips.
First we will talk about what to remove or hide from your model like:
Do not expose in a view a column that is not necessary in the Power BI data model.
TIP: use radacad power bi helper to find out which things are not being used in the model:radacad.com/power-bi-helper
More info about naming conventions here:
docs.microsoft...
And about vertipaq: • What is Vertipaq and h...
Now for the best practices here are some:
Use names that are well-known for the business
It is a best practice to rename all the columns in the views, using exactly the names you will expose in the user interface of Power BI.
You should avoid renaming tables and columns in a Power BI data model.
Try to use single word names on tables if possible.
And when it comes to naming conventions, take these into account:
Avoid all caps (SALESTERRITORY)
Pascal casing (SalesTerritory)
Camel casing (salesTerritory)
Use words separated by space (Sales Territory)
You should remove any prefix and any suffix you might use in table names.
Avoid acronyms
Avoid abbreviations, use complete name when possible. If acronyms/abbreviations are used, they are always used in the same way.
What are your best Power BI modelling tips? Let us know in the comments box!
Looking for a download file? Go to our Download Center: curbal.com/don...
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
Join our DAX Fridays! Series: goo.gl/FtUWUX
Power BI dashboards for beginners: goo.gl/9YzyDP
Power BI Tips & Tricks: goo.gl/H6kUbP
Power Bi and Google Analytics: goo.gl/ZNsY8l
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/...
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Linkedin: goo.gl/3VW6Ky
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
#POWERBITIPS #CURBAL #POWERBI #MVP
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
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
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
My best practice is simply to hire this woman.
Thanks! :)
/Ruth
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!
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 tips, some real eye openers. Also your presentation style is awesome!
Thaaaanks! 😊
/Ruth
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
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
Great idea about short date be date time compression benefits 👍
Excelent Ruth!!!! , thanks a lot
Perfect :)))
Glad some of them helped!
/Ruth
Excellent tips! Thanks a lot!
You welcome Michael :)
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
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
finished watching
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
Grymt. Tack så mycket =)))
Varsågoda!! ;)
/Ruth
Do you have a link to the Radacad team's tool?
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.ruclips.net/video/DiE-STtiRFo/видео.html
/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.ruclips.net/video/pRxQyYEgfrI/видео.html
It is a 3-part series. There is more content on the channel, take a look.
/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 🤫🤫