Fabric - Sample Notebook scripts for MSDyn365FO

This will be a fairly straightforward blog post covering different ways of copying data from a shortcut delta table into a delta table created automatically via a notebook.

Select statement with a join

%%sql
SELECT 
    party.recid AS PartyId
    ,party.name AS Name
    ,COALESCE(party.namealias, '') AS ShortName
    ,COALESCE(postal.countryregionid, '') AS Country
    ,COALESCE(postal.state, '') AS State
    ,COALESCE(postal.city, '') AS City
    ,COALESCE(postal.street, '') AS Street
    ,COALESCE(postal.zipcode, '') AS PostCode
    ,COALESCE(phone.locator, '') AS PhoneNumber
    ,COALESCE(email.locator, '') AS Email
FROM dirpartytable party 
LEFT OUTER JOIN logisticspostaladdress postal ON postal.location = party.primaryaddresslocation
AND postal.validto > current_date() -- filters only valid(effective) addresses
LEFT OUTER JOIN logisticselectronicaddress phone ON phone.recid = party.primarycontactphone
LEFT OUTER JOIN logisticselectronicaddress email ON email.recid = party.primarycontactemail

You should see a table result showing below your query.

Create table if not exists

This is a one of copy and will not copy data if the table exists already.

%%sql
CREATE TABLE IF NOT EXISTS fact_dirpartytable 
USING DELTA AS 
SELECT 
    party.recid AS PartyId
    ,party.name AS Name
    ,COALESCE(party.namealias, '') AS ShortName
    ,COALESCE(postal.countryregionid, '') AS Country
    ,COALESCE(postal.state, '') AS State
    ,COALESCE(postal.city, '') AS City
    ,COALESCE(postal.street, '') AS Street
    ,COALESCE(postal.zipcode, '') AS PostCode
    ,COALESCE(phone.locator, '') AS PhoneNumber
    ,COALESCE(email.locator, '') AS Email
FROM dirpartytable party 
LEFT OUTER JOIN logisticspostaladdress postal ON postal.location = party.primaryaddresslocation
AND postal.validto > current_date() -- filters only valid(effective) addresses
LEFT OUTER JOIN logisticselectronicaddress phone ON phone.recid = party.primarycontactphone
LEFT OUTER JOIN logisticselectronicaddress email ON email.recid = party.primarycontactemail

Create table if not exists - use merge

This does a copy similar to the above but uses a merge to match the records

%%sql

-- Step 1: Create Delta table
CREATE TABLE IF NOT EXISTS fact3_dirpartytable (
    PartyId LONG,
    Name STRING,
    ShortName STRING,
    Country STRING,
    State STRING,
    City STRING,
    Street STRING,
    PostCode STRING,
    PhoneNumber STRING,
    Email STRING
) USING delta;

-- Step 2: Create temporary view
CREATE OR REPLACE TEMPORARY VIEW temp_dirpartytable AS
SELECT 
    party.recid AS PartyId
    ,party.name AS Name
    ,COALESCE(party.namealias, '') AS ShortName
    ,COALESCE(postal.countryregionid, '') AS Country
    ,COALESCE(postal.state, '') AS State
    ,COALESCE(postal.city, '') AS City
    ,COALESCE(postal.street, '') AS Street
    ,COALESCE(postal.zipcode, '') AS PostCode
    ,COALESCE(phone.locator, '') AS PhoneNumber
    ,COALESCE(email.locator, '') AS Email
FROM dirpartytable party 
LEFT OUTER JOIN logisticspostaladdress postal ON postal.location = party.primaryaddresslocation
AND postal.validto > current_date() -- filters only valid(effective) addresses
LEFT OUTER JOIN logisticselectronicaddress phone ON phone.recid = party.primarycontactphone
LEFT OUTER JOIN logisticselectronicaddress email ON email.recid = party.primarycontactemail;

-- Step 3: Merge into delta table
MERGE INTO fact3_dirpartytable AS target
USING temp_dirpartytable AS source
ON target.PartyId = source.PartyId
WHEN MATCHED THEN
  UPDATE SET target.Name = source.Name, target.ShortName = source.ShortName
WHEN NOT MATCHED THEN
  INSERT (PartyId, Name, ShortName) VALUES (source.PartyId, source.Name, source.ShortName);

This will do an update, insert but will not handle deletes.

Create table, Delete and Insert data

This creates the table, then deletes the data in full and inserts it all again.

%%sql

