ANALISI VENDITE
MODENA CORPORATION
Alfredo Adinolfi Borea, Laura Corsini, Leonardo Drahorad
SALES ORDER DETAIL
MODELLAZIONE CONCETTUALE
E PROGETTAZIONE LOGICA
SCHEMA E/R
SalesPerson
ID
(1,n)
SalesOrderDetailID
Category
(1,1)
Name
DETAIL
(1,n)
(1,1)
FK
7
FK
1
Header
(1,n)
(1,1)
DueDate
SubCategory
Name
(1,n)
FK
3
(1,n)
FK
6
FK
2
(1,1)
(1,1)
Name
FK
8
ID
ProductNumber
Name
(0,n)
TerritoryID
(1,1)
Product
ProductID
(0,n)
FK
4
(1,1)
SpecialOffer
Product
Territory
(1,1)
FK
5
(1,n)
SpecialOffer
ID
Description
Nr
DATA PROFILING
MINIMALITÀ DELLE CHIAVI
SalesOrderDetailID
DETAIL
(1,1)
SalesOrderDetailID
FK
1
DETAIL
(1,1)
FK
1
ALTERNATIVE KEY
Name
Territory
Territory
TerritoryID
TerritoryID
DIPENDENZE FUNZIONALI
Name
Name
TerritoryID
CountryRegionCode
Group
TerritoryID
CountryRegionCode
Group
ALBERO DEGLI ATTRIBUTI
SCHEMA DI FATTO
GRANULARITÀ: TEMPORALE
SALESPERSON  TERRITORY
GLOSSARIO DELLE MISURE
Nome
Tipo
Alimentazione
Aggregazione
Non Aggr.
ProdottiVenduti
Normale
SUM(OrderQty)
SUM
{}
PrezzoSomma
Normale
SUM(UnitPrice)
SUM
{}
Ricavo
Normale
SUM(OrderQty*UnitPrice)
SUM
{}
ScontoSomma
Normale
SUM(DiscountPct)
SUM
{}
PrezzoMedio
Calcolata
PrezzoSomma
/ProdottiVenduti
{}
ScontoMedio
Calcolata
ScontoSomma /
ProdottiVenduti
{}
RicavoListino
Calcolata
Ricavo /
(1 - ScontoMedio)
{}
ScontoApplicato
Calcolata
RicavoListino –
Ricavo
{}
SNOWFLAKE SCHEMA
FACT_TABLE
Sales Order Detail
DT_SalesPerson
SalesPerson
CommissionPct
ProductCode
SpecialOffer
SalesPerson
Territory
DT_Territory
Name
SalesLastYear
SalesYTD
CountryRegion
DueMonth
ProdottiVenduti
Zone
ProductCode
Name
Selling
ProductLine
Subcategory
PrezzoSomma
ScontoSomma
DT_SpecialOffer
Ricavo
SpecialOffer
DT_Subcategory
Subcategory
Category
Type
DT_CountryRegion
CountryRegion
DT_Product
DT_Month
Category
Month
StartMonth
MonthName
EndMonth
Year
DOCUMENTAZIONE: CONVERGENZA SU TERRITORY DA SALESPERSON
(SE SALESPERSON <> ‘NO-SALESPERSON’)
FACT TABLE
CREATE VIEW FACT_TABLE AS
SELECT
Product.ProductNumber
as ProductCode,
Detail.SpecialOfferID
as SpecialOffer,
coalesce(cast(Header.SalesPersonID as
varchar(14)),'No-SalesPerson’)
as SalesPerson,
str(year(Header.Duedate),4)
+str(month(Header.DueDate),2)
as DueMonth,
cast(Territory.Name as varchar(50))
as SalesTerritory,
SUM(Detail.OrderQty)
as ProdottiVenduti,
SUM(Detail.UnitPrice)
as PrezzoSomma,
SUM(Detail.OrderQty * Detail.UnitPrice)
as Ricavo,
SUM(SpecialOffer.DiscountPct)
as ScontoSomma
FROM
Detail
left join Header
using (Detail.SalesOrderID)
left join SpecialOffer
using (SpecialOfferID)
left join Territory
using (TerritoryID)
left join Product
using (ProductID)
GROUP BY
Product.ProductNumber,
Detail.SpecialOfferID,
Header.SalesPersonID,
str(year(Header.Duedate),4)
+ str(month(Header.DueDate),2),
Territory.Name
SALES TERRITORY
HISTORY
MODELLAZIONE CONCETTUALE
E PROGETTAZIONE LOGICA
SCHEMA E/R
StartDate
SalesPerson
TerritoryHistory
FK1
(1,1)
(0,n)
LoginID
Employee
NationalIDNumber
OrganizationNode
Person
BusinessEntityID
TerritoryID
EndDate
Territory
FK2
(1,1)
(0,n)
Name
DATA PROFILING
MINIMALITÀ DELLE CHIAVI
StartDate
FK
1
TerritoryHistor
y
StartDate
FK
2
FK
1
TerritoryHistor
y
FK
2
ALTERNATIVE KEY
LoginID
Employee
TerritoryID
NationalIDNumber
Territory
Name
OrganizationNode
DIPENDENZE FUNZIONALI
Name
JobTitle
SalesPerson
Bonus
CountryRegionCode
Territory
Employee
Group
SalariedFlag
SalesQuota
ALBERO DEGLI ATTRIBUTI
SCHEMA DI FATTO
GRANULARITÀ: TRANSAZIONALE
SALESPERSON, SNAPSHOTMONTH  TERRITORYID
GLOSSARIO DELLE MISURE
SNAPSHOTMONTH
COUNT(DISTINCT SALESPERSON)
AGGREGAZIONE: SUM
NON AGGREGABILITÀ: {SNAPSHOTMONTH}
SalesPerson
TERRITORY
VENDITORI
COUNT
DISTINCT
COUNT
DISTINCT
∅
COUNT
DISTINCT
COUNT
DISTINCT
∅
Σ
Σ
∅
TerritoryID
SnapshotMonth
SNOWFLAKE SCHEMA
FACT_TABLE
Sales Territory
History
DT_Territory
VendorIDNumber
DT_Vendor
SnapShotMonth
NationalIDNumber
Territory
CommissionPct
Name
HireYear
SalesLastYear
CurrentFlag
SalesYTD
Gender
CountryRegion
BirthYear
JobTitle
DT_JobTitle
JobTitle
SalariedFlag
DT_SnapShotMonth
DT_CountryRegion
CountryRegion
Zone
SnapshotMonth
MonthName
Year
Manager
FACT TABLE
CREATE VIEW FACT_TABLE AS
SNAPSHOTMONTH
WITH FACT_TABLE_TEMP AS (
SELECT
VendorIDNumber
cast(t.Name as varchar(50))
as Territory,
SALESPERSON
TERRITORY
e.NationalIDNumber
VendorIDNumber
Territory
as VendorIDNumber,
s.SnapshotMonth,
(select COUNT(*) from STH
FACT_TABLE_TEMP
where STH.TerritoryID = t.TerritoryID
and STH.BusinessEntityID = e.BusinessEntityID
Territory
and CAST(convert(varchar,s.Anno
VendorIDNumber
+'-’+convert(varchar,s.Mese)
SnapshotMonth
+'-’+convert(varchar,15)
as datetime)
Attivo = {0,1}
BETWEEN
STH.StartDate AND coalesce(STH.EndDate,getdate())
Attivo = 1
) as Attivo
FROM Territory t, SalesPerson, SnapshotMonth s, Employee
FACT_TABLE
e
Territory
WHERE SalesPerson.BusinessEntityID = e.BusinessEntityID
VendorIDNumber
)
SELECT Territory, VendorIDNumber, SnapshotMonth
SnapshotMonth
FROM FACT_TABLE_TEMP WHERE Attivo=1
×
REPORT
BUSINESS OBJECT
UNIVERSI
SALESORDERDETAIL
SALESTERRITORYHISTORY
FATTURATO DEI VENDITORI
ANALISI ABC
REPORT LIVE
BUSINESS OBJECT
GRAZIE PER L’ATTENZIONE
MODENA CORPORATION
PREZZO MEDIO DI CATEGORIA
QUESTO REPORT ANALIZZA L’ANDAMENTO NEL TEMPO DEI PREZZI DEI TRE TIPI DI
BICICLETTE VENDUTI: MOUNTAIN BIKES, BICI DA CORSA E TOURING BIKES.
LA CATEGORIA DI ANALISI È MODIFICABILE TRAMITE PROMPT.
PROMOZIONI
LA TABELLA ANALIZZA I PRODOTTI CHE SONO STATI VENDUTI ASSOCIATI AD UN’OFFERTA
SPECIALE E QUINDI SCONTATI, CALCOLANDO QUANTO RICAVO SI È PERSO.