When you are creating a Power BI Data Model, How do you name the elements?
My vision is to follow good practices and use your common sense (being aware as the well-known conventions agreements, KISS). I’ve made my own choices and my own guide where I clear up on a list where I name the elements (tables, columns, measures, calculation groups, field parameters, hierarchies, groups, and so on).
As a matter on fact I’m keen on thinking about how to improve my work and also improve the work of the several teams (Keywords: productivity, high performance and, self-development), and shape this dynamic in self-service strategies (Keywords: Communications, transference y audit).
What does the concept of a team mean to me? Your team colleagues, the group of people that work for the same company and on the same project, and people that directly or indirectly work for the client and interact as developers, IT team members, Governance team members and auditors.
Language
Define the language to be used in the documentation from this perspective. In previous posts, you may have noticed my preference for documenting nomenclatures in English. However, it depends on several factors, including the client company's culture, whether I am working for a corporate area or a local team (depending on the country), data governance policies and procedures, and specific business requirements. Based on these factors, the decision on which language to use is made by either myself or the team. The exception to the English rule is based on the fact that we are involving tables and columns, and it's more sensitive, IMHO.
If I had to think about it from a development perspective, I would do it all in English, but we should think in business people than in software engineers.
Before we continue, I have a few questions for you
I’ve some questions for you before to start naming elements:
Do you use prefix or subfix? I prefer prefixes, I feel it’s more easy to use.
Which conventions do you use? I prefer [type][name] or [prefix][name].
Which case type do you usually use?
flatcase: myvariable
camelCase: myVariable 👈 My preference for tables.
PascalCase: MyVariable 👈 My preference for columns.
snake_case: my_variable
kebab-case: my-variable
Do you use a blank space or an underscore sign to separate prefixes and words? I prefer underscore (_).
Do you name the tables in the plural or singular way? I feel confuse sometimes. I don’t know yet which is the right way, but I know that I prefer to choose only one way, whatever.
Naming
1. Tables
Table names should have clear meanings and be easily comprehensible. Use meaningful, clear, and concise table and column names (Kimball best practices).
1.1. Dimension tables
Prefixes: d, dim, Dim or DIM.
Name: I try to use something short: Material, Calendar, Client, Vendor, Product
e.g. dProduct, dCalendar
1.2. Related to ‘Dimension Tables’, we need to consider :
grouping attributes by Hierarchies:
[Dimension Name without ‘d’] + [_Hierarchy] → e.g. Product_Hierarchy
Configuring groups.
1.3. Fact tables
Prefixes: f, fact, FCT or facts.
Name: I try to use something short: Sales, Budget, SalesBudget
e.g. fSales, fSalesBudget
1.3. Stage, Staging or intermediate tables
Regarding ETL processes, it's common to use intermediate tables or queries. These are typically utilized in dataflow or other ETL systems that are separate from Power BI Desktop, as recommended by best practices. On the other hand, developers use staging tables to create final tables (denoted by "d" or "f") using DAX. For example, one could import a staging table through Power Query in Power BI Desktop and then create a new table with DAX. With regards to intermediate tables, what naming convention do you typically use?
Prefixes: st, STG, stg, sys or Sys
Name: I try to use something short: SalesBudget (to append in ‘fSales’ e.g.).
e.g. stSalesBudget
1.4. Numeric range and Field parameters
Prefixes: p, P, param, Param or pm. My preference is p.
Name: I try to use something short, but I find it hard to decide many times, especially if I create different combinations.
e.g. pDimensions, pMeasures, pPctSales.
2. Measures and Calculation groups
2.1 Measures
Prefixes: without prefix, numbers or underscore (_). At the date of publication or review of this post, I do not have enough technical information about it, I have seen everything and I prefer nothing (without prefix). 💡Ideas?
2.2. Calculation groups
Prefixes: cg. This is my only option today.
Name: I try to use something short, like TimeIntelligence (is not my idea, I really don’t know that much about the topic, so, usually copy and paste from colleagues).
e.g. cgTimeIntelligence
2.3. Grouping in folder
I feel the same as with measures. I try to organize using common sense. 💡Ideas?
3. Dataset .pbix file name
Prefixes: I don’t use prefixes from conventions, yet, unless you have ideas to me.
Name: I try to use common sense. During the last time I’ve been using the following convention (my own):
[company name]_[type]_DATA → e.g. Contoso_Finance_DATA, Contoso_FI_DATA
[country subsidiary]_[type]_DATA → e.g. ES_FI_DATA
[type]_DATA → e.g. Operations_DATA, Sales_DATA
Conclusions 🚀
I believe in conventions to create tables, columns and other things. So, open a new tab in your browser (Google, ChatGPT, BingChat) and learn more about it. I did it. So IMHO if you use f or Fact, d or Dim, your team members will understand (at least, as a developer) that you are referring to a Fact or Dim Table, therefore, use some prefix that is within the convention.
I believe in common sense, but as you know is too much subjective. I guess, everything is in dimensional modeling conventions, however, if not, use common sense or ask your colleagues.
Try to use always the same conventions, at least, on the same project. Convention is like a language, What do you think about that?
Standardize your way of documenting facilitate teamwork, inheritance of the code and auditability of the modeling process, your work indeed. This is for you and your colleagues, be kind, everybody happy.
Although it is impossible to cover every possibility, I want to share with you the common options based on conventions, my own experience, and what I learned from my colleagues.
I would like to receive your comments, ideas or related practices to share and work better. And if this post has helped you, it makes me happy 😁.
Cheers 🤟
Matias