Welcome to the AdventureWorks Data Analysis documentation! This comprehensive guide will walk you through the process of importing and analyzing data from the AdventureWorks sample database. We will cover everything from creating views for importing data to constructing a star schema, creating measures, and charts, and delivering a compelling sales story.
AdventureWorks Sample Database: Install and Configure
Conductivity Mode: Import
vw_DimProducts
vw_DimSalesPersons
vw_DimShipMethods
vw_DimStatuses
vw_DimTerritories
vw_FactOrderDetails
This file contains SQL code snippets for creating views to import data.
DimDate (Created using Power Query)
Number.ToText(Date.Year([Date])) &
(
if Date.Month([Date]) < 10 then
"0" & Number.ToText(Date.Month([Date]))
else
Number.ToText(Date.Month([Date]))
) &
(
if Date.Day([Date]) < 10 then
"0" & Number.ToText(Date.Day([Date]))
else
Number.ToText(Date.Day([Date]))
)
-
General Information:
Number of Rows: 121317
Number of Columns: 18 -
Column Details:
-
SalesOrderID:
Data Type: Integer
Null Values: 0
Unique Values: 31465
Min Value: 43659
Max Value: 75123 -
SalesOrderDetailsID:
Data Type: Integer
Null Values: 0
Unique Values: 121317
Min Value: 1
Max Value: 121317 -
ProductID:
Data Type: Integer
Null Values: 0
Unique Values: 266
Min Value: 707
Max Value: 999 -
SalesPersonID:
Data Type: Integer
Null Values: 60398
Unique Values: 18
Min Value: 274
Max Value: 290 -
ShipMethodID:
Data Type: Integer
Null Values: 0
Unique Values: 2
Min Value: 1
Max Value: 5 -
StatusID:
Data Type: Integer
Null Values: 0
Unique Values: 1
Min Value: 5
Max Value: 5 -
TerritoryID:
Data Type:Integer
Null Values: 0
Unique Values: 10
Min Value: 1
Max Value: 10 -
OrderDateKey:
Data Type: Integer
Null Values: 0
Unique Values: 1124
Min Value: 20110531
Max Value: 20140630 -
ShipDateKey:
Data Type: Integer
Null Values: 0
Unique Values: 1124
Min Value: 20110607
Max Value: 20140707 -
DueDateKey:
Data Type: Integer
Null Values: 0
Unique Values: 1124
Min Value: 20110612
Max Value: 20140712 -
OrderQty:
Data Type: Integer
Null Values: 0
Unique Values: 41
Min Value: 1
Max Value: 44 -
OnlineOrderFlag:
Data Type: Boolean
Null Values: 0
Unique Values: 2 -
UnitPrice:
Data Type: Integer
Null Values: 0
Unique Values: 287
Min Value: 1.3282
Max Value: 3578.27 -
LineTotal:
Data Type: Integer
Null Values: 0
Unique Values: 1488
Min Value: 1.37
Max Value: 27893.62 -
Taxamt:
Data Type: Integer
Null Values: 0
Unique Values: 52513
Min Value: 0.11
Max Value: 2867.61 -
Fright:
Data Type: Integer
Null Values: 0
Unique Values: 51944
Min Value: 0.03
Max Value: 896.13 -
TotalDue:
Data Type: Integer
Null Values: 0
Unique Values: 55012
Min Value: 1.45
Max Value: 31657.23 -
SalesAmount:
Data Type: Integer
Null Values: 0
Unique Values: 1468
Min Value: 1.374
Max Value: 30992.91
SalesAmount = 'vw_Fact|OrderDetails'[UnitPrice] * 'vw_Fact|OrderDetails'[OrderQty] -
MeasuresTable =
SUMMARIZECOLUMNS (
'vw_Fact|OrderDetails'[OrderDateKey],
'vw_Fact|OrderDetails'[DueDateKey],
'vw_Fact|OrderDetails'[ShipDateKey],
"Orders by Order Date", 'vw_Fact|OrderDetails'[OrdersByOrderDate],
"Orders by Ship Date", 'vw_Fact|OrderDetails'[OrdersByDueDate],
"Orders by Due Date", 'vw_Fact|OrderDetails'[OrdersByShipDate],
"Total SubTotal", [SubTotal],
"Total Tax", [TaxAmount],
"Total Freight", [Total Freight],
"Total Due", [Total Due]
)
- No. of Orders by Order Date Measure
OrdersByOrderDate = CALCULATE( COUNTROWS( VALUES('vw_Fact|OrderDetails'[SalesOrderID]) ), USERELATIONSHIP( 'vw_Fact|OrderDetails'[OrderDateKey], DimDate[DateKey] )) - No. of Orders by Ship Date Measure
OrdersByShipDate = CALCULATE( COUNTROWS( VALUES('vw_Fact|OrderDetails'[SalesOrderID]) ), USERELATIONSHIP( 'vw_Fact|OrderDetails'[ShipDateKey], DimDate[DateKey] ) ) - No. of Orders by Due Date Measure
OrdersByDueDate = CALCULATE( COUNTROWS( VALUES('vw_Fact|OrderDetails'[SalesOrderID]) ), USERELATIONSHIP( 'vw_Fact|OrderDetails'[DueDateKey], DimDate[DateKey] ) ) - Total SubTotal Measure
SubTotal = SUM('vw_Fact|OrderDetails'[LineTotal]) - Total Tax Measure
TaxAmount = SUM('vw_Fact|OrderDetails'[Taxamt]) - Total Freight Measure
Total Freight = SUM('vw_Fact|OrderDetails'[Freight]) - Total Due Measure
Total Due = SUM('vw_Fact|OrderDetails'[TotalDue])
- Card for Each Chart (Order Date): Display data by Order Date using cards.
- Max Qty per Product Chart: Visualize the maximum quantity per product.
- Matrix Chart (Territories, Years, Values: Total Sales, Total Canada Sales): Row-wise breakdown of territories, column-wise breakdown of years, and values represented by total sales and total Canada sales.
- QA Chart: Quality Assurance chart for data validation.
- Drilldown and Drill Through: Implement drilldown and drill through functionality for enhanced exploration.
- Product Hierarchy Chart: Create a hierarchy for products.
- Tooltip for Sales: Utilize tooltips to show sales associated with order quantity for each graph.
- Color Theme (Exported as JSON): Create and export a custom color theme for visual consistency.
In this Power BI report, I've utilized advanced visualization techniques to enhance user interaction and provide deeper insights:
-
Tooltips: Tooltips offer contextual information without cluttering visuals, providing a seamless way to access additional details when hovering over specific data points.
-
Drilldown: Drill down functionality allows users to explore hierarchical data at different levels, promoting a more detailed view of the data hierarchy, and enhancing user exploration.
-
Drillthrough: Drillthrough enables users to access detailed information related to specific data points, fostering in-depth analyses based on specific queries.
-
Interactive Charts: Interactive charts, like the Matrix Chart and QA Chart, empower users to dynamically interact with data, selecting and filtering points for a tailored visualization.
-
Color Theme: A custom color theme ensures visual consistency, contributing to a cohesive and visually appealing presentation of data.