Forum Discussion
Semantic layer - translate technical names to functional names
Anonymous 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.
Anonymous 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.
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 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.
thnx Anonymous for the explanation
Regards
Ron