Challenge 23 Enterprisedna Modelo en SQL Server.

Hola y feliz día tarde o noche según donde me leas, hoy traigo una breve explicación fácil y sencilla en la cual los datos que se pueden obtener al participar en los retos de Enterpisedna  aparte de practicar con la creación de cuadros de mando o reportes puedes practicar tu nivel de SQL en este caso la carga de el fichero a SQL server y luego creando el modelado de datos , en este post te explicare paso a paso como realizarlo.

EMPECEMOS!!!!

1. Primero es crear la Base de datos ejemplo en nuestro SQL sever en el cual ya en este  mismo blog esta la explicación e instalarlo de manera fácil y sencillo https://solutionsgeek.es/como-instalar-sql-sever-facil/, ahora teniendo esto claro lo primero es crear una bases de datos ejemplo en este caso le asigne el nombre de challenge23 y la creamos con la instrucción T-SQL Create database Challenge23.
2. Posterior a ello creamos la tabla física con la siguiente instrucción Create table Fact_Table
   (
   AdmisionDate date,
   PPID char(7),
   Id_Program int ,
   Age char(2),
   Id_Gender int,
   Id_RaceEthnicity int,
   Id_ConditionaMedical int,
   Id_Substance int,
   MedDx Char(3),
   PsychAdmit Char(3),
   DLA1 decimal(18,2),
   DLA2 decimal(18,2),
   constraint FK_Program foreign key(Id_Program) references Dim_Program (Id_Program),
   constraint FK_Gender foreign key(Id_Gender) references Dim_Gender (Id_Gender),
   constraint FK_RaceEthnicity foreign key(Id_RaceEthnicity) references Dim_RaceEthnicity (Id_RaceEthnicity),
   constraint FK_ConditionalMedical foreign key(Id_ConditionaMedical)
   references Dim_ConditionalMedical (Id_ConditionalMedical),
   constraint FK_Subtance foreign key(Id_Substance) references Dim_Subtance (Id_Subtance)
   ) , pero acá debemos tener diferentes consideraciones que explico
   1. Esta tabla será la fact es decir la tabla de hechos esto quiere decir que debemos contemplar las siguientes consideraciones como que se compondrá por llaves foráneas de las dimensiones.
   2. Solo dejaremos fecha y los datos numéricos a calcular.
   3. Utilizando a detalle especifico los tamaños de cada campo para optimizar el tamaño de la BBDD.
   4. Se utilizo como llave primaria el PPID el cual es único si no existiese podrías crear un auto incrementable.
3. Creamos una tabla Temporal en la cual cargaremos la importación del archivo , dirás pero por que temporal por que luego sobre esta debemos hacer transformaciones y cruces para obtener dimensiones y luego alimenta la física que creamos en el paso 2Create table #Temporal_table

   (

   AdmisionDate date,

   PPID char(7),

   Program char(12),

   Age char(2),

   Gender char(1),

   RaceEthnicity       char(15),

   MHDx   char(10),

   SUDx   char(10),

   MedDx  char(2),

   PsychAdmit   int,

   DLA1 char(5),

   DLA2 char(5)

   )

4. Creamos el código del importador el cual es muy básico y fácil de utilizarbulk insert #Temporal_table

   rom ‘C:\Users\PC\Documents\ejercisios_PBI\Retos\DNA challenge\challenge 23\EDNA Challenge 23 Dataset\Unmodelled Dataset\Substance Abuse.csv’

   with(

   format=’CSV’,

   fieldterminator=’;’,

   rowterminator=’\n’,

   firstrow=2

   ),Como podemos observar explico brevemente que estamos haciendo. En este caso usamos el bulk insert a la tabla temporal, utilizando el format CSV , correcto transforme el fichero que descargamos en Excel en CSV, el ;es el separador y la terminación de para hacer el salto a la siguiente es con \n, por ultimo comenzamos de la fila dos omitiendo los cabeceros.

5. Ahora en este paso creamos las dimensiones y las alimentamos con lo que tenemos en la temporal en la cual almacenamos lo importado–Dimesion Program

   create table Dim_Program

   (

   Id_Program int identity (1,1) primary key,

   Name_Program char(12)

   )

    

   insert into Dim_Program (Name_Program)

   select distinct Program from #Temporal_table

    

   select * from Dim_Program

    

   –Dimension gender

   create table Dim_Gender

   (

   Id_Gender int identity (1,1) primary key,

   Name_Gender char(2)

   )

    

   insert into Dim_Gender (Name_Gender)

   select distinct Gender from #Temporal_table

    

   –Dimension RaceEthnicity

   create table Dim_RaceEthnicity

   (

   Id_RaceEthnicity int identity (1,1) primary key,

   Name_RaceEthnicity char(15)

   )

    

   insert into Dim_RaceEthnicity (Name_RaceEthnicity)

   select distinct RaceEthnicity from #Temporal_table

    

   –Dimension MHDX

   create table Dim_ConditionalMedical

   (

   Id_ConditionalMedical int identity (1,1) primary key,

   Name_ConditionalMedical char(15)

   )

    

   insert into Dim_ConditionalMedical (Name_ConditionalMedical)

   select distinct MHDX from #Temporal_table

    

    

   –Dimension SUDx

   create table Dim_Subtance

   (

   Id_Subtance int identity (1,1) primary key,

   Name_Substance char(15)

   )

    

   insert into Dim_Subtance (Name_Substance)

   select distinct SUDx from #Temporal_table, como podemos observar estamos creando la tabla que usaremos para las dimensiones, creando una columna auto incrementable o clave foránea que usaremos o estas también se les llaman claves subrogadas las cuales son mejor que utilizar las claves de negocio primero se optimiza la consulta y es lo adecuado cuando creamos modelos dimensiones en BI, ahora retomando vemos la imagen.

6. Actualizamos ahora la tabla temporal reemplazando las claves de negocio por las subrogadas con el respectivo left join en cada una ,–Actualizando tabla temporal con tablas de dimesiones

   Update  #Temporal_table

   set program=b.Id_Program

   from #Temporal_table as a

   Left join Dim_Program as b

   on a.Program=b.Name_Program

   where Program=b.Name_Program

    

   –Actualizando Gender

   Update  #Temporal_table

   set Gender=b.Id_Gender

   from #Temporal_table as a

   Left join Dim_Gender as b

   on a.Gender=b.Name_Gender

   where Gender=b.Name_Gender

    

   –Actualizando Dim_RaceEthnicity

   Update  #Temporal_table

   set RaceEthnicity=b.Id_RaceEthnicity

   from #Temporal_table as a

   Left join Dim_RaceEthnicity as b

   on a.RaceEthnicity=b.Name_RaceEthnicity

   where RaceEthnicity=b.Name_RaceEthnicity

    

    

   –Actualizando Dim_ConditionalMedical

   Update  #Temporal_table

   set MHDx=b.Id_ConditionalMedical

   from #Temporal_table as a

   Left join Dim_ConditionalMedical as b

   on a.MHDx=b.Name_ConditionalMedical

   where MHDx=b.Name_ConditionalMedical

    

   –Actualizando Dim_Subtance

   Update  #Temporal_table

   set SUDx=b.Id_Subtance

   from #Temporal_table as a

   Left join Dim_Subtance as b

   on a.SUDx=b.Name_Subtance

   where SUDx=b.Name_Subtance

    

   –Insercion a la tabla de hechos

    

   

7. Luego realizamos la carga de la fact table que creamos en el primer pasoInsert into Fact_Table

   Select AdmisionDate,PPID,CONVERT(int,program)as Id_Program,Age,

   CONVERT(int,Gender)as Id_Gender,CONVERT(int,RaceEthnicity)as Id_RaceEthnicity,

   CONVERT(int,MHDx)as Id_ConditionalMedical,

   CONVERT(int,SUDx)as Id_Subtance

   ,MedDx,PsychAdmit,

   Replace(DLA1,’,’,’.’) as DLA1

   ,Replace(DLA2,’,’,’.’) as DLA2

   from #Temporal_table, Acá estamos usando la temporal para alimentar la fact

8. Por ultimo pero no menos importante creamos la tabla de dimensión fecha como en este reto es tan corto el tiempo de tres meses solo creamos una tabla con los campos, año, mes, nombre mes y semana como vemos el paso a paso a continuación–Creando dimension fecha

   drop table Dim_fecha

    

   create table Dim_Fecha

   (

   Fecha date primary key,

   Anio Char(4),

   Mont_N char(2),

   N_Month char(10),

   Week_num char(2)

   )

    

    

   –Consulta para extraer los datos de fecha

   select distinct AdmisionDate as [Date],

   YEAR(AdmisionDate)as Anio,

   FORMAT(AdmisionDate,’MM’)AS Mont_Short,

   FORMAT(AdmisionDate,’MMMM’,’En’) as Month_Name,

   DATEPART(iso_week,AdmisionDate) as  [Week]

   from Fact_Table

    

   –Insertando fecha

   Insert into Dim_Fecha

   select distinct AdmisionDate as [Date],

   YEAR(AdmisionDate)as Anio,

   FORMAT(AdmisionDate,’MM’)AS Mont_N,

   FORMAT(AdmisionDate,’MMMM’,’En’) as Month_Name,

   DATEPART(iso_week,AdmisionDate) as  [Week]

   from Fact_Table, Evidentemente extraemos los datos de fecha de la tabla fact.

    

    

    

9. Por ultimo podemos ver el diagrama de relaciona en SQL sever

Con estos sencillos pasos lograste practicar múltiples funciones de SQL con la base de datos ejemplo y así colocando en practica tus conocimientos en T-SQL los cuales como analistas de datos son fundamentales.

Vicente Antonio Juan Magallanes

Business Intelligence Technical.

Perfil linkedin.