-- Step 1: Create Delta table
CREATE TABLE IF NOT EXISTS fact4_dirpartytable (
    PartyId LONG,
    Name STRING,
    ShortName STRING,
    Country STRING,
    State STRING,
    City STRING,
    Street STRING,
    PostCode STRING,
    PhoneNumber STRING,
    Email STRING
) USING delta;

-- Step 2: Delete data from the Delta table
DELETE FROM fact4_dirpartytable;

-- Step 3: Create temporary view
INSERT INTO fact4_dirpartytable
SELECT 
  party.recid AS PartyId
  ,party.name AS Name
  ,COALESCE(party.namealias, '') AS ShortName
  ,COALESCE(postal.countryregionid, '') AS Country
  ,COALESCE(postal.state, '') AS State
  ,COALESCE(postal.city, '') AS City
  ,COALESCE(postal.street, '') AS Street
  ,COALESCE(postal.zipcode, '') AS PostCode
  ,COALESCE(phone.locator, '') AS PhoneNumber
  ,COALESCE(email.locator, '') AS Email
FROM dirpartytable party 
LEFT OUTER JOIN logisticspostaladdress postal ON postal.location = party.primaryaddresslocation
AND postal.validto > current_date() -- filters only valid(effective) addresses
LEFT OUTER JOIN logisticselectronicaddress phone ON phone.recid = party.primarycontactphone
LEFT OUTER JOIN logisticselectronicaddress email ON email.recid = party.primarycontactemail;

This runs fast but however not what you may want to do on a regular basis.

Create a temporary view and use SinkModifiedOn

Create a temporary view within your notebook to use as part of complex queries.

This query joins 4 tables together and each table has its own SinkModifiedOn field. I wanted to create a view that gave me the greatest (max) SingModifiedOn date time. This is to later allow me to do an incremental update.

CREATE OR REPLACE TEMPORARY VIEW temp_dirpartytable AS
SELECT 
    party.SinkModifiedOn AS party_SinkModifiedOn,
    postal.SinkModifiedOn AS postal_SinkModifiedOn,
    phone.SinkModifiedOn AS phone_SinkModifiedOn,
    email.SinkModifiedOn AS email_SinkModifiedOn,
    GREATEST(party.SinkModifiedOn, postal.SinkModifiedOn, phone.SinkModifiedOn, email.SinkModifiedOn) AS SinkModifiedOn,
    party.recid AS PartyId
    ,party.name AS Name
    ,COALESCE(party.namealias, '') AS SearchName
    ,COALESCE(postal.countryregionid, '') AS Country
    ,COALESCE(postal.state, '') AS State
    ,COALESCE(postal.city, '') AS City
    ,COALESCE(postal.street, '') AS Street
    ,COALESCE(postal.zipcode, '') AS PostCode
    ,COALESCE(phone.locator, '') AS PhoneNumber
    ,COALESCE(email.locator, '') AS Email
FROM dirpartytable party 
LEFT OUTER JOIN logisticspostaladdress postal ON postal.location = party.primaryaddresslocation
AND postal.validto > current_date() -- filters only valid(effective) addresses
LEFT OUTER JOIN logisticselectronicaddress phone ON phone.recid = party.primarycontactphone
LEFT OUTER JOIN logisticselectronicaddress email ON email.recid = party.primarycontactemail;

SELECT * FROM temp_dirpartytable
WHERE SinkModifiedOn >= '2024-09-03T02:39:16Z';

This would be good for a transactional table where there are no deletes. You get the last SinkModifiedOn date time field for all the related table. Then filter based on the last run you have in your destination table. You could then do incremental updates.

AX - How to use Set and SetEnumerator

The Set class is used for the storage and retrieval of data from a collection in which the values of the elements contained are unique and serve as the key values according to which the data is automatically ordered. You can create a set of primitive data types or complex data types such as a Class, Record or Container. Below is sample of a set of records. static void _Set(Args _args) { CustTable custTable; Set set = new Set(Types::Record); SetEnumerator setEnumerator; ; while select custTable { if (custTable && !set.in(custTable)) { set.add(custTable); } } if (!set.empty()) { setEnumerator = set.getEnumerator(); setEnumerator.reset(); while (setEnumerator.moveNext()) { custTable = setEnumerator.current(); info(strfmt("Customer: %1",custTable.AccountNum)); } } } Common mistake when creating a set of recIds

Dynamics NAVAX

Search This Blog