Microsoft SSMS Database to Power BI Desktop & Service

Contoso Retail: Competitive Sales Channel & Marketing Analysis

Front Page Cover Images

Table of Content

Project Title:

Contoso Competitive Sales Channel Analysis

My Role

Power BI Developer/Data Analyst

Project Overview

This project analyses Contoso’s multi-channel retail operations (Store, Online, Reseller, Catalog) to uncover sales trends, profit margins, and geographic performance using a SQL-based backend. Interactive Power BI dashboards support executive-level decisions regarding marketing strategy, resource allocation, and inventory planning.

Problem Statement

Contoso lacked clear visibility into how different sales channels, products, and regions performed over time. Key business questions included:

This project resolves these issues by delivering a suite of Power BI dashboards with intuitive visualizations and actionable metrics.

Stakeholder Engagement

Target Stakeholder

Use Cases

Stakeholder Stories

Acceptance Criteria

Success Criteria

Data Source

ContosoRetailDW Database Structure

ContosoRetailDW is a sample data warehouse database provided by Mircosoft, designed to support intelligence and analytics scenarios. It represent a a fictional retail company, Contoso, and includes data relevant to sales, products, iventory, promotions, and customer behaviour.

The database follows a star schema structure, commonly used in data warehousing, with FactSales, FactInventory, FactSalesOnline but for this project only FactSales sales table was query for our analysis which contain record of measurable business event, and dimension tables like DimProduct, DimCustomer, DimStore, and DimDate that provide contextual information.

Contoso ER Diagram

Fact Table

Column Name Data Type Description
SalesKey Whole Number Unique Identifier of each sales
DateKey Date Date of each sales
ChannelKey Whole Number Foreign key referencing channel table (ChannelKey)
StoreKey Whole Number (Delete) not needed
SalesKey Whole Number Unique Identifier of each sales
DateKey Date Date of each sales
ChannelKey Whole Number Foreign key referencing channel table (ChannelKey)
ProductKey Whole Number Forforeign key referencing product table (Productkey)
SalesKey Whole Number Unique Identifier of each sales
DateKey Date Date of each sales
ChannelKey Whole Number Foreign key referencing channel table (ChannelKey)
UnitCost Fixed decimal number Cost per product sold
UnitPrice Fixed decimal number unit price per product sold
SalesQuantity Whole Number qauntity of product sold
ReturnedQuantity Whole Number Quantity of product sold amount returned
ReturnedQuantity Fixed decimal number Returned Sold Amount
DiscountQuantity Whole Number qauntity of product that is being discounted
DiscountAmount Fixed decimal number amount on discounted qauntity
TotalCost Fixed decimal number cost of goods sold
Sales fixed decimal number products qauntity sales
Gross_Margin Fixed decimal number Difference between sales and total cost amount

Dimension Tables

Column Name Data Type Description
ProductCategoryKey Whole Number Unique identifier for each Product Category
ProductCategoryLabel Text Deletd
ProductCategoryName Text Name of each product category
ProductCategoryDescription Text short note to decribe each product category
Column Name Data Type Description
ProductSubCategoryKey Whole Number Unique identifier for each Product Subcategory
ProductSubCategoryName Text Name of each product subcategory
ProductSubCategoryDescription Text short note to describe the product subCategory
ProductCategoryKey Whole Number Foreign key referencing product category table (ProductCategoryKey)
Column Name Data Type Description
ProductKey Whole Number Unique identifier for each product
ProductLabel Text (Delete) not needed in this analysis
ProductName Text Name of selling product
ProductDescrition Text short note of product description
ProductSubcategory Whole Number Foreign key referencing product Product Subcategory (ProductSubcategoryKey)
Manufacturer Text Manufacturer of contoso products
BrandName Text products brand name
ColorID Text (Delete) not needed
ColorName Text (Delete) not needed
StockTypeID Whole Number (Delete) not needed
StockTypeNmae Text (Delete) not needed
UnitCost Fixed decimal number Cost per product sold
UnitPrice Fixed decimal number unit price per product sold
AvailableForSaleDate Date/Time (Delete) Date when product would be available for sale
StopSaleDate (Delete) Date/Time Date when product is stopped being sold
Status Text (Delete) not needed
Column Name Data Type Description
Channelkey Whole Number Unique Identifier of each channel
ChannelLabel Text (Delete) not needed
ChannelName Text name of each Sales channel
ChannelDescription Text short note to explain channel further
LoadDate Date/Time (delete) not needed
UpdateDate Date/Time (delete) not needed
Column Name Data Type Description
GeographyKey Whole number Unique identifier to DimGeopgraphy
ContinentName Text Continent of each customers
SateProvinceName Text store or customer Continent Name
RegionCountryName Text store or customer countryname
Column Name Data Type Description
StoreKey Whole Number Unique Key Identifier of each store
GeographyKey Whole Number Foreign Key referencing the Geograpgy table (GeographyKey)
StoreManger Whole Number this a number that is attached to each manager
StoreType Text Available store type used for sales of goods
StoreName Text Name of each store
Status Text This indicate if the store is still active or shutdown
OpenDate Date/Time Date the store will shutdown from dervices
ColseDate Date/Time Date the store was close for services
AdressLine1 Text Address to the sotres
EmployeeCount Whole Number Total number of employee for each store
GeoLocation Text Longitude and Latitude point of each store Location
Geometery Text longitude or latitude point of each store location

This table is not needed for now but for future purpose is would be needed so in that case while cleaning transforming data, the table would not be loaded in the report pane.

Time Intelligence Date Table

Column Name Data Type Description
Date Date Full date of day/Month/Year and a unique identifier
Year Whole Number Calendar year
Month Whole Number month number of calendar year
Month Name Text Month name of calendar name
Quarter Whole Number Quarter of calendar year
Month Short Text Uses the first three character of each month
Period Text Combination of year and month of the year
FY - Month Whole Number Fiscal year month
FY - Year Whole Number Fiscal year
FY - Quarter Whole Number Fiscal year quarter

Benefit of Microsoft SSMS Storage.

Methodology

Tools Used

Development

General Approach to Creating the Solution:

  1. Project Planning & Requirement Gathering
  2. Data Exploration & Profiling
  3. Data Exploration & Profiling
  4. ETL Process Using Power Query
  5. Data Modelling
  6. Measure Development Using DAX
  7. Dashboard Design & Visualization
  8. Publishing and Collaboration
  9. Documentation & Version Control
  10. Review & Iteration

Project Planning & Requirement Gathering

Data Exploration & Profiling

ETL Process Using Power Query

Data Modelling

Contoso semantic model

From the above Power BI data model pane image above we can see that a many to one (*:1) was establish between the FactSales (The Fact Table) and Dimensions Table (Product, Customer, and date table).The other tables shown are the Dax measure and measure documentation tables.

Measure Development Using DAX

% Return_Quantity = 
 DIVIDE(
    SUM(FactSales[ReturnQuantity]),
    SUM(FactSales[SalesQuantity]),
    0
)

% Total_Cost = 
DIVIDE(
    SUM(FactSales[TotalCost]),
    SUM(FactSales[Sales]),
    0
)

Profit Margin = 
DIVIDE(
    SUMX(FactSales, FactSales[Gross Profit]), 
    SUM(FactSales[Net Sales]), 
    0
)

Profit Margin = 
DIVIDE(
    SUMX(FactSales, FactSales[Gross Profit]), 
    SUM(FactSales[Net Sales]), 
    0
)

Profit Margin % by BrandName = 
DIVIDE(
    SUM(FactSales[Gross Profit]), 
    CALCULATE(SUM(FactSales[Net Sales]), ALL(DimProduct[BrandName])), 
    0
)

Profit Margin % by Channel = 
DIVIDE(
    SUM(FactSales[Gross Profit]), 
    CALCULATE(SUM(FactSales[Net Sales]), ALL('DimChannel'[ChannelName])), 
    0
)

Profit Margin % by Product = 
DIVIDE(
    SUM(FactSales[Gross Profit]), 
    CALCULATE(SUM(FactSales[Net Sales]), ALL('DimProductSubcategory'[ProductSubcategoryName])), 
    0
)

YoY Catalog Store = 
VAR CurrentYear = MAX('Date'[Year])
VAR PreviousYr = CurrentYear - 1
VAR CurrentSales = 
    CALCULATE(
        SUM('FactSales'[Sales]),
        'Date'[Year] = CurrentYear,
        'DimChannel'[ChannelName] = "Catalog"
    )
VAR PreviousSales = 
    CALCULATE(
        SUM('FactSales'[Sales]),
        'Date'[Year]= PreviousYr,
        'DimChannel'[ChannelName] = "Catalog"
    )
RETURN 
    CurrentSales - PreviousSales

YoY Catalog Store (%) = 
VAR CurrentYear = MAX('Date'[Year])
VAR PreviousYr = CurrentYear - 1
VAR CurrentSales = 
    CALCULATE(
        SUM('FactSales'[Sales]),
        'Date'[Year] = CurrentYear,
        'DimChannel'[ChannelName] = "Catalog"
    )
VAR PreviousSales = 
    CALCULATE(
        SUM('FactSales'[Sales]),
        'Date'[Year] = PreviousYr,
        'DimChannel'[ChannelName] = "Catalog"
    )
RETURN 
    IF(
        PreviousSales <> 0,
        DIVIDE(CurrentSales - PreviousSales, PreviousSales),
        BLANK()  // This handles the scenario where there are no previous year sales to compare against.
    )

Dashboard Design & Visualization

Publishing and Collaboration

link to power BI service !!!

Using the link below will enable you to gain full access to the interactive reports and semantic model behind the success of this project.

View Report

Documentation & Version Control

Review & Iteration

Detailed Insights and Recommendation

DASHBOARD 1

COMPETITIVE SALES CHANNEL ANALYSIS REPORT (Executive Overview)

Dashboard 1

**View: Executive Overview Year: 2008**

Headline KPIs

📌 Insight:

Total Sales by Channel

📌 Insight: Physical stores still lead, but online and reseller channels make up over 37%—these should be prioritized for investment.

Total Sales by Product and Channel

📌 Insight:

Total Sales by Channel Over Time

📌 Insight:

Strategic Takeaways for Stakeholders

  1. Address the Decline in Store Sales
    • Investigate causes (e.g., competition, pricing, service issues).
    • Consider omnichannel strategies to retain foot traffic.
  2. Invest in Growing Channels (Online & Reseller)
    • Scale digital presence, optimize delivery and user experience.
    • Build reseller partnerships or loyalty programs.
  3. Phase Out or Reimagine Catalog Channel
    • With declining sales and relevance, consider digital catalogs or targeted mailers only for high-value customers.
  4. Match Products with Their Best Channels
    • Example: Focus camcorder and projector promotions on catalog and online channels.
  5. Capitalize on Seasonal Trends
    • Plan promotions and inventory spikes for store sales in Q4.
    • Launch mid-year campaigns to accelerate online sales momentum.

DASHBOARD 2

COMPETITIVE SALES CHANNEL ANALYSIS REPORT (Sales by country & Brand(Top5))

Dashboard 1

Focus: Regional sales distribution and brand/channel contribution.

KPI CARDS(Top Section):

Key Insights: Store sales dominate, contributing over 57% of total sales, followed by online and reseller channels. Catalogue sales trail behind, suggesting potential for digital transformation or optimization.

Channel-by-Brand Breakdown:

📌 Insight:

Total Sales by Country and Channel:

📌 Insight: Geographic sales patterns show where to allocate resources. For instance, North America’s store dominance might benefit from in-store promotions, while Asia’s online strength suggests expanding digital campaigns.

Strategic Takeaways for Stakeholders

  1. Double Down on Store Channel:
    • Still the highest performer across all regions and brands. Optimize store experience and regional marketing.
  2. Grow Online Sales with Top Brands:
    • Contoso and Fabrikam are performing well online. Scale their digital campaigns and promotions.
  3. Develop Reseller and Catalogue Channels Strategically:
    • Lower performance here indicates room for partnerships, incentives, or streamlining catalog operations.
  4. Expand in High-Growth Regions:
    • Asia and parts of Europe show online and mixed-channel potential. Focus on local digital strategies.
  5. Evaluate Brand Penetration:
    • Contoso is a top brand across channels—identify what’s working and apply best practices to underperforming brands.

DASHBOARD 3

COMPETITIVE SALES CHANNEL ANALYSIS REPORT (Profit Margin)

Dashboard 1

Focus: Profit Margin (%) Time Period: 2007–2009

🔑 Key Insights & Visual Interpretation Overall Store Profit Margin 33.94%

This KPI gauge shows that, on average, the store generates a 33.94% profit margin, which is relatively healthy.

Store Profit Margin by Country Profit margin is geographically visualized across North America, Europe, Asia, and parts of Africa and Australia.

Darker/larger dots indicate higher profit margins in certain regions—likely concentration in Western Europe, North America, and parts of Asia.

Insight:
This helps identify geographies that outperform or underperform, guiding region-specific pricing or marketing strategies.

Store Profit Margin by Product Subcategory Top-performing subcategories based on profit margin %:

📌 Insight: Focus on high-margin categories like camcorders and projectors for promotional campaigns or bundling. Low-margin items may need pricing review, inventory reduction, or promotion reconsideration.

Sales & Profit Margin Over Time

📌 Insight: Monitor high-sales months (e.g., March, May, October) for stock planning and marketing pushes.
Align marketing with margin trends to avoid high sales with low profitability.

Strategic Takeaways for Stakeholders