Objective: Played the role of Business Intelligence Analyst for AdventureWorks Cycles, a fictional manufacturing company. My responsibility was to transform raw data into professional-quality reports and dashboards to track KPIs, compare regional performance, analyze product-level trends, and identify high-value customers.
STAGE 1: Connecting & Shaping Data
In this stage we’ll focus on building automated workflows to extract, transform, and load our project data using Power Query, and explore common data connectors, storage modes, profiling tools, table transformations, and more:
Data connectors Storage & import modes Query editing tools Table transformations Connecting to a database Extracting data from the web QA & Profiling tools Text, numerical, date & time tools Rolling calendars Index & conditional columns Grouping & aggregating Pivoting & unpivoting Merging & appending queries Data source parameters Importing Excel models
STAGE 2: Creating a Relational Data Model
In stage 2 we’ll review data modeling best practices, introduce topics like cardinality, normalization, filter flow and star schemas, and begin to build our AdventureWorks data model from the ground up:
Database normalization Fact & dimension tables Primary & foreign keys Star & snowflake schemas Active & inactive relationships Relationship cardinality Filter context & flow Bi-directional filters Model layouts Data formats & categories Hierarchies
STAGE 3: Adding Calculated Fields with DAX
In stage 3 we’ll introduce data analysis expressions (DAX). We’ll create calculated columns and measures, explore topics like row and filter context, and practice applying powerful tools like filter functions, iterators, and time intelligence patterns:
DAX vs. M Calculated columns & measures Implicit, explicit & quick measures Measure calculation steps DAX syntax & operators Math & stats functions Conditional & logical functions The SWITCH function Text functions Date & time functions The RELATED function CALCULATE, FILTER & ALL Iterator (X) functions Time intelligence patterns
STAGE 4: Visualizing Data with Reports
Stage 4 is about bringing our data to LIFE with reports and dashboards. We’ll review data viz best practices, building and format basic charts, and add interactivity with bookmarks, slicer panels, parameters, tooltips, report navigation, and more:
Data viz best practices Dashboard design framework Cards & KPIs Line charts, trend lines & forecasts On-object formatting Table & matrix visuals Conditional formatting Top N filtering Map visuals Drill up, drill down & drill-through Report slicers & interactions Bookmarks & page navigation Numeric & field parameters Custom tooltips Importing custom visuals Managing & viewing roles (RLS)
-
KPI Tracking: Effortlessly monitor sales, revenue, profit, and returns with intuitive cards, providing a quick overview of vital metrics. Regional Performance: Delve into regional sales performance via dynamic maps and tables, facilitating swift decision-making processes.
-
Product-Level Trends: Explore in-depth insights into product-level trends with interactive charts, ensuring a thorough understanding of market dynamics.
-
Customer Insights: Pinpoint and prioritize high-value customers based on their purchase history, fostering personalized engagement and retention strategies.
Results:– Adventure Works now harnesses data to inform strategic decisions, streamline operations, and elevate customer satisfaction levels. The dashboard offers a comprehensive view of the business, empowering the management team to navigate the dynamic landscape of manufacturing and cycling with confidence.