Odnosi između tablica u podatkovnom modelu

Novi zadatak
Videozapis: Odnosi u značajkama Power View i Power Pivot

Poboljšajte analizu podataka stvaranjem odnosa između podataka u različitim tablicama. Odnos je veza između dviju tablica koja se temelji na jednome stupcu u svakoj tablici. Da biste uvidjeli koliko su odnosi korisni, zamislite da pratite podatke o narudžbama klijenata u svojoj tvrtki. Te podatke možete pratiti u jednoj tablici s ovakvom strukturom:

IDklijenta

Naziv

E-pošta

Popust

IDnarudžbe

DatumNarudžbe

Proizvod

Količina

1

Vukelić

gordan.vukelic@contoso.com

0,05

256

07.01.2010.

Kompaktni digitalni fotoaparat

11

1

Vukelić

gordan.vukelic@contoso.com

0,05

255

03.01.2010.

SLR fotoaparat

15

2

Hrvatin

nikica.hrvatin@contoso.com

0,10

254

3.1.2010.

Jeftini softver za izradu filmova

27

Takav pristup može biti vrlo funkcionalan, ali podrazumijeva pohranu velike količine redundantnih podataka, kao što je adresa e-pošte klijenta za svaku narudžbu. Prostor za pohranu je jeftin, ali ako se adresa e-pošte promijeni, morat ćete ažurirati svaki redak koji se odnosi na tog klijenta. Jedno od rješenja tog problema jest podjela podataka na više tablica i definiranje odnosa između tih tablica. Takav se pristup koristi u relacijskim bazama podataka, kao što je SQL Server. U bazi podataka koju uvezete, primjerice, podaci o narudžbama mogu se prikazivati pomoću triju povezanih tablica:

Klijenti

[IDklijenta]

Naziv

E-pošta

1

Vukelić

gordan.vukelic@contoso.com

2

Hrvatin

nikica.hrvatin@contoso.com

PopustiZaKlijenta

[IDklijenta]

Popust

1

0,05

2

0,10

Narudžbe

[IDklijenta]

IDnarudžbe

DatumNarudžbe

Proizvod

Količina

1

256

07.01.2010.

Kompaktni digitalni fotoaparat

11

1

255

03.01.2010.

SLR fotoaparat

15

2

254

3.1.2010.

Jeftini softver za izradu filmova

27

U podatkovnom modelu postoje odnosi koje stvarate vi i ono koje umjesto vas stvara Excel prilikom istodobnog uvoza više tablica. Podatkovni model možete stvoriti ili njime upravljati i pomoću dodatka Power Pivot. Dodatne informacije potražite u člankuStvaranje podatkovnog modela u programu Excel.

Ako za uvoz tablica iz iste baze podataka koristite dodatak Power Pivot, Power Pivot može prepoznati odnose između tablica na temelju stupaca koji se nalaze u [uglatim zagradama] i reproducirati te odnose u podatkovnom modelu koji se stvara u pozadini. Dodatne informacije potražite u članku Automatsko prepoznavanje i primjena odnosa. Ako tablice uvozite iz više izvora, odnose možete stvoriti ručno na način opisan u članku Stvaranje odnosa između dviju tablica.

Vrh stranice

Sadržaj članka

Stupci i ključevi

Vrste odnosa

Odnosi i performanse

Više odnosa između tablica

Preduvjeti odnosa između tablica

Što nije podržano u odnosu između tablica

Složeni ključevi i stupci s vrijednostima

Odnosi više-prema-više

Interna spajanja i petlje

Automatsko prepoznavanje i primjena odnosa u dodatku Power Pivot

Automatsko prepoznavanje imenovanih skupova

Primjena odnosa

Stupci i ključevi

Odnosi se temelje na onim stupcima u svakoj tablici koji sadrže iste podatke. Tako, primjerice, tablice Klijenti i Narudžbe mogu biti povezane jedna s drugom jer obje sadrže stupac u koji se pohranjuje ID klijenta. U našem su primjeru nazivi stupaca isti, ali to ne mora biti tako. Naziv jednog stupca mogao bi biti IDKlijenta, a drugog BrojKlijenta, važno je samo da reci u tablici Narudžbe sadrže ID koji je pohranjen i u tablicu Klijenti.

