dimensional data model data warehouse tutorial with examples
Aquest tutorial explica els avantatges i els mites del model de dades dimensionals a Data Warehouse. A més, obteniu informació sobre les taules de dimensions i les taules informatives amb exemples:
Proves de magatzem de dades es va explicar en el nostre tutorial anterior, en aquest document Sèrie de formació de magatzem de dades per a tothom .
Les dades enormes s’organitzen al Data Warehouse (DW) amb tècniques de modelatge dimensional de dades. Aquestes tècniques de modelatge de dades dimensionals fan que la feina dels usuaris finals sigui molt fàcil d’informar-se sobre les dades empresarials. Aquest tutorial explica tot sobre els models de dades dimensionals a DW.
Públic objectiu
- Desenvolupadors i provadors de magatzem de dades / ETL.
- Professionals de bases de dades amb coneixements bàsics sobre conceptes de bases de dades.
- Administradors de bases de dades / experts en big data que vulguin entendre els conceptes de magatzem de dades / ETL.
- Graduats universitaris / estudiants de primer any que busquen feina en magatzem de dades.
Què aprendreu:
Models de dades dimensionals
Els models de dades dimensionals són les estructures de dades disponibles per als usuaris finals del flux ETL, per consultar i analitzar les dades. El procés ETL acaba carregant dades als models de dades dimensionals objectiu. Tots els models de dades dimensionals es construeixen amb una taula de dades envoltada de diverses taules de dimensions.
Passos a seguir durant el disseny d'un model de dades dimensionals:
Avantatges del modelatge dimensional de dades
A continuació es detallen els diversos avantatges del modelatge dimensional de dades.
- Estan segurs per utilitzar els entorns DW en continu canvi.
- Es poden construir dades enormes fàcilment amb l'ajut de models de dades dimensionals.
- Les dades dels models de dades dimensionals són fàcils d’entendre i analitzar.
- Els usuaris finals els poden accedir ràpidament per fer consultes amb un alt rendiment.
- Els models de dades dimensionals ens permeten aprofundir (o) enrotllar les dades jeràrquicament.
Modelització ER vs Modelatge dimensional de dades
- El modelatge ER és adequat per a sistemes operatius, mentre que el modelatge dimensional és adequat per al magatzem de dades.
- El modelatge ER manté dades transaccionals actuals detallades mentre que el modelatge dimensional manté el resum de les dades transaccionals actuals i històriques.
- El modelatge ER ha normalitzat les dades, mentre que el modelatge dimensional ha desnormalitzat les dades.
- El modelatge ER utilitza més combinacions durant la recuperació de consultes, mentre que el modelatge dimensional utilitza un nombre menor d'unions, de manera que el rendiment de la consulta és més ràpid en el modelatge dimensional.
Mites de modelatge de dades dimensionals
A continuació es detallen alguns dels mites de modelatge de dades dimensionals existents.
- Els models de dades dimensionals només s’utilitzen per representar el resum de les dades.
- Són específics per departament en una organització.
- No admeten l’escalabilitat.
- Estan dissenyats per servir als informes i consultes dels usuaris finals.
- No podem integrar els models de dades dimensionals.
Taules de dimensions
Les taules de dimensions tenen un paper clau en el sistema DW emmagatzemant tots els valors mètrics analitzats. Aquests valors s’emmagatzemen a atributs dimensionals (columnes) fàcilment seleccionables de la taula. La qualitat d’un sistema DW depèn sobretot de la profunditat dels atributs de dimensió.
Per tant, hem d'intentar proporcionar molts atributs juntament amb els seus valors respectius a les taules de dimensions.
Explorem l'estructura de les taules de dimensions !!
# 1) Clau de la taula de dimensions: Cada taula de dimensions tindrà qualsevol dels seus atributs de dimensió com a clau principal per identificar de manera única cada fila. Per tant, els diferents valors numèrics d'aquest atribut poden actuar com a claus primàries.
Si els valors dels atributs no són únics en cap cas, podeu considerar els números de sistema generats seqüencialment com a claus primàries. També s’anomenen claus subrogades.
Els models de dades dimensionals han de tenir la restricció d’integritat referencial per a cada clau entre dimensions i fets. Per tant, les taules de dades tindran una referència de clau externa per a cada clau primària / substitut de la taula de dimensions per mantenir la integritat referencial.
Si falla, no es poden recuperar les dades de la taula de dades respectives per a la clau de dimensió.
# 2) La taula és àmplia: Podem dir que les taules de dimensions són àmplies, ja que podem afegir qualsevol nombre d’atributs a una taula de dimensions en qualsevol moment del cicle DW. L'arquitecte DW sol·licitarà a l'equip ETL que afegeixi els atributs nous respectius a l'esquema.
En escenaris en temps real, podeu veure taules de dimensions amb 50 (o) atributs més.
# 3) Atributs textuals: Els atributs dimensionals poden ser de qualsevol tipus, preferiblement de text (o) numèric. Els atributs textuals tindran paraules comercials reals en lloc de codis. Les taules de dimensions no estan pensades per als càlculs, de manera que els valors numèrics poques vegades s’utilitzen per als atributs dimensionals.
# 4) Els atributs poden no estar directament relacionats: És possible que tots els atributs d'una taula de dimensions no estiguin relacionats entre ells.
# 5) No normalitzat: Normalitzar una taula de dimensions aporta a la imatge més taules intermediàries que no són eficients. Per tant, les taules de dimensions no es normalitzen.
Els atributs dimensionals poden actuar com a font de restriccions a les consultes i també es poden mostrar com a etiquetes als informes. Les consultes tindran un rendiment eficient si escolliu directament un atribut de la taula de dimensions i feu referència directament a la taula de dades respectiva sense tocar cap altra taula intermedia.
# 6) Drilling and Rolling Up: Els atributs de dimensió tenen la possibilitat de detallar (o) arrossegar les dades sempre que sigui necessari.
# 7) Jerarquies múltiples: Una taula de dimensions única amb diverses jerarquies és molt comuna. Una taula de dimensions tindrà una jerarquia simple si només existeix un camí des del nivell inferior fins al superior. De la mateixa manera, tindrà diverses jerarquies si hi ha diversos camins presents per arribar des del nivell inferior fins al superior.
# 8) Pocs registres: Les taules de dimensions tindran menys nombre de registres (en centenars) que les taules de dades (en milions). Tot i que són més petits que els fets, proporcionen totes les aportacions a les taules de fets.
Aquí teniu un exemple de taula de dimensions del client:
En entendre els conceptes anteriors, podeu decidir si un camp de dades pot actuar com a atribut de dimensió (o) no mentre extreu les dades de la mateixa font.
El pla de càrrega bàsic per a una dimensió
Les dimensions es poden crear de dues maneres, és a dir, mitjançant l'extracció de les dades de dimensió de sistemes d'origen externs (o) El sistema ETL pot construir les dimensions a partir de la posada en marxa sense implicar fonts externes. No obstant això, un sistema ETL sense cap processament extern és més adequat per crear taules de dimensions.
A continuació es detallen els passos que s’han implicat en aquest procés:
exemples de programes c ++ que utilitzen funcions
- Neteja de dades: Les dades es netegen, es validen i s'apliquen les regles empresarials abans de carregar-les a la taula de dimensions per mantenir la coherència.
- Conformitat de dades: Les dades d'altres parts del magatzem de dades s'han d'agregar correctament com a valor únic, respecte a cada camp de la taula de dimensions.
- Comparteix els mateixos dominis: Un cop confirmades les dades, es tornen a emmagatzemar a les taules de prova.
- Lliurament de dades: Finalment, tots els valors de l'atribut dimensional es carreguen amb les claus primàries / subrogades assignades.
Tipus de dimensions
A continuació es detallen els diferents tipus de dimensions per a la vostra referència.
Comencem!!
# 1) Dimensions petites
Les petites dimensions del magatzem de dades actuen com a taules de cerca amb menys nombre de files i columnes. Les dades en dimensions petites es poden carregar fàcilment des de fulls de càlcul. Si cal, es poden combinar petites dimensions com a superdimensions.
# 2) Dimensió conformada
Una dimensió conformada és una dimensió que es pot referir de la mateixa manera amb totes les taules de dades relacionades.
La dimensió de data és el millor exemple d'una dimensió conformada, ja que els atributs de la dimensió de data, com ara l'any, el mes, la setmana, els dies, etc., comuniquen les mateixes dades de la mateixa manera en qualsevol nombre de fets.
Un exemple de dimensió conformada.
# 3) Dimensió brossa
Pocs atributs en una taula de dades, com ara indicadors i indicadors, es poden moure a una taula de dimensions brossa independent. Aquests atributs no pertanyen a cap altra taula de dimensions existent. En general, els valors d'aquests atributs són simplement un 'sí / no' (o) 'veritable / fals'.
La creació d’una nova dimensió per a cada atribut de senyalador individual ho fa complex mitjançant la creació de més nombre de claus externes a la taula de dades. Al mateix temps, mantenir totes aquestes marques i informació d'indicadors a les taules de fet també augmenta la quantitat de dades emmagatzemades en fets, cosa que degrada el rendiment.
Per tant, la millor solució per a això és crear una dimensió brossa única, ja que una dimensió brossa és capaç de contenir qualsevol nombre d’indicadors “sí / no” o “veritable / fals”. Tot i això, les dimensions brossa emmagatzemen valors descriptius per a aquests indicadors (sí / no (o) veritable / fals), com ara actiu i pendent, etc.
Basat en la complexitat d’una taula de fets i dels seus indicadors, una taula de fets pot tenir una o més dimensions brossa.
Un exemple de Junk Dimension.
# 4) Dimensió de rol
Una dimensió única que es pot referir amb diversos propòsits en una taula de fets es coneix com a dimensió de joc de rol.
El millor exemple per a una dimensió de joc de rol és de nou una taula de dimensions de data, ja que el mateix atribut de data d'una dimensió es pot utilitzar per a diferents propòsits, com ara la data de la comanda, la data de lliurament, la data de la transacció, la data de cancel·lació, etc.
Si cal, podeu crear quatre visualitzacions diferents a la taula de dimensions de data respecte a quatre atributs de data diferents d'una taula de dades.
Un exemple de dimensió de rol.
# 5) Dimensions degenerades
Pot haver-hi pocs atributs que no puguin ser ni dimensions (mètriques) ni fets (mesures), però que siguin necessaris per a l'anàlisi. Tots aquests atributs es poden moure a dimensions degenerades.
Per exemple, podeu considerar el número de comanda, el número de factura, etc. com a atributs de dimensió degenerats.
Un exemple de dimensió degenerada.
# 6) Canvi lent de les dimensions
Una dimensió que canvia lentament és una mena en què les dades poden canviar lentament en qualsevol moment i no en intervals regulars periòdics. Les dades modificades de les taules de dimensions es poden gestionar de diferents maneres, tal com s’explica a continuació.
Podeu seleccionar el tipus SCD per respondre a un canvi individualment per a cada atribut d'una taula dimensional.
(i) SCD tipus 1
- Al tipus 1, quan hi ha un canvi en els valors dels atributs dimensionals, els valors existents se sobreescriuen amb els valors acabats de modificar, que no són altra cosa que una actualització.
- No es conserven dades antigues com a referència històrica.
- Els informes anteriors no es poden regenerar a causa de la inexistència de dades antigues.
- Fàcil de mantenir.
- L’impacte en les taules de dades és més gran.
Exemple de SCD de tipus 1:
(Ii) SCD tipus 2
- Al tipus 2, quan hi ha un canvi en els valors dels atributs dimensionals, s'inserirà una fila nova amb els valors modificats sense canviar les dades de la fila antiga.
- Si hi ha alguna referència de clau estrangera que existeix al registre antic en alguna de les taules de dades, la clau subrogada antiga s'actualitza a tot arreu amb una nova clau substitutòria automàticament.
- L’impacte en els canvis de la taula de fets és molt menor amb el pas anterior.
- Les dades antigues no es consideren enlloc després dels canvis.
- Al tipus 2, podem fer un seguiment de tots els canvis que s’estan produint als atributs dimensionals.
- No hi ha límit d’emmagatzematge de dades històriques.
- Al tipus 2, s’afegeixen pocs atributs a cada fila, com ara la data de canvi, la data efectiva, la data de finalització, el motiu del canvi i el senyalador actual, és opcional. Però això és significatiu si l’empresa vol conèixer el nombre de canvis realitzats durant un període de temps determinat.
Exemple de SCD tipus 2:
(Iii) SCD tipus 3
- Al tipus 3, quan hi ha un canvi en els valors dels atributs dimensionals, s'actualitzen els nous valors, però els valors antics continuen sent vàlids com a segona opció.
- En lloc d'afegir una nova fila per a cada canvi, s'afegirà una nova columna si no existeix anteriorment.
- Els valors antics es col·loquen als atributs afegits anteriorment i les dades de l’atribut primari se sobreescriuen amb el valor canviat com en el tipus 1.
- Hi ha un límit en l’emmagatzematge de dades històriques.
- L’impacte en les taules de dades és més gran.
Exemple de SCD de tipus 3:
(iv) SCD tipus 4
- Al tipus 4, les dades actuals s’emmagatzemen en una taula.
- Totes les dades històriques es mantenen en una altra taula.
Exemple de SCD de tipus 4:
(v) SCD tipus 6
- Una taula dimensional també pot tenir una combinació dels tres tipus SCD 1, 2 i 3, que es coneix com a dimensió híbrida de tipus 6 (o) que canvia lentament.
Taules de dades
Les taules de dades emmagatzemen un conjunt de valors mesurats quantitativament que s’utilitzen per als càlculs. Els valors de la taula de dades es mostren als informes empresarials. A diferència del tipus de dades textuals de les taules de dimensions, el tipus de dades de les taules de dades és significativament numèric.
Les taules de dades són profundes, mentre que les taules de dimensions són àmplies, ja que les taules de dades tindran un nombre més alt de files i un nombre menor de columnes. Una clau principal definida a la taula de dades és principalment identificar cada fila per separat. La clau primària també s'anomena taula composta de fet.
Si falta una clau composta en una taula de dades i si hi ha dos registres que tinguin les mateixes dades, és molt difícil diferenciar-les i referir-les a les taules de dimensions.
Per tant, si existeix una clau única adequada com a clau composta, és bo generar un número de seqüència per a cada registre de la taula de fets. Una altra alternativa és formar una clau primària concatenada. Això es generarà concatenant totes les claus primàries referides de les taules de dimensions segons les files.
Una taula de fets única pot estar envoltada per diverses taules de dimensions. Amb l'ajut de les claus externes que existeixen a les taules de fet, es pot fer referència al context respectiu (dades detallades) dels valors mesurats a les taules de dimensions. Amb l'ajuda de consultes, els usuaris realitzaran un aprofundiment i una distribució eficient.
El nivell més baix de dades que es poden emmagatzemar en una taula de fets es coneix com a Granularitat. El nombre de taules de dimensions associades a una taula de fets és inversament proporcional a la granularitat de les dades de la taula de fets. És a dir, el valor de mesura més petit necessita més taules de dimensions per referir-se.
En un model dimensional, les taules de fets mantenen una relació de molts a molts amb les taules de dimensions.
Un exemple de taula de dades de vendes:
Pla de càrrega de les taules de dades
Podeu carregar les dades d'una taula de dades de manera eficient tenint en compte els indicadors següents:
# 1) Eliminar i restaurar índexs
Els índexs de les taules de fet són bons impulsors de rendiment mentre es consulten les dades, però demolen el rendiment mentre es carreguen les dades. Per tant, abans de carregar dades enormes en taules de dades, deixeu caure tots els índexs en aquesta taula, carregueu-les i restaureu-ne els índexs.
# 2) Separeu les insercions de les actualitzacions
No combini els registres d'inserció i actualització mentre es carrega en una taula de dades. Si el nombre d’actualitzacions és menor, processeu les insercions i les actualitzacions per separat. Si el nombre d’actualitzacions és superior, és recomanable truncar i recarregar la taula de dades per obtenir resultats ràpids.
# 3) Particionament
Feu el particionament físicament en una taula de dades en mini taules per obtenir un millor rendiment de la consulta sobre les dades de la taula de dades massiva. Excepte els DBA i l'equip ETL, ningú no serà conscient de les particions dels fets.
Com a exemple , podeu particionar una taula segons el mes, el trimestre, l'any, etc. Mentre es fa una consulta, només es tenen en compte les dades particionades en lloc d'explorar tota la taula.
# 4) Càrrega en paral·lel
com reproduir fitxers .mkv
Ara tenim una idea sobre les particions de les taules de dades. Les particions sobre fets també són beneficioses mentre es carreguen dades enormes en fets. Per fer-ho, primer, dividiu les dades lògicament en diferents fitxers de dades i executeu els treballs ETL per carregar totes aquestes porcions lògiques de dades en paral·lel.
# 5) Utilitat de càrrega massiva
A diferència d'altres sistemes RDBMS, el sistema ETL no necessita mantenir registres de recuperació explícits per a fallades de transacció mitjana. Aquí 'càrregues massives' passen a fets en lloc de 'insercions SQL' per carregar dades enormes. Si en cas que falla una sola càrrega, es poden tornar a carregar fàcilment totes les dades (o) es poden continuar des d'on es queden amb la càrrega massiva.
# 6) Eliminació d'un registre de dades
La supressió d’un registre de la taula de fets només passa si l’empresa ho vol explícitament. Si hi ha dades de taula de fets que ja no existeixen als sistemes d'origen, les dades respectives es poden suprimir físicament (o) lògicament.
- Supressió física: Els registres no desitjats s’eliminen permanentment de la taula de dades.
- Eliminació lògica: S'afegirà una nova columna a la taula de dades, com ara 'suprimit' de tipus booleà Bit (o). Això actua com a indicador per representar els registres suprimits. Heu d’assegurar-vos que no seleccioneu els registres suprimits mentre consulteu les dades de la taula de fets.
# 7) Seqüència d’actualitzacions i supressions en una taula de dades
Quan hi hagi dades que s’han d’actualitzar, les taules de dimensions s’han d’actualitzar primer seguides d’actualitzar les claus subrogades a la taula de cerca si és necessari i després s’actualitzen les taules de dades respectives. La supressió es produeix de manera inversa perquè suprimir totes les dades no desitjades de les taules de dades facilita la supressió de les dades no desitjades enllaçades de les taules de dimensions.
Hauríem de seguir la seqüència anterior en ambdós casos perquè les taules de dimensions i les taules de dades mantenen la integritat referencial tot el temps.
Tipus de fets
Basant-se en el comportament de les dades de les taules de fets, es classifiquen com a taules de fets de transaccions, taules de fets instantanis i taules de fets instantànies acumulades. Tots aquests tres tipus segueixen diferents funcions amb diferents estratègies de càrrega de dades.
# 1) Taules de dades sobre transaccions
Com el nom indica, les taules de dades de transaccions emmagatzemen dades de nivell de transacció per a cada esdeveniment que succeeix. Aquest tipus de dades és fàcil d’analitzar a nivell de taula de fets. Però per a una anàlisi posterior, també podeu consultar les dimensions associades.
Per exemple, totes les vendes (o) compres realitzades des d'un lloc web de màrqueting s'han de carregar en una taula de dades de transaccions.
A continuació es mostra un exemple de taula de dades sobre transaccions.
# 2) Taules de dades instantànies periòdiques
Com el nom indica, les dades de la taula de dades de les instantànies periòdiques s’emmagatzemen en forma d’instantànies (imatges) a intervals periòdics, com ara cada dia, setmana, mes, trimestre, etc., segons les necessitats de l’empresa.
Per tant, és clar que es tracta d’una agregació de dades tot el temps. Per tant, els fets instantanis són més complexos en comparació amb les taules de dades de transaccions. Per exemple, les dades dels informes d'ingressos de rendiment es poden emmagatzemar en taules de dades instantànies per facilitar-ne la consulta.
A continuació es mostra un exemple de taula de dades instantànies periòdiques.
# 3) Taules de dades instantànies acumulades
L'acumulació de taules de dades instantànies us permet emmagatzemar dades en taules durant tota la vida útil d'un producte. Això actua com una combinació dels dos tipus anteriors, on qualsevol esdeveniment pot inserir dades en qualsevol moment com a instantània.
En aquest tipus, s'actualitzen columnes de dades i dades addicionals per a cada fila amb cada fita d'aquest producte.
Un exemple de taula de dades acumulativa d’instantànies.
A més dels tres tipus anteriors, aquí teniu alguns altres tipus de taules de dades:
# 4) Taules de fets reals: Un fet és una col·lecció de mesures, mentre que el fet només capta menys esdeveniments (o) condicions que no contenen cap mesura. Una taula de fets sense fets s’utilitza principalment per fer un seguiment d’un sistema. Les dades d’aquestes taules es poden analitzar i utilitzar per fer informes.
Per exemple, podeu cercar detalls d’un empleat que s’hagi acomiadat i el tipus de permís en un any, etc. Incloent tots aquests detalls de dades no clars en un fet, la taula augmentarà definitivament la mida dels fets.
A continuació es mostra un exemple de taula de fets sense fets.
# 5) Taules de fets conformats: Un fet conformat és un fet que es pot referir de la mateixa manera amb totes les dades de dades relacionades.
Especificacions d'una taula de dades
A continuació es detallen les especificacions d’una taula de dades.
- Nom del fet: Aquesta és una cadena que descriu breument la funcionalitat de la taula de dades.
- Procés empresarial: Aquesta taula de fets ha de complir les converses sobre el negoci.
- Preguntes: Esmenta una llista de preguntes empresarials que respondrà aquesta taula de dades.
- Gra: Indica el nivell més baix de detall associat a les dades de la taula de fets.
- Dimensions: Enumereu totes les taules de dimensions associades a aquesta taula de dades.
- Mesures: Els valors calculats emmagatzemats a la taula de dades.
- Freqüència de càrrega Representa els intervals de temps per carregar les dades a la taula de dades.
- Primeres files: Consulteu les dades inicials poblades a la taula de dades per primera vegada.
Exemple de modelatge dimensional de dades
Podeu fer-vos una idea de com es poden dissenyar les taules de dimensions i les taules de dades per a un sistema si consulteu el diagrama de modelització de dades dimensionals de vendes i comandes que apareix a continuació.
Conclusió
A hores d’ara hauríeu d’haver obtingut un coneixement excel·lent sobre les tècniques de modelatge de dades dimensionals, els seus avantatges, mites, taules de dimensions, taules de dades, juntament amb els seus tipus i processos.
Consulteu el nostre proper tutorial per obtenir més informació sobre els esquemes de magatzem de dades.
=> Visiteu aquí per aprendre l’emmagatzematge de dades des de zero.
Lectura recomanada
- Tutorial de proves de magatzem de dades amb exemples | Guia de proves ETL
- Exemples de mineria de dades: aplicacions més habituals de mineria de dades 2021
- Tutorial de Python DateTime amb exemples
- Fonaments de l’emmagatzematge de dades: una guia definitiva amb exemples
- Tutorial de proves de volum: exemples i eines de prova de volum
- Top 10 de les eines i tecnologies de proves més populars del magatzem de dades
- Mineria de dades: procés, tècniques i grans qüestions en l’anàlisi de dades
- Com realitzar proves basades en dades a SoapUI Pro - Tutorial SoapUI núm. 14