<?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 Christophe Oliveres on Medium]]></title>
        <description><![CDATA[Stories by Christophe Oliveres on Medium]]></description>
        <link>https://medium.com/@Christophe_Ol?source=rss-e368c4898c92------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*Yy9DL2fjENut0aOkRzMOGw.png</url>
            <title>Stories by Christophe Oliveres on Medium</title>
            <link>https://medium.com/@Christophe_Ol?source=rss-e368c4898c92------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 23:47:28 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@Christophe_Ol/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[I Turned My Kobo Reading Data into a Dashboard (and Discovered I’m a Night Owl Reader) — chap 1/2 …]]></title>
            <link>https://medium.com/@Christophe_Ol/i-turned-my-kobo-reading-data-into-a-dashboard-and-discovered-im-a-night-owl-reader-chap-1-2-7df05e4385c6?source=rss-e368c4898c92------2</link>
            <guid isPermaLink="false">https://medium.com/p/7df05e4385c6</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[analytics]]></category>
            <dc:creator><![CDATA[Christophe Oliveres]]></dc:creator>
            <pubDate>Tue, 07 Oct 2025 12:20:17 GMT</pubDate>
            <atom:updated>2025-10-07T12:20:17.520Z</atom:updated>
            <content:encoded><![CDATA[<h3>I Turned My Kobo Reading Data into a Dashboard (and Discovered I’m a Night Owl Reader) — chap 1/2 : the workflow.</h3><p>We carry our books everywhere, but rarely do we see the <em>data</em> behind our reading habits. <br>I was wondering: <em>How much do I really read? Which authors do I binge? Am I more of a sprinter or a marathon reader?</em></p><p>Don’t have a premium account? <a href="https://chrsolv.substack.com/p/i-turned-my-kobo-reading-data-into">Read it on Substack</a></p><blockquote><em>So I decided to </em><strong><em>hack my Kobo e-reader</em></strong><em> by installing </em><a href="https://koreader.rocks/"><em>KO-reader</em></a><em>. <br>Spoiler alert: every page turn is quietly logged in a humble SQLite database, and t </em>hat’s where my reading story begins.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*8rDb_XJP_XAMbw4M.png" /><figcaption>All screenshots in this article come directly from my dashboard.</figcaption></figure><h3>Step 1 — Getting the data</h3><p>Every time I plug my Kobo into my laptop, I copy its statistics.sqlite file. This little file knows everything:</p><ul><li>What books I’ve loaded</li><li>How many hours I’ve read</li><li>Where I stopped last night at 1:43am</li><li>And the exact moment in the year I decide it’s time to reread Harry Potter</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hgEg6Qb3StfGzd__6IAOUA.png" /><figcaption>Streamlit + Plotly combo 🔥</figcaption></figure><h3>Step 2 — Building the pipeline</h3><p>I wrote <strong>Python scripts</strong> that:</p><ol><li>Cleans the raw data</li><li>Builds <strong>Parquet tables</strong>:</li></ol><ul><li>fact (reading sessions)</li><li>dim (books metadata)</li><li>agg (summaries per dataviz)</li></ul><p>3. Updates incrementally (so I can rerun it whenever I sync new data).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mm0Wwyi4BlgBTtA3ck4Mqw.png" /><figcaption>Turns out I read a lot more than I sleep, productivity goals, redefined.</figcaption></figure><h3>Step 3 — Filling the gaps with LLM</h3><p>Not every book comes with nice metadata. Some don’t even have a proper genre. So I used a <strong>LLM </strong>to enrich the data:</p><ul><li>Detecting book categories / genre</li><li>Guessing missing publication years</li><li>Completing author information</li></ul><p>Since this can be <strong>the slowest and most costly part</strong> (LLM calls!), I designed it to run <strong>incrementally</strong> (only new books and missing metadata are processed).</p><p>This saves <strong>tokens, API costs, and execution time</strong></p><blockquote><em>Suddenly, my dashboard could tell me: </em>“You read more sci-fi in winter, and fantasy in spring”.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MtMX3l9DSck1X9UnwPuwnA.png" /><figcaption>Social life goes into hibernation in December</figcaption></figure><h3>Step 4 — Visualizing with Streamlit</h3><p>I built a <strong>Streamlit dashboard</strong> where I can:</p><ul><li>See a timeline of all my reading</li><li>Check how long it takes me to finish a book</li><li>Compare authors and genres</li><li>Track my average reading speed</li></ul><p>It’s like Goodreads, but powered by <strong>my own data pipeline</strong> : no ads, no recommendations, just my reading habits, raw and visualized.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MRFlpFUulYAokuRnmoErmg.jpeg" /><figcaption>After 10 PM, you’ll probably find me lost in the latest Cosmere book</figcaption></figure><h3>Lessons learned</h3><ul><li>Even a tiny SQLite file can grow into a <strong>full data engineering workflow</strong></li><li>Incremental pipelines make a huge difference when you update frequently</li><li>LLM is a surprisingly good ally for enriching messy real-world data</li><li>Visualization isn’t just for boring work dashboards, it can reveal patterns everywhere</li></ul><h3>What’s next?</h3><ul><li>Deploy the dashboard online</li><li>Write the chap 2 of this article (next week 😱)</li><li>Make it plug and play for the e-reader community ❤️</li></ul><blockquote><em>Until then, I’ll keep reading, and my Kobo will keep logging every page.</em></blockquote><p><em>Originally published at </em><a href="https://chrsolv.substack.com/p/i-turned-my-kobo-reading-data-into"><em>https://chrsolv.substack.com</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7df05e4385c6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Full LOCAL analytics workflow with dbt & DuckDB]]></title>
            <link>https://medium.com/@Christophe_Ol/full-local-analytics-workflow-with-dbt-duckdb-23acd6e9baa9?source=rss-e368c4898c92------2</link>
            <guid isPermaLink="false">https://medium.com/p/23acd6e9baa9</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Christophe Oliveres]]></dc:creator>
            <pubDate>Thu, 02 Oct 2025 18:36:16 GMT</pubDate>
            <atom:updated>2025-10-07T12:40:22.138Z</atom:updated>
            <content:encoded><![CDATA[<blockquote>Can we do some analytics locally in 2025? Short answer: yes. Long answer: also yes — but read this first.</blockquote><h3>Why bother doing analytics locally?</h3><p>Most of today’s analytics workflows assume the cloud: warehouses like BigQuery, Snowflake, or Redshift, orchestrated by Airflow, dbt Cloud, or similar. And for good reason : scalability, managed services (plug and play), easy integration.</p><p>But here’s the thing: <strong>not every project needs cloud-scale infrastructure</strong>. Sometimes you just want to test things quickly, run a proof of concept, or build a pipeline on your laptop without burning through credits.</p><p>That’s the bet I wanted to test: <strong>can we build a fully automated analytics pipeline locally, with modern tools, and make it production-like?</strong></p><p>Spoiler: yes, we can.</p><blockquote>Hey don’t have premium ? Read me on <a href="https://open.substack.com/pub/chrsolv/p/duckdb-dbt-analytics?r=4jzz2c&amp;utm_campaign=post&amp;utm_medium=web&amp;showWelcomeOnShare=true">Substack</a>!</blockquote><h3>The stack</h3><p>Here’s what the project looks like in practice:</p><ul><li><strong>Source</strong>: <a href="https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce">Olist E-Commerce Dataset</a></li><li><strong>Warehouse</strong>: <a href="https://duckdb.org/">DuckDB</a> ( blazing-fast database)</li><li><strong>Transformation</strong>: dbt</li><li><strong>Orchestration</strong>: GitHub Actions (yes it is not strictly local, but GitHub Actions fits well here: it automates the pipeline, runs the same commands you would locally, without adding cloud-scale complexity.)</li><li><strong>Validation / test</strong>: dbt tests</li><li><strong>Language</strong>: Python + SQL</li></ul><blockquote>In other words: <strong>an ELT pipeline without leaving your laptop.</strong></blockquote><p><a href="https://github.com/ChrisOlv/can_we_DBT_duck_this_data">Full repo is here</a></p><h3>Workflow overview (ELT in practice)</h3><h3>1. Extract &amp; Load</h3><ul><li>Fetch raw CSV files directly from Kaggle with Python &amp; Kaggle API.</li><li>Store them in the filesystem, ready to be read by DuckDB.</li></ul><h3>2. Transform</h3><ul><li>Use dbt to create <strong>staging models</strong> (cleaning, typing, renaming).</li><li>Build a <strong>Star Schema</strong> in the marts layer.</li></ul><h3>3. Orchestrate</h3><ul><li>GitHub Actions triggers the pipeline daily.</li><li>Runs ingestion, dbt transformations, and dbt tests.</li><li>Keeps the local warehouse fresh and reproducible.</li><li>Can even update the documentation and publish it.</li></ul><h3>4. Validate</h3><ul><li>dbt tests catch broken data early: missing keys, duplicate IDs, failed relationships.</li><li>No manual “eyeballing” CSVs needed.</li></ul><h3>Why DuckDB?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*KSZNEYCzcNv2-FUO" /><figcaption>Unrelated Duck Staring Intently at the Camera, by <a href="https://unsplash.com/@bielmorro?utm_source=medium&amp;utm_medium=referral">Biel Morro</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>DuckDB is honestly a game-changer:</p><ul><li><strong>Fast</strong>: in-memory engine, queries huge files instantly.</li><li><strong>Simple</strong>: no server, no setup, just import and query.</li><li><strong>Portable</strong>: works the same on Mac, Linux, Windows, even inside GitHub Actions.</li><li><strong>Free</strong>: no credits, no hidden costs.</li></ul><p>After a few weeks using it, I’d say it’s become my favorite local warehouse 🔥(goodbye sqlite).</p><h3>Pros and cons of this approach</h3><p><strong>Pros:</strong></p><ul><li>💾 <strong>Full local</strong>: no need for cloud infra, everything runs on your machine.</li><li>💸 <strong>Cost-free</strong>: perfect for testing or personal projects.</li><li>🧪 <strong>Reproducible</strong>: GitHub Actions ensures consistent runs.</li></ul><p><strong>Cons:</strong></p><ul><li>🖥️ <strong>“Works on my machine” risk</strong>: local environments can differ.</li><li>🌩️ <strong>No modern cloud UI</strong>: everything is code-driven.</li><li>🐌 No powerfull scalable distributed compute</li></ul><h3>How to run it yourself</h3><ol><li>Clone <a href="https://github.com/ChrisOlv/can_we_DBT_duck_this_data">the rep</a></li><li>Install dependencies (pip install -r requirements.txt)</li><li>Run the ingestion script: python ingestion.py</li><li>Build models: cd brazilian_ecommerce &amp;&amp; dbt build</li></ol><p>In a couple of minutes, you’ll have a <strong>mini data warehouse running locally</strong>.</p><h3>What’s next?</h3><p>This pipeline builds a warehouse, but analytics doesn’t stop there, it only begins.<br>In the next one, I’ll dive into <strong>full data visualization</strong> on the same dataset. Turning this warehouse into charts, dashboards, and insights.</p><p>For me, this project was about playing with a point: <strong>you don’t need enterprise-grade infra to apply analytics engineering principles</strong>. dbt, DuckDB, and GitHub Actions make it possible to <strong>replicate cloud workflows locally</strong> : fast, cheap, and reproducible.</p><p>If you’re curious, check out the repo and give it a spin. And if it breaks on your machine… well, you know how it goes!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=23acd6e9baa9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Lego analysis dataset with Power BI]]></title>
            <link>https://medium.com/@Christophe_Ol/lego-analysis-dataset-with-power-bi-b5dad228cb41?source=rss-e368c4898c92------2</link>
            <guid isPermaLink="false">https://medium.com/p/b5dad228cb41</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[dashboard]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Christophe Oliveres]]></dc:creator>
            <pubDate>Sun, 26 Jun 2022 15:14:48 GMT</pubDate>
            <atom:updated>2022-06-26T15:15:09.109Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5UIxC5-70TnQNcK1a6PMWw.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@yuliamatvienko?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Yulia Matvienko</a> on <a href="https://unsplash.com/s/photos/lego?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>During my journey to learn Data Analysis, I’ve made a lot of Boot Camp and tuto. This article describes the method I used to understand and analyse a Lego dataset using (not Python but) Power BI.</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*xWBSwogw0Kh6TmwgMTllig.png" /><figcaption>TLDR : I created an easy and clear Power BI Dashboard to analyse a Lego Dataset.</figcaption></figure><h4>Summary:</h4><ul><li>Connect the data from the web</li><li>Answer questions using Data Vizualisations</li><li>Create a nice dashboard</li></ul><h4>1 Connect the data from the web</h4><p>Let’s load the dataset into Power Bi. Since it is stored on my <a href="https://github.com/ChrisOlv/Using-Power-BI-and-vizualisation-to-analyse-Lego-Data-set--">GitHub repo</a>, we will connect the data using a URL :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/699/1*Nl1aNXEP9kHSZr3zJeIMdg.png" /><figcaption>Use the URL of the Raw csv file to connect. Since the repo is public, there is no authentication issue.</figcaption></figure><blockquote>I try to keep as a best practice to always work with online saved items. In real world, you rarely work with a csv saved on your desktop.</blockquote><p>Reading the description, lego_set and parent_themes are linked. Let’s connect it in power pivot</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/634/1*Jn8QsknARkwcr4mbAwTz5g.png" /><figcaption>This is a relation * to 1. Read “many to one”. The parent theme is a “Dimension Table”, it contains the detail about the data. The lego_sets is a fact table, it can contains duplicated values.</figcaption></figure><h4>Observing the DF:</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HOmq_rLXY24ajeje7hTtvA.png" /></figure><p>Power Bi automatically promote first line as headline, then pick a data type.</p><p>It did a decent job here, just had to replace the “.” by “,” in the num_parts column to change the type to Integer.</p><p>This column is not complete, but as noted in the intro, we don’t need it for now (we might change it for fun at the end though).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/511/1*fU_C68s29zp_9iC7ZjZkTg.png" /></figure><p>The dataset comes with the description bellow:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/546/1*lb0ezTUtoShUQHAI-NbXqw.png" /></figure><p>The questions:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/546/1*YW_da8P8UeHOhie3fstQdg.png" /></figure><h4>Q1 : What percentage of all licensed sets ever released were Star Wars themed ?</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y47F9Yp8SYnREie_6ktF9g.png" /><figcaption>Before trying to answer this question, let’s visualize the licensed sets on a table and a bar chart.</figcaption></figure><p>As we see here, Star Wars is the first licensed set, in term of quantity of sets released. To display this info in Power Bi, we create a bar chart, then plug the variables:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/858/1*QG04ywgE8M39ylZCfCEK6Q.png" /><figcaption>On the left, the chart. In the middle the filter tab (True = licensed set only). We just want the licensed set, then on the right the X and Y axis. By default, PBI apply a Count when using a categorical variable.</figcaption></figure><p>Display the percentage of StarWars :</p><p>We will create 2 measures to count the Star Wars license vs the <em>not </em>Star Wars license :</p><pre>is_Star_Wars = CALCULATE(COUNTA(‘lego_sets’[set_name]),<br> ‘parent_themes’[licence_name] = “Star Wars”)</pre><pre>is_not_star_wars = CALCULATE( COUNTA(‘lego_sets’[set_name]),<br> ‘parent_themes’[licence_name] &lt;&gt; “Star Wars”)</pre><p>We simply count the row where the theme is / is not equal to ‘Star Wars’</p><blockquote>Why do we use “CALCULATE”?<br>Because Power BI and DAX are insane and irrational.</blockquote><p>Let’s delete the unnecessary stuff and highlight the important data (I always color in grey the superfluous, and in bright color the important.)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/517/1*IB0Mroq4feYDbh_R-ARMUw.png" /><figcaption>In Data viz, less is more</figcaption></figure><h4>2/ In which year was Star Wars not the most popular licensed theme (in terms of number of sets released that year)?</h4><p>We will split this one in multiple small elements:</p><ul><li>We need the quantity of set released by year</li><li>We need to filter only the licensed set</li><li>We need to visualize different licenses</li></ul><p>We will play with visualization a bit to find the best answer here:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/908/1*9nXm7SY_0bKvMT2QLQXCOQ.png" /><figcaption>Here we have the licensed set by year, now split by license</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/896/1*-6xTkZk6GtrD1Ihc54PSNg.png" /><figcaption>Ugly but effective</figcaption></figure><p>Use lines instead of bar</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/889/1*KVwVtIoJppjv8pZdYmhwmw.png" /><figcaption>Better, now let’s play with color to highlight Star Wars, and clean everything else</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/683/1*0oQgv2RsJlu5qgPm9htn1g.png" /><figcaption>Again, less is more</figcaption></figure><p>The grey is unnecessary information, we highlight Star Wars (this is why we are here, remember?) and the Super Heroes that steals first place.</p><p>When we over the cross over, we can read that the year was 2016 when Super Heroes arrived 1st.</p><h4>Now we’re done with the analysis, let’s play with lego :</h4><p>What to do with the null value?</p><p>We can delete it, replace it, ignore it … Most of the time it is better to get rid of uncomplete value and outliers. But let’s pretend we want to replace it with values.</p><p>It exists a lot of different ways to replace missing value in a data set. We can replace the values by the mean, media, mode … of the column.</p><p>In that case, let’s pretend we need to analyse the number of parts by license, we’d like to keep our complete data set.</p><p>Kudos to <a href="https://medium.com/u/169e8b9e5fe7">Braulio Berlanga</a> and his tuto below:</p><p><a href="https://medium.com/@mbsbraulio/how-to-replace-null-values-with-average-values-in-power-bi-5e45daed9dd5">How to replace null values with average values in Power BI</a></p><p>We will apply his method here :</p><pre>#&quot;Added Custom&quot; = Table.AddColumn(#&quot;Changed Type1&quot;, &quot;num_part_and_average&quot;, each if [num_parts] = 0 then null else [num_parts]),<br>#&quot;AVR_pricelist&quot; = Table.Column(#&quot;Added Custom&quot;,&quot;num_part_and_average&quot;),<br>#&quot;AVR_numparts&quot; = List.Average(#&quot;AVR_pricelist&quot;),<br>#&quot;Nul--&gt;Avrge_value&quot; = Table.AddColumn(#&quot;Added Custom&quot;,&quot;Nulm--&gt;Avrge_value&quot;, each if [num_part_and_average] = null then  #&quot;AVR_numparts&quot; else [num_part_and_average] )</pre><p>1/ To resume, we need to add a new column where the missing values are replaced by ‘null’.</p><p>2/ Then define the variable “average” to calculate the mean of the column (it allows your code to stay dynamic).</p><p>3/ Then use a simple if statement to declare: If it’s null, use the mean. If it’s not, don’t change anything.</p><p>Et voila !</p><h4>One final bonus question:</h4><p>How many unique sets were released each year ? Between 1955 and 2017 :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/753/1*SsaVuAwIlrcK0PEfo7cidg.png" /><figcaption>We plot the qty of set, and the year of release</figcaption></figure><p>Sounds easy, but there is a catch, we want the count of UNIQUE sets.</p><p>No problem, this is where Power Bi shines:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/342/1*TG7nkZmTg5BRQXUSR7MB3w.png" /></figure><p>In the visualisation panel, you can pick Count (distinct) instead of Count.</p><p>Or if we need to count distinct rows, the DISTINCTCOUNT does the job as well:</p><pre>Unique_set_num = CALCULATE(DISTINCTCOUNT(&#39;lego_sets (url)&#39;[set_num]))</pre><p>We add the new line on the same visual:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qd4D4ZEMs4b2IDFB9DF-pw.png" /><figcaption>The 2 lines are almost completely equals.</figcaption></figure><p>To check the difference between those 2 lines, we can plot the delta line:</p><p>First, add a new measure:</p><pre>Delta_set_num =</pre><pre>COUNTA(&#39;lego_sets (url)&#39;[set_num]) - DISTINCTCOUNT(&#39;lego_sets (url)&#39;[set_num])</pre><p>Then plot the line:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/790/1*qWXUMgs9Fcf7zANOD1gxqQ.png" /><figcaption>The dotted line is not easy to read</figcaption></figure><p>We will plot the difference as a bar chart, now we can see the difference:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1007/1*crkw-BX5k1ldwJmJaGJEiQ.png" /><figcaption>The bar chart + line chart highlights the data better. Turn off the data label, we can use the hover tool tip to read the values</figcaption></figure><h4>Let’s clean everything to add the UI :</h4><p>We will simply display the 4 graphs we created during this article. On the left panel, we can pretend this would be one of the many pages of a Dashboard (that’s why there is room to add items).</p><p>At the top, the 3 main KPI.</p><p>On the left, the line chart who display the quantity of sets, and below the licence who get the most sets.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xWBSwogw0Kh6TmwgMTllig.png" /><figcaption>Here is the final look</figcaption></figure><p>Finally, we can see the Star Wars focus with a light background, where we put our Star Wars focus visuals.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*35dgFMoK7jR5i-VJ8TBf_w.png" /><figcaption>In my previous article I insisted on the role of color in your dashboard</figcaption></figure><p>Conclusion:</p><p>This beginer friendly exercice was a nice example of how you can use Power BI to answer questions!</p><h4>Links &amp; Credits :</h4><p>The project was released initially on Data Camp : <a href="https://app.datacamp.com/learn/projects/history-of-lego/guided/Python">https://app.datacamp.com/learn/projects/history-of-lego/guided/Python</a> (premium)</p><p>Since I’m not a premium member, I use the GitHub of <a href="https://www.youtube.com/c/KGMIT">Keith Galli</a> to read the test : <a href="https://github.com/KeithGalli/lego-analysis/blob/master/project.ipynb">https://github.com/KeithGalli/lego-analysis/blob/master/project.ipynb</a></p><p>Dataset : <a href="https://rebrickable.com/downloads/">https://rebrickable.com/downloads/</a></p><p>Link to my previous article :</p><p><a href="https://medium.com/@oliveres.christophe/superstore-data-analysis-with-power-bi-b4be86a29230">Superstore data analysis with Power BI</a></p><p>Photo by <a href="https://unsplash.com/@yuliamatvienko?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Yulia Matvienko</a> on <a href="https://unsplash.com/s/photos/lego?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b5dad228cb41" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Superstore data analysis with Power BI]]></title>
            <link>https://medium.com/@Christophe_Ol/superstore-data-analysis-with-power-bi-b4be86a29230?source=rss-e368c4898c92------2</link>
            <guid isPermaLink="false">https://medium.com/p/b4be86a29230</guid>
            <category><![CDATA[dashboard]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-analyst]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Christophe Oliveres]]></dc:creator>
            <pubDate>Wed, 27 Apr 2022 11:13:56 GMT</pubDate>
            <atom:updated>2025-10-14T10:45:53.546Z</atom:updated>
            <content:encoded><![CDATA[<h3>Superstore intro to data analysis with Power BI</h3><p>In this article we will follow the data analysis of an online store dataset.</p><p>At the menu, <strong>connection to the dataset</strong>, <strong>data cleaning with Power Queries</strong>, <strong>Exploratory data analysis</strong>, then a bit of <strong>dashboard design</strong>.</p><p><a href="https://www.urbandictionary.com/define.php?term=tl%3Bdr"><em>TLDR</em></a><em> : We use Power BI to do a quick analysis of a sample dataset.</em></p><p>Not premium? <a href="https://chrsolv.substack.com/p/superstore-intro-to-data-analysis">read me on Substack</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Amm9eWzzqk13ywAtvkW1gg.jpeg" /></figure><h3>Summary</h3><p><em>Dataset and use case presentation</em><br>Data analysis : <br>1. Import and Clean data<br>2. Discover the dataset<br>3. First insights and observations<br>4. Ask the good questions, find the right answers<br>5. Create an user friendly dashboard page to our marketing department</p><h3>Dataset and use case presentation</h3><p><a href="https://www.kaggle.com/datasets/vivek468/superstore-dataset-final">This dataset</a> contains sales data from an online store who sales furniture, office supplies and tech stuff.</p><p>It includes : order id, date of order / shipping, info about the customer, info about the product, total sales, profit …</p><p>For this use case, let’s pretend the Marketing department came to see us with this dataset.</p><p><strong>They’d like our insights regarding the existing data and they came with these questions :</strong></p><blockquote>- Can we identify the best product / sub category / category, regarding quantity, sales or profit ?<br>- What items are generally sold together ? <br>- What other insights and observations can you provide us ?<br>- Can you help us monitoring these KPI over time ?</blockquote><p><em>Let’s answer their questions using Power BI !</em></p><h3>1. Import Data</h3><p>First, we need to import and understand the dataset :</p><figure><img alt="Power BI queries view" src="https://cdn-images-1.medium.com/max/1024/1*NMmjJJ3kEiYutjKIxMf6Pw.png" /><figcaption>The Power Queries menu, embedded in Power BI for desktop. The tool help you understand your dataset.</figcaption></figure><p>In power BI, once you’re connected to your source, (in that case, a traditional CSV file, but it could be anything : SQL Server, API, Spreadsheet…) we arrive on this view who gives us info regarding the data quality.</p><blockquote>Best practice : we always turn on the : Column distribution, Column Profile and Column quality. It will help us checking the distribution of each column, % of errors, duplicate, empty values …</blockquote><p>Here we can see that some rows are missing, that column contains numerical vs categorical values, the number of distinct values by column, min / max of each column …</p><h3>2. Data cleaning</h3><p>Let’s order this :</p><ul><li>First, change the column <strong>Data Type </strong>when needed : Data type will be very important to create calculation, comparison …</li><li>As a good example, the date values are currently text (string), sometimes in universal or US format . We need to standardize the format, then change the type to <em>date type</em>.</li><li>Replace every “.” by “,” in numerical columns</li><li>To delete error rows, we have multiple choices here : we can <strong>replace the missing values by the mean of the column</strong>, by the most common value (the mode), or simply delete the incomplete rows. We don’t have enough values to select the mode / mean option, let’s delete the incomplete rows.</li></ul><p>Next, the current dataset doesn’t show the unit values (price, margin, % margin) … We need to add those columns :</p><ul><li>Product price</li><li>Product margin</li><li>% margin</li></ul><p>Finally, we will add dates column once the original date is defined as date type : Order Day, order month, order year … (this will be helpful when we analyze the best sales days / months / years).</p><blockquote>Naming convention is <strong>fundamental </strong>in data analysis. Think of your future you, or your teammate who will use your dashboard in 2 months and won’t understand the difference between : sales, total sales, value, test_measure_sale_total…<br>A solid naming convention is mandatory !</blockquote><blockquote>Here we will prefer : <br>Current columns : Total sales, total margin, total profit <br>Adding the unit column : price sales, price margin, price profit</blockquote><h3>3. Exploratory Data Analysis</h3><p>Now the data are cleaned, we can dive into the next layer of details, first by using simple data visualization :</p><p>We can see on the images below :</p><ul><li>Some cities are performing better, in term of sales and profit</li><li>The 3 product categories are relatively close in term of sales but not in term of quantity sold and margin</li><li>Sales and profit are growing each year</li><li>We are addressing 3 customer segments, with different sales pattern</li><li>Our sales are best performing between the 9th and the 13th of each month. first days of the month are really low. The best month to sale are in 11/ 9 / 12 …</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/772/1*-_YQxIz3c87nNZmlhNMA4Q.png" /><figcaption>First dataviz to understand the dataset</figcaption></figure><p>Some cities are loosing money, we need to dive deeper to understand why. It’s doesn’t seem to be correlated to the total sales of the shop :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1001/1*0uWTsynGfC4qQzhgv7CpDQ.png" /><figcaption>When the dark blue is on the left side, the store is actually losing money.</figcaption></figure><p>Some sub cat are loosing money as well. We will zoom in to identify which product are loosing money</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1017/1*R0WyaSXAPOtpXx3WoqS0jA.png" /><figcaption>Like earlier, dark blue on the left side means the product subcategory is losing money</figcaption></figure><p>With this first intro to EDA, we can complete the marketing questions by adding our own questions :</p><p>Marketing questions : <br>1. Can we identify the best product / sub category / category, regarding quantity, sales and profit ?<br>2. What items are generally sold together ?<br>3. Can you help us monitoring these KPI over time ?</p><p><strong>Our first observation leads us to ask : <br></strong>4. What products are sold by the best shops ?<br>5. What shops are loosing money, what items do they sell the most / the least ?<br>6. What are the best days to sale product ? We can next pass this info to our marketing department to select the perfect day to : launch new product / launch ad campaign …<br>7. Do we need to delete of the shelves the product who lose money ? or do we need them to bring clients ? What product people buy with these product ? Do the low profit items help us selling the high profit items ?<br>8. Does the 3 customer segments have the same patterns and habit ?<br>9. What values can we use to predict sales in the future ?</p><p>We won’t cover in this article the full set of questions, but you understand the idea here. Data analysis is way more efficient when you are <strong>curious</strong>, when you <strong>understand the client needs</strong>, when <strong>you perform extra research </strong>with your dataset.</p><h3>4. Question 1 : What are the best product in term of sales, profit, quantity ?</h3><p>Let’s create a product page. We need to keep in mind that some of our visualizations will remains in the marketing Dashboard.</p><h4>Quantity sold :</h4><p>Let’s create a simple bar chart to visualize our quantity by category / sub category / product. We use the “drill down” feature of Power BI, to avoid creating too much charts :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xg5L0rzCiaqNwUQ4l8fdow.png" /><figcaption>We can drill down the data in order to visualize the next layer.</figcaption></figure><p>The company sells 3 type of product, let’s add colors to identify category easily :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/636/1*3zNNkFE3Mf6uYdlhqQinVA.png" /><figcaption>Sometimes you can’t trust your instinct regarding the color palette</figcaption></figure><blockquote>In real life, that’s the moment when Dani from marketing asks you : ”hum but what’s wrong with your colors ?” He’d be right ! Best practice, stick to your company style guide.</blockquote><p>Check this article (in French) from <a href="https://medium.com/u/303ddde344b6">Manon Lay</a> about color harmony :</p><p><a href="https://medium.com/@manon_l/lharmonie-de-couleurs-87d53fb80335">L’harmonie de couleurs</a></p><p>Get back to our report, how to add custom colors ?</p><p>Add a column “category_number”, who applies a number regarding the category label. Furniture = 1, Office = 2 …<br>In power query it should looks like :</p><pre>= Table.AddColumn(#”Renamed Columns”, “Category_number”,<br>each if [Category] = “Furniture” then 1<br>else if [Category] = “Technology” then 2<br>else if [Category] = “Office Supplies” then 3 else null)</pre><p>On your chart, you can then create a conditional formatting like this :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/782/1*p1n_Uyb1TKW7FWUhfU31MQ.png" /><figcaption>There are probably other way to do it, but it’ll do the job here</figcaption></figure><h4>Margins :</h4><p>Let’s use the same color set</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xz9ChILGYiMhDeSOog_ydA.png" /><figcaption>Some products have negative margin, keep that info in mind</figcaption></figure><p>We can dive deeper and check the products margin. The company is selling hundreds of product, it won’t be easy to visualize all of these on a bar chart.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lM2LLjydFYFUyZy9dA6Mxw.png" /><figcaption>Keeping the same color palette help us being consistent in our visualizations</figcaption></figure><h4>Sales :</h4><p>Same job here :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sqPd1Mp1dPGWe9PB0yFO4g.png" /><figcaption>Products sales and subcategories sales</figcaption></figure><h4>How do we compare these 3 values ?</h4><blockquote>Can we identify the best product regarding quantity, sales or profit ?<br>Don’t get lost in your analysis, we’re here to answer questions !</blockquote><p>As we can see on the next image, in this case we have different product / price / quantity and margin, we can’t just compare these values together.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/933/1*9BvP_FpR9o7-DvCv3Z5ujQ.png" /><figcaption>How do you know what’s good or bad here ?</figcaption></figure><h4>Marketing concept : Product mix effect</h4><p>To make it short, the product mix allows you to understand what product contributes the most to the company’s margin. It will gives you an absolute answer to the question.<br>To find it, you multiply contribution to Sales in % by Margin in %.</p><p><a href="https://medium.com/the-crunch/product-mix-the-dark-art-of-blending-marketing-and-accounting-c422c49a4388">Product Mix — the dark art of blending marketing and accounting</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/718/1*aa0t5DkyunJIbUhsm3EhyA.png" /><figcaption>Product / % Sales / % margin / Margin Contribution</figcaption></figure><p>We then add 3 columns to our dataset : contribution to Total sales, margin, margin x contribution to total sales :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*T9f-vhmEIPPVV6DV3Lr1LA.png" /><figcaption>Numbers seem very low, keep in mind the total margin is below 5%</figcaption></figure><p>Here is the ordered list of our <em>best </em>products !</p><h3>5. Question 2: Which products are the most sold together ?</h3><p>Why do we need this info ?</p><ul><li>To understand which products help us selling the other products</li><li>To develop cross sell</li><li>To decide whether or not we keep low margin products</li></ul><blockquote>Spoiler alert : Unfortunately, in this sample dataset we never sold twice the same couple of items, (it was worth it to try, we wouldn’t have found out otherwise), but the idea remains the same.</blockquote><p>Here is one way to get the set of product aggregated and ordered :</p><p>Duplicate your data frame in order to get only the column you need : Order ID et product name.</p><ol><li>Sort by product name (we always want “product A — product B”, otherwise we would have some “product B — product A” mixed )</li><li>Keep only the duplicated values on the “Order ID” column (we want products sold together, so we exclude the unique product orders)</li><li>Group by Order ID :</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/352/1*kg5B7WGszhqfAl5Fw1uHqg.png" /><figcaption>Power BI merge the data in a table, inside the table. <strong>Tableception</strong></figcaption></figure><p>4. Split the newly merged table into 1 column, using this line of custom query :</p><pre>= Table.AggregateTableColumn(#”Grouped Rows”, “Count”, {{“Product Name”, each Text.Combine(List.Transform(_, (x) =&gt; Text.From(x)), “ — , — “), “Desired Result”}})</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/778/1*gX_Ndez5PvpdWc7RgK8EIw.png" /><figcaption>Here is the new column. It contains every order’s products.</figcaption></figure><p>5. Lastly, we can plot the data on a bar chart to visualize the repartition :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/957/1*mAaijwqKRrvJcmaCiveKcA.png" /><figcaption>After all of our research, it happens this dataset is only composed of unique sets of products. Never mind, we have learnt a new approach anyway</figcaption></figure><p>In the real world we would get something like :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/557/1*dhkuPKtRdiHaQTsDWC5F8w.png" /><figcaption>In an other article we will only use Python to analyze a dataset, with a better result on this topic.</figcaption></figure><h3>6. Last Question : How can we create an user friendly dashboard to our marketing friends</h3><p>Despite our effort to keep the Dashboard clean, we need to do some housekeeping to design a robust and user friendly dashboard</p><p>Before :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1006/1*72LOj7nyvchQDBb_d0eQMg.png" /><figcaption>We need to try things out before deciding to keep them on the dashboard</figcaption></figure><p>After :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VLBVpQnZLD1StP4N-5l1TQ.png" /><figcaption>Nothing too sophisticated here, less is more.</figcaption></figure><p>Finally we need to display here :</p><ul><li>A filter panel : here we can filter by category / sub category, City, Date.</li><li>A top row with cards</li><li>Our 4 bar charts</li></ul><blockquote>Why we don’t use pie chart to display the sales repartition ?</blockquote><blockquote><a href="https://www.storytellingwithdata.com/blog/2011/07/death-to-pie-charts">Because Pie chart are evil</a></blockquote><p>We can remove unnecessary visual / table of our dashboard, users will be able to dive deeper using the UI if needed :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WvXjPu2BjnhxZeTqHLlsAw.png" /><figcaption>Here the small bar chart can be expanded, we can also display the correspondent table.</figcaption></figure><h3>Conclusion</h3><p>In this quick analysis, we performed standard and easy data manipulation to understand and analyze a dataset.</p><p>In the real world, real time data analysis (and especially real time dashboard design) often requires multiple iteration between the client (the team who needed the analysis) and you (the data analyst). Final users rarely know exactly what they want, their needs are rarely mature enough. Multiple workshops and iterations are essential.</p><p>If you’re still reading, you probably notice that we performed a superficial analysis here… And you’re right ! In real life, we would need to answer every additional question we found during the exploratory analysis, and probably more topics to analyze once all the said questions are answered.</p><p>Finally, Power BI is a great tool to dive into dataset, and it provides robust and easy-to-use dashboard for end users.</p><p>Credit : <br>Photo by <a href="https://unsplash.com/@tmwd?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Artem Gavrysh</a> on <a href="https://unsplash.com/s/photos/store?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a><br>Dataset on <a href="https://www.kaggle.com/datasets/vivek468/superstore-dataset-final">Kaggle</a><br><a href="https://www.storytellingwithdata.com/books">Story telling with data</a> by <a href="https://www.linkedin.com/in/colenussbaumer/">Cole Nussbaumer Knaflic</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b4be86a29230" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>