cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Semantic layer - translate technical names to functional names

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Semantic layer - translate technical names to functional names

@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.

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

5 REPLIES 5
Highlighted
Community Champion
Community Champion

Re: Semantic layer - translate technical names to functional names

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.

Highlighted
Super User III
Super User III

Re: Semantic layer - translate technical names to functional names

@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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Highlighted
Helper I
Helper I

Re: Semantic layer - translate technical names to functional names

Hai @Seth_C_Bauer , 

 

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

Highlighted
Super User III
Super User III

Re: Semantic layer - translate technical names to functional names

@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.

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

Highlighted
Helper I
Helper I

Re: Semantic layer - translate technical names to functional names

thnx @Seth_C_Bauer for the explanation

 

Regards

Ron

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors