Clouds On Mars

Lab Example: An e-commerce store

In todays data-driven world, understanding customer behavior is essential for optimizing the online shopping experience. This lab focuses on a simplified e-commerce store scenario that demonstrates how user interactions can be captured and analyzed using key data entities.

You'll explore a data model consisting of products, product categories, and event tracking—specifically impressions and clicks. These interactions help uncover insights into customer preferences and product performance.

The lab provides examples and real-world context to make the data more relatable and practical. By the end, you will gain hands-on experience in how to build a Fabric Real-Time end-to-end solution to analyze how customers engage with an online storefront—knowledge that is invaluable for making data-informed business decisions.

The e-commerce store database entities are:

• Product: the product catalogue.
• ProductCategory: the product categories.
• events: a click or impression event.

Photo credits: Himiway Bikes, HEAD Accessories, Jan Kopřiva on Unsplash.

1. Login to Lab Environment

1. Open app.fabric.microsoft.com in your browser.

2. Login with provided credentials, if a trial fabric tenant was previously setup (reference Pre-reqs). You may also choose to run the lab in your own Fabric Tenant if you already have one.
3. Click Real-Time Intelligence.

2. Fabric Workspace

1. There is already Fabric workspace assigned to your user. Please use it during the workshop.

3. Create a new Eventhouse

1. Click + New Item.

2. In New item select Eventhouse.

3. In New Eventhouse insert WebEvents_EH as name and click Create. After creation, it opens automatically.

4. Enable OneLake Availability

Also called one logical copy. It lets KQL Database tables be accessed from a Lakehouse, Notebooks, etc in delta-parquet format via OneLake.

Setup

1. Open the KQL Database WebEvents_EH under KQL Databases.

2. Toggle availability to activate OneLake availability.

3. Confirm Apply to existing tables and click Turn on.

5. Create a new Eventstream

We will stream notebook-generated impressions and clicks into an Eventstream, consumed by the Eventhouse KQL Database.

1. Open your Workspace (e.g., WorkSpace.Z.XXX) → + New Item → Eventstream.

2. Name it WebEventsStream_ES, select Enhanced Capabilities, click Create.

3. On Design a flow... click Use Custom Endpoint to create an Event Hub.

4. Source name: WebEventsCustomSource → Add.

5. Click Publish to create the Event Hub.

6. Select source WebEventsCustomSource → Keys → copy Event hub name.

7. Reveal and copy the connection string (primary or secondary).

6. Import Data Generator Notebook

Use the Python notebook Generate_synthetic_web_events.ipynb.

1. Open the notebook in GitHub → Download raw file.

Import it to your Fabric Workspace:

2. Go to workspace (e.g., RTI Tutorial) → Import → Notebook → From this computer.

3. In Import status select Upload.

4. Browse and open the downloaded notebook. Fabric will confirm successful import.

7. Run the notebook

1. Open Generate_synthetic_web_events in your workspace.

2. Paste eventHubNameevents and eventHubConnString from Task 5.

3. Click Run all.

4. Scroll to last code cell; confirm JSON events output appears.

8. Define Eventstream topology

1. Open Eventstream WebEventStream_ES → Edit.

3. Add Filter → name ClickEventsFilter to keep eventType == CLICK.

6. Connect a Stream named ClickEventsStream (JSON) → destination Eventhouse → create table BronzeClicks.

12. Add a second Filter ImpressionEventsFilter for eventType == IMPRESSION → Stream ImpressionsEventsStream (JSON) → Eventhouse table BronzeImpressions.

21. Click Publish. After a few minutes, destinations switch to Streaming.

9. Setting up the Lakehouse

Download products.csv and productcategory.csv from the repo and create a Lakehouse WebSalesData_LH (schemas disabled).

10. Uploading reference data files and creating delta tables

1. Click Get data → Upload Files → select both CSV files → Upload.

5. For each file, use Load to tables → New table → retain defaults → Load.

11. Accessing Eventhouse data from the Lakehouse

Create OneLake shortcuts for BronzeClicks and BronzeImpressions.

1. Get data → New shortcut (or New table shortcut under schema).

2. Select Microsoft OneLake.

3. Choose KQL Database WebEvents_EH → Next.

4. Check tables BronzeClicks and BronzeImpressions → Next → Create.

12. Build the KQL DB schema

Create silver tables, functions, and update policies. Add OneLake shortcuts for products and productcategory from the lakehouse.

1. Open KQL Database WebEvents_EH → + New → OneLake shortcut.

3. Select Microsoft OneLake → lakehouse WebSalesData_LH → Next.

5. Add shortcut to table products (repeat for productcategory).

7. Click Explore your Data.

8. Open createAll.kql from GitHub and copy contents.

9. Open queryset WebEvents_EH_queryset, paste contents, Run. Rename tab to createAll.

10. Expand folders to verify all tables and functions created by the script.

13. Real-Time Dashboard

Build a dashboard to visualize streaming data and set auto-refresh to 30 seconds (or Continuous). Optionally import a pre-built JSON definition.

1. Workspace → + New Item → Real-Time Dashboard → name Web Events Dashboard.

4. + Add tile → + Data source → select Eventhouse WebEvents_EH → Add.

Clicks by hour

//Clicks by hour
SilverClicks
| where eventDate between (_startTime.._endTime)
| summarize date_count = count() by bin(eventDate, 1h)
| render timechart
| top 30 by date_count

1. Set time range to Last 7 days → Run → + Add Visual.
2. Title: Click by hour. Visual type: Area chart → Apply changes.

3. Manage → Parameters → edit Time range default to Last 7 Days.

Impressions by hour

//Impressions by hour
SilverImpressions
| where eventDate between (_startTime.._endTime)
| summarize date_count = count() by bin(eventDate, 1h)
| render timechart
| top 30 by date_count

Impressions by location

//Impressions by location
SilverImpressions
| where eventDate  between (_startTime.._endTime)
| join external_table('products') on $left.productId == $right.ProductID
| project lon = toreal(geo_info_from_ip_address(ip_address).longitude), lat = toreal(geo_info_from_ip_address(ip_address).latitude), Name
| render scatterchart with (kind = map)

Average Page Load time

//Average Page Load time
SilverImpressions
| where eventDate   between (_startTime.._endTime)
| make-series average_loadtime = avg(page_loading_seconds) on eventDate from _startTime to _endTime+4h step 1h
| extend forecast = series_decompose_forecast(average_loadtime, 4)
| render timechart

Impressions, Clicks & CTR

//Clicks, Impressions, CTR
let imp =  SilverImpressions
| where eventDate  between (_startTime.._endTime)
| extend dateOnly = substring(todatetime(eventDate).tostring(), 0, 10)
| summarize imp_count = count() by dateOnly;
let clck = SilverClicks
| where eventDate  between (_startTime.._endTime)
| extend dateOnly = substring(todatetime(eventDate).tostring(), 0, 10)
| summarize clck_count = count() by dateOnly;
imp
| join clck on $left.dateOnly == $right.dateOnly
| project selected_date = dateOnly , impressions = imp_count , clicks = clck_count, CTR = clck_count * 100 / imp_count

Average Page Load Time Anomalies

//Avg Page Load Time Anomalies
SilverImpressions
| where eventDate   between (_startTime.._endTime)
| make-series average_loadtime = avg(page_loading_seconds) on eventDate from _startTime to _endTime+4h step 1h
| extend anomalies = series_decompose_anomalies(average_loadtime)
| render anomalychart

Strong Anomalies

//Strong Anomalies
SilverImpressions
| where eventDate between (_startTime.._endTime)
| make-series average_loadtime = avg(page_loading_seconds) on eventDate from _startTime to _endTime+4h step 1h
| extend anomalies = series_decompose_anomalies(average_loadtime,2.5)
| mv-expand eventDate, average_loadtime, anomalies
| where anomalies <> 0
| project-away anomalies

Logo (Markdown Text Tile)

![AdventureWorks](https://vikasrajput.github.io/resources/PBIRptDev/AdventureWorksLogo.jpg "AdventureWorks")

Arrange visuals; the dashboard should look similar to:

Auto-refresh

1. Manage → Auto refresh → Enabled → Default refresh rate: Continuous → Apply.
2. Home → Save.

14. Data Activator

Create a Reflex Alert to send a Teams message when a value meets a threshold.

1. On tile Click by hour → Set alert.

2. Set values: On each event grouped by → event_date, When: date_count, Condition: Becomes greater than → value 250. Action: Message me in teams.

3. Select Workspace → Create a new item → name My Reflex → Create.

Stop running the notebook when done.

4. Open Generate synthetic events → Cancel all.