U relacijskoj bazi podataka postoji nekoliko vrsta ključeva, koji su obično samo stupci s posebnim svojstvima. Poznavanje namjene svakog ključeva pomoći će vam pri upravljanju podatkovnim modelom s više tablica koji daje podatke za zaokretnu tablicu, zaokretni grafikon ili izvješće dodatka Power View.

Za naše su potrebe najzanimljiviji sljedeći ključevi:

  • Primarni ključ: jedinstveno označava redak u tablici, kao npr. IDKlijenta u tablici Klijenti.

  • Zamjenski ključ (ili nadomjesni ključ): stupac koji nije primarni ključ, ali je također jedinstven. Tako se, recimo, u tablici Zaposlenici može pohranjivati ID zaposlenika i matični broj zaposlenika, što su u oba slučaja jedinstvene vrijednosti.

  • Vanjski ključ: stupac koji upućuje na jedinstveni stupac u drugoj tablici, kao što je IDKlijenta u tablici Narudžbe, koji upućuje na IDKlijenta u tablici Klijenti.

U podatkovnom se modelu primarni ključ i zamjenski ključ nazivaju povezanim stupcem. Ako tablica sadrži primarni i zamjenski ključ, kao osnovu za odnos tablice možete koristiti bilo koji. Vanjski se ključ naziva izvornim stupcem ili samo stupcem. U našem bi primjeru odnos bio definiran između ključa IDKlijenta u tablici Narudžbe (stupac) i ključa IDKlijenta u tablici Klijenti (stupac s vrijednostima za pretraživanje). Ako uvezete podatke iz relacijske baze podataka, Excel po zadanom odabire vanjski ključ iz jedne tablice i odgovarajući primarni ključ iz druge. Možete, međutim, koristiti bilo koji stupac koji ima jedinstvene vrijednosti za stupac s vrijednostima za pretraživanje.

Biranje raspona datuma za prikaz

Odnos između tablica Klijenti i Narudžbe jest odnos jedan-prema-više. Uz svakoga se klijenta može vezati veći broj narudžbi, no narudžbe se ne mogu vezati uz veći broj klijenata. Postoje i odnosi jedan-prema-jedan i više-prema-više. Tablica PopustiZaKlijenta, koja definira popust koji se dodjeljuje svakom klijentu, predstavlja odnos jedan-prema-jedan unutar tablice Klijenti.

U sljedećoj se tablici prikazuju odnosi između tri tablice:

Odnos

Vrsta

Stupac za pretraživanje

Stupac

Klijenti-PopustZaKlijenta

jedan-prema-jedan

Klijenti.IDKlijenta

PopustZaKlijenta.IDKlijenta

Klijenti-Narudžbe

jedan-prema-više

Klijenti.IDKlijenta

Narudžbe.IDKlijenta

Napomena :  Podatkovni model ne podržava odnose više-prema-više. Dobar primjer odnosa više-prema-više jest izravan odnos između Proizvoda i Klijenata, u kojemu klijent može kupiti veći broj proizvoda, ali i veći broj klijenata možete kupiti isti proizvod.

Odnosi i performanse

Excel nakon stvaranja odnosa obično mora ponovno izračunati sve formule koje se koriste stupcima iz tablica u novostvorenom odnosu. Trajanje te obrade ovisi o količini podataka i složenosti odnosa. Pojedinosti potražite u članku Ponovni izračun formula.

Više odnosa između tablica

Podatkovni model može sadržavati veći broj odnosa između tablica. Da bi proveo točne izračune, programu Excel je potreban jedinstven put od jedne tablice prema sljedećoj. Zbog toga je samo jedan odnos između svakoga para tablica istodobno aktivan. Drugi su za to vrijeme neaktivni, no neaktivan odnos možete navesti u formulama i upitima. U prikazu dijagrama aktivni se odnos prikazuje punom crtom, a neaktivni isprekidanom. Tako, primjerice, u radnoj knjizi AdventureWorksDW2012 tablica DatumDim sadrži stupac KljučDatuma koji je povezan s tri različita stupca u tablici StvarnaInternetskaProdaja: DatumNarudžbe, Rok i DatumIsporuke. Ako postoji aktivan odnos između stupca KljučDatuma i DatumNarudžbe, to je ujedno i zadani odnos u formulama, osim ako ne navedete drukčije.

