tag:blogger.com,1999:blog-76568882024-03-05T20:38:30.130-07:00Aggregated IntelligenceAI - Aggregation of ideasRaj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.comBlogger1852125tag:blogger.com,1999:blog-7656888.post-15764981603115230682024-01-18T18:00:00.002-07:002024-01-18T18:00:36.798-07:00AWS - MWAA - Customize UI Title<p>If you would like to customize the Airflow UI title to include some additional information, you can do so in MWAA by setting webserver.instance_name </p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEiH0jloKDbwhhZE45L2UAXMaYbKxcABpulS8TK5tePKM2t8ZDrNJk97e-jOk-_W5dHeFV5Cn02cWvepHb_MZ8TdWXiszUhM_7ZCi1W4PhEsjaiYEVWCz-NqHRNmhRNHDs3ukwHtta4RC6l_s3ZsNai_ASPicuiSJx0FuK_wPeP1a8qo63YIcjjKxw" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="337" data-original-width="680" height="159" src="https://blogger.googleusercontent.com/img/a/AVvXsEiH0jloKDbwhhZE45L2UAXMaYbKxcABpulS8TK5tePKM2t8ZDrNJk97e-jOk-_W5dHeFV5Cn02cWvepHb_MZ8TdWXiszUhM_7ZCi1W4PhEsjaiYEVWCz-NqHRNmhRNHDs3ukwHtta4RC6l_s3ZsNai_ASPicuiSJx0FuK_wPeP1a8qo63YIcjjKxw" width="320" /></a></div><br />This will update the Title bar to show the value you set as shown below:<p></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhQUdJ1bzHUKz9-Xm8ujRypf-HJ0hvGt8fID0CDjt8L1P-02DnHo0RweV_GlU7C67k_mfQ3VaT90Cxxi-uGZ04wqXkeSwdEav5Y3pYkfRflSFdaA_ZBHVYWq7T8Xy3w7t51XbUUNo_MBjwzGwXNYlABcqr10Xt9fydJjqHQtTqIp838RToHk9yRNA" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="31" data-original-width="146" height="44" src="https://blogger.googleusercontent.com/img/a/AVvXsEhQUdJ1bzHUKz9-Xm8ujRypf-HJ0hvGt8fID0CDjt8L1P-02DnHo0RweV_GlU7C67k_mfQ3VaT90Cxxi-uGZ04wqXkeSwdEav5Y3pYkfRflSFdaA_ZBHVYWq7T8Xy3w7t51XbUUNo_MBjwzGwXNYlABcqr10Xt9fydJjqHQtTqIp838RToHk9yRNA=w207-h44" width="207" /></a></div><div class="separator" style="clear: both; text-align: left;">For more info see: <a href="https://airflow.apache.org/docs/apache-airflow/2.6.3/howto/customize-ui.html#customizing-dag-ui-header-and-airflow-page-titles">Customizing the UI — Airflow Documentation (apache.org)</a></div><br /><br /><p></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-81478750391529343022023-10-23T14:55:00.007-06:002023-10-24T09:58:36.569-06:00AWS Athena - Using Merge + Iceberg tables to store only changed records<p><span style="background-color: white;"><span face="-apple-system, BlinkMacSystemFont, Segoe UI, Noto Sans, Helvetica, Arial, sans-serif, Apple Color Emoji, Segoe UI Emoji" style="color: #1f2328;">This post is based on my github page: </span></span><a href="https://github.com/rajrao/mypublicnotes/blob/master/AWS/Athena/iceberg_tracking_changes.md">mypublicnotes/AWS/Athena/iceberg_tracking_changes.md at master · rajrao/mypublicnotes (github.com)</a></p><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px !important;"><span style="box-sizing: border-box; font-weight: var(--base-text-weight-semibold, 600);">Change data capture using Athena and iceberg</span></p><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;">Many times in a datalake, you have a source, where the source doesnt provide information about which records changed. Another use case is where you have an ETL, where you have multiple tables and columns taking part in it and its traditionally difficult to track which records changed in that ETL query. This page shows you one method for being able to track those changes and insert only those records that are new or had updates. (at the end, I also show how to track deletes). The method leverages AWS Iceberg tables in Athena (Athena Engine 3) and the upsert mechanism provided via the <span style="box-sizing: border-box; font-weight: var(--base-text-weight-semibold, 600);">merge-into</span> statement.</p><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;">TL;DR; Check out the <a href="https://github.com/rajrao/mypublicnotes/blob/master/AWS/Athena/iceberg_tracking_changes.md#Merge" style="background-color: transparent; box-sizing: border-box; text-underline-offset: 0.2rem;">merge</a> statement used to update only those records that had changes.</p><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;"><span style="box-sizing: border-box; font-weight: var(--base-text-weight-semibold, 600);">Setup: A CTE for source data</span></p><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;">I am using a CTE to simulate source data, in practice, you would typically use another Athena table as your source, or a query that brings data together from multiple tables (aka ETL), etc. A key part to this method is using a hashing function that can be used to determine when a record has changes. I use <a href="https://trino.io/docs/current/functions/binary.html#hashing-functions:~:text=of%20binary.-,xxhash64,-(binary)" rel="nofollow" style="background-color: transparent; box-sizing: border-box; text-underline-offset: 0.2rem;">xxhas64</a></p><div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" style="box-sizing: border-box; color: #1f2328; display: flex; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; justify-content: space-between; margin-bottom: 16px; overflow: auto !important; position: relative !important;"><pre style="border-radius: 6px; box-sizing: border-box; color: var(--fgColor-default, var(--color-fg-default)); font-family: ui-monospace, SFMono-Regular, "SF Mono", Menlo, Consolas, "Liberation Mono", monospace; font-size: 13.6px; line-height: 1.45; margin-bottom: 0px; margin-top: 0px; overflow-wrap: normal; overflow: auto; padding: 16px; word-break: normal;">with cte(id, value1, value2) <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">as</span>
(
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">1</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>a1<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>b<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">union all</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">4</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>raj<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>rao<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">union all</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">2</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>c2<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>d2<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>
)
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">*</span>, xxhash64(from_base64(value1 <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">||</span> value2)) <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">as</span> hash <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">from</span> cte</pre><div class="zeroclipboard-container" style="animation: auto ease 0s 1 normal none running none; box-sizing: border-box;"><clipboard-copy aria-label="Copy" class="ClipboardButton btn btn-invisible js-clipboard-copy m-2 p-0 tooltipped-no-delay d-flex flex-justify-center flex-items-center" data-copy-feedback="Copied!" data-tooltip-direction="w" role="button" style="align-items: center !important; appearance: none; border-radius: 6px; border: 0px; box-shadow: none; box-sizing: border-box; color: var(--fgColor-accent, var(--color-accent-fg)); cursor: pointer; display: flex !important; font-size: 14px; font-weight: var(--base-text-weight-medium, 500); height: var(--control-small-size, 28px); justify-content: center !important; line-height: 20px; margin: var(--base-size-8, 8px) !important; padding: 0px !important; position: relative; text-wrap: nowrap; transition: color 80ms cubic-bezier(0.33, 1, 0.68, 1) 0s, background-color 0s ease 0s, box-shadow 0s ease 0s, border-color 0s ease 0s; user-select: none; vertical-align: middle; width: var(--control-small-size, 28px);" tabindex="0" value="with cte(id, value1, value2) as
(
select 1,'a1','b' union all
select 4,'raj','rao' union all
select 2,'c2','d2'
)
select *, xxhash64(from_base64(value1 || value2)) as hash from cte"><svg aria-hidden="true" class="octicon octicon-copy js-clipboard-copy-icon" data-view-component="true" height="16" version="1.1" viewbox="0 0 16 16" width="16"><path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 0 1 0 1.5h-1.5a.25.25 0 0 0-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 0 0 .25-.25v-1.5a.75.75 0 0 1 1.5 0v1.5A1.75 1.75 0 0 1 9.25 16h-7.5A1.75 1.75 0 0 1 0 14.25Z"></path><path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75 1.75 0 0 1 14.25 11h-7.5A1.75 1.75 0 0 1 5 9.25Zm1.75-.25a.25.25 0 0 0-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 0 0 .25-.25v-7.5a.25.25 0 0 0-.25-.25Z"></path></svg></clipboard-copy></div></div><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;">Note 1: You can use murmur3 instead of xxhash64 using the following code: murmur3(to_utf8(value1 || value2)).</p><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;">Note 2: Here are the other hashing functions available: <a href="https://trino.io/docs/current/functions/binary.html" rel="nofollow" style="background-color: transparent; box-sizing: border-box; text-underline-offset: 0.2rem;">https://trino.io/docs/current/functions/binary.html</a></p><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;"><span style="box-sizing: border-box; font-weight: var(--base-text-weight-semibold, 600);">Setup: Create an iceberg table</span></p><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;">The iceberg table is your final table. This will track the data that had changes. Id is the primary key in this case, you can have more columns that are part of the primary key used for the update.</p><div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" style="box-sizing: border-box; color: #1f2328; display: flex; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; justify-content: space-between; margin-bottom: 16px; overflow: auto !important; position: relative !important;"><pre style="border-radius: 6px; box-sizing: border-box; color: var(--fgColor-default, var(--color-fg-default)); font-family: ui-monospace, SFMono-Regular, "SF Mono", Menlo, Consolas, "Liberation Mono", monospace; font-size: 13.6px; line-height: 1.45; margin-bottom: 0px; margin-top: 0px; overflow-wrap: normal; overflow: auto; padding: 16px; word-break: normal;">CREATE TABLE
<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">test_db</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">hash_test</span> (
id <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">int</span>,
value1 string,
value2 string,
hash string,
last_updated_on <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">timestamp</span>)
LOCATION <span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>s3://my_test_bucket/hash_test<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>
TBLPROPERTIES ( <span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>table_type<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">=</span><span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>ICEBERG<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>)</pre><div class="zeroclipboard-container" style="animation: auto ease 0s 1 normal none running none; box-sizing: border-box;"><clipboard-copy aria-label="Copy" class="ClipboardButton btn btn-invisible js-clipboard-copy m-2 p-0 tooltipped-no-delay d-flex flex-justify-center flex-items-center" data-copy-feedback="Copied!" data-tooltip-direction="w" role="button" style="align-items: center !important; appearance: none; border-radius: 6px; border: 0px; box-shadow: none; box-sizing: border-box; color: var(--fgColor-accent, var(--color-accent-fg)); cursor: pointer; display: flex !important; font-size: 14px; font-weight: var(--base-text-weight-medium, 500); height: var(--control-small-size, 28px); justify-content: center !important; line-height: 20px; margin: var(--base-size-8, 8px) !important; padding: 0px !important; position: relative; text-wrap: nowrap; transition: color 80ms cubic-bezier(0.33, 1, 0.68, 1) 0s, background-color 0s ease 0s, box-shadow 0s ease 0s, border-color 0s ease 0s; user-select: none; vertical-align: middle; width: var(--control-small-size, 28px);" tabindex="0" value="CREATE TABLE
test_db.hash_test (
id int,
value1 string,
value2 string,
hash string,
last_updated_on timestamp)
LOCATION 's3://my_test_bucket/hash_test'
TBLPROPERTIES ( 'table_type' ='ICEBERG')"><svg aria-hidden="true" class="octicon octicon-copy js-clipboard-copy-icon" data-view-component="true" height="16" version="1.1" viewbox="0 0 16 16" width="16"><path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 0 1 0 1.5h-1.5a.25.25 0 0 0-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 0 0 .25-.25v-1.5a.75.75 0 0 1 1.5 0v1.5A1.75 1.75 0 0 1 9.25 16h-7.5A1.75 1.75 0 0 1 0 14.25Z"></path><path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75 1.75 0 0 1 14.25 11h-7.5A1.75 1.75 0 0 1 5 9.25Zm1.75-.25a.25.25 0 0 0-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 0 0 .25-.25v-7.5a.25.25 0 0 0-.25-.25Z"></path></svg></clipboard-copy></div></div><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;"><span style="box-sizing: border-box; font-weight: var(--base-text-weight-semibold, 600);">The ##Merge## statement</span></p><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;">Here is a merge statement that inserts new records and updates only when there are changes. The merge statement uses the CTE described above as its source data. You can manipulate the CTE to test various scenarios. The hash column is used to determine when to insert/update data.</p><div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" style="box-sizing: border-box; color: #1f2328; display: flex; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; justify-content: space-between; margin-bottom: 16px; overflow: auto !important; position: relative !important;"><pre style="border-radius: 6px; box-sizing: border-box; color: var(--fgColor-default, var(--color-fg-default)); font-family: ui-monospace, SFMono-Regular, "SF Mono", Menlo, Consolas, "Liberation Mono", monospace; font-size: 13.6px; line-height: 1.45; margin-bottom: 0px; margin-top: 0px; overflow-wrap: normal; overflow: auto; padding: 16px; word-break: normal;">MERGE INTO hash_test <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">as</span> tgt
USING (
with cte(id, value1, value2, value3) <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">as</span>
(
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">1</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>a1<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>b<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">100</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">union all</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">4</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>rao<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>raj<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">200</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">union all</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">2</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>c2<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>d2<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">300</span>
)
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">*</span>, xxhash64(to_utf8(concat_ws(<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>::<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,coalesce(value1,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>-<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>),coalesce(value2,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>-<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>),coalesce(cast(value3 <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">as</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">varchar</span>))))) <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">as</span> hash <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">from</span> cte
) <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">as</span> src
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">ON</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">tgt</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">id</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">=</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">src</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">id</span>
WHEN MATCHED <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">and</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">src</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">hash</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);"><></span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">tgt</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">hash</span>
THEN <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">UPDATE</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">SET</span>
value1 <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">=</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">src</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">value1</span>,
value2 <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">=</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">src</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">value2</span>,
hash <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">=</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">src</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">hash</span>,
last_updated_on <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">=</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">current_timestamp</span>
WHEN NOT MATCHED
THEN INSERT (id, value1, value2, hash, last_updated_on)
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">VALUES</span> (<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">src</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">id</span>, <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">src</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">value1</span>, <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">src</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">value2</span>, <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">src</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">hash</span>, <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">current_timestamp</span>) </pre><div class="zeroclipboard-container" style="animation: auto ease 0s 1 normal none running none; box-sizing: border-box;"><clipboard-copy aria-label="Copy" class="ClipboardButton btn btn-invisible js-clipboard-copy m-2 p-0 tooltipped-no-delay d-flex flex-justify-center flex-items-center" data-copy-feedback="Copied!" data-tooltip-direction="w" role="button" style="align-items: center !important; appearance: none; border-radius: 6px; border: 0px; box-shadow: none; box-sizing: border-box; color: var(--fgColor-accent, var(--color-accent-fg)); cursor: pointer; display: flex !important; font-size: 14px; font-weight: var(--base-text-weight-medium, 500); height: var(--control-small-size, 28px); justify-content: center !important; line-height: 20px; margin: var(--base-size-8, 8px) !important; padding: 0px !important; position: relative; text-wrap: nowrap; transition: color 80ms cubic-bezier(0.33, 1, 0.68, 1) 0s, background-color 0s ease 0s, box-shadow 0s ease 0s, border-color 0s ease 0s; user-select: none; vertical-align: middle; width: var(--control-small-size, 28px);" tabindex="0" value="MERGE INTO hash_test as tgt
USING (
with cte(id, value1, value2, value3) as
(
select 1,'a1','b',100 union all
select 4,'rao','raj',200 union all
select 2,'c2','d2',300
)
select *, xxhash64(to_utf8(concat_ws('::',coalesce(value1,'-'),coalesce(value2,'-'),coalesce(cast(value3 as varchar))))) as hash from cte
) as src
ON tgt.id = src.id
WHEN MATCHED and src.hash <> tgt.hash
THEN UPDATE SET
value1 = src.value1,
value2 = src.value2,
hash = src.hash,
last_updated_on = current_timestamp
WHEN NOT MATCHED
THEN INSERT (id, value1, value2, hash, last_updated_on)
VALUES (src.id, src.value1, src.value2, src.hash, current_timestamp) "><svg aria-hidden="true" class="octicon octicon-copy js-clipboard-copy-icon" data-view-component="true" height="16" version="1.1" viewbox="0 0 16 16" width="16"><path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 0 1 0 1.5h-1.5a.25.25 0 0 0-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 0 0 .25-.25v-1.5a.75.75 0 0 1 1.5 0v1.5A1.75 1.75 0 0 1 9.25 16h-7.5A1.75 1.75 0 0 1 0 14.25Z"></path><path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75 1.75 0 0 1 14.25 11h-7.5A1.75 1.75 0 0 1 5 9.25Zm1.75-.25a.25.25 0 0 0-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 0 0 .25-.25v-7.5a.25.25 0 0 0-.25-.25Z"></path></svg></clipboard-copy></div></div><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;">If you need to deal with deletes, you can add as your first matched phrase one of the following options (delete, or archive):</p><div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" style="box-sizing: border-box; color: #1f2328; display: flex; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; justify-content: space-between; margin-bottom: 16px; overflow: auto !important; position: relative !important;"><pre style="border-radius: 6px; box-sizing: border-box; color: var(--fgColor-default, var(--color-fg-default)); font-family: ui-monospace, SFMono-Regular, "SF Mono", Menlo, Consolas, "Liberation Mono", monospace; font-size: 13.6px; line-height: 1.45; margin-bottom: 0px; margin-top: 0px; overflow-wrap: normal; overflow: auto; padding: 16px; word-break: normal;">WHEN MATCHED <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">and</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">src</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">IsDeleted</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">=</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">1</span>
THEN <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">DELETE</span></pre><div class="zeroclipboard-container" style="animation: auto ease 0s 1 normal none running none; box-sizing: border-box;"><clipboard-copy aria-label="Copy" class="ClipboardButton btn btn-invisible js-clipboard-copy m-2 p-0 tooltipped-no-delay d-flex flex-justify-center flex-items-center" data-copy-feedback="Copied!" data-tooltip-direction="w" role="button" style="align-items: center !important; appearance: none; border-radius: 6px; border: 0px; box-shadow: none; box-sizing: border-box; color: var(--fgColor-accent, var(--color-accent-fg)); cursor: pointer; display: flex !important; font-size: 14px; font-weight: var(--base-text-weight-medium, 500); height: var(--control-small-size, 28px); justify-content: center !important; line-height: 20px; margin: var(--base-size-8, 8px) !important; padding: 0px !important; position: relative; text-wrap: nowrap; transition: color 80ms cubic-bezier(0.33, 1, 0.68, 1) 0s, background-color 0s ease 0s, box-shadow 0s ease 0s, border-color 0s ease 0s; user-select: none; vertical-align: middle; width: var(--control-small-size, 28px);" tabindex="0" value="WHEN MATCHED and src.IsDeleted = 1
THEN DELETE"><svg aria-hidden="true" class="octicon octicon-copy js-clipboard-copy-icon" data-view-component="true" height="16" version="1.1" viewbox="0 0 16 16" width="16"><path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 0 1 0 1.5h-1.5a.25.25 0 0 0-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 0 0 .25-.25v-1.5a.75.75 0 0 1 1.5 0v1.5A1.75 1.75 0 0 1 9.25 16h-7.5A1.75 1.75 0 0 1 0 14.25Z"></path><path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75 1.75 0 0 1 14.25 11h-7.5A1.75 1.75 0 0 1 5 9.25Zm1.75-.25a.25.25 0 0 0-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 0 0 .25-.25v-7.5a.25.25 0 0 0-.25-.25Z"></path></svg></clipboard-copy></div></div><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;">or</p><div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" style="box-sizing: border-box; color: #1f2328; display: flex; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; justify-content: space-between; margin-bottom: 16px; overflow: auto !important; position: relative !important;"><pre style="border-radius: 6px; box-sizing: border-box; color: var(--fgColor-default, var(--color-fg-default)); font-family: ui-monospace, SFMono-Regular, "SF Mono", Menlo, Consolas, "Liberation Mono", monospace; font-size: 13.6px; line-height: 1.45; margin-bottom: 0px; margin-top: 0px; overflow-wrap: normal; overflow: auto; padding: 16px; word-break: normal;">WHEN MATCHED <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">and</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">src</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">IsDeleted</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">=</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">1</span>
THEN <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">UPDATE</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">SET</span>
is_archived <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">=</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">1</span>,
last_updated_on <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">=</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">current_timestamp</span></pre><div class="zeroclipboard-container" style="animation: auto ease 0s 1 normal none running none; box-sizing: border-box;"><clipboard-copy aria-label="Copy" class="ClipboardButton btn btn-invisible js-clipboard-copy m-2 p-0 tooltipped-no-delay d-flex flex-justify-center flex-items-center" data-copy-feedback="Copied!" data-tooltip-direction="w" role="button" style="align-items: center !important; appearance: none; border-radius: 6px; border: 0px; box-shadow: none; box-sizing: border-box; color: var(--fgColor-accent, var(--color-accent-fg)); cursor: pointer; display: flex !important; font-size: 14px; font-weight: var(--base-text-weight-medium, 500); height: var(--control-small-size, 28px); justify-content: center !important; line-height: 20px; margin: var(--base-size-8, 8px) !important; padding: 0px !important; position: relative; text-wrap: nowrap; transition: color 80ms cubic-bezier(0.33, 1, 0.68, 1) 0s, background-color 0s ease 0s, box-shadow 0s ease 0s, border-color 0s ease 0s; user-select: none; vertical-align: middle; width: var(--control-small-size, 28px);" tabindex="0" value="WHEN MATCHED and src.IsDeleted = 1
THEN UPDATE SET
is_archived = 1,
last_updated_on = current_timestamp"><svg aria-hidden="true" class="octicon octicon-copy js-clipboard-copy-icon" data-view-component="true" height="16" version="1.1" viewbox="0 0 16 16" width="16"><path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 0 1 0 1.5h-1.5a.25.25 0 0 0-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 0 0 .25-.25v-1.5a.75.75 0 0 1 1.5 0v1.5A1.75 1.75 0 0 1 9.25 16h-7.5A1.75 1.75 0 0 1 0 14.25Z"></path><path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75 1.75 0 0 1 14.25 11h-7.5A1.75 1.75 0 0 1 5 9.25Zm1.75-.25a.25.25 0 0 0-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 0 0 .25-.25v-7.5a.25.25 0 0 0-.25-.25Z"></path></svg></clipboard-copy></div></div><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;"><span style="box-sizing: border-box; font-weight: var(--base-text-weight-semibold, 600);">Finally some examples of queries to view the data</span></p><div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" style="box-sizing: border-box; color: #1f2328; display: flex; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; justify-content: space-between; margin-bottom: 16px; overflow: auto !important; position: relative !important;"><pre style="border-radius: 6px; box-sizing: border-box; color: var(--fgColor-default, var(--color-fg-default)); font-family: ui-monospace, SFMono-Regular, "SF Mono", Menlo, Consolas, "Liberation Mono", monospace; font-size: 13.6px; line-height: 1.45; margin-bottom: 0px; margin-top: 0px; overflow-wrap: normal; overflow: auto; padding: 16px; word-break: normal;"><span class="pl-c" style="box-sizing: border-box; color: var(--color-prettylights-syntax-comment);"><span class="pl-c" style="box-sizing: border-box; color: var(--color-prettylights-syntax-comment);">--</span> see the history of changes</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">*</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">from</span> test_db.<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">"</span>hash_test$history<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">"</span></span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">order by</span> made_current_at <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">desc</span>
<span class="pl-c" style="box-sizing: border-box; color: var(--color-prettylights-syntax-comment);"><span class="pl-c" style="box-sizing: border-box; color: var(--color-prettylights-syntax-comment);">--</span> use a snasphot_id from above as your value for xxxxx</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">*</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">from</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">test_db</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">hash_test</span> for version <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">as</span> of xxxxx
<span class="pl-c" style="box-sizing: border-box; color: var(--color-prettylights-syntax-comment);"><span class="pl-c" style="box-sizing: border-box; color: var(--color-prettylights-syntax-comment);">--</span> get only the latest records from the table</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">*</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">from</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">test_db</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">hash_test</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">where</span> last_updated_on <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">in</span> (<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">max</span>(last_updated_on) <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">from</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">test_db</span>.<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">hash_test</span>)
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">order by</span> last_updated_on</pre><div class="zeroclipboard-container" style="animation: auto ease 0s 1 normal none running none; box-sizing: border-box;"><clipboard-copy aria-label="Copy" class="ClipboardButton btn btn-invisible js-clipboard-copy m-2 p-0 tooltipped-no-delay d-flex flex-justify-center flex-items-center" data-copy-feedback="Copied!" data-tooltip-direction="w" role="button" style="align-items: center !important; appearance: none; border-radius: 6px; border: 0px; box-shadow: none; box-sizing: border-box; color: var(--fgColor-accent, var(--color-accent-fg)); cursor: pointer; display: flex !important; font-size: 14px; font-weight: var(--base-text-weight-medium, 500); height: var(--control-small-size, 28px); justify-content: center !important; line-height: 20px; margin: var(--base-size-8, 8px) !important; padding: 0px !important; position: relative; text-wrap: nowrap; transition: color 80ms cubic-bezier(0.33, 1, 0.68, 1) 0s, background-color 0s ease 0s, box-shadow 0s ease 0s, border-color 0s ease 0s; user-select: none; vertical-align: middle; width: var(--control-small-size, 28px);" tabindex="0" value="-- see the history of changes
select * from test_db."hash_test$history" order by made_current_at desc
-- use a snasphot_id from above as your value for xxxxx
select * from test_db.hash_test for version as of xxxxx
-- get only the latest records from the table
select * from test_db.hash_test
where last_updated_on in (select max(last_updated_on) from test_db.hash_test)
order by last_updated_on"><svg aria-hidden="true" class="octicon octicon-copy js-clipboard-copy-icon" data-view-component="true" height="16" version="1.1" viewbox="0 0 16 16" width="16"><path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 0 1 0 1.5h-1.5a.25.25 0 0 0-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 0 0 .25-.25v-1.5a.75.75 0 0 1 1.5 0v1.5A1.75 1.75 0 0 1 9.25 16h-7.5A1.75 1.75 0 0 1 0 14.25Z"></path><path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75 1.75 0 0 1 14.25 11h-7.5A1.75 1.75 0 0 1 5 9.25Zm1.75-.25a.25.25 0 0 0-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 0 0 .25-.25v-7.5a.25.25 0 0 0-.25-.25Z"></path></svg></clipboard-copy></div></div><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;">Reference:</p><ol dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px; padding-left: 2em;"><li style="box-sizing: border-box;"><a href="https://docs.aws.amazon.com/athena/latest/ug/functions.html" rel="nofollow" style="background-color: transparent; box-sizing: border-box; text-underline-offset: 0.2rem;">Athena Functions</a></li><li style="box-sizing: border-box; margin-top: 0.25em;"><a href="https://docs.aws.amazon.com/athena/latest/ug/delta-lake-tables.html" rel="nofollow" style="background-color: transparent; box-sizing: border-box; text-underline-offset: 0.2rem;">Query Delta Lake Tables</a><a href="https://docs.aws.amazon.com/athena/latest/ug/delta-lake-tables.html" rel="nofollow" style="background-color: transparent; box-sizing: border-box; text-underline-offset: 0.2rem;">https://docs.aws.amazon.com/athena/latest/ug/delta-lake-tables.html</a>)</li><li style="box-sizing: border-box; margin-top: 0.25em;"><a href="https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg.html" rel="nofollow" style="background-color: transparent; box-sizing: border-box; text-underline-offset: 0.2rem;">Using Apache Iceberg tables</a></li></ol><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;"><span style="box-sizing: border-box; font-weight: var(--base-text-weight-semibold, 600);">Testing Hashing Behavior</span></p><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;">When hashing you need to make sure that null values are handled appropriately.</p><p dir="auto" style="background-color: white; box-sizing: border-box; color: #1f2328; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; margin-bottom: 16px; margin-top: 0px;">Ex: null, a, null and a, null, null should be treated as changes. If they generate the same hash, then you will miss this change. Also the hash functions need string input and hence, one needs to cast the data when its not of type string. For this reason, the computation of the hash gets complicated and I have not found a simpler solution around this.</p><div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" style="box-sizing: border-box; color: #1f2328; display: flex; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "Noto Sans", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji"; font-size: 16px; justify-content: space-between; margin-bottom: 16px; overflow: auto !important; position: relative !important;"><pre style="border-radius: 6px; box-sizing: border-box; color: var(--fgColor-default, var(--color-fg-default)); font-family: ui-monospace, SFMono-Regular, "SF Mono", Menlo, Consolas, "Liberation Mono", monospace; font-size: 13.6px; line-height: 1.45; margin-bottom: 0px; margin-top: 0px; overflow-wrap: normal; overflow: auto; padding: 16px; word-break: normal;">with cte(id,note, value1, value2,value3) <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">as</span>
(
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">1</span>,<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">null</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>a1<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>b<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">1</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">union all</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">4</span>,<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">null</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>raj<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>rao<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">2</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">union all</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">5</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>both null<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">null</span>,<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">null</span>,<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">null</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">union all</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">6</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>empty & null<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">null</span>,<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">null</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">union all</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">7</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>null & empty<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">null</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">1</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">union all</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">8</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>empty-empty<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">2</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">union all</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">9</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>str-null<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>a<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">null</span>,<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">3</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">union all</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">10</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>null-str<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">null</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>a<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">4</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">union all</span>
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">100</span>,<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">null</span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>c2<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>d2<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,<span class="pl-c1" style="box-sizing: border-box; color: var(--color-prettylights-syntax-constant);">5</span>
)
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">select</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">*</span>
,concat_ws(<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>::<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,coalesce(value1,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>-<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>),coalesce(value2,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>-<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>),coalesce(cast(value3 <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">as</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">varchar</span>)))
, murmur3(to_utf8(concat_ws(<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>::<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,coalesce(value1,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>-<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>),coalesce(value2,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>-<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>),coalesce(cast(value3 <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">as</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">varchar</span>))))) <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">as</span> hash1
, xxhash64(to_utf8(concat_ws(<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>::<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>,coalesce(value1,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>-<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>),coalesce(value2,<span class="pl-s" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);"><span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span>-<span class="pl-pds" style="box-sizing: border-box; color: var(--color-prettylights-syntax-string);">'</span></span>),coalesce(cast(value3 <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">as</span> <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">varchar</span>))))) <span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">as</span> hash2
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">from</span> cte
<span class="pl-k" style="box-sizing: border-box; color: var(--color-prettylights-syntax-keyword);">order by</span> id</pre></div><p><br /></p><p><br /></p><p><br /></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-45481645687996521612023-07-18T11:43:00.003-06:002023-07-18T11:43:17.795-06:00PowerBi/PowerQuery: Dealing with errors in Excel files<p> When you have errors in your excel file, they sometimes leak through and adding "Table.ReplaceErrorValues" or "Table.RemoveRowsWithErrors" doesnt really work. What I have found is to add the error fix step right after the navigation step that loads the sheet. </p><p>In the screenshot below, I have used "Table.RemoveRowsWithErrors" after the Navigation step and it fixed the error.</p><p><br /></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjn43EQr1pD73TWvzCL40qnQl7aKFo3dIYrxKz_Sxe97aHS2stZzxetJI5BroZ0zlLk-QT6ANkfllJs7AHKV8Y1KcFvjlv1D2gucA7bgNo4TyYameHBFtktphHYuDS8HMrIzYcNkCZUc56KISTmlBNrOIFUyPiIR1jTAYgHdQFEOiDXrwCLglGKOA" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="144" data-original-width="228" height="202" src="https://blogger.googleusercontent.com/img/a/AVvXsEjn43EQr1pD73TWvzCL40qnQl7aKFo3dIYrxKz_Sxe97aHS2stZzxetJI5BroZ0zlLk-QT6ANkfllJs7AHKV8Y1KcFvjlv1D2gucA7bgNo4TyYameHBFtktphHYuDS8HMrIzYcNkCZUc56KISTmlBNrOIFUyPiIR1jTAYgHdQFEOiDXrwCLglGKOA" width="320" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgrsoUw5zqUTtkYZgHlmYY6vXOkQaHf9zoderVfy_857J5-IU4ETRUYkttTLm15t85CeSXDctTCDCijvPEJY_GZioYDoasYZU21TCiXsei7mYRhwpP9dHbe_hUfRy1V2tI7g05wE-5iIw7XPMQtQfiXEoVI7y9Ly_l4MDwvQLA067-sDajKvbRGJg" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="470" data-original-width="239" height="320" src="https://blogger.googleusercontent.com/img/a/AVvXsEgrsoUw5zqUTtkYZgHlmYY6vXOkQaHf9zoderVfy_857J5-IU4ETRUYkttTLm15t85CeSXDctTCDCijvPEJY_GZioYDoasYZU21TCiXsei7mYRhwpP9dHbe_hUfRy1V2tI7g05wE-5iIw7XPMQtQfiXEoVI7y9Ly_l4MDwvQLA067-sDajKvbRGJg=w163-h320" width="163" /></a></div><br /><br /><p></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-14972211958962632452023-05-28T09:48:00.003-06:002023-05-28T09:48:57.489-06:00Applying for US passport in 2023<p>We needed a new passport for our daughter as her passport expires in 5 months and 3 weeks from date of travel (country requires 6 months). </p><p>Panicking, we emailed our senators and representatives. Got a call from one of them and they advised to call passport phone number and tell them country we were travelling to needs a visa (urgent passport appointment is provided 28 days out for countries needing passport).</p><p><br /></p><p>Time line</p><p>May 5: figured out we needed new passport. Called passport agency, was told to call back 2 weeks prior to travel.</p><p>May 6: emailed senators and representatives</p><p>May 8: got call back from one of the representatives' staff advising about calling back and telling them that we needed a visa</p><p>May 9: called passport agency and got an interview date for May 23 at Colorado office in Aurora. Lucky for us this is a 30 minute drive for us.</p><p>May 23: appointment was for 8am. Should have lined up 30 minutes early. Line was long, but efficiently managed. Had flight tickets, birth certificate (as passport was for kid and this is considered a new application and not renewal), paper work about needing visa. The entire appointment lasted less than 60 minutes. Was told to return after 2pm on 25th to pick up passport.</p><p>May 25: got passport (took 15 minutes)</p><p><br /></p><p>Reflections:</p><p>1. Everyone we spoke to from the phone staff to the people in the Colorado passport office were extremely helpful, efficient and great to work with.</p><p>2. Didn't really need help from senator/representatives, but the help they provided telling us the provision for visa, was the breakthrough we needed.</p><p>3. Next time we will apply for passport 12 months prior to expiration, as many countries need 6 months validity on passport for travel.</p><p>4. Kids need a ds-11 and you are reapplying for a new passport (I believe until age of 16). Their passports are valid for only 5 years.</p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-58825797923699093532023-05-12T16:50:00.002-06:002023-05-12T16:51:14.714-06:00AWS CloudFormation error: Properties validation failed for resource LAMBDAXXXX with message: #/Code: expected type: JSONObject, found: String<p>During a AWS CF deployment I got the following error:</p><p><span style="background-color: white;"><span face="Amazon Ember, Helvetica Neue, Roboto, Arial, sans-serif" style="color: #16191f;"><span style="font-size: 14px;">Properties validation failed for resource LAMBDAXXXX with message: #/Code: expected type: JSONObject, found: String</span></span></span></p><p><span face=""Amazon Ember", "Helvetica Neue", Roboto, Arial, sans-serif" style="background-color: white; color: #16191f; font-size: 14px;">After struggling for over 4 hours, it turned out that the issue was that the path was not correct in the CF Template!!!</span></p><div style="color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; white-space: pre;"> <span style="color: #569cd6;">LAMBDAXXXX</span>:</div><div style="color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; white-space: pre;"> <span style="color: #569cd6;">Type</span>: <span style="color: #ce9178;">"AWS::Lambda::Function"</span></div><div style="color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; white-space: pre;"> <span style="color: #569cd6;">Properties</span>:</div><div style="color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; white-space: pre;"> <span style="color: #569cd6;">FunctionName</span>: <span style="color: #ce9178;">LambdaIngestXXXX</span></div><div style="color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; white-space: pre;"> <span style="color: #569cd6;">Code</span>: <span style="color: #ce9178;">"../lambda/lambda_function_folderpath_xxxx/"</span></div><div style="color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; white-space: pre;"> <span style="color: #569cd6;">Handler</span>: <span style="color: #ce9178;">"lambda_function.lambda_handler"</span></div><div style="color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; white-space: pre;"> <span style="color: #569cd6;">Layers</span>:</div><div style="color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; white-space: pre;"> - <span style="color: #569cd6;">!ImportValue</span> <span style="color: #ce9178;">LAYERPYREQUESTS</span></div><div style="color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; white-space: pre;"> - <span style="color: #569cd6;">!ImportValue</span> <span style="color: #ce9178;">LAYERAWSWRANGLER</span></div><div><br /></div><div>The error: "<span style="background-color: white; color: #16191f; font-size: 14px;">#/Code: expected type: JSONObject, found: String</span>" it turns out translates to "your path <span style="color: #ce9178; font-family: Consolas, "Courier New", monospace; font-size: 14px; white-space: pre;">../lambda/lambda_function_folderpath_xxxx/</span>" is wrong!!!</div><p>How I hate CloudFormation!!!</p><p>Hope this saves someone else time!</p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-71651884107033139672023-05-01T18:24:00.002-06:002023-05-01T18:24:44.149-06:00Add an image into a PowerBi report via PowerBi.com/PowerBi Service<p>Currently, there is no way to insert an image into a PowerBi report via PowerBi.com (power bi service).</p><p>One, workaround though, is to insert a "Blank" button and then set the "Fill" options Background</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEi1h7I5t_V3BzmQ1wWV4n2X142AdR4PVkNMBFe39bqfidaIwzVokTaGNPPEN6Hdd4LfA_jfbjuiJdE0FwNI-mNqTysYuii4KEM499xjc1wkrTKewBulfX1olk-1_uY2-sbMv36oPL95ZVGl_eJ13dttuMdFk6LOQUjdDqXdPdngIqMX3LrFAXg" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="" data-original-height="356" data-original-width="158" height="189" src="https://blogger.googleusercontent.com/img/a/AVvXsEi1h7I5t_V3BzmQ1wWV4n2X142AdR4PVkNMBFe39bqfidaIwzVokTaGNPPEN6Hdd4LfA_jfbjuiJdE0FwNI-mNqTysYuii4KEM499xjc1wkrTKewBulfX1olk-1_uY2-sbMv36oPL95ZVGl_eJ13dttuMdFk6LOQUjdDqXdPdngIqMX3LrFAXg=w84-h189" width="84" /> <span> </span></a><a href="https://blogger.googleusercontent.com/img/a/AVvXsEiSrYPThuKfrT4SfVP_TqfkI8gjGg0uBbBl0Ia0PUwcerFngL8Njc-K8y3_c6YAhx8wmejf-v2848qfZHF3q2XijOPcnHHNdCURi35Xpxzia-F909Zzj8QK7vkTWLtxpH4XRIXLRvn6PkSQNrhS1I4hAjvmt4JrFn2Fxz4vvKLaX_X1IgY_RuA" style="clear: left; display: inline !important; margin-bottom: 1em; margin-right: 1em;"><img alt="" data-original-height="252" data-original-width="173" height="183" src="https://blogger.googleusercontent.com/img/a/AVvXsEiSrYPThuKfrT4SfVP_TqfkI8gjGg0uBbBl0Ia0PUwcerFngL8Njc-K8y3_c6YAhx8wmejf-v2848qfZHF3q2XijOPcnHHNdCURi35Xpxzia-F909Zzj8QK7vkTWLtxpH4XRIXLRvn6PkSQNrhS1I4hAjvmt4JrFn2Fxz4vvKLaX_X1IgY_RuA=w126-h183" width="126" /></a><a href="https://blogger.googleusercontent.com/img/a/AVvXsEi1h7I5t_V3BzmQ1wWV4n2X142AdR4PVkNMBFe39bqfidaIwzVokTaGNPPEN6Hdd4LfA_jfbjuiJdE0FwNI-mNqTysYuii4KEM499xjc1wkrTKewBulfX1olk-1_uY2-sbMv36oPL95ZVGl_eJ13dttuMdFk6LOQUjdDqXdPdngIqMX3LrFAXg" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br /></a></div><p></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-33479811439684256832023-04-28T14:53:00.005-06:002023-04-28T14:55:36.100-06:00PowerBi Deep Links (URL query string parameter filtering) is not working<p> If you find that your deeplinks into a PowerBi report are not working, please make sure you are using a "?" in front of the "filter parameter.</p><p>More info: <a href="https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters">Filter a report using query string parameters in the URL - Power BI | Microsoft Learn</a></p><p>If you are coming from a Reporting Server environment, the filter url used to have a & in front of it, which will not work.</p><p>When testing, one easy way to see if your query parameter is working is to check your filter pane. The filter if correctly picked up will show up in there in italics. The following filters the report by a field named "District" in a table named "District". <b>The table name and column names are case sensitive</b>. The value used for filtering is not. Another tell tale sign that your url is not correct is if the filter gets dropped from the URL once the page loads.</p><p><b><span style="color: #38761d;">?</span></b>filter=District/District eq 'FD - 01'</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhokUTPXrx2mdi3KOMT1-nFjlADfVDEsev6Aw-ZbN5Pb1W01d9YEvkmwCnyh-idSQl0_TpGFlKyTtojKRs5M-v8wNLHW-mK9IT3iUMf9-wBM5uS3TLjRmXutSBRgoJ6cRue_NogaBj66vBpUk38ivSx3zgrlDYUTDyTxsoGigYFBcy9FHHKNh4" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="88" data-original-width="206" height="137" src="https://blogger.googleusercontent.com/img/a/AVvXsEhokUTPXrx2mdi3KOMT1-nFjlADfVDEsev6Aw-ZbN5Pb1W01d9YEvkmwCnyh-idSQl0_TpGFlKyTtojKRs5M-v8wNLHW-mK9IT3iUMf9-wBM5uS3TLjRmXutSBRgoJ6cRue_NogaBj66vBpUk38ivSx3zgrlDYUTDyTxsoGigYFBcy9FHHKNh4" width="320" /></a></div><br /><p></p><p>Example of an old Report Server Link which will not work because of the "&": ?rs:embed=true<b><span style="color: red;">&</span></b>filter=District/District eq 'FD - 01'</p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-89967555444516627412023-04-06T15:33:00.003-06:002023-04-06T15:33:18.861-06:00Power BI + Denodo: FailedWithUnknownOrUnsupportedDataSources<p>We started getting an error with our Power Bi datasets that were using a Denodo connector. This seems to be happeing only with new files and not existing datasets.</p><p>The error we were getting is:</p><p><span _ngcontent-app-id-c656="" style="background-color: #fef7b2; font-family: "Segoe UI", wf_segoe-ui_normal, helvetica, arial, sans-serif; font-size: 14px;">Unable to determine the data source. In order to determine details about custom connector based data sources, Power BI service needs to connect to a gateway, however, no gateway was available/reachable. Details: Static analysis failed in gateway. gatewayObjectId:e884ca97-b4d9-45bc-8560-ad3d2d4eaa56, resultCode:FailedWithUnknownOrUnsupportedDataSources Query contains unsupported function. Function name: Denodo.Contents .</span></p><li _ngcontent-app-id-c656="" class="ng-star-inserted" style="background-color: #fef7b2; font-family: "Segoe UI", wf_segoe-ui_normal, helvetica, arial, sans-serif; font-size: 14px;">Data source for Query1</li><p>Locally the PowerBi files works fine, this error shows up in PowerBi service after you publish the dataset and take a look at the settings for the dataset.</p><p>It looks like this might be a bug in PowerBI where when you add a new query that uses the Denodo connector, it is adding an empty list to the arguments and that is causing this is.</p><p></p><blockquote><p> Source = Denodo.Contents("MyDenodoSourceName", null, []),</p></blockquote><p></p><p>The problem is caused by the []. If you change the line to remove the empty [], then everything works fine!</p><p><span><span> </span><span> </span> </span>Source = Denodo.Contents("MyDenodoSourceName", null),</p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-89783619630978679062023-02-11T09:25:00.002-07:002023-02-11T09:25:33.553-07:00Honda Service Codes<p>Recently my car was blinking the maintenance minder B167 code.</p><p>Turns out each character has a specific meaning.</p><h2 style="background-color: white; box-sizing: border-box; font-family: "Avenir Next", sans-serif; font-size: 24px; font-weight: 500; line-height: 1.1; margin: 20px 0px 10px; overflow-wrap: break-word;">Main Honda Service Codes</h2><p style="background-color: white; box-sizing: border-box; font-family: "Avenir Next", sans-serif; font-size: 13px; margin: 0px 0px 10px; overflow-wrap: break-word;"></p><p style="background-color: white; box-sizing: border-box; font-family: "Avenir Next", sans-serif; font-size: 13px; margin: 0px 0px 10px; overflow-wrap: break-word;"></p><div class="hidden-lg hidden-md" style="background-color: white; box-sizing: border-box; font-family: "Avenir Next", sans-serif; font-size: 13px;"><div class="table" style="box-sizing: border-box; margin-bottom: 20px; width: 382.19px;"><table align="center" border="1px" class="table" style="border-collapse: collapse; border-spacing: 0px; margin-bottom: 20px; max-width: 100%; width: 350px;"><tbody style="box-sizing: border-box;"><tr style="background-color: #8acdf7; box-sizing: border-box;"><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 112px;"><span style="box-sizing: border-box; font-weight: 700;">Code</span></td><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 540px;"><span style="box-sizing: border-box; font-weight: 700;">What it Means</span></td></tr><tr style="box-sizing: border-box;"><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 112px;">A</td><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 540px;">Change Oil and Filter</td></tr><tr style="box-sizing: border-box;"><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 112px;">B</td><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 540px;">Replace the engine oil and oil filter. Perform front and rear brake cleaning and lubrication. Adjust parking brake. Perform in-depth inspection of the brake components.</td></tr></tbody></table></div></div><p style="background-color: white; box-sizing: border-box; font-family: "Avenir Next", sans-serif; font-size: 13px; margin: 0px 0px 10px; overflow-wrap: break-word;"></p><p style="background-color: white; box-sizing: border-box; font-family: "Avenir Next", sans-serif; font-size: 13px; margin: 0px 0px 10px; overflow-wrap: break-word;"></p><div class="hidden-lg hidden-md" style="background-color: white; box-sizing: border-box; font-family: "Avenir Next", sans-serif; font-size: 13px;"><div class="table" style="box-sizing: border-box; margin-bottom: 20px; width: 382.19px;"><h2 style="box-sizing: border-box; color: inherit; font-size: 24px; font-weight: 500; line-height: 1.1; margin: 20px 0px 10px; overflow-wrap: break-word;">Honda Service Sub-Codes</h2><table align="center" border="1px" class="table" style="border-collapse: collapse; border-spacing: 0px; margin-bottom: 20px; max-width: 100%; width: 350px;"><tbody style="box-sizing: border-box;"><tr style="background-color: #8acdf7; box-sizing: border-box;"><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 119px;"><span style="box-sizing: border-box; font-weight: 700;">Sub-Code</span></td><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 533px;"><span style="box-sizing: border-box; font-weight: 700;">What it Means</span></td></tr><tr style="box-sizing: border-box;"><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 119px;">0</td><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 533px;">Perform multi-point inspection.</td></tr><tr style="box-sizing: border-box;"><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 119px;">1</td><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 533px;">Tires need a rotation service.</td></tr><tr style="box-sizing: border-box;"><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 119px;">2</td><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 533px;">Replace the cabin and engine air filters.</td></tr><tr style="box-sizing: border-box;"><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 119px;">3</td><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 533px;">Replace the transmission fluid.</td></tr><tr style="box-sizing: border-box;"><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 119px;">4</td><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 533px;">Replace spark plugs, timing belt (if equipped) and adjust valves.</td></tr><tr style="box-sizing: border-box;"><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 119px;">5</td><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 533px;">The engine coolant needs replacing.</td></tr><tr style="box-sizing: border-box;"><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 119px;">6</td><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 533px;">If equipped, rear differential fluid needs replacing.</td></tr><tr style="box-sizing: border-box;"><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 119px;">7</td><td style="border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box; line-height: 1.42857; padding: 8px; text-align: center; vertical-align: top; width: 533px;">Replace brake fluid.</td></tr></tbody></table></div></div>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-67250165388745871662022-11-01T14:02:00.007-06:002022-11-01T14:06:38.582-06:00Connecting to AWS Athena using JDBC (Dbeaver)<p>A quick post on setting up a connection to Athena with OKTA using JDBC. The SQL client I am using is DBeaver, but any other tool could be used.</p><p></p><ol style="text-align: left;"><li>Figure out your connection string.<br />replace the highlighted sections with values that are valid for your org.<br /><blockquote>jdbc:awsathena://AwsRegion=<span style="background-color: #fcff01;">us-west-2</span>;S3OutputLocation=<span style="background-color: #fcff01;">s3://my-athena-results-bucket/results-folder/</span>;AwsCredentialsProviderClass=com.simba.athena.iamsupport.plugin.OktaCredentialsProvider;user={user};password={password};idp_host=<span style="background-color: #fcff01;">mycompany</span>.okta.com;App_ID=<span style="background-color: #fcff01;">0aosd2asadsdsdsd/123</span>;SSL_Insecure=true;</blockquote></li><li>In Dbeaver, click on "New Connection", which will bring up the "Connect to a database" dialog. Search for Athena and click next.<br /> <a href="https://blogger.googleusercontent.com/img/a/AVvXsEiMVwQ9hhneiUvmRK9x-fRkKtaOUE82c9suvr30FehYs4J17c73FYU2TrmGaMpIlfVFOOzM9joqJowFDGzQQwc7gOFJUzn-ZAN9HnZum6tr0gRPTFibCrKgj8ixGkm0yb3RyrBGJoV9230XUA2Xh6eZVf4BPvmZ8Fk_5T1tGAsQZC9uA_Zgr1c" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img alt="" data-original-height="233" data-original-width="302" src="https://blogger.googleusercontent.com/img/a/AVvXsEiMVwQ9hhneiUvmRK9x-fRkKtaOUE82c9suvr30FehYs4J17c73FYU2TrmGaMpIlfVFOOzM9joqJowFDGzQQwc7gOFJUzn-ZAN9HnZum6tr0gRPTFibCrKgj8ixGkm0yb3RyrBGJoV9230XUA2Xh6eZVf4BPvmZ8Fk_5T1tGAsQZC9uA_Zgr1c=s16000" /></a></li><li>Enter your username & password. <i>For my org, I just had to enter my WindowsNT id without the domain, but it might be different for your org.</i><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhpnMMGSR4uR6q1mKWw9-0Mu2qEfTFnDayLGw7d6cGPvYC4uB4NalN7-VHxUcDEr2wgY7Fb3LAfRif1fet4x4SvZhxU-SB4iJXoDm3Gx2P2xQqwlNlkJF7RRPLIdp8xLv4lp1iEplNKPLiVnlvc4V9zrus5DJWgQivFyj6zLmMgQPypSrgYpM8" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="314" data-original-width="431" src="https://blogger.googleusercontent.com/img/a/AVvXsEhpnMMGSR4uR6q1mKWw9-0Mu2qEfTFnDayLGw7d6cGPvYC4uB4NalN7-VHxUcDEr2wgY7Fb3LAfRif1fet4x4SvZhxU-SB4iJXoDm3Gx2P2xQqwlNlkJF7RRPLIdp8xLv4lp1iEplNKPLiVnlvc4V9zrus5DJWgQivFyj6zLmMgQPypSrgYpM8=s16000" /></a></div></li><li><div class="separator" style="clear: both; text-align: left;">Click on the button Driver Settings</div></li><li><div class="separator" style="clear: both; text-align: left;">Replace the "URL Template" with the value from step 1 on this page (connection string)<br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhlvNNSf1fMTSqTkZRZaJuBMvbW6rvG9uxdSuuJzjoFWakUH-3md6CXvhOy4lQSTAiFQ7XF5BXeGcMD4JxVZE75kfhhITxz4Y7ZlUxVyDtAPhyLwBfZWbabDhhk_EnH19DQHa76sSEzDpnOwsDJpKcWztQ6wkRWv3l58wmGLJ2cjwq0Zcm1aSs" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="406" data-original-width="495" src="https://blogger.googleusercontent.com/img/a/AVvXsEhlvNNSf1fMTSqTkZRZaJuBMvbW6rvG9uxdSuuJzjoFWakUH-3md6CXvhOy4lQSTAiFQ7XF5BXeGcMD4JxVZE75kfhhITxz4Y7ZlUxVyDtAPhyLwBfZWbabDhhk_EnH19DQHa76sSEzDpnOwsDJpKcWztQ6wkRWv3l58wmGLJ2cjwq0Zcm1aSs=s16000" /></a></div><br />Click OK.</div></li><li><div class="separator" style="clear: both; text-align: left;">Test your connection.</div></li></ol><div>That should make it work!</div><div><br /></div><div><br /></div><div>Creating an ODBC connection is very similar:</div><div><div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEj9SADmTS2J1jtgo1tjC1cI-bXIpBiApHllON-VGJxkI_CEoUWBGbV3XGcBfl5QMLM9LzlZzDUAtUyKRd2BoFnJxZckflzs3ehCQmUC9FOGjYHwBWoaqkEMVtrNiv5h19cs3eHvswlLNTdYWrB-Y9RXVaWG9eC9m1qUWHsKm5LfDRoZ38ytx98" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="622" data-original-width="412" src="https://blogger.googleusercontent.com/img/a/AVvXsEj9SADmTS2J1jtgo1tjC1cI-bXIpBiApHllON-VGJxkI_CEoUWBGbV3XGcBfl5QMLM9LzlZzDUAtUyKRd2BoFnJxZckflzs3ehCQmUC9FOGjYHwBWoaqkEMVtrNiv5h19cs3eHvswlLNTdYWrB-Y9RXVaWG9eC9m1qUWHsKm5LfDRoZ38ytx98=s16000" /></a></div><br /><br /></div><div><div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgUbXddaqt9ulm2dE1damLzY_HQNXcDnU5OJBZGNX_l2iBk7O6Eqwb3O60xEN86HH6SAdQNQ0lw9hmjiYfpVwZrwWWVdOonf2kKqqpY4WCJ5yntDaVb0AtRk_NQ7tMhC9L8RcCXVP3eIvb2U1WYj6FYat876PGiJs5hBRj2p6MDVHP9burQiPY" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="619" data-original-width="379" src="https://blogger.googleusercontent.com/img/a/AVvXsEgUbXddaqt9ulm2dE1damLzY_HQNXcDnU5OJBZGNX_l2iBk7O6Eqwb3O60xEN86HH6SAdQNQ0lw9hmjiYfpVwZrwWWVdOonf2kKqqpY4WCJ5yntDaVb0AtRk_NQ7tMhC9L8RcCXVP3eIvb2U1WYj6FYat876PGiJs5hBRj2p6MDVHP9burQiPY=s16000" /></a></div><br /><br /></div><p></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com1tag:blogger.com,1999:blog-7656888.post-90877723671783656302022-09-28T10:28:00.002-06:002022-09-28T10:28:29.593-06:00PowerBi - Row Level Security behavior - without table filter expressions<p>This is a quick post on RLS behavior in a powerbi dataset, where you dont define any table filter expressions.</p><p>Basically, when you setup a role and you dont define any table filter DAX expressions, it allows you to control who has access to your dataset. This is useful, when you have multiple datasets in a workspace and you need to reduce the access to a particular group.</p><p>Here is an example:</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjp61NR343_HpM2ChadJKFi-4nt38mtMd0hdnYUonDWFTefliIEpjC0zASublGT_QwQUKTMhbJfWJmeuSed-mNQi2msCYh76JEt0b3DCqxF1eA32GE0qBTAOLomE5UU629K-9z3ANLuKu-9fvDCUINxamLWRKijkJQycT_l4Czoz8db20ItGWk" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="212" data-original-width="766" src="https://blogger.googleusercontent.com/img/a/AVvXsEjp61NR343_HpM2ChadJKFi-4nt38mtMd0hdnYUonDWFTefliIEpjC0zASublGT_QwQUKTMhbJfWJmeuSed-mNQi2msCYh76JEt0b3DCqxF1eA32GE0qBTAOLomE5UU629K-9z3ANLuKu-9fvDCUINxamLWRKijkJQycT_l4Czoz8db20ItGWk=s16000" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: left;"><br /></div>In the above case, people not assigned to the RLS role, will get the following message:<p></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgWQlSyHD1Z6fWesfCR_2l5dLDlf3XmER9s8R6hCFvIK4id5Or2cfxvZGxlLs8-lfq3J6ncx2lC9XcG0IQew-Qq9_QJOh4LdImnIE4u7XFMmNZZX4ww8xgQsZzmREimijss8c5nPLGutqZpa7ZP2oqKgVW2z-hHwug3QYZStGrGSmmKj6I739E" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="294" data-original-width="687" height="137" src="https://blogger.googleusercontent.com/img/a/AVvXsEgWQlSyHD1Z6fWesfCR_2l5dLDlf3XmER9s8R6hCFvIK4id5Or2cfxvZGxlLs8-lfq3J6ncx2lC9XcG0IQew-Qq9_QJOh4LdImnIE4u7XFMmNZZX4ww8xgQsZzmREimijss8c5nPLGutqZpa7ZP2oqKgVW2z-hHwug3QYZStGrGSmmKj6I739E" width="320" /></a></div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">I have found this behavior useful many times, when a dataset gets published, and all of a sudden, someone comes up with a late breaking security requirement and needs the dataset locked down. I quickly add a role and publish the dataset and that allows me to control who can see the data. Also, as people can see the reports, just with an error, it allows them to ask for access to this dataset (instead of completely hiding the dataset from them).</div><br /><br /><br /><p></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-74278505262376500522022-08-23T17:56:00.000-06:002022-08-23T17:56:38.842-06:00PowerBi - Datasets - Create a report from a template<p>Have you wondered how to use the "Create a report" >> "From a template" option from a dataset in PowerBi Service?</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjNxm2k-lNNzgHgdCdDweUBdI3Mnr_KNJBBpBpfnFQ_PPKctv9WwImBqjpF8b4j-afRIZijrKjj2EcSArrGPiPrW8y-iXG56ybl-N-Bnlfk2GlE9HwSOuMiWuepyKt8NfJyguHVxApSDUX83Lxg0__lK3YGOnameogerHq6cwWq_EDZSv-fQTE" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="197" data-original-width="193" src="https://blogger.googleusercontent.com/img/a/AVvXsEjNxm2k-lNNzgHgdCdDweUBdI3Mnr_KNJBBpBpfnFQ_PPKctv9WwImBqjpF8b4j-afRIZijrKjj2EcSArrGPiPrW8y-iXG56ybl-N-Bnlfk2GlE9HwSOuMiWuepyKt8NfJyguHVxApSDUX83Lxg0__lK3YGOnameogerHq6cwWq_EDZSv-fQTE=s16000" /></a></div><br />If you look at the documentation: <a href="https://docs.microsoft.com/en-us/power-bi/connect-data/service-dataset-details-page#visualize-this-data">Dataset details page - Power BI | Microsoft Docs</a>, it is rather unhelpful: <div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhMvuBTbMnk6OztirNL3Lw8dLaRBfnKHfe08zM5cEllPl6kryyavUKiO-2tTVbGLGFb98Bf26Nfl_G7lGMLiyMGG4vlRM8Sjqqb19-rP-bgT5iVRqQ5RSbgQPq7gT25sWrqFEWRv8Hm-t9Uhfp2WZ4Nol83Mt8pXVdGBIkXBNZgKzSRuyBnqk8" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="68" data-original-width="514" src="https://blogger.googleusercontent.com/img/a/AVvXsEhMvuBTbMnk6OztirNL3Lw8dLaRBfnKHfe08zM5cEllPl6kryyavUKiO-2tTVbGLGFb98Bf26Nfl_G7lGMLiyMGG4vlRM8Sjqqb19-rP-bgT5iVRqQ5RSbgQPq7gT25sWrqFEWRv8Hm-t9Uhfp2WZ4Nol83Mt8pXVdGBIkXBNZgKzSRuyBnqk8=s16000" /></a></div><br />It turns out that, there is some additional information a little earlier in that page: <a href="https://docs.microsoft.com/en-us/power-bi/connect-data/service-dataset-details-page#explore-related-reports">Dataset details page - Power BI | Microsoft Docs</a></div><div><br /></div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhghgxXmC0a-E--_dMQX-YdCL5pNuN339rrtR6TlUF8UijG6zn80mqAqKRdA-ITsXaQ8OWRUd3c0mNELhp51nL7rSrJUkaZsWaxjoXQ4VsQP8MZcYdw6ZidPhbjBd_iOYwLLDlczEI6gyQwkKAI_2zGViaoOEPXxsZptO38ap7mbJXxdgRxBws" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="54" data-original-width="864" src="https://blogger.googleusercontent.com/img/a/AVvXsEhghgxXmC0a-E--_dMQX-YdCL5pNuN339rrtR6TlUF8UijG6zn80mqAqKRdA-ITsXaQ8OWRUd3c0mNELhp51nL7rSrJUkaZsWaxjoXQ4VsQP8MZcYdw6ZidPhbjBd_iOYwLLDlczEI6gyQwkKAI_2zGViaoOEPXxsZptO38ap7mbJXxdgRxBws=s16000" /></a></div><br />So it turns out that if you create a report from your dataset and post fix the report name as "(template)", it will use that report as the template for the new report. This is extremely helpful, as you can now create a report with instructions and some sample pages that can help users get started. I LOVE THIS FEATURE!</div><div><br /></div><div><b>Note: (2022-8-23): </b>Unfortunately, this does not seem to work when you try and create a report directly (from outside the dataset details page).</div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEg0fmWywAxUeLKRqWwQLXPqvOGA-uQuDSNxtteyK2tXmiP_i8i2ekN-V8TBxQ-HFBZrO7gUV10mI1fHSIKYjFkZluDwT0CpU0XoFsz3ihI2ieiFHaiX72giis3yHdxP5VSO2xilPVTVQiSqVB_FoJVwjqxHE5xBTdABhE_NaYFwFPkp64QCl_M" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="214" data-original-width="423" height="162" src="https://blogger.googleusercontent.com/img/a/AVvXsEg0fmWywAxUeLKRqWwQLXPqvOGA-uQuDSNxtteyK2tXmiP_i8i2ekN-V8TBxQ-HFBZrO7gUV10mI1fHSIKYjFkZluDwT0CpU0XoFsz3ihI2ieiFHaiX72giis3yHdxP5VSO2xilPVTVQiSqVB_FoJVwjqxHE5xBTdABhE_NaYFwFPkp64QCl_M" width="320" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgUCYfUruLGjrqSI4OUyuEorPT9pHhsb3uIbstkSrbllC5t4-Z5QD11BqfVb9JcgelWBmPEysqYYdV9TPho__FXJMrHIwB_bCdOOQrGRnF627QJQ5tHYjlZbdD-RumIHZBHzvmDHJm5EOb-gffQxZ8sCZT5rrDy5MydSjR7isdW_1qQu5EYCb4" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="640" data-original-width="924" height="222" src="https://blogger.googleusercontent.com/img/a/AVvXsEgUCYfUruLGjrqSI4OUyuEorPT9pHhsb3uIbstkSrbllC5t4-Z5QD11BqfVb9JcgelWBmPEysqYYdV9TPho__FXJMrHIwB_bCdOOQrGRnF627QJQ5tHYjlZbdD-RumIHZBHzvmDHJm5EOb-gffQxZ8sCZT5rrDy5MydSjR7isdW_1qQu5EYCb4" width="320" /></a></div><br />The "Auto-create report" option does not work! I really wish this did as currently we provide access to our datasets via PowerBi apps and so users dont have access to the dataset details page. Ideally, when the pick the "auto-create report" option with a dataset, it should use the template associated with the dataset.<br /><br /></div>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-33428491794879196722022-07-27T17:30:00.003-06:002022-07-27T17:30:13.307-06:00Bash script and /bin/bash^M: bad interpreter: No such file or directory<p>If you get the error: "Bash script and /bin/bash^M: bad interpreter: No such file or directory", then you are likely trying to run a shell script (.sh) from your windows drive. Most likely, the issue is that your line-endings are CR+LF and not the Unix style LF.</p><p>If you need to fix this, the simplest way is to open the file in NotePad++ and change the line-endings (bottom right side of the status bar).</p><p>Another simple way to do this is to run the following script that runs regex to replace CR+LF with just LF.</p><p></p><blockquote>sed -i -e 's/\r$//' myfolder/*.*</blockquote><p></p><p>Another option, if you are using GIT is to add a .gitattributes file with line-endings set to LF.</p><p></p><blockquote>* text eol=lf</blockquote><p></p><p>Please note that if you do this, you may have to first delete all files in your repo (expect .git folder and .gitattributes and then run git reset to get the latest files again).</p><p>Finally, another option is to not download the files directly from your command prompt or windows explorer. Instead use your WSL terminal to download the files and they should come down with the correct line endings.</p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-73252765083569808292022-07-20T16:55:00.002-06:002022-07-20T16:55:28.537-06:00PowerBi PowerQuery - Convert Unix Timestamp to DateTime<p>The Unix timestamp represents the number of seconds since 1970-01-01. So to convert it to a DateTime in PowerQuery, use the following code:</p><p><br /></p><div style="text-align: left;"><blockquote> = Table.AddColumn(Source, "unixTimeStamp to DateTime", each #datetimezone(1970,1,1,0,0,0,0,0) + #duration(0,0,0,[unixtimestamp]))</blockquote></div>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-74166407729843656612022-06-07T17:48:00.006-06:002022-06-07T17:49:10.043-06:00Power Bi - Filtering by a list of values<p>When you need to filter a Power Bi report by multiple values and your users want to copy & paste the list of values by which they wish the report filtered, there isnt an out of the box way to do it in Power Bi.</p><p>I recently stumbled upon an AppSource visual called "<a href="https://appsource.microsoft.com/en-US/product/power-bi-visuals/insiderscoop1611244107840.powerbi_customviz_mass-filter" target="_blank">Mass Filter</a>" and it does exactly that!</p><p>And a quick video that shows you how to use it: <a href="https://www.youtube.com/watch?v=6nKYTFTA1h4&feature=youtu.be">Power Bi - Filter by Multiple Comma Separated Values - YouTube</a></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-31342505806301701942022-06-03T23:13:00.005-06:002022-06-06T16:16:55.734-06:00Understanding PowerBi Incremental Refresh with Detect Changes<p>Incremental refresh involves partitioning of the data and this concept isnt something that is dwelled upon a lot in the documentation. Because of what fields you use for partitioning can make or break incremental refresh, a lot of times, not understanding the underlying concepts can cause weird behavior later on. Most often the issue, is duplicate records or ghost records. These issues typically surface a months after the incremental refresh is implemented, as the time periods for the archived and incremental refresh periods are measured in multiple months.</p><p>Here is some typical account data that one might encounter in a CRM system. I will be using this data to illustrate how Incremental Refresh works.</p><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody><tr>
<td style="border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Id<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Name<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Created
Date<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Last
Modified Date<o:p></o:p></b></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">1<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Microsoft<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-01-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-01-02<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Sales Force<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-02-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-02-02<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">3<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Hitachi<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-03-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-03-01<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">4<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">NCM<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-04-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-04-05<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">5<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Tesla<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-05-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-06-03<o:p></o:p></p>
</td>
</tr>
</tbody></table><p><b>Picking the partitioning field</b></p><p>Incremental refresh works by storing the data in separate partitions. If you choose "months" then a partition is created for each month. These partitions are important for PowerBi as when data needs to be refreshed, the entire partition's data is reloaded. When a partition is older than the number of months you wish to archive, then that partition is dropped. The partitions are always created using a date column.</p><p>The first thing to remember is that one needs to pick a date-time column that <b>does not change (invariant)</b>. This means that one cannot use the <b>LastModifiedDate</b> column, which many systems capture, as that will change all the time. Typically, one will end up using the <b>CreatedDate</b> field, as that should never change once the record has been created. </p><p>Next you need to create 2 parameters named "RangeStart" and "RangeEnd". These need to be of type Date or Date/Time. You will use these 2 parameters to filter the above table by its CreatedDate. You can use any values you like for the RangeStart and RangeEnd on your computer, while developing your dataset, they will be reset upon the first refresh that is performed in PowerBi.com.</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEiTftW4ZLK73A8ypBPz5qoDAxICp9nVWIc_XDe_4d-ULpPQjHBhFZd2wpdbVX4tYuqjIyFZgWRpDqWqtQdmMC1b_gZNhXcOJqrqJ-5DXbu1EiUSz-kSPazuq3yx9irBpn1R0SwfECSBGogJS4TnYck-C1nIDxIP3mawp6SK3UXcojHCcJXiF_U" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="222" data-original-width="421" height="169" src="https://blogger.googleusercontent.com/img/a/AVvXsEiTftW4ZLK73A8ypBPz5qoDAxICp9nVWIc_XDe_4d-ULpPQjHBhFZd2wpdbVX4tYuqjIyFZgWRpDqWqtQdmMC1b_gZNhXcOJqrqJ-5DXbu1EiUSz-kSPazuq3yx9irBpn1R0SwfECSBGogJS4TnYck-C1nIDxIP3mawp6SK3UXcojHCcJXiF_U" width="320" /></a></div><br />This is what the formula looks like:<p></p><p>= Table.SelectRows(#"Reordered Columns", each <span style="background-color: #fcff01;">[createddate] >= RangeStart and [createddate] < RangeEnd</span>)</p><p>The next step is to setup the incremental refresh.</p><p></p><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEh164rI80PziLRrjbesbTQ7YUjRuJbxjd18OvEs_rr9r8jMITzro7HdDhS5s7-Wz6cTzMip61JKIh8zjKgJ4CAVSNL0mS3QHfq-6Yx2NzOXL0HRXG_6Q42K8Shwfh7iPvNZmUIFVJ8OVhcHUlsO2_27g09NomtOAaG39X4-u4kUx4n272p-z2U" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="1230" data-original-width="1422" height="346" src="https://blogger.googleusercontent.com/img/a/AVvXsEh164rI80PziLRrjbesbTQ7YUjRuJbxjd18OvEs_rr9r8jMITzro7HdDhS5s7-Wz6cTzMip61JKIh8zjKgJ4CAVSNL0mS3QHfq-6Yx2NzOXL0HRXG_6Q42K8Shwfh7iPvNZmUIFVJ8OVhcHUlsO2_27g09NomtOAaG39X4-u4kUx4n272p-z2U=w400-h346" width="400" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjtabB5lep9DXKRTcTREvw7hXJiap8sWt6wXR5WS3RGOMONwJZFNOZCmi3913fUal0ImdLsso7IBZLSFCS_npGUlAOgoaznYdmpmkIoEBZHjmYtbpEvGlobTYx7qhXYLvtyMoNhKYcsefRCRizevMp0aRAn158jHEj-lAfMlyIxBJlL9OVrYsU" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="190" data-original-width="1407" height="86" src="https://blogger.googleusercontent.com/img/a/AVvXsEjtabB5lep9DXKRTcTREvw7hXJiap8sWt6wXR5WS3RGOMONwJZFNOZCmi3913fUal0ImdLsso7IBZLSFCS_npGUlAOgoaznYdmpmkIoEBZHjmYtbpEvGlobTYx7qhXYLvtyMoNhKYcsefRCRizevMp0aRAn158jHEj-lAfMlyIxBJlL9OVrYsU=w640-h86" width="640" /></a></div><br /><br /></div><p>For this example, I am using Archive data set to <b>6 months</b> and Incrementally refresh data <b>starting 3 months</b> before refresh date. This will make it easier for me to illustrate the way incremental refresh works. You will likely use very different values in your production datasets. Take note of the fact that I have set the "detect data changes" option. This allows you to fine tune the incremental refresh even more (and a setting I think you should definitely use). Instead of refreshing all the partitions in the "Incremental Refresh" period, only those partitions that have changes are refreshed. But how does PowerBi know which partitions changed? You use a field that is updated anytime a record is updated (an audit date column). Typically in most systems this is a field called <b>LastModifiedDate.</b> PowerBi, keeps track of the max value of this field for each Incremental Refresh partition. The next time the dataset is refreshed, only those partitions are reloaded where the source data returns a higher LastModifiedDate than the one PowerBi captured the last time it ran. <b>SalesForce note</b>: you should use the <b>SysModStamp</b> column instead of the <b>LastModifiedDate</b> column, as even backend process changes will update the <b>SysModStamp</b> column, where as LastModifedDate is typically only updated by user based changes.</p><p>With the settings as defined above (3 months of incremental refresh and another 3 months of archived data, for a total of 6 months of data), on the very first refresh in PowerBi.com, we will see 3 + (3*2) queries getting fired off (3 for the archived months and 6 for incremental refresh months). The reason you see the additional set of queries for the incremental refresh is that PowerBi will send 2 queries per partition (in this case its by month). These queries will look like this:</p><p></p><p></p><blockquote>select max("systemmodstamp") as "C1" from ( select "systemmodstamp" from account where "createddate" >= timestamp '2022-01-01 00:00:00' and "createddate" < timestamp '2022-02-01 00:00:00' ) as "ITBL"</blockquote><p></p><blockquote><p>select "id", "name", "isdeleted", "createddate", "systemmodstamp" from account where "createddate" >= timestamp '2022-01-01 00:00:00' and "createddate" < timestamp '2022-02-01 00:00:00'</p><p></p></blockquote><p>The 1st query is on the "detect data changes" column (<b>sysmodstamp</b>) and gets the max value for that partition. The 2nd query gets the data for that partition. After this first refresh in PowerBi.com, all 6 months of data are loaded into the dataset.</p><p>Now on every subsequent refresh, PowerBi will first send similar queries as this for each partition that is part of "Incremental Refresh". In our case it will run the queries for the months of April, May, June.</p><p></p><blockquote>select max("systemmodstamp") as "C1" from ( select "systemmodstamp" from account where "createddate" >= timestamp '2022-04-01 00:00:00' and "createddate" < timestamp '2022-05-01 00:00:00' ) as "ITBL"</blockquote><p></p><p>PowerBi will then compare these max values to the previous max values it retrieved. PowerBi will only refresh those partitions for which the new max values are different from the previous max values on the detect-changes-column. By doing this, PowerBi can be even more efficient about which partitions it is reloading. If you had not used the detect-data-changes option, then for every subsequent refresh, PowerBi would reload all 3 "incremental refresh" partitions.</p><p>Now lets get back to the data we were loading. Initially this data was (I have added the partition each record would be loaded into, in this table). Lets assume this occurred on <b>6/3/2022</b></p><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody><tr>
<td style="border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Id<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Name<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Created
Date<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Last
Modified Date<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Partition<o:p></o:p></b></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">1<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Microsoft<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-01-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-01-02<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">January
(archived)<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Sales Force<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-02-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-02-02<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">February
(archived)<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">3<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Hitachi<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-03-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-03-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">March
(archived)<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">4<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">NCM<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-04-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-04-05<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Apr (Incr.
Refresh)<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">5<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Tesla<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-05-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-06-03<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">May (Incr.
Refresh)<o:p></o:p></p>
</td>
</tr>
</tbody></table><p></p><p>Lets assume the following changes happened on the <b>next day</b> (<b>6/4/2022</b>)</p><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody><tr>
<td style="border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Id<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Name<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Created
Date<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Last
Modified Date<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Partition<o:p></o:p></b></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">1<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Microsoft
(deleted)<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-01-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-06-04<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">January
(<span style="background-color: #fcff01;">archived</span>)<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">SalesForce
(SFDC)<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-02-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-02-02<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">February
(<span style="background-color: #fcff01;">archived</span>)<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">3<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Hitachi<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-03-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-03-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">March
(<span style="background-color: #fcff01;">archived</span>)<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">4<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">National
CineMedia<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-04-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-06-04<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Apr (<span style="background-color: #fcff01;">Incr.
Refresh</span>)<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">5<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Tesla/Twitter<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-05-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-06-04<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">May (<span style="background-color: #fcff01;">Incr.
Refresh</span>)<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">6<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 63pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Disney<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-06-04<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-06-04<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 99pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">June (<span style="background-color: #fcff01;">Incr.
Refresh</span><o:p></o:p></p>
</td>
</tr>
</tbody></table><p>In the above case, because rows 1 and 2 are in archived partitions they will not be reloaded. This can be a problem if you support soft-deletes, as now your report potentially has 1 additional record than the source (as row 1 was soft-deleted in the source system). The name change from row 2 will also not show up in your report. The only changes that will be loaded are the changes to the rows 4, 5 and 6.</p><p>Finally, when July rolls around, the January partition will be dropped entirely from the dataset and a July Partition will be added.</p><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody><tr>
<td style="border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Id<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 70.45pt;" valign="top" width="94">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Name<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 69.05pt;" valign="top" width="92">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Created
Date<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Last
Modified Date<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 121.5pt;" valign="top" width="162">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Partition<o:p></o:p></b></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><s>1<o:p></o:p></s></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 70.45pt;" valign="top" width="94">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><s>Microsoft
(deleted)<o:p></o:p></s></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 69.05pt;" valign="top" width="92">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><s>2022-01-01<o:p></o:p></s></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><s>2022-06-04<o:p></o:p></s></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 121.5pt;" valign="top" width="162">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><s>January
(archived)<o:p></o:p></s></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><span style="background: yellow; mso-highlight: yellow;">Partition is dropped</span><o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 70.45pt;" valign="top" width="94">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">SalesForce
(SFDC)<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 69.05pt;" valign="top" width="92">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-02-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-02-02<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 121.5pt;" valign="top" width="162">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">February
(archived)<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">3<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 70.45pt;" valign="top" width="94">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Hitachi<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 69.05pt;" valign="top" width="92">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-03-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-03-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 121.5pt;" valign="top" width="162">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">March
(archived)<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">4<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 70.45pt;" valign="top" width="94">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">National
CineMedia<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 69.05pt;" valign="top" width="92">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-04-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-06-04<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 121.5pt;" valign="top" width="162">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Apr (<span style="background-color: #ead1dc;">archived</span>)<o:p></o:p></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><span style="background: yellow; mso-highlight: yellow;">Partition type changed</span><o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">5<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 70.45pt;" valign="top" width="94">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Tesla/Twitter<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 69.05pt;" valign="top" width="92">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-05-01<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-06-04<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 121.5pt;" valign="top" width="162">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">May (Incr.
Refresh)<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">6<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 70.45pt;" valign="top" width="94">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Disney<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 69.05pt;" valign="top" width="92">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-06-04<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-06-04<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 121.5pt;" valign="top" width="162">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">June (Incr.
Refresh<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">7<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 70.45pt;" valign="top" width="94">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">Paramount
Studios<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 69.05pt;" valign="top" width="92">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-07-04<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-07-04<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 121.5pt;" valign="top" width="162">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">July (<span style="background-color: #ead1dc;">Incr.
Refresh</span>)<o:p></o:p></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><span style="background: yellow; mso-highlight: yellow;">Partition is added</span><o:p></o:p></p>
</td>
</tr>
</tbody></table><p><b>Incremental Refresh and CRM data:</b></p><p>When you use "CreatedDate" as your partition field, one thing you have to think about is what period of data you need refreshed. Imagine if you set up your Incremental Refresh to be 12 months and Archive period to be 24 months. On an entity like "Account", sometimes accounts may get created and may not get sold to or touched for a long time. So, if the account was created more than 12 months ago, then when it gets finally used and if any changes were to occur on that entity, you would not see those changes. In the above example, Microsoft is no longer even in the dataset, as its created date is outside the archival range. If Microsoft had an order on 2022-06-04, you would see the order's details, but the order would not have the account details, as its no longer being loaded.</p><p>For this reason, one needs to know what is the maximum time period between last-modified on an entity and its created date. You would then have to setup your refresh settings based on the usage statistics of the entities at your company. This is also one of the biggest reasons that it is very hard to setup an incremental refresh with CRM data.</p><p>When it comes to Order/Opportunity data, you have a little more lee-way. Typically one should use a field like "Order-Date" and not something like "Shipped-Date", as the latter could change. Also, you could institute a rule that states that orders/opportunities that are older than x months should be closed and a new one created.</p><p><b>What would happen if you use a partition column that might change</b></p><p>Lets take the following data as an example:</p><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody><tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Id<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 63.0pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Order Name<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Order Date<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 99.0pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Last
Modified Date<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 99.0pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Order
Value<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 99.0pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Partition<o:p></o:p></b></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">1<o:p></o:p></p>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 63.0pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">My Big Order<o:p></o:p></p>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2021-01-01<o:p></o:p></p>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 99.0pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-01-02<o:p></o:p></p>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 99.0pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">$1 million<o:p></o:p></p>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 99.0pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">January<o:p></o:p></p>
</td>
</tr>
</tbody></table><br />If we were to have a refresh setting where the partition field were set to "Order Date" and where we incrementally refresh data in the last 12 months and archive another 12 months of data. The above order would have be in an "archived" partition. Now if the Order Date were to be changed to 2022-06-01:<div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody><tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Id<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 63.0pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Order Name<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Order Date<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 99.0pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Last
Modified Date<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 99.0pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Order
Value<o:p></o:p></b></p>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 99.0pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><b>Partition<o:p></o:p></b></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 22.25pt;" valign="top" width="30">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">1<o:p></o:p></p>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 63.0pt;" valign="top" width="84">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">My Big Order<o:p></o:p></p>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 76.5pt;" valign="top" width="102">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-06-01<o:p></o:p></p>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 99.0pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">2022-06-01<o:p></o:p></p>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 99.0pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">$1.5 million<o:p></o:p></p>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 99.0pt;" valign="top" width="132">
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0in;">January<o:p></o:p></p>
</td>
</tr>
</tbody></table><br />This same record would be loaded into the "Incremental Refresh" partition. So you would have 2 copies of the same order in your dataset and your order value would be over stated by $1 million. One could try and de-dup this data, but PowerBi is very inefficient at de-duping large record sets. Also, your algorithm would have to be smart, as you would want to keep the latest version of the order.<br /><p><b>Important things to remember:</b></p><p></p><ol style="text-align: left;"><li>The incremental refresh column must be a date/time column. You can use an integer column but it needs to be of type yyyymmdd (so you cant use any arbitrary integer column).</li><li>Hard deletes cannot be handled by incremental refresh. You can use it only if your entity implements soft-deletes (i.e., uses a flag to denote a delete). So if your system performs hard-deletes, then you are SOL!</li><li>Make sure that query folding is supported by your data-source and that it is occurring, otherwise, you will end up pulling multiple copies of the data from the data-source and end up considerably slowing down your refresh and possibly inviting the wrath of your data-source administrators. (Don't ask me how I know!)<br /><a href="https://blogger.googleusercontent.com/img/a/AVvXsEg0C4fMJOmUWsG1m6YIhWSq5bLk67RRIZTh6J204-85mgaUNMaVyUsg8IOHJP-BAiK1INtil4vqcj3TrYXrtlNdCgiEtn2XqnkIQon_4AjVGrdFVS8isJ6pTTbMN5tFQbLRSmfM9iSNKF7E3FsdxYN9OiwL6Z5C3cX_5V5NuwG_sVXczwZmE84" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img alt="" data-original-height="56" data-original-width="569" src="https://blogger.googleusercontent.com/img/a/AVvXsEg0C4fMJOmUWsG1m6YIhWSq5bLk67RRIZTh6J204-85mgaUNMaVyUsg8IOHJP-BAiK1INtil4vqcj3TrYXrtlNdCgiEtn2XqnkIQon_4AjVGrdFVS8isJ6pTTbMN5tFQbLRSmfM9iSNKF7E3FsdxYN9OiwL6Z5C3cX_5V5NuwG_sVXczwZmE84=s16000" /></a></li></ol><p><b>Some Tips and Notes:</b></p><p></p><ol style="text-align: left;"><li>If you implement incremental refresh in your dataset, then know that you will not be able to download your PBIX file from powerbi.com. Plan for this ahead of time. Another important consideration is that if you implement incremental refresh in your dataset and you need to make model changes, then the partitions will be deleted and the entire data reloaded. This is another reason to not implement incremental refresh directly in your dataset.</li><li>If you do need to implement incremental refresh in your dataset, then consider:</li><ol><li>Storing your PBIX file in a shared location and have an agreed upon method for your team to work on that file. I highly recommend using Sharepoint for this.</li><li>You can publish your changes using a tool like "ALM Toolkit" This will not cause your dataset to loose its changes. You can also use the deployment pipeline to push your changes in PowerBi and again this will not delete your existing partitions.</li></ol><li>Often times you need to implement incremental refresh because your source is very slow. In this case, you could implement the incremental refresh as a dataflow. You would pull the dataflow into your dataset. In this case, you may not have to implement incremental refresh in your dataset, as the data may load fast enough from the data-flow into your dataset.</li><li>PowerBi will setup a partitions based on your incremental refresh settings. When powerbi refreshes data, it is refreshing the entire partition (i.e., it will delete the partition and reload the data from the data-source). So you need to carefully consider the incremental refresh settings and figure out how many rows on average might fall in a partition. If you partition by year and each year has a billion rows, then you might want to consider partitioning by month.</li></ol><p></p></div>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-15716644426975489482022-06-02T21:06:00.006-06:002022-06-03T20:14:39.397-06:00Error Correction using PID (Proportional Integral Derivative) - The Math<p>PID or Proportional Integral Derivate is used often times when you need an automated system to self correct. Think for example a robot that has to drive straight or follow a line, or self balance.</p><p><b>Driving or Flying Straight</b>: In this case the robot needs to keep to a certain heading or angle. Various forces might make it deviate from that heading (wind, wheel slip or friction, etc). The difference between the target heading and the actual heading is the error and PID can be used to correct that heading.</p><p><b>Following a line</b>: In line following the robot attempts to follow a dark line to its target. In this case, typically a sensor that senses reflected light is used. The robot attempts to keep the reflected light within a certain range and tries to correct its direction to keep tracking the line. The difference between the amount of reflected light sensed and target reflected light is the error value and used in self correcting the direction the robot is travelling.</p><p><b>Self Balancing Robot: </b>Think about a segway like robot that attempts to self balance using 2 motors. In this case the robot's target orientation is upright and the error is the angle from the vertical plane. The robot then attempts to self correct by applying a force towards the vertical plane. PID again provides the amount of self correcting needed. </p><p>PID is most important in the self balancing example, as one cannot use a constant amount of correction, as the robot would likely topple. This is because while the robot is attempting to balance, the amount of deviation from the vertical plane will vary randomly.</p><p><b>Quad Copter: </b>This is probably the most intuitive applications to work with. Think of a quad copter on the ground. You wish to have it take off and hover at a certain height (eg: 10 feet). When the quad copter first takes off, its altitude sensor will start at 0 feet and the error is 10 feet. The quad copter increases its propeller speed and starts its journey to 10 feet. It will invariably overshoot the target altitude (say 11 feet), so it will have to slow down its propeller to return back to its target of 10 feet. Wind and other factors will keep moving the copter away from its target altitude and so, as the sensor returns its altitude readings, the quad copter will have to continuously change the propeller speed to increase or decrease its altitude.</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgtUmltK9NJnUN7vpIAthS9VDY7EGtXSrFx1cmQEbz1-8RtOhAlDhJUn0cYpjwjF4Z_7vjNUl89JtydiEznx_9OiPrTWI293mVcd8v2aAElLQTW-Ko3HRO6CmXdNNDqzVxAzfKZwLcrf5_rDuge9y7wkt7BEmXzK_4G5ApLdmX1yY4FIGzG_M0" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="690" data-original-width="912" height="303" src="https://blogger.googleusercontent.com/img/a/AVvXsEgtUmltK9NJnUN7vpIAthS9VDY7EGtXSrFx1cmQEbz1-8RtOhAlDhJUn0cYpjwjF4Z_7vjNUl89JtydiEznx_9OiPrTWI293mVcd8v2aAElLQTW-Ko3HRO6CmXdNNDqzVxAzfKZwLcrf5_rDuge9y7wkt7BEmXzK_4G5ApLdmX1yY4FIGzG_M0=w400-h303" width="400" /></a></div><br />PID is again a better algorithm to use for a quad-copter trying to attain a target height, compared to a constant correction amount, as it will allow it to get to its target height and maintain it better. <p></p><p>In a PID based system, the math is trying to reduce the error at every calculation step (or loop) and bring it to its target value. The systems typically never ever reaches the their target value and continuously fluctuates around the target value.</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgmylbYRlYMbuh3Nx8Tt-Q15Cj6Au0y-R8QVIip8b7AxJMD0KON214ZBvQgnj_BkhPv-IkBItgNc3gZ1DiB2aJ16JSom_HnKfyB3iv4nPND3N9E0owKb-9_6vuh5CcTMcP-nEc-YGIOeVOaHlSRPV2gI3mx3bdT9CYz6Y6S2J7yZgcXFaddBJk" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="560" data-original-width="859" height="261" src="https://blogger.googleusercontent.com/img/a/AVvXsEgmylbYRlYMbuh3Nx8Tt-Q15Cj6Au0y-R8QVIip8b7AxJMD0KON214ZBvQgnj_BkhPv-IkBItgNc3gZ1DiB2aJ16JSom_HnKfyB3iv4nPND3N9E0owKb-9_6vuh5CcTMcP-nEc-YGIOeVOaHlSRPV2gI3mx3bdT9CYz6Y6S2J7yZgcXFaddBJk=w400-h261" width="400" /></a></div><br /> <p></p><p>Even though PID uses fancy words like integral and derivate, the math itself is very simple.</p><p>Lets go through PID math by its various components:</p><p><b>Proportional</b></p><p>One can use only the proportional part of PID (with I = 0 and D = 0) in many simple systems (eg: line following robot). The job of this component of the PID math is to provide a correction towards the target value.</p><p>Kp: constant of proportion (usually called the "proportional gain constant")</p><p>SP: Set Point (or the target value we want on the sensor)</p><p>PV(t): The process value at time t (or the current sensor value)</p><p>E(t) = SP - PV(t) : This is the error at any given time t. (or the error in the graph above).</p><p>The proportional value P(t) is calculated as <b>Kp * E(t)</b> or <b>Kp * (SP - PV(t))</b>.</p><p>P(t) <span> </span>= Kp * E(t) </p><p><span><span>P(t)</span><span> </span> </span>= Kp * (SP - PV(t))</p><p>In other words its the error multiplied by a certain tuning factor (Kp). Kp can be used to magnify or dampen the effect of the error on the driving components.</p><p>In the diagram below, its Kp * 9ft (where Kp could be any value that you pick, other than 0!). If Kp = 1, then the value would be 9.</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEg_jkEdVnsv5JYskDGA3W3hVd_lmiJh4wfac9XRWvH63kaWUH6s3l77LpymO-TGzckPKFtXnL3Pyi_4Y_m6ptZTCIKQogE1fTFCR0GDo5uEf6qIFEX6PR90L0TfLp5emGZBaPRmmG-aTMjI33LPOYMwecsFi7ibpnOYJDvkEZX9wG3DkhlYoEE" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="641" data-original-width="257" height="400" src="https://blogger.googleusercontent.com/img/a/AVvXsEg_jkEdVnsv5JYskDGA3W3hVd_lmiJh4wfac9XRWvH63kaWUH6s3l77LpymO-TGzckPKFtXnL3Pyi_4Y_m6ptZTCIKQogE1fTFCR0GDo5uEf6qIFEX6PR90L0TfLp5emGZBaPRmmG-aTMjI33LPOYMwecsFi7ibpnOYJDvkEZX9wG3DkhlYoEE=w160-h400" width="160" /></a></div><br /><br /><p></p><p><b>Integral</b></p><p>The job of the integral is make larger corrections if the Proportional component is not providing enough of a correction. It accumulates the errors and applies it to the correction. Again a Integral gain constant is used to dampen or magnify the effect of this component.</p><p>Ki: integral gain constant</p><p>I(t) = Ki * E(t) + I(t-1)</p><p>Where I(t-1) is the I(t) that was calculated in the previous cycle. If the terms (t-1) and (t) are confusing, you can also write this as:</p><p>I = Ki * E + Iprev</p><p></p><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgAJLCPJXYCH8kxjlDwq7NmX78iV7-rRWVNA9g6VtofDsrstt6TkJkj4Bwq_fN2wDjPqbJBVgQx0Ve7iszwnLPpJl2hk7MSSWeFY1ihGn5NWswliE-UIR67F9qi1H30o6M74mnVgy4AAaCSywpKJR42nsQwNMuYmA4Pg5hMati1Y5fPqkWekUE" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="679" data-original-width="323" height="400" src="https://blogger.googleusercontent.com/img/a/AVvXsEgAJLCPJXYCH8kxjlDwq7NmX78iV7-rRWVNA9g6VtofDsrstt6TkJkj4Bwq_fN2wDjPqbJBVgQx0Ve7iszwnLPpJl2hk7MSSWeFY1ihGn5NWswliE-UIR67F9qi1H30o6M74mnVgy4AAaCSywpKJR42nsQwNMuYmA4Pg5hMati1Y5fPqkWekUE=w190-h400" width="190" /></a></div><br /><br /></div><br />In the above diagram if Ki = 1, then at time = 1:<p></p><p>I = 1 * 9 + 0 = 9 <span> (0, as I starts at 0)</span></p><p>at time = 2, (now I = 9)</p><p>I = 1 * 7 + 9 = 16</p><p><b>Derivate</b></p><p>The job of the derivate is to slow down the effect of the Proportional and Integral components. It attempts to minimize overshoot that can happen as the previous two components attempt to bring the system to the target value. From the graph above its the difference in value of the 2 error values shown above at (t-1) and t.</p><p>Kd: derivate gain constant</p><p>D(t) = Kd * (E(t) - E(t-1))</p><p>or alternatively:</p><p>D = Kd * (E - Eprev)</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhl2x1aPq4N7lZj6-JbAlo1gXvJT9wOMUeoLw0z-VprCXj5mcTaHR5cv6L6ZltsuXeVTKVzhusMGsy_BAyvuXcPGUIOaFUH59cvOoB8V4VyDuJbumJY8D9lR7AM9w_CE8oDs_4bui6IFyYt2p_8kishIcsiH63czew4nBkFwkh9XZdoVkdSXek" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="683" data-original-width="401" height="400" src="https://blogger.googleusercontent.com/img/a/AVvXsEhl2x1aPq4N7lZj6-JbAlo1gXvJT9wOMUeoLw0z-VprCXj5mcTaHR5cv6L6ZltsuXeVTKVzhusMGsy_BAyvuXcPGUIOaFUH59cvOoB8V4VyDuJbumJY8D9lR7AM9w_CE8oDs_4bui6IFyYt2p_8kishIcsiH63czew4nBkFwkh9XZdoVkdSXek=w235-h400" width="235" /></a></div><br />In the above example, if Kd = 1, then at t = 1:<p></p><p>D = 1 * (9-0) = 9</p><p>at t=2, E(t-1) or Eprev = 9:</p><p>D = 1 * (7-9) = 1 * -2 = -2</p><p><b>Putting it all together:</b></p><p>C(t) = P(t) + I(t) + D(t)<span> where C is the correction.</span></p><p><span>C(t) = Kp*E(t) + (Ki*E(t) + Ki*I(t-1)) + Kd*(E(t) - E(t-1))</span></p><p><span>Going back to our quad-copter example, this is what its flight might have looked like</span></p><p><span></span></p><div class="separator" style="clear: both; text-align: center;"><span><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhfcMV5zWbto0PwScXGgZC6YbRHzpcBvSsFYpCl9YUl1HVlkWtu26RPegBMzf5ec6LyZ8UzU8rhVf-I-YF2smcLhS19VcdvDIpa3_XldL5klpjgoy5pFx-aQsjeiGDrQfecspbYWB6GhEZ7YFCd04_1Sk7Re8UxzQw3NBT8OOKBiWwGnOO_lE4" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="261" data-original-width="535" src="https://blogger.googleusercontent.com/img/a/AVvXsEhfcMV5zWbto0PwScXGgZC6YbRHzpcBvSsFYpCl9YUl1HVlkWtu26RPegBMzf5ec6LyZ8UzU8rhVf-I-YF2smcLhS19VcdvDIpa3_XldL5klpjgoy5pFx-aQsjeiGDrQfecspbYWB6GhEZ7YFCd04_1Sk7Re8UxzQw3NBT8OOKBiWwGnOO_lE4=s16000" /></a></span></div><span><br /><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEj08pDsevgSQPjIOdRofryzbbx9XMTHF42F_iwgGfPpEBOgBRkGDJP0-B9Fl4atiUAK9MT-dKdE9uMk-5beJ5dFTGajXms728mHZLzFbCVw5hFBB9Aztk0HsBrjWpf9_Unia5vwFeqCoKywog-IEfxwPIlYoPo2_cpwilyZ4LbByiLC31N9YyA" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="452" data-original-width="752" height="240" src="https://blogger.googleusercontent.com/img/a/AVvXsEj08pDsevgSQPjIOdRofryzbbx9XMTHF42F_iwgGfPpEBOgBRkGDJP0-B9Fl4atiUAK9MT-dKdE9uMk-5beJ5dFTGajXms728mHZLzFbCVw5hFBB9Aztk0HsBrjWpf9_Unia5vwFeqCoKywog-IEfxwPIlYoPo2_cpwilyZ4LbByiLC31N9YyA=w400-h240" width="400" /></a></div><br /></div></span><p></p><p><span>In fancy math terms this is the PID equation:</span></p><p><span></span></p><div class="separator" style="clear: both; text-align: center;"><span><a href="https://blogger.googleusercontent.com/img/a/AVvXsEh2HFcs2kLUzWIe86Nj5pEg4ChaKpNqNq0P_lpXX0BLjs8VCSVKN-JLdjKMige-s06jFmY26b3IsoaR2SASRAbJU1M-pOmLYHx_oEH8LYb49ZAxvzt-era0DNJVOm17S-2IIlddJXSWK6abkjEe8M9BWzFdtrFa1vmKeKkraJirIBogTtXS86U" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="49" data-original-width="344" src="https://blogger.googleusercontent.com/img/a/AVvXsEh2HFcs2kLUzWIe86Nj5pEg4ChaKpNqNq0P_lpXX0BLjs8VCSVKN-JLdjKMige-s06jFmY26b3IsoaR2SASRAbJU1M-pOmLYHx_oEH8LYb49ZAxvzt-era0DNJVOm17S-2IIlddJXSWK6abkjEe8M9BWzFdtrFa1vmKeKkraJirIBogTtXS86U=s16000" /></a></span></div><span>The first term is <b>P</b>roportional component, 2nd is <b>I</b>ntegral and 3rd term is <b>D</b>erivative component.<br /><br /></span><p></p><p><span><b>And as an Algorithm:</b></span></p><p></p><ol style="text-align: left;"><li><span style="font-family: courier;">Kp = 1, Ki = 1, Kd = 1<span> (where the values can be different and tuned for your system)</span></span></li><li><span style="font-family: courier;">Target = 100 (this is the set-point. This the target value you wish your system to achieve)</span></li><li><span style="font-family: courier;">CurrentValue = 0 (this is the current sensor measurement or Process value - PV)</span></li><li><span style="font-family: courier;">ErrPrev = 0</span></li><li><span style="font-family: courier;">I = 0</span></li><li><span style="font-family: courier;">while true</span></li><ol><li><span style="font-family: courier;">Err = Target - CurrentValue(from Sensor)</span></li><li><span style="font-family: courier;">P = Kp * Err</span></li><li><span style="font-family: courier;">I = Ki * Err + I</span></li><li><span style="font-family: courier;">D = Kd * (Err - ErrPrev)</span></li><li><span style="font-family: courier;">ErrPrev = Err</span></li><li><span style="font-family: courier;">C = P + I + D</span></li><li><span style="font-family: courier;">Do something with C (turn your robot by C degrees, etc).</span></li></ol></ol><div>The above loop is typically continuously evaluated until a certain condition is met (quad-copter has been asked to return home, or robot has reached its target or robot has been running for x minutes, etc).</div><div><br /></div><p><br /></p><span><a name='more'></a></span><p><a href="https://www.flaticon.com/free-icons/camera-drone" rel="nofollow" target="_blank">Camera drone icons created by DinosoftLabs - Flaticon</a></p><p><br /></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-19457855802877656692022-06-01T21:22:00.005-06:002022-06-05T09:06:41.554-06:00Lego 51515 - Tricky - Understanding the line following code<p>One of the activities one can code with the Tricky bot is the line follower. But how does the line following code work?</p><p>Here is the line following code that tricky uses:</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjK7PHD7ExPStz-7tCbCre39uUubn0beSWFzTHtCx5k6tuMeLQWLgJrXnlENSu4-xl1KaQYy2dgsTYjN-IIiDq8ZjhQ85HfjGuWke2ttMJprbKFcw4Raz4SxCBxYFBekj9Wt0Y9TSf5FEl2hrTEnBM9uMdKC0YuzOSbbsh4RmYnKpzNX3MvRDg" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="113" data-original-width="509" src="https://blogger.googleusercontent.com/img/a/AVvXsEjK7PHD7ExPStz-7tCbCre39uUubn0beSWFzTHtCx5k6tuMeLQWLgJrXnlENSu4-xl1KaQYy2dgsTYjN-IIiDq8ZjhQ85HfjGuWke2ttMJprbKFcw4Raz4SxCBxYFBekj9Wt0Y9TSf5FEl2hrTEnBM9uMdKC0YuzOSbbsh4RmYnKpzNX3MvRDg=s16000" /></a></div><br />The first line is controls the loop execution and run the line following code until the sensor senses the green piece that is part of the basket.<p></p><p>But what is the the code within the loop doing? In a word: Propotional Line Following Algorithm (ok 4 words!)</p><p>Lets break down the code into its various bits:</p><p></p><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto;"><tbody><tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEi0PETShYh9Pmr4DnnPbjcZOYkn5qL3lqMDCs3Y43q9UJE9A2B3WAEtXzcxoMhyKYIzSDFN6Smzs202YVKq0wyWbzp4MgStRfz-cMzghxaA4YwrxH0d9IbVGUlgAmimbGke6INEetrRlmeAKEoHLU2THGbbbUCq5b4ZdUolhTWxrP8IGPsv0AE" style="margin-left: auto; margin-right: auto;"><img alt="" data-original-height="188" data-original-width="343" src="https://blogger.googleusercontent.com/img/a/AVvXsEi0PETShYh9Pmr4DnnPbjcZOYkn5qL3lqMDCs3Y43q9UJE9A2B3WAEtXzcxoMhyKYIzSDFN6Smzs202YVKq0wyWbzp4MgStRfz-cMzghxaA4YwrxH0d9IbVGUlgAmimbGke6INEetrRlmeAKEoHLU2THGbbbUCq5b4ZdUolhTWxrP8IGPsv0AE=s16000" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">the various bits in that one line of code. order of operations is bottom up</td></tr></tbody></table><br /><p></p><p></p><ol style="text-align: left;"><li><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgn2k90_orKWVw71dIvs8ky1GQ6poUYsxh1wTO8BjHXnG5cKlLaMMvgQW3iL_rTtFf16REtqb-zj8A1E6RNWqZ815PV8Ksjrr2hA2xcozQnatahb4kMF8ZHEyaXVkAfY2s6iLxXtcY6eywLx9KzRCbrSL5sZl7ckQEd_z8dHFIo0yjGpAFGKjI" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="40" data-original-width="177" src="https://blogger.googleusercontent.com/img/a/AVvXsEgn2k90_orKWVw71dIvs8ky1GQ6poUYsxh1wTO8BjHXnG5cKlLaMMvgQW3iL_rTtFf16REtqb-zj8A1E6RNWqZ815PV8Ksjrr2hA2xcozQnatahb4kMF8ZHEyaXVkAfY2s6iLxXtcY6eywLx9KzRCbrSL5sZl7ckQEd_z8dHFIo0yjGpAFGKjI=s16000" /></a></div><br />The code uses the sensor in "reflected light" mode. In this mode, the color sensor reports back the amount of light being reflected back to the sensor. Remember dark colors reflect less light compared to light colors.</li><li><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjOtmX3npkOOZzacQZAQJvNylt_LxsdayjPvWXNhzv-Ic13ibMoaSZ9R6m8wOcU2PgIQ_MlVROyBjC7Ozd3-Rm1VXsHaMoqXz5_DcUW_g0eYfmnQ8DEqQLdwlKwQmyt4tRVVdcinQLzVjsVY-61qffKsfT2vlAnpE-SgKpVRIIRLrx-oJylhCk" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="41" data-original-width="84" src="https://blogger.googleusercontent.com/img/a/AVvXsEjOtmX3npkOOZzacQZAQJvNylt_LxsdayjPvWXNhzv-Ic13ibMoaSZ9R6m8wOcU2PgIQ_MlVROyBjC7Ozd3-Rm1VXsHaMoqXz5_DcUW_g0eYfmnQ8DEqQLdwlKwQmyt4tRVVdcinQLzVjsVY-61qffKsfT2vlAnpE-SgKpVRIIRLrx-oJylhCk=s16000" /></a></div>Next up is the subtract block. The sensor value is one of the operands and the other operand here (the 60) is the target value.<br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEj7GUsv800tuzJcl1kNGvZGY8SPgmRWO6e-M_0apQhOZfDTNyI89ydjhqw9ywmRhD4by_kZISUXIc4pEBQD7BvI6QVZymMCN1rVuP8MXyStWz_snrBB3K9x6l9QPrz3keHGdoNr-ijbT3s1E2RhgAS2NkM9fg5yg3mQW7d604OFJJBgDt3Lvk0" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="60" data-original-width="235" src="https://blogger.googleusercontent.com/img/a/AVvXsEj7GUsv800tuzJcl1kNGvZGY8SPgmRWO6e-M_0apQhOZfDTNyI89ydjhqw9ywmRhD4by_kZISUXIc4pEBQD7BvI6QVZymMCN1rVuP8MXyStWz_snrBB3K9x6l9QPrz3keHGdoNr-ijbT3s1E2RhgAS2NkM9fg5yg3mQW7d604OFJJBgDt3Lvk0=s16000" /></a></div><br />The idea here is that we want the robot to try and get to the target reflected light value of 60. If it senses more, than it needs to reduce it and if its less, then it needs to increase it. One can call this the "error" value.</li><li><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjGOQYbZwNGYb_3tHnnT-FTQ3z3_LexHULRcbPjYr1XvVC8cRdWER0XQ9gHXs3olHPyuL0JQBHSxae3YiT_sKxZnsu6ROSo7V9PYYux_LXlgWPbFOGFUg1tXIEg5qOcZZy0U8ZmER71mJfqUv8Em0BUnCORJVCgsx2qSYchXxm42pbRVLOasi4" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="38" data-original-width="82" src="https://blogger.googleusercontent.com/img/a/AVvXsEjGOQYbZwNGYb_3tHnnT-FTQ3z3_LexHULRcbPjYr1XvVC8cRdWER0XQ9gHXs3olHPyuL0JQBHSxae3YiT_sKxZnsu6ROSo7V9PYYux_LXlgWPbFOGFUg1tXIEg5qOcZZy0U8ZmER71mJfqUv8Em0BUnCORJVCgsx2qSYchXxm42pbRVLOasi4=s16000" /></a></div><br />The next block is a multiply block. It multiplies the "error" by a constant (in this case 2). One can consider this entire expression upto here, as the "correction value". Here is the complete line:<br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgnpVWdq2M3QYcVcMsmUM8-zN19v1QbiCa9XMdwLdxXVsdi74IGcvrzORrDsGApK-nRLur7yMcsMX-7DgVpvFq2UvZ5LbESOOOKuYtHW6A8skDYSVStCFpyk0n_QAHD0hAm5UCPyRSSZg6XFYT0qK_HOo04t9aIHRIKkVPOb1583b5rIs0eWME" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="62" data-original-width="280" height="71" src="https://blogger.googleusercontent.com/img/a/AVvXsEgnpVWdq2M3QYcVcMsmUM8-zN19v1QbiCa9XMdwLdxXVsdi74IGcvrzORrDsGApK-nRLur7yMcsMX-7DgVpvFq2UvZ5LbESOOOKuYtHW6A8skDYSVStCFpyk0n_QAHD0hAm5UCPyRSSZg6XFYT0qK_HOo04t9aIHRIKkVPOb1583b5rIs0eWME" width="320" /></a></div><br /></li><li><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjSn14zCRGRuOiIXHkyoUcDzkVE5drQhAsjdVSa42Y9ZlAZLMJNfEzlIW2a9s8vlsIxJmXrn1H73BtNR9cV4uaRzDQrKAlUXsFlpxApRtkqLSP38yP1ZUudhCbYxBpWfYojuRpajrCJGn4H0m_7JC1YezUHylgQ_Qs1re2WlZyKL3XVxvyhGyQ" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="232" data-original-width="296" src="https://blogger.googleusercontent.com/img/a/AVvXsEjSn14zCRGRuOiIXHkyoUcDzkVE5drQhAsjdVSa42Y9ZlAZLMJNfEzlIW2a9s8vlsIxJmXrn1H73BtNR9cV4uaRzDQrKAlUXsFlpxApRtkqLSP38yP1ZUudhCbYxBpWfYojuRpajrCJGn4H0m_7JC1YezUHylgQ_Qs1re2WlZyKL3XVxvyhGyQ=s16000" /></a></div><br />Next up is the "start moving with steering at power" block. It will move the robot at the specified power (35%) and at the angle defined in the first field. The value we will use for the angle is the "correction value" that we calculated previously. This is what the final line looks like:<br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjLW0JVntxLWcxRSNxTZ63WQ6O8qLnfycsz2K5nWVRm5GUwcAleLP5f-86_uJVDAGPndeOMv1UEciLSPL7__KMklswNezvkKshzmSrP0Gd2dUMyqdU8N9tyXUULQu-fwqIMHmE9XWspM6rla6C95Y25MC2sl-c8DRUAqS0SF8YvjG8IwPGZFCw" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="63" data-original-width="489" src="https://blogger.googleusercontent.com/img/a/AVvXsEjLW0JVntxLWcxRSNxTZ63WQ6O8qLnfycsz2K5nWVRm5GUwcAleLP5f-86_uJVDAGPndeOMv1UEciLSPL7__KMklswNezvkKshzmSrP0Gd2dUMyqdU8N9tyXUULQu-fwqIMHmE9XWspM6rla6C95Y25MC2sl-c8DRUAqS0SF8YvjG8IwPGZFCw=s16000" /></a></div></li></ol><div><b>How does it all work?</b></div><div>First lets look at how start moving block works. The first input to the block is the direction in which the robot is supposed to move. The values it can take are -100 to 100 (negative values = Left, positive values = Right, 0 = Straight).</div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgPyQhbKauQ1EzpMfEChpAh_j11bW1Lfav_jivJISapY3qa5UxTvmASAAIB_49ABXydcpZ8vx3gEzMIzz3QOuW7dmnkmSAd5IJe-pULoa8OvbX6Bge9Cdt-RiSRSXTVKfwnIvhK1ti59gLOYu5HGQZuZmJpUhdb4AjERaZK8NKyE9GeC8QQueA" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="589" data-original-width="588" src="https://blogger.googleusercontent.com/img/a/AVvXsEgPyQhbKauQ1EzpMfEChpAh_j11bW1Lfav_jivJISapY3qa5UxTvmASAAIB_49ABXydcpZ8vx3gEzMIzz3QOuW7dmnkmSAd5IJe-pULoa8OvbX6Bge9Cdt-RiSRSXTVKfwnIvhK1ti59gLOYu5HGQZuZmJpUhdb4AjERaZK8NKyE9GeC8QQueA=s16000" /></a></div>The sensor outputs a value between 0 and 100 (0 on black, 100 on white).</div><div><br /></div><div><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgRqTjFWktqwJkdF4nQ6mJPJ-FJigOvUNFMwDKwETt1xwd-53dM-9-Rp0Td4WCZhp7jmFE77v6qpfQmgafRQnO4CnRqTQGXKMvBGip9ajAbJbvTxNNK8DySCr0q9jW-5B6XeAmSdP-iJ5vIY5cFEn2cgSxFfdiFr7KsI4xHpfvyIFva_SHzYB8" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="337" data-original-width="1035" height="208" src="https://blogger.googleusercontent.com/img/a/AVvXsEgRqTjFWktqwJkdF4nQ6mJPJ-FJigOvUNFMwDKwETt1xwd-53dM-9-Rp0Td4WCZhp7jmFE77v6qpfQmgafRQnO4CnRqTQGXKMvBGip9ajAbJbvTxNNK8DySCr0q9jW-5B6XeAmSdP-iJ5vIY5cFEn2cgSxFfdiFr7KsI4xHpfvyIFva_SHzYB8=w640-h208" width="640" /></a></div><br /><br /></div>The line following algorithm tries to follow the edge of the line, as this is a lot more reliable, than trying to stay completely on the white or on the black part of the line. You should try this as an exercise with your robot (Set target to 0)</div><div><br /></div><div>Here are the different values for the output angle based on a target value of 60.</div><div><google-sheets-html-origin><table border="1" cellpadding="0" cellspacing="0" dir="ltr" style="border-collapse: collapse; border: none; font-family: Arial; font-size: 10pt; table-layout: fixed; width: 0px;" xmlns="http://www.w3.org/1999/xhtml"><colgroup><col width="100"></col><col width="100"></col><col width="120"></col><col width="100"></col><col width="100"></col></colgroup><tbody><tr style="height: 21px;"><td data-sheets-value="{"1":2,"2":"Target"}" style="border: 1px solid rgb(204, 204, 204); font-weight: bold; overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Target</td><td data-sheets-value="{"1":3,"3":60}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">60</td><td style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;"></td><td style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;"></td><td style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;"></td></tr><tr style="height: 21px;"><td data-sheets-value="{"1":2,"2":"Kp"}" style="border: 1px solid rgb(204, 204, 204); font-weight: bold; overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Kp</td><td data-sheets-value="{"1":3,"3":2}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">2</td><td style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;"></td><td style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;"></td><td style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;"></td></tr><tr style="height: 21px;"><td data-sheets-value="{"1":2,"2":"Sensor"}" style="border: 1px solid rgb(204, 204, 204); font-weight: bold; overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Sensor</td><td data-sheets-value="{"1":2,"2":"Error"}" style="border: 1px solid rgb(204, 204, 204); font-weight: bold; overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Error</td><td data-sheets-value="{"1":2,"2":"Angle Calculation"}" style="border: 1px solid rgb(204, 204, 204); font-weight: bold; overflow: hidden; padding: 2px 3px; text-align: center; vertical-align: bottom;">Angle Calculation</td><td data-sheets-value="{"1":2,"2":"Angle"}" style="border: 1px solid rgb(204, 204, 204); font-weight: bold; overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Angle</td><td data-sheets-value="{"1":2,"2":"Direction"}" style="border: 1px solid rgb(204, 204, 204); font-weight: bold; overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Direction</td></tr><tr style="height: 21px;"><td data-sheets-value="{"1":3,"3":55}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">55</td><td data-sheets-formula="=R1C2-R[0]C[-1]" data-sheets-value="{"1":3,"3":5}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">5</td><td data-sheets-formula="=R2C2 & " x (" & R[0]C[-2] & " - " & R1C2 & ")"" data-sheets-value="{"1":2,"2":"2 x (55 - 60)"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">2 x (55 - 60)</td><td data-sheets-formula="=R2C2*(R[0]C[-3] - R1C2)" data-sheets-value="{"1":3,"3":-10}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">-10</td><td data-sheets-formula="=if(R[0]C[-1]<0,"Left",If(R[0]C[-1]=0,"Straight","Right"))" data-sheets-value="{"1":2,"2":"Left"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Left</td></tr><tr style="height: 21px;"><td data-sheets-value="{"1":3,"3":65}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">65</td><td data-sheets-formula="=R1C2-R[0]C[-1]" data-sheets-value="{"1":3,"3":-5}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">-5</td><td data-sheets-formula="=R2C2 & " x (" & R[0]C[-2] & " - " & R1C2 & ")"" data-sheets-value="{"1":2,"2":"2 x (65 - 60)"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">2 x (65 - 60)</td><td data-sheets-formula="=R2C2*(R[0]C[-3] - R1C2)" data-sheets-value="{"1":3,"3":10}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">10</td><td data-sheets-formula="=if(R[0]C[-1]<0,"Left",If(R[0]C[-1]=0,"Straight","Right"))" data-sheets-value="{"1":2,"2":"Right"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Right</td></tr><tr style="height: 21px;"><td data-sheets-value="{"1":3,"3":60}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">60</td><td data-sheets-formula="=R1C2-R[0]C[-1]" data-sheets-value="{"1":3,"3":0}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">0</td><td data-sheets-formula="=R2C2 & " x (" & R[0]C[-2] & " - " & R1C2 & ")"" data-sheets-value="{"1":2,"2":"2 x (60 - 60)"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">2 x (60 - 60)</td><td data-sheets-formula="=R2C2*(R[0]C[-3] - R1C2)" data-sheets-value="{"1":3,"3":0}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">0</td><td data-sheets-formula="=if(R[0]C[-1]<0,"Left",If(R[0]C[-1]=0,"Straight","Right"))" data-sheets-value="{"1":2,"2":"Straight"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Straight</td></tr><tr style="height: 21px;"><td data-sheets-value="{"1":3,"3":70}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">70</td><td data-sheets-formula="=R1C2-R[0]C[-1]" data-sheets-value="{"1":3,"3":-10}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">-10</td><td data-sheets-formula="=R2C2 & " x (" & R[0]C[-2] & " - " & R1C2 & ")"" data-sheets-value="{"1":2,"2":"2 x (70 - 60)"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">2 x (70 - 60)</td><td data-sheets-formula="=R2C2*(R[0]C[-3] - R1C2)" data-sheets-value="{"1":3,"3":20}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">20</td><td data-sheets-formula="=if(R[0]C[-1]<0,"Left",If(R[0]C[-1]=0,"Straight","Right"))" data-sheets-value="{"1":2,"2":"Right"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Right</td></tr><tr style="height: 21px;"><td data-sheets-value="{"1":3,"3":80}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">80</td><td data-sheets-formula="=R1C2-R[0]C[-1]" data-sheets-value="{"1":3,"3":-20}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">-20</td><td data-sheets-formula="=R2C2 & " x (" & R[0]C[-2] & " - " & R1C2 & ")"" data-sheets-value="{"1":2,"2":"2 x (80 - 60)"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">2 x (80 - 60)</td><td data-sheets-formula="=R2C2*(R[0]C[-3] - R1C2)" data-sheets-value="{"1":3,"3":40}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">40</td><td data-sheets-formula="=if(R[0]C[-1]<0,"Left",If(R[0]C[-1]=0,"Straight","Right"))" data-sheets-value="{"1":2,"2":"Right"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Right</td></tr><tr style="height: 21px;"><td data-sheets-value="{"1":3,"3":50}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">50</td><td data-sheets-formula="=R1C2-R[0]C[-1]" data-sheets-value="{"1":3,"3":10}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">10</td><td data-sheets-formula="=R2C2 & " x (" & R[0]C[-2] & " - " & R1C2 & ")"" data-sheets-value="{"1":2,"2":"2 x (50 - 60)"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">2 x (50 - 60)</td><td data-sheets-formula="=R2C2*(R[0]C[-3] - R1C2)" data-sheets-value="{"1":3,"3":-20}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">-20</td><td data-sheets-formula="=if(R[0]C[-1]<0,"Left",If(R[0]C[-1]=0,"Straight","Right"))" data-sheets-value="{"1":2,"2":"Left"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Left</td></tr><tr style="height: 21px;"><td data-sheets-value="{"1":3,"3":40}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">40</td><td data-sheets-formula="=R1C2-R[0]C[-1]" data-sheets-value="{"1":3,"3":20}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">20</td><td data-sheets-formula="=R2C2 & " x (" & R[0]C[-2] & " - " & R1C2 & ")"" data-sheets-value="{"1":2,"2":"2 x (40 - 60)"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">2 x (40 - 60)</td><td data-sheets-formula="=R2C2*(R[0]C[-3] - R1C2)" data-sheets-value="{"1":3,"3":-40}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">-40</td><td data-sheets-formula="=if(R[0]C[-1]<0,"Left",If(R[0]C[-1]=0,"Straight","Right"))" data-sheets-value="{"1":2,"2":"Left"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Left</td></tr><tr style="height: 21px;"><td data-sheets-value="{"1":3,"3":100}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">100</td><td data-sheets-formula="=R1C2-R[0]C[-1]" data-sheets-value="{"1":3,"3":-40}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">-40</td><td data-sheets-formula="=R2C2 & " x (" & R[0]C[-2] & " - " & R1C2 & ")"" data-sheets-value="{"1":2,"2":"2 x (100 - 60)"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">2 x (100 - 60)</td><td data-sheets-formula="=R2C2*(R[0]C[-3] - R1C2)" data-sheets-value="{"1":3,"3":80}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">80</td><td data-sheets-formula="=if(R[0]C[-1]<0,"Left",If(R[0]C[-1]=0,"Straight","Right"))" data-sheets-value="{"1":2,"2":"Right"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Right</td></tr><tr style="height: 21px;"><td data-sheets-value="{"1":3,"3":0}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">0</td><td data-sheets-formula="=R1C2-R[0]C[-1]" data-sheets-value="{"1":3,"3":60}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">60</td><td data-sheets-formula="=R2C2 & " x (" & R[0]C[-2] & " - " & R1C2 & ")"" data-sheets-value="{"1":2,"2":"2 x (0 - 60)"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">2 x (0 - 60)</td><td data-sheets-formula="=R2C2*(R[0]C[-3] - R1C2)" data-sheets-value="{"1":3,"3":-120}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; text-align: right; vertical-align: bottom;">-120</td><td data-sheets-formula="=if(R[0]C[-1]<0,"Left",If(R[0]C[-1]=0,"Straight","Right"))" data-sheets-value="{"1":2,"2":"Left"}" style="border: 1px solid rgb(204, 204, 204); overflow: hidden; padding: 2px 3px; vertical-align: bottom;">Left</td></tr></tbody></table></google-sheets-html-origin></div><div><br /></div><div>One thing to note is that with a target value = 60 and Kp = 2, the robot will follow the line when placed on the left side of the line. This is because, values above 60 (more white), will make the robot turn right (towards the line) and values below 60 (more black), will make the robot turn left. If you wanted the robot to travel on the right side of the line what do you think you will have to change? (see answer at the bottom). </div><div><br /></div><div>You can play with the above calculations using Google Sheets: https://docs.google.com/spreadsheets/d/1RHkM3w1KmZSh7D-QXvpsda7rRfRzInH4p_jKrHQJQg0/edit?usp=sharing</div><div><br /></div><div><div>When you use this piece of code, you might have to change the value of the target (in this case 60) and the multiplier. </div></div><div><br /></div><div><b>Summary:</b></div><div><b><br /></b></div><div>Why is this called a proportional line follower? As the robot drifts away from the line, the error value will become larger. We make this error value even larger by using a constant as a multiplier. This error value is then used as the angle to turn the robot. So the larger the difference, the bigger the angle that will applied for steering. Hence the proportional line follower!</div><div><br /></div><div>So where does the math for all of this come from? There is a concept called PID: Proportional-Integral-Derivative and this concept is used in a lot robotic controllers. In the line-follower, we used just the proportional component of the math. PID uses the following complicated looking formula: <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjuwhfydxtlN9rEo_2F7aptnj9AUopO3U6ZTCjfo4zHaz4IhZWjxgO7uN3w1QKw8gdG1fQ56Sr_efiJZQ2rNZ_2nisUoqe04yK3IgY6pUpDEIOMIFBS13ka6JCp-1qhr1zzpZfzPZYMt4ndOsGLjkeebxNzPM-q9TU8c2qW3a5e0E-wkv7z8C0" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="49" data-original-width="344" src="https://blogger.googleusercontent.com/img/a/AVvXsEjuwhfydxtlN9rEo_2F7aptnj9AUopO3U6ZTCjfo4zHaz4IhZWjxgO7uN3w1QKw8gdG1fQ56Sr_efiJZQ2rNZ_2nisUoqe04yK3IgY6pUpDEIOMIFBS13ka6JCp-1qhr1zzpZfzPZYMt4ndOsGLjkeebxNzPM-q9TU8c2qW3a5e0E-wkv7z8C0=s16000" /></a></div><div class="separator" style="clear: both; text-align: left;">We used just the first term of the above equation in our code:</div><div class="separator" style="clear: both; text-align: left;"><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEi9KHFrr3RD-F7DwsSqFHFLHfow1_9mj7G5b1UYdA14GO_tPzqIXO6n6YY1ucOI66d2F0Df0Iln1mcch10W9hWV6pLjBG7R-OGOAfk0WcxseN8dw2ywrq-ZgVRn448PYcdtIm7NEe-AIriufSkQTtX7CI1pLazKfAzm-NOUI8LkaToUuF43SdE" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="24" data-original-width="118" src="https://blogger.googleusercontent.com/img/a/AVvXsEi9KHFrr3RD-F7DwsSqFHFLHfow1_9mj7G5b1UYdA14GO_tPzqIXO6n6YY1ucOI66d2F0Df0Iln1mcch10W9hWV6pLjBG7R-OGOAfk0WcxseN8dw2ywrq-ZgVRn448PYcdtIm7NEe-AIriufSkQTtX7CI1pLazKfAzm-NOUI8LkaToUuF43SdE=s16000" /></a></div><div class="separator" style="clear: both; text-align: left;">If you want to learn more about how PID is used and implemented and the math, read this post of mine: <a href="https://blog.aggregatedintelligence.com/2022/06/error-correction-using-pid-proportional.html" style="text-align: center;">Error Correction using PID (Proportional Integral Derivative) - The Math</a></div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">You can also learn more about PID here: <a href="https://en.wikipedia.org/wiki/PID_controller#Proportional_term" style="text-align: center;">PID controller - Wikipedia</a></div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;"><b>Answer:</b></div><div class="separator" style="clear: both; text-align: left;">To make the robot travel on the right side of the line, you will have to reverse the output angles. The easiest way to do this is to change the sign of Kp. You would set Kp = -2.</div></div><br /></div><br /></div><div><br /></div><p></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-14286326440425752262022-05-18T13:50:00.004-06:002022-12-01T08:56:12.253-07:00Organizer/Storage for Lego Robot Inventor Kit (Mindstorms) 51515<p></p><div class="separator" style="clear: both; text-align: center;"><br /></div><br />Ever since I got my kid the Lego Robot Inventors 51515 kit, I have been looking for a good way to store the pieces and I think I have finally found the solution thanks to <a href="http://www.RoboticsLearning.com">www.RoboticsLearning.com</a>!<p></p><p>On that website, I found a link to a storage organizer system sold at Harbor Freight: <a href="https://www.harborfreight.com/toolbox-organizer-with-4-drawers-68238.html" target="_blank">Storehouse Toolbox organizer with 4 drawers</a>. If you dont have a harbor freight near you, I think these from Amazon are similar organizer systems:</p><p><a href="https://www.amazon.com/ABN-Tool-Box-10in-1pk/dp/B08WG55HW7/ref=sr_1_2?keywords=toolbox+organizer+4+drawer&qid=1652902819&sprefix=toolbox+organizer+4+d%2Caps%2C130&sr=8-2">ABN Tool Box, 10in 1pk - 4 Drawers Toolbox Organizers and Storage, Toolkit Workbench Garage Organizing Set Plastic Chest - - Amazon.com</a></p><p><a href="https://www.amazon.com/VonHaus-Very-Small-Utility-Storage/dp/B01AC3Y63Q/ref=rvi_sccl_14/135-8341648-7430507?pd_rd_w=KDsTf&pf_rd_p=f5690a4d-f2bb-45d9-9d1b-736fee412437&pf_rd_r=YPHXCPZRC5BR9D0RYXD2&pd_rd_r=1b82c7c2-73d0-4081-ae54-e75cfa172541&pd_rd_wg=LcBw8&pd_rd_i=B01AC3Y63Q&psc=1">VonHaus Multi-purpose Small Parts, Crafts or Tool Organizer/Storage Box - 4 Removable Trays & Adjustable Dividers (10.9 x 10.1 x 6.9 inches - Black/Orange) - - Amazon.com</a></p><p>Here are some images of my organized 51515 kit:</p><p><a href="https://photos.app.goo.gl/bxrgZ6fAAvpn5eyG7">https://photos.app.goo.gl/bxrgZ6fAAvpn5eyG7</a></p><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhr80KOGM0UQCw6mB-aoIA0FMribcNruk64IeU6rwC5h9dAgZRmV0TvhwswPBawWidoJ8YeKPMu2MvuXdRDTm9YgW0Zyhi77K5qtVheOQxxKEqJvjI3YQ4mZYREdDWQdmX3smssM5ptkLnDIWB6DxZ6pAxxSekgue4nebyvvpnKNEXkgJyCPFI/s1014/20220518_131735-ANIMATION.gif" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="1014" data-original-width="761" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhr80KOGM0UQCw6mB-aoIA0FMribcNruk64IeU6rwC5h9dAgZRmV0TvhwswPBawWidoJ8YeKPMu2MvuXdRDTm9YgW0Zyhi77K5qtVheOQxxKEqJvjI3YQ4mZYREdDWQdmX3smssM5ptkLnDIWB6DxZ6pAxxSekgue4nebyvvpnKNEXkgJyCPFI/s320/20220518_131735-ANIMATION.gif" width="240" /></a></p><p>Here is where you can buy the labels ($10 for the PDF) and find more information on RoboticsLearning.com: <a href="https://www.roboticslearning.com/store/storage-lego-51515-labels.html">LEGO Robot Inventor Set Case Label PDF Files (roboticslearning.com)</a></p><p>Update (11/29/2022): Here is how you can use the above storage box to organize the Spike Prime Kit: <a href="https://docs.google.com/presentation/d/10Esa5g_wjFTve83ESnl0FsqPti4y4rMp9Yz4-k-3rP4/edit?usp=sharing">https://docs.google.com/presentation/d/10Esa5g_wjFTve83ESnl0FsqPti4y4rMp9Yz4-k-3rP4/edit?usp=sharing</a></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-1366074403340406882022-05-02T07:52:00.005-06:002022-05-02T07:52:46.090-06:00GitHub - Quickly create a patch file<p>Recently I had to create a patch file to submit to an open source project.</p><p>Here is a quick way to do it via the GitHub web-pages.</p><p></p><ol style="text-align: left;"><li>Go to your pull request (eg: https://github.com/pentaho/pentaho-kettle/pull/8283)</li><li>Add ".patch" to the end. Click enter.</li><li>This will redirect you to a new page with the patch info with some additional email info, etc.</li><li>Copy the entire text and paste it into a file. Save the file with an extension of .patch.</li><li>I found that I needed to add a new line at the end of the patch file.</li></ol><div>That is your patch file!</div><div><br /></div><div><b>To apply the patch file:</b></div><div><ol style="text-align: left;"><li>Create a new branch from master (or whichever branch the patch is to be applied to).</li><li>Switch to the branch.</li><li>Run the following command: git apply name_of_patch_file.patch</li></ol><div>If you instead want to <b>manually </b>create your patch using GIT:</div></div><div><ol style="text-align: left;"><li>Switch to the branch with your changes</li><li>Run the following command: git diff master > ..\name_of_patch_file.patch</li></ol><div><br /></div></div><div><br /></div><p></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-10253845405787701292022-04-27T12:17:00.003-06:002022-04-27T12:17:40.659-06:00AWS Athena's Information_Schema<p>The information_schema in most database systems is very useful to get information about tables, views, columns that might be available in that db.</p><p>Here are the information schema views available. (WARNING: queries against the information_schema views are <b>very</b> slow)</p><p><br /></p><p><b> Schemas:</b></p><p></p><blockquote><p>SELECT</p><p> catalog_name, schema_name</p><p></p><p>FROM information_schema.schemata</p></blockquote><p> <b>Tables:</b></p><blockquote><p>SELECT</p><p> table_catalog, table_schema, table_name, table_type</p><p></p><p>FROM information_schema.tables</p></blockquote><p></p><p></p><p><b>Columns:</b></p><p></p><blockquote><p>select</p><p> table_catalog, table_schema, table_name, column_name, data_type, is_nullable,</p><p> column_default, comment</p><p>from information_schema.columns</p><p>order by</p><p> table_catalog, table_schema, table_name, ordinal_position</p></blockquote><p><br /></p><p><b>Retrieved by looking at the PrestoDB test cases: </b><a href="https://github.com/prestodb/presto/tree/2ad67dcf000be86ebc5ff7732bbb9994c8e324a8/presto-product-tests/src/main/resources/sql-tests/testcases/system">presto/presto-product-tests/src/main/resources/sql-tests/testcases/system at 2ad67dcf000be86ebc5ff7732bbb9994c8e324a8 · prestodb/presto · GitHub</a> </p><p></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-30467761258896855652022-04-27T01:38:00.001-06:002022-04-27T01:38:20.000-06:00Pentaho Data Integration - Java Defined Class - Main working sample<p> The sample code provided in PDI for "main" doesnt work. Here is the fixed up version:</p><p><a href="https://github.com/rajrao/mypublicnotes/blob/master/Pentaho/Sample-UserDefinedJavaClass-Main.md">mypublicnotes/Sample-UserDefinedJavaClass-Main.md at master · rajrao/mypublicnotes (github.com)</a></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-29529461649522067382022-04-27T01:37:00.001-06:002022-04-27T01:37:12.263-06:00Pentaho Data Integration - Stripping characters out of string fields<p> Here is a generic method (actually 2) for stripping out characters from all string fields in Pentaho.</p><p><a href="https://github.com/rajrao/mypublicnotes/blob/master/Pentaho/Sample-Generic-Input-FieldFixerUpper.md">mypublicnotes/Sample-Generic-Input-FieldFixerUpper.md at master · rajrao/mypublicnotes (github.com)</a></p><p>These methods are convenient when you are loading a variety of tables or files and you dont know the names of all the string fields ahead of time and so cannot use field based transformations (like Replace in String, etc).</p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-75524239656891246452022-04-12T16:58:00.003-06:002022-04-12T16:58:32.842-06:00Connecting to Denodo from AWS Sagemaker<p> Here is some sample code that shows you how to connect from AWS Sagemaker to Denodo</p><p><a href="https://github.com/rajrao/mypublicnotes/blob/master/AWS/SageMaker/aws_sagemaker_connect_to_denodo_conda_python.ipynb">mypublicnotes/aws_sagemaker_connect_to_denodo_conda_python.ipynb at master · rajrao/mypublicnotes (github.com)</a></p><p>More info: <a href="https://community.denodo.com/kb/en/view/document/How%20to%20connect%20to%20Denodo%20from%20Amazon%20SageMaker">How to connect to Denodo from Amazon SageMaker</a></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com0tag:blogger.com,1999:blog-7656888.post-56769535846518850592022-02-17T12:24:00.003-07:002022-02-17T12:24:38.224-07:00PowerBi Icons for Diagrams<p> I found a couple of awesome resources for PowerBi icons that you can use in diagrams.</p><p>1. Official repo of PBI icons: <a href="https://github.com/microsoft/PowerBI-Icons">microsoft/PowerBI-Icons: Icons (PNG and SVG) for Power BI (github.com)</a></p><p>2. Marc Lelijveld repo: Marc has provided an xml document that allows you to easily import all the icons into Draw.Io. This makes the whole process extremely easy. <a href="https://github.com/marclelijveld/Power-BI-Icons">marclelijveld/Power-BI-Icons: Icons (PNG and SVG) for Power BI (github.com)</a>. The most import link from Marc's repo is the raw link to the xml file: https://raw.githubusercontent.com/marclelijveld/Power-BI-Icons/main/Diagrams.net_PowerBIIcons.xml. This link can be used in Draw.Io via File >>Open Library from >> URL...</p><p>Once done, you will get all the icons in the left pane:</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjEBXofzoAiaADf8bw2xxMUVtOSrdFVFEJ0BbmSsytIsjzZUw1BrycIWZtMax9kinIAE_EFeJBvmpKVSgOnzn04-e8IJw07YOe5OB23lnAC14DFoF8jHuvcjgLXWAQ6y8ApsYQPHK4HBbKPHvghVAi9aLBk3_3ERyflozJCeyvH-92V2x64-5A" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="626" data-original-width="979" height="205" src="https://blogger.googleusercontent.com/img/a/AVvXsEjEBXofzoAiaADf8bw2xxMUVtOSrdFVFEJ0BbmSsytIsjzZUw1BrycIWZtMax9kinIAE_EFeJBvmpKVSgOnzn04-e8IJw07YOe5OB23lnAC14DFoF8jHuvcjgLXWAQ6y8ApsYQPHK4HBbKPHvghVAi9aLBk3_3ERyflozJCeyvH-92V2x64-5A" width="320" /></a></div><br /><br /><p></p><p>Here is more info from Marc's blog: <a href="https://data-marc.com/2021/06/30/power-bi-icon-library-now-includes-official-icons-to-use-in-draw-io-diagrams-net/">Power BI Icon Library – now includes official icons to use in Draw.io / Diagrams.net – Data – Marc (data-marc.com)</a></p>Raj Raohttp://www.blogger.com/profile/02773005045522319448noreply@blogger.com1