Use SQL simple para administrar sus datos en Power Pivot

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Cuando comencé a aprender Power Pivot, aprendí que hay 2 formas de importar datos. Hay la «forma estándar» que usa el asistente de importación de tablas – bueno para nosotros, gente de Excel. Hay una segunda forma que usa el Editor de consultas para escribir código SQL y extraer los datos. lo necesita directamente de la base de datos. El problema es que necesita saber SQL para poder hacer esto. Así que desde el principio decidí que este segundo enfoque no era una herramienta muy útil para el usuario típico de Excel.
import-choices-5727919

Sin embargo, recientemente me encontré con algunos desafíos de la vida real que necesitaba resolver. Tengo un workbook «maestro» muy grande que tiene todas mis medidas y todos mis datos (durante varios años) cargados; tiene casi 400 MB y contiene más de 40 millones de filas de datos. Guardo una copia maestra para no tener que volver a escribir todas estas medidas cada vez que tengo una nueva solicitud de informe. Pero, por otro lado, si alguien quiere un informe que diga que contiene solo 10 productos, no quiero enviarle el libro completo de 400 MB.

Comencé escribiendo vistas personalizadas que contienen un subconjunto de datos directamente en SQL Server y luego volver a apuntar mi workbook a esta nueva vista. Pero pronto me di cuenta de que esto iba a crear un lío en mi base de datos de SQL Server. Sé que puedo seleccionar manualmente cada producto que necesito de la lista de filtros en el editor de tablas, pero esto es un trabajo duro si tiene más de un par de productos.
manual-9792588

¿Entonces lo que hay que hacer?

Luego recordé el editor de consultas SQL alternativo y pensé que esta podría ser la solución. Y de hecho lo es. Así es como hago para resolver este problema comercial en particular. Es fácil de aprender, simplemente siga el patrón que utilizo a continuación.

Primero clone su workbook maestro

Piense en este Libro de trabajo maestro como un activo en crecimiento que construye y mejora gradualmente como un campeón de BI de autoservicio. Cargue todos sus datos, cree las relaciones y escriba todas sus medidas útiles y reutilizables en este workbook. Ahora que tiene su maestro, puede tomar una copia de este workbook y darle un nombre nuevo cada vez que necesite un informe nuevo. Clona el maestro y dale un nuevo nombre.

Luego actualice la copia clonada con un subconjunto de datos

Aquí es donde puede usar una cláusula WHERE de SQL simple para decirle a Power Pivot que traiga SOLO las ventas y los productos que necesita. Usaré la declaración SQL de la siguiente manera.

WHERE productkey IN enter, a, list, of, your, product, keys, here, separated, by, commas

SQL es un lenguaje muy simple y fácil de aprender. Hay muchos recursos en línea de los que puede aprender. En este caso, no necesita saber SQL en absoluto, simplemente puede copiar el patrón que le mostraré. SQL utiliza un «lenguaje reconocible en inglés», por lo que podrá averiguar rápidamente lo que está haciendo. En la forma más simple, una declaración SQL es la siguiente (esta es la sintaxis que usaré en esta demostración).

SELECT some columns of data
FROM   some table
WHERE  some condition exists

Cree una lista de los códigos de producto que desea incluir en su nuevo workbook

Ahora querrá crear una lista de productos que desee en su informe. Para hacer esto, creo una lista de los códigos de producto en mi libro de Excel que estoy usando. Copio la lista de códigos en un área en blanco de la hoja de cálculo y luego los uso para crear la última parte de la cláusula WHERE que se muestra arriba (solo el bit IN). Tengo una pequeña macro para ayudar con esto que puede copiar y usar si lo desea, o simplemente puede escribirla manualmente.
create-in-clause-5554793
Aquí está el código VBA

Sub CombineCommasSQLCode()
    Dim Cell As Range, mySelection As Range
    Set mySelection = Selection
    For Each Cell In mySelection
        If Cell.Address <> mySelection.Address Then
            If Cell.Address <> mySelection.Range("A1").Address Then
                If Cell.Value <> "" Then
                    mySelection.Range("A1").Value = mySelection.Range("A1").Value & ", " & Cell.Value
                    Cell.Clear
                End If
            End If
        End If
    Next Cell
    mySelection.Range("A1").Value = "in (" & mySelection.Range("A1").Value & ")"
End Sub

Entonces, ahora que tiene su talón de cláusula IN, es hora de ir a su tabla de ventas y limitar los datos que se devuelven a Power Pivot a esta lista de códigos de producto. Para hacer esto, vaya a Power Pivot, seleccione la tabla correcta (ventas en este caso) y luego haga clic en PROPIEDADES DISEÑABLES. Desde allí, puede cambiar el método de obtención de datos de “VISTA PREVIA DE LA TABLA” a “EDITOR DE CONSULTA”.
switch-to-8146130

Luego, simplemente agregue la cláusula WHERE a la declaración SQL existente. Si necesita encontrar el nombre de campo para su clave de producto, es el mismo que el nombre de la columna de la tabla (suponiendo que no lo haya cambiado).

add-where-clause-7723977

Repita para la tabla de productos.

Una vez que haya hecho esto para su tabla de Ventas, debe repetir el proceso para su tabla de productos. No tiene sentido traer datos de productos para productos que no están en su tabla de ventas.

He producido un video que muestra el proceso de principio a fin a continuación.

El sitio web que utilicé en el video para formatear el código SQL se puede encontrar aquí => http://www.dpriver.com/pp/sqlformat.htm

Espero que te guste este truco y encuentres inspiración para otras formas de utilizar el Editor de consultas. Me encantaría saber de usted cómo usa el Editor de consultas; publique sus comentarios a continuación.

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.