Vrh stranice

Preduvjeti odnosa između tablica

Odnos se može stvoriti kada se zadovolje sljedeći uvjeti:

Kriteriji

Opis

Jedinstveni identifikator za svaku tablicu

Svaka tablica mora sadržavati jedan stupac koji utvrđuje jedinstveni identitet svakoga retka u toj tablici. Taj se stupac obično naziva primarnim ključem.

Jedinstvene vrijednosti pretraživanja u stupcima

Podatkovne vrijednosti u stupcima za pretraživanje vrijednosti moraju biti jedinstvene. Drugim riječima, stupci ne smiju sadržavati duplikate. Podatkovni model izjednačava nule i prazne nizove kao praznine, što je zasebna podatkovna vrijednost. To znači da ne smijete imati više praznih polja ili nula u stupcu za pretraživanje vrijednosti.

Kompatibilne vrste podataka

Vrsta podataka u izvornom stupcu i stupcu za pretraživanje vrijednosti mora biti kompatibilna. Dodatne informacije o vrstama podataka potražite u članku o vrstama podataka koji su podržani u podatkovnim modelima.

Što nije podržano u odnosu između tablica

Odnos tablice u podatkovnom modelu nije moguće stvoriti ako je ključ složen. Također ste ograničeni na stvaranje odnosa jedan-prema-jedan i jedan-prema-više. Ostale vrste odnosa nisu podržane.

Složeni ključevi i stupci s vrijednostima za pretraživanje

Složeni se ključ sastoji od više od jednog stupca. Podatkovni modeli ne mogu koristiti složene ključeve. Tablica uvijek mora imati točno jedan stupac koji jedinstveno označava svaki redak u tablici. Ako uvezete tablice koje sadrže odnos utemeljen na složenom ključu, čarobnjak za uvoz tablica u dodatku Power Pivot zanemarit će taj odnos jer ga u takvom modelu nije moguće stvoriti.

Da biste stvorili odnos između dviju tablica s više stupaca koji definiraju primarni i vanjski ključ, najprije kombinirajte vrijednosti da biste stvorili jedan stupac ključa prije nego što stvorite odnos. To možete učiniti i prije uvoza podataka ili tako da stvorite izračunati stupac u podatkovnom modelu pomoću dodatka Power Pivot.

Odnosi više-prema-više

Podatkovni model ne podržava odnose više-prema-više. Ne možete jednostavno dodati spojene tablice u model. Možete se, međutim, poslužiti funkcijama jezika DAX da biste oblikovali odnos više-prema-više.

Interna spajanja i petlje

Interna spajanja nisu dopuštena u podatkovnom modelu. Interno spajanje jest odnos unutar same tablice. Obično se koristi radi definiranja hijerarhijskog odnosa nadređenosti i podređenosti. Tako biste, primjerice, mogli spojiti tablicu Zaposlenici samu sa sobom da biste stvorili hijerarhiju u kojoj se prikazuje lanac odgovornosti u tvrtki.

Excel ne dopušta stvaranje petlji u sklopu odnosa u radnoj knjizi. Drugim riječima, nisu dopušteni sljedeći odnosi:

  • 1. tablica, stupac a   prema   2. tablici, stupcu f

  • 2. tablica, stupac f   prema   3. tablici, stupcu n

  • 3. tablica, stupac n   prema   1. tablici, stupcu a

Ako stvorite odnos koji dovodi do stvaranja petlje, generirat ćete pogrešku.

Vrh stranice

Automatsko prepoznavanje i primjena odnosa u dodatku Power Pivot

Jedna od prednosti dodatka Power Pivot u tome je što Power Pivot ponekad može prepoznati odnose i stvoriti nove odnose u podatkovnom modelu koji stvara u programu Excel.

Kada uvozite više tablica, Power Pivot automatski prepoznaje sve postojeće odnose između tablica. Isto tako, prilikom stvaranja zaokretne tablice Power Pivot analizira podatke u tablicama. Utvrđuje moguće postojanje odnosa koji još nisu definirani pa predlaže odgovarajuće stupce koji bi se mogli uvrstiti u te odnose.

