sql server - servidor - ¿Qué debería tener en cuenta al construir una solución OLAP desde cero?

error al implementar cubo analysis services (2)

Estoy trabajando para una empresa que ejecuta un producto de software basado en un servidor de base de datos MS SQL, y a través de los años he desarrollado 20-30 informes bastante avanzados en PHP, tomando datos directamente de la base de datos. Esto ha sido muy exitoso, y la gente está feliz con eso.

Pero tiene algunos inconvenientes:

Para nuevos cambios, puede ser bastante intensivo en desarrollo
El usuario no puede experimentar mucho con los datos: está bloqueado en una vista codificada
Puede ser lento para grandes informes

Estoy considerando ir gradualmente a un enfoque basado en OLAP, que se puede consultar desde Excel o algún servicio basado en web. Pero me gustaría hacer esto de una manera que introduzca la menor cantidad de nueva complejidad en el entorno de TI: la menor cantidad de servicios diferentes, trabajos de sincronización, etc.

Tengo algunas preguntas al respecto:

1) relacionado con el flujo de trabajo:

¿Cuál es una buena ruta de desarrollo de "Black Box SQL Server" a "OLAP listo para usar"?
¿Qué servidores y servicios deberían configurarse y qué scripts deberían escribirse?
¿Cuáles son las partes más difíciles / más críticas / que requieren más tiempo?

2) ETL:

¿Supongo que es mejor tener servidores separados para su Data Warehouse y Production SQL?
¿Cómo se mantienen sincronizados (push / pull)? ¿Utilizando qué tecnologías / idiomas?
Para mí, SSIS parece demasiado complicado, y el flujo de trabajo gráfico no me atrae mucho; prefiero un script basado en texto que haga el trabajo. ¿Es esto factible?
¿O es ventajoso utilizar el cliente gráfico con una sola fuente y un destino?

3) Desarrollo:

¿Cuánto de esto (integración de datos, servicios de análisis) se puede mantener de manera eficiente desde una herramienta CLI?
¿Se puede transferir la configuración entre la producción y el desarrollo fácilmente?

Estoy satisfecho con cualquier respuesta que cubra solo algo de esto, y aunque es un entorno de MS, también me interesan las ventajas de otras tecnologías.

Básicamente estás haciendo la pregunta del millón de dólares de "¿Cómo construyo un DWH?". Esta no es realmente una pregunta que pueda ser respondida decisivamente.

Sin embargo, aquí hay un kickstart:

Si está buscando un producto viable mínimo, tenga en cuenta que se encuentra en un entorno de datos, y no de software puro. En entornos con muchos datos, es mucho más difícil crear un producto incrementalmente, porque la cantidad de esfuerzo para introducir cambios en el sistema es mucho mayor. Piense en ello como si cada cambio que realiza en una pieza de software tiene que ser de alguna manera compatible con cualquier cosa que haya hecho alguna vez. Ahora entiendes en qué demonios está Microsoft :-).

Además, los sistemas de datos involucran muchas herramientas de terceros como bases de datos, herramientas ETL y plataformas de informes. Las elecciones que haga deben ser viables para el desarrollo esperado de su sistema, de lo contrario, es posible que tenga que reemplazar por completo estas herramientas más adelante.

Si bien puede comenzar con una clonación de base de datos que se basará en SQL de copia simple y luego agregarlo o insertarlo en un OLAP, le recomiendo que se ensucie las manos con una herramienta ETL real desde el principio. Esto es especialmente cierto si prevé la necesidad de crecer. 9 de cada 10 veces, la necesidad crecerá.

MS-SQL es una buena opción para un DB si no le importa el costo. La herramienta ETL natural sería SSIS, y es una herramienta sólida también.

Incluso si sus primeras transformaciones son meramente "tome esta tabla y vacíela allí", todavía gana mucho en términos de gestión de procesos (¿ha ejecutado el trabajo? ¿Qué sucede si falla ?, etc.) y depuración. Además, es más fácil crecer orgánicamente a medida que los requisitos y / o casos especiales tienen que ser tratados.

Solo tengo experiencia con Microsoft OLAP, así que aquí están mis dos centavos con respecto a lo que sé:

Si está implementando cubos, entonces separe el servidor SQL de producción de la fuente de los cubos. Los cubos requieren una gran cantidad de SELECT DISTINCT column_name FROM source.table. No quiere que el procesamiento de cubos bloquee su sistema de producción crítico para la misión.
Aunque puede implementar cubos OLAP con tablas de relaciones estándar, encontrará rápidamente que, a menos que sus datos sean un sistema de libro mayor, probablemente necesite reprocesar completamente sus tablas de hechos y dimensiones, y esto requerirá volver a consultar la base de datos de origen una y otra vez. Ese es un gran argumento para construir un almacén de datos separado que usa transacciones estilo libro mayor para las tablas de hechos. Por ejemplo, si un cliente ordena algo y luego lo cancela, su sistema de origen puede rastrear esto como un cambio de estado. En su tabla de hechos, probablemente necesite mostrar esto como una fila para pedidos que tiene una cantidad positiva y una secuencia de ingresos y una fila para cancelar que tiene una cantidad negativa y un flujo de ingresos.
OLAP puede ser excesivo para su entorno. El principal problema que parecía plantear era que sus informes son estáticos y los usuarios quieren acceder a los datos directamente. Podría construir un modelo de datos y dar acceso a los creadores de informes en SSRS, o informar el acceso de escritura en otro conjunto de BI como Cognos, Business Objects, etc. Generalmente, no recomiendo este enfoque ya que es mucho más de lo que deberían tener la mayoría de los usuarios. saber para obtener datos, pero en una tienda pequeña esto puede ser suficiente y es fácil de implementar. Afrontémoslo: los usuarios generalmente solo quieren obtener los datos en Excel para manipularlos aún más. Por lo tanto, si no desea darles un front-end web y solo desea que accedan a los datos de Excel, podría darles acceso directo a la base de datos a una copia de los datos de producción. La desventaja de este enfoque es que los usuarios generalmente no entienden las relaciones SQL o de bases de datos. OLAP le ayuda a evitar forzar a los usuarios a aprender SQL o relaciones, pero no es fácil de implementar en su extremo. Si solo tiene un par de usuarios avanzados que necesitan este tipo de acceso, podría ser fácil enseñarles a los pocos usuarios avanzados cómo realizar consultas básicas en Excel en la base de datos, y les complacerá recibirlo mañana. OLAP no estará listo mañana.
Si solo tiene algunos tipos de sistemas de datos de origen, podría salirse con la suya creando un informe estático superdinámico. Por ejemplo, tengo un informe que fue escrito en C # que básicamente permite a los usuarios seleccionar tantas columnas como deseen de una lista de 30 columnas y filtrar los datos en algunos campos de intervalos de fechas y listas de filtros de campo. Este sencillo informe cubre aproximadamente el 40% de todas las solicitudes de informes ad hoc de usuarios finales, ya que cubre todas las métricas y campos básicos y básicos del cliente. Recientemente trasladamos este informe a SSRS y eso nos permitió incrementar el número de campos a aproximadamente 100 y mejorar la experiencia general del usuario. Independientemente de la plataforma de informes, es posible ofrecer a los usuarios cierta flexibilidad dinámica incluso en los límites de un sistema de informes estáticos.
Si solo tiene un par de bases de datos, probablemente pueda respaldar y restaurar las bases de datos como su ETL. Sin embargo, si desea hacer algo más allá de eso, entonces también puede morder la bala y usar SSIS (o alguna otra herramienta de ETL). Una vez que ingresa a ETL para el almacenamiento de datos, va a utilizar una herramienta de diseño orientada a gráficos. La codificación funciona bien para las aplicaciones, pero ETL se trata más de flujos de trabajo y es por eso que las herramientas tienden a converger en una interfaz gráfica de usuario. Puede solucionar esto e intentar codificar un depósito de datos desde un editor de texto, pero al final perderá mucho. Consulte esta publicación para obtener más detalles sobre las diferencias entre cargar datos desde el código y cargar datos desde SSIS .

RETROALIMENTACIÓN SOBRE CÓMO USAR CUBOS CON UNA TIENDA DE DATOS RELATIVA

