Poboljšajte analizu podataka stvaranjem odnosa u različitim tablicama. Odnos je veza između dviju tablica koje sadrže podatke: jedan stupac u svakoj tablici osnova je odnosa. 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 | Popust | IDnarudžbe | DatumNarudžbe | Proizvod | Količina | |
|---|---|---|---|---|---|---|---|
| 1 | Vukelić | chris.ashton@contoso.com | 0,05 | 256 | 07.01.2010. | Kompaktni digitalni fotoaparat | 11 |
| 1 | Vukelić | chris.ashton@contoso.com | 0,05 | 255 | 03.01.2010. | SLR fotoaparat | 15 |
| 2 | Hrvatin | michal.jaworski@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ć | chris.ashton@contoso.com |
| 2 | Hrvatin | michal.jaworski@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 – onaj koji izričito stvorite vi ili onaj koji Excel automatski stvara u vaše ime kada istodobno uvozite 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.
Stupci i ključevi
Odnosi se temelje na onim stupcima u svakoj tablici koji sadrže iste podatke. Na primjer, mogli biste povezati tablicu Kupci s tablicom Narudžbe ako svaka sadrži 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. Ključ je obično stupac 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.
Iako postoje brojne vrste ključeva, ovi su najvažniji za našu svrhu:
- 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 klijenta 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. Drugi je važan odnos između tablica jedan-prema-jedan. U ovom primjeru tablica PopustiZaKlijenta , koja definira popust koji se dodjeljuje svakom klijentu, ima odnos jedan-prema-jedan s tablicom Klijenti.
U ovoj se tablici prikazuju odnosi između triju tablica (Kupci, PopustiKupci i Narudžbe):
| 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. Dodatne 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 sastavljao 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. Iako su ostali neaktivni, 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.
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. U podatkovnom modelu vrijednosti null i prazni nizovi istovjetni su praznini, koja je posebna podatkovna vrijednost. To znači da u stupcu za pretraživanje ne možete imati više vrijednosti null. |
| 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 Vrste podataka koje podržava podatkovni modeli. |
Nepodržane značajke baze podataka u podatkovnom modelu programa Excel
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 sadržavati 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.
Automatsko prepoznavanje i primjena odnosa u dodatku Power Pivot
Jedna od prednosti uvoza podataka pomoću dodatka Power Pivot jest ta š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 EMP_ID 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.