Algoritam prepoznavanja koristi se statističkim podacima o vrijednostima i metapodacima stupaca da bi pretpostavio vjerojatnost odnosa.

  • Vrste podataka u svim povezanim stupcima morale bi bite kompatibilne. Automatsko prepoznavanje podržava samo numeričku i tekstnu vrstu podataka. Dodatne informacije o vrstama podataka potražite u članku o vrstama podataka koji su podržani u podatkovnim modelima.

  • Da bi odnos bio uspješno prepoznat, broj jedinstvenih ključeva u stupcu s vrijednostima za pretraživanje morao bi biti veći od vrijednosti u tablici na strani većeg broja vrijednosti. Drugim riječima, stupac ključa na strani odnosa s većim brojem vrijednosti ne smije sadržavati nijednu vrijednost koja nije u stupcu ključa tablice s vrijednostima za pretraživanje. Recimo, primjerice, da imate tablicu u kojoj se navode proizvodi s ID-ovima (tablica s vrijednostima za pretraživanje) i tablicu prodaje u kojoj se navodi prodaja svakog proizvoda (strana odnosa s većim brojem vrijednosti). Ako vam zapisi o prodaji sadrže ID proizvoda koji nema odgovarajući ID u tablici proizvoda, odnos se ne može automatski stvoriti, ali biste ga mogli stvoriti ručno. Da bi Excel prepoznao odnos, morate prvo ažurirati tablicu s vrijednostima pretraživanja proizvoda te u nju uvrstiti ID-ove proizvoda koji dosad nisu bili upisani.

  • Provjerite je li naziv stupca ključa na strani odnosa s više vrijednosti sličan nazivu stupca ključa u tablici s vrijednostima za pretraživanje. Nazivi ne moraju biti identični. Tako se, primjerice, u poslovnom okruženju često pojavljuju varijacije naziva stupaca koji sadrže uglavnom iste podatke: ID Zaposl, IDZaposlenika, ID Zaposlenika itd. Algoritam prepoznaje slične nazive i dodjeljuje veću vjerojatnost onim stupcima koji imaju slične ili identične nazive. Zato ako želite povećati vjerojatnost stvaranja odnosa, pokušajte s preimenovanjem stupaca podataka koje uvozite tako da nose naziv sličan stupcu u postojećim tablicama. Ako Excel pronađe više mogućih odnosa, neće stvoriti nijedan.

Sada možda bolje razumijete zašto se ne prepoznaju baš svi odnosi i kako promjene u metapodacima, kao što su nazivi polja i vrste podataka, poboljšavaju rezultate automatskoga prepoznavanja odnosa. Dodatne informacije potražite u članku o otklanjanju poteškoća s odnosima.

Automatsko prepoznavanje imenovanih skupova

Odnosi između imenovanih skupova i povezanih polja u zaokretnim tablicama ne prepoznaju se automatski. Takvi se odnosi mogu stvoriti ručno. Ako ipak želite koristiti automatsko prepoznavanje odnosa, uklonite svaki imenovani skup i dodajte zasebna polja iz imenovanog skupa izravno u zaokretnu tablicu.

Primjena odnosa

U nekim se slučajevima odnosi između tablica automatski primjenjuju. Ako, primjerice, stvorite odnos između prva dva skupa niže navedenih tablica, pretpostavlja se da postoji odnos između druge dvije tablice i taj se odnos automatski uspostavlja.

  • Proizvodi i Kategorije: stvoren ručno

  • Kategorije i Potkategorije: stvoren ručno

  • Proizvodi i Potkategorije: pretpostavlja se da postoji odnos

Da bi se automatski uspostavio odnos, on se mora kretati u jednom pravcu, kao što je prikazano gore. Da je početni odnos bio između, primjerice, Prodaje i Proizvoda te Prodaje i Klijenata, ne bi bilo pretpostavljeno da postoji odnos. To je zbog toga što je odnos između Proizvoda i Klijenata odnos više-prema-više.

Vrh stranice

Proširite svoje vještine
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×