1

In my project I am using Data Vault 2.0 modelling. Now I wanted to build a dimension modelling from the Data Vault model (as per client request). Also we have CDC logic implemented in Data Vault and same has to be implemented in new dimension modeling (Dimension & Fact tables). How will I build a DIM & FACT table from Data Vault model (hub, link, sat, msat)? What all columns go into DIM & FACT tables? Please share your thoughts on this.

Jomy
  • 87
  • 1
  • 13
  • 1
    So you need to design your facts and dimensions, and then map your source data (your data vault) into your dimensional model – NickW Mar 16 '22 at 21:31
  • Hi @NickW, Yes. I have a data model built using DATA VAULT. Now i need to create another layer (DIMENSION Modelling) with Dim & Facts for which the source will the Data Vault tables (Hub, Link & Satellite tables). In SAT tables, CDC logics are also included. For CDC logic except PK & ETL Columns, all other columns are used. – Jomy Mar 17 '22 at 04:00
  • I may have missed the point of your comment as it doesn’t seem to relate to my comment or add anything significant to your original question? – NickW Mar 17 '22 at 07:49
  • hi @NickW - Answer to you comment is YES. I just elaborated my need. :) – Jomy Mar 17 '22 at 11:06
  • OK - but that doesn't seem to change my original comment/answer. What else are you looking for, apart from what I've already said? – NickW Mar 17 '22 at 11:36
  • I am faced with a pretty similar challenge at the moment. I think the question is what would be the best practice / most common approach to transform a DV2.0 model into a dimensional model in a very general way. Without any further reading I attended to transform my Hubs directly into Facts and my Links (and some Lookups) to Dimensions ... I didn't do much aggr. and came up with a model which does the job, but it has flaws. After some reading I came to the conclusion, that it would be better to transform Links to Facts (including aggregations) while Hubs form Dims ... happy/open to discussion – Jürgen Zornig May 10 '22 at 07:29
  • 2
    Presumably your DV model is based (very roughly) on your source data - whereas your dimensional model should be based on your reporting requirements. Basing your dimensional model on the structure of your DV model is highly likely to cause you issues in the future – NickW May 10 '22 at 11:33
  • Ok, as our "analysts" are in fact the technicians/product managers who also have a very "technical" view on their reporting requirements (want to know counts of certain Entities) I assume thats the major reason why the fact tables are very similar to the Entities we have stored in our DV model and not because of false assumptions made in the transformation process. Thanks, I appreciate your input on this very much – Jürgen Zornig May 10 '22 at 14:26

0 Answers0