Eliminando duplicados

Un factor de confusión y además un problema común con al trabajar con datos es la información duplicada. Puede presentarse en una tabla pobremente diseñada, en datos importados u otras situaciones, pero el problema sigue siendo el mismo, ¿como le dices a tu sistema que borre todo excepto una copia de cada fila? Afortunadamente, SQL Server tiene una herramienta que lo hace fácil: ROW_NUMBER()

En información duplicada, cada fila es lo mismo. Mientras puedes verlo y decidir que fila quieres borrar, el problema es puedes decirle a SQL Server cual fila no borrar. ROW_NUMBER() te permite agregar una columna que diferencia entre las filas y puedes borrar entonces solo las que tu deseas.

Digamos que tienes una tabla

CREATE TABLE DuplicateRow(
FName varchar(30),
LName varchar(30),
JobTitle varchar(30),
Age tinyint
)

Mantengamoslo sencillo para este ejemplo. Ahora digamos que consultas tu tabla y encuentras que cuando estabas haciendo los inserts te entusiasmaste de mas con el “execute”

Fname LName JobTitle Age
Mike      Klarm   Manager 37
Mike      Klarm   Manager 37
Mike      Klarm   Manager 37

Tu no quieres que este sujeto aparezca tres veces, pero si le mandas a SQL Server un DELETE basado en cualquiera de la información disponible, se borrarán los tres registros y tendrás que insertar de nuevo. Para mantener una fila y borrar las demás, puedes usar ROW_NUMBER(). Empezaremos con un SELECT para que puedas ver lo que está pasando

SELECT
ROW_NUMBER() OVER (PARTITION BY FName,LName, JobTitle, Age ORDER BY LName) R
, FName
, LName
, JobTitle
, Age
FROM DuplicateRow

esto te arrojará

R FName LName JobTitle Age
1 Mike      Klarm Manager 37
2 Mike      Klarm Manager 37
3 Mike      Klarm Manager 37

y ahora tu puedes borrar cualquier fila excepto donde R > 1

DELETE q
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY FName, LName, JobTitle, Age ORDER BY LName) R
,FName
,LName
,JobTitle
,Age
FROM DuplicateRow
) q
WHERE R > 1

No necesitas hacer el SELECT primero, pero es bueno ver que vas a borrar

Si hay otras filas en la base de datos, la primera instancia de cada conjunto de datos empezara con el número 1 y no sera borrada, pero cualquier registro que sea un duplicado si será borrado

Veamos ROW_NUMBER() un poco más de cerca. Toda la magia sucede en la clausula OVER() que le sigue a ROW_NUMBER(), especificamente la sección “PARTITION BY”. Aque es donde tu enlistas las columnas que esperas esten duplicadas. Ya que queremos borrar cualquier fila donde todas las columnas esten duplicadas, en esta sección enlistamos todas las columnas. “ORDER BY” no hace gran diferencia aqui ya que todas las filas son iguales, pero necesitas un valor aquí para que todo esto funcione.

Si tus datos son un poco diferentes, puedes usar ROW_NUMBER() para deshacerte de diferentes combinaciones de información. Digamos que Mike obtuvo una promoción el año pasado y alguién agrego una fila en lugar de actualizarla.

FName LName JobTitle Age
Mike      Klarm Manager 37
Mike      Klarm Manager 37
Mike      Klarm Manager 37
Mike      Klarm Operator 32
Mike      Klarm Operator 32
Mike      Klarm Intern 22

Dependiendo de que pongas en tu sección “PARTITION BY”, puedes decidir que borrar. Si tu quieres mantener una de cada conjunto, usa el mismo query que arriba y obtendras

R FName LName JobTitle Age
1 Mike      Klarm Manager 37
2 Mike      Klarm Manager 37
3 Mike      Klarm Manager 37
1 Mike      Klarm Operator 32
2 Mike      Klarm Operator 32
1 Mike      Klarm Intern       22

Se enumerará cada fila, empezando con uno para cada combinación unica de datos listados en la sección “PARTITION BY”. Si entonces corres la sentencia DELETE, te quedará lo siguiente

FName LName JobTitle Age
Mike      Klarm Manager  37
Mike      Klarm Operator 32
Mike      Klarm Intern       22

Pero digamos que solo quieres dejar el registro mas reciente. Podrías correr el query de arriba y también borrar cualquier registro donde Age <> 37, pero eso no funciona muy bien si tienes mucha información en la tabla, tendrías que especificar el caso de Mike Klarm para prevenir alguna perdida de información.

Una manera más fácil es modificando la clausula “PARTITION BY”

SELECT RO_NUMBER() OVER (PARTITION BY FName,LName ORDER BY Age DESC) R
,FName
,LName
,JobTitle
,Age
FROM DuplicateRow

Terminaras con algo como esto

R FName LName JobTitle Age
1 Mike      Klarm Manager  37
2 Mike      Klarm Manager  37
3 Mike      Klarm Manager  37
4 Mike      Klarm Operator 32
5 Mike      Klarm Operator 32
6 Mike      Klarm Intern       22

Entonces cuando borres donde R > 1 te quedraá el registro mas reciente. Puedes usar la clausula “PARTITION BY” para obtener muchos ordenes diferentes de la información solo parcialmente duplicada

 

Fuente

Exportar un query a un archivo .txt

Y bien, otra vez un “nunca lo había hecho”, ahora se necesitaba exportar el resultado de un query a un archivo de texto plano, y mientras buscaba, las soluciones cada vez eran mas descabelladas, enredadas,complicadas y extenuantes.

Hasta que encontré la siguiente, me resultó bastante buena así que aquí la tienen,

1. Debes ejecutar lo siguiente

EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE

esto es para asegurarnos que el xp_cmdshell esta habilitado (también pueden habilitarlo desde la configuración de superficie)

2. Se debe ejecutar lo siguiente para que el comando haga lo que tenga que hacer

EXEC xp_cmdshell ‘bcp “SELECT * FROM sysfiles” queryout “C:\bcptest.txt” -T -c -t,’

aqui tenemos que le pasamos el query tal cual lo necesitamos, y le pasamos la ruta donde queremos que cree el archivo de texto (recuerden que la ruta es local, así que si estan conectados a un servidor tendrán que ir por el archivo de texto a esa ruta en ese servidor)

El caso es que el archivo de texto es creado y uds tendran un problema menos que resolver.

Los parámetros que se están utilizando son:

Queryout – es la que permite espeficar el query con el que se trabajara

File name – donde se insertara el resultado (debe ser la ruta completa)

-T, que especifica que la utilidad bcp se conectara a SQL Server con una conección segura, se pude usar -P (contraseña) y -U (usuario)

-c, especifica el tipo de caracteres que se usara para cada campo

-t, permite especificar el delimitador de campo, el caracter que se especifique despues del -t sera el que separe cada campo

-S, se pude usar para espeficar el nombre del servidor. Si se tiene una Instancia nombrada, sera forzoso usar este parámetro. Algo así como,

EXEC xp_cmdshell ‘bcp “select name, type_desc, create_date from sys.objects” queryout “C:\bcptest2.txt” -T -SDEVELOP\DEV1 -c -t,’

Fuente

Obtener los nombres de las tablas en la BD

Bueno pues, si necesitan saber el nombre de las tablas en una base de datos en específico, solo debe accederse al catálogo de tablas, y eso se hace así,

SELECT   name AS object_name ,SCHEMA_NAME(schema_id) AS schema_name,type_desc,create_date,modify_date
FROM sys.objects WHERE type_desc = ‘USER_TABLE’
ORDER BY modify_date;

y listo, obtendremos un resultado como este:

Esta tabla también puede darles los procesdimientos almacenados, funciones, tablas de sistema, etc.

 

Obtener todas las funciones y/o procedimientos almacenados en una base de datos

Este requerimiento resulto ser bastante interesante, sabia que tenía que ir a las tablas de metadatos, pero ¿cual exactamente?, ese era el punto.

Así que aquí esta el query para obtener procedimientos almacenados o funciones en una base de datos:

select *
from
    information_schema.routines

y para obtener los correspondientes parametros,

select *
from
    information_schema.parameters

Saludos!

Data Journalism o Periodismo de Datos

Vaya concepto me he encontrado el día de hoy, Data Journalism, obviamente dado que nunca lo había escuchado antes, fui directito a San Google y encontre un par de definiciones que le dieron un gran valor a esto de los datos.

Data Journalism o Periodismo de datos, es una especialidad del periodismo que refleja el cada vez mas importante rol que están teniendo los datos numéricos usados en la producción y distribución de información en la era digital. Refleja la creciente interacción entre los productores de contenido (periodistas) y muchos otros campos tales como diseño, ciencias de la computación y estadística.  El periodismo de datos ha sido muy usado para reunir varios conceptos y ligarlos al periodismo. Algunos ven esto como niveles o pasos que van desde el mas simple hasta el mas complejo de los usos de la nueva tecnología en el proceso periodistico.

Bueno pues, seguí buscando y encontré un libro llamado Data Journalism handbook, lamentablemente esta en inglés, pero de todos modos lo pongo en las fuentes al final, pues aqui encontre varios puntos que quiero compartir aquí,

El análisis de datos puede revelar “la forma de una historia” (Sarah Cohen), o proveernos con una “nueva camara” (David McCandless). Usando datos el trabajo del periodismta cambia su objetivo princial de ser los primeros en reportar a ser aquellos que nos dicen lo que cierto desarrollo podría significar realmente.

Y bien, esto puede sonar exageradamente abstracto pero en el mismo libro lo ilustran creo de una mejor manera

“(los datos)… , por ejemplo, podrían revelar alguna amenaza abstracta tal como los efectos del desempleo basado en la edad, genero, educación. Usando la información transformamos algo abstracto en algo que todos pueden entender y con lo que pueden relacionarse.”

Aunque sinceramente si algo me gusto fue el siguiente parrafo,

“Volverse reconocido en la busqueda, limpieza y visualización de datos es transformable para la profesión de obtener información. Los periodistas que manejen esto experimentaran que construir artículos sobre hechos e ideas es un alivio. Menos adivinanzas, menor busqueda de frases – en lugar, un periodista pues construir fuerte su posición en base a datos y esto puede afectar grandemente el rol del periodismo.”

Quiza no le encuentren sentido a este tipo de entrada en un blog enfocado a las bases de datos y la información dentro de las mismas, pero me encanta la idea de que la información y su manejo empiece a obtener el crédito que se merece y por que no, que nuestro campo de acción se agrande no nos afecta en lo mas mínimo, sino que al contrario nos abre muchas nuevas opciones =)

 

Fuente 1

Data Journalism handbook

Entendamos con eso de los ERP

Bueno pues, esta semana escuche a una persona decir:

“bueno pues en el nombre dice ERP, así que tiene que hacer de todo”

a mi la verdad solo la frase me dejo muy disconforme, pero preferí no empezar una discusión al respecto ya que no poseía toda la información como me hubiera gustado. Pero para no caer de nuevo en el error, me puse a investigar y esto es lo que obtuve.

Planificación de Recursos Empresariales, ERP (Enterprise Resource Planning) son sistemas de gestión de información que automatizan muchas de las prácticas de negocio asociadas con los aspectos productivos u operativos de una empresa.

Los objetivos principales de un ERP son:

  • Optimización de los procesos empresariales
  • Acceso a la información
  • Posibilidad de compartir información entre todos los componentes de la organización
  • Eliminación de datos y operaciones innecesarias

El software ERP, es diseñado para mejorar tanto las relaciones con clientes externos y las colaboraciones internas al automatizar tareas y actividades que se llevan a cabo en la línea de producción, acortar ciclos de procesos de negocio, e incrementar la productividad del usuario.

Las compañías normalmente buscan un software de sistemas ERP para señalar y corregir procesos de negocio ineficientes o cuando cierto número de problemas complejos existen en el ambiente de negocio. El software de sistemas ERP también son implementados para mejorar las eficiencias operacionales, lograr metas financieras, administrar y coordinar los procesos operacionales de la compañía, reemplazar un software de sistema ERP existente que esta desactualizado o es incapaz de manejar las actividades diarias de la compañía; o para mejorar el manejo de información a traves de una mejor accesibilidad a los datos, decrementar la duplicidad de los datos.La verdad es que con todo lo que se dice de los ERP en la red, uno podría pensar que son la respuesta a todos nuestros problemas, sin embargo, debemos entender varias cosas, entre ellas, un Sistema ERP no es lo mismo que un Softwer Sistema ERP, y bueno esta ha sido mi pelea de siempre con eso de que un SISTEMA no necesariamente es del area de IT, pero bueno, otro día escribiré al respecto.Otra cosa que debemos considerar respecto a los ERPs es el costo de la tecnología, y el costo de la capacitación a los usuarios que debe ser continua, ademas de asegurar la integridad de los datos.En uno de los artículos que encontré mencionaban lo siguiente:

  • El éxito del sistema es completamente dependiente de como los trabajadores lo utilicen.
  • Un ERP requiere mucho trabajo para poder adaptarlo a las necesidades de la compañía. Requiere que sea modificado para que satisfaga las necesidades especificas de la compañía y esto puede ser tanto caro como tedioso.

Tras leer tanto artículos donde se habla de que son lo mejor de lo mejor y leer aquellos que dicen que realmente no se equiparan las ventajas al considerar de cerca las desventajas y posteriormente leer artículos donde se asegura que el ERP va de salida debido a la nueva voga del trabajo en la nube y de la tendencia a no adquirir bienes sino el rentar servicios.

Me queda la sensación de que el ERP debe delimitarse perfectamente y orientarse tanto como pueda a procesos previamente establecidos y así como se maneja en el desarrollo de sistemas desde hace ya un buen rato, concentrarse en que hace y puede hacer por mi y luego entonces llevarlo a su cota superior y obtener lo mejor de él, sin querer que nos resuelva la vida e ir entonces pidiendo indiscriminadamente y terminando con un ERP parchado y crecido hasta límites ridículos, donde será extremadamente difícil el mantenimiento, sino es que imposible.

Fuente 1

Fuente 2

Fuente 3

Roles de nivel Servidor (Server roles)

Son nueve roles de servidor fijos y los permisos concedidos a estos roles no pueden ser cambiados, aunque apartir de SQL Server 2012, ya pueden crearse roles de servidor definidos por el usuario. Se pueden añadir principales de nivel Servidor ( Logins, cuentas de Windows, grupos de Windows) a roles de nivel Servidor. Cada miembro de un rol de servidor fijo puede agregar otro Login a ese mismo rol. Miembros de roles de servidor definidos por el usuario no puede agregar otros principales al rol.

Roles fijos de nivel Servidor

sysadmin  – Los miembros de este rol pueden llevar a cabo cualquier actividad en el servidor

serveradmin – Los miembros pueden cambiar las opciones de configuración del servidor y apagar el servidor

securityadmin – Los miembros administran los logins y sus propiedades. Pueden OTORGAR, DENEGAR, y REVOCAR permisos de nivel servidor. Pueden también OTORGAR, DENEGAR, y REVOCAR permisos a nivel base de datos si tienen acceso a la base de datos. Adicionalmente, pueden resetear contraseñas para Logins de SQL Server.

processadmin – Los miembros pueden terminar procesos que estan corriendo en una instancia de SQL Server

setupadmin – Los miembros pueden agregar y quitar Servidores Ligados (Linked Servers)

bulkadmin – Los miembros pueden correr la sentencia BULK INSERT

diskadmin – Se usa para la administración de archivos en disco

dbcreator – Los miembros pueden crear, alterar, eliminar, y restaurar cualquier base de datos

public – todo login de SQL Server pertenece a este rol de servidor. Cuando a un principal no se le ha otorgado o negado permisos especificos sobre un objeto, el usuario hereda los permisos otorgados al objeto publico. Solo asigne permisos Public sobre un objeto cuando quiera que el objeto este disponible para todos los usuarios.

 

Permisos a nivel servidor

Solo los permisos de nivel servidor pueden ser agregados a roles de servidor definidos por el usuario. Y la siguiente sentencia nos dará el listado de los permisos de nivel servido.

SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name