[SQL 분석][Ch6] SCM 대시보드 (1)

김지현·2024년 8월 4일

SQL 분석

목록 보기

38/40

데이터 가져오기

csv파일 가져오기
데이터의 컬럼타입과 컬럼명이 동일한 경우 PowerBI에서 자동으로 key로 인식하여 연결하는 경우가 있음
→ 방지하기 위해 설정 변경
→ 파일 - 옵션 및 설정 - 옵션 > 관계 에 해당된 설정 선택 해제

데이터 살펴보기

구매 데이터

InvoicePurchases 테이블 : 4,192행 / PONumber 컬럼 (고유값 4,192개)
Purchases 테이블 : 1,740,401행 / PONumber 컬럼 (고유값 4,192개)
▶ InvoicePurchases와 Purchase 테이블은 1:다 로 연결 가능
Purchases 테이블의 PurchasePrice값은 해당 테이블에만 존재 (제품 단가)
- Dollars (판매액) = PurchasePrice * Quantity

재고, 판매 데이터

BegInv 테이블과 EndInv 테이블은 startDate와 endDate 차이만 존재 (나머지 구성 모두 동일)

차트 생성

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

[결과]

Purchases[PONumber]와 InvoicePurchases[PONumber] 관계 연결

PONumber, VendorNumber는 숫자로 계산할 일이 없으므로 텍스트 형태로 변경
판매자별 PO 개수, 총 매출, 총 운송비, 총 상품개수, 평균 가격 확인할 테이블 생성

슬라이서 추가

필드 Vendorname
드롭다운 형태로 변경 + 모두 선택 옵션 on
추가옵션 > 검색 활성화
복사하여 필드 InvoicePurchases 테이블의 PONumber로 변경
- 공통적으로 필터링이 필요한 슬라이서를 추가할 때는 1:다 중 1에 해당되는 테이블의 컬럼을 가져와야 함
복사하여 필드 InvoicePurchases 테이블의 PODate로 변경

PO Date별 시계열 차트 생성

날짜 표시 형식은 서식으로 변경 가능
날짜 형태인 값을 추가하면 기본적으로 드릴다운된 형태로 나타남
PO Date vs 날짜 형식 차이는 차트에서 드릴다운할 수 있는 요소 유무 차이
Dollars(합)를 보조 Y축에 추가하여 시계열 데이터 완성
축이 한 개 일때는 추세선을 추가할 수 있었는데, 보조축에 값을 추가하니 추세선이 사라짐

주요 지표 생성 및 시각화

새 측정값은 모두 한 테이블에서 작성 (새 테이블 생성)
- V1_Vendor Count = DISTINCTCOUNT(InvoicePurchases[VendorNumber])
- V2_PO Count = DISTINCTCOUNT(InvoicePurchases[PONumber])
- V3_Brand Count = DISTINCTCOUNT(Purchases[Brand])
- V4_AVG Qty per PO = DIVIDE(sum(InvoicePurchases[Quantity]), [V2_PO Count])
- V5_AVG Amount per PO = DIVIDE(sum(InvoicePurchases[Dollars]), [V2_PO Count])
- V6_AVG Frieght per PO = DIVIDE(sum(InvoicePurchases[Freight]), [V2_PO Count])
카드(신규) 이용하여 시각화

판매액 기준 Top3 판매자 이름 표시 (Top N 필터 사용)

판매자 이름, 총 판매액(Dollars 합), PO 개수(PONumber 개수) 이용하여 여러 행 카드 생성
판매자 이름이 필터링된 결과를 보고싶은 것이므로 Vendorname에 필터 적용
- 판매액이 필터의 기준이 되므로 값에는 총 판매액 (합계 dollars 개)

Datediff

Purchases 테이블의 날짜 컬럼 확인

PODate : Price Order 날짜
RecivingDate : 물건 받은 날짜(로 추정)
InvoiceDate : 송장 생성 날짜
PayDate : 결제 날짜

각 날짜 순서별로 소요되는 일수 계산하는 열 Purchases 테이블에서 바로 추가

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_Invocie_datediff = DATEDIFF(Purchases[ReceivingDate], Purchases[InvoiceDate], DAY)
Invocie_Pay_datediff = DATEDIFF(Purchases[InvoiceDate], Purchases[PayDate], DAY)
PO_Pay_datediff = DATEDIFF(Purchases[PODate], Purchases[PayDate], DAY)

소요되는 날짜는 평균값으로 계산하여 테이블 생성

기초재고와 기말재고 gap 모니터링 (재고 데이터)

[결과]

데이터 병합

두 테이블은 각각 startDate와 endDate 컬럼을 제외하고 모두 동일한 데이터
테이블 병합을 위해 컬럼명을 Date로 두 컬럼 모두 수정
빈 쿼리 생성 (데이터 변환 - 새 원본 - 빈 쿼리)
- = BegInv 입력 > BegInv 테이블 불러옴
홈탭 - 결합 메뉴 - 쿼리 추가 선택 > 추가할 테이블 선택
테이블이 잘 합쳐졌는지 Date컬럼 값 확인
조건 열 추가
쿼리 빈 공간 우클릭 - 새 그룹 선택 > Inven 그룹 생성하여 정리
- 유의) Inventory 테이블은 BegInv와 EndInv 테이블을 병합한 것이므로, 적어도 Inven 폴더 내에서는 두 테이블 다음에 생성한 테이블을 위치시킬 것

결측치 보간

새로 만든 Inventory 테이블 확인 결과, Store 46의 City값이 Begin에는 있으나 End에는 없음 → 누락된 것으로 추정
Store 46 값 복사 후 데이터 변환 이동, City 컬럼 오름차순 정렬 > 결측치 보기 위해
- City 컬럼 우클릭 - 값 바꾸기 선택 > 항목 입력 후 확인 > 닫기 및 적용 > 결측치 보간 확인
도시별 기초재고와 기말재고의 차이를 볼 수 있는 행렬 차트에 넣을 주요 지표 측정값으로 생성
- Inv1_Store count = DISTINCTCOUNT(Inventory[Store])
- Inv2_Inventory Quantity = sum(Inventory[Quantity])
- Inv3_Amount = sum(Inventory[Amount])
  (Amount = Inventory[Quantity] * Inventory[Price] 계산식으로 매출 열 먼저 생성하여 추가)
- 생성한 측정값들을 행렬에 넣음

Waterfall chart

Waterfall Chart

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

재고량의 차이를 볼거니까 Y축에 Inventory Quantity
Bridge 처음과 끝에 나올 구분자 값은 기초재고(BeginInv), 기말재고(EndInv)이므로 범주에 Inv_flag
BeginInv와 EndInv가 양 끝에 위치하고 사이에 어떤 gap이 있는지 bridge를 보고싶음 > 어떤 구분자 값으로 분류하여 gap을 모니터링하고 싶은지 해당 요소를 분석 결과에 >> City
어떤 도시에서 가장 많은 재고량의 gap이 발생하였는지 절대값을 큰 것부터 작은 순으로 나열
노란색 기타 : 표시된 city 외에 나머지 city 결과 (모든 city를 다 출력할 수 없으므로)
그래서 최대한 많은 요인을 표시하고 싶다면 분석 결과 옵션 사용
- 표시할 요인 수 조절 가능