Power Pivot və verilənlər modeli: əlaqələr və DAX

Bu dərsdə öyrənəcəksən

Fakt və ölçü cədvəllərini ayırmaq və «bir-çox» əlaqəsi qurmaq
Hesablanan sütunla ölçü (measure) arasındakı fərqi izah etmək
SUM, CALCULATE və DIVIDE ilə sadə ölçülər yazmaq
Filtr kontekstinin ölçünün nəticəsini necə dəyişdiyini anlamaq

Şirkətin satış cədvəlində milyonlarla sətir var, müştərilər və məhsullar isə ayrıca cədvəllərdədir. Adi yol: satış cədvəlinə VLOOKUP ilə «City», «Category», «Price» sütunları əlavə etmək. Nəticə — nəhəng, yavaş fayl, üstəlik o, Excel vərəqinin 1 048 576 sətir həddinə dirənir. Verilənlər modeli (Data Model) cədvəlləri ayrı saxlayır, onları əlaqələrlə birləşdirir və sıxılmış halda yaddaşda işləyir; Power Pivot isə bu modeli idarə etmək üçün alətdir.

Ulduz sxemi və əlaqələr

Tərif

Fakt və ölçü cədvəlləri

Fakt cədvəli hadisələri saxlayır (hər satış bir sətir: tarix, müştəri kodu, məhsul kodu, məbləğ). Ölçü cədvəlləri hadisəni təsvir edir (müştərilər, məhsullar, təqvim) və hər açar orada yalnız bir dəfə olur. Ölçülər mərkəzdəki fakt cədvəlinin ətrafında «ulduz» kimi düzülür.

1
Cədvəlləri hazırla
Hər siyahını Ctrl+T ilə Excel cədvəlinə çevir və ad ver: Sales, Customers, Products (Table Design › Table Name).
2
Modelə əlavə et
Insert › PivotTable pəncərəsində Add this data to the Data Model-i işarələ və ya Power Pivot-u qoş: File › Options › Add-ins › Manage: COM Add-ins › Go… › Microsoft Power Pivot for Excel; sonra Power Pivot › Tables › Add to Data Model.
3
Əlaqəni yarat
Data › Data Tools › Relationships › New…: Table = Sales, Column (Foreign) = CustID; Related Table = Customers, Related Column (Primary) = CustID. Power Pivot pəncərəsində (Diagram View) sahəni bir cədvəldən digərinə dartmaq da olar.

İnteraktiv

Simulyasiya yüklənir…

Kiçik ulduz modeli: satışlar (manatla) və müştərilər; əlaqənin məntiqi VLOOKUP ilə.

DAX: hesablanan sütun və ölçü

Meyar	Hesablanan sütun	Ölçü (measure)
Nə vaxt hesablanır	yeniləmədə, hər sətir üçün bir dəfə	pivot cədvəlin hər xanası üçün, filtrlərə görə
Nümunə	`=Sales[Qty]*RELATED(Products[Price])`	`Total Sales := SUM(Sales[Amount])`
Harada işlənir	`Rows`, `Columns`, filtr (dilimləmək üçün)	yalnız `Values`
Yaddaş	faylı böyüdür	demək olar ki, yer tutmur

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

burada:

[Total Sales]əvvəlcədən yaradılmış ölçü: SUM(Sales[Amount])
CALCULATE(expr, filter…)ifadəni dəyişdirilmiş filtr kontekstində hesablayır: şəhər filtrini «Baku» ilə əvəz edir, digər filtrləri saxlayır

Ölçü yaratmaq: Power Pivot › Calculations › Measures › New Measure… (və ya PivotTable Fields panelində cədvələ sağ klik › Add Measure…).

Filtr kontekstində üç ölçü

Modeldə 6 satış var: C1 (Baku) — Laptop 1200, Lamp 80; C2 (Ganja) — Phone 650, Laptop 1150; C3 (Baku) — Chair 300, Phone 700. Laptop və Phone — Electronics, Chair və Lamp — Home. Pivot cədvəldə sətirlərə Products[Category] qoyulub. [Total Sales], [Baku Sales] və [Baku Share] := DIVIDE([Baku Sales],[Total Sales]) hər sətirdə nə göstərəcək?

Həllini göstər

Electronics sətri (filtr: Category = Electronics):
[Total Sales] = 1200 + 650 + 1150 + 700 = 3700; [Baku Sales] = 1200 + 700 = 1900; pay = 1900 / 3700 ≈ 51,35%.
Home sətri: [Total Sales] = 300 + 80 = 380; [Baku Sales] = 380 (hər ikisi Bakıdadır); pay = 100%.
Ümumi cəm: 4080; Baku 2280; pay ≈ 55,88%.
CALCULATE yalnız şəhər filtrini dəyişdi, kateqoriya filtri isə qaldı — filtr kontekstinin mahiyyəti budur. Məxrəc 0 olsa, DIVIDE səhv əvəzinə boş dəyər qaytarı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 ölçülər dəsti. SUMX sətir-sətir hesablayıb toplayır (pivot dərsindəki «Price × Quantity» tələsinin düzgün həlli), ALL isə şəhər filtrini tamamilə götürür — pay üçün məxrəc belə alınır.

Diapazonu adlı cədvələ çevir — modelin ilk addımıCtrl+T

Aktiv pivot cədvəli yeniləAlt+F5

Model daxil olmaqla hər şeyi yeniləCtrl+Alt+F5

Əsas fikirlər

Verilənlər modeli cədvəlləri ayrı saxlayır və «bir-çox» əlaqələri ilə birləşdirir — VLOOKUP sütunlarına ehtiyac qalmır.
Ölçü cədvəlində açar unikal olmalıdır, tiplər hər iki tərəfdə eyni olmalıdır.
Hesablanan sütun sətir-sətir, ölçü isə pivot xanasının filtr kontekstində hesablanır.
CALCULATE filtr kontekstini dəyişir, DIVIDE sıfıra bölməni təhlükəsiz edir, ALL filtri götürür.
Nisbət və paylar həmişə ölçü kimi yazılmalıdır, hesablanan sütun kimi yox.

Özünü yoxla

10 sual. Hər düzgün cavab XP qazandırır.

1 / 10

Sales və Products cədvəlləri arasında əlaqədə «bir» tərəf hansıdır?