Source Queries best practice naming

[Anonymous]

This probably goes into some Kimball\Inmon theories, but I am curious as to what people's thoughts are on naming columns in the stored procedures or views to match what you want them to be in the model. 

Prior to Power BI I used SSAS and I would create a layer of stored procedures for prepping the data for loading. Once I started reusing those sprocs in other models I realized it was more efficient to name the columns as I want them to appear in the models.

Otherwise I might have to rename 100's of columns in each model. And also risk not naming them the same, or accidentally putting in an extra space or something. 

With Power BI, It just seems to make even more sense to push that governance down to the layer that the developers have control over before end users start using a base model to create reports and come up with their own whacky naming conventions. 

If anyone has a Best practices or standards doc that they found useful and are willing to share, I would greatly appreciate it. I assume others would also. Even if they don't agree with all of the standards, it is nice to have a starting point.

[Anonymous · Accepted solution]

Hi @Anonymous  -  @jdbuchanan71 has some good thoughts and link.

I don't have a document, just some thoughts.

1. I agree that the fewer changes required, the better.  Reasons:
   1. Less maintenance / development time spent on tedious tasks.
   2. Consistent naming gives clearer insight in terms of lineage.
   3. Consistent naming makes documentation simpler to maintain and understand.
2. Distinguish between columns that will be used by end users and hidden columns.
   1. If columns will be hidden from end users, then it is less important to prep the names.
   2. Attribute columns are generally the only columns that will be displayed to end users.
   3. Value columns are generally hidden and Measures are used instead. So, you're better off NOT naming the column in a way that will be presented to end users. Consider adding some indication to the name, such as "RAW". 
3. Additionally, here are some best practices for Power BI development.
   1. Consider using Dataflows - this allows you to develop in Power Query (in the PBI Service) once and share resulting Tables across Datasets.
   2. Consider using Shared Datasets - this allows you to develop in Power BI Desktop once and share resulting Datasets across Reports.
      1. You can Promote / Certify datasets to let developers know which ones are "official".

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Anonymous]

Thanks for the tips and the links from @jdbuchanan71.

On a side note. I know I need to start working with those dataflows, and I did once a month or so back. But I got caught up trying to map to a CDM and it jsut didn't fit my data. So I stepped back to discuss with another developer I work with. They said they didn't map their dataflow to CDM fields and that was OK. I work with HealthCare data and those CDMs jsut don't fit our data well. But I am looking forward to trying again, so I can use the ML\AI features that dataflows can take advantage of.

[Anonymous]

@Anonymous  - It's true that MS keeps bringing new features, like AutoML, which strengthen the entire ecosystem. 

But primarily just think of dataflows as PowerQuery in the cloud, with the advantage of re-use - I'd previously refered to PBI only, but you can also use them as training set for ML.

[jdbuchanan71]

Hello @Anonymous 

Take a look at this article from the SQLBI team.  Using views in SQL with the names set how you want them in the model has worked well for me and avoids confusion when working with the DBA's if a change needs to happen.  

https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/

One other thing I do is, when adding a calculated table or column I will prefix the name with ct or cc: "ctAgingBuckets", "ccAgingDays".  That way I know at a glance that an item is not coming from the view but from inside the model.