schema types data warehouse modeling star snowflake schema
Aquest tutorial explica diversos tipus d'esquema de magatzem de dades. Apreneu què és l’esquema d’estrelles i l’esquema de flocs de neu i la diferència entre l’esquema d’estrelles i l’esquema de flocs de neu:
En aquest Tutorials de data Warehouse per a principiants , vam tenir una mirada en profunditat Model de dades dimensionals a Data Warehouse al nostre tutorial anterior.
En aquest tutorial, aprendrem tot sobre els esquemes de magatzem de dades que s’utilitzen per estructurar taules de magatzems de dades (o).
preguntes i respostes d’entrevistes de serveis web per a usuaris experimentats
Comencem!!
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 les àrees de magatzem de dades / ETL.
- Graduats universitaris / estudiants de primer any que busquen feina en magatzem de dades.
Què aprendreu:
Esquema de magatzem de dades
En un magatzem de dades, s’utilitza un esquema per definir la manera d’organitzar el sistema amb totes les entitats de base de dades (taules de dades, taules de dimensions) i la seva associació lògica.
Aquests són els diferents tipus d’esquemes a DW:
- Horari d’estrelles
- Esquema Floc de Neu
- Diagrama de la galàxia
- Esquema de cúmuls estel·lars
# 1) Horari d’estrelles
Aquest és l'esquema més senzill i eficaç en un magatzem de dades. Una taula de dades al centre envoltada de taules de dimensions múltiples s’assembla a una estrella del model d’esquema d’estrelles.
La taula de dades manté relacions d'un a molts amb totes les taules de dimensions. Totes les files d'una taula de fets s'associen a les files de la taula de dimensions amb una referència de clau externa.
A causa del motiu anterior, la navegació entre les taules d’aquest model és fàcil per consultar dades agregades. Un usuari final pot entendre fàcilment aquesta estructura. Per tant, totes les eines de Business Intelligence (BI) admeten en gran mesura el model d’esquema Star.
Mentre es dissenyen esquemes d’estrelles, les taules de dimensions es desnormalitzen a propòsit. Són àmplies amb molts atributs per emmagatzemar les dades contextuals per a una millor anàlisi i informe.
Avantatges de l’esquema d’estrelles
- Les consultes fan servir combinacions molt senzilles mentre es recuperen les dades i, per tant, s’incrementa el rendiment de les consultes.
- És senzill recuperar dades per informar, en qualsevol moment del temps i durant qualsevol període.
Desavantatges de l’esquema d’estrelles
- Si hi ha molts canvis en els requisits, no es recomana modificar i reutilitzar l'esquema estel·lar existent a la llarga.
- La redundància de dades és més gran ja que les taules no es divideixen jeràrquicament.
A continuació es mostra un exemple d’esquema d’estrelles.
Consulta d’un esquema d’estrelles
Un usuari final pot sol·licitar un informe mitjançant les eines de Business Intelligence. Totes aquestes sol·licituds es processaran creant una cadena de 'CONSULTES SELECCIONADES' internament. El rendiment d'aquestes consultes tindrà un impacte en el temps d'execució de l'informe.
A partir de l'exemple d'esquema Star anterior, si un usuari empresarial vol saber quantes novel·les i DVD s'han venut a l'estat de Kerala el gener del 2018, podeu aplicar la consulta de la següent manera a les taules d'esquemes Star:
SELECT pdim.Name Product_Name, Sum (sfact.sales_units) Quanity_Sold FROM Product pdim, Sales sfact, Store sdim, Date ddim WHERE sfact.product_id = pdim.product_id AND sfact.store_id = sdim.store_id AND sfact.date_id = ddim.date_id AND sdim.state = 'Kerala' AND ddim.month = 1 AND ddim.year = 2018 AND pdim.Name in (‘Novels’, ‘DVDs’) GROUP BY pdim.Name Resultats:
| Nom del producte | Quantitat_ venuda | |
|---|---|---|
| 7 | Tothom pot entendre i dissenyar fàcilment l’esquema. | És difícil entendre i dissenyar l’esquema. |
| Novel·les | 12,702 | |
| DVD | 32,919 |
Espero que hàgiu entès el fàcil que és consultar un esquema d’estrelles.
# 2) Esquema Floc de Neu
L'esquema estrella actua com a entrada per dissenyar un esquema SnowFlake. La descamació de la neu és un procés que normalitza completament totes les taules de dimensions d'un esquema estel·lar.
La disposició d’una taula de fets al centre envoltada de múltiples jerarquies de taules de dimensions sembla un floc de neu al model d’esquema de floc de neu. Totes les files de la taula de fets s’associen a les files de la taula de dimensions amb una referència de clau externa.
Mentre es dissenyen esquemes SnowFlake, les taules de dimensions es normalitzen a propòsit. Les claus externes s’afegiran a cada nivell de les taules de dimensions per enllaçar-les amb l’atribut pare. La complexitat de l’esquema SnowFlake és directament proporcional als nivells de jerarquia de les taules de dimensions.
Avantatges de l’esquema SnowFlake:
- La redundància de dades s’elimina completament creant noves taules de dimensions.
- Si es compara amb l’esquema d’estrelles, les taules de dimensions Snow Flaking utilitzen menys espai d’emmagatzematge.
- És fàcil actualitzar (o) mantenir les taules Snow Flaking.
Desavantatges de l’esquema SnowFlake:
- A causa de les taules de dimensions normalitzades, el sistema ETL ha de carregar el nombre de taules.
- És possible que necessiteu combinacions complexes per realitzar una consulta a causa del nombre de taules afegides. Per tant, el rendiment de la consulta es degradarà.
A continuació es mostra un exemple d’esquema SnowFlake.
Les taules de dimensions del diagrama SnowFlake anterior es normalitzen tal com s’explica a continuació:
- La dimensió de data es normalitza en taules trimestrals, mensuals i setmanals deixant identificadors de clau estrangera a la taula de dates.
- La dimensió de la botiga es normalitza per incloure la taula d'Estat.
- La dimensió del producte es normalitza en marca.
- A la dimensió Client, els atributs connectats a la ciutat es mouen a la nova taula Ciutat deixant un identificador de clau externa a la taula Client.
De la mateixa manera, una única dimensió pot mantenir diversos nivells de jerarquia.
Es poden referir els diferents nivells de jerarquies del diagrama anterior de la següent manera:
- L'identificador trimestral, l'identificador mensual i els identificadors setmanals són les noves claus substitutives que es creen per a les jerarquies de la dimensió Data i que s'han afegit com a claus externes a la taula de la dimensió Data.
- L'identificador d'estat és la nova clau substitut creada per a la jerarquia de dimensions de la botiga i s'ha afegit com a clau externa a la taula de dimensions de la botiga.
- L'identificador de marca és la nova clau substitut creada per a la jerarquia de les dimensions del producte i s'ha afegit com a clau externa a la taula de dimensions del producte.
- L'identificador de ciutat és la nova clau substitut creada per a la jerarquia de les dimensions del client i s'ha afegit com a clau externa a la taula de dimensions del client.
Consulta d’un esquema de flocs de neu
Podem generar el mateix tipus d’informes per als usuaris finals que les estructures d’esquemes d’estrelles també amb els esquemes de SnowFlake. Però aquí les consultes són una mica complicades.
A partir de l'exemple d'esquema SnowFlake anterior, generarem la mateixa consulta que hem dissenyat durant l'exemple de consulta d'esquema Star.
És a dir, si un usuari empresarial vol saber quantes novel·les i DVD s'han venut a l'estat de Kerala el gener del 2018, podeu aplicar la consulta de la següent manera a les taules d'esquema de SnowFlake.
SELECT pdim.Name Product_Name, Sum (sfact.sales_units) Quanity_Sold FROM Sales sfact INNER JOIN Product pdim ON sfact.product_id = pdim.product_id INNER JOIN Store sdim ON sfact.store_id = sdim.store_id INNER JOIN State stdim ON sdim.state_id = stdim.state_id INNER JOIN Date ddim ON sfact.date_id = ddim.date_id INNER JOIN Month mdim ON ddim.month_id = mdim.month_id WHERE stdim.state = 'Kerala' AND mdim.month = 1 AND ddim.year = 2018 AND pdim.Name in (‘Novels’, ‘DVDs’) GROUP BY pdim.Name Resultats:
| Nom del producte | Quantitat_ venuda |
|---|---|
| Novel·les | 12,702 |
| DVD | 32,919 |
Punts que cal recordar mentre es consulten les taules d’esquema d’estrelles (o) de flocs de neu
Qualsevol consulta es pot dissenyar amb l'estructura següent:
Clàusula SELECT:
- Els atributs especificats a la clàusula select es mostren als resultats de la consulta.
- La sentència Select també utilitza grups per trobar els valors agregats i, per tant, hem d’utilitzar la clàusula group by a la condició where.
Clàusula FROM:
- S'han de triar totes les taules de dades essencials i taules de dimensions segons el context.
Clàusula ON:
- Els atributs de dimensió adequats s’esmenten a la clàusula where unint-se als atributs de la taula de fets. Les claus subrogades de les taules de dimensions s'uneixen a les claus externes respectives de les taules de dades per fixar l'interval de dades que cal consultar. Consulteu l’exemple de consulta d’esquema d’estrelles anteriorment escrit per entendre-ho. També podeu filtrar les dades a la pròpia clàusula from si en cas que hi feu servir combinacions interiors / externes, tal com s’escriu a l’exemple d’esquema SnowFlake.
- Els atributs de dimensió també s’esmenten com a restriccions a les dades de la clàusula where.
- En filtrar les dades amb tots els passos anteriors, es retornaran les dades adequades per als informes.
Segons les necessitats empresarials, podeu afegir (o) suprimir els fets, les dimensions, els atributs i les restriccions a un esquema en estrella (o) a una consulta d’esquema SnowFlake seguint l’estructura anterior. També podeu afegir subconsultes (o) combinar diferents resultats de la consulta per generar dades per a informes complexos.
# 3) Diagrama de la galàxia
Un esquema de galàxies també es coneix com Fact Constellation Schema. En aquest esquema, diverses taules de dades comparteixen les mateixes taules de dimensions. La disposició de taules de dades i taules de dimensions sembla una col·lecció d’estrelles del model d’esquema Galaxy.
Les dimensions compartides d’aquest model es coneixen com a dimensions conformes.
Aquest tipus d’esquema s’utilitza per a requisits sofisticats i per a taules de dades agregades que són més complexes per ser compatibles amb l’esquema Star (o) SnowFlake. Aquest esquema és difícil de mantenir a causa de la seva complexitat.
A continuació es mostra un exemple de Galaxy Schema.
# 4) Esquema de cúmul d’estrelles
Un esquema SnowFlake amb moltes taules de dimensions pot necessitar juntes més complexes durant la consulta. Un esquema estrella amb menys taules de dimensions pot tenir més redundància. Per tant, va aparèixer un esquema de cúmul estel·lar combinant les característiques dels dos esquemes anteriors.
L’esquema estel·lar és la base per dissenyar un esquema de cúmuls estel·lars i poques taules de dimensions essencials de l’esquema estel·lar estan formades per flocs de neu i això, al seu torn, forma una estructura d’esquema més estable.
A continuació es mostra un exemple d’esquema d’estrelles.
Quin és millor esquema de flocs de neu o esquema d’estrelles?
La plataforma de magatzem de dades i les eines de BI que s’utilitzen al vostre sistema DW tindran un paper fonamental a l’hora de decidir l’esquema adequat que s’ha de dissenyar. Star i SnowFlake són els esquemes més utilitzats a DW.
Es prefereix l’esquema d’estrelles si les eines de BI permeten als usuaris empresarials interactuar fàcilment amb les estructures de taula amb consultes senzilles. Es prefereix l’esquema SnowFlake si les eines de BI són més complicades per als usuaris empresarials per interactuar directament amb les estructures de la taula a causa de més combinacions i consultes complexes.
Podeu seguir endavant amb l’esquema SnowFlake si voleu estalviar una mica d’espai d’emmagatzematge o si el vostre sistema DW té eines optimitzades per dissenyar aquest esquema.
Esquema d’estrelles contra esquema de flocs de neu
A continuació es detallen les diferències claus entre l’esquema Star i l’esquema SnowFlake.
| S.No | Horari d’estrelles | Esquema de flocs de neu |
|---|---|---|
| 1 | La redundància de dades és més. | La redundància de dades és menor. |
| 2 | L’espai d’emmagatzematge de les taules de dimensions és més gran. | L’espai d’emmagatzematge de les taules de dimensions és comparativament menor. |
| 3 | Conté taules de dimensions desnormalitzades. | Conté taules de dimensions normalitzades. |
| 4 | La taula de fets únics està envoltada de taules de dimensions múltiples. | La taula de fets únics està envoltada de múltiples jerarquies de taules de dimensions. |
| 5 | Les consultes fan servir combinacions directes entre fet i dimensions per obtenir les dades. | Les consultes fan servir combinacions complexes entre fet i dimensions per obtenir les dades. |
| 6 | El temps d’execució de la consulta és menor. | El temps d'execució de la consulta és més gran. |
| 8 | Utilitza l'enfocament de dalt a baix. | Utilitza l'enfocament de baix a dalt. |
Conclusió
Esperem que tingueu una bona comprensió dels diferents tipus d’esquemes de Data Warehouse, juntament amb els seus avantatges i desavantatges d’aquest tutorial.
També vam aprendre com es poden consultar l’esquema d’estels i l’esquema SnowFlake, i quin esquema s’ha de triar entre aquests dos juntament amb les seves diferències.
Estigueu atent al nostre proper tutorial per obtenir més informació sobre Data Mart a ETL.
=> Vigileu aquí les sèries d’entrenament sobre emmagatzematge de dades senzilles.
Lectura recomanada
- Tipus de dades Python
- Tipus de dades C ++
- Tutorial de proves de magatzem de dades amb exemples | Guia de proves ETL
- Top 10 de les eines i tecnologies de proves més populars del magatzem de dades
- Model de dades dimensionals a Data Warehouse: tutorial amb exemples
- Tutorial de proves de magatzem de dades de proves ETL (una guia completa)
- Què és el procés ETL (extracció, transformació, càrrega) a Data Warehouse?
- Mineria de dades: procés, tècniques i grans qüestions en l’anàlisi de dades