[Sql분석] SCM Process (1)

김보림·2024년 7월 30일

SQL 분석

목록 보기

32/33

목표 : SCM Process 별 모니터링용 Power BI 대시보드 만들기

구매 데이터 : Vendor별 실적 모니터링

재고 데이터 : 기초재고와 기말재고 Gap 모니터링

고객 판매 데이터 : 날짜, 요일별 판매 트렌드 모니터링

SCM 이란? (Supply Chain Management) 
- 공급망 관리

생산자, 공급자, 고객에 이르는 물류의 흐름을 하나의 가치사슬 관점에서 파악하고
공급망의 구성요소들 간에 이루어지는 전체 프로세스 최적화를 달성 하고자 하는 경영 혁신 기법

우리가 현재 가지고 있는 csv파일

구매 관련 데이터
- Invoice_purchases
- Purchases
재고 관련 데이터
- BeginInv
- EndInv
고객판매 관련 데이타
- Sales

Vendor 별 실적 모니터링

1. 데이터 불러오기

csv파일 가져오기
데이터끼리 컬럼타입과 컬럼이름이 동일할 경우 Power BI에서 자동으로 key로 인식해 연결하는 경우가 있음
이를 방지하고 싶다면 설정을 바꿔주면 됨
파일 -> 옵션 및 설정 -> 데이터 설정

관계에 해당하는 설정들은 선택해제하면 됨

2. 데이터 살펴보기

Invoice Purchases 와 Purchases의 관계를 살펴보자

Invoice Purchases에는 PONumber의 값이 고유한 개수로 들어있고
Purchase는 동일하게 PONumber가 들어있지만 중복되어 들어가 있다
이를 봤을 때 Invoice Purchases 와 Purchase는 1대 다로 연결 가능

Purchases의 Inventoryid는 store + Vendorname + Brand 인것을 확인

PurchasePrice는 Purchases테이블에만 있는데 살펴보면 제품의 단가임
PurchasePrice * Quantity = Dollars
고로 Dollars는 판매액

3. 차트생성

판매자별 PO개수, 총 매출, 총 운임비, 총 상품개수, 평균 가격을 확인할 테이블 생성
VendorNumber의 경우 숫자계산될 일이 없으니 텍스트로 변경해주는 편이 편함

Vendorname으로 만든 슬라이이서 생성
옵션 -> 드롭다운 / 선택 -> 모두선택 으로 다중선택이 가능하도록 설정
추가적으로 검색옵션을 추가해서 검색을 통해 찾을 수 있게 할 수 있음
점3개 클릭 -> 검색

PO Date별로 판매액, 판매량을 알 수 있는 시계열 차트 생성

x축에 해당하는 날짜는 우클릭을 통해 PO Date를 선택하면 드롭다운하지 않고 전체적으로 확인할 수 있음 (드롭다운 아이콘 사라짐)

날짜계층을 선택해서 다시 드롭다운 형태로 변경해줄 수도 있음

날짜 표시 형식은 서식으로 변경해줄 수 있음

시계열데이터 완성

주요지표 생성하기
(앞서서와 동일하게 측정값을 생성해줄때는 새테이블을 생성해서 그안에 관리하기)

Vendor Count
V1_Vendor Count = DISTINCTCOUNT(InvoicePurchases[VendorName])

PO Count
V2_PO Count = DISTINCTCOUNT(InvoicePurchases[PONumber])

Brand Count
V3_Brand Count = DISTINCTCOUNT(InvoicePurchases[Brand])

AVG Qty per PO (총수량 / PO Count)
V4_AVG Qty per PO = DIVIDE(sum(InvoicePurchases[Quantity]),[V2_PO Count])

AVG Amount per PO (총 Amount / PO Count)
V5_Amount per PO = DIVIDE(sum(InvoicePurchases[Dollars]),[V2_PO Count])

AVG Freight per PO (총 Freight / PO Count)
V5_Freight per PO = DIVIDE(sum(InvoicePurchases[Freight]),[V2_PO Count])

카드(신규) 이용해서 주요지표 표시하기
판매액 기준 Top3 판매자 이름 표시하기
(Top N 필터 사용)
판매자 이름, 총판매액(Dollars의 합), PO개수(PO Number 개수) 를 이용해 여러행카드 생성

판매자 이름이 필터링되서 나오게 보고싶은것이므로 필터를 Vendorname에 걸어준다
판매액이 필터의 기준이 됨으로 값에는 총판매액(합계 Dollars개)

Purchaseㄴ 테이블에는 Invooice Purchases에는 없는 RecievingDate컬럼이 존재
PO Date : Price of Order 날짜
RecivingDate : (정확하지 않음) 주문 받은 날짜?
InvoiceDate : 송장생성날짜
PayDate : 결제날짜

각 단계의 날짜들 순서별로 걸리는 날을 계산해서 보여주는 테이블을 만들어보자
DateDiff Dax함수 사용 DATEDIFF(<Date1>, <Date2>, <Interval>)
( Interval - SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR )
일수로 차이를 구해 새열로 추가해준다

PO_RCV_datediff = DATEDIFF(Purchases[PODate],Purchases[ReceivingDate],DAY)

RCV_Invoice_datediff = DATEDIFF(Purchases[ReceivingDate],Purchases[InvoiceDate],DAY)

Invoice_Pay_datediff = DATEDIFF(Purchases[InvoiceDate],Purchases[PayDate],DAY)

PO_Pay_datediff = DATEDIFF(Purchases[PODate],Purchases[PayDate],DAY)

날짜는 평균적으로 걸리는 날로 계산해준다

[대시보드 결과]

기초재고와 기말재고 Gap 모니터링

1. 데이터 관계 설정

두 테이블은 StartDate와 EndDate를 제외하고는 모두 동일한 데이터

해서 두개의 쿼리를 합칠 것
두개의 쿼리를 합칠 때는 반드시 컬럼의 개수와 이름이 완벽히 동일해야 함
그러므로 StartDate와 EndDate의 컬럼명을 둘다 Date로 변경해준다

쿼리를 합쳐주기 위해 빈쿼리를 생성해준다

아무것도 없는 빈쿼리가 생성된다

우선 BegInv테이블을 불러와줌 ("="붙여야함)

상단에 쿼리 병합 선택

2개의 테이블을 위아래로 붙일 것이기 때문에 2개의 테이블 선택

잘 붙었는지 확인하려면 Date컬럼의 날짜 범위확인

조건열을 통해 Inv_flag 생성

쿼리 우클릭 -> 새그룹 선택

파일을 생성해서 쿼리 넣어주기

2. 결측치 보강

새로 만들어준 Inventoy 테이블을 확인해보니 46번 가게가 Begin에는 도시이름이있으나 End에는 없음
도시이름이 누락된 것으로 보인다

46번 가게 End_Inv값을 복사한다

데이터 변환 탭으로 이동
누락된 도시값을 보기 위해 오름차순으로 정렬해준다

City열 우클릭 -> 값 바꾸기

적용 및 닫기 선택
결측치가 잘 보강되었다

도시별 기초재고와 기말재고의 차이를 볼 수 있는 행렬차트에 들어갈 주요 지표들을 측정값으로 생성해준다

Store Count
Inv1_Store Count = DISTINCTCOUNT(Inventory[Store])

Inventory Quantity
Inv2_Inventory Quantity = SUM(Inventory[Quantity])

Inventory Amount
Inv3_Amount = SUM(Inventory[Amount])
(Amount = Inventory[Quantity] * Inventory[Price] 계산식으로 매출열을 먼저 만들었음)

측정값들을 이용해 만들어준 행렬차트

3. Waterfall Chart

Waterfall Chart 란?

- 비교하고자 하는 Column 이 어떠한 요인으로 Gap이 나게 되는지 표현하는 데이터 시각화
- X 축에는 Gap의 요인이 되는 항목 (시계열, 카테고리 등)
- 순차적인 양수 또는 음수 Gap 의 누적 효과를 이해하는데 효과적
- Waterfall Chart 또는 Bridge Chart 라고 불리움