Our sales representatives call Leads to propose services. Leads are stored in Crm with attributes: LeadId, PrimaryContactNumber, SecondaryContactNumber. All calls are done via Teams and recorded. Calls could be extracted via Microsoft Graph Api and have attributes: CallId, UserId, CalleeNumber, CallerNumber, Duration. Users have these attributes: UserId, Username, Email. How can I generate LINK (relation between User, Lead) using data vault modeling when foreign keys are not known? I thought my design should look like this: User(hub)-Call(link)-Lead(hub), but my call has only userId, leadId could only be inferred from one of the Lead attributes(PrimaryContactNumber or SecondaryContactNumber) What is the best solution to this problem? Or should I model Call as a hub aswell and perform filter when loading data to datamart?
How to create the LINK in data vault without having strong relationship keys (Foreign Keys)?
884 Views Asked by DmitriBodiu At
1
There are 1 best solutions below
Related Questions in DATA-MODELING
- Does Power BI provide an OOTB Geographical Dimension Table
- Table Restructuring Options in PowerBI
- How to model such that a drill through for Order suppliers having M:1 relation with Order fact table can be configured?
- Excel formula to change sum field depending on date
- How to properly define data models in TypeScript?
- Power Bi - Modelling
- How to create a MySQL table or store the values in the MySQL table if values is of type python list
- Dynamo DB M:N relationship issue
- What data type is appropriate for star schema keys (integer or text)?
- How to fetch data from this Nested JSON using GET method in flutter using Data Model?
- How to model composite primary keys in the ER model?
- LSTM layer in Sequential model requires 3d input but only receives d2
- How can I generate many random community matrices and then apply a function to all of them?
- Model the number of available spots in dimension or fact table?
- How to get data collection from a nested list of a data collection from postman api to list<dynamic> in dart Flutter
Related Questions in DATA-WAREHOUSE
- How to blind data in data warehouse when sending from preanonymized layer to anonymized layer while keeping referential integrity of all key columns
- Run Pyspark job using Matillion
- the right grain of a fact table in data warehouse
- Model the number of available spots in dimension or fact table?
- What is the most efficient way to generate a change data set given two SQL Server backup files?
- Does it make sense to use an IDENTITY column in a raw layer?
- Power BI star (constellation) schema: 2 fact tables with relation between each other
- Rolling Period Table or CTE
- Representation of sequential rules in data mining (sequence pattern mining)
- Snowflake Bulk Inserts vs. Single Row Inserts
- Data not showing in power bi report
- Data Warehouse to Power BI Desktop
- Error initialize process greenplum major upgrade 5.29.12 to 6.25.2 on centos 7
- Modify column type in Parquet file with ruby (using parquet Gem)
- Translating Snowflake warehouse usage to BigQuery
Related Questions in DATA-VAULT
- How to properly partition by hashkey in spark (to achieve co-partitioning)?
- Data vault 2.0 hub end date
- Data Vault Modelling Hub and Link
- Adding Load date in real time from message queue DV2.0
- In DV2.0 how to change a business key?
- Datavault modeling where source data is slightly heterogenous
- Absence of temporality to Links in Data Vault 2.0
- Data Vault: Relationship between LINK and SAT on Historical analysis using SQL
- Add Valid_From & Valid_To Columns to Table w/ only load_timestamp - where relationships can revert back
- Data Vault 2.0: Creating an Effectivity Satellite off link table that only has insert_date for each record
- How can I specify a table name in the DBT merge process?
- Generate Metric on how frequently an individual table attribute changes , ideally relative to another
- Is Data Vault modelling appropiate for small teams?
- How can I specify what snowflake warehouse to use in dbtvault?
- If a payment contains 1-1 mapping with transfer and each transfer contains 1-many mapping with attempts, how should I model my data vault
Trending Questions
- UIImageView Frame Doesn't Reflect Constraints
- Is it possible to use adb commands to click on a view by finding its ID?
- How to create a new web character symbol recognizable by html/javascript?
- Why isn't my CSS3 animation smooth in Google Chrome (but very smooth on other browsers)?
- Heap Gives Page Fault
- Connect ffmpeg to Visual Studio 2008
- Both Object- and ValueAnimator jumps when Duration is set above API LvL 24
- How to avoid default initialization of objects in std::vector?
- second argument of the command line arguments in a format other than char** argv or char* argv[]
- How to improve efficiency of algorithm which generates next lexicographic permutation?
- Navigating to the another actvity app getting crash in android
- How to read the particular message format in android and store in sqlite database?
- Resetting inventory status after order is cancelled
- Efficiently compute powers of X in SSE/AVX
- Insert into an external database using ajax and php : POST 500 (Internal Server Error)
Popular Questions
- How do I undo the most recent local commits in Git?
- How can I remove a specific item from an array in JavaScript?
- How do I delete a Git branch locally and remotely?
- Find all files containing a specific text (string) on Linux?
- How do I revert a Git repository to a previous commit?
- How do I create an HTML button that acts like a link?
- How do I check out a remote Git branch?
- How do I force "git pull" to overwrite local files?
- How do I list all files of a directory?
- How to check whether a string contains a substring in JavaScript?
- How do I redirect to another webpage?
- How can I iterate over rows in a Pandas DataFrame?
- How do I convert a String to an int in Java?
- Does Python have a string 'contains' substring method?
- How do I check if a string contains a specific word?
A call is not a business entities, it's rather a relation between a sales person and a lead. So, your first thought is right.
To answers your question, you might want take a look at the zero/ghost records concept. http://roelantvos.com/blog/unknown-keys-zero-keys-or-ghost-keys-in-hubs-for-dv2-0/
Basically, add a "Unknown" entry in your Lead(Hub). Then, when you generate the link and the Lead is not known, simply link it to the unknown entities. Make sure you add a satellite to you link, so you can track the period it was unknown, and when it is known.