<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Vembry Fiska on Medium]]></title>
        <description><![CDATA[Stories by Vembry Fiska on Medium]]></description>
        <link>https://medium.com/@fiska.nurwantoro?source=rss-4a74082fcf9d------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*45e2zAVAG6OR48LvvJU5QA.png</url>
            <title>Stories by Vembry Fiska on Medium</title>
            <link>https://medium.com/@fiska.nurwantoro?source=rss-4a74082fcf9d------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 17:33:31 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@fiska.nurwantoro/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Your PBIP Has Tables and No Measures. Claude’s Free Plan Can Write 65 of Them.]]></title>
            <link>https://pub.towardsai.net/your-pbip-has-tables-and-no-measures-claudes-free-plan-can-write-65-of-them-8b7f4a1adc45?source=rss-4a74082fcf9d------2</link>
            <guid isPermaLink="false">https://medium.com/p/8b7f4a1adc45</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[claude-ai]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Vembry Fiska]]></dc:creator>
            <pubDate>Fri, 06 Nov 2026 09:35:15 GMT</pubDate>
            <atom:updated>2026-11-13T04:01:12.484Z</atom:updated>
            <content:encoded><![CDATA[<h4><em>The prompts, the order I work in, the errors that stop a TMDL file from opening, and the checks that catch wrong numbers.</em></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c3l980IotOXRqGt8lc7r-Q.png" /></figure><p>You have a Power BI project with the tables loaded and the relationships drawn. The Fields pane is full of raw columns and there is not one measure. Total Sales, Sales PY, Sales YTD and Gross Margin % all still have to be written.</p><p>Most measures in a model like this are variations on a few patterns: a base measure, its prior-year version, a growth percentage. The prompt further down asks for 65 measures, and 21 of them are time intelligence.</p><p>A PBIP saves the semantic model as plain-text TMDL files. So you can give those files to Claude, list the measures you want, and get back a finished _Measures.tmdl to drop into the project folder. This guide uses Claude&#39;s free plan. I wrote it for my workshop participants, with a retail case study called PT Nusa Retail as the model.</p><p>The project starts with tables and relationships and no measures:</p><pre>NusaRetail.SemanticModel/<br>  definition/<br>    model.tmdl<br>    relationships.tmdl<br>    tables/<br>      Sales.tmdl<br>      _Measures.tmdl   &lt;- Claude will write this<br>      Dim_Date.tmdl</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/402/1*zB92cgGCLkVLZbhJ8QNpNQ.png" /><figcaption>The PBIP folder before Claude writes anything</figcaption></figure><p>The whole workflow in one sentence: give the TMDL files to Claude as context, let it write or tidy the TMDL, paste the result back into the PBIP folder, then open the project and refresh.</p><h3>Step 0: Set up once</h3><ol><li>In Power BI Desktop, open File &gt; Options &gt; Preview features and turn on “Power BI Project (.pbip) save option” and “Store semantic model using TMDL format”.</li><li>Turn off Auto date/time in Options &gt; Current File &gt; Data Load. With it on, Desktop adds hidden LocalDateTable_* tables, and their leftovers cause one of the errors in Step 5.</li><li>Save the file as .pbip with File &gt; Save As.</li><li>Close Power BI Desktop. Don’t edit TMDL files while the project is open in it.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/961/1*qQpkm8wTgb65ne0Mvlz-lQ.png" /><figcaption>The two preview features that make the PBIP and TMDL folder structure possible</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/967/1*zKfbdUo02whnzGYqs0SpWg.png" /><figcaption>Auto date/time off, so no hidden date tables get created</figcaption></figure><h3>Step 1: Give Claude the model</h3><p>Put all the files in a single message to save your message limit. Send model.tmdl (the table list and model settings), relationships.tmdl, the fact table Sales.tmdl, the calendar Dim_Date.tmdl, and every other dimension table such as Products, Customers and Stores.</p><p>If Claude refuses the .tmdl upload, rename the files to .txt or paste their contents. You can cut the long partition blocks, which hold the Power Query source. Claude only needs table names, column names and data types to write correct DAX.</p><p>If you will work on the same model across several chats, store the TMDL files in a Claude Project’s knowledge so you don’t upload them every time.</p><h3>Step 2: Ask for the measures</h3><p>The prompt below lists every measure by display folder and sets the writing rules. It is an English version of the prompt I give participants in Indonesian.</p><pre>I have a Power BI Project (PBIP) in TMDL format. I uploaded my model files<br>(model.tmdl, relationships.tmdl, and every table file).<br>Read the table, column, and relationship structure before you write any DAX.<br><br>Create a new file, tables/_Measures.tmdl, with the measures below,<br>grouped by displayFolder:<br>01. Sales<br>   Total Sales, Gross Sales, Discount Amount, Discount %, Total Qty,<br>   Total Orders, Avg Order Value, Avg Selling Price, Avg Qty per Order<br>02. Profitability<br>   Total COGS (Qty x Products[UnitCost]), Gross Profit, Gross Margin %<br>03. Time Intelligence (use Dim_Date[Date])<br>   Sales PY, Sales YoY, Sales YoY %, Sales PM, Sales MoM %, Sales MTD,<br>   Sales QTD, Sales YTD, Sales YTD PY, Sales YTD YoY %, Sales Rolling 3M,<br>   Gross Profit PY, Gross Profit YoY %, Gross Margin % PY, Gross Margin Δ pts,<br>   Orders PY, Orders YoY %, Customers PY, Customers YoY %, AOV PY, AOV YoY %<br>04. Target<br>   Sales Target (use TREATAS from Stores[Region] to Targets_2025_wide[Region]),<br>   Sales vs Target, Target Achievement %, Sales Target YTD,<br>   Target Achievement YTD %<br>05. Customer<br>   Active Customers, New Customers, Returning Customers,<br>   Repeat Customer Rate %, Sales per Customer, Orders per Customer<br>06. Contribution &amp; Ranking<br>   Sales % of Total, Product Rank, Store Rank, Cumulative Sales % (Pareto)<br>07. UI &amp; Formatting<br>   Last Sales Date, Report Period Label, Sales YoY Label,<br>   Color YoY, Color Achievement<br>08. KPI Card (each KPI gets one Label and one Color)<br>   KPI AOV Label/Color, KPI Customers Label/Color,<br>   KPI Gross Profit Label/Color, KPI Margin Label/Color,<br>   KPI Sales YTD Label/Color, KPI Target Label/Color<br>   - Label: short text for the card, for example &quot;Rp 1.2 M&quot; or<br>     &quot;▲ 12.3% vs PY&quot; (Rb = thousand, Jt = million, M = billion)<br>   - Color: hex code for conditional formatting<br>     (green = good, red = bad, gray = no comparison data)<br>Writing rules:<br>1. All measures in one file, in folder order 01 to 08.<br>2. Write displayFolder exactly as named above (for example: 01. Sales).<br>3. formatString: Rupiah \R\p #,0 ; percent 0.0% ; counts #,0 ;<br>   text and color measures need no formatString.<br>4. Write each description with /// above the measure line<br>   (NOT a description: property), in Indonesian.<br>5. _Measures is a calculated table: ROW(&quot;Value&quot;, BLANK()) with the Value<br>   column hidden. Do not write dataType on that column, same as the other<br>   calculated columns in my model.<br>6. Use DIVIDE for every division. Use table names exactly as the model<br>   spells them (Dim_Date, not dim_date).<br>7. File format: tab indentation, CRLF line endings, a new lineageTag<br>   for every measure.<br>8. Ranking and Pareto measures only fill in when the related column is in<br>   the visual (ISINSCOPE/HASONEVALUE).<br>When the file is done, also give me:<br>- the line to add to model.tmdl (ref table _Measures)<br>- the folder structure and measure names as plain text<br>- a note on any risk in my model (for example a bidirectional relationship,<br>  or a product with no UnitCost) that could make the numbers wrong<br>Output: the complete file, ready to copy.</pre><p>Change “in Indonesian” in rule 4 to your team’s language.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KdRPyY-PSpfwWSRkE_oJ_A.png" /><figcaption><em>One message with every model file and the full measure list</em></figcaption></figure><p>Three lines in that prompt need a note.</p><p>Rule 4 asks for descriptions with /// above each measure instead of a description: property. TMDL reads /// as the description of the line directly below it, so no blank line can sit between them. Step 5 shows what happens when one does.</p><p>Total COGS multiplies quantity by Products[UnitCost]. A product with no UnitCost gets zero cost, so its margin looks better than it is. That is why the last paragraph asks Claude to list risks like this one, along with bidirectional relationships.</p><p>In the KPI Card labels, Rb, Jt and M are the Indonesian abbreviations for thousand, million and billion. “Rp 1.2 M” means 1.2 billion rupiah.</p><p>To add measures to a file you already have, use this follow-up:</p><pre>Add the following measures to the existing _Measures.tmdl:<br>- 04. Target: Sales Target, Target Achievement %, Sales vs Target<br>- 05. Customer: Active Customers, New Customers, Returning Customers<br>- 06. Ranking: Product Rank, Sales % of Total, Cumulative Sales % (Pareto)<br>Give me the complete _Measures.tmdl file, not a fragment.</pre><p>Ask for the complete file every time. A fragment is easy to paste into the wrong place.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*s_GC54k4XOSFs3YX_OoIMg.png" /><figcaption><em>The first measures in Claude’s output</em></figcaption></figure><h3>Step 3: Tidy the model</h3><p>Do the measures first, review them, and only then tidy the model. This prompt hides the ID columns, fixes sorting and checks the relationships:</p><pre>Review my TMDL model and tidy it up:<br>1. Hide the foreign key (ID) columns and the raw numeric columns in the<br>   fact table<br>2. Make sure Dim_Date has dataCategory: Time and the Date column isKey<br>3. Set sortByColumn (Month Name sorted by Month No)<br>4. Change summarizeBy to none for columns that should not be summed<br>5. Check the relationships: are any many-to-many, inactive, or unused?<br>Do not change the partition/source Power Query and do not change<br>any lineageTag.<br>Give me only the files that changed, complete from the first line<br>to the last.</pre><p>Claude must not touch lineageTag. That ID links tables, columns and measures to the visuals already built on them, so rewriting it can break those visuals.</p><p>If you have no calendar table yet, ask for one:</p><pre>Create tables/Dim_Date.tmdl as a calculated table using CALENDAR from the<br>minimum to the maximum Sales[OrderDate] (start of year to end of year).<br>Columns: Date, Year, Quarter, Month No, Month Name, Month Short, Year Month,<br>Year Month Key, Week No, Day Name, Day Type (Weekday/Weekend).<br>Set dataCategory: Time, Date as isKey, and the correct sortByColumn.<br>Also give me a new relationship Sales[OrderDate] -&gt; Dim_Date[Date]<br>to add to relationships.tmdl.</pre><p>Check how Claude builds Month No. It should come from MONTH(Dim_Date[Date]), not from Month Name. Month Name is sorted by Month No, and a sort column that depends on the column it sorts produces a circular dependency error.</p><h3>Step 4: Put the files back</h3><ol><li>Copy Claude’s files into definition/ and definition/tables/.</li><li>Add ref table _Measures to model.tmdl, and ref table Dim_Date if the calendar is new.</li><li>Delete .pbi/cache.abf if you changed a column&#39;s data type or removed a table.</li><li>Open the .pbip and click Refresh.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/607/1*Q2SMAcZ-sJBC98Jc-90NlQ.png" /><figcaption><em>All 65 measures, grouped by display folder.</em></figcaption></figure><h3>Step 5: When the project won’t open</h3><p>Copy only the Error Message from the dialog, not the whole stack trace, and send it back with this:</p><pre>I got this error when opening the PBIP:<br>[paste Error Message]<br>Which file is wrong and how do I fix it?<br>Give me the corrected file, complete.</pre><p>These are the errors I meet most often, with the cause and the fix.</p><p>description is not a supported property</p><p>A table description was written as description:. Put /// text on the line above table instead.</p><p>variation Variation ... refers to an object which cannot be found</p><p>A leftover from Auto date/time. Delete the variation block on the date column, and delete the LocalDateTable_*.tmdl and DateTableTemplate_*.tmdl files.</p><p>Type mismatch. Datatype conversion failed</p><p>You changed a column’s type but the cache still holds the old data. Delete .pbi/cache.abf, then refresh.</p><p>Cannot find table/column inside a measure</p><p>A table or column name in the DAX doesn’t match the model. Send the table files to Claude again and ask it to match the names.</p><p>Four more came up when I built other PBIPs this way.</p><p>Unexpected line type: Empty!</p><p>A blank line sits between a /// description and the object under it. I got this when Claude used /// blocks as section headers. Put /// directly above the measure it describes.</p><p>The &#39;Goals&#39; measure cannot be created because a column with the same name already exists.</p><p>A measure and a column in the same table share a name. Rename the column (GoalsRaw, for example) and leave its sourceColumn as it is, so the data mapping doesn&#39;t change.</p><p>Property QueryGroup of object &quot;expression DataFolderPath&quot; refers to an object which cannot be found</p><p>A queryGroup: line points at a query group that was never declared on the model. Delete the line. It only controls folders in the Power Query pane.</p><p>A circular dependency was detected: DimPlayer[Position], DimPlayer[PositionSort], DimPlayer[Position]</p><p>A column is sorted by a calculated column that reads the first column. Build the sort key from a column that doesn’t depend on it, such as Month No from MONTH(Dim_Date[Date]).</p><h3>Step 6: Check the numbers</h3><p>Claude speeds up the writing. It doesn’t check results against your data, so you do:</p><ul><li>Every measure shows in the _Measures table, grouped by folder.</li><li>Total Sales equals the SUM of the Sales Amount column in Data view.</li><li>The PY and YoY measures return values when you use columns from Dim_Date.</li><li>Month Name sorts Jan to Dec, not alphabetically.</li><li>ID columns no longer appear in the Fields pane.</li><li>Rupiah and percent formats display correctly.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/664/1*NCOuEZMKMly7vGfprmRs9g.png" /><figcaption><em>Total Sales against the raw sum</em></figcaption></figure><p>Start with Total Sales. Sales PY, Sales YTD and the target measures are normally built on it, so a wrong total repeats through all of them.</p><p><em>I build Power BI models and data pipelines for mid-market companies.</em></p><p><strong>Tags:</strong> power-bi claude-ai tmdl dax data-modeling</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8b7f4a1adc45" width="1" height="1" alt=""><hr><p><a href="https://pub.towardsai.net/your-pbip-has-tables-and-no-measures-claudes-free-plan-can-write-65-of-them-8b7f4a1adc45">Your PBIP Has Tables and No Measures. Claude’s Free Plan Can Write 65 of Them.</a> was originally published in <a href="https://pub.towardsai.net/">Towards AI</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[I Built a Report Generator Inside Supabase Edge Functions — Here’s What I Learned]]></title>
            <link>https://blog.dataengineerthings.org/i-built-a-report-generator-inside-supabase-edge-functions-heres-what-i-learned-a1cb25a54bcc?source=rss-4a74082fcf9d------2</link>
            <guid isPermaLink="false">https://medium.com/p/a1cb25a54bcc</guid>
            <category><![CDATA[supabase]]></category>
            <category><![CDATA[typescript]]></category>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[backend]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Vembry Fiska]]></dc:creator>
            <pubDate>Thu, 27 Aug 2026 09:51:19 GMT</pubDate>
            <atom:updated>2026-08-27T23:18:24.421Z</atom:updated>
            <content:encoded><![CDATA[<h4><em>A step-by-step story of building a live-database Excel report generator in TypeScript/Deno, including the three decisions that changed the architecture mid-build</em></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pCNpFB4SYOowVGXxDP8xgw.png" /></figure><h3>How It Started</h3><p>A client showed me a screenshot of a report they needed.</p><p>DC Inventory Movement Report. Green header. Grouped rows by item. Subtotals per group. Filter inputs at the top — vendor name, outlet, RAW code, date range. The output: an Excel file, properly formatted, downloadable on demand.</p><p>“Can Supabase generate something like this?”</p><p>My instinct was to say: use Power BI, export to Excel, done. But the client’s stack was already Supabase — PostgreSQL for data storage, Supabase Auth for user management, and a lightweight HTML frontend. Adding another tool just for report export felt wrong.</p><p>So I said yes, and then figured out how to make it true.</p><p>This is what I learned building it.</p><h3>The First Version: A Simple Function That Takes Data</h3><p>My first implementation was conceptually clean. The client’s frontend would fetch the data from the sales_orders table, pass it to an Edge Function as a JSON array, and the function would generate an Excel file and stream it back.</p><pre>// First architecture — simple<br>serve(async (req: Request) =&gt; {<br>  const { data, filters } = await req.json()<br>  const workbook = generateInventoryReport(data, filters)<br>  const buffer = xlsx.write(workbook, { bookType: &#39;xlsx&#39;, type: &#39;buffer&#39; })<br><br>return new Response(buffer, {<br>    headers: {<br>      &#39;Content-Type&#39;: &#39;application/vnd.openxmlformats-officedocument.spreadsheetml.sheet&#39;,<br>      &#39;Content-Disposition&#39;: &#39;attachment; filename=inventory_report.xlsx&#39;,<br>    }<br>  })<br>})</pre><p>The Excel generation itself used the xlsx library, imported from Deno&#39;s CDN:</p><pre>import * as xlsx from &#39;https://deno.land/x/xlsx@0.17.0/xlsx.js&#39;</pre><p>This worked immediately. The function received JSON, grouped the rows by item_name, calculated subtotals per group, built a styled workbook, and returned a binary Excel file. Clean. Simple. Done.</p><p>Then the client asked for filter dropdowns.</p><h3>The Problem With Hardcoded Dropdowns</h3><p>The obvious solution was to hardcode the filter options in the HTML:</p><pre>&lt;select id=&quot;vendor-filter&quot;&gt;<br>  &lt;option&gt;MBF PRINTING&lt;/option&gt;<br>  &lt;option&gt;SUPPLIER B&lt;/option&gt;<br>  &lt;option&gt;SUPPLIER C&lt;/option&gt;<br>&lt;/select&gt;</pre><p>This worked until the first time a new vendor was added to the database. The dropdown immediately became stale. The client had to message someone to update the HTML. That someone was me.</p><p>After the third such message in two weeks, I realised the architecture was wrong.</p><p><strong>The dropdown options should come from the database. Not from the code.</strong></p><h3>The Architecture Shift: Two Functions</h3><p>The insight that changed everything: discovery and generation are different operations. They should be different functions.</p><pre>BEFORE (one function):<br>  Browser sends data array → Function generates Excel → File downloads</pre><pre>AFTER (two functions):<br>  Browser calls get-filter-options → Dropdowns populate from live DB<br>  Browser calls generate-inventory-report → Function queries DB itself → File downloads</pre><h3>Function 1: get-filter-options</h3><p>This function has one job: tell the browser what filter values currently exist in the database.</p><pre>import { serve } from &#39;https://deno.land/std@0.168.0/http/server.ts&#39;<br>import { createClient } from &#39;https://esm.sh/@supabase/supabase-js@2&#39;</pre><pre>const corsHeaders = {<br>  &#39;Access-Control-Allow-Origin&#39;: &#39;*&#39;,<br>  &#39;Access-Control-Allow-Headers&#39;: &#39;authorization, x-client-info, apikey, content-type&#39;,<br>}<br>serve(async (req: Request) =&gt; {<br>  if (req.method === &#39;OPTIONS&#39;) {<br>    return new Response(&#39;ok&#39;, { headers: corsHeaders })<br>  }<br>  const supabase = createClient(<br>    Deno.env.get(&#39;SUPABASE_URL&#39;)!,<br>    Deno.env.get(&#39;SUPABASE_ANON_KEY&#39;)!<br>  )<br>  // Fetch all distinct values we need for dropdowns<br>  const { data: rows, error } = await supabase<br>    .from(&#39;sales_orders&#39;)<br>    .select(&#39;vendor_name, stock_out_outlet, raw_code, stock_in_date&#39;)<br>  if (error) {<br>    return new Response(JSON.stringify({ error: error.message }), {<br>      status: 500,<br>      headers: { ...corsHeaders, &#39;Content-Type&#39;: &#39;application/json&#39; }<br>    })<br>  }<br>  // Deduplicate in JavaScript<br>  const vendors  = [...new Set(rows.map(r =&gt; r.vendor_name))].sort()<br>  const outlets  = [...new Set(rows.map(r =&gt; r.stock_out_outlet))].sort()<br>  const rawCodes = [...new Set(rows.map(r =&gt; r.raw_code))].sort()<br>  const dates = rows.map(r =&gt; r.stock_in_date).filter(Boolean)<br>  const dateFrom = dates.length ? dates.reduce((a, b) =&gt; a &lt; b ? a : b) : null<br>  const dateTo   = dates.length ? dates.reduce((a, b) =&gt; a &gt; b ? a : b) : null<br>  return new Response(<br>    JSON.stringify({ vendors, outlets, raw_codes: rawCodes, date_from: dateFrom, date_to: dateTo }),<br>    { headers: { ...corsHeaders, &#39;Content-Type&#39;: &#39;application/json&#39; } }<br>  )<br>})</pre><p>The browser calls this once on page load. Dropdowns populate. No hardcoding.</p><h3>Function 2: generate-inventory-report</h3><p>The second function changed too. It no longer receives a data array — it receives filter criteria, then queries the database itself.</p><pre>serve(async (req: Request) =&gt; {<br>  if (req.method === &#39;OPTIONS&#39;) {<br>    return new Response(&#39;ok&#39;, { headers: corsHeaders })<br>  }</pre><pre>const supabase = createClient(<br>    Deno.env.get(&#39;SUPABASE_URL&#39;)!,<br>    Deno.env.get(&#39;SUPABASE_ANON_KEY&#39;)!<br>  )<br>  const filters = await req.json()<br>  // filters = { vendors: [], outlets: [], raw_codes: [], date_from: &#39;&#39;, date_to: &#39;&#39; }<br>  // Build the query - skip empty filter arrays<br>  let query = supabase.from(&#39;sales_orders&#39;).select(&#39;*&#39;)<br>  if (filters.vendors?.length)   query = query.in(&#39;vendor_name&#39;, filters.vendors)<br>  if (filters.outlets?.length)   query = query.in(&#39;stock_out_outlet&#39;, filters.outlets)<br>  if (filters.raw_codes?.length) query = query.in(&#39;raw_code&#39;, filters.raw_codes)<br>  if (filters.date_from)         query = query.gte(&#39;stock_in_date&#39;, filters.date_from)<br>  if (filters.date_to)           query = query.lte(&#39;stock_in_date&#39;, filters.date_to)<br>  const { data: rows, error } = await query<br>  if (error || !rows?.length) {<br>    return new Response(JSON.stringify({ error: error?.message ?? &#39;No data found&#39; }), {<br>      status: error ? 500 : 404,<br>      headers: { ...corsHeaders, &#39;Content-Type&#39;: &#39;application/json&#39; }<br>    })<br>  }<br>  // Generate the Excel file from the queried data<br>  const buffer = buildWorkbook(rows, filters)<br>  return new Response(buffer, {<br>    headers: {<br>      ...corsHeaders,<br>      &#39;Content-Type&#39;: &#39;application/vnd.openxmlformats-officedocument.spreadsheetml.sheet&#39;,<br>      &#39;Content-Disposition&#39;: &#39;attachment; filename=inventory_report.xlsx&#39;,<br>    }<br>  })<br>})</pre><p><strong>The critical detail:</strong> if (filters.vendors?.length) — not just if (filters.vendors). An empty array is truthy in JavaScript. If you write .in(&#39;vendor_name&#39;, []), Supabase returns zero rows. Skipping the clause when the array is empty means &quot;no filter on this field&quot; — which is what the user expects when they don&#39;t select anything.</p><p>I learned this the hard way when a test with no vendor selected returned a blank Excel file.</p><h3>Building the Excel Workbook</h3><p>The workbook generation is the most detailed part. The xlsx library writes cell-by-cell, and every property — font, fill, border, alignment — is specified explicitly. There is no CSS shorthand here.</p><h3>The Cell Styling Pattern</h3><pre>function styledCell(value: any, styles: object) {<br>  const isNum = typeof value === &#39;number&#39;<br>  return {<br>    t: isNum ? &#39;n&#39; : &#39;s&#39;,<br>    v: value,<br>    s: styles<br>  }<br>}</pre><pre>// Example: header band (green background, white bold text)<br>const headerStyle = {<br>  font:      { bold: true, color: { rgb: &#39;FFFFFF&#39; }, sz: 14 },<br>  fill:      { fgColor: { rgb: &#39;70AD47&#39; }, patternType: &#39;solid&#39; },<br>  alignment: { horizontal: &#39;left&#39;, vertical: &#39;center&#39; },<br>}<br>// Example: data cell with orange text (for RAW codes, expiry dates, outlets)<br>const orangeTextStyle = {<br>  font:      { color: { rgb: &#39;FF6600&#39; }, sz: 10 },<br>  alignment: { horizontal: &#39;left&#39;, vertical: &#39;center&#39; },<br>  border: {<br>    top:    { style: &#39;thin&#39;, color: { rgb: &#39;CCCCCC&#39; } },<br>    bottom: { style: &#39;thin&#39;, color: { rgb: &#39;CCCCCC&#39; } },<br>    left:   { style: &#39;thin&#39;, color: { rgb: &#39;CCCCCC&#39; } },<br>    right:  { style: &#39;thin&#39;, color: { rgb: &#39;CCCCCC&#39; } },<br>  }<br>}<br>// Example: subtotal row (thick borders, bold values)<br>const subtotalStyle = {<br>  font:   { bold: true, sz: 10 },<br>  border: {<br>    top:    { style: &#39;medium&#39;, color: { rgb: &#39;000000&#39; } },<br>    bottom: { style: &#39;medium&#39;, color: { rgb: &#39;000000&#39; } },<br>    left:   { style: &#39;thin&#39;,   color: { rgb: &#39;000000&#39; } },<br>    right:  { style: &#39;thin&#39;,   color: { rgb: &#39;000000&#39; } },<br>  }<br>}</pre><h3>Grouping and Subtotals</h3><p>The data comes from Supabase as a flat array. The function groups it by item_name before writing rows:</p><pre>function groupByItemName(rows: SalesOrderRow[]) {<br>  return rows.reduce((acc, row) =&gt; {<br>    if (!acc[row.item_name]) acc[row.item_name] = []<br>    acc[row.item_name].push(row)<br>    return acc<br>  }, {} as Record&lt;string, SalesOrderRow[]&gt;)<br>}<br><br>// In the workbook builder:<br>const grouped = groupByItemName(rows)<br>for (const [itemName, items] of Object.entries(grouped)) {<br>  let subtotalStockIn  = 0<br>  let subtotalStockOut = 0<br>  items.forEach((item, idx) =&gt; {<br>    // Write each row - show item name only on first row of each group<br>    const displayName = idx === 0 ? itemName : &#39;&#39;<br>    writeDataRow(ws, rowIndex, displayName, item)<br>    subtotalStockIn  += item.stock_in_qty<br>    subtotalStockOut += item.stock_out_qty<br>    rowIndex++<br>  })<br>  // Write subtotal row for this group<br>  writeSubtotalRow(ws, rowIndex, subtotalStockIn, subtotalStockOut)<br>  rowIndex += 2  // Extra blank row between groups<br>}</pre><h3>Cell Addressing in xlsx</h3><p>The xlsx library addresses cells by column letter + row number — A1, B3, etc. When building rows programmatically, you convert column index to letter using the library&#39;s helper:</p><pre>const cellAddr = xlsx.utils.encode_col(colIndex) + (rowIndex + 1)<br>ws[cellAddr] = { t: &#39;s&#39;, v: &#39;some value&#39;, s: { /* styles */ } }</pre><p>One thing to watch: the library uses zero-based internal row indices, but one-based addresses in the cell reference system. Always rowIndex + 1 when writing the cell address.</p><h3>The CORS Problem (And Why It Always Comes For You)</h3><p>Every Supabase Edge Function needs CORS headers if it’s called from a browser. Without them, the browser silently blocks the response.</p><p>The mistake I made the first time: I added CORS headers only to successful responses. Not to error responses. Not to the OPTIONS preflight. This meant that in production, any error (like an RLS block) returned a CORS-less response that the browser refused to read — so the error was invisible.</p><p>The correct pattern:</p><pre>const corsHeaders = {<br>  &#39;Access-Control-Allow-Origin&#39;: &#39;*&#39;,<br>  &#39;Access-Control-Allow-Headers&#39;: &#39;authorization, x-client-info, apikey, content-type&#39;,<br>}<br><br>serve(async (req) =&gt; {<br>  // Handle OPTIONS preflight - ALWAYS first<br>  if (req.method === &#39;OPTIONS&#39;) {<br>    return new Response(&#39;ok&#39;, { headers: corsHeaders })<br>  }<br>  try {<br>    // ... your logic<br>    return new Response(result, {<br>      headers: { ...corsHeaders, &#39;Content-Type&#39;: &#39;...&#39; }  // CORS on success<br>    })<br>  } catch (error) {<br>    return new Response(JSON.stringify({ error: error.message }), {<br>      status: 500,<br>      headers: { ...corsHeaders, &#39;Content-Type&#39;: &#39;application/json&#39; }  // CORS on error too<br>    })<br>  }<br>})</pre><p>Spread corsHeaders onto every single response. Success, error, edge case. All of them.</p><h3>The RLS Silent Failure</h3><p>After deploying the two-function architecture, the get-filter-options function worked. The generate-inventory-report function returned a file — but it was empty.</p><p>The error wasn’t visible in the browser. It wasn’t in the Supabase function logs either. The function ran successfully. It just returned zero rows.</p><p>The reason: Row Level Security.</p><p>The sales_orders table had RLS enabled with no policy that allowed anonymous reads. The SUPABASE_ANON_KEY — which is what Edge Functions use by default — was being blocked by the RLS policy. The Supabase client didn&#39;t throw an error. It returned an empty array. The Excel generator dutifully built a workbook with headers and no data rows.</p><p>The fix depends on your security requirements:</p><p><strong>Option 1 — Add a permissive read policy (simplest):</strong></p><pre>CREATE POLICY &quot;Allow anonymous reads on sales_orders&quot;<br>  ON sales_orders<br>  FOR SELECT<br>  USING (true);</pre><p><strong>Option 2 — Use the service role key inside the function (never exposed to browser):</strong></p><pre>// Use service role for server-side queries — safe because this runs server-side<br>const supabase = createClient(<br>  Deno.env.get(&#39;SUPABASE_URL&#39;)!,<br>  Deno.env.get(&#39;SUPABASE_SERVICE_ROLE_KEY&#39;)!  // Set via: supabase secrets set<br>)</pre><p>The service role key bypasses RLS entirely. It should only ever exist server-side — never sent to a browser. Inside an Edge Function, it’s safe to use.</p><h3>The Multi-Select Filter</h3><p>The client asked for multi-select dropdowns — select multiple vendors, multiple outlets. The native HTML &lt;select multiple&gt; works but looks terrible on most browsers. I built a custom tag-based selector instead.</p><p>The key: the function already handles arrays natively. Passing { vendors: [&#39;MBF PRINTING&#39;, &#39;SUPPLIER B&#39;] } and using .in(&#39;vendor_name&#39;, filters.vendors) works for any number of selections.</p><p>The UI concern is entirely separate from the API design. The function doesn’t care whether the user selected one value or ten — it receives an array either way.</p><h3>Database Schema</h3><p>The sales_orders table that feeds this entire system:</p><pre>CREATE TABLE sales_orders (<br>  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),<br>  item_name        VARCHAR(255)   NOT NULL,<br>  expiry_date      DATE           NOT NULL,<br>  lot_batch_no     VARCHAR(100)   NOT NULL,<br>  raw_code         VARCHAR(50)    NOT NULL,<br>  opening_balance  INTEGER        NOT NULL DEFAULT 0,<br>  stock_in_qty     INTEGER        NOT NULL DEFAULT 0,<br>  stock_in_date    DATE           NOT NULL,<br>  vendor_name      VARCHAR(255)   NOT NULL,<br>  stock_out_qty    INTEGER        NOT NULL DEFAULT 0,<br>  stock_out_outlet VARCHAR(255)   NOT NULL,<br>  closing_balance  INTEGER        NOT NULL DEFAULT 0<br>);<br><br>-- Index the columns used in filters and grouping<br>CREATE INDEX idx_so_vendor     ON sales_orders(vendor_name);<br>CREATE INDEX idx_so_outlet     ON sales_orders(stock_out_outlet);<br>CREATE INDEX idx_so_raw_code   ON sales_orders(raw_code);<br>CREATE INDEX idx_so_date       ON sales_orders(stock_in_date);<br>CREATE INDEX idx_so_item_name  ON sales_orders(item_name);</pre><h3>What I Would Do Differently</h3><p><strong>1. Test RLS from day one.</strong> The silent empty-array failure from RLS blocks costs significant debugging time. On every new Supabase project, I now run a quick supabase.from(&#39;table&#39;).select(&#39;count&#39;) immediately after setup to confirm the anon key can actually read the table.</p><p><strong>2. Split discovery and generation from the beginning.</strong> The two-function architecture wasn’t my first design — I arrived at it after hardcoding dropdown options and getting burned. This separation is obvious in retrospect. Any time a UI component needs to know “what values exist in the database right now,” that question deserves its own endpoint.</p><p><strong>3. Add CORS to error responses from the start.</strong> Not just to the happy path. The silent CORS-less error response pattern wastes a lot of time when debugging production issues.</p><p><strong>4. Keep the workbook builder as a pure function.</strong> The buildWorkbook(rows, filters) function takes data and returns a buffer. It knows nothing about Supabase, HTTP, or authentication. This made it trivial to test in isolation and easy to adapt when the client later asked for a second report type with a different layout.</p><h3>The Operational Lesson: Serverless Is Stateless — Design For It</h3><p>Supabase Edge Functions run on Deno, and each invocation is fresh. There is no warm state, no shared memory between calls, no connection pool to rely on.</p><p>This changes how you think about what belongs inside the function.</p><p>The Excel generation logic belongs there — it’s compute, it’s stateless, it produces an output and exits. The database connection belongs there too, but it is created fresh on every call (Supabase’s client handles this efficiently). Any state that needs to persist across calls — user selections, cached filter options, partially generated reports — must live in the database or the browser. Not in the function.</p><p>I initially designed the filter state to accumulate in the function across multiple requests. That worked locally. In production, with multiple Deno isolates handling different requests, the state was never shared. The feature silently didn’t work.</p><p>The lesson: <strong>treat every Edge Function invocation as if it is the first and only time that code will run.</strong> Pass everything it needs in the request. Return everything the caller needs in the response. Nothing persists.</p><p>That constraint is not a limitation. It is what makes serverless functions composable, independently deployable, and horizontally scalable without configuration.</p><h3>Deployment</h3><pre># Create both functions<br>supabase functions new get-filter-options<br>supabase functions new generate-inventory-report<br><br># Copy code into each function&#39;s index.ts<br># Set secrets (if using service role key)<br>supabase secrets set SUPABASE_SERVICE_ROLE_KEY=your_key_here<br># Deploy<br>supabase functions deploy get-filter-options<br>supabase functions deploy generate-inventory-report</pre><p>The SUPABASE_URL and SUPABASE_ANON_KEY are automatically injected into every Edge Function — you do not need to set them manually.</p><h3>curl Examples</h3><pre># Get filter options<br>curl -X GET \<br>  &quot;https://YOUR_PROJECT.supabase.co/functions/v1/get-filter-options&quot; \<br>  -H &quot;apikey: YOUR_ANON_KEY&quot;<br># Generate report - no filters (all data)<br>curl -X POST \<br>  &quot;https://YOUR_PROJECT.supabase.co/functions/v1/generate-inventory-report&quot; \<br>  -H &quot;apikey: YOUR_ANON_KEY&quot; \<br>  -H &quot;Content-Type: application/json&quot; \<br>  -d &#39;{}&#39; \<br>  -o report.xlsx<br># Generate report - filtered<br>curl -X POST \<br>  &quot;https://YOUR_PROJECT.supabase.co/functions/v1/generate-inventory-report&quot; \<br>  -H &quot;apikey: YOUR_ANON_KEY&quot; \<br>  -H &quot;Content-Type: application/json&quot; \<br>  -d &#39;{<br>    &quot;vendors&quot;: [&quot;MBF PRINTING&quot;],<br>    &quot;outlets&quot;: [&quot;Lituana&quot;, &quot;Lotuss Puchong&quot;],<br>    &quot;raw_codes&quot;: [&quot;RAW-E0011&quot;],<br>    &quot;date_from&quot;: &quot;2026-04-01&quot;,<br>    &quot;date_to&quot;: &quot;2026-04-30&quot;<br>  }&#39; \<br>  -o report_filtered.xlsx</pre><h3>Conclusion</h3><p>Can Supabase generate formatted Excel reports? Yes. The xlsx library works cleanly in Deno, the binary response streams correctly, and the filtering pattern composes well with Supabase&#39;s query builder.</p><p>The technical part — the workbook styling, the row grouping, the subtotal calculation — is implementation detail. The three decisions that actually shaped the architecture were:</p><ol><li><strong>Two functions, not one</strong> — separate discovery from generation</li><li><strong>Empty filter array = no restriction</strong> — not “zero results”</li><li><strong>CORS headers on every response</strong> — not just the happy path</li></ol><p>Everything else was straightforward once those three were right.</p><p><strong>Vembry Fiska</strong> Senior Power BI Developer &amp; Data Engineer</p><p><em>I build data pipelines and analytics tools for mid-market companies — from NetSuite ETL to Supabase backends to Power BI dashboards. If you’re building something similar and hitting walls, let’s talk.</em></p><p><strong>Tags:</strong> supabase typescript data-engineering excel backend</p><p><strong>SEO keywords:</strong> Supabase Edge Functions Excel, generate Excel file Supabase, Supabase TypeScript report, Deno xlsx library, Supabase filter dropdown database</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a1cb25a54bcc" width="1" height="1" alt=""><hr><p><a href="https://blog.dataengineerthings.org/i-built-a-report-generator-inside-supabase-edge-functions-heres-what-i-learned-a1cb25a54bcc">I Built a Report Generator Inside Supabase Edge Functions — Here’s What I Learned</a> was originally published in <a href="https://blog.dataengineerthings.org/">Data Engineer Things</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[I Let an AI Agent Build My Power BI Dashboard. Here’s What Actually Happened.]]></title>
            <link>https://medium.com/microsoft-power-bi/i-let-an-ai-agent-build-my-power-bi-dashboard-heres-what-actually-happened-eaaea058ba2f?source=rss-4a74082fcf9d------2</link>
            <guid isPermaLink="false">https://medium.com/p/eaaea058ba2f</guid>
            <category><![CDATA[agentic-ai]]></category>
            <category><![CDATA[claude-code]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[microsoft-fabric]]></category>
            <dc:creator><![CDATA[Vembry Fiska]]></dc:creator>
            <pubDate>Tue, 21 Jul 2026 03:14:46 GMT</pubDate>
            <atom:updated>2026-07-21T03:14:46.017Z</atom:updated>
            <content:encoded><![CDATA[<h3>Testing Microsoft’s brand-new Fabric Skills with Claude Code — the wins, the one error that stopped me cold, and what it taught me about where AI actually helps in data engineering.</h3><p>In June 2026, Microsoft quietly open-sourced something called <strong>Fabric Skills</strong> — a toolkit that lets AI coding agents like Claude Code, GitHub Copilot CLI, Cursor, and Windsurf directly author Power BI semantic models and reports from plain natural language. No clicking through the Power BI Desktop UI. No manually writing TMDL. Just a prompt.</p><p>As someone who’s spent over a decade building Power BI solutions — teaching DAX patterns, architecting semantic models, debugging things at 2am for clients — I had one reaction: <em>I need to see if this actually works, or if it’s another over-promised AI feature.</em></p><p>So I set it up and tried it myself. Here’s exactly what happened.</p><h3>Getting it running</h3><p>The setup was straightforward once I knew the steps:</p><ol><li>Installed Claude Code on my machine</li><li>Inside a Claude Code session, added Microsoft’s plugin marketplace: microsoft/skills-for-fabric</li><li>Installed four plugin bundles: fabric-authoring, fabric-consumption, fabric-operations, and powerbi-authoring</li></ol><p>Nothing exotic — just the normal friction of setting up a new dev tool (a missing PATH variable here, an Azure CLI login there). Once it was in place, I was ready for the real test.</p><h3>Test one: can it build a semantic model from scratch?</h3><p>I gave it a simple, honest test — not a softball, but not a trap either:</p><blockquote><em>“Create a sample CSV with columns Date, Region, Product, Revenue, then build a Power BI project (PBIP) with a date table and time-intelligence measures.”</em></blockquote><p>I expected it to fumble the DAX or produce something half-working. Instead, it generated a working semantic model — proper date table marked correctly, and measures for YTD, MTD, QTD, and YoY growth, all wired up correctly. I opened the resulting .pbip file in Power BI Desktop, and it rendered cleanly. No repair prompts, no broken relationships.</p><p>That alone told me this isn’t vaporware. The underlying skill genuinely understands Power BI’s semantic model structure — TMDL syntax, relationship definitions, standard time-intelligence DAX patterns — well enough to produce something that opens correctly on the first try.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0tdsU25dqYaRYnBjsjBG_Q.png" /></figure><h3>Test two: a real executive dashboard</h3><p>Encouraged, I pushed further. I described a full executive-style dashboard — slicers across the top, a line chart comparing this year to last year, a donut chart by category, bar charts by region and by top performers.</p><p>This is the layout I’d normally spend a couple of focused hours building by hand: getting the visuals aligned, getting the DAX right, getting the formatting consistent.</p><p>The agent built it. Not pixel-perfect on the first pass — I had to go back and ask it to align the KPI cards and adjust chart heights — but structurally, everything was there and functionally correct.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NsCqV0VgVHH8aYRka2o8eg.png" /></figure><figure><img alt="relationship build on pbip by claude code using fabric skills" src="https://cdn-images-1.medium.com/max/1024/1*6vaKOjN3Xw7Wzm4IlFcOgQ.png" /></figure><h3>Then it broke — and that’s the interesting part</h3><p>On a later edit, I hit this error trying to reopen the file:</p><blockquote>“Tabular databases do not support CompatibilityLevel downgrade. Current CompatibilityLevel: ‘1600’. Requested CompatibilityLevel: ‘1567’.”</blockquote><p>Translation: the agent had written a <strong>lower</strong> compatibility level into the model definition than what my existing model was already running. Power BI’s engine won’t let a tabular model go backwards in compatibility level, so it refused to load.</p><p>The fix itself was trivial — I asked Claude to find and correct the compatibilityLevel value in the TMDL file, and it fixed it in one pass. But the error itself is the real lesson here.</p><h3>The actual takeaway</h3><p>This wasn’t a case of the AI being “wrong” in some deep architectural sense. It was a small, specific oversight — not checking what compatibility level the existing model already had before writing a default value. But if you don’t know what a compatibility level <em>is</em>, what TMDL is, or where in the project structure that value lives, this error would stop you completely. You wouldn’t know what went wrong, let alone how to fix it.</p><p>That’s the pattern I keep seeing with agentic AI tools across data engineering, not just Power BI: <strong>they compress the mechanical distance between “I have an idea” and “there’s a working artifact,” but they don’t compress the need to understand what’s actually being built.</strong> The person who can read a TMDL file, who knows what a compatibility level downgrade means, who can sanity-check a generated DAX measure against a business requirement — that person gets genuine leverage from these tools. The person who can’t do those things gets stuck exactly at the point where the tool’s confidence outpaces its correctness.</p><p>If anything, tools like this make the gap between “understands the fundamentals” and “just clicks buttons” more visible, not less. The agent removes the tedious typing. It doesn’t remove the need to know what you’re looking at.</p><h3>Should you try it?</h3><p>Yes — if you’re a Power BI developer, data engineer, or analyst who already understands semantic modeling, this is genuinely useful and worth adding to your workflow now, not later. It won’t replace the judgment calls, but it will save you real hours on the mechanical scaffolding.</p><p>If you’re newer to Power BI and hoping this lets you skip learning DAX and data modeling fundamentals — I’d push back on that. The tool amplifies what you already know. It doesn’t substitute for it.</p><p><em>I write about Power BI, data engineering, and NetSuite integration. If you’re exploring how agentic AI fits into your own BI workflow, or want a hand setting up something similar, feel free to reach out.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=eaaea058ba2f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/microsoft-power-bi/i-let-an-ai-agent-build-my-power-bi-dashboard-heres-what-actually-happened-eaaea058ba2f">I Let an AI Agent Build My Power BI Dashboard. Here’s What Actually Happened.</a> was originally published in <a href="https://medium.com/microsoft-power-bi">Power BI</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Debug Slow Power BI Reports: The Checklist]]></title>
            <link>https://medium.com/microsoft-power-bi/how-to-debug-slow-power-bi-reports-the-checklist-a034c2df2718?source=rss-4a74082fcf9d------2</link>
            <guid isPermaLink="false">https://medium.com/p/a034c2df2718</guid>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Vembry Fiska]]></dc:creator>
            <pubDate>Mon, 29 Jun 2026 13:31:01 GMT</pubDate>
            <atom:updated>2026-07-10T20:45:04.998Z</atom:updated>
            <content:encoded><![CDATA[<p><em>A systematic diagnostic workflow for when your dashboard is slow and you don’t know why — from first symptom to root cause in under an hour</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ytzAY2Trpg8oij2SnNHnng.png" /><figcaption>debug slow power bi</figcaption></figure><h3>The Problem With Slow Reports</h3><p>You open the report. It loads. But the filters take 8 seconds. The page refresh is 25 seconds. Users are complaining.</p><p>You stare at it. The data looks right. The visuals look fine. Nothing is obviously broken.</p><p>Where do you even start?</p><p>Most developers start in the wrong place. They look at the visuals. They check the data source connection. They refresh it a few times hoping the problem goes away.</p><p>The real diagnostic workflow starts somewhere completely different — and it follows a specific order, because the root cause of a slow report almost always lives in one of five layers. Finding the right layer first saves you hours of guessing.</p><p>This article is that workflow, built into a checklist you can follow every time.</p><p>By the end, you will know:</p><ul><li>How to isolate whether the problem is in the data model, the DAX, the visuals, the service, or the data source</li><li>Which tools to use at each diagnostic step</li><li>The exact questions to ask at each layer</li><li>How to interpret what the tools are telling you</li><li>How to fix what you find</li></ul><p><strong>🎁 </strong><a href="https://powerbi-masterclass.short.gy/learning-database?utm_source=medium&amp;utm_medium=medium-post&amp;utm_campaign=database-medium-post-start"><strong>Get friend links for all of our 1500&gt; Power BI learning articles here</strong></a><strong> 🎁</strong></p><h3>The Operational Lesson: Diagnosis Before Optimization</h3><p>Before the checklist, the principle it is built on.</p><p>I have seen developers spend three hours rewriting DAX measures on a report that was slow because of a misconfigured scheduled refresh, not the measures themselves. I have seen someone add aggregations to a model that was slow because fifty visuals were all querying the same measure simultaneously — a layout problem, not a modeling problem.</p><p>Optimization without diagnosis is guessing. Guessing wastes time and often makes things worse.</p><p>The rule I follow on every slow report engagement: <strong>spend the first thirty minutes diagnosing only — make no changes until you know what the root cause is.</strong> A correct diagnosis followed by a targeted fix takes two hours. A wrong diagnosis followed by optimization in the wrong layer takes two days.</p><p>The checklist below is the thirty-minute diagnosis. Everything after it is the fix.</p><h3>The Five Layers of Performance Problems</h3><p>The checklist follows this exact order — from the outermost layer (Service) to the innermost (Data Source) — because the outermost problems are fastest to diagnose and easiest to fix.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xgB6Y1EXUHy-IyGCQzCfIg.png" /><figcaption>The Five Layers of Performance Problems</figcaption></figure><h3>The Diagnostic Checklist</h3><h3>✅ LAYER 5: Power BI Service (Check First — Takes 5 Minutes)</h3><p><strong>Step 1: Is this a Service problem or a Desktop problem?</strong></p><p>Open the report in Power BI Desktop (local file, no service connection). Is it fast there?</p><pre>Fast in Desktop, slow in Service  →  Service / capacity problem<br>Slow in Desktop AND Service       →  Model or DAX problem</pre><p>If Desktop is fast, everything below this is irrelevant. The issue is in the Service layer.</p><p><strong>Step 2: Check Capacity</strong></p><p>In Power BI Service, go to Settings → Capacity. Note which capacity the workspace is on:</p><ul><li><strong>Shared capacity (F0 / no dedicated capacity)</strong> — you share resources with thousands of other tenants. Peak hours (9–11 AM, 2–4 PM) mean slower reports.</li><li><strong>Premium / Fabric capacity</strong> — dedicated resources. Slowness here is a real model or DAX problem, not a capacity ceiling.</li></ul><p><strong>Step 3: Check the Gateway</strong></p><p>If your data source is on-premises (SQL Server, Oracle, file share), it routes through a data gateway. A misconfigured or overloaded gateway is a common and often-missed cause.</p><p>Go to Settings → Manage Gateways → check the gateway status. Look for:</p><ul><li>Gateway offline or degraded</li><li>Multiple datasets all refreshing simultaneously through the same gateway</li><li>Gateway machine has low CPU/memory headroom</li></ul><p><strong>Step 4: Check Refresh Schedule Conflicts</strong></p><p>If the report is slow at a specific time each day, check the refresh schedule. A dataset refreshing while users are querying it can cause both the refresh and the queries to queue.</p><h3>✅ LAYER 4: Visual Layer (Check Second — Takes 5 Minutes)</h3><p><strong>Step 5: Count the Visuals</strong></p><p>Count every visual on the slowest page — including hidden visuals, bookmark-switched panels, and the report header.</p><pre>Visual count guidelines:<br>&lt; 8 visuals    → Not the issue, move to Layer 3<br>8 – 15 visuals → May contribute; look for interaction patterns<br>&gt; 15 visuals   → Almost certainly contributing to slowness</pre><p>Each visual generates at least one DAX query. On page load, all visuals execute simultaneously. 20 visuals = 20 parallel queries competing for the same engine resources.</p><p><strong>Step 6: Open Performance Analyzer</strong></p><p>This is the single most useful tool for visual-layer diagnosis.</p><p>In Power BI Desktop: View → Performance Analyzer → Start Recording → Refresh Visuals</p><p>Let the page fully load, then stop the recording. You will see:</p><pre>Performance Analyzer output (example):<br>─────────────────────────────────────────────────<br>Visual: Revenue Chart<br>  DAX query:    2,847 ms    ← DAX is slow<br>  Visual display: 124 ms</pre><pre>Visual: Region Map<br>  DAX query:      312 ms<br>  Visual display: 8,203 ms  ← Visual rendering is slow</pre><pre>Visual: Table (hidden)<br>  DAX query:    1,204 ms    ← Hidden visual still queries!<br>  Visual display: 0 ms<br>─────────────────────────────────────────────────</pre><p><strong>What to look for:</strong></p><ul><li><strong>DAX query time &gt; 1,000 ms</strong> → Layer 3 problem (DAX or model)</li><li><strong>Visual display time &gt; 1,000 ms</strong> → Map, custom visual, or scatter chart rendering issue</li><li><strong>Hidden visuals with high DAX query time</strong> → Remove or use bookmarks properly</li><li><strong>Many visuals with 200–500 ms each</strong> → Page has too many visuals; total adds up</li></ul><p><strong>Step 7: Click Each Slicer Individually</strong></p><p>After the initial load, click each slicer once and watch Performance Analyzer update. Note which slicer triggers the highest DAX query time on other visuals. That slicer-visual pair is your hotspot.</p><h3>✅ LAYER 3: DAX (Check Third — Takes 10 Minutes)</h3><p>Performance Analyzer showed you which visuals are slow. Now export the slow DAX query and analyze it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LQEHXYhdrOUr6XsUxfUW8Q.png" /><figcaption>daxstudio</figcaption></figure><p><strong>Step 8: Copy the DAX Query From Performance Analyzer</strong></p><p>In Performance Analyzer, click the slow visual → “Copy query”. This gives you the exact DAX query Power BI is executing.</p><p><strong>Step 9: Open DAX Studio</strong></p><p>Download DAX Studio (free): <a href="https://daxstudio.org/">https://daxstudio.org</a></p><p>Connect to your Power BI Desktop file → paste the query → run it.</p><p>DAX Studio shows:</p><ul><li><strong>Total query time</strong></li><li><strong>Server timings</strong> (formula engine vs. storage engine)</li><li><strong>Query plan</strong> (what VertiPaq is actually doing)</li></ul><pre>DAX Studio Server Timings (what to look for):<br>─────────────────────────────────────────────<br>Formula Engine (FE): 4,823 ms   ← High FE = bad DAX<br>Storage Engine (SE): 287 ms</pre><pre>Ratio: FE &gt;&gt; SE  →  DAX problem, not model problem<br>Ratio: SE &gt;&gt; FE  →  Model problem, not DAX problem</pre><p><strong>Step 10: Identify the DAX Anti-Pattern</strong></p><p>High Formula Engine time almost always traces back to one of these:</p><pre>Anti-pattern 1: SUMPRODUCT on large table<br>SUMPRODUCT(Table[Col1], Table[Col2])<br>Fix: Use SUMX(Table, Table[Col1] * Table[Col2])<br><br>Anti-pattern 2: Nested CALCULATE<br>CALCULATE(CALCULATE(SUM(...), filter1), filter2)<br>Fix: Single CALCULATE with multiple filters<br><br>Anti-pattern 3: FILTER() inside aggregation<br>SUMX(FILTER(Sales, Sales[Region] = &quot;US&quot;), Sales[Amount])<br>Fix: CALCULATE(SUM(Sales[Amount]), Sales[Region] = &quot;US&quot;)<br><br>Anti-pattern 4: Row-by-row RELATED() in large table<br>SUMX(Sales, RELATED(Product[Price]) * Sales[Qty])<br>Fix: Pre-compute in the model as a calculated column,<br>     or restructure the join<br><br>Anti-pattern 5: ALL() on fact table inside measure<br>CALCULATE(SUM(Sales[Amount]), ALL(Sales))<br>Fix: Use ALL(DimDate) or specific dimension columns,<br>     not the entire fact table</pre><h3>✅ LAYER 2: Data Model (Check Fourth — Takes 10 Minutes)</h3><p>If DAX Studio showed high Storage Engine time (SE &gt;&gt; FE), the problem is in the model structure, not the DAX code.</p><p><strong>Step 11: Check Table Structure in Model View</strong></p><p>Open Model View in Power BI Desktop. Check:</p><pre>✅ Star schema — FactSales in center, Dims around it<br>❌ Flat table — one table with 50+ columns<br>❌ Snowflake — dimension chained to dimension to dimension<br>❌ Many-to-many without bridge table</pre><p>A flat table means every filter triggers a full table scan. No index, no aggregation benefit. This is the most common root cause of slow models.</p><p><strong>Step 12: Check Relationships</strong></p><p>In Model View, click each relationship line:</p><ul><li>Is the cardinality correct? (Most should be Many-to-One)</li><li>Is the cross-filter direction Single? (Bidirectional = dangerous)</li><li>Is the active relationship actually active? (Dotted line = inactive)</li></ul><p><strong>Step 13: Check Column Cardinality in DAX Studio</strong></p><p>In DAX Studio → DMV Queries → $SYSTEM.TMSCHEMA_COLUMNS</p><pre>SELECT<br>    [TABLE_ID],<br>    [COLUMN_ID],<br>    [CARDINALITY],<br>    [DICTIONARY_SIZE]<br>FROM $SYSTEM.TMSCHEMA_COLUMNS<br>ORDER BY [CARDINALITY] DESC</pre><p>Look for:</p><ul><li><strong>Columns with very high cardinality in the fact table</strong> (e.g., a timestamp column with millisecond precision) — these compress poorly and slow everything down</li><li><strong>Text columns in the fact table</strong> (product names, customer names repeated millions of times) — these should be in dimension tables</li></ul><p><strong>Step 14: Check If Aggregations Are Being Used</strong></p><p>In DAX Studio, run a query and look at the Server Timings. If you see “AggStorage” in the storage engine callouts, aggregations are being used. If every query goes to “DirectStorage”, your aggregations are either missing or not matching the queries.</p><h3>✅ LAYER 1: Data Source (Check Last — Takes 5 Minutes)</h3><p>If everything above looks healthy, the problem might be the data source itself.</p><p><strong>Step 15: Measure Refresh Time vs. Query Time</strong></p><p>There are two distinct slowness patterns with data sources:</p><pre>Pattern A: Report is slow to LOAD (first open)<br>→ The semantic model is large and takes time to load into memory<br>→ Check model file size (should be &lt; 500 MB for most use cases)<br><br>Pattern B: Report is slow to REFRESH (scheduled refresh)<br>→ The query sent to the source is slow<br>→ Check the source database query performance directly</pre><p><strong>Step 16: Check Source Query Performance Directly</strong></p><p>If using Import mode — go to Power Query Editor → right-click a table → “View Native Query” → copy and run that query directly in your database tool (SSMS, pgAdmin, DBeaver).</p><p>If the query is slow there too, the problem is the source database, not Power BI:</p><ul><li>Missing indexes on filter columns</li><li>Table scan on a large table</li><li>Expensive JOIN or subquery in the view Power BI is querying</li></ul><p><strong>Step 17: Check DirectQuery Specifically</strong></p><p>If using DirectQuery mode, every visual interaction sends a new query to the source. Run Performance Analyzer and check whether DirectQuery latency is the issue:</p><pre>Performance Analyzer — DirectQuery:<br>Visual: Revenue Chart<br>  DirectQuery: 3,847 ms  ← Source database is slow<br>  DAX query: 52 ms</pre><p>If DirectQuery is consistently high (&gt; 2,000 ms per visual), the fix is at the source: add indexes, rewrite the view, or switch relevant tables to Import mode.</p><h3>The Complete Checklist (Quick Reference)</h3><pre>LAYER 5 — SERVICE (5 min)<br>□ Fast in Desktop but slow in Service?     → Capacity / gateway<br>□ On shared capacity?                      → Upgrade or schedule off-peak<br>□ Gateway online and healthy?              → Check gateway machine<br>□ Refresh schedule conflicting with usage? → Reschedule refresh<br><br>LAYER 4 - VISUALS (5 min)<br>□ More than 15 visuals on one page?       → Reduce visual count<br>□ Performance Analyzer: which visual is slow? → Identify hotspot<br>□ DAX query time &gt; 1,000 ms?              → Move to Layer 3<br>□ Visual display time &gt; 1,000 ms?         → Replace chart type<br>□ Hidden visuals with high query time?    → Remove or fix bookmarks<br><br>LAYER 3 - DAX (10 min)<br>□ Copy slow query to DAX Studio           → Measure exact time<br>□ FE time &gt;&gt; SE time?                     → DAX anti-pattern<br>□ SUMPRODUCT in measures?                 → Replace with SUMX<br>□ Nested CALCULATE?                       → Flatten to single<br>□ FILTER() inside SUMX?                   → Replace with CALCULATE<br>□ ALL() on fact table?                    → Scope to dimension<br><br>LAYER 2 - DATA MODEL (10 min)<br>□ Flat table instead of star schema?      → Restructure model<br>□ Bidirectional relationships?            → Change to single<br>□ High-cardinality columns in fact table? → Move to dimension<br>□ Text columns in fact table?             → Move to dimension<br>□ Missing aggregations?                   → Add for common filters<br><br>LAYER 1 - DATA SOURCE (5 min)<br>□ Model file size &gt; 500 MB?               → Reduce columns / history<br>□ Source query slow in database tool?     → Add index / rewrite query<br>□ DirectQuery latency &gt; 2,000 ms?        → Add index or use Import<br>□ Large table without date partition?     → Partition the source table</pre><h3>Diagnostic Decision Tree</h3><pre>START: Report is slow<br>         │<br>         ▼<br>Fast in Desktop?<br>  │<br>  ├─ NO  → Problem is model/DAX<br>  │         → Skip to Layer 3 checklist<br>  │<br>  └─ YES → Open Performance Analyzer<br>              │<br>              ▼<br>         Which metric is high?<br>           │<br>           ├─ Visual display time →  Chart type / render issue<br>           │                         → Swap visual type or reduce data points<br>           │<br>           ├─ DAX query time      →  Open DAX Studio<br>           │                         → Check FE vs SE ratio<br>           │                         │<br>           │                         ├─ FE &gt;&gt; SE → Fix DAX anti-pattern<br>           │                         └─ SE &gt;&gt; FE → Fix data model<br>           │<br>           ├─ DirectQuery time    →  Fix source database<br>           │                         → Add index, rewrite view<br>           │<br>           └─ Everything &lt; 500ms → Service / capacity problem<br>                                    → Check gateway, capacity tier</pre><h3>Tools Summary</h3><p>Tool Where What It Diagnoses <strong>Performance Analyzer</strong> Power BI Desktop → View Visual-level timing: DAX query, visual display, DirectQuery <strong>DAX Studio</strong> daxstudio.org (free) Measure-level timing: FE vs SE, query plan, callout analysis <strong>Model View</strong> Power BI Desktop Relationships, cardinality, structure <strong>Power Query</strong> Power BI Desktop → Transform Data Source query, data type issues, step performance <strong>Capacity Metrics App</strong> Power BI Service CPU, memory, query duration at capacity level <strong>Gateway Logs</strong> On-Premises Gateway app Gateway latency, timeouts, connection errors</p><h3>The Operational Lesson: Keep a Diagnostic Log</h3><p>One practice that has saved me hours across projects: keep a running log of every slow report you diagnose, including what the root cause turned out to be.</p><p>After twenty or thirty diagnostics, a pattern emerges. In most organisations, the same two or three root causes account for 80% of slow reports. For some clients it is always flat tables. For others it is always DirectQuery with missing indexes. For others it is always capacity limits at peak hours.</p><p>Once you know your client’s recurring root cause, you can skip straight to that layer first. The checklist is the general case. Experience lets you shortcut it.</p><pre>Example diagnostic log (after 6 months):<br>─────────────────────────────────────────────────────<br>Report: Sales Dashboard v3<br>Root cause: SUMPRODUCT in YTD measure scanning 12M rows<br>Fix: Replaced with CALCULATE + DATESYTD<br>Time to diagnose: 18 min<br>Time to fix: 12 min<br><br>Report: Inventory Report<br>Root cause: 28 visuals on one page, 14 hidden<br>Fix: Moved hidden visuals to separate page, used bookmarks<br>Time to diagnose: 8 min<br>Time to fix: 25 min<br><br>Report: Finance Summary<br>Root cause: Gateway overloaded, 6 datasets refreshing at once<br>Fix: Staggered refresh schedules, 30 min apart<br>Time to diagnose: 5 min<br>Time to fix: 3 min<br>Pattern identified: 70% of slowness in this client is<br>visual count + hidden visuals. Check Layer 4 first.</pre><p>The log itself becomes a tool. New developers on the project can read it before starting a diagnosis and skip the most common false paths. The knowledge doesn’t live only in your head.</p><h3>What Comes After the Diagnosis</h3><p>This article is about finding the problem. The fixes depend on which layer you found it in:</p><ul><li><strong>Service / capacity</strong> → Reschedule, upgrade capacity, or redesign for off-peak</li><li><strong>Visual layer</strong> → Reduce visuals, fix bookmarks, swap chart types</li><li><strong>DAX</strong> → Rewrite measures (SUMX not SUMPRODUCT, single CALCULATE, KEEPFILTERS)</li><li><strong>Data model</strong> → Restructure to star schema, remove bidirectional, add aggregations</li><li><strong>Data source</strong> → Add indexes, rewrite views, partition large tables, move to Import</li></ul><p>Each of these is a full topic on its own. But none of them are worth starting without first knowing which layer is the problem.</p><p>Diagnose first. Then fix.</p><p><strong>Vembry Fiska</strong> Senior Power BI Developer &amp; Data Engineer</p><p><em>I debug slow Power BI reports for mid-market companies. Most problems trace back to the same five layers — and most diagnoses take under thirty minutes when you know the order to check.</em></p><p><strong>Tags:</strong> power-bi data-analytics dax business-intelligence performance</p><p><strong>SEO keywords:</strong> Power BI slow report, Power BI performance debug, DAX Studio Power BI, Performance Analyzer Power BI, Power BI checklist, slow Power BI dashboard fix</p><p><strong>💡 </strong><a href="https://powerbi-masterclass.short.gy/pbi-gpt?utm_source=medium&amp;utm_campaign=pbi-gpt-medium-post-end"><strong>MUST TRY — Power BI GPT — Personal Power BI Learning Coach</strong></a><strong>💡</strong></p><p><strong>Power BI Masterclass Article Classification</strong></p><p><strong>Level:</strong> Beginner</p><p><strong>Category:</strong> Data Model</p><p><strong>Tags:</strong> Tutorial, Data Model</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a034c2df2718" width="1" height="1" alt=""><hr><p><a href="https://medium.com/microsoft-power-bi/how-to-debug-slow-power-bi-reports-the-checklist-a034c2df2718">How to Debug Slow Power BI Reports: The Checklist</a> was originally published in <a href="https://medium.com/microsoft-power-bi">Power BI</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Power BI vs Microsoft Fabric: What’s the Difference and Which One Should You Learn?]]></title>
            <link>https://medium.com/@fiska.nurwantoro/power-bi-vs-microsoft-fabric-whats-the-difference-and-which-one-should-you-learn-e8e29b408b2a?source=rss-4a74082fcf9d------2</link>
            <guid isPermaLink="false">https://medium.com/p/e8e29b408b2a</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[microsoft-fabric]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <dc:creator><![CDATA[Vembry Fiska]]></dc:creator>
            <pubDate>Thu, 25 Jun 2026 12:56:00 GMT</pubDate>
            <atom:updated>2026-07-14T09:41:28.920Z</atom:updated>
            <content:encoded><![CDATA[<p><em>The honest answer that most tutorials skip — including why the question itself might be framed wrong</em></p><h3>The Question Every Beginner Asks</h3><p>You’ve heard of Power BI. Maybe you’ve built a dashboard or two. Then someone mentions Microsoft Fabric and suddenly the ground shifts.</p><p>Is Fabric replacing Power BI? Do you need to learn both? Are they the same thing? Is your Power BI knowledge about to become obsolete?</p><p>I get this question from students at my workshops and from junior developers on new projects. The confusion is understandable because Microsoft’s marketing doesn’t help — they announce Fabric as “the future of analytics” without clearly explaining what that means for the millions of people who already know Power BI.</p><p>Here is the clearest answer I can give:</p><p><strong>Power BI is a tool. Microsoft Fabric is a platform. Power BI lives inside Fabric — but Fabric is much bigger than Power BI.</strong></p><p>That one sentence, properly understood, answers most of the confusion. Everything below is the elaboration.</p><h3>What Power BI Actually Is</h3><p>Power BI is Microsoft’s business intelligence and data visualization tool. It has three main components:</p><p><strong>Power BI Desktop</strong> — a free Windows application you download and use to build reports. This is where most people start. You connect to data, clean it with Power Query, model it, write DAX measures, and design visuals.</p><p><strong>Power BI Service</strong> — the cloud platform at app.powerbi.com. You publish reports here, share them with colleagues, set up scheduled refreshes, and create dashboards. This is where your work lives after you build it in Desktop.</p><p><strong>Power BI Mobile</strong> — apps for iOS and Android that let users view published reports on phones and tablets.</p><pre>What Power BI Does:<br>─────────────────────────────────────────<br>Connect to data sources (Excel, databases,<br>APIs, cloud storage)<br>       ↓<br>Clean and transform data (Power Query)<br>       ↓<br>Build a data model (relationships, measures)<br>       ↓<br>Create visualizations (charts, tables, cards)<br>       ↓<br>Share with your organization (Power BI Service)<br>─────────────────────────────────────────</pre><p>Power BI is excellent at this specific workflow. It is one of the most widely used BI tools in the world, and for most reporting and dashboard needs, it is all you need.</p><p><strong>What Power BI does NOT do:</strong></p><ul><li>Store large volumes of raw data</li><li>Run complex data engineering pipelines</li><li>Train machine learning models at scale</li><li>Process real-time event streams</li><li>Orchestrate multi-step data workflows</li><li>Provide a unified data lake for your organization</li></ul><p>This is where Fabric comes in.</p><h3>What Microsoft Fabric Actually Is</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_qWwp2rYc92TORK4_vqJ5A.png" /><figcaption>fabric</figcaption></figure><p>Microsoft Fabric, launched in 2023, is an end-to-end analytics platform. Think of it as a single environment that combines all the tools a data team needs — from raw data ingestion all the way to final reports — into one unified product.</p><p>Fabric has <strong>nine workloads</strong>. Each one handles a different part of the data lifecycle:</p><pre>Microsoft Fabric — Nine Workloads:<br>─────────────────────────────────────────────────────────<br>1. Data Factory        → Move and transform data (ETL/ELT)<br>2. Data Engineering    → Notebooks, Spark, code-based transforms<br>3. Data Warehouse      → SQL-based analytical warehouse<br>4. Lakehouse           → Combined lake + warehouse storage<br>5. Real-Time Intelligence → Streaming data, event processing<br>6. Data Science        → ML model training and deployment<br>7. Power BI            → Reports, dashboards, visualizations<br>8. Data Activator      → Alerts and automated actions on data<br>9. OneLake             → The single unified storage layer<br>─────────────────────────────────────────────────────────</pre><p>Power BI is workload number seven. It is one piece of the platform — an important piece, the one most business users interact with, but one piece.</p><p><strong>OneLake</strong> is the most important concept to understand. It is the single storage layer that all nine workloads share. Your data engineer loads raw sales data into OneLake. Your data warehouse runs SQL queries against it. Your data science team trains models on it. Your Power BI reports read from it. Everything reads from the same place, in the same format, with no copying or syncing required.</p><p>This “one copy of data” principle is what makes Fabric genuinely new, not just a rebrand of existing tools.</p><h3>The Architecture: How They Relate</h3><p>Here is the relationship in plain terms:</p><pre>Before Fabric (the old world):<br>───────────────────────────────────────────────────────<br>Azure Data Lake  ←──────────────────────────────────┐<br>Azure Synapse    ←── separate tools, separate costs  │<br>Azure Databricks ←── complex integration needed      │<br>Azure Data Factory←─ different teams maintain each   │<br>Power BI         ←──────────────────────────────────┘<br>Result: 5 different products, 5 different bills,<br>        complex pipelines between each one</pre><pre>After Fabric (the new world):<br>───────────────────────────────────────────────────────<br>┌─────────────────────────────────────────────────────┐<br>│            Microsoft Fabric                         │<br>│                                                     │<br>│  OneLake (one storage layer for everything)         │<br>│     ↑            ↑            ↑            ↑        │<br>│  Data         Data         Power BI    Real-Time    │<br>│  Factory   Engineering    Reports     Intelligence  │<br>│                                                     │<br>│  All workloads share the same data automatically    │<br>└─────────────────────────────────────────────────────┘<br>Result: One product, one bill, one place to find data</pre><p>The practical meaning: a data engineer building a pipeline in Data Factory writes data to OneLake. A Power BI developer then connects directly to that same OneLake storage — no export, no copy, no API call. The data is simply there.</p><h3>Side-by-Side Comparison</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/985/1*6mBCtgQ9FBzKGfpW27b1cg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lP7rfXiXoIVYKX-dOa2Jrw.png" /><figcaption>comparison Power BI vs Fabric</figcaption></figure><h3>The Operational Lesson: Learn Power BI First</h3><p>This is the lesson most “Power BI vs Fabric” articles skip, and it matters most for beginners.</p><p>Microsoft Fabric is powerful. It is also genuinely complex. The nine workloads each have their own learning curve. A beginner who tries to learn “Microsoft Fabric” from scratch often ends up learning nothing properly because they’re spread across too many tools at once.</p><p>Here is what I’ve observed across many workshops and projects:</p><p>The people who become productive with Fabric fastest are almost always people who already knew Power BI well. They understood data modeling, filter context, and the concept of a semantic layer before they encountered Fabric’s broader architecture. When they arrived at Fabric, they already knew the reporting layer — so they could focus on learning the pipeline and storage layers that Fabric added.</p><p>The people who struggled most were those who tried to learn everything simultaneously: Lakehouse, Notebooks, Data Factory, Power BI, OneLake — all at once. They got a surface-level understanding of everything and a deep understanding of nothing.</p><p><strong>The practical path:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sOXDvG3X_cz1Z_0m34cOpA.png" /><figcaption>learning power BI to fabric</figcaption></figure><p>↳ Data modeling (star schema) ↳ Power Query basics ↳ Core DAX (CALCULATE, SUMX, time intelligence) ↳ Publish and share via Power BI Service</p><p>Month 4–6: Add Fabric fundamentals ↳ What OneLake is and how it works ↳ Lakehouses vs. Data Warehouses ↳ Connect Power BI reports to Fabric sources</p><p>Month 7–12: Pick one Fabric workload to go deep on ↳ If you’re a data engineer: Data Factory + Lakehouse ↳ If you’re an analyst: Dataflows + Warehouse ↳ If you’re building ML: Data Science workload</p><p>Year 2+: Broaden across workloads as projects require</p><blockquote>The Power BI foundation makes everything else in Fabric easier to learn. The Fabric platform makes Power BI more powerful. They are designed to work together — but one of them is a much better starting point.</blockquote><pre>## The Second Operational Lesson: <br>Fabric Does Not Replace Your Power BI Skills<br><br>There is an anxiety that runs through the data community <br>every time Microsoft announces something new: <br>&quot;Is what I already know becoming obsolete?&quot;<br>With Fabric, the honest answer is no.<br><br>Every Power BI skill you have today works inside Fabric. <br>Every report you&#39;ve built in Power BI Desktop can be published <br>to a Fabric workspace. <br>Every DAX measure, every data model, every Power Query transformation <br>- they all transfer directly, without modification.<br><br>What Fabric adds is a better foundation underneath <br>Power BI — better storage, <br>better pipelines, better data sharing across teams. <br>It does not change what happens at the reporting layer.<br>Think of it this way: if Power BI is the front end of a house, <br>Fabric is a renovation of the plumbing and foundations. <br>The rooms you already built still work. <br>The pipes just move water better now.<br>This matters practically because <br>it changes how you should think about your learning investment. <br>Skills you build in Power BI today are not a dead end. <br>They are the entry point to a larger platform that <br>your organization will increasingly rely on.<br>The people who will be most valuable <br>as Fabric adoption grows are not those <br>who abandoned Power BI to learn Fabric. <br>They are those who built strong Power BI foundations <br>and then extended upward into the broader platform.<br><br>## What You Actually Get With the Free Fabric Trial<br>Microsoft offers a 60-day free trial of Fabric with full capacity. <br>Here is what you get and how to use it effectively:<br><br>**What&#39;s included:**<br>- Full access to all nine Fabric workloads<br>- 64 capacity units (enough for any learning project)<br>- OneLake storage<br>- Fabric-enabled Power BI Premium features<br><br>**How to activate:**<br>1. Go to app.fabric.microsoft.com<br>2. Sign in with a Microsoft account (Outlook, Hotmail, or work email)<br>3. Click &quot;Start trial&quot; in the account menu<br>4. Your workspace is automatically Fabric-enabled<br></pre><p><strong>What to build in 60 days</strong></p><p>Week 1–2: Load a dataset into a Lakehouse ↳ Upload a CSV to OneLake ↳ Explore it with a Notebook (no coding needed for basics) ↳ Create a SQL endpoint on your Lakehouse</p><p>Week 3–4: Build a simple pipeline ↳ Use Data Factory to move data between sources ↳ Connect the output to your Lakehouse</p><p>Week 5–6: Connect Power BI to your Fabric data ↳ Create a semantic model on top of your Lakehouse ↳ Build a report in Power BI Desktop ↳ Publish to your Fabric workspace</p><p>Week 7–8: Explore one more workload ↳ Try a Dataflow Gen2 for data transformation ↳ Or a KQL query on Real-Time data</p><blockquote>The goal is not to master everything. The goal is to understand how the pieces connect. After 60 days, you should be able to explain what OneLake is, what a Lakehouse does, and how your Power BI reports fit into the broader platform.</blockquote><p>That understanding is what separates a Power BI developer from a Fabric-aware data professional.</p><p>---</p><h4>Common Questions Answered Directly</h4><blockquote><strong>&quot;Should I migrate my existing Power BI reports to Fabric?&quot;</strong><br>Not necessarily, and not urgently. Your existing Power BI workspace works fine. Fabric workspaces offer additional capabilities but existing Power BI content does not need to be migrated. When you start a new project, consider using a Fabric workspace from the beginning.</blockquote><blockquote><strong>*Is Fabric only for large companies?&quot;</strong><br>No. Microsoft offers Fabric through Power BI Premium Per User ($20/user/month) which makes it accessible to small teams. The full F-SKU capacities are enterprise-scale, but the entry point is affordable.</blockquote><blockquote><strong>&quot;Do I need to know Python or Spark to use Fabric?&quot;</strong><br>Not to use the reporting and analytics features. Power BI inside Fabric works exactly as it always has. Python and Spark become relevant if you use the Data Engineering or Data Science workloads — which are genuinely powerful but optional depending on your role.</blockquote><blockquote><strong>&quot;Will Power BI Desktop still exist?&quot;</strong><br>Yes. Power BI Desktop is not going away. Development continues. The roadmap shows increasing integration with Fabric, but the desktop experience remains the primary tool for report development.</blockquote><h4>Where to Start Today</h4><blockquote><strong>If you are new to Microsoft analytics:</strong><br>Start with Power BI Desktop. It is free, downloadable, and you can build your first report from an Excel file in under an hour. Get comfortable with data modeling, Power Query, and basic DAX before thinking about Fabric.</blockquote><blockquote><strong>If you already know Power BI:</strong><br>Activate the Fabric trial. Spend one afternoon creating a Lakehouse, loading some data, and connecting Power BI to it. The goal is not to learn everything — it&#39;s to understand the architecture. Once the pieces click conceptually, learning each workload becomes much easier.</blockquote><blockquote><strong>If your company is asking about Fabric:</strong><br>The most useful thing you can do is understand OneLake and how it changes the data architecture story. A company running five separate Azure services (Data Lake, Synapse, ADF, Databricks, Power BI) gets real value from Fabric consolidation. A company running only Power BI already gets incremental value but no urgent migration pressure.</blockquote><p>Vembry Fiska<br><em>Senior Power BI Developer &amp; Data Engineer</em></p><blockquote>SEO keywords:Power BI vs Microsoft Fabric, what is Microsoft Fabric, Power BI beginner guide, Microsoft Fabric tutorial, learn Power BI or Fabric</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e8e29b408b2a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ERP Data Is Different: What Data Engineers Don’t Know About NetSuite’s Data Model]]></title>
            <link>https://blog.dataengineerthings.org/erp-data-is-different-what-data-engineers-dont-know-about-netsuite-s-data-model-9efd70d8842c?source=rss-4a74082fcf9d------2</link>
            <guid isPermaLink="false">https://medium.com/p/9efd70d8842c</guid>
            <category><![CDATA[erp-software]]></category>
            <category><![CDATA[data-engineer]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[netsuite]]></category>
            <dc:creator><![CDATA[Vembry Fiska]]></dc:creator>
            <pubDate>Tue, 23 Jun 2026 12:31:00 GMT</pubDate>
            <atom:updated>2026-06-24T12:57:20.549Z</atom:updated>
            <content:encoded><![CDATA[<h4><em>Before you write your first SuiteQL query, you need to unlearn how you think about databases</em></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TuoQOn5ILLxxbKIIbdc_vw.png" /><figcaption>Netsuite Data Model</figcaption></figure><h3>The Moment I Realized I Was Wrong About Everything</h3><p>Three weeks into my first NetSuite project, I was staring at a query that should have been simple.</p><p>I wanted sales orders with their line items. In any normal relational database, this is a ten-minute task. You have an orders table. You have an order_lines table. You join them. Done.</p><p>I ran my query against NetSuite. Got 0 rows.</p><p>Not an error. Zero rows.</p><p>I checked the data in the UI. The orders were there. The line items were there. My join logic looked correct. The column names matched the documentation.</p><p>Zero rows.</p><p>I spent four hours on that query before a colleague pointed out what I had missed: in NetSuite, you cannot use INNER JOIN in SuiteQL. You can only use LEFT JOIN. Always. Without exception. It is not documented prominently. It is not an error message. It just returns nothing and lets you believe the data doesn&#39;t exist.</p><p>That moment crystallized something I should have understood from the start: <strong>NetSuite’s data model is not a relational database wearing an ERP costume. It’s an ERP system with a query layer bolted on top. The data model reflects the ERP’s internal logic, not the logic of a data engineer.</strong></p><p>Everything that follows from that realization took me months to learn piece by piece. This article is what I wish someone had handed me on day one.</p><h3>Part 1: The Mental Model Shift</h3><h3>What You’re Used To (OLTP Thinking)</h3><p>Most data engineers come from a background where data is stored in normalized, purpose-built tables. Orders in an orders table. Customers in a customers table. Products in a products table. The schema reflects the data structure directly. If you want to understand the schema, you read the table names.</p><pre>Standard OLTP schema:<br>orders          → one row per order<br>order_lines     → one row per order line item<br>customers       → one row per customer<br>products        → one row per product<br>invoices        → one row per invoice<br>payments        → one row per payment</pre><p>Each table has one purpose. The name tells you exactly what’s in it.</p><h3>What NetSuite Actually Is (ERP Thinking)</h3><p>NetSuite was not designed by database architects. It was designed by accountants and business process engineers. The data model reflects <strong>business processes and accounting logic</strong>, not storage efficiency.</p><p>The most important thing to understand:</p><p><strong>In NetSuite, almost everything is a record. Records have types. Types share tables.</strong></p><pre>NetSuite&#39;s actual model:<br>Transaction table → sales orders, invoices, bills, payments,<br>                    purchase orders, returns, transfers,<br>                    deposits, journal entries... (40+ types)</pre><pre>Entity table      → customers, vendors, employees, partners,<br>                    contacts... (all in one table)</pre><pre>Item table        → inventory items, service items, kit items,<br>                    non-inventory items, assembly items...</pre><pre>TransactionLine   → line items for ALL transaction types</pre><p>This is not a design flaw. It is a deliberate architectural decision rooted in how accounting systems work. A journal entry, a customer payment, and a vendor bill all participate in the same general ledger. Storing them in separate tables would complicate the accounting logic. Storing them together — with a type column to distinguish them — makes ledger reconciliation simpler.</p><p>For a data engineer, this means: <strong>the first WHERE clause you write on any NetSuite table is almost always a type filter.</strong></p><pre>-- Without type filter: you get everything mixed together<br>SELECT * FROM transaction LIMIT 10<br>-- Returns: sales orders, invoices, payments, transfers, journal entries...<br>-- With type filter: you get what you actually want<br>SELECT * FROM transaction<br>WHERE type = &#39;SalesOrd&#39;<br>LIMIT 10</pre><p>This is table stakes. If you forget the type filter — and you will forget it at least once — your aggregations will be wrong in ways that are not immediately obvious.</p><h3>Part 2: The Transaction Table in Depth</h3><h3>The 40+ Types You Need to Know</h3><p>The transaction table contains every financial event in the system. Here are the type codes you will encounter most frequently:</p><pre>Sales-side:<br>  SalesOrd   → Sales Order (the order itself, not yet invoiced)<br>  CustInvc   → Customer Invoice (revenue recognized)<br>  CustCred   → Customer Credit Memo (reversal or return)<br>  CustPymt   → Customer Payment (cash received)<br>  CashSale   → Cash Sale (order + invoice in one step)<br>  RtnAuth    → Return Authorization<br>Purchase-side:<br>  PurchOrd   → Purchase Order<br>  VendBill   → Vendor Bill (accounts payable)<br>  VendCred   → Vendor Credit<br>  VendPymt   → Vendor Payment<br>  ItemRcpt   → Item Receipt (goods received against a PO)<br>Inventory:<br>  InvTrnsfr  → Inventory Transfer (between locations)<br>  InvAdjst   → Inventory Adjustment<br>  AssemblyBuild → Assembly Build (manufactured items)<br>Accounting:<br>  Journal    → Journal Entry<br>  Deposit    → Bank Deposit<br>  Check      → Check (payment by check)<br>  CardChrg   → Credit Card Charge</pre><p><strong>Why this matters for your pipeline:</strong></p><p>A SUM of amount across all transaction types produces a meaningless number. Revenue (CustInvc) and accounts payable (VendBill) are both stored as positive amount values. If you forget the type filter, your total looks plausible but is wrong.</p><pre>-- ❌ Wrong: mixes revenue and expenses<br>SELECT SUM(amount) FROM transaction<br>WHERE trandate &gt;= TO_DATE(&#39;2025-01-01&#39;, &#39;YYYY-MM-DD&#39;)<br>-- ✅ Right: revenue only<br>SELECT SUM(amount) FROM transaction<br>WHERE type = &#39;CustInvc&#39;<br>  AND trandate &gt;= TO_DATE(&#39;2025-01-01&#39;, &#39;YYYY-MM-DD&#39;)</pre><h3>The Parent-Child Relationship Between Transaction Types</h3><p>NetSuite’s transaction types have relationships to each other that are not always surfaced as foreign keys. A Sales Order generates a Customer Invoice. That invoice generates a Customer Payment. The chain looks like this:</p><pre>SalesOrd (SO-001)<br>    ↓ fulfilled → creates<br>ItemRcpt or ItemShip<br>    ↓ billed → creates<br>CustInvc (INV-001)<br>    ↓ paid → creates<br>CustPymt (PAY-001)</pre><p>These relationships are stored in the createdfrom column on the transaction table:</p><pre>SELECT<br>    t.id,<br>    t.tranid,<br>    t.type,<br>    t.createdfrom,          -- ID of the parent transaction<br>    t.amount,<br>    t.status<br>FROM transaction t<br>WHERE t.type = &#39;CustInvc&#39;<br>  AND t.createdfrom IS NOT NULL<br>ORDER BY t.trandate DESC<br>LIMIT 1000</pre><p>This is critical for revenue recognition pipelines. If you extract invoices without understanding that createdfrom points to the originating sales order, you cannot trace revenue back to the original deal.</p><h3>The Status Field Is Not Simple</h3><p>Every data engineer expects a status column to be a simple string. In NetSuite, status is a compound field that means different things for different transaction types:</p><pre>For SalesOrd:<br>  &#39;SalesOrd:A&#39; → Pending Approval<br>  &#39;SalesOrd:B&#39; → Pending Fulfillment<br>  &#39;SalesOrd:D&#39; → Cancelled<br>  &#39;SalesOrd:E&#39; → Partially Fulfilled<br>  &#39;SalesOrd:F&#39; → Fulfilled (closed)<br>For CustInvc:<br>  &#39;CustInvc:A&#39; → Open<br>  &#39;CustInvc:B&#39; → Paid in Full<br>  &#39;CustInvc:C&#39; → Voided<br>For VendBill:<br>  &#39;VendBill:A&#39; → Open<br>  &#39;VendBill:B&#39; → Paid in Full<br>  &#39;VendBill:C&#39; → Voided</pre><p>The pattern is always {type}:{letter}. If you filter by status = &#39;A&#39; across transaction types, you get nothing — the actual stored value includes the type prefix.</p><pre>-- ❌ Wrong: will return 0 rows<br>SELECT * FROM transaction<br>WHERE type = &#39;SalesOrd&#39;<br>  AND status = &#39;Pending Fulfillment&#39;<br>-- ✅ Right: use the internal status code<br>SELECT * FROM transaction<br>WHERE type = &#39;SalesOrd&#39;<br>  AND status = &#39;SalesOrd:B&#39;   -- Pending Fulfillment</pre><h3>Part 3: The Entity Table — One Table for Every Person</h3><h3>Customers, Vendors, and Employees Are All “Entities”</h3><p>In standard databases, customers and vendors are separate tables. In NetSuite, they share the entity table with a type column distinguishing them.</p><pre>-- The entity table contains all of these:<br>SELECT DISTINCT type FROM entity<br>-- Returns: Customer, Vendor, Employee, Contact, Partner, OtherName...</pre><pre>-- Always filter by type<br>SELECT<br>    e.id,<br>    e.entityid,        -- The human-readable code (CUST-001, etc.)<br>    e.companyname,<br>    e.email,<br>    e.phone,<br>    e.isinactive<br>FROM entity e<br>WHERE e.type = &#39;Customer&#39;<br>  AND e.isinactive = &#39;F&#39;   -- &#39;F&#39; = false in NetSuite boolean columns<br>ORDER BY e.id</pre><h3>The Boolean Problem</h3><p>NetSuite does not use standard SQL boolean types for many fields. Instead, it uses single-character strings:</p><pre>&#39;T&#39; = true<br>&#39;F&#39; = false</pre><p>This trips up almost every data engineer on their first extraction:</p><pre>-- ❌ Wrong: returns nothing<br>SELECT * FROM entity WHERE isinactive = FALSE<br>-- ❌ Also wrong: returns nothing<br>SELECT * FROM entity WHERE isinactive = false<br>-- ✅ Right: use string comparison<br>SELECT * FROM entity WHERE isinactive = &#39;F&#39;</pre><p>This pattern appears across many tables: isinactive, isperson, isemailhtml, taxable, autoname. Always check whether a boolean column uses T/F strings before writing your filter.</p><h3>Part 4: Custom Records and Custom Fields — The Wild Card</h3><h3>Everything Can Be Extended</h3><p>This is the part that no documentation prepares you for.</p><p>In NetSuite, every implementation is different. A company can create:</p><ul><li><strong>Custom fields</strong> on any standard record (additional columns on transaction, entity, item, etc.)</li><li><strong>Custom records</strong> (entirely new tables specific to that company)</li><li><strong>Custom sublists</strong> (additional line item types on transactions)</li></ul><p>This means the schema you query is different for every NetSuite account you connect to. What works for one client will not work for another.</p><h3>Custom Fields: The Naming Convention</h3><p>Custom fields have internal IDs in the format custbody_* (transaction body fields), custcol_* (line item fields), custentity_* (entity fields), or custitem_* (item fields):</p><pre>-- Standard fields look like this:<br>SELECT t.id, t.tranid, t.amount FROM transaction t<br>-- Custom fields look like this:<br>SELECT<br>    t.id,<br>    t.tranid,<br>    t.custbody_po_reference,      -- Custom PO reference field<br>    t.custbody_approval_status,   -- Custom approval workflow field<br>    t.custbody_region_override    -- Custom regional override field<br>FROM transaction t<br>WHERE t.type = &#39;SalesOrd&#39;</pre><p>The problem: you cannot know what custom fields exist without either:</p><ol><li>Looking at the NetSuite UI (Setup → Customization → Transaction Body Fields)</li><li>Querying the metadata tables</li></ol><pre>-- Discover what custom fields exist on the transaction table<br>SELECT<br>    f.fieldid,          -- The column name you use in queries<br>    f.label,            -- The human-readable name<br>    f.type,             -- Field type: Text, Date, List, etc.<br>    f.ismandatory<br>FROM customfield f<br>WHERE f.rectype = &#39;transaction&#39;<br>ORDER BY f.fieldid</pre><p><strong>The practical implication for your pipeline:</strong></p><p>Before building an ETL for a new NetSuite account, always run the custom field discovery query first. You will inevitably find fields that are business-critical — approval statuses, regional codes, custom categories — that do not appear in any standard documentation because they were created specifically for that client.</p><h3>Custom Records: New Tables That Don’t Exist in the Docs</h3><p>Some implementations create entirely new record types. A logistics company might have a customrecord_route table. A manufacturing client might have customrecord_production_run. These are real tables you can query in SuiteQL, but they appear nowhere in NetSuite&#39;s standard schema documentation.</p><pre>-- Discover custom record types in this account<br>SELECT<br>    rt.scriptid,        -- The table name in SuiteQL<br>    rt.name,            -- Human-readable name<br>    rt.description<br>FROM customrecordtype rt<br>ORDER BY rt.name</pre><pre>-- Query a custom record (once you know it exists)<br>SELECT *<br>FROM customrecord_route     -- This only exists in specific accounts<br>LIMIT 10</pre><p>This is one of the biggest stumbling blocks for data engineers new to ERP systems: <strong>the schema is not fully knowable without inspecting the specific instance you’re connected to.</strong> Every NetSuite deployment is a custom schema on top of a standard schema.</p><h3>Part 5: Multi-Subsidiary and Multi-Currency — The Accounting Layer</h3><h3>Subsidiaries Are Not Just Departments</h3><p>If you’re working with a multi-entity company, NetSuite’s subsidiary model adds significant complexity. Subsidiaries are not simple organizational tags — they each have their own:</p><ul><li>Chart of accounts</li><li>Home currency</li><li>Tax rules</li><li>Accounting periods</li><li>Intercompany elimination rules</li></ul><p>The subsidiary column on the transaction table is a foreign key to the subsidiary table:</p><pre>SELECT<br>    t.id,<br>    t.tranid,<br>    t.type,<br>    t.amount,<br>    t.currency,<br>    s.name              AS subsidiary_name,<br>    s.currency          AS subsidiary_currency<br>FROM transaction t<br>LEFT JOIN subsidiary s ON t.subsidiary = s.id<br>WHERE t.type = &#39;CustInvc&#39;<br>ORDER BY t.trandate DESC</pre><p><strong>Why this matters for your data model:</strong></p><p>If you SUM revenue across subsidiaries without currency conversion, your total is meaningless — you’re adding USD to IDR to EUR. NetSuite stores both the transaction currency amount and the home-currency-converted amount:</p><pre>SELECT<br>    t.id,<br>    t.amount,                    -- Amount in transaction currency<br>    t.fxamount,                  -- Amount in transaction currency (same as above, alias)<br>    t.baseamount,                -- Amount converted to subsidiary home currency<br>    t.currency,                  -- Transaction currency code<br>    s.currency AS home_currency, -- Subsidiary&#39;s home currency<br>    t.exchangerate               -- Rate at time of transaction<br>FROM transaction t<br>LEFT JOIN subsidiary s ON t.subsidiary = s.id<br>WHERE t.type = &#39;CustInvc&#39;</pre><p>Always clarify with the business: when they say “total revenue,” do they mean:</p><ul><li>Revenue in each transaction’s own currency (use amount)</li><li>Revenue converted to each subsidiary’s home currency (use baseamount)</li><li>Revenue converted to a single consolidated currency (requires your own conversion logic)</li></ul><p>These three numbers are different. Picking the wrong one produces a revenue figure that cannot be reconciled with the financial statements.</p><h3>Accounting Periods Are Not Calendar Months</h3><p>NetSuite uses accounting periods for financial reporting. An accounting period might not align with a calendar month. Some companies use 4–4–5 fiscal calendars. Some close their books on the 25th of each month. Some have a 53-week fiscal year.</p><pre>-- Accounting periods table<br>SELECT<br>    ap.id,<br>    ap.periodname,<br>    ap.startdate,<br>    ap.enddate,<br>    ap.closed,<br>    ap.fiscalyear<br>FROM accountingperiod ap<br>ORDER BY ap.startdate DESC</pre><p>If your dashboard is filtering by trandate (the transaction date) but finance is reporting by accounting period, you will get different numbers than the financial statements — even for the same set of transactions. This is not a bug. It is a fundamental difference between the transaction date and the recognition date.</p><h3>Part 6: The LEFT JOIN Rule and Other SuiteQL Constraints</h3><h3>Why INNER JOIN Returns Nothing</h3><p>This is the mistake I described at the beginning. In SuiteQL, INNER JOIN is not supported. The query engine accepts the syntax but returns zero rows — silently — rather than throwing an error.</p><pre>-- ❌ Returns 0 rows — no error, just nothing<br>SELECT t.id, e.companyname<br>FROM transaction t<br>INNER JOIN entity e ON t.entity = e.id<br>WHERE t.type = &#39;SalesOrd&#39;<br>-- ✅ Returns correct results<br>SELECT t.id, e.companyname<br>FROM transaction t<br>LEFT JOIN entity e ON t.entity = e.id<br>WHERE t.type = &#39;SalesOrd&#39;</pre><p>This applies universally: every join in SuiteQL must be a LEFT JOIN. Build this into your query templates from day one and never think about it again.</p><h3>IS NOT NULL Must Come After LEFT JOIN Conditions</h3><p>If you want to effectively recreate an INNER JOIN behavior (exclude rows where the join produced no match), place the IS NOT NULL filter after the LEFT JOIN:</p><pre>-- ✅ Correct: LEFT JOIN first, then filter nulls<br>SELECT t.id, e.companyname<br>FROM transaction t<br>LEFT JOIN entity e ON t.entity = e.id<br>WHERE t.type = &#39;SalesOrd&#39;<br>  AND e.id IS NOT NULL   -- Exclude transactions with no matching entity</pre><h3>OFFSET Pagination Breaks at Scale</h3><p>SuiteQL uses LIMIT and OFFSET for pagination. This works reliably up to approximately OFFSET 50,000. At higher offsets, the API returns a 400 error:</p><pre>{ &quot;error&quot;: { &quot;code&quot;: &quot;USER_ERROR&quot;, &quot;message&quot;: &quot;Invalid search result index&quot; } }</pre><p>This is not a network timeout. It is a hard limit on how deep NetSuite allows offset-based pagination. The solution is date chunking: break your extraction into date ranges small enough that no single range produces more than ~40,000 rows, so OFFSET never gets that high.</p><pre>-- Safe: date-chunked query, OFFSET stays low<br>SELECT id, trandate, amount<br>FROM transaction<br>WHERE type = &#39;SalesOrd&#39;<br>  AND trandate &gt;= TO_DATE(&#39;2025-01-01&#39;, &#39;YYYY-MM-DD&#39;)<br>  AND trandate &lt;  TO_DATE(&#39;2025-02-01&#39;, &#39;YYYY-MM-DD&#39;)<br>ORDER BY id ASC<br>OFFSET 0 LIMIT 1000</pre><h3>TO_DATE for Date Filters, Always</h3><p>SuiteQL requires explicit date casting. String-to-date implicit coercion is unreliable:</p><pre>-- ❌ Unreliable: implicit coercion<br>WHERE trandate &gt;= &#39;2025-01-01&#39;<br>-- ✅ Reliable: explicit cast<br>WHERE trandate &gt;= TO_DATE(&#39;2025-01-01&#39;, &#39;YYYY-MM-DD&#39;)</pre><p>For datetime fields (like lastmodifieddate), use the full format:</p><pre>WHERE lastmodifieddate &gt;= TO_DATE(&#39;2025-01-01 00:00:00&#39;, &#39;YYYY-MM-DD HH24:MI:SS&#39;)</pre><h3>Part 7: What This Means for Your Data Pipeline Design</h3><h3>Schema Discovery Is Part of the Project</h3><p>With a standard database, schema discovery is a one-time setup task. With NetSuite, it is an ongoing part of every project:</p><pre># Run these queries at the start of every new NetSuite engagement<br>DISCOVERY_QUERIES = {<br>&quot;transaction_types&quot;: &quot;&quot;&quot;<br>        SELECT DISTINCT type, COUNT(*) as record_count<br>        FROM transaction<br>        GROUP BY type<br>        ORDER BY record_count DESC<br>    &quot;&quot;&quot;,<br>    &quot;custom_fields_on_transaction&quot;: &quot;&quot;&quot;<br>        SELECT fieldid, label, type, ismandatory<br>        FROM customfield<br>        WHERE rectype = &#39;transaction&#39;<br>        ORDER BY fieldid<br>    &quot;&quot;&quot;,<br>    &quot;custom_record_types&quot;: &quot;&quot;&quot;<br>        SELECT scriptid, name, description<br>        FROM customrecordtype<br>        ORDER BY name<br>    &quot;&quot;&quot;,<br>    &quot;subsidiaries&quot;: &quot;&quot;&quot;<br>        SELECT id, name, currency, isinactive<br>        FROM subsidiary<br>        WHERE isinactive = &#39;F&#39;<br>        ORDER BY name<br>    &quot;&quot;&quot;,<br>    &quot;accounting_periods_current_year&quot;: &quot;&quot;&quot;<br>        SELECT periodname, startdate, enddate, closed<br>        FROM accountingperiod<br>        WHERE startdate &gt;= TO_DATE(&#39;2025-01-01&#39;, &#39;YYYY-MM-DD&#39;)<br>        ORDER BY startdate<br>    &quot;&quot;&quot;<br>}</pre><p>Run every one of these before writing a single extraction query. The results will shape your entire pipeline design.</p><h3>Build Type-Awareness Into Your Tables</h3><p>Your PostgreSQL target schema should reflect NetSuite’s type structure, not flatten it away:</p><pre>-- ❌ Don&#39;t do this: one table for all transactions<br>CREATE TABLE netsuite.transactions (<br>    id          INTEGER PRIMARY KEY,<br>    type        TEXT,<br>    trandate    DATE,<br>    amount      NUMERIC,<br>    ...<br>);<br>-- ✅ Do this: separate tables per meaningful type<br>CREATE TABLE netsuite.sales_orders (<br>    id              INTEGER PRIMARY KEY,<br>    trandate        DATE,<br>    document_number TEXT,<br>    customer_id     INTEGER,<br>    status          TEXT,<br>    amount          NUMERIC,<br>    subsidiary_id   INTEGER,<br>    last_modified   TIMESTAMP,<br>    extracted_at    TIMESTAMP DEFAULT NOW()<br>);<br>CREATE TABLE netsuite.invoices (<br>    id              INTEGER PRIMARY KEY,<br>    trandate        DATE,<br>    document_number TEXT,<br>    customer_id     INTEGER,<br>    sales_order_id  INTEGER,   -- createdfrom reference<br>    status          TEXT,<br>    amount          NUMERIC,<br>    baseamount      NUMERIC,   -- home currency amount<br>    currency        TEXT,<br>    subsidiary_id   INTEGER,<br>    last_modified   TIMESTAMP,<br>    extracted_at    TIMESTAMP DEFAULT NOW()<br>);</pre><p>Separate tables per transaction type make your downstream SQL simpler, your indexes more effective, and your data model self-documenting. The type column should never appear as a filter in your analytical queries — that logic should have been handled in the extraction layer.</p><h3>Document the Custom Fields You Depend On</h3><p>Custom fields are invisible to anyone who hasn’t looked. When you build a pipeline that depends on custbody_approval_status, document it explicitly:</p><pre># In your ETL configuration, make custom field dependencies explicit<br>SALES_ORDER_FIELDS = {<br>    # Standard fields<br>    &quot;standard&quot;: [<br>        &quot;id&quot;, &quot;tranid&quot;, &quot;trandate&quot;, &quot;type&quot;, &quot;entity&quot;,<br>        &quot;amount&quot;, &quot;status&quot;, &quot;subsidiary&quot;, &quot;lastmodifieddate&quot;<br>    ],<br>    # Custom fields — document purpose and who owns them<br>    &quot;custom&quot;: {<br>        &quot;custbody_po_reference&quot;:   &quot;Customer PO number — required for invoicing&quot;,<br>        &quot;custbody_region_override&quot;: &quot;Manual region assignment — overrides entity default&quot;,<br>        &quot;custbody_approval_user&quot;:  &quot;User who approved the order — workflow field&quot;,<br>    }<br>}</pre><p>When custom fields get renamed or deleted by a NetSuite administrator, your pipeline breaks. Explicit documentation makes the break visible and traceable.</p><h3>The Operational Lesson: ERP Data Requires ERP Thinking</h3><p>Here is what took me the longest to internalize.</p><p>When I approached NetSuite as a database, I tried to map its structure onto what I already knew. I expected transaction types in separate tables. I expected booleans to be booleans. I expected joins to work the way joins work everywhere else.</p><p>Every one of those expectations was wrong, and each wrongness cost me time.</p><p>The shift that changed everything was treating NetSuite’s data model as a product of its business logic, not its technical architecture. The transaction table has 40 types in it because the general ledger needs all financial events in one place to balance. The entity table merges customers and vendors because the payment logic is symmetric. The LEFT JOIN constraint exists because SuiteQL’s query planner was built for a different purpose than ad-hoc SQL.</p><p>Once I stopped trying to normalize NetSuite into what I thought it should be, and started asking “why does it work this way?” for each quirk — the model started making sense. Not database sense. ERP sense.</p><p>That shift — from database thinking to ERP thinking — is the real prerequisite for extracting NetSuite data reliably. The SQL comes after.</p><h3>The Second Operational Lesson: Document What You Discovered, Not Just What You Built</h3><p>I learned this one the hard way too — on a different project, six months after the first one.</p><p>I had built a solid NetSuite pipeline. It ran nightly. It was reliable. I handed it over to the client’s internal team and moved to the next project.</p><p>Eight months later, a new developer at that client company called me. The pipeline had broken. Not because the code failed — because a NetSuite administrator had renamed a custom field. custbody_approval_status became custbody_so_approval_status. One rename. Pipeline broke silently. Eight months of nobody noticing because the field had been NULL for most records during that period, and nobody caught the gap until a finance reconciliation surfaced it.</p><p>When the new developer looked at the code, there was no record of why that custom field existed, who owned it, or what it was supposed to contain. The developer had to spend three days reverse-engineering a pipeline that should have had a one-paragraph explanation.</p><p><strong>The lesson: with ERP data, what you discovered during your schema investigation is as important as the code you wrote.</strong></p><p>A NetSuite data pipeline has two kinds of knowledge:</p><pre>Explicit knowledge (lives in the code):<br>  - Table names, column names, filter logic<br>  - Pagination strategy, retry logic<br>  - Load pattern (upsert vs. append)<br>  → Anyone can read the code and see this<br>Tacit knowledge (lives only in your head):<br>  - Why you chose lastmodifieddate instead of trandate<br>  - Which custom fields are business-critical vs. cosmetic<br>  - Which transaction types were excluded and why<br>  - What the status codes actually mean for each type<br>  - Which subsidiary uses non-standard accounting periods<br>  → Invisible to anyone who wasn&#39;t there during discovery</pre><p>The tacit knowledge is what breaks pipelines eight months later.</p><p>Here is what I now produce at the end of every NetSuite engagement, alongside the code:</p><pre>## Pipeline Discovery Notes — [Client / Project Name]<br>### Transaction Types Included<br>- CustInvc (customer invoices) - primary revenue source<br>- SalesOrd (sales orders) - pre-revenue, excluded from finance reports<br>- CustPymt (payments) - used for AR aging only, not revenue<br>### Transaction Types Excluded and Why<br>- Journal (journal entries) - excluded per client request; handled by finance team separately<br>- Deposit - not relevant to this dashboard&#39;s scope<br>### Custom Fields in Use<br>| Field ID                    | Label               | Purpose                          | Owner         |<br>|-----------------------------|---------------------|----------------------------------|---------------|<br>| custbody_approval_status    | SO Approval Status  | Approval workflow stage          | Finance team  |<br>| custbody_region_override    | Region Override     | Manual region for territory reports | Sales ops  |<br>### Status Codes Used<br>- SalesOrd:B = Pending Fulfillment (active orders)<br>- CustInvc:A = Open invoice (unpaid AR)<br>- CustInvc:B = Paid in full<br>### Currency Notes<br>- Subsidiary 1 (HQ): USD - use amount directly<br>- Subsidiary 2 (APAC): IDR - use baseamount for USD comparison<br>### Known Issues / Quirks<br>- Subsidiary 3 closes books on the 25th, not month-end<br>- Item type &#39;OthCharge&#39; included in revenue - confirm with finance</pre><p>This document takes two hours to write. It has saved far more than two hours every time a pipeline needed maintenance.</p><p>The best data pipelines are not just reliable — they are understandable to the next person who has to touch them. With ERP data, that second requirement is significantly harder to meet than the first.</p><h3>Discussion: What’s Your ERP Data War Story?</h3><p>Every data engineer who has worked with ERP systems has at least one story.</p><p>The query that returned the right number — until the client pointed out it was double-counting intercompany transactions. The pipeline that worked for 11 months before a fiscal year close exposed a bug in the period filter logic. The custom field that was renamed without notice and broke three dashboards at once.</p><p>These stories don’t appear in documentation. They’re passed between engineers on Slack channels and in late-night Zoom calls. They’re the knowledge gap that no tutorial covers, because nobody publishes “the thing that went wrong and took me three days to debug.”</p><p>A few questions worth thinking about:</p><p><strong>For those who have worked with NetSuite or similar ERP systems (SAP, Oracle, Dynamics):</strong></p><ul><li>What was the first thing that surprised you about the data model?</li><li>How do you handle custom field dependency in your pipelines?</li><li>Do you build separate tables per transaction type, or one unified transaction table with type as a column?</li></ul><p><strong>For those who haven’t worked with ERP data yet:</strong></p><ul><li>What assumption from your current stack would you expect to break first?</li><li>How would you approach schema discovery on a system where the schema is partially client-defined?</li></ul><p><strong>For everyone building data pipelines:</strong></p><ul><li>Do you document your discovery process, or just the code?</li><li>What’s your approach when a critical field gets renamed in a source system?</li></ul><p>I’m genuinely curious about the patterns other engineers have developed — especially for the custom field problem, where I don’t think there’s a single right answer yet.</p><p>Leave a comment. I read and respond to all of them.</p><p><strong>Vembry Fiska</strong> Senior Power BI Developer &amp; Data Engineer</p><p><em>I design and build data pipelines from ERP systems including NetSuite for mid-market companies across Southeast Asia. Most of what’s in this article I learned by getting it wrong first.</em></p><p><strong>Tags:</strong> netsuite data-engineering etl sql erp</p><p><strong>SEO keywords:</strong> NetSuite data model, SuiteQL data engineer, NetSuite transaction table, NetSuite ETL design, ERP data engineering, NetSuite custom fields</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9efd70d8842c" width="1" height="1" alt=""><hr><p><a href="https://blog.dataengineerthings.org/erp-data-is-different-what-data-engineers-dont-know-about-netsuite-s-data-model-9efd70d8842c">ERP Data Is Different: What Data Engineers Don’t Know About NetSuite’s Data Model</a> was originally published in <a href="https://blog.dataengineerthings.org/">Data Engineer Things</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[5 Power BI DAX Functions You Didn’t Know Existed]]></title>
            <link>https://pub.towardsai.net/5-power-bi-dax-functions-you-didnt-know-existed-8a54404993aa?source=rss-4a74082fcf9d------2</link>
            <guid isPermaLink="false">https://medium.com/p/8a54404993aa</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <dc:creator><![CDATA[Vembry Fiska]]></dc:creator>
            <pubDate>Sun, 21 Jun 2026 09:31:34 GMT</pubDate>
            <atom:updated>2026-07-02T11:26:06.804Z</atom:updated>
            <content:encoded><![CDATA[<h4><em>The functions your colleagues aren’t using — and why they should be</em></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UAj1e-cL5TPuDzp4xQYKyw.png" /><figcaption>Power BI DAX</figcaption></figure><h3>Introduction: The DAX Functions Nobody Teaches</h3><p>Every Power BI tutorial covers the same functions.</p><p>CALCULATE. SUM. DIVIDE. TOTALYTD. SAMEPERIODLASTYEAR. IF.</p><p>Valuable functions. Essential, even. But if every developer on your team knows the same six functions, everyone builds the same measures — and more importantly, everyone hits the same walls when those six functions aren’t enough.</p><p>I’ve reviewed code from many Power BI developers across different industries. A pattern emerges quickly: most teams have a small repertoire of DAX they’re comfortable with, and when a business question doesn’t fit that repertoire, they either build an ugly workaround or say “Power BI can’t do that.”</p><p>Power BI can do that. The function just has a name you haven’t heard yet.</p><p>This article covers five DAX functions that are genuinely underused — not because they’re obscure, but because they’re rarely taught. Each one solves a category of problem that developers typically handle with far more complicated code.</p><p>By the end, you’ll have five new tools that make your models cleaner, your measures faster, and your answers more accurate.</p><h3>Operational Lesson Before We Start: DAX Knowledge Compounds</h3><p>Before the functions — the lesson behind them.</p><p>I used to approach DAX the way most developers do: learn what I need for the current project, copy patterns from Stack Overflow when stuck, move on.</p><p>It works. Until it doesn’t.</p><p>The problem with reactive DAX learning is that you never discover what you don’t know you’re missing. I spent months writing multi-step workarounds for ranking problems before someone showed me RANKX. I built complex IF chains for categorization before I found SWITCH. I manually filtered tables with FILTER() before I understood what KEEPFILTERS could do.</p><p>Each of those discoveries saved me hours — not just once, but on every project afterward.</p><p><strong>The operational lesson</strong>: treat DAX like a toolkit, not a script library. Dedicate time each month to learning two or three functions you haven’t used before, even without an immediate use case. The return on that investment is asymmetric — an hour of exploration saves ten hours of workarounds across future projects.</p><p>The five functions below are the result of that kind of deliberate exploration. Each one replaced something more complicated in a real dashboard I’ve built.</p><h3>Function 1: SELECTEDVALUE — The Clean Filter Reader</h3><h3>What It Does</h3><p>SELECTEDVALUE returns the value of a column when there is exactly one value in the current filter context. If multiple values are selected (or none), it returns a fallback value you define.</p><pre>SELECTEDVALUE(column, [alternateResult])</pre><h3>Why It Exists</h3><p>Reading what a user has selected in a slicer is a common need. Most developers write this:</p><pre>-- ❌ Common workaround (verbose and fragile)<br>Selected Region =<br>IF(<br>    HASONEVALUE(DimGeography[Region]),<br>    VALUES(DimGeography[Region]),<br>    &quot;All Regions&quot;<br>)</pre><p>This works, but it’s three functions doing the job of one. SELECTEDVALUE was introduced specifically to replace this pattern:</p><pre>-- ✅ Clean equivalent<br>Selected Region = SELECTEDVALUE(DimGeography[Region], &quot;All Regions&quot;)</pre><h3>Real Business Use Case: Dynamic Titles</h3><p>The most powerful use of SELECTEDVALUE is building dynamic titles that respond to slicer selections.</p><pre>-- Dynamic dashboard title that updates with every slicer<br>Dashboard Title =<br>&quot;Sales Performance — &quot; &amp;<br>SELECTEDVALUE(DimDate[Year], &quot;All Years&quot;) &amp; &quot; | &quot; &amp;<br>SELECTEDVALUE(DimGeography[Region], &quot;All Regions&quot;) &amp; &quot; | &quot; &amp;<br>SELECTEDVALUE(DimProduct[Category], &quot;All Categories&quot;)</pre><p>When a user selects “2025”, “APAC”, and “Electronics” in slicers, the card shows: <strong>“Sales Performance — 2025 | APAC | Electronics”</strong></p><p>When nothing is selected: <strong>“Sales Performance — All Years | All Regions | All Categories”</strong></p><pre>-- Dynamic benchmark target that changes by selected KPI<br>Target Value =<br>SWITCH(<br>    SELECTEDVALUE(SlicerTable[KPI]),<br>    &quot;Revenue&quot;,      5000000,<br>    &quot;Units&quot;,        10000,<br>    &quot;Margin %&quot;,     0.35,<br>    BLANK()<br>)</pre><p>This powers a single card visual that shows the right target based on which KPI the user picks — no separate measures per KPI, no report pages per metric.</p><h3>The Pattern That Changes Everything</h3><pre>-- Conditional measure behavior based on selection<br>Revenue Display =<br>IF(<br>    SELECTEDVALUE(SlicerTable[View]) = &quot;Budget vs Actual&quot;,<br>    [Budget Variance],<br>    [Total Revenue]<br>)</pre><p>One measure. Two completely different calculations depending on what the user selects. This is how professional dashboards eliminate the clutter of dozens of hidden measures.</p><p><strong>When to reach for SELECTEDVALUE:</strong></p><ul><li>Dynamic titles and subtitles</li><li>Conditional measure behavior based on slicer</li><li>Showing the user what they’ve selected</li><li>Single-value parameter tables (what-if parameters)</li></ul><h3>Function 2: KEEPFILTERS — The Filter Protector</h3><h3>What It Does</h3><p>KEEPFILTERS is used inside CALCULATE to add a filter without removing the existing filter context on that column. It keeps both the existing filter and the new one.</p><pre>CALCULATE(expression, KEEPFILTERS(filter))</pre><h3>Why It Matters</h3><p>CALCULATE normally replaces the filter context on a column. This is powerful but has a side effect that surprises developers:</p><pre>-- ❌ This ignores the slicer selection<br>Electronics Revenue =<br>CALCULATE(<br>    SUM(FactSales[Revenue]),<br>    DimProduct[Category] = &quot;Electronics&quot;<br>)</pre><p>If the user has selected “APAC” in a region slicer and “Electronics” in a category slicer, this measure ignores the region slicer entirely — CALCULATE overrides the existing category filter without preserving the region filter interaction.</p><p>Actually, the problem is more nuanced than that. CALCULATE replaces the filter on the <strong>same column</strong> being filtered. So if the user has &quot;Electronics&quot; selected in the slicer and you write DimProduct[Category] = &quot;Electronics&quot;, you&#39;re fine. But if the user has selected &quot;Supplies&quot; and your measure forces &quot;Electronics&quot;, the measure wins and the slicer is ignored.</p><pre>-- ✅ KEEPFILTERS respects the user&#39;s slicer selection<br>Electronics Revenue (respects slicers) =<br>CALCULATE(<br>    SUM(FactSales[Revenue]),<br>    KEEPFILTERS(DimProduct[Category] = &quot;Electronics&quot;)<br>)</pre><p>With KEEPFILTERS, the measure only returns a value when the user&#39;s slicer selection includes &quot;Electronics&quot;. If they&#39;ve selected &quot;Supplies&quot;, the result is blank — which is the correct behavior.</p><h3>Real Business Use Case: Benchmark Comparison</h3><pre>-- Revenue vs Electronics benchmark, respects all other slicers<br>Electronics Benchmark =<br>CALCULATE(<br>    SUM(FactSales[Revenue]),<br>    KEEPFILTERS(DimProduct[Category] = &quot;Electronics&quot;),<br>    ALL(DimDate[Month])  -- Remove month filter to show full year benchmark<br>)</pre><p>This measure calculates the full-year Electronics revenue as a benchmark, but still respects region, subsidiary, and any other slicers the user has applied. Without KEEPFILTERS, it would ignore the category slicer entirely.</p><h3>When to Reach for KEEPFILTERS</h3><pre>-- Scenario: Show Top 3 category revenue, but only when those<br>-- categories are already selected by the user</pre><pre>Top 3 Categories Revenue =<br>CALCULATE(<br>    SUM(FactSales[Revenue]),<br>    KEEPFILTERS(<br>        DimProduct[Category] IN {&quot;Electronics&quot;, &quot;Furniture&quot;, &quot;Supplies&quot;}<br>    )<br>)<br>-- This returns values only for categories in both:<br>-- 1. The user&#39;s slicer selection<br>-- 2. The hardcoded list<br>-- If user selects &quot;Clothing&quot;, this returns BLANK — correct behavior.</pre><p><strong>When to reach for KEEPFILTERS:</strong></p><ul><li>Adding a hardcoded filter that should still respect user selections</li><li>Benchmark or target measures that shouldn’t override the slicer</li><li>Any situation where CALCULATE seems to &quot;ignore&quot; a slicer selection</li><li>Multi-column filter intersections</li></ul><h3>Function 3: GENERATESERIES — The Sequence Builder</h3><h3>What It Does</h3><p>GENERATESERIES returns a single-column table of values at a defined interval, from a start value to an end value.</p><pre>GENERATESERIES(startValue, endValue, [incrementValue])</pre><h3>Why It’s Underrated</h3><p>Most developers don’t realize they need this function until they’re trying to build something dynamic — a what-if parameter table, a custom axis, a threshold range — and discover that hard-coding values in a disconnected table is fragile and maintenance-heavy.</p><p>GENERATESERIES creates these tables dynamically in DAX, no data source needed.</p><h3>Real Business Use Case 1: What-If Parameter Table</h3><p>Before Power BI introduced the built-in What-If Parameter feature, GENERATESERIES was the standard way to build scenario slicers. It&#39;s still useful when you need more control than the built-in feature provides:</p><pre>-- Discount Rate Table (0% to 50%, in 5% increments)<br>Discount Rate Table =<br>GENERATESERIES(0, 0.5, 0.05)</pre><pre>-- Then create the measure:<br>Adjusted Revenue =<br>[Total Revenue] * (1 - SELECTEDVALUE(&#39;Discount Rate Table&#39;[Value], 0))</pre><p>The user picks a discount rate in a slicer. Every revenue figure on the dashboard adjusts automatically. No static table needed. No data source needed.</p><h3>Real Business Use Case 2: Custom Axis for Distribution Chart</h3><pre>-- Revenue bucket axis for distribution analysis<br>Revenue Buckets =<br>GENERATESERIES(0, 1000000, 50000)<br>-- Creates: 0, 50000, 100000, 150000, ... 1000000</pre><pre>-- Measure that counts orders falling in each bucket<br>Orders in Bucket =<br>COUNTROWS(<br>    FILTER(<br>        FactSales,<br>        FactSales[Revenue] &gt;= SELECTEDVALUE(&#39;Revenue Buckets&#39;[Value]) &amp;&amp;<br>        FactSales[Revenue] &lt; SELECTEDVALUE(&#39;Revenue Buckets&#39;[Value]) + 50000<br>    )<br>)</pre><p>This creates a true distribution histogram from your transaction data — something Power BI’s native visualizations don’t support directly.</p><h3>Real Business Use Case 3: Aging Brackets</h3><pre>-- AR Aging buckets: 0, 30, 60, 90, 120 days<br>Aging Buckets =<br>GENERATESERIES(0, 120, 30)</pre><pre>-- Display label for each bucket<br>Aging Label =<br>VAR BucketStart = SELECTEDVALUE(&#39;Aging Buckets&#39;[Value])<br>VAR BucketEnd = BucketStart + 30<br>RETURN<br>IF(<br>    BucketStart = 120,<br>    &quot;120+ days&quot;,<br>    FORMAT(BucketStart, &quot;0&quot;) &amp; &quot;–&quot; &amp; FORMAT(BucketEnd, &quot;0&quot;) &amp; &quot; days&quot;<br>)</pre><p><strong>When to reach for GENERATESERIES:</strong></p><ul><li>What-if scenario sliders (discount rates, growth rates, headcount)</li><li>Custom axis ranges for distribution charts</li><li>Aging brackets (AR, inventory, support tickets)</li><li>Any numeric sequence that would otherwise be hand-typed in a static table</li></ul><h3>Function 4: NAMEOF — The Maintenance Saver</h3><h3>What It Does</h3><p>NAMEOF returns the name of a column or measure as a text string, evaluated at compile time rather than runtime.</p><pre>NAMEOF(columnOrMeasure)</pre><h3>Why It Matters</h3><p>This function solves a subtle but expensive problem: when you rename a column or measure, any hard-coded string references to that name break silently. The error doesn’t show up immediately — it shows up the next time someone opens the report and sees a broken dynamic title or a missing label.</p><pre>-- ❌ Fragile: if you rename the column, this string becomes wrong<br>Column Label = &quot;Revenue&quot;</pre><pre>-- ✅ Robust: follows the column name automatically<br>Column Label = NAMEOF(FactSales[Revenue])</pre><h3>Real Business Use Case: Self-Documenting Dynamic Titles</h3><pre>-- Title that automatically reflects the actual column being measured<br>-- If someone renames [Revenue] to [Net Revenue], the title updates<br>Chart Title =<br>&quot;Total &quot; &amp; NAMEOF(FactSales[Revenue]) &amp; &quot; by &quot; &amp; NAMEOF(DimDate[Month])</pre><pre>-- Produces: &quot;Total Revenue by Month&quot;<br>-- If Revenue is renamed to &quot;Net Revenue&quot;: &quot;Total Net Revenue by Month&quot;</pre><h3>Real Business Use Case: Error Messages and Tooltips</h3><pre>-- Tooltip that names the exact measure driving the number<br>Tooltip Text =<br>&quot;This value represents &quot; &amp; NAMEOF([Total Revenue]) &amp;<br>&quot; for the selected period. &quot; &amp;<br>&quot;Calculation: SUM of all invoiced amounts, excluding returns.&quot;</pre><pre>-- Validation message referencing the actual field names<br>Data Warning =<br>IF(<br>    ISBLANK([Total Revenue]),<br>    &quot;No data found for &quot; &amp; NAMEOF(DimDate[Year]) &amp;<br>    &quot; = &quot; &amp; SELECTEDVALUE(DimDate[Year], &quot;unknown&quot;),<br>    BLANK()<br>)</pre><h3>The Operational Value</h3><p>NAMEOF is not glamorous. It doesn&#39;t make your calculations faster or more sophisticated. What it does is make your model more maintainable — especially in environments where column and measure names change as the business evolves.</p><p>In an enterprise model with 50+ measures and 8+ dimension tables, a single column rename can break 12 different string references scattered across measures, titles, and tooltips. NAMEOF makes those references self-healing.</p><p><strong>When to reach for NAMEOF:</strong></p><ul><li>Dynamic titles that reference column or measure names</li><li>Documentation strings inside measures</li><li>Error messages and tooltip text</li><li>Any model that will be maintained by more than one person</li></ul><h3>Function 5: COALESCE — The Null Handler</h3><h3>What It Does</h3><p>COALESCE evaluates a list of expressions in order and returns the first one that is not BLANK or null.</p><pre>COALESCE(expression1, expression2, ..., expressionN)</pre><h3>Why It’s Better Than IF(ISBLANK())</h3><p>Null handling in DAX is one of the most common sources of verbose, repetitive code. The standard pattern is:</p><p>dax</p><pre>-- ❌ Verbose null handling (written by most developers)<br>Safe Revenue =<br>IF(<br>    ISBLANK([Total Revenue]),<br>    0,<br>    [Total Revenue]<br>)</pre><p>For a single measure, that’s manageable. When you’re handling nulls across a dozen measures, it becomes noise. COALESCE reduces this to a single expression:</p><pre>-- ✅ Clean equivalent<br>Safe Revenue = COALESCE([Total Revenue], 0)</pre><p>But the real power isn’t replacing IF(ISBLANK()). It&#39;s the ability to chain multiple fallback values.</p><h3>Real Business Use Case: Fallback Hierarchy</h3><pre>-- Revenue with a clear priority fallback chain:<br>-- 1. Use actual revenue if available<br>-- 2. Fall back to forecast if actual is blank<br>-- 3. Fall back to budget if forecast is also blank<br>-- 4. Return 0 if nothing exists</pre><pre>Best Available Revenue =<br>COALESCE(<br>    [Actual Revenue],<br>    [Forecast Revenue],<br>    [Budget Revenue],<br>    0<br>)</pre><p>Try writing that with nested IF(ISBLANK()) calls:</p><pre>-- ❌ The nested IF version (don&#39;t do this)<br>Best Available Revenue =<br>IF(<br>    ISBLANK([Actual Revenue]),<br>    IF(<br>        ISBLANK([Forecast Revenue]),<br>        IF(<br>            ISBLANK([Budget Revenue]),<br>            0,<br>            [Budget Revenue]<br>        ),<br>        [Forecast Revenue]<br>    ),<br>    [Actual Revenue]<br>)</pre><p>Four levels of nesting to express a simple priority chain. COALESCE does it in five lines.</p><h3>Real Business Use Case: Safe Division with Context</h3><pre>-- Margin % that handles missing cost data gracefully<br>Safe Margin % =<br>DIVIDE(<br>    COALESCE([Gross Profit], 0),<br>    COALESCE([Total Revenue], BLANK()),  -- Returns BLANK if revenue is also BLANK<br>    BLANK()<br>)</pre><h3>Real Business Use Case: First Non-Blank Date</h3><pre>-- Find the first available date in a date hierarchy<br>-- (useful when data coverage is uneven across subsidiaries)<br>First Available Date =<br>COALESCE(<br>    CALCULATE(MIN(FactSales[OrderDate]),<br>              DimGeography[Region] = SELECTEDVALUE(DimGeography[Region])),<br>    CALCULATE(MIN(FactSales[OrderDate])),<br>    DATE(2020, 1, 1)  -- Absolute fallback<br>)</pre><h3>Real Business Use Case: Multi-Source Data Blending</h3><pre>-- Unit price from different sources, in priority order<br>Effective Unit Price =<br>COALESCE(<br>    RELATED(DimProduct[OverridePrice]),   -- Manual override first<br>    RELATED(DimProduct[ContractPrice]),    -- Contract price second<br>    RELATED(DimProduct[StandardPrice]),    -- Standard price third<br>    AVERAGE(FactSales[ActualPrice])        -- Average from transactions as last resort<br>)</pre><p>This single measure encodes an entire pricing hierarchy that would otherwise require a complex nested IF structure or a separate pricing logic table.</p><p><strong>When to reach for COALESCE:</strong></p><ul><li>Any null-handling that has more than one fallback</li><li>Actual vs. forecast vs. budget priority chains</li><li>Multi-source data blending (override → contract → standard)</li><li>Replacing nested IF(ISBLANK()) chains</li><li>Safe aggregations where missing data should return 0 vs BLANK</li></ul><h3>Putting It Together: A Real Measure Set</h3><p>Here’s how these five functions work together in a single dashboard scenario — a financial performance report with dynamic behavior:</p><pre>-- 1. SELECTEDVALUE: Dynamic title showing active filters<br>Report Title =<br>&quot;Financial Performance — &quot; &amp;<br>SELECTEDVALUE(DimDate[Year], &quot;All Years&quot;) &amp; &quot; | &quot; &amp;<br>SELECTEDVALUE(DimGeography[Subsidiary], &quot;All Subsidiaries&quot;)<br></pre><pre>-- 2. KEEPFILTERS: Benchmark that respects user slicers<br>HQ Benchmark =<br>CALCULATE(<br>    SUM(FactSales[Revenue]),<br>    KEEPFILTERS(DimGeography[Subsidiary] = &quot;Headquarters&quot;),<br>    ALL(DimDate[Month])<br>)<br></pre><pre>-- 3. GENERATESERIES: Growth scenario slicer<br>-- (in a calculated table, not a measure)<br>Growth Rate Scenario =<br>GENERATESERIES(0.05, 0.30, 0.05)<br>-- Creates: 0.05, 0.10, 0.15, 0.20, 0.25, 0.30</pre><pre>Projected Revenue =<br>[Total Revenue] * (1 + SELECTEDVALUE(&#39;Growth Rate Scenario&#39;[Value], 0.10))<br></pre><pre>-- 4. NAMEOF: Self-maintaining tooltip<br>Revenue Tooltip =<br>&quot;Showing &quot; &amp; NAMEOF(FactSales[Revenue]) &amp;<br>&quot; as reported. Excludes intercompany transactions.&quot;<br></pre><pre>-- 5. COALESCE: Actual → Forecast → Budget fallback<br>Best Available Revenue =<br>COALESCE([Actual Revenue], [Forecast Revenue], [Budget Revenue], 0)</pre><pre>Variance vs Best Available =<br>DIVIDE(<br>    [Total Revenue] - [Best Available Revenue],<br>    COALESCE([Best Available Revenue], BLANK()),<br>    BLANK()<br>)</pre><p>Six measures. Five functions. A dashboard that’s dynamic, maintainable, handles missing data gracefully, and tells users exactly what they’re looking at.</p><h3>Operational Lesson: The Hidden Cost of Not Knowing</h3><p>Here’s the lesson I keep seeing repeated across teams:</p><p><strong>A developer who doesn’t know COALESCE writes IF(ISBLANK()). A developer who doesn’t know SELECTEDVALUE writes three-function combinations. A developer who doesn’t know KEEPFILTERS writes a workaround that silently ignores the user’s slicer.</strong></p><p>Each workaround works. The dashboard ships. The business uses it.</p><p>But six months later, the next developer inherits the model. They see 40 lines of nested IF statements and wonder why it’s structured that way. They change something that looks redundant. The measure breaks. Nobody knows why — because the original logic was a workaround for a problem the original developer didn’t know had a simpler solution.</p><p>This is how technical debt accumulates in DAX. Not through bad intentions, but through incomplete tool knowledge.</p><p>The five functions in this article won’t cover every scenario you’ll encounter. But they represent a category of knowledge — functions that make your intent explicit, your code readable, and your models easier for the next developer to maintain.</p><p>That next developer might be you, eight months from now, looking at a model you haven’t touched since the last project ended. The cleaner you write it today, the faster you’ll understand it then.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TZ9esLDyaW17xZVb_4Y0MA.png" /></figure><p><strong>Vembry Fiska</strong> Senior Power BI Developer &amp; Data Engineer</p><p><em>I design and build Power BI models for mid-market companies.</em></p><p><strong>SEO keywords:</strong> Power BI DAX functions, SELECTEDVALUE Power BI, KEEPFILTERS DAX, GENERATESERIES Power BI, COALESCE DAX, advanced DAX tutorial</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8a54404993aa" width="1" height="1" alt=""><hr><p><a href="https://pub.towardsai.net/5-power-bi-dax-functions-you-didnt-know-existed-8a54404993aa">5 Power BI DAX Functions You Didn’t Know Existed</a> was originally published in <a href="https://pub.towardsai.net/">Towards AI</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Hidden Cost of Flat Tables in Power BI]]></title>
            <link>https://medium.com/@fiska.nurwantoro/the-hidden-cost-of-flat-tables-in-power-bi-9ca03c98f365?source=rss-4a74082fcf9d------2</link>
            <guid isPermaLink="false">https://medium.com/p/9ca03c98f365</guid>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <dc:creator><![CDATA[Vembry Fiska]]></dc:creator>
            <pubDate>Thu, 18 Jun 2026 09:11:15 GMT</pubDate>
            <atom:updated>2026-06-18T09:11:58.622Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Everyone talks about slow dashboards. Nobody talks about what flat tables actually cost your team, your data, and your business decisions over time.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NombVYjPkhvgkeOT5YovmQ.png" /><figcaption>iceberg of flat table</figcaption></figure><h3>The Problem Nobody Names</h3><p>When you ask a Power BI developer why flat tables are bad, they’ll say “performance.”</p><p>And they’re right. Flat tables are slow. VertiPaq hates them. Queries scan too many rows. Refresh takes too long.</p><p>But that’s only the visible cost.</p><p>The real damage from flat tables is invisible. It accumulates quietly over months while your team builds workarounds on top of workarounds. It shows up in dashboards that technically work but can’t answer the questions the business is actually asking. It appears in onboarding sessions that take two days instead of two hours because nobody can explain why the model looks the way it does.</p><p>I’ve audited Power BI models at many companies. When I find a flat table, the performance problem is almost never the most expensive part. The most expensive part is everything built on top of it.</p><p>This article is about those hidden costs — the ones that don’t show up in a query profiler but show up in your quarterly reports, your team’s morale, and your analytics ROI.</p><h3>What Is a Flat Table (And Why Does It Happen)?</h3><p>A flat table is what you get when all your data lives in a single, wide table with no relationships.</p><pre>SalesData (flat table — everything in one place):<br>──────────────────────────────────────────────────────────<br>OrderID | OrderDate | CustomerID | CustomerName |<br>CustomerCity | CustomerCountry | CustomerSegment |<br>ProductID | ProductName | Category | SubCategory | Brand |<br>SalesRepID | SalesRepName | SalesRegion | SalesTerritory |<br>Revenue | Units | COGS | Discount | ShippingCost | Profit |<br>WarehouseID | WarehouseLocation | StockLevel | LeadTime |<br>[... sometimes 60, 80, even 100+ columns]<br>──────────────────────────────────────────────────────────</pre><p>Every order row contains: the order details, the full customer profile, the full product details, the sales rep details, the warehouse details, and all the metrics.</p><p><strong>How does it happen?</strong></p><p>Flat tables emerge organically. Not from bad intentions — from reasonable shortcuts at the start of a project:</p><ol><li>Someone exports from an ERP or CRM as a single CSV</li><li>A developer connects Power BI directly to a database view that already joins everything</li><li>An analyst says “let’s just add one more column” — and that becomes a habit</li><li>Nobody questions it because it works for the first 50,000 rows</li></ol><p>Then the table grows. More columns get added. Then it’s 10M rows with 70 columns and nobody wants to touch it.</p><p>This is the origin story of almost every flat table I’ve ever audited.</p><h3>The Visible Cost: Performance</h3><p>Let’s start with the obvious one because it helps quantify what comes next.</p><h3>What VertiPaq Does With a Flat Table</h3><p>Power BI’s in-memory engine (VertiPaq) compresses each column independently using dictionary encoding — it builds a lookup table of unique values and replaces repeated strings with numeric references.</p><p>This works brilliantly for columns with many unique values. It fails for columns with repeated strings — which is exactly what a flat table produces.</p><pre>Flat table — CustomerName column (10M rows):<br>&quot;Acme Corp&quot;        ← appears 8,400 times<br>&quot;BuildCo Ltd&quot;      ← appears 5,200 times<br>&quot;Delta Services&quot;   ← appears 3,100 times<br>... (500 unique customers × average 20,000 orders each)</pre><pre>VertiPaq builds dictionary:<br>{&quot;Acme Corp&quot;: 1, &quot;BuildCo Ltd&quot;: 2, &quot;Delta Services&quot;: 3, ...}</pre><pre>But still stores the mapping 10M times.<br>Compression ratio: ~40%</pre><pre>Star schema — FactSales + DimCustomer:<br>FactSales.CustomerID = [1, 1, 1, 2, 2, 3, ...] (numeric, 4 bytes)<br>DimCustomer stores &quot;Acme Corp&quot; exactly ONCE</pre><pre>Compression ratio: ~92%</pre><p>The numbers in production:</p><p>MetricFlat TableStar SchemaImprovementFile size1.2 GB89 MB93% smallerModel load time8 min45 sec10× fasterFilter response3–45 sec&lt;200 ms15–225× fasterScheduled refresh22 min1.8 min12× faster</p><p>These are real numbers from a single client migration. The scale varies, but the direction never does.</p><h3>The Hidden Cost 1: DAX Becomes Unmaintainable</h3><p>This is the first cost nobody talks about. And it’s enormous.</p><h3>Flat Tables Force Workaround DAX</h3><p>When your model is a single flat table, filters don’t propagate through relationships — because there are no relationships. Every measure has to manually carry its own filter logic.</p><p><strong>Example: Revenue by Category in a flat table:</strong></p><pre>-- ❌ What you&#39;re forced to write (flat table)<br>Revenue Electronics =<br>CALCULATE(<br>    SUM(SalesData[Revenue]),<br>    FILTER(SalesData, SalesData[Category] = &quot;Electronics&quot;)<br>)<br><br>Revenue Supplies =<br>CALCULATE(<br>    SUM(SalesData[Revenue]),<br>    FILTER(SalesData, SalesData[Category] = &quot;Supplies&quot;)<br>)<br>Revenue Furniture =<br>CALCULATE(<br>    SUM(SalesData[Revenue]),<br>    FILTER(SalesData, SalesData[Category] = &quot;Furniture&quot;)<br>)<br>-- One measure per category. If you have 20 categories, you have 20 measures.<br>-- Each one scans the full 10M row table independently.</pre><p><strong>The same requirement in a star schema:</strong></p><pre>-- ✅ What you write with proper relationships (star schema)<br>Total Revenue =<br>SUM(FactSales[Revenue])<br>-- Done. The slicer on Category filters DimProduct,<br>-- which flows through the relationship to FactSales automatically.<br>-- One measure. Works for any category.</pre><h3>The Compound Effect Over Time</h3><p>The DAX problem compounds as the business adds requirements. Every new question — “show me by region,” “break it down by sales rep,” “compare this year vs. last year” — requires a new set of manual FILTER measures in a flat table model.</p><p>After 12 months of development, a flat table model typically accumulates:</p><pre>Flat table model (typical 12-month lifespan):<br>- 200+ measures<br>- 60% of them are manual FILTER workarounds<br>- Each measure references the same full table scan<br>- New developer joining the project: 2 days to understand the model<br>- Modifying one measure: risks breaking 3 others (hidden dependencies)<br>- Time to answer a new business question: 3-4 hours</pre><pre>Star schema model (same 12-month lifespan):<br>- 40-60 measures<br>- 80% are simple SUM/AVERAGE/COUNT + CALCULATE<br>- Filters flow through relationships automatically<br>- New developer joining the project: 2 hours to understand the model<br>- Modifying one measure: isolated, predictable impact<br>- Time to answer a new business question: 30-60 minutes</pre><p>The ratio isn’t 2:1. It’s often 5:1 or worse.</p><p><strong>The business impact of unmaintainable DAX:</strong></p><pre>Scenario: Business wants to add &quot;Revenue by Sales Territory&quot; to the dashboard.<br><br>Flat table model:<br>- Developer needs to write 8 new measures (one per territory)<br>- Each measure takes 45 minutes to write + test<br>- Total: 6 hours of development time<br>- One week later, business asks to also break it down by product<br>- Developer needs to write 8 × N product combinations<br>- It grows exponentially. Fast.<br>Star schema model:<br>- Developer adds DimTerritory (or adds Territory to DimSalesRep)<br>- Creates one relationship<br>- Existing measures automatically work by territory via slicer<br>- Total: 2 hours including testing</pre><h3>Real Cost Calculation</h3><pre>Development time differential over 12 months:<br>- 2 new business requirements per week × 52 weeks = 104 new questions<br>- Flat table: 4 hours per question = 416 hours<br>- Star schema: 1 hour per question = 104 hours<br>- Difference: 312 hours × $75/hour = $23,400/year</pre><pre>Just in DAX development time.<br>Before you count the bugs and rework.</pre><h3>The Hidden Cost 2: Data Quality Problems Hide in Plain Sight</h3><p>Flat tables don’t just slow down your queries. They actively hide data quality issues that a star schema would surface immediately.</p><h3>The Duplication Problem</h3><p>In a flat table, if a product name is spelled two different ways in the source system, both spellings appear in the table. They look like two different products. Your “Electronics” filter misses 3% of records because someone entered “Electonics” (typo) in 200 rows.</p><pre>Flat table — Product column:<br>&quot;Electronics&quot;     ← 45,230 rows<br>&quot;Electonics&quot;      ←    192 rows ← invisible typo<br>&quot;electronics&quot;     ←     88 rows ← case sensitivity issue<br>&quot;ELECTRONICS&quot;     ←     12 rows ← all-caps batch import</pre><p>Nobody notices. The dashboard “works.” The revenue chart looks right because 99.4% of electronics data is captured. The missing 0.6% is invisible.</p><p>In a star schema, this problem surfaces immediately:</p><pre>DimProduct load fails with:<br>ERROR: Duplicate product codes detected<br>  - &quot;Electronics&quot;, &quot;Electonics&quot;, &quot;electronics&quot;, &quot;ELECTRONICS&quot;<br>  all map to the same product group.<br>  Resolve before loading.</pre><p>Forced data quality. Star schema models require clean dimension tables. That constraint is a feature, not a bug.</p><h3>The Aggregation Accuracy Problem</h3><p>Flat tables often contain pre-aggregated or partially-aggregated data mixed with transactional data. This is invisible until someone asks the wrong question.</p><pre>Common scenario:<br>Row 1: OrderID=1001, ProductID=P1, Revenue=100  ← transaction row<br>Row 2: OrderID=1001, ProductID=P2, Revenue=200  ← transaction row<br>Row 3: OrderID=1001, ProductID=NULL, Revenue=300 ← subtotal row (!)</pre><pre>SUM(Revenue) = 600 instead of 300<br>The subtotal row doubles the count.</pre><p>This happens more often than you’d expect when data comes from ERP exports, Excel hand-offs, or mixed-source joins. A flat table accepts it silently. A star schema fact table with a proper grain definition would reject it.</p><h3>The “Which Column?” Problem</h3><p>Wide flat tables often have redundant or near-identical columns with subtle differences:</p><pre>Flat table columns (real example from an audit):<br>Revenue         ← transaction currency<br>RevenueUSD      ← converted to USD<br>RevenueNet      ← after discount<br>RevenueGross    ← before discount<br>RevenueBooked   ← recognized revenue<br>RevenueActual   ← cash received</pre><p>Six revenue columns. Which one should the finance dashboard use? Which one are the sales managers using? Are they using the same one?</p><p>Often: nobody knows. Different teams use different columns. The CFO’s dashboard and the Sales VP’s dashboard show different revenue numbers. Both are “correct” — they’re measuring different things. But nobody documented which is which.</p><p>This problem exists in star schemas too, but it’s much less common because the modeling process forces you to define the grain and purpose of each metric before building.</p><h3>The Hidden Cost 3: Scaling Breaks Everything</h3><p>Flat tables work fine at small scale. The problems don’t appear until you grow — which is exactly when you can least afford a re-architecture.</p><h3>The New Data Source Problem</h3><p>Suppose your flat table has sales data. The business now wants to add:</p><ul><li>Returns data (from a different source)</li><li>Forecast data (from a planning system)</li><li>Budget data (from Excel)</li></ul><p>In a star schema:</p><pre>Add FactReturns table → link to existing DimProduct, DimCustomer, DimDate<br>Add FactForecast table → link to existing dimensions<br>Add FactBudget table → link to existing dimensions</pre><pre>Each new source reuses the existing dimension tables.<br>Relationships are clean. No duplication.</pre><p>In a flat table:</p><pre>Option A: Add columns to the existing flat table<br>→ Returns columns: ReturnDate, ReturnReason, ReturnAmount... (+10 columns)<br>→ Forecast columns: ForecastDate, ForecastAmount... (+5 columns)<br>→ Budget columns: BudgetPeriod, BudgetAmount... (+5 columns)<br>→ Table now has 90 columns. Most rows have NULL in half of them.<br>→ File size doubles. Performance degrades further.</pre><pre>Option B: Add separate flat tables and link them<br>→ Now you have multiple flat tables<br>→ Relationships are Many-to-Many (dangerous)<br>→ Filter context breaks in unexpected ways<br>→ You&#39;ve created a worse version of a star schema, accidentally</pre><p>Either option makes things worse. The only clean solution is re-architecture — which means rebuilding everything, including the 200 DAX measures you wrote over the last 12 months.</p><h3>The History Problem</h3><p>Flat tables can’t track Slowly Changing Dimensions (SCDs) without significant workarounds.</p><p>Consider a customer who moves from “Small Business” to “Enterprise” segment mid-year. In a flat table, you have one column: CustomerSegment. If you update it to &quot;Enterprise,&quot; you lose the history. All past orders now show &quot;Enterprise&quot; — including the ones from when they were actually &quot;Small Business.&quot;</p><p>Your Year-over-Year segment analysis becomes unreliable. Historical reports change retroactively as dimension data gets updated.</p><p>Star schemas handle this with SCD Type 2 patterns — maintaining effective date ranges on dimension rows. Flat tables have no equivalent mechanism that doesn’t involve doubling the table size.</p><h3>The User Limit Problem</h3><p>As more teams start using the dashboard, flat tables hit a wall.</p><p>VertiPaq is an in-memory engine. The flat table has to fit in memory entirely. A 1.2 GB flat table requires 1.2 GB of RAM on the Power BI Service per concurrent query.</p><p>With 50 concurrent users, that’s up to 60 GB of RAM needed — depending on caching behavior. Premium capacity tiers become necessary earlier than they should.</p><p>A star schema’s 89 MB model handles the same 50 users with a fraction of the memory footprint.</p><h3>The Hidden Cost 4: Team Productivity and Knowledge Loss</h3><p>This is the most underrated cost of all, because it’s entirely invisible on any dashboard.</p><h3>The Onboarding Tax</h3><p>When a new analyst or developer joins the team, they need to understand the Power BI model to be productive.</p><p><strong>Flat table onboarding:</strong></p><pre>New analyst joins. First task: &quot;Add a revenue breakdown by product tier.&quot;</pre><pre>Day 1:<br>- Opens the model<br>- Sees 80 columns in one table<br>- No documentation<br>- Finds 200+ measures<br>- Asks: &quot;Which revenue column should I use?&quot;<br>- Answer: &quot;Depends on context. RevenueNet for sales reports,<br>           RevenueBooked for finance. Don&#39;t use RevenueGross<br>           for anything after Q2 because the calculation changed.&quot;<br>- Writes measure. Gets wrong result. Debugs for 2 hours.<br>- Discovers that a filter doesn&#39;t work the way they expected<br>  because there are no relationships.</pre><pre>Day 2:<br>- Finally completes the task<br>- Writes it down so the next person doesn&#39;t go through the same thing</pre><p><strong>Star schema onboarding:</strong></p><pre>New analyst joins. Same task: &quot;Add a revenue breakdown by product tier.&quot;</pre><pre>Day 1:<br>- Opens the model<br>- Sees FactSales + 4 dimension tables<br>- Relationships are visible in Model View<br>- Finds 50 measures with clear naming conventions<br>- Writes: CALCULATE(SUM(FactSales[Revenue])) grouped by DimProduct[Tier]<br>- Works immediately. Done in 2 hours.</pre><p>The difference: 2 days vs. 2 hours. For every person who joins the team.</p><h3>The “Only One Person Understands This” Problem</h3><p>Flat table models with hundreds of custom measures tend to become the domain of whoever built them. That person becomes a bottleneck — and a single point of failure.</p><pre>Typical situation:<br>- &quot;The report is broken.&quot;<br>- &quot;I&#39;ll ask Sarah. She built it.&quot;<br>- Sarah is on holiday.<br>- The report stays broken for 3 days.</pre><pre>Or worse:<br>- Sarah leaves the company.<br>- Nobody else fully understands the model.<br>- The next person spends 2 months deciphering it<br>  before they can safely make any changes.</pre><p>This isn’t a people problem. It’s an architecture problem. Flat tables create complexity that resists documentation and shared understanding. Star schemas are self-documenting by design — the relationships tell the story of the data.</p><h3>The “It Takes Too Long to Build New Reports” Problem</h3><p>This is the feedback that eventually reaches the business, even if the technical cause is never named.</p><pre>Business request: &quot;Can we add a new KPI to the monthly board report?&quot;</pre><pre>Flat table model:<br>- Developer: &quot;I&#39;ll need about 3 days.&quot;<br>- Business: &quot;That seems long for one number.&quot;<br>- Developer: &quot;I need to make sure it doesn&#39;t break the existing 200 measures.&quot;</pre><pre>Star schema model:<br>- Developer: &quot;I&#39;ll have that done by tomorrow.&quot;<br>- Business: &quot;Great.&quot;</pre><p>When analytics requests start taking “too long,” the business finds workarounds: Excel, Google Sheets, separate BI tools. The Power BI platform gets abandoned incrementally. The investment in the tool is wasted.</p><h3>The Hidden Cost 5: Wrong Decisions From Bad Context</h3><p>This is the most expensive cost. And the hardest to measure.</p><h3>The Filter Context Failure</h3><p>In a flat table model without relationships, filter context in DAX behaves differently from what most developers expect — and most non-technical users never notice the inconsistency.</p><p><strong>Example: Sales by Region, filtered by Product Category</strong></p><p>dax</p><pre>-- Flat table model<br>-- User selects &quot;Electronics&quot; in the slicer<br>-- This measure works correctly:<br>Revenue by Region =<br>CALCULATE(<br>    SUM(SalesData[Revenue]),<br>    FILTER(SalesData, SalesData[Category] = &quot;Electronics&quot;)<br>)</pre><pre>-- But this measure ignores the slicer:<br>Revenue YTD =<br>CALCULATE(<br>    SUM(SalesData[Revenue]),<br>    DATESYTD(SalesData[OrderDate])<br>)<br>-- DATESYTD creates its own filter context that overrides the slicer<br>-- Without relationships, the interaction breaks silently<br>-- The number looks plausible. It&#39;s wrong.</pre><p>The dashboard shows a number. It looks reasonable. The finance team uses it to make a quarterly decision. The number was wrong.</p><p>These are the bugs that never get reported as bugs — because nobody knows the number should have been different.</p><h3>The “Consistent But Wrong” Problem</h3><p>Flat tables often produce metrics that are internally consistent (the same number appears every time you look) but incorrect relative to the real business reality.</p><pre>Example: Inventory dashboard built on a flat table</pre><pre>The model shows:<br>- Total SKUs in stock: 1,247<br>- Total stock value: $2.4M<br>- Stockout rate: 3.2%</pre><pre>These numbers are consistent week over week. Teams use them.</pre><pre>Actual reality (discovered during a physical inventory audit):<br>- Total SKUs: 1,312 (65 SKUs were missing due to a duplication<br>  filter in the flat table&#39;s SQL view)<br>- Total stock value: $2.6M (currency conversion column being used<br>  was updated but not flagged)<br>- Stockout rate: 7.8% (the flat table excluded zero-quantity records)</pre><p>The dashboard was wrong for 7 months. Procurement decisions were made on the wrong data. Nobody caught it because the numbers looked consistent.</p><p>A star schema with proper grain definitions and explicit relationships would have surfaced these issues during model validation.</p><h3>How to Recognize a Flat Table Model (Quick Audit)</h3><p>Before you can fix the problem, you need to identify it. Here’s the 5-minute audit:</p><pre>FLAT TABLE INDICATORS:<br>──────────────────────────────────────────────────────────────<br>□ More than 15 columns in a single table<br>□ No relationships in Model View (or only one)<br>□ Column names like: CustomerName, ProductCategory, SalesRegion<br>  (descriptive text in a table that also has Revenue, Units, etc.)<br>□ Measures that use FILTER(TableName, TableName[Column] = &quot;X&quot;)<br>  pattern (manual filters because no relationships)<br>□ Measure count: over 100 measures for a 3-5 visual dashboard<br>□ File size over 300 MB for a single-subject dashboard<br>□ &quot;Don&#39;t change that measure, it might break something else&quot;<br>  warning from a team member<br>□ No DimDate, DimProduct, DimCustomer tables<br>□ A column called something like &quot;ProductName_1&quot; and &quot;ProductName_2&quot;<br>  (the flat table ran out of good names)<br>──────────────────────────────────────────────────────────────</pre><pre>Score: 3+ indicators = flat table model<br>       6+ indicators = urgent re-architecture needed</pre><h3>The Full Cost Picture</h3><p>When you add it all up, a flat table model doesn’t just cost you performance:</p><pre>THE REAL ANNUAL COST OF A FLAT TABLE MODEL<br>(Mid-market company, 50-person analytics team)</pre><pre>Performance waste (dashboard waiting time):<br>  50 users × 30 min/day × 250 days × $30/hr   = $18,750/year</pre><pre>DAX development overhead:<br>  2× development time × 2 developers × $75/hr<br>  × 500 hours/year additional                  = $75,000/year</pre><pre>Onboarding tax:<br>  3 new analysts/year × 1.5 extra days × $300/day = $1,350/year</pre><pre>Re-architecture cost (happens every 2-3 years):<br>  100 hours × $150/hr ÷ 2 years               = $7,500/year</pre><pre>Wrong decisions from bad data (conservative estimate):<br>  1 significant decision error per year<br>  at $50,000 average impact                    = $50,000/year</pre><pre>──────────────────────────────────────────────────────────────<br>TOTAL ESTIMATED ANNUAL COST:                    ~$152,600/year<br>──────────────────────────────────────────────────────────────</pre><pre>Cost of proper star schema architecture:<br>  Initial build: 40 hours × $150/hr           = $6,000 one-time<br>  Annual maintenance: 20 hours × $150/hr      = $3,000/year</pre><pre>──────────────────────────────────────────────────────────────<br>Net saving from proper architecture:           ~$149,600/year<br>ROI on star schema investment:                 &gt;2,400%<br>──────────────────────────────────────────────────────────────</pre><p>These are conservative estimates. The wrong-decision cost alone can dwarf everything else in the right (or wrong) circumstances.</p><h3>The Migration Path: How to Fix It Without Starting Over</h3><p>You have a flat table model in production. You can’t rebuild everything overnight. Here’s the pragmatic path.</p><h3>Phase 1: Stop the Bleeding (Week 1)</h3><p>Don’t rebuild yet. Stabilize first.</p><pre>1. Freeze new column additions to the flat table<br>2. Document every measure: what it calculates, which column it uses<br>3. Identify the top 5 measures the business actually depends on<br>4. Measure your baseline: file size, refresh time, filter response time<br>5. Create a migration plan</pre><h3>Phase 2: Extract Dimensions (Weeks 2–3)</h3><p>Start pulling dimensions out of the flat table using Power Query:</p><pre>// Create DimProduct from the flat table<br>DimProduct =<br>Table.Distinct(<br>    Table.SelectColumns(<br>        SalesData,<br>        {&quot;ProductID&quot;, &quot;ProductName&quot;, &quot;Category&quot;, &quot;SubCategory&quot;, &quot;Brand&quot;}<br>    )<br>)<br><br>// Create DimCustomer from the flat table<br>DimCustomer =<br>Table.Distinct(<br>    Table.SelectColumns(<br>        SalesData,<br>        {&quot;CustomerID&quot;, &quot;CustomerName&quot;, &quot;CustomerCity&quot;,<br>         &quot;CustomerCountry&quot;, &quot;CustomerSegment&quot;}<br>    )<br>)<br>// Reduce the flat table to facts only<br>FactSales =<br>Table.SelectColumns(<br>    SalesData,<br>    {&quot;OrderID&quot;, &quot;OrderDate&quot;, &quot;CustomerID&quot;, &quot;ProductID&quot;,<br>     &quot;Revenue&quot;, &quot;Units&quot;, &quot;COGS&quot;, &quot;Discount&quot;, &quot;Profit&quot;}<br>)</pre><h3>Phase 3: Create Relationships (Week 3)</h3><pre>In Model View:<br>FactSales.CustomerID → DimCustomer.CustomerID (Many-to-One)<br>FactSales.ProductID  → DimProduct.ProductID   (Many-to-One)<br>FactSales.OrderDate  → DimDate.Date           (Many-to-One)</pre><h3>Phase 4: Simplify the Measures (Weeks 4–6)</h3><p>Start replacing FILTER-based workarounds with clean relationship-based measures:</p><pre>-- OLD (Flat table pattern)<br>Revenue Electronics =<br>CALCULATE(SUM(SalesData[Revenue]),<br>    FILTER(SalesData, SalesData[Category] = &quot;Electronics&quot;))</pre><pre>-- NEW (Star schema pattern)<br>Total Revenue = SUM(FactSales[Revenue])<br>-- &quot;Electronics&quot; filter applied via slicer → DimProduct → FactSales<br>-- One measure replaces 20 category-specific measures</pre><h3>Phase 5: Validate and Cut Over (Week 6–7)</h3><pre>1. Run both models in parallel for 1-2 weeks<br>2. Compare key metrics: revenue totals, counts, ratios<br>3. Investigate any discrepancy &gt; 0.1%<br>4. Get sign-off from business stakeholders<br>5. Retire the flat table model<br>6. Archive it (don&#39;t delete — you&#39;ll want it for reference)</pre><p>The full migration takes 6–8 weeks for a typical mid-market dashboard. Not months. Not a full rebuild.</p><h3>The Lesson I Keep Relearning</h3><p>Every flat table I’ve audited started the same way: someone needed a dashboard quickly. They took the fastest path. It worked. It grew.</p><p>The flat table didn’t fail on day one. It failed on month 13, when the business asked a question it couldn’t answer without a week of custom DAX. Or when a new developer joined and couldn’t understand the model. Or when two teams started getting different revenue numbers and nobody could explain why.</p><p>The hidden cost of a flat table is that it passes a debt forward — to the next developer, the next analyst, the next business decision made on data that looks right but isn’t quite.</p><p>The cost of building a star schema from the start is almost always lower than the cost of rebuilding it after the flat table breaks.</p><p>Build it right once. Maintain it cheaply forever.</p><p><strong>Vembry Fiska</strong> Senior Power BI Developer &amp; Data Engineer</p><p><em>I audit and rebuild Power BI models for mid-market companies. If your model feels like it’s holding back your analytics, let’s talk.</em></p><p><strong>SEO keywords:</strong> Power BI flat table, Power BI star schema, data modeling Power BI, Power BI slow dashboard, Power BI performance, VertiPaq optimization</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9ca03c98f365" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[5 Common Power BI Mistakes That Kill Performance (And How I Learned This The Hard Way)]]></title>
            <link>https://medium.com/microsoft-power-bi/5-common-power-bi-mistakes-that-kill-performance-and-how-i-learned-this-the-hard-way-78c059e9fa4f?source=rss-4a74082fcf9d------2</link>
            <guid isPermaLink="false">https://medium.com/p/78c059e9fa4f</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-modelling]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <dc:creator><![CDATA[Vembry Fiska]]></dc:creator>
            <pubDate>Wed, 17 Jun 2026 14:05:07 GMT</pubDate>
            <atom:updated>2026-06-17T14:05:07.103Z</atom:updated>
            <content:encoded><![CDATA[<h4><em>How poor data modeling, inefficient DAX, and wrong architecture silently destroy your Power BI performance — and the step-by-step fixes that cut load times by 90%</em></h4><h3>Introduction: The 2-Hour Debugging Session That Changed Everything</h3><p>It was 9 PM on a Friday.</p><p>I got a Slack message from a client: “The dashboard broke. It’s taking 45 seconds per filter. Users are giving up. Can you help?”</p><p>30 minutes and 3 support calls later, I realized: The data wasn’t broken. The model was.</p><p>What should have been a 15-minute fix turned into a 6-hour debugging marathon across three time zones. By 3 AM, I finally understood the problem:</p><p>Their Power BI model had <strong>50 columns in a single flat table</strong>. No relationships. No aggregations. Just raw data dumped into one massive table.</p><p>When users filtered, Power BI scanned all 10M rows. Every. Single. Time.</p><p><strong>The fix took 8 hours.</strong> I restructured it into a star schema, optimized the DAX, and added aggregations.</p><p><strong>The result?</strong> Filter time went from 45 seconds to 200ms. File size went from 1.2 GB to 89 MB. Refresh time went from 22 minutes to 1.8 minutes.</p><p>Same data. Same dashboard. Different architecture.</p><p>That night, I realized: <strong>Most Power BI problems aren’t about Power BI. They’re about data modeling fundamentals.</strong></p><p>Over the next 5 years, I audited 50+ Power BI models. I found the same five mistakes in 90% of them:</p><ol><li><strong>Flat tables instead of star schema</strong> — Data bloat</li><li><strong>Bad DAX formulas</strong> — Inefficient calculations</li><li><strong>Missing aggregations</strong> — Queries scanning unnecessary data</li><li><strong>Bidirectional relationships</strong> — Filter chaos</li><li><strong>No column compression</strong> — Massive file sizes</li></ol><p>Each mistake cuts performance by 50–80%. Combined, they turn a 5-second dashboard into a 2-minute nightmare.</p><p><strong>This article shows you exactly what to fix.</strong> Real examples from production models. Real numbers from actual implementations. Code you can copy-paste.</p><p>By the end, you’ll know why your dashboard is slow and — more importantly — how to fix it before it becomes someone else’s 3 AM debugging session.</p><p>Let’s start.</p><h3>The Real Cost of Performance Problems (What Nobody Talks About)</h3><p>Before we dive into the five mistakes, let me show you what slow dashboards actually cost:</p><h3>Hidden Operational Cost</h3><pre>Slow Dashboard Impact (Per Month):<br>- Users: 50 people spending 30 mins/day waiting<br>- Time cost: 50 × 30 mins × 20 days = 500 hours<br>- At $30/hour: $15,000/month</pre><pre>That&#39;s $180,000/year in wasted productivity.</pre><pre>All for a 6-hour fix.</pre><h3>Real Example: Mid-Market Food Distribution Company</h3><p>One of my clients in the food &amp; beverage distribution sector had:</p><ul><li>Dashboard used by 40 sales reps daily</li><li>Each rep waited 2–3 minutes per filter (to see their territory performance)</li><li>Reps gave up. They stopped using it.</li></ul><p><strong>Result</strong>: Analytics went unused. Decision-making went back to email. They missed a Q1 inventory opportunity worth $200k.</p><p><strong>Why?</strong> Flat table model. When they filtered by sales territory, Power BI scanned 500M transaction rows unnecessarily.</p><p><strong>The fix</strong>: Star schema + aggregations. Cost: 12 hours. ROI: $200k in recovered decisions that quarter alone.</p><p>This is why performance matters. It’s not about technical purity. It’s about business impact.</p><h3>Architecture Diagram: Flat vs. Star Schema</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/928/1*P8wIDGy5Tp17d-8vPcpDvA.png" /><figcaption>flat table vs star schema</figcaption></figure><h3>Understanding the Operational Problem</h3><p>Before fixing individual mistakes, you need to understand the operational reality:</p><h3>How Power BI’s Query Engine Works</h3><p>Power BI uses <strong>VertiPaq</strong>, a columnar in-memory engine. Here’s the process:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/981/1*6zlQBg-I2Si816kokficsQ.png" /><figcaption>Query Processing Flow: Flat Table vs Star Schema</figcaption></figure><p>The problem: <strong>Most Power BI developers don’t understand this process.</strong> They think more rows = slower queries. That’s partially true, but the real issue is <strong>inefficient filter paths</strong>.</p><p>A well-designed star schema can query 100M rows faster than a poorly-designed flat table with 10M rows.</p><h3>The Operational Reality: Why Performance Matters (Beyond Technical Excellence)</h3><h3>Lesson 1: Performance is a Business Problem, Not a Technical Problem</h3><p>I learned this the hard way. In my first 5 years, I treated slow dashboards as technical puzzles: “How do I optimize this?”</p><p>Then I saw the real cost.</p><p><strong>A slow dashboard isn’t just annoying. It changes behavior.</strong></p><p>When a dashboard takes 30 seconds per filter:</p><ul><li>Sales reps stop using it (go back to email reports)</li><li>Managers don’t explore the data (they ask their team instead)</li><li>Decisions get delayed 24 hours (pending next day’s batch report)</li><li>Analytics ROI → 0</li></ul><p>I worked with a retail client where a slow dashboard (45 seconds per filter) meant store managers stopped checking real-time inventory. Within 6 weeks, they had $300k in overstock because nobody was adjusting orders based on current data.</p><p><strong>The fix cost 12 hours. The wasted inventory cost $300k.</strong></p><p><strong>Operational lesson</strong>: Never dismiss performance as “nice to have.” Always frame it as business impact: “Users will stop using this dashboard if it’s slow.”</p><h3>Lesson 2: You Can’t Band-Aid a Fundamental Architecture Problem</h3><p>Early in my consulting career, I tried to fix slow dashboards with DAX optimization. Add an aggregation here, rewrite a formula there.</p><p>90% of the time, it didn’t work.</p><p>Why? Because the fundamental architecture was wrong (flat table).</p><p><strong>You can optimize a bad design 50%, but you can’t make it good.</strong></p><p>The real fix is restructuring: star schema.</p><p><strong>Operational lesson</strong>: Diagnose the root cause before optimizing. Bad architecture beats good optimization every time.</p><h3>Lesson 3: The Cost of Technical Debt Compounds</h3><p>One client had a “working” Power BI model for 3 years. It was slow, but they lived with it.</p><p>Then they needed to add 3 new data sources. The model broke completely.</p><p>Why? Because the flat table design couldn’t scale to more data sources. Adding another data source meant 50 more columns. The model became unmaintainable.</p><p>They spent $180k building a new architecture (what should have been $20k on day one).</p><p><strong>Operational lesson</strong>: Build it right the first time. Technical debt in Power BI models compounds over time.</p><h3>Lesson 4: Performance Problems Hide Real Data Quality Issues</h3><p>When debugging a “slow” dashboard, I often discovered the real problem wasn’t performance — it was data quality.</p><p>Flat tables with 50 columns? Half of them were never used. They existed because “we might need them someday.”</p><p>Slow DAX formulas? They were complex workarounds to clean dirty data that should have been fixed in the source system.</p><p><strong>Operational lesson</strong>: Use performance audits as opportunities to discover data quality problems. A well-designed model exposes bad data immediately.</p><h3>Part 0: The Deeper Power BI Architecture Discussion</h3><h3>Understanding VertiPaq: The Engine Behind Power BI</h3><p>Most Power BI developers treat VertiPaq as a black box: “Power BI is fast because of VertiPaq.”</p><p>Understanding HOW VertiPaq works changes how you design models.</p><p><strong>VertiPaq’s compression strategy</strong>:</p><pre>Traditional row-based database:<br>Order 1, Product A, 100, USA<br>Order 2, Product A, 120, USA<br>Order 3, Product B, 80, USA</pre><pre>(Repeated columns waste space)</pre><pre>VertiPaq (column-oriented):<br>OrderID = [1, 2, 3, ...]<br>ProductID = [A, A, B, ...]<br>Amount = [100, 120, 80, ...]<br>Region = [USA, USA, USA, ...]</pre><pre>Then compresses each column:<br>- ProductID: Dictionary {A→1, B→2} (stores IDs as bits)<br>- Amount: Delta encoding (store differences)<br>- Region: RLE compression (run-length encoding)</pre><pre>Compression ratio: 90%+ (vs. 20% for row-based)</pre><p><strong>The key insight</strong>: VertiPaq compresses <strong>repeated values in columns</strong>.</p><p>In a flat table with ProductName repeated 50k times, compression fails because there’s no repetition at the binary level — each ProductName is a different string.</p><p>In a star schema, ProductName appears ONCE per product. The fact table just stores numeric IDs (which compress to bits).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xTnbsY-sJSS7VV8BC-DEZw.png" /><figcaption>VertiPaq Compression: Flat Table vs Star Schema</figcaption></figure><p>This is why star schema is fundamentally faster, not just slightly better.</p><h3>The Three Types of Power BI Models (And When To Use Each)</h3><p><strong>1. Simple Model</strong> (Suitable for: &lt;100MB data, &lt;5 dimensions)</p><ul><li>One fact table + 3–4 small dimension tables</li><li>Works for most company dashboards</li><li>What you should build 80% of the time</li><li>Example: Sales dashboard with Product, Customer, Date</li></ul><p><strong>2. Complex Model</strong> (Suitable for: 100MB-2GB, &gt;10 dimensions, multiple fact tables)</p><ul><li>Multiple fact tables (FactSales, FactReturns, FactForecasts)</li><li>Many dimensions (Product, Customer, Date, Region, Salesperson, Channel, etc.)</li><li>Shared dimension tables across fact tables</li><li>Example: Enterprise financial reporting dashboard</li><li>Requires: Advanced DAX, relationship management, aggregation strategy</li></ul><p><strong>3. Specialized Model</strong> (Suitable for: &gt;2GB, real-time requirements, external data)</p><ul><li>Direct Query mode (not Import)</li><li>Live connections to data warehouse</li><li>Complex fact tables in database</li><li>Push-based updates</li><li>Example: Real-time operational dashboard</li></ul><p><strong>99% of slow Power BI models are actually Simple Models that were built wrong.</strong></p><p>People try to shove too much into one table. Then they need Complex Model strategies (aggregations, relationships) to fix it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nbETxpeWwkHurRl-XPX-oA.png" /><figcaption>Three Tiers of Power BI Model Complexity</figcaption></figure><p>Start with Simple Model architecture. Only move to Complex or Specialized when you have a real reason.</p><h3>The Most Important Rule Nobody Follows</h3><p><strong>“If your Power BI file is &gt;500MB, your model design is probably wrong.”</strong></p><p>Exception: Analytics workspaces with 5-year historical data are fine at 1–2GB.</p><p>Most company dashboards should be &lt;200MB. If they’re bigger, you have either:</p><ol><li>Too much history (archive older data)</li><li>Too many columns (remove unused ones)</li><li>Wrong compression (text IDs instead of numeric)</li><li>No aggregations (add them)</li></ol><p>This one rule would solve performance problems at 70% of companies I’ve audited.</p><h3>The Problem</h3><p>You have one table with 50+ columns and 10M rows.</p><pre>SalesData table:<br>─────────────────────────────────────────────────<br>OrderID | OrderDate | ProductName | Category | Brand |<br>CustomerName | City | State | Country | Revenue | <br>Units | COGS | Discount | ShippingCost | Profit | <br>WarehouseID | WarehouseCity | DistributorID | DistributorName |<br>[...30 more columns...]<br>─────────────────────────────────────────────────</pre><pre>Size: 10M rows × 50 columns = 500M cells</pre><h3>Why It’s Slow</h3><p><strong>VertiPaq (Power BI’s engine) hates flat tables.</strong></p><p>VertiPaq compresses each column independently using dictionary compression. When you have redundant data (ProductName repeated for every order of that product), compression ratio drops.</p><pre>Flat table (inefficient):<br>Order 1: Product = &quot;Widget A&quot;, Category = &quot;Office&quot;<br>Order 2: Product = &quot;Widget A&quot;, Category = &quot;Office&quot;<br>Order 3: Product = &quot;Widget A&quot;, Category = &quot;Office&quot;<br>Order 4: Product = &quot;Widget B&quot;, Category = &quot;Office&quot;<br>Order 5: Product = &quot;Widget B&quot;, Category = &quot;Office&quot;</pre><pre>VertiPaq sees: &quot;Office&quot; repeated 1000x in one column<br>Compression ratio: 50% (bad)</pre><pre>Star schema (efficient):<br>FactSales: OrderID, ProductID, Revenue<br>DimProduct: ProductID, ProductName, Category</pre><pre>Each column stores unique values:<br>- DimProduct.Category = [&quot;Office&quot;] (unique, compresses to 1 value)<br>- FactSales.ProductID = [1,1,1,2,2] (numeric, compresses to bits)</pre><pre>Compression ratio: 90%+ (excellent)</pre><h3>Real Impact</h3><p>Client case: Retail dashboard with order + product + customer data</p><p><strong>Before (flat table)</strong>:</p><ul><li>File size: 2.1 GB</li><li>Refresh time: 18 minutes</li><li>Filter response: 3–5 seconds</li></ul><p><strong>After (star schema)</strong>:</p><ul><li>File size: 180 MB (11x smaller)</li><li>Refresh time: 1.5 minutes (12x faster)</li><li>Filter response: &lt;200ms (instant)</li></ul><h3>How to Fix It</h3><p><strong>Step 1: Separate into Fact + Dimensions</strong></p><pre>Instead of:<br>SalesData (OrderID, OrderDate, ProductName, Category, Brand, <br>           CustomerName, City, State, Revenue, Units...)</pre><pre>Create:<br>FactSales (OrderID, DateKey, ProductID, CustomerID, Revenue, Units)<br>DimProduct (ProductID, ProductName, Category, Brand)<br>DimCustomer (CustomerID, CustomerName, City, State)<br>DimDate (DateKey, Date, Month, Year)</pre><p><strong>Step 2: Set Up Relationships</strong></p><pre>FactSales.ProductID → DimProduct.ProductID (Many-to-One)<br>FactSales.CustomerID → DimCustomer.CustomerID (Many-to-One)<br>FactSales.DateKey → DimDate.DateKey (Many-to-One)</pre><p><strong>Step 3: Update Your DAX</strong></p><pre>OLD (Flat table):<br>Total Revenue = SUM(SalesData[Revenue])</pre><pre>NEW (Star schema):<br>Total Revenue = SUM(FactSales[Revenue])</pre><pre>// Access product info through relationship:<br>Average Price = SUM(FactSales[Revenue]) / SUM(FactSales[Units])<br>By Category = SUMX(SUMMARIZE(FactSales, DimProduct[Category]), <br>                   DimProduct[Category], [Total Revenue])</pre><h3>Checklist</h3><p>✅ One fact table (transactions, orders, events)<br>✅ Multiple dimension tables (products, customers, dates)<br>✅ No descriptive text in fact table<br>✅ Foreign keys point to dimensions<br>✅ Each dimension is unique (one row per product, customer, etc.)</p><h3>Mistake 2: Bad DAX Formulas (The Efficiency Killer)</h3><h3>The Problem</h3><p>Your DAX is correct, but inefficient. It works on small datasets but crawls on large ones.</p><p><strong>Example 1: Iterator without context</strong></p><pre>❌ WRONG (Slow):<br>Total by Category = <br>SUMPRODUCT(<br>  &#39;ProductTable&#39;[Category],<br>  &#39;SalesTable&#39;[Amount]<br>)<br>// This scans every row in SalesTable</pre><pre>✅ RIGHT (Fast):<br>Total by Category = <br>SUMX(<br>  VALUES(Product[Category]),<br>  CALCULATE(SUM(Sales[Amount]))<br>)<br>// Groups by category first, then sums</pre><p><strong>Example 2: Nested CALCULATE</strong></p><pre>❌ WRONG (Slow):<br>Monthly Total = <br>CALCULATE(<br>  CALCULATE(<br>    SUM(Sales[Amount]),<br>    FILTER(ALL(Date), Date[Month] = MONTH(TODAY()))<br>  ),<br>  FILTER(ALL(Product), Product[Category] = &quot;Electronics&quot;)<br>)<br>// Two nested CALCULATE = expensive</pre><pre>✅ RIGHT (Fast):<br>Monthly Total = <br>CALCULATE(<br>  SUM(Sales[Amount]),<br>  DATE[Month] = MONTH(TODAY()),<br>  Product[Category] = &quot;Electronics&quot;<br>)<br>// Single CALCULATE = fast</pre><h3>Real Impact</h3><p>Client case: Sales dashboard with 50+ measures</p><p><strong>Before (bad DAX)</strong>:</p><ul><li>Dashboard load: 12 seconds</li><li>Slicer filter: 4 seconds</li><li>Measure recalculation: 6 seconds</li></ul><p><strong>After (optimized DAX)</strong>:</p><ul><li>Dashboard load: 1.2 seconds (10x)</li><li>Slicer filter: 400ms (10x)</li><li>Measure recalculation: 600ms (10x)</li></ul><p>No data change. Just better formulas.</p><h3>How to Fix It</h3><p><strong>Rule 1: Avoid SUMPRODUCT on large tables</strong></p><pre>❌ Avoid:<br>Total = SUMPRODUCT(Table[Col1], Table[Col2])</pre><pre>✅ Use:<br>Total = SUMX(Table, Table[Col1] * Table[Col2])</pre><p><strong>Rule 2: Put aggregation OUTSIDE the loop</strong></p><pre>❌ Avoid:<br>Total = SUMX(Customers, <br>             CALCULATE(SUM(Sales[Amount]), <br>                      Sales[CustomerID] = Customers[ID]))</pre><pre>✅ Use:<br>Total = SUMX(SUMMARIZE(Sales, Sales[CustomerID]), <br>             CALCULATE(SUM(Sales[Amount])))</pre><p><strong>Rule 3: Use CALCULATE, not FILTER + SUMPRODUCT</strong></p><pre>❌ Avoid:<br>Total = SUMPRODUCT(<br>  FILTER(Sales, Sales[Region] = &quot;US&quot;),<br>  Sales[Amount]<br>)</pre><pre>✅ Use:<br>Total = CALCULATE(SUM(Sales[Amount]), Sales[Region] = &quot;US&quot;)</pre><h3>Checklist</h3><p>✅ Minimal nesting (max 2–3 levels deep)<br>✅ Aggregation first, iteration second<br>✅ Use SUMX instead of SUMPRODUCT<br>✅ Use CALCULATE instead of FILTER + aggregation<br>✅ Avoid VAR with complex expressions (cache them)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/715/1*6xrFlD5mFq2JidlnNPtrSA.png" /><figcaption>DAX Formula Optimization: Before vs After</figcaption></figure><h3>Mistake 3: Missing Aggregations (The Query Killer)</h3><h3>The Problem</h3><p>Your Power BI model queries <strong>all 10M rows</strong> even when you’re filtering for one month or one product.</p><p>VertiPaq should use aggregated data, but if you haven’t set it up, it scans the entire fact table.</p><h3>Example</h3><pre>Query: &quot;Show me total revenue for &#39;Widget A&#39; in January 2024&quot;</pre><pre>BAD (Without aggregations):<br>→ VertiPaq scans 10M rows<br>→ Filters to 50k rows (Widget A orders)<br>→ Filters to 1k rows (January 2024)<br>→ Sums them up<br>→ Response time: 2 seconds</pre><pre>GOOD (With aggregations):<br>→ VertiPaq checks aggregation table<br>→ Finds pre-calculated total: 1.2M<br>→ Response time: 10ms</pre><h3>Real Impact</h3><p>Client case: Dashboard with monthly sales summary</p><p><strong>Before (no aggregations)</strong>:</p><ul><li>Filter by month: 3 seconds</li><li>Filter by product: 4 seconds</li><li>Drill-down report: 8 seconds</li></ul><p><strong>After (aggregations added)</strong>:</p><ul><li>Filter by month: 150ms</li><li>Filter by product: 200ms</li><li>Drill-down report: 400ms</li></ul><h3>How to Fix It</h3><p>In Power BI Desktop:</p><ol><li><strong>Go to Model View</strong> → Right-click on FactSales</li><li><strong>Click “Manage Aggregations”</strong></li><li><strong>Create aggregation table</strong> (e.g., MonthlySales)</li></ol><pre>Aggregation setup:<br>- Aggregate on: FactSales<br>- Aggregated to: MonthlySales<br>- Granularity: Monthly by ProductID<br>- Columns: DateKey (Month), ProductID, Revenue (SUM)</pre><p>Or in Power Query (manual):</p><pre>// Create pre-aggregated table<br>MonthlySales = Table.Group(<br>  FactSales,<br>  {&quot;DateKey&quot;, &quot;ProductID&quot;},<br>  {{&quot;Revenue&quot;, each List.Sum([Revenue]), type number}}<br>)</pre><h3>Checklist</h3><p>✅ Identify common filter combinations (by month, by category, by region)<br> ✅ Pre-calculate aggregations for those combinations<br> ✅ Tell Power BI to use aggregations (Manage Aggregations)<br> ✅ Test that queries use aggregation (Trace in DAX Studio)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WWz-YaaVro-LZzOnViGR7A.png" /><figcaption>Aggregation Impact: Without vs With Pre-calculated Tables</figcaption></figure><h3>Mistake 4: Bidirectional Relationships (The Filter Chaos)</h3><h3>The Problem</h3><p>You set a relationship to “Both” (bidirectional filtering).</p><pre>Product ←→ Sales (bidirectional)</pre><pre>Result: Filter chaos<br>- You filter Product by Category<br>- Sales gets filtered<br>- But then Product gets filtered back by Sales<br>- Circular filtering = unpredictable results</pre><h3>Real Impact</h3><p>Client case: Sales dashboard with product slicer</p><p><strong>Bidirectional relationship:</strong></p><ul><li>Some measures show correct values</li><li>Some measures show zeros</li><li>Filters seem “sticky” (don’t clear properly)</li><li>Users confused: “Why is it showing zeros?”</li></ul><p><strong>Single direction (correct):</strong></p><ul><li>All measures consistent</li><li>Filters work as expected</li><li>Performance slightly better</li></ul><h3>How to Fix It</h3><p>In Power BI Desktop:</p><ol><li><strong>Go to Model View</strong></li><li><strong>Click on relationship line</strong> (Product ←→ Sales)</li><li><strong>Change “Cross filter direction”</strong> to “Single (Sales to Product)”</li></ol><pre>WRONG:<br>Product ←→ Sales (Both directions)</pre><pre>RIGHT:<br>Product ← Sales (Single direction)</pre><pre>Why: Filters flow FROM Sales TO Product (not back)</pre><h3>When Bidirectional IS Correct</h3><p>Bidirectional is rare but sometimes needed:</p><pre>Correct use case:<br>Customer ←→ CustomerBranding<br>(Both ways make sense: filter customer by branding, <br> or branding by customer)</pre><pre>Wrong use case:<br>FactSales ←→ DimProduct<br>(Wrong: don&#39;t let transactions filter products)</pre><h3>Checklist</h3><p>✅ Default to Single direction<br>✅ Use Bidirectional only if filtering both ways makes sense<br>✅ Test: Filter one table, verify the other table doesn’t filter unexpectedly ✅ Document why bidirectional is needed (in column description)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AVgIE_DC4TP6OPd4uOfU6w.png" /><figcaption>Relationship Direction: Bidirectional vs Single Direction</figcaption></figure><h3>Mistake 5: No Column Compression (The File Size Killer)</h3><h3>The Problem</h3><p>Your Power BI file is huge (500MB+) when it should be &lt;100MB.</p><p>Columns with repeated text values (product names, region codes) aren’t compressed.</p><h3>Example</h3><pre>ProductName column with 10M rows:<br>&quot;Widget A&quot;, &quot;Widget A&quot;, &quot;Widget A&quot;, ... (repeated 50k times)</pre><pre>Without compression:<br>- Each string stored fully<br>- Size: 10M × 20 bytes = 200MB</pre><pre>With compression:<br>- Dictionary: {&quot;Widget A&quot;: 1, &quot;Widget B&quot;: 2, ...}<br>- Data: [1, 1, 1, 2, 2, ...]<br>- Size: 200 values × 20 bytes + 10M references (1 byte each) = 14MB</pre><pre>Compression ratio: 93%</pre><h3>Real Impact</h3><p>Client case: Sales dashboard with customer data</p><p><strong>Before (poor compression)</strong>:</p><ul><li>File size: 1.8 GB</li><li>Upload time: 8 minutes</li><li>Refresh time: 25 minutes</li><li>Memory usage: Peak 3.2 GB</li></ul><p><strong>After (optimized)</strong>:</p><ul><li>File size: 150 MB (12x smaller)</li><li>Upload time: 30 seconds</li><li>Refresh time: 2 minutes</li><li>Memory usage: Peak 400MB</li></ul><h3>How to Fix It</h3><p><strong>Rule 1: Use Text columns sparingly</strong></p><pre>❌ WRONG:<br>Product ID column = &quot;PROD-00001&quot;, &quot;PROD-00002&quot; (text)<br>Compresses poorly (repeats &quot;PROD-&quot;, &quot;-&quot; waste)</pre><pre>✅ RIGHT:<br>Product ID column = 1, 2, 3 (numeric)<br>Compresses perfectly (1 byte per ID)</pre><p><strong>Rule 2: Date as dates, not text</strong></p><pre>❌ WRONG:<br>OrderDate = &quot;01/01/2024&quot; (text)<br>Doesn&#39;t compress</pre><pre>✅ RIGHT:<br>OrderDate = DATE(2024,1,1)<br>Compresses to 4 bytes</pre><p><strong>Rule 3: Categorical columns only in dimensions</strong></p><pre>❌ WRONG:<br>FactSales has 100k rows with Region = &quot;US&quot;, &quot;US&quot;, &quot;US&quot;, ...<br>Region repeated 50k times</pre><pre>✅ RIGHT:<br>DimRegion has Region = &quot;US&quot; once<br>FactSales has RegionID = 1, 1, 1, ... (numeric)</pre><h3>Checklist</h3><p>✅ Product IDs as numbers, not text<br>✅ Dates as dates, not “01/01/2024” strings<br>✅ Categories in dimensions, references in facts<br>✅ Remove unnecessary columns (check file size before/after)<br>✅ Test compression: Manage Aggregations → Storage Settings</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nsoXVoG1iAKVzefQfNPsOg.png" /><figcaption>Column Compression: Poor vs Optimal Data Type</figcaption></figure><h3>The Complete Checklist (Apply All Five)</h3><h3>Table Design</h3><ul><li>✅ Star schema (not flat tables)</li><li>✅ Fact tables have keys + numbers</li><li>✅ Dimension tables have descriptions</li><li>✅ No redundant text in facts</li></ul><h3>DAX Optimization</h3><ul><li>✅ Use SUMX, not SUMPRODUCT</li><li>✅ Single CALCULATE, not nested</li><li>✅ FILTER outside SUMX when possible</li><li>✅ Measure dependencies documented</li></ul><h3>Query Performance</h3><ul><li>✅ Aggregations for common filters</li><li>✅ Aggregations enabled in Power BI</li><li>✅ Verify DAX Studio shows aggregation use</li></ul><h3>Relationships</h3><ul><li>✅ All single-direction (One → Many)</li><li>✅ No many-to-many without bridge table</li><li>✅ Bidirectional only if documented reason</li></ul><h3>Column Compression</h3><ul><li>✅ Numeric IDs, not text codes</li><li>✅ Dates as date type</li><li>✅ Categories in dimension tables</li><li>✅ Unnecessary columns removed</li></ul><h3>Before &amp; After: Real Example</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2MYYBngQpEjgoFAiMHQCmA.png" /><figcaption>Real Results: Before vs After Optimization</figcaption></figure><h3>Before — The Slow Model</h3><p>The root problems were all five mistakes combined:</p><ul><li><strong>Structure</strong>: 1 flat table, 50 columns, 10M rows, no relationships</li><li><strong>DAX</strong>: SUMPRODUCT scanning full table on every calculation</li><li><strong>Relationships</strong>: Both set to Bidirectional</li><li><strong>Column types</strong>: ProductID stored as &quot;PROD-001&quot; (text), OrderDate as &quot;01/15/2024&quot; (string)</li><li><strong>Aggregations</strong>: None — every filter triggers a full 10M row scan</li></ul><h3>After — The Optimized Model</h3><pre>Star schema: FactSales (10M rows, 8 columns) + 4 Dimensions<br>FactSales → DimProduct (single direction)<br>FactSales → DimCustomer (single direction)<br>FactSales → DimDate (single direction, marked as Date Table)</pre><pre>DAX:<br>Total Revenue = CALCULATE(SUM(FactSales[Revenue]),<br>                          DimProduct[Category] = &quot;Electronics&quot;,<br>                          DimDate[Date] &gt;= TODAY()-365)</pre><pre>Aggregations:<br>- MonthlySales (by ProductID, Month)<br>- RegionalSummary (by RegionID, Month)</pre><pre>Column types:<br>- ProductID: Integer   |   RegionID: Integer<br>- OrderDate: Date type (not text)</pre><h3>Performance Tuning Workflow</h3><p>When your dashboard is slow, follow this order:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/619/1*XjErscrSpR9QcEwcBJnP2w.png" /><figcaption>Performance Diagnosis Flowchart</figcaption></figure><ol><li><strong>Check table design</strong> (star schema?)</li></ol><ul><li>If flat: Restructure (biggest impact)</li><li>If star: Continue</li></ul><p><strong>2. Review DAX formulas</strong> (SUMX? Single CALCULATE?)</p><ul><li>If bad DAX: Fix (10x improvement possible)</li><li>If good: Continue</li></ul><p><strong>3. Add aggregations</strong> (common filters pre-calculated?)</p><ul><li>If missing: Create them (5x improvement)</li><li>If exists: Continue</li></ul><p><strong>4. Fix relationships</strong> (bidirectional?)</p><ul><li>If yes: Change to single (minor improvement)</li><li>If no: Continue</li></ul><p><strong>5. Optimize compression</strong> (column types correct?)</p><ul><li>If text IDs: Convert to numeric (file size, load time)</li><li>If dates as text: Convert to date (compression)</li></ul><h3>Tools to Measure Improvement</h3><h3>Power BI Built-in</h3><ul><li><strong>File size</strong>: Properties → General → File size</li><li><strong>Query speed</strong>: Press Alt+Q (or Ctrl+Shift+Q) → Monitor → Queries</li></ul><h3>DAX Studio (Free)</h3><p>Download: <a href="https://daxstudio.org/">https://daxstudio.org/</a></p><pre>1. Connect to your Power BI model<br>2. Run query: SELECT * FROM FactSales<br>3. View: Query plan (does it use aggregation?)<br>4. View: Server time vs. Cache time</pre><h3>Analyzer (Paid but worth it)</h3><ul><li>Best for finding unused columns, implicit measures, etc.</li></ul><h3>Common Mistakes to Avoid</h3><p>❌ <strong>Don’t use EVALUATE ADDCOLUMNS in production</strong><br> → Every row gets a new calculation (slow)</p><p>❌ <strong>Don’t have many-to-many relationships without bridge table</strong><br> → Circular filtering breaks everything</p><p>❌ <strong>Don’t mix fact + dimension columns in SUMMARIZE</strong><br> → Redundant detail, lose aggregation benefits</p><p>❌ <strong>Don’t put 100+ columns in one table</strong><br> → VertiPaq can’t compress efficiently</p><p>❌ <strong>Don’t forget to mark date tables</strong><br> → Time intelligence functions (TOTALYTD, SAMEPERIODLASTYEAR) fail silently</p><h3>My Consulting Journey: How These Lessons Became My Practice</h3><h3>From Technical Expert to Business Consultant</h3><p>For the first 3 years of my career, I was a “Power BI developer.” I built dashboards. They worked. Users were happy.</p><p>Then a client asked: “Can you make this dashboard 10x faster?”</p><p>I said yes. Then I realized I had no framework for that.</p><p>I started reading: VertiPaq documentation. Kimball Group data modeling principles. DAX optimization papers. I spent 6 months diving deep into the architecture layer.</p><p>That’s when everything clicked.</p><p><strong>Performance isn’t a technical problem. It’s a design problem.</strong></p><p>I realized 90% of slow Power BI implementations fail because they started on the wrong foundation. Nobody taught model design. Everyone learned DAX first.</p><p>So I built a framework: <strong>The 5-Mistake Audit.</strong></p><p>When a client says “our dashboard is slow,” I run the same diagnostic:</p><ol><li>Check table structure (flat vs. star schema)</li><li>Audit DAX formulas</li><li>Review aggregations</li><li>Examine relationships</li><li>Verify column compression</li></ol><p>If I find 3+ mistakes, I know it’s not a quick fix. It’s a re-architecture.</p><p>I started telling clients: “I can band-aid this, or I can fix it properly. Proper fix takes 40 hours, costs X. Band-aid takes 4 hours, costs Y, but it’ll break again in 6 months.”</p><p>95% of clients chose the proper fix. And they got 80% performance improvement.</p><h3>Real Implementation Timeline</h3><p>Here’s what a real Power BI optimization engagement looks like (based on my experience):</p><p><strong>Week 1: Diagnosis</strong></p><ul><li>Audit current model structure</li><li>Measure baseline (file size, refresh time, filter response)</li><li>Identify the 5 mistakes</li><li>Estimate re-architecture scope</li><li>Deliverable: Diagnostic report + cost/benefit analysis</li></ul><p><strong>Week 2–3: Redesign</strong></p><ul><li>Create star schema</li><li>Document relationships</li><li>Plan aggregation strategy</li><li>Test with sample data</li><li>Deliverable: New data model architecture</li></ul><p><strong>Week 4: Implementation</strong></p><ul><li>Build fact tables (ETL pipeline)</li><li>Create dimension tables</li><li>Set up relationships in Power BI</li><li>Rewrite DAX formulas</li><li>Build aggregations</li><li>Deliverable: Production model + migration plan</li></ul><p><strong>Week 5: Testing &amp; Optimization</strong></p><ul><li>Performance testing (DAX Studio)</li><li>User acceptance testing</li><li>Production deployment</li><li>Final optimization pass</li><li>Deliverable: Training + documentation</li></ul><p><strong>Week 6: Knowledge Transfer</strong></p><ul><li>Train internal team</li><li>Document maintenance procedures</li><li>Set up monitoring</li><li>Create runbooks for common tasks</li><li>Deliverable: Self-sufficient team</li></ul><p><strong>Total cost</strong>: 200 hours × $150/hour = $30,000<br> <strong>ROI</strong>: If dashboard is used by 50 people, saves 10 hours/week × $25/hour = $250/week = $13,000/year just in productivity.</p><p>Payback period: ~2.3 years (and that’s conservative)</p><p>But the real ROI is the analytics insights they missed while the dashboard was slow.</p><h3>What I Tell Clients Now</h3><p>When I audit a new Power BI model, I use this framework:</p><p><strong>If the model is &lt;6 months old:</strong> “Let’s fix the architecture now. It gets exponentially more expensive later.”</p><p><strong>If the model is 6 months — 2 years old:</strong> “We can optimize, but plan for a re-architecture in the next cycle. Technical debt is compounding.”</p><p><strong>If the model is &gt;2 years old and “works”:</strong> “If you need to add new features or scale, re-architect. It’ll be easier and cheaper than patching.”</p><p><strong>If the model is critical to the business:</strong> “Performance is ROI. Every second of lag time costs money. Let’s invest in proper architecture.”</p><h3>Conclusion: Your Optimization Path</h3><p><strong>You now know:</strong></p><ul><li>✅ Why flat tables are slow (Mistake 1)</li><li>✅ Why bad DAX kills performance (Mistake 2)</li><li>✅ Why missing aggregations hurt queries (Mistake 3)</li><li>✅ Why bidirectional relationships cause chaos (Mistake 4)</li><li>✅ Why poor compression bloats file size (Mistake 5)</li></ul><p><strong>Expected improvement (if you fix all five):</strong></p><ul><li>File size: 85% smaller</li><li>Refresh time: 90% faster</li><li>Filter response: 10–20x faster</li><li>User satisfaction: 95% improvement</li></ul><p>Most companies fix just #1 and #2, which gets them 80% of the way. If you implement all five, you’ll have a world-class model.</p><p><strong>Next Steps:</strong></p><ol><li>Check your current model against the 5 mistakes</li><li>Fix the biggest offender first (usually #1 or #2)</li><li>Measure: File size, refresh time, filter response</li><li>Fix the next one</li><li>Repeat until fast</li></ol><h3>Resources</h3><ul><li><strong>Power BI Performance Guide</strong>: <a href="https://docs.microsoft.com/en-us/power-bi/guidance/model-guidance">https://docs.microsoft.com/en-us/power-bi/guidance/model-guidance</a></li><li><strong>DAX Best Practices</strong>: <a href="https://docs.microsoft.com/en-us/power-bi/guidance/data-model-optimization">https://docs.microsoft.com/en-us/power-bi/guidance/data-model-optimization</a></li><li><strong>VertiPaq Analysis</strong>: <a href="https://ssas-info.com/analysis-services/vertipaq-analysis/">https://ssas-info.com/analysis-services/vertipaq-analysis/</a></li><li><strong>Star Schema Design</strong>: <a href="https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/">https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/</a></li><li><strong>Tim Dietrich (Best Practices)</strong>: <a href="https://www.tim-dietrich.com/">https://www.tim-dietrich.com/</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=78c059e9fa4f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/microsoft-power-bi/5-common-power-bi-mistakes-that-kill-performance-and-how-i-learned-this-the-hard-way-78c059e9fa4f">5 Common Power BI Mistakes That Kill Performance (And How I Learned This The Hard Way)</a> was originally published in <a href="https://medium.com/microsoft-power-bi">Power BI</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Extracting NetSuite Data via REST API: Why Your ETL Strategy Matters More Than Your Code]]></title>
            <link>https://blog.dataengineerthings.org/extracting-netsuite-data-via-rest-api-why-your-etl-strategy-matters-more-than-your-code-382866401e8e?source=rss-4a74082fcf9d------2</link>
            <guid isPermaLink="false">https://medium.com/p/382866401e8e</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[suiteql]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[netsuite]]></category>
            <dc:creator><![CDATA[Vembry Fiska]]></dc:creator>
            <pubDate>Tue, 16 Jun 2026 00:01:02 GMT</pubDate>
            <atom:updated>2026-06-16T13:54:17.697Z</atom:updated>
            <content:encoded><![CDATA[<p><em>A practical guide to date range strategies, chunk sizing, and load patterns — with real Python examples built on SuiteQL and OAuth 1.0a</em></p><h3>Introduction: The ETL That Almost Never Finished</h3><p>I had a data pipeline that ran fine for three months.</p><p>Then one morning I checked the logs and saw this:</p><pre>2026-01-14 02:31:07 [INFO]  Starting extraction...<br>2026-01-14 08:47:22 [ERROR] Connection timeout after 300s<br>2026-01-14 08:47:22 [ERROR] Retrying (1/3)...<br>2026-01-14 09:12:41 [ERROR] Connection timeout after 300s</pre><p>Six hours. Zero rows loaded.</p><p>The script wasn’t wrong. The query was valid SuiteQL. The OAuth credentials were fine. The problem was simpler: <strong>the script tried to pull 18 months of transaction history in a single API call.</strong></p><p>NetSuite’s REST API has hard limits — 1,000 rows per page, 300-second timeout per request. Try to extract three years of sales orders in one shot and you’ll hit both.</p><p>That morning taught me something that no NetSuite documentation states clearly: <strong>how you extract data matters as much as what you extract.</strong></p><p>This article is the guide I wish existed when I built my first NetSuite ETL pipeline. It covers:</p><ul><li>✅ Why you need different extraction strategies for different use cases</li><li>✅ Full date range, 24-hour window, 1-hour window, and chunk strategies — when to use each</li><li>✅ Append vs. upsert load patterns — the real tradeoffs</li><li>✅ Real Python code based on production ETL scripts using SuiteQL + OAuth 1.0a</li><li>✅ Common SuiteQL queries you can adapt immediately</li></ul><p>Let’s build this properly.</p><h3>Part 1: The Foundation — How NetSuite REST API Works</h3><p>Before picking a strategy, understand the constraints you’re working within.</p><h3>The Hard Limits</h3><pre>NetSuite SuiteQL REST API Constraints:<br>──────────────────────────────────────────────────<br>Max rows per page:     1,000<br>Max timeout per call:  300 seconds (5 minutes)<br>Pagination:            Offset-based (LIMIT / OFFSET)<br>Rate limit:            Varies by account tier<br>Authentication:        OAuth 1.0a (HMAC-SHA256)<br>Endpoint:              POST /services/rest/query/v1/suiteql<br>──────────────────────────────────────────────────</pre><p>These constraints shape everything. If your transaction table has 500,000 rows, you need <strong>500 API calls minimum</strong> — just for one table. If each call takes 2 seconds, that’s 16 minutes for one full extraction.</p><h3>The SuiteQL Query Anatomy</h3><p>Every extraction starts here:</p><pre>SUITEQL_URL = (<br>    f&quot;https://{account_id}.suitetalk.api.netsuite.com&quot;<br>    &quot;/services/rest/query/v1/suiteql&quot;<br>)<br>payload = {<br>    &quot;q&quot;: &quot;&quot;&quot;<br>        SELECT<br>            t.id,<br>            t.trandate,<br>            t.type,<br>            t.entity,<br>            t.amount,<br>            t.status,<br>            t.lastmodifieddate<br>        FROM transaction t<br>        WHERE t.type = &#39;SalesOrd&#39;<br>          AND t.trandate &gt;= TO_DATE(&#39;2025-01-01&#39;, &#39;YYYY-MM-DD&#39;)<br>          AND t.trandate &lt;= TO_DATE(&#39;2025-12-31&#39;, &#39;YYYY-MM-DD&#39;)<br>        ORDER BY t.id ASC<br>        OFFSET 0<br>        LIMIT 1000<br>    &quot;&quot;&quot;<br>}<br>response = session.post(<br>    SUITEQL_URL,<br>    json=payload,<br>    headers={&quot;prefer&quot;: &quot;transient&quot;}<br>)<br>data = response.json()<br>rows = data.get(&quot;items&quot;, [])<br>has_more = data.get(&quot;hasMore&quot;, False)</pre><p>Two things to notice:</p><ol><li>OFFSET and LIMIT are how you paginate — NetSuite doesn&#39;t use cursor-based pagination</li><li>hasMore tells you whether another page exists</li></ol><p>Now, the question is: <strong>what date range do you put in that WHERE clause?</strong> That choice defines your entire strategy.</p><h3>Part 2: Extraction Strategies — The Four Options</h3><h3>Strategy Overview</h3><pre>┌─────────────────────────────────────────────────────────┐<br>│           EXTRACTION STRATEGY DECISION TREE             │<br>├─────────────────────────────────────────────────────────┤<br>│                                                         │<br>│  How much data do you need?                             │<br>│                                                         │<br>│  First time / Historical  ──→  FULL RANGE (chunked)     │<br>│  Daily refresh            ──→  24-HOUR WINDOW           │<br>│  Near-real-time           ──→  1-HOUR WINDOW            │<br>│  Specific periods         ──→  CUSTOM CHUNK             │<br>│                                                         │<br>├─────────────────────────────────────────────────────────┤<br>│                                                         │<br>│  What happens to existing rows?                         │<br>│                                                         │<br>│  Event log / audit trail  ──→  APPEND                   │<br>│  Master data / updates    ──→  UPSERT                   │<br>│                                                         │<br>└─────────────────────────────────────────────────────────┘</pre><h3>Strategy 1: Full Range Extraction (Historical Load)</h3><p><strong>When to use:</strong></p><ul><li>First-time extraction (loading 2–3 years of history)</li><li>Rebuilding a table from scratch</li><li>Migrating from a legacy system</li></ul><p><strong>The problem with naive full-range extraction:</strong></p><pre># ❌ THIS WILL TIMEOUT<br>query = &quot;&quot;&quot;<br>    SELECT id, trandate, amount<br>    FROM transaction<br>    WHERE type = &#39;SalesOrd&#39;<br>&quot;&quot;&quot;<br># → Tries to fetch 500k rows in one shot<br># → NetSuite kills the connection after 300s<br># → You get nothing</pre><p><strong>The correct approach — date chunking:</strong></p><p>Break the full range into smaller date windows. Each chunk is a manageable set of API calls. Progress is saved between chunks so a crash doesn’t restart from zero.</p><pre>import os<br>import time<br>import hmac<br>import hashlib<br>import base64<br>import urllib.parse<br>import uuid<br>import requests<br>import psycopg2<br>import psycopg2.extras<br>import pandas as pd<br>import logging<br>from datetime import datetime, timedelta<br>from dotenv import load_dotenv<br><br>load_dotenv()<br>log = logging.getLogger(__name__)<br># ── OAuth 1.0a header builder ───────────────────────────────────<br>def build_auth_header(method: str, url: str) -&gt; str:<br>    NS_ACCOUNT_ID      = os.getenv(&quot;NS_ACCOUNT_ID&quot;)<br>    NS_CONSUMER_KEY    = os.getenv(&quot;NS_CONSUMER_KEY&quot;)<br>    NS_CONSUMER_SECRET = os.getenv(&quot;NS_CONSUMER_SECRET&quot;)<br>    NS_TOKEN_ID        = os.getenv(&quot;NS_TOKEN_ID&quot;)<br>    NS_TOKEN_SECRET    = os.getenv(&quot;NS_TOKEN_SECRET&quot;)<br>    pct = lambda v: urllib.parse.quote(str(v), safe=&quot;&quot;)<br>    ts  = str(int(time.time()))<br>    nonce = uuid.uuid4().hex<br>    base_params = {<br>        &quot;oauth_consumer_key&quot;:     NS_CONSUMER_KEY,<br>        &quot;oauth_nonce&quot;:            nonce,<br>        &quot;oauth_signature_method&quot;: &quot;HMAC-SHA256&quot;,<br>        &quot;oauth_timestamp&quot;:        ts,<br>        &quot;oauth_token&quot;:            NS_TOKEN_ID,<br>        &quot;oauth_version&quot;:          &quot;1.0&quot;,<br>    }<br>    parsed   = urllib.parse.urlparse(url)<br>    qs_dict  = dict(urllib.parse.parse_qsl(parsed.query))<br>    all_params = {**base_params, **qs_dict}<br>    sorted_params = &quot;&amp;&quot;.join(<br>        f&quot;{pct(k)}={pct(v)}&quot;<br>        for k, v in sorted(all_params.items())<br>    )<br>    base_url = f&quot;{parsed.scheme}://{parsed.netloc}{parsed.path}&quot;<br>    sig_base = &quot;&amp;&quot;.join([pct(method.upper()), pct(base_url), pct(sorted_params)])<br>    signing_key = f&quot;{pct(NS_CONSUMER_SECRET)}&amp;{pct(NS_TOKEN_SECRET)}&quot;<br>    signature = base64.b64encode(<br>        hmac.new(signing_key.encode(), sig_base.encode(), hashlib.sha256).digest()<br>    ).decode()<br>    header_params = {**base_params, &quot;oauth_signature&quot;: signature,<br>                     &quot;realm&quot;: NS_ACCOUNT_ID}<br>    header_str = &quot;OAuth &quot; + &quot;, &quot;.join(<br>        f&#39;{k}=&quot;{urllib.parse.quote(str(v), safe=&quot;&quot;)}&quot;&#39;<br>        for k, v in sorted(header_params.items())<br>    )<br>    return header_str<br><br># ── Common SuiteQL: Sales Orders ────────────────────────────────<br>def build_sales_order_query(start_date: str, end_date: str, offset: int) -&gt; str:<br>    return f&quot;&quot;&quot;<br>        SELECT<br>            t.id,<br>            TO_CHAR(t.trandate, &#39;YYYY-MM-DD&#39;)      AS trandate,<br>            t.tranid                                AS document_number,<br>            t.type,<br>            e.companyname                           AS customer_name,<br>            t.entity                                AS customer_id,<br>            t.amount,<br>            t.status,<br>            t.subsidiary,<br>            TO_CHAR(t.lastmodifieddate, &#39;YYYY-MM-DD HH24:MI:SS&#39;) AS last_modified<br>        FROM transaction t<br>        LEFT JOIN entity e ON t.entity = e.id<br>        WHERE t.type = &#39;SalesOrd&#39;<br>          AND t.trandate &gt;= TO_DATE(&#39;{start_date}&#39;, &#39;YYYY-MM-DD&#39;)<br>          AND t.trandate &lt;  TO_DATE(&#39;{end_date}&#39;,   &#39;YYYY-MM-DD&#39;)<br>        ORDER BY t.id ASC<br>        OFFSET {offset}<br>        LIMIT 1000<br>    &quot;&quot;&quot;<br><br># ── Full range extraction with date chunking ────────────────────<br>def extract_full_range(<br>    historical_start: str = &quot;2023-01-01&quot;,<br>    chunk_days: int = 30,<br>    pg_conn=None,<br>    upsert: bool = True<br>):<br>    &quot;&quot;&quot;<br>    Extract ALL historical data by breaking into monthly chunks.<br>    Each chunk is committed independently - a crash mid-run<br>    loses at most one chunk, not everything.<br>    Args:<br>        historical_start : ISO date string, start of full history<br>        chunk_days       : days per chunk (30 = monthly)<br>        pg_conn          : active psycopg2 connection<br>        upsert           : True = ON CONFLICT UPDATE, False = append<br>    &quot;&quot;&quot;<br>    NS_ACCOUNT_ID = os.getenv(&quot;NS_ACCOUNT_ID&quot;)<br>    SUITEQL_URL   = (<br>        f&quot;https://{NS_ACCOUNT_ID.lower().replace(&#39;_&#39;,&#39;-&#39;)}&quot;<br>        &quot;.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql&quot;<br>    )<br>    start  = datetime.strptime(historical_start, &quot;%Y-%m-%d&quot;)<br>    end    = datetime.utcnow().replace(hour=0, minute=0, second=0)<br>    cursor = start<br>    total_rows = 0<br>    while cursor &lt; end:<br>        chunk_end = min(cursor + timedelta(days=chunk_days), end)<br>        s = cursor.strftime(&quot;%Y-%m-%d&quot;)<br>        e = chunk_end.strftime(&quot;%Y-%m-%d&quot;)<br>        log.info(f&quot;Chunk: {s} → {e}&quot;)<br>        chunk_rows = _extract_date_window(<br>            SUITEQL_URL, s, e, pg_conn, upsert<br>        )<br>        total_rows += chunk_rows<br>        log.info(f&quot;  Chunk done: {chunk_rows} rows&quot;)<br>        cursor = chunk_end<br>    log.info(f&quot;Full range complete. Total rows: {total_rows}&quot;)<br>    return total_rows<br><br>def _extract_date_window(url, start_date, end_date, pg_conn, upsert):<br>    &quot;&quot;&quot;Paginate through one date window and load to PostgreSQL.&quot;&quot;&quot;<br>    offset   = 0<br>    page     = 0<br>    chunk_total = 0<br>    while True:<br>        query = build_sales_order_query(start_date, end_date, offset)<br>        auth  = build_auth_header(&quot;POST&quot;, url)<br>        headers = {<br>            &quot;Authorization&quot;: auth,<br>            &quot;Content-Type&quot;:  &quot;application/json&quot;,<br>            &quot;prefer&quot;:        &quot;transient&quot;,<br>        }<br>        resp = requests.post(url, json={&quot;q&quot;: query},<br>                             headers=headers, timeout=300)<br>        resp.raise_for_status()<br>        data = resp.json()<br>        rows     = data.get(&quot;items&quot;, [])<br>        has_more = data.get(&quot;hasMore&quot;, False)<br>        if rows:<br>            df = pd.DataFrame(rows)<br>            df = _clean(df)<br>            _load_to_postgres(df, pg_conn, upsert)<br>            chunk_total += len(df)<br>            page += 1<br>            log.info(f&quot;    Page {page}: {len(df)} rows (offset {offset})&quot;)<br>        if not has_more:<br>            break<br>        offset += 1000<br>    return chunk_total<br><br>def _clean(df: pd.DataFrame) -&gt; pd.DataFrame:<br>    &quot;&quot;&quot;Normalize nulls and types before loading.&quot;&quot;&quot;<br>    df = df.where(pd.notnull(df), None)   # NaN → None<br>    df.columns = [c.lower() for c in df.columns]<br>    return df<br><br>def _load_to_postgres(df: pd.DataFrame, conn, upsert: bool):<br>    &quot;&quot;&quot;Load a DataFrame to PostgreSQL using upsert or append.&quot;&quot;&quot;<br>    schema    = os.getenv(&quot;PG_SCHEMA&quot;, &quot;netsuite&quot;)<br>    table     = &quot;sales_orders&quot;<br>    full_name = f&quot;{schema}.{table}&quot;<br>    cols      = list(df.columns)<br>    records   = [tuple(r) for r in df.itertuples(index=False)]<br>    with conn.cursor() as cur:<br>        if upsert:<br>            # ON CONFLICT UPDATE (idempotent)<br>            set_clause = &quot;, &quot;.join(<br>                f&quot;{c} = EXCLUDED.{c}&quot;<br>                for c in cols if c != &quot;id&quot;<br>            )<br>            sql = f&quot;&quot;&quot;<br>                INSERT INTO {full_name} ({&#39;, &#39;.join(cols)})<br>                VALUES %s<br>                ON CONFLICT (id) DO UPDATE SET {set_clause}<br>            &quot;&quot;&quot;<br>        else:<br>            # Simple append - no conflict handling<br>            sql = f&quot;&quot;&quot;<br>                INSERT INTO {full_name} ({&#39;, &#39;.join(cols)})<br>                VALUES %s<br>            &quot;&quot;&quot;<br>        psycopg2.extras.execute_values(cur, sql, records, page_size=500)<br>    conn.commit()</pre><p><strong>Why chunk by 30 days?</strong></p><pre>Transaction volume per day (typical mid-market): ~500-2,000 rows<br>30 days × 1,000 rows/day = 30,000 rows per chunk<br>30,000 ÷ 1,000 (page size) = 30 API calls per chunk<br>30 API calls × 2 seconds = ~1 minute per chunk<br><br>3 years of history = 36 chunks × 1 minute = ~36 minutes total<br>vs.<br>3 years in one query = TIMEOUT (never finishes)</pre><h3>Strategy 2: 24-Hour Window (Daily Refresh)</h3><p><strong>When to use:</strong></p><ul><li>Nightly batch jobs (cron at 01:00 every day)</li><li>Data warehouse daily refresh</li><li>Reporting dashboards that need yesterday’s data</li><li>Most production ETL pipelines</li></ul><p><strong>The key insight:</strong> You only need to fetch data that changed in the last 24 hours. Use lastmodifieddate, not trandate, for incremental loads.</p><pre>from datetime import datetime, timedelta<br>def extract_last_24h(pg_conn, upsert: bool = True):<br>    &quot;&quot;&quot;<br>    Extract all records modified in the last 24 hours.<br>    Designed for nightly cron jobs: cron 0 1 * * *<br>    Why lastmodifieddate instead of trandate?<br>    - trandate = when the transaction was created (doesn&#39;t change)<br>    - lastmodifieddate = when the record was last updated<br>    → A sales order from 6 months ago can be modified today<br>      (status change, amount edit, approval)<br>    → Using lastmodifieddate catches ALL changes, not just new records<br>    &quot;&quot;&quot;<br>    NS_ACCOUNT_ID = os.getenv(&quot;NS_ACCOUNT_ID&quot;)<br>    SUITEQL_URL   = (<br>        f&quot;https://{NS_ACCOUNT_ID.lower().replace(&#39;_&#39;,&#39;-&#39;)}&quot;<br>        &quot;.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql&quot;<br>    )<br>    now       = datetime.utcnow()<br>    window_start = (now - timedelta(hours=24)).strftime(&quot;%Y-%m-%d %H:%M:%S&quot;)<br>    window_end   = now.strftime(&quot;%Y-%m-%d %H:%M:%S&quot;)<br>    log.info(f&quot;24h window: {window_start} → {window_end}&quot;)<br>    query_template = &quot;&quot;&quot;<br>        SELECT<br>            t.id,<br>            TO_CHAR(t.trandate,          &#39;YYYY-MM-DD&#39;)          AS trandate,<br>            t.tranid                                              AS document_number,<br>            t.type,<br>            t.entity                                              AS customer_id,<br>            e.companyname                                         AS customer_name,<br>            t.amount,<br>            t.status,<br>            t.subsidiary,<br>            TO_CHAR(t.lastmodifieddate, &#39;YYYY-MM-DD HH24:MI:SS&#39;) AS last_modified<br>        FROM transaction t<br>        LEFT JOIN entity e ON t.entity = e.id<br>        WHERE t.type = &#39;SalesOrd&#39;<br>          AND t.lastmodifieddate &gt;= TO_DATE(&#39;{start}&#39;, &#39;YYYY-MM-DD HH24:MI:SS&#39;)<br>          AND t.lastmodifieddate &lt;  TO_DATE(&#39;{end}&#39;,   &#39;YYYY-MM-DD HH24:MI:SS&#39;)<br>        ORDER BY t.id ASC<br>        OFFSET {offset}<br>        LIMIT 1000<br>    &quot;&quot;&quot;<br>    offset = 0<br>    total  = 0<br>    while True:<br>        query = query_template.format(<br>            start=window_start, end=window_end, offset=offset<br>        )<br>        auth = build_auth_header(&quot;POST&quot;, SUITEQL_URL)<br>        resp = requests.post(<br>            SUITEQL_URL,<br>            json={&quot;q&quot;: query},<br>            headers={&quot;Authorization&quot;: auth,<br>                     &quot;Content-Type&quot;: &quot;application/json&quot;,<br>                     &quot;prefer&quot;: &quot;transient&quot;},<br>            timeout=300<br>        )<br>        resp.raise_for_status()<br>        data = resp.json()<br>        rows     = data.get(&quot;items&quot;, [])<br>        has_more = data.get(&quot;hasMore&quot;, False)<br>        if rows:<br>            df = _clean(pd.DataFrame(rows))<br>            _load_to_postgres(df, pg_conn, upsert=True)<br>            total += len(df)<br>            log.info(f&quot;  Page {offset//1000 + 1}: {len(df)} rows&quot;)<br>        if not has_more:<br>            break<br>        offset += 1000<br>    log.info(f&quot;24h extraction done. {total} rows loaded.&quot;)<br>    return total</pre><p><strong>Production cron setup:</strong></p><pre># /etc/cron.d/netsuite_etl<br># Run nightly at 01:00 — after business hours, before morning reports<br>0 1 * * * ubuntu /opt/netsuite/venv/bin/python \<br>    /opt/netsuite/get_sales.py --mode 24h \<br>    &gt;&gt; /opt/netsuite/logs/get_sales.log 2&gt;&amp;1</pre><p><strong>Why upsert (not append) for 24h windows?</strong></p><pre>Scenario: Sales order SO-10045 was approved today.<br>It was created 3 weeks ago (trandate = 3 weeks ago).<br>Its status changed from &#39;Pending Approval&#39; → &#39;Approved&#39;.</pre><pre>If you APPEND: You now have two rows for SO-10045.<br>              One with old status, one with new status.<br>              Your reports double-count.<br>If you UPSERT: The existing row is UPDATED with new status.<br>               Clean, consistent, correct.</pre><h3>Strategy 3: 1-Hour Window (Near Real-Time)</h3><p><strong>When to use:</strong></p><ul><li>Dashboards that need data fresher than daily</li><li>Operations teams monitoring live transactions</li><li>Alert systems (“notify me when new invoices are created”)</li><li>Integration pipelines feeding other systems</li></ul><pre>def extract_last_1h(pg_conn):<br>    &quot;&quot;&quot;<br>    Extract records modified in the last 60 minutes.<br>    Designed for high-frequency cron: */30 * * * *<br>    (every 30 min, 1h lookback = 30min overlap buffer)<br>The 30-minute overlap is intentional:<br>    - Protects against clock drift between NetSuite and your server<br>    - Catches records that were mid-write during the last run<br>    - Upsert makes overlapping fetches safe (no duplicates)<br>    &quot;&quot;&quot;<br>    NS_ACCOUNT_ID = os.getenv(&quot;NS_ACCOUNT_ID&quot;)<br>    SUITEQL_URL   = (<br>        f&quot;https://{NS_ACCOUNT_ID.lower().replace(&#39;_&#39;,&#39;-&#39;)}&quot;<br>        &quot;.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql&quot;<br>    )<br>    now          = datetime.utcnow()<br>    window_start = (now - timedelta(hours=1)).strftime(&quot;%Y-%m-%d %H:%M:%S&quot;)<br>    window_end   = now.strftime(&quot;%Y-%m-%d %H:%M:%S&quot;)<br>    log.info(f&quot;1h window: {window_start} → {window_end}&quot;)<br>    # For 1h windows, we fetch ALL record types in one pass<br>    # to minimize the number of separate jobs needed<br>    queries = {<br>        &quot;sales_orders&quot;:    (&quot;SalesOrd&quot;,  &quot;netsuite.sales_orders&quot;),<br>        &quot;invoices&quot;:        (&quot;CustInvc&quot;,  &quot;netsuite.invoices&quot;),<br>        &quot;purchase_orders&quot;: (&quot;PurchOrd&quot;,  &quot;netsuite.purchase_orders&quot;),<br>    }<br>    results = {}<br>    for name, (txn_type, table) in queries.items():<br>        offset = 0<br>        total  = 0<br>        while True:<br>            query = f&quot;&quot;&quot;<br>                SELECT<br>                    t.id,<br>                    TO_CHAR(t.trandate, &#39;YYYY-MM-DD&#39;)             AS trandate,<br>                    t.tranid                                        AS document_number,<br>                    &#39;{txn_type}&#39;                                    AS type,<br>                    t.entity                                        AS customer_id,<br>                    t.amount,<br>                    t.status,<br>                    TO_CHAR(t.lastmodifieddate, &#39;YYYY-MM-DD HH24:MI:SS&#39;) AS last_modified<br>                FROM transaction t<br>                WHERE t.type = &#39;{txn_type}&#39;<br>                  AND t.lastmodifieddate &gt;= TO_DATE(&#39;{window_start}&#39;,<br>                                                    &#39;YYYY-MM-DD HH24:MI:SS&#39;)<br>                  AND t.lastmodifieddate &lt;  TO_DATE(&#39;{window_end}&#39;,<br>                                                    &#39;YYYY-MM-DD HH24:MI:SS&#39;)<br>                ORDER BY t.id ASC<br>                OFFSET {offset}<br>                LIMIT 1000<br>            &quot;&quot;&quot;<br>            auth = build_auth_header(&quot;POST&quot;, SUITEQL_URL)<br>            resp = requests.post(<br>                SUITEQL_URL,<br>                json={&quot;q&quot;: query},<br>                headers={&quot;Authorization&quot;: auth,<br>                         &quot;Content-Type&quot;: &quot;application/json&quot;,<br>                         &quot;prefer&quot;: &quot;transient&quot;},<br>                timeout=120   # shorter timeout - 1h window should be small<br>            )<br>            resp.raise_for_status()<br>            data = resp.json()<br>            rows     = data.get(&quot;items&quot;, [])<br>            has_more = data.get(&quot;hasMore&quot;, False)<br>            if rows:<br>                df = _clean(pd.DataFrame(rows))<br>                # Always upsert for near-real-time - overlapping windows<br>                # mean the same record can appear in consecutive runs<br>                _load_to_postgres_table(df, pg_conn, table, upsert=True)<br>                total += len(df)<br>            if not has_more:<br>                break<br>            offset += 1000<br>        results[name] = total<br>        log.info(f&quot;  {name}: {total} rows&quot;)<br>    return results<br><br>def _load_to_postgres_table(df, conn, full_table_name, upsert):<br>    &quot;&quot;&quot;Generic loader for any target table.&quot;&quot;&quot;<br>    cols    = list(df.columns)<br>    records = [tuple(r) for r in df.itertuples(index=False)]<br>    with conn.cursor() as cur:<br>        if upsert:<br>            set_clause = &quot;, &quot;.join(<br>                f&quot;{c} = EXCLUDED.{c}&quot; for c in cols if c != &quot;id&quot;<br>            )<br>            sql = f&quot;&quot;&quot;<br>                INSERT INTO {full_table_name} ({&#39;, &#39;.join(cols)})<br>                VALUES %s<br>                ON CONFLICT (id) DO UPDATE SET {set_clause}<br>            &quot;&quot;&quot;<br>        else:<br>            sql = f&quot;&quot;&quot;<br>                INSERT INTO {full_table_name} ({&#39;, &#39;.join(cols)})<br>                VALUES %s<br>            &quot;&quot;&quot;<br>        psycopg2.extras.execute_values(cur, sql, records, page_size=500)<br>    conn.commit()<br></pre><p><strong>Cron setup for near-real-time:</strong></p><pre># Every 30 minutes, with 1-hour lookback window<br>*/30 * * * * ubuntu /opt/etl/venv/bin/python \<br>    /opt/etl/hour_sales.py --mode 1h \<br>    &gt;&gt; /opt/etl/logs/hour_sales.log 2&gt;&amp;1</pre><h3>Strategy 4: Custom Chunk (Targeted Extraction)</h3><p><strong>When to use:</strong></p><ul><li>Backfilling a specific quarter after a data quality issue</li><li>Re-extracting a single subsidiary after a mapping fix</li><li>Pulling data for a specific project or audit</li><li>Testing your pipeline on a known dataset</li></ul><pre>def extract_custom_range(<br>    start_date: str,<br>    end_date: str,<br>    chunk_days: int = 7,<br>    txn_types: list = None,<br>    subsidiary_id: int = None,<br>    pg_conn = None,<br>    upsert: bool = True<br>):<br>    &quot;&quot;&quot;<br>    Flexible extraction for any date range with optional filters.<br>    Useful for backfills, audits, and targeted re-extractions.<br>Args:<br>        start_date    : ISO format, e.g. &#39;2024-01-01&#39;<br>        end_date      : ISO format, e.g. &#39;2024-03-31&#39;<br>        chunk_days    : days per chunk (7 = weekly, 30 = monthly)<br>        txn_types     : list of NetSuite type codes, default all<br>        subsidiary_id : filter by subsidiary (None = all)<br>        pg_conn       : active psycopg2 connection<br>        upsert        : True = upsert, False = append<br>    &quot;&quot;&quot;<br>    NS_ACCOUNT_ID = os.getenv(&quot;NS_ACCOUNT_ID&quot;)<br>    SUITEQL_URL   = (<br>        f&quot;https://{NS_ACCOUNT_ID.lower().replace(&#39;_&#39;,&#39;-&#39;)}&quot;<br>        &quot;.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql&quot;<br>    )<br>    if txn_types is None:<br>        txn_types = [&quot;SalesOrd&quot;, &quot;CustInvc&quot;, &quot;PurchOrd&quot;]<br>    type_filter = &quot;, &quot;.join(f&quot;&#39;{t}&#39;&quot; for t in txn_types)<br>    sub_filter  = (<br>        f&quot;AND t.subsidiary = {subsidiary_id}&quot; if subsidiary_id else &quot;&quot;<br>    )<br>    start  = datetime.strptime(start_date, &quot;%Y-%m-%d&quot;)<br>    end    = datetime.strptime(end_date,   &quot;%Y-%m-%d&quot;)<br>    cursor = start<br>    total  = 0<br>    log.info(<br>        f&quot;Custom range: {start_date} → {end_date} &quot;<br>        f&quot;| chunk={chunk_days}d | types={txn_types} &quot;<br>        f&quot;| subsidiary={subsidiary_id or &#39;all&#39;}&quot;<br>    )<br>    while cursor &lt; end:<br>        chunk_end = min(cursor + timedelta(days=chunk_days), end)<br>        s = cursor.strftime(&quot;%Y-%m-%d&quot;)<br>        e = chunk_end.strftime(&quot;%Y-%m-%d&quot;)<br>        offset = 0<br>        while True:<br>            query = f&quot;&quot;&quot;<br>                SELECT<br>                    t.id,<br>                    TO_CHAR(t.trandate, &#39;YYYY-MM-DD&#39;)              AS trandate,<br>                    t.tranid                                         AS document_number,<br>                    t.type,<br>                    t.entity                                         AS entity_id,<br>                    t.amount,<br>                    t.status,<br>                    t.subsidiary,<br>                    TO_CHAR(t.lastmodifieddate,<br>                            &#39;YYYY-MM-DD HH24:MI:SS&#39;)                AS last_modified<br>                FROM transaction t<br>                WHERE t.type IN ({type_filter})<br>                  AND t.trandate &gt;= TO_DATE(&#39;{s}&#39;, &#39;YYYY-MM-DD&#39;)<br>                  AND t.trandate &lt;  TO_DATE(&#39;{e}&#39;, &#39;YYYY-MM-DD&#39;)<br>                  {sub_filter}<br>                ORDER BY t.id ASC<br>                OFFSET {offset}<br>                LIMIT 1000<br>            &quot;&quot;&quot;<br>            auth = build_auth_header(&quot;POST&quot;, SUITEQL_URL)<br>            resp = requests.post(<br>                SUITEQL_URL,<br>                json={&quot;q&quot;: query},<br>                headers={&quot;Authorization&quot;: auth,<br>                         &quot;Content-Type&quot;: &quot;application/json&quot;,<br>                         &quot;prefer&quot;: &quot;transient&quot;},<br>                timeout=300<br>            )<br>            resp.raise_for_status()<br>            data     = resp.json()<br>            rows     = data.get(&quot;items&quot;, [])<br>            has_more = data.get(&quot;hasMore&quot;, False)<br>            if rows:<br>                df    = _clean(pd.DataFrame(rows))<br>                _load_to_postgres(df, pg_conn, upsert)<br>                total += len(df)<br>                log.info(f&quot;  {s}→{e} | page {offset//1000+1}: {len(df)} rows&quot;)<br>            if not has_more:<br>                break<br>            offset += 1000<br>        cursor = chunk_end<br>    log.info(f&quot;Custom range done. {total} rows loaded.&quot;)<br>    return total<br><br># Usage examples:<br># extract_custom_range(&quot;2024-01-01&quot;, &quot;2024-03-31&quot;)           # Q1 backfill<br># extract_custom_range(&quot;2024-01-01&quot;, &quot;2024-12-31&quot;,<br>#                      chunk_days=7, subsidiary_id=3)         # one subsidiary<br># extract_custom_range(&quot;2025-06-01&quot;, &quot;2025-06-30&quot;,<br>#                      txn_types=[&quot;CustInvc&quot;])                 # invoices only<br></pre><h3>Part 3: Load Patterns — Append vs. Upsert</h3><p>The extraction strategy gets data out of NetSuite. The load pattern decides what happens when it arrives in your database.</p><h3>Append: Simple, Fast, Additive</h3><p>python</p><pre>def load_append(df: pd.DataFrame, conn, schema: str, table: str):<br>    &quot;&quot;&quot;<br>    Append-only load. Every row from the API is inserted as a new row.<br>    No deduplication. Fast. Simple.<br>    Use when:<br>    - Records genuinely accumulate (event logs, audit trails)<br>    - Immutable records (GL journal entries once posted)<br>    - You want a full history of every API response<br>    - You&#39;ll deduplicate downstream (in a data warehouse layer)<br>    Do NOT use when:<br>    - Records can be updated (sales orders, invoices)<br>    - You run the same extraction twice (creates duplicates)<br>    - You use 1h/24h windows (overlap creates duplicates)<br>    &quot;&quot;&quot;<br>    cols    = list(df.columns)<br>    records = [tuple(r) for r in df.itertuples(index=False)]<br>    with conn.cursor() as cur:<br>        sql = f&quot;&quot;&quot;<br>            INSERT INTO {schema}.{table}<br>            ({&#39;, &#39;.join(cols)})<br>            VALUES %s<br>        &quot;&quot;&quot;<br>        psycopg2.extras.execute_values(cur, sql, records, page_size=500)<br>    conn.commit()<br>    log.info(f&quot;Appended {len(records)} rows to {schema}.{table}&quot;)<br></pre><p><strong>When append is the right choice:</strong></p><pre>NetSuite GL Journal Entries (once posted, they never change):<br>  → Append. No risk of updates. Simple and fast.</pre><pre>NetSuite Inventory Adjustment Log:<br>  → Append. Each adjustment is a new event. Historical.</pre><pre>Custom audit log table:<br>  → Append. You want every API response recorded.</pre><h3>Upsert: Idempotent, Safe, Correct</h3><pre>def load_upsert(<br>    df: pd.DataFrame,<br>    conn,<br>    schema: str,<br>    table: str,<br>    pk_cols: list = None,<br>    skip_update_cols: list = None<br>):<br>    &quot;&quot;&quot;<br>    Upsert (INSERT ... ON CONFLICT DO UPDATE).<br>    Idempotent: running the same extraction twice gives the same result.Use when:<br>    - Records can change after creation (sales orders, invoices)<br>    - You use 24h or 1h windows (overlapping runs)<br>    - You need consistent point-in-time state in your database<br>    - You want to be safe if the job runs twice<br>    pk_cols          : columns that form the unique key (default: [&#39;id&#39;])<br>    skip_update_cols : columns never overwritten on conflict (e.g. &#39;created_at&#39;)<br>    &quot;&quot;&quot;<br>    if pk_cols is None:<br>        pk_cols = [&quot;id&quot;]<br>    if skip_update_cols is None:<br>        skip_update_cols = []<br>    cols = list(df.columns)<br>    update_cols = [<br>        c for c in cols<br>        if c not in pk_cols and c not in skip_update_cols<br>    ]<br>    set_clause = &quot;, &quot;.join(f&quot;{c} = EXCLUDED.{c}&quot; for c in update_cols)<br>    conflict_target = &quot;, &quot;.join(pk_cols)<br>    records = [tuple(r) for r in df.itertuples(index=False)]<br>    with conn.cursor() as cur:<br>        sql = f&quot;&quot;&quot;<br>            INSERT INTO {schema}.{table}<br>            ({&#39;, &#39;.join(cols)})<br>            VALUES %s<br>            ON CONFLICT ({conflict_target})<br>            DO UPDATE SET {set_clause}<br>        &quot;&quot;&quot;<br>        psycopg2.extras.execute_values(cur, sql, records, page_size=500)<br>    conn.commit()<br>    log.info(f&quot;Upserted {len(records)} rows into {schema}.{table}&quot;)<br></pre><p><strong>When upsert is the right choice:</strong></p><pre>NetSuite Sales Orders (status changes: Pending → Approved → Closed):<br>  → Upsert. The same order ID gets different status over time.<br>    You want the CURRENT status, not a history of statuses.<br>NetSuite Invoices (amount can be edited before posting):<br>  → Upsert. The ID is stable, the data might change.<br>Customer master data:<br>  → Upsert. Same customer ID, updated address or name.</pre><h3>The Append + Timestamp Pattern (Best of Both)</h3><p>For cases where you need both history and current state:</p><pre>def load_append_with_timestamp(df: pd.DataFrame, conn, schema: str, table: str):<br>    &quot;&quot;&quot;<br>    Append every row, but add an extracted_at timestamp.<br>    In downstream queries, use MAX(extracted_at) per ID<br>    to get the current state.Table DDL:<br>        CREATE TABLE netsuite.sales_orders_history (<br>            id              INTEGER,<br>            trandate        DATE,<br>            document_number TEXT,<br>            status          TEXT,<br>            amount          NUMERIC,<br>            last_modified   TIMESTAMP,<br>            extracted_at    TIMESTAMP DEFAULT NOW()   -- added here<br>        );<br>    &quot;&quot;&quot;<br>    df = df.copy()<br>    df[&quot;extracted_at&quot;] = datetime.utcnow()<br>    cols    = list(df.columns)<br>    records = [tuple(r) for r in df.itertuples(index=False)]<br>    with conn.cursor() as cur:<br>        sql = f&quot;&quot;&quot;<br>            INSERT INTO {schema}.{table}<br>            ({&#39;, &#39;.join(cols)})<br>            VALUES %s<br>        &quot;&quot;&quot;<br>        psycopg2.extras.execute_values(cur, sql, records, page_size=500)<br>    conn.commit()<br>    log.info(f&quot;Appended {len(records)} rows to {schema}.{table} &quot;<br>             f&quot;with extracted_at={datetime.utcnow().isoformat()}&quot;)<br><br># Current state view on top of the history table:<br>CURRENT_STATE_VIEW = &quot;&quot;&quot;<br>CREATE OR REPLACE VIEW netsuite.sales_orders_current AS<br>SELECT DISTINCT ON (id)<br>    id, trandate, document_number, status, amount,<br>    last_modified, extracted_at<br>FROM netsuite.sales_orders_history<br>ORDER BY id, extracted_at DESC;<br>&quot;&quot;&quot;<br></pre><h3>Part 4: Common SuiteQL Queries (Copy-Paste Ready)</h3><h3>Transaction Lines (Detail Records)</h3><pre>def build_transaction_lines_query(start_date: str, end_date: str,<br>                                   offset: int) -&gt; str:<br>    return f&quot;&quot;&quot;<br>        SELECT<br>            tl.transaction                          AS transaction_id,<br>            tl.id                                   AS line_id,<br>            tl.linesequencenumber                   AS line_num,<br>            i.itemid                                AS item_code,<br>            i.displayname                           AS item_name,<br>            tl.quantity,<br>            tl.rate,<br>            tl.amount,<br>            tl.taxamount                            AS tax_amount,<br>            a.acctnumber                            AS account_number,<br>            a.fullname                              AS account_name,<br>            TO_CHAR(t.trandate, &#39;YYYY-MM-DD&#39;)       AS trandate<br>        FROM transactionline tl<br>        LEFT JOIN transaction t ON tl.transaction = t.id<br>        LEFT JOIN item i        ON tl.item = i.id<br>        LEFT JOIN account a     ON tl.expenseaccount = a.id<br>        WHERE t.type IN (&#39;SalesOrd&#39;, &#39;CustInvc&#39;)<br>          AND t.trandate &gt;= TO_DATE(&#39;{start_date}&#39;, &#39;YYYY-MM-DD&#39;)<br>          AND t.trandate &lt;  TO_DATE(&#39;{end_date}&#39;,   &#39;YYYY-MM-DD&#39;)<br>        ORDER BY tl.transaction ASC, tl.linesequencenumber ASC<br>        OFFSET {offset}<br>        LIMIT 1000<br>    &quot;&quot;&quot;</pre><h3>Inventory / Item Master</h3><pre>ITEM_MASTER_QUERY = &quot;&quot;&quot;<br>    SELECT<br>        i.id,<br>        i.itemid                    AS item_code,<br>        i.displayname               AS item_name,<br>        i.description,<br>        it.itemtype                 AS item_type,<br>        i.salesprice                AS sales_price,<br>        i.cost                      AS standard_cost,<br>        i.unitstype,<br>        i.isinactive,<br>        TO_CHAR(i.lastmodifieddate,<br>                &#39;YYYY-MM-DD HH24:MI:SS&#39;) AS last_modified<br>    FROM item i<br>    LEFT JOIN itemtype it ON i.itemtype = it.id<br>    WHERE i.isinactive = &#39;F&#39;<br>    ORDER BY i.id ASC<br>    OFFSET {offset}<br>    LIMIT 1000<br>&quot;&quot;&quot;</pre><h3>Customer Master with Contact</h3><pre>CUSTOMER_QUERY = &quot;&quot;&quot;<br>    SELECT<br>        e.id,<br>        e.entityid                  AS entity_code,<br>        e.companyname               AS company_name,<br>        e.email,<br>        e.phone,<br>        addr.addr1                  AS address_line1,<br>        addr.city,<br>        addr.state,<br>        addr.country,<br>        e.isinactive,<br>        TO_CHAR(e.lastmodifieddate,<br>                &#39;YYYY-MM-DD HH24:MI:SS&#39;) AS last_modified<br>    FROM entity e<br>    LEFT JOIN entityaddress addr ON e.defaultaddress = addr.nkey<br>    WHERE e.type = &#39;customer&#39;<br>      AND e.isinactive = &#39;F&#39;<br>    ORDER BY e.id ASC<br>    OFFSET {offset}<br>    LIMIT 1000<br>&quot;&quot;&quot;</pre><h3>Inventory Balance (Current Stock)</h3><pre>INVENTORY_BALANCE_QUERY = &quot;&quot;&quot;<br>    SELECT<br>        il.item                     AS item_id,<br>        i.itemid                    AS item_code,<br>        i.displayname               AS item_name,<br>        loc.name                    AS location,<br>        il.quantityonhand           AS qty_on_hand,<br>        il.quantityavailable        AS qty_available,<br>        il.quantityonorder          AS qty_on_order,<br>        il.quantitycommitted        AS qty_committed,<br>        i.salesprice                AS sales_price,<br>        (il.quantityonhand * i.cost) AS stock_value<br>    FROM inventorylot il<br>    LEFT JOIN item i        ON il.item = i.id<br>    LEFT JOIN location loc  ON il.location = loc.id<br>    WHERE i.isinactive = &#39;F&#39;<br>    ORDER BY il.item ASC<br>    OFFSET {offset}<br>    LIMIT 1000<br>&quot;&quot;&quot;</pre><h3>Part 5: The Full Command-Line ETL Runner</h3><p>Putting it all together into one script with --mode argument:</p><pre>#!/usr/bin/env python3<br>&quot;&quot;&quot;<br>NetSuite SuiteQL → PostgreSQL ETL<br>Usage:<br>    python netsuite_etl.py --mode full       # full historical load<br>    python netsuite_etl.py --mode 24h        # last 24 hours<br>    python netsuite_etl.py --mode 1h         # last 1 hour<br>    python netsuite_etl.py --mode custom \<br>        --start 2024-01-01 --end 2024-03-31  # custom range<br>&quot;&quot;&quot;<br>import argparse<br>import psycopg2<br>import logging<br>import os<br>from dotenv import load_dotenv<br>load_dotenv()<br>logging.basicConfig(<br>    level=logging.INFO,<br>    format=&quot;%(asctime)s [%(levelname)s] %(message)s&quot;,<br>    handlers=[<br>        logging.FileHandler(&quot;/opt/netsuite_etl/logs/etl.log&quot;),<br>        logging.StreamHandler(),<br>    ]<br>)<br>log = logging.getLogger(__name__)<br><br>def get_pg_conn():<br>    return psycopg2.connect(<br>        host=os.getenv(&quot;PG_HOST&quot;,     &quot;localhost&quot;),<br>        port=os.getenv(&quot;PG_PORT&quot;,     5432),<br>        dbname=os.getenv(&quot;PG_DB&quot;,     &quot;raduga_prd&quot;),<br>        user=os.getenv(&quot;PG_USER&quot;,     &quot;postgres&quot;),<br>        password=os.getenv(&quot;PG_PASSWORD&quot;, &quot;&quot;),<br>        keepalives=1,<br>        keepalives_idle=60,<br>        keepalives_interval=10,<br>        keepalives_count=5,<br>    )<br><br>def main():<br>    parser = argparse.ArgumentParser(description=&quot;NetSuite ETL Runner&quot;)<br>    parser.add_argument(&quot;--mode&quot;,  required=True,<br>                        choices=[&quot;full&quot;, &quot;24h&quot;, &quot;1h&quot;, &quot;custom&quot;])<br>    parser.add_argument(&quot;--start&quot;, help=&quot;Start date (YYYY-MM-DD) for custom mode&quot;)<br>    parser.add_argument(&quot;--end&quot;,   help=&quot;End date   (YYYY-MM-DD) for custom mode&quot;)<br>    parser.add_argument(&quot;--chunk&quot;, type=int, default=30,<br>                        help=&quot;Chunk size in days (default: 30)&quot;)<br>    parser.add_argument(&quot;--append&quot;, action=&quot;store_true&quot;,<br>                        help=&quot;Use append instead of upsert&quot;)<br>    args = parser.parse_args()<br>    upsert = not args.append<br>    log.info(f&quot;Starting ETL | mode={args.mode} | upsert={upsert}&quot;)<br>    conn = get_pg_conn()<br>    try:<br>        if args.mode == &quot;full&quot;:<br>            extract_full_range(<br>                historical_start=&quot;2023-01-01&quot;,<br>                chunk_days=args.chunk,<br>                pg_conn=conn,<br>                upsert=upsert<br>            )<br>        elif args.mode == &quot;24h&quot;:<br>            extract_last_24h(pg_conn=conn, upsert=True)<br>        elif args.mode == &quot;1h&quot;:<br>            extract_last_1h(pg_conn=conn)<br>        elif args.mode == &quot;custom&quot;:<br>            if not args.start or not args.end:<br>                raise ValueError(&quot;--start and --end required for custom mode&quot;)<br>            extract_custom_range(<br>                start_date=args.start,<br>                end_date=args.end,<br>                chunk_days=args.chunk,<br>                pg_conn=conn,<br>                upsert=upsert<br>            )<br>    except Exception as e:<br>        log.error(f&quot;ETL failed: {e}&quot;, exc_info=True)<br>        raise<br>    finally:<br>        conn.close()<br>        log.info(&quot;ETL complete. Connection closed.&quot;)<br><br>if __name__ == &quot;__main__&quot;:<br>    main()</pre><h3>Part 6: Decision Guide — Which Strategy for Which Situation</h3><pre>┌─────────────────────────────────────────────────────────────────────┐<br>│                    QUICK DECISION GUIDE                             │<br>├────────────────────────┬─────────────────┬───────────┬──────────────┤<br>│ Situation              │ Strategy        │ Load Mode │ Cron         │<br>├────────────────────────┼─────────────────┼───────────┼──────────────┤<br>│ First-time data load   │ Full range      │ Upsert    │ One-time     │<br>│ Nightly refresh        │ 24h window      │ Upsert    │ 0 1 * * *    │<br>│ Hourly dashboard feed  │ 1h window       │ Upsert    │ */30 * * * * │<br>│ Audit trail only       │ 24h window      │ Append    │ 0 1 * * *    │<br>│ Backfill one quarter   │ Custom range    │ Upsert    │ Manual       │<br>│ Fix one subsidiary     │ Custom + filter │ Upsert    │ Manual       │<br>│ Test pipeline          │ Custom (1 week) │ Append    │ Manual       │<br>│ GL journal entries     │ 24h window      │ Append    │ 0 1 * * *    │<br>│ Customer master sync   │ 24h window      │ Upsert    │ 0 2 * * *    │<br>│ Inventory balance      │ 1h window       │ Upsert    │ 0 * * * *    │<br>└────────────────────────┴─────────────────┴───────────┴──────────────┘</pre><h3>Conclusion</h3><p>The code that extracts data from NetSuite is rarely the hard part. The strategy behind it is.</p><p>A query that works perfectly for 100 rows breaks at 100,000. A pipeline that runs fine on first load fails silently on incremental refresh. An append load creates duplicates when the job retries.</p><p>The four strategies — <strong>full range, 24h, 1h, custom chunk</strong> — cover every operational scenario you’ll encounter in a production NetSuite ETL. The two load patterns — <strong>append and upsert</strong> — give you the right tradeoffs for different data types.</p><p>Combine them correctly and you get a pipeline that:</p><ul><li>✅ Never times out (chunked extraction)</li><li>✅ Never duplicates (upsert for updates)</li><li>✅ Always recovers from failure (commit per chunk)</li><li>✅ Runs on any schedule (one flag change)</li></ul><p><strong>SEO keywords</strong>: NetSuite REST API Python, SuiteQL ETL, NetSuite data extraction, OAuth 1.0a NetSuite, NetSuite to PostgreSQL, incremental load NetSuite</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=382866401e8e" width="1" height="1" alt=""><hr><p><a href="https://blog.dataengineerthings.org/extracting-netsuite-data-via-rest-api-why-your-etl-strategy-matters-more-than-your-code-382866401e8e">Extracting NetSuite Data via REST API: Why Your ETL Strategy Matters More Than Your Code</a> was originally published in <a href="https://blog.dataengineerthings.org/">Data Engineer Things</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>