Clear all

Generate query for the transaction link

Posts: 3
Topic starter
New Member
Joined: 9 months ago

1 good thing of DataVault is that a lot of stuff can be generated. The only difficulty in that I see right now is the problem with links, especially with transaction link. 

We have transaction table I want to load, it has set of the foreign key (int values). Most of the business keys of these values are composite and can be up to 4-5 columns. To be able to get proper hub hash key values, I need to join a lot of tables by these business keys and it gets tricky... 

Is there a way how to handle such situations? I might need to store the PKs (integers) somewhere to help on such tables joins? Or are there any techniques or best practices what should I do?

This is the query I got to generate the data for links:

 INSERT INTO Enterprise_DataVault.dbo.l_gl_journal_detail (gl_journal_detail_hk
                                , load_date
                                , record_source
                                , gl_template_hk
                                 , gl_account_hk
                                , gl_journal_hk
                                , gl_entry_item_hk
                                , lease_hk
                                , gl_journal_detail_id
                                , gl_template_detail_id)
SELECT       hf.hash_value              AS gl_journal_detail_hk
          , SYSDATETIME()              AS load_date
          , 'lw_main_migration'        AS record_source
          , hgt.gl_template_hk         AS gl_template_hk   --
          , hga.gl_account_hk          AS gl_account_hk    --
          , hgj.gl_journal_hk          AS gl_journal_hk
          , hgei.gl_entry_item_hk      AS gl_entry_item_hk --
          , hl.lease_hk                AS lease_hk
          , lgjd.gl_journal_detail_id  AS gl_journal_detail_id
          , lgjd.gl_template_detail_id AS gl_template_detail_id
 FROM       dbo.lw_gl_journal_details                   AS lgjd
 JOIN       source_db.dbo.GLJournalDetails      AS gjd ON gjd.Id = lgjd.gl_journal_detail_id
 JOIN       source_db.dbo.GLJournals            AS gj ON gj.Id = gjd.GLJournalId
 JOIN       source_db.dbo.GLTemplateDetails     AS gtd ON gtd.Id = gjd.GLTemplateDetailId
 JOIN       source_db.dbo.GLTemplates           AS gt ON gt.Id = gtd.GLTemplateId
 JOIN       source_db.dbo.GLTransactionTypes    AS gtt ON gtt.Id = gt.GLTransactionTypeId
 JOIN       source_db.dbo.GLEntryItems          AS gei ON gei.Id = gtd.EntryItemId
 JOIN       source_db.dbo.GLAccounts            AS ga ON ga.Id = gjd.GLAccountId
 JOIN       source_db.dbo.Contracts             AS c ON c.Id = gjd.EntityId
 JOIN       Enterprise_DataVault.dbo.h_gl_template   AS hgt ON hgt.gl_template_name = gt.Name
                                                              AND hgt.gl_transaction_type = gtt.Name
 JOIN       Enterprise_DataVault.dbo.h_gl_account    AS hga ON hga.gl_account_number = LEFT(ga.Name4)
 JOIN       Enterprise_DataVault.dbo.h_gl_journal    AS hgj ON hgj.gl_journal_id = gj.Id
 JOIN       Enterprise_DataVault.dbo.h_gl_entry_item AS hgei ON hgei.gl_transaction_type = gtt.Name
                                                               AND hgei.gl_system_defined_book_code = gei.GLSystemDefinedBook
                                                               AND hgei.gl_entry_item_name = gei.Name
                                                               AND hgei.gl_entry_item_is_debit = gei.IsDebit
 JOIN       Enterprise_DataVault.dbo.h_lease         AS hl ON hl.lease_number = c.SequenceNumber
CROSS APPLY dbo.hashing_function(
                        TRIM(CAST(hgt.gl_template_name AS NVARCHAR(MAX)))
                      , ';'
                      , TRIM(CAST(hgei.gl_transaction_type AS NVARCHAR(MAX)))
                      , ';'
                      , TRIM(CAST(hga.gl_account_number AS NVARCHAR(MAX)))
                      , ';'
                      , TRIM(CAST(hgj.gl_journal_id AS NVARCHAR(MAX)))
                      , ';'
                      , TRIM(CAST(hgei.gl_transaction_type AS NVARCHAR(MAX)))
                      , ';'
                      , TRIM(CAST(hgei.gl_system_defined_book_code AS NVARCHAR(MAX)))
                      , ';'
                      , TRIM(CAST(hgei.gl_entry_item_name AS NVARCHAR(MAX)))
                      , ';'
                      , TRIM(CAST(hgei.gl_entry_item_is_debit AS NVARCHAR(MAX)))
                      , '')))                           AS hf;
2 Replies