Dodajte više energije u analizu podataka stvaranjem odnosa zamrzavanje različitih tablica. Odnos je veza između dviju tablica koje sadrže podatke: jedan stupac u svakoj tablici temelj je odnosa. Da biste uvidjeli koliko su odnosi korisni, zamislite da pratite podatke o narudžbama klijenata u svojoj tvrtki. Možete pratiti sve podatke u jednoj tablici koji imaju strukturu kao što je ova:
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 |
Odnosi postoje u podatkovnom modelu – onom koji eksplicitno stvarate ili onaj koji Excel automatski stvara u vaše ime kada istovremeno 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.
Odnosi se temelje na onim stupcima u svakoj tablici koji sadrže iste podatke. Tablicu Kupci možete, primjerice, povezati s tablicom Narudžbe ako svaki sadrži stupac u koji se pohranjuje ID kupca. 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 relacijskim bazama podataka postoji nekoliko vrsta ključeva. Tipka 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 postoji mnogo vrsta ključeva, ovo su najvažnije za našu svrhu:
-
Primarni ključ: jedinstveno identificira redak u tablici, kao što je CUSTOMERID u tablici Kupci.
-
Zamjenski ključ (ili ključ kandidata): stupac koji nije jedinstveni primarni ključ. 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 se odnosi na jedinstveni stupac u drugoj tablici, kao što je CUSTOMERID u tablici Narudžbe, koji se odnosi na CUSTOMERID u tablici Kupci.
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 primjeru odnos bi se definirao između CUSTOMERID-a u tablici Narudžbe (stupac) i CUSTOMERID u tablici Kupci (stupac s vrijednostima). 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.
Odnos između klijenta i narudžbe odnosi se 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 CustomerDiscounts, koja definira jedinstvenu diskontnu stopu za svakog klijenta, ima odnos jedan-prema-jedan sa tablicom Kupci.
U ovoj su tablici prikazani odnosi između tri tablice (Kupci, CustomerDiscountsi 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.
Kada se stvori bilo koji odnos, Excel se obično moraju ponovno izračunavati sve formule koje koriste stupce iz tablica u novostvorenom odnosu. Trajanje te obrade ovisi o količini podataka i složenosti odnosa. Dodatne informacije potražite u članku Ponovni izračun formula.
Podatkovni model može sadržavati veći broj odnosa između tablica. Da biste izgradili točne izračune, Excel treba jedan put iz jedne tablice u sljedeću. Zbog toga je samo jedan odnos između svakoga para tablica istodobno aktivan. Iako drugi nisu aktivni, u formulama i upitima možete navesti neaktivan odnos.
U prikazu dijagrama aktivni je odnos puna crta, a neaktivni su isprekidane crte. Na primjer, u programu AdventureWorksDW2012 tablica DimDate sadrži stupac, DateKey, koji je povezan s tri različita stupca u tablici FactInternetSales:Datum Narudžbe, DatumDatotekei DatumIspreme. 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.
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 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 Vrste podataka podržane u podatkovnim modelima. |
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 ključ sastoji se od više stupaca. Podatkovni modeli ne mogu koristiti složene tipke: tablica mora uvijek imati točno jedan stupac koji jedinstveno označava svaki redak u tablici. Ako uvozite tablice koje imaju postojeći odnos na temelju složenog ključa, čarobnjak za uvoz tablice u dodatku Power Pivot zanemarit će taj odnos jer ga nije moguće stvoriti u modelu.
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 prije uvoza podataka ili stvaranjem izračunatog stupca 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.
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. Na primjer, u poslovnoj postavki često imate varijacije u nazivima stupaca koji u osnovi sadrže iste podatke: Emp ID, EmployeeID, Employee ID, EMP_IDi tako dalje. 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.