Semantic layer - translate technical names to functional names

[PowerRon]

Hai

let me explain our situation. We have a data warehouse, which will be migrated to SQL Server (SQL DB or DWH) in the cloud.
Front-end will be Power BI.
Our data warehouse, star scheme, has a lot of fact tables and dimensions.
Dimensions can be used combined with multiple fact-tables (conformed dimensions).
The data warehouse is divided into different area's of interest. To simplify the situation: we have two area's, one focusses on payment transactions, the other focusses on savings transactions. We have multiple user groups, that focus on only (a part of) payment transactions or only (a part of) savings or both.

When I import or directquery the tables in Power BI, I get technical names. I would like to replace them with functional names, understandable for the end-user.
For speed of development and easy maintenance, I don't want to translate a dimension or fact table over and over again in multiple datasets.
Is the only possible solution then making a dataflow for every table I use in the front-end, do the translation over there and embed this dataflow table in a dataset? So making almost a one-on-one copy of all my tables in the data warehouse??

Regards 
Ron

[Anonymous]

@PowerRon  I agree, whether its a database or DW I always use a layer of views between my tables and the reports. Makes handling these types of changes and structural table changes easy to deal with.

[PowerRon]

Hai @Anonymous , 

What does this mean (looking for our best way of working in the near future) ?

Do you build a view for every single table, in which you translate the technical names in logical names? And maybe do some other minor transformations? And are these views then input for a shared dataset?

Or are you also buidling views of a whole starschema, whereby you flatten everything in one denormalized datamart, which is input for a dataset?

Regards

Ron

Regards Ron

[Anonymous · Accepted solution]

@PowerRon Here are my thoughts and the caveat being I'm not a guy who has spent a ton of time building massive DWs over a long period of time.
If you have technical names in the DW, and you have to translate them anyway... why not use a view instead of burying that into your report layer? Then its closer to the source info, and you have an easier time forming that "query" for Power BI or any reporting tool. 

Whether its a live database or DW I've almost always added that layer into the database as it is easier to manage and gives me more flexibility to change things in the data without effecting the downstream reports. This also protects against changes of the table schemas and doesn't create report dependancies on those.

The two ways you describe are both applicable in my opinion. Filtering for particular areas of business off the main tables to create smaller datamarts or subsets of info for different models is a good use-case. As well as creating the versions of tables that I'm going to use / re-use in one or many business tools. Saves all the work of doing it multiple times for each use case.

[PowerRon]

thnx @Anonymous for the explanation

Regards

Ron

[lbendlin]

If you have a semantic problem in your data warehouse you need to solve it in your data warehouse. Trying to do this in Power BI is just asking for trouble.