Power Pivot ve Veri Modeli: ilişkiler ve DAX

Bu derste öğreneceklerin

Olgu ve boyut tablolarını ayırmak ve bire-çok ilişki kurmak
Hesaplanmış sütun ile ölçü arasındaki farkı açıklamak
SUM, CALCULATE ve DIVIDE ile basit ölçüler yazmak
Filtre bağlamının bir ölçünün sonucunu nasıl değiştirdiğini anlamak

Bir şirketin satış tablosunda milyonlarca satır var; müşteriler ve ürünler ise ayrı tablolarda. Alışılmış yol: satış tablosuna VLOOKUP ile “City”, “Category”, “Price” sütunları eklemek. Sonuç, devasa ve yavaş bir dosya ile çalışma sayfasının 1.048.576 satır sınırı. Veri Modeli (Data Model) tabloları ayrı tutar, onları ilişkilerle bağlar ve bellekte sıkıştırılmış verilerle çalışır; Power Pivot ise bu modeli yönetme aracıdır.

Yıldız şema ve ilişkiler

Tanım

Olgu ve boyut tabloları

Olgu tablosu olayları saklar (her satış bir satır: tarih, müşteri kodu, ürün kodu, tutar). Boyut tabloları olayı tanımlar (müşteriler, ürünler, takvim) ve her anahtar orada yalnızca bir kez geçer. Boyutlar merkezdeki olgu tablosunun çevresinde bir yıldız gibi dizilir.

1
Tabloları hazırla
Her listeyi Ctrl+T ile Excel tablosuna dönüştür ve ad ver: Sales, Customers, Products (Table Design › Table Name).
2
Modele ekle
Insert › PivotTable penceresinde Add this data to the Data Model'i işaretle ya da Power Pivot'u etkinleştir: File › Options › Add-ins › Manage: COM Add-ins › Go… › Microsoft Power Pivot for Excel; ardından Power Pivot › Tables › Add to Data Model.
3
İlişkiyi oluştur
Data › Data Tools › Relationships › New…: Table = Sales, Column (Foreign) = CustID; Related Table = Customers, Related Column (Primary) = CustID. Power Pivot penceresinde (Diagram View) bir alanı bir tablodan diğerine sürüklemek de mümkündür.

Etkileşimli

Simülasyon yükleniyor…

Küçük bir yıldız modeli: satışlar (manat cinsinden) ve müşteriler; ilişkinin mantığı VLOOKUP ile gösterilmiştir.

DAX: hesaplanmış sütunlar ve ölçüler

Ölçüt	Hesaplanmış sütun	Ölçü (measure)
Ne zaman hesaplanır	yenilemede, her satır için bir kez	Özet Tablonun her hücresi için, filtrelerine göre
Örnek	`=Sales[Qty]*RELATED(Products[Price])`	`Total Sales := SUM(Sales[Amount])`
Nerede kullanılır	`Rows`, `Columns`, filtreler (dilimlemek için)	yalnızca `Values`
Bellek	dosyayı büyütür	neredeyse yer kaplamaz

Baku Sales := CALCULATE([Total Sales], Customers[City] = "Baku")

burada:

[Total Sales]önceden oluşturulmuş bir ölçü: SUM(Sales[Amount])
CALCULATE(expr, filter…)ifadeyi değiştirilmiş bir filtre bağlamında hesaplar: şehir filtresini “Baku” ile değiştirir, diğer filtreleri korur

Ölçü oluşturmak: Power Pivot › Calculations › Measures › New Measure… (ya da PivotTable Fields bölmesinde tabloya sağ tık › Add Measure…).

Filtre bağlamında üç ölçü

Modelde 6 satış var: C1 (Baku) — Laptop 1200, Lamp 80; C2 (Ganja) — Phone 650, Laptop 1150; C3 (Baku) — Chair 300, Phone 700. Laptop ve Phone Electronics, Chair ve Lamp Home kategorisindedir. Özet Tablonun satırlarında Products[Category] var. [Total Sales], [Baku Sales] ve [Baku Share] := DIVIDE([Baku Sales],[Total Sales]) her satırda ne gösterir?

Çözümü göster

Electronics satırı (filtre: Category = Electronics):
[Total Sales] = 1200 + 650 + 1150 + 700 = 3700; [Baku Sales] = 1200 + 700 = 1900; pay = 1900 / 3700 ≈ %51,35.
Home satırı: [Total Sales] = 300 + 80 = 380; [Baku Sales] = 380 (ikisi de Bakü'de); pay = %100.
Genel toplam: 4080; Baku 2280; pay ≈ %55,88.
CALCULATE yalnızca şehir filtresini değiştirdi, kategori filtresi ise kaldı; filtre bağlamının özü budur. Payda 0 olursa DIVIDE hata yerine boş değer döndürür.

Text

Total Sales := SUM ( Sales[Amount] )
Total Cost := SUMX ( Sales, Sales[Qty] * RELATED ( Products[UnitCost] ) )
Margin % := DIVIDE ( [Total Sales] - [Total Cost], [Total Sales] )
Baku Sales := CALCULATE ( [Total Sales], Customers[City] = "Baku" )
Share of All Cities := DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Customers[City] ) ) )

Tipik bir ölçü kümesi. SUMX satır satır hesaplayıp toplar (Özet Tablo dersindeki “Price × Quantity” tuzağının doğru çözümü); ALL ise şehir filtresini tamamen kaldırır, pay için payda böyle elde edilir.

Aralığı adlandırılmış tabloya dönüştür — modelin ilk adımıCtrl+T

Etkin Özet Tabloyu yenileAlt+F5

Veri Modeli dâhil her şeyi yenileCtrl+Alt+F5

Önemli noktalar

Veri Modeli tabloları ayrı tutar ve bire-çok ilişkilerle bağlar; VLOOKUP sütunlarına gerek kalmaz.
Boyut tablosunda anahtar benzersiz olmalı, veri türleri de iki tarafta aynı olmalıdır.
Hesaplanmış sütun satır satır, ölçü ise her Özet Tablo hücresinin filtre bağlamında hesaplanır.
CALCULATE filtre bağlamını değiştirir, DIVIDE sıfıra bölmeyi güvenli kılar, ALL filtreyi kaldırır.
Oranlar ve paylar her zaman ölçü olarak yazılmalı, hesaplanmış sütun olarak değil.

Kendini test et

10 soru. Her doğru cevap XP kazandırır.

1 / 10

Sales ile Products arasındaki ilişkide “bir” tarafı hangisidir?