Es posible implementar un cubo sobre un almacén de datos relacional, pero existen algunos problemas importantes con el uso de este enfoque. La razón principal por la que es técnicamente factible tiene que ver con la forma en que configura su DSV. El DSV es esencialmente una capa lógica entre la base de datos física y las definiciones de cubo / dimensión. En lugar de importar las tablas relacionales al DSV, puede definir consultas con nombre o crear vistas en la base de datos que aplanan los datos.

La ventaja de este enfoque es la siguiente:

Es relativamente fácil de implementar ya que no tiene que construir un subsistema ETL completo para comenzar con OLAP.
Este enfoque funciona bien para crear prototipos de cómo desea construir una solución a más largo plazo. Puede crear un prototipo en 1-2 días y mostrar algunos de los beneficios de OLAP hoy.
Algunas tablas muy, muy grandes no tienen que estar completamente duplicadas solo para admitir un cubo OLAP. Tengo varias tablas de fila multimillonarias que son tablas de hechos casi completamente estandarizadas. Las únicas columnas que no tienen son las claves de fecha y también contienen algunos valores NULOS en campos que no deberían tener nulos. En lugar de duplicar estas tablas muy masivas, puede crear las claves de fecha sustituta y establecer valores para los nulos en la vista o consulta con nombre. Si no va a ver una gran ventaja de rendimiento para duplicar la tabla, entonces esto puede ser un candidato para dejarlo en un formato más crudo en la base de datos.

Las desventajas de este enfoque son las siguientes:

Si no ha construido un verdadero almacén de datos del método Kimball, entonces probablemente no esté rastreando las transacciones en un libro mayor. Las tablas de hechos del método Kimball (al menos tal como yo las entiendo) siempre cambian los valores sumando y restando filas. Si alguien cancela parte de un pedido, no puede actualizar el valor en el cubo para la transacción individual. En cambio, debe equilibrar la transacción con un valor negativo. Si tiene que actualizar la transacción, tendrá que reprocesar completamente la partición del cubo para reemplazar el valor, que puede ser una operación muy costosa. A menos que su sistema fuente sea un sistema de transacción estilo libro de contabilidad, probablemente tendrá que crear una copia al estilo libro mayor en su subsistema ETL.
Si no construye un almacén de datos del método Kimball, entonces probablemente esté utilizando claves principales no obstruidas y posiblemente no enteros en su base de datos. Esto afecta directamente el rendimiento de la consulta dentro del cubo. También lo configura para tener un almacén de datos teóricamente inflexible. Por ejemplo, si tiene un sistema de pedido de productos que usa una clave entera y comienza a utilizar un segundo sistema de pedido de productos como reemplazo del sistema heredado o en conjunto con el sistema heredado, puede tener dificultades para combinar los datos simplemente mediante el DSV ya que cada sistema tiene diferentes puntos de datos, métricas, flujos de trabajo, tipos de datos, etc. Peor aún, si tienen los mismos tipos de datos para la identificación del pedido y los valores de la identificación del pedido se superponen entre los sistemas, entonces debe declarar una clave sustituta que usted puede usar en ambos sistemas. Esto puede ser difícil, pero no imposible, de implementar sin utilizar un depósito de datos aplanado.
Es posible que deba construir el sistema dos veces si comienza con el almacén de datos relacionales y luego pasa a la base de datos plana. Francamente, creo que la cantidad de trabajo duplicado es trivial. La mayoría de lo que aprendiste al construir el cubo en un almacén de datos relacional se traducirá en la configuración del nuevo cubo OLAP. El principal problema, sin embargo, es que probablemente creará un nuevo cubo por completo y luego los usuarios del antiguo cubo tendrán que migrar al nuevo cubo. Cualquier informe creado en SSRS o Excel probablemente se rompa en ese punto y necesite ser reescrito desde cero. Entonces, el costo principal de reconstruir el cubo está realmente en la reconstrucción de objetos dependientes, no en la reconstrucción del cubo mismo.

Avíseme si desea que amplíe cualquiera de los puntos anteriores. buena suerte.