Consultar um armazém de dados

Quando as tabelas de dimensões e fatos em um data warehouse tiverem sido carregadas com dados, você poderá usar SQL para consultar as tabelas e analisar os dados que elas contêm. A sintaxe Transact-SQL usada para consultar tabelas em um pool SQL dedicado Synapse é semelhante ao SQL usado no SQL Server ou no Banco de Dados SQL do Azure.

Agregação de medidas por atributos de dimensão

A maioria das análises de dados com um data warehouse envolve a agregação de medidas numéricas em tabelas de fato por atributos em tabelas de dimensão. Devido à forma como um esquema de estrela ou floco de neve é implementado, as consultas para a execução deste tipo de agregação dependem de cláusulas JOIN para conectar tabelas de fatos a tabelas de dimensão, e de uma combinação de funções agregadas e cláusulas GROUP BY para definir as hierarquias de agregação.

Por exemplo, o SQL a seguir consulta as tabelas FactSales e DimDate em um data warehouse hipotético para agregar valores de vendas por ano e trimestre:

SELECT  dates.CalendarYear,
        dates.CalendarQuarter,
        SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter
ORDER BY dates.CalendarYear, dates.CalendarQuarter;

Os resultados dessa consulta seriam semelhantes à tabela a seguir:

Você pode unir quantas tabelas de dimensão forem necessárias para calcular as agregações necessárias. Por exemplo, o código a seguir estende o exemplo anterior para dividir os totais de vendas trimestrais por cidade com base nos detalhes de endereço do cliente na tabela DimCustomer :

SELECT  dates.CalendarYear,
        dates.CalendarQuarter,
        custs.City,
        SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
JOIN dbo.DimCustomer AS custs ON sales.CustomerKey = custs.CustomerKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter, custs.City
ORDER BY dates.CalendarYear, dates.CalendarQuarter, custs.City;

Desta vez, os resultados incluem um total trimestral de vendas para cada cidade:

Junta-se em um esquema de flocos de neve

Ao usar um esquema de flocos de neve, as dimensões podem ser parcialmente normalizadas; exigindo várias junções para relacionar tabelas de fatos com dimensões de flocos de neve. Por exemplo, suponha que seu data warehouse inclua uma tabela de dimensões DimProduct a partir da qual as categorias de produtos foram normalizadas em uma tabela DimCategory separada. Uma consulta para agregar itens vendidos por categoria de produto pode ser semelhante ao exemplo a seguir:

SELECT  cat.ProductCategory,
        SUM(sales.OrderQuantity) AS ItemsSold
FROM dbo.FactSales AS sales
JOIN dbo.DimProduct AS prod ON sales.ProductKey = prod.ProductKey
JOIN dbo.DimCategory AS cat ON prod.CategoryKey = cat.CategoryKey
GROUP BY cat.ProductCategory
ORDER BY cat.ProductCategory;

Os resultados desta consulta incluem o número de itens vendidos para cada categoria de produto:

Usando funções de classificação

Outro tipo comum de consulta analítica é particionar os resultados com base em um atributo de dimensão e classificar os resultados dentro de cada partição. Por exemplo, você pode querer classificar as lojas a cada ano pela receita de vendas. Para atingir esse objetivo, você pode usar Transact-SQL funções de classificação , como ROW_NUMBER, RANK, DENSE_RANKe NTILE. Essas funções permitem que você particione os dados em categorias, cada uma retornando um valor específico que indica a posição relativa de cada linha dentro da partição:

• ROW_NUMBER retorna a posição ordinal da linha dentro da partição. Por exemplo, a primeira linha é numerada como 1, a segunda 2 e assim por diante.
• RANK retorna a posição classificada de cada linha nos resultados ordenados. Por exemplo, em uma partição de lojas ordenadas por volume de vendas, a loja com o maior volume de vendas é classificada como 1. Se várias lojas tiverem os mesmos volumes de vendas, elas serão classificadas da mesma forma, e a classificação atribuída às lojas subsequentes reflete o número de lojas que têm volumes de vendas mais altos - incluindo empates.
• DENSE_RANK classifica linhas em uma partição da mesma forma que RANK, mas quando várias linhas têm a mesma classificação, as linhas subsequentes são posições de classificação ignoram empates.
• NTILE retorna o percentil especificado no qual a linha cai. Por exemplo, em uma partição de lojas ordenadas por volume de vendas, NTILE(4) retorna o quartil em que o volume de vendas de uma loja o coloca.

Por exemplo, considere a consulta seguinte:

SELECT  ProductCategory,
        ProductName,
        ListPrice,
        ROW_NUMBER() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS RowNumber,
        RANK() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Rank,
        DENSE_RANK() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS DenseRank,
        NTILE(4) OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Quartile
FROM dbo.DimProduct
ORDER BY ProductCategory;

A consulta divide os produtos em agrupamentos com base em suas categorias e, dentro de cada partição de categoria, a posição relativa de cada produto é determinada com base em seu preço de tabela. Os resultados dessa consulta podem ser semelhantes à tabela a seguir:

Recuperando uma contagem aproximada

Embora o objetivo de um armazém de dados seja principalmente dar suporte a modelos de dados analíticos e relatórios para a empresa; Analistas de dados e cientistas de dados geralmente precisam realizar alguma exploração inicial de dados, apenas para determinar a escala básica e a distribuição dos dados.

Por exemplo, a consulta a seguir usa a COUNT função para recuperar o número de vendas para cada ano em um data warehouse hipotético:

SELECT dates.CalendarYear AS CalendarYear,
    COUNT(DISTINCT sales.OrderNumber) AS Orders
FROM FactSales AS sales
JOIN DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear
ORDER BY CalendarYear;

Os resultados dessa consulta podem ser semelhantes à tabela a seguir:

O volume de dados em um data warehouse pode significar que até mesmo consultas simples para contar o número de registros que atendem aos critérios especificados podem levar um tempo considerável para serem executadas. Em muitos casos, uma contagem precisa não é necessária - uma estimativa aproximada será suficiente. Nesses casos, você pode usar a APPROX_COUNT_DISTINCT função como mostrado no exemplo a seguir:

SELECT dates.CalendarYear AS CalendarYear,
    APPROX_COUNT_DISTINCT(sales.OrderNumber) AS ApproxOrders
FROM FactSales AS sales
JOIN DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear
ORDER BY CalendarYear;

A APPROX_COUNT_DISTINCT função usa um algoritmo HyperLogLog para recuperar uma contagem aproximada. É garantido que o resultado tenha uma taxa de erro máxima de 2% com 97% de probabilidade, de modo que os resultados desta consulta com os mesmos dados hipotéticos de antes podem ser semelhantes à tabela a seguir:

As contagens são menos precisas, mas ainda assim suficientes para uma comparação aproximada das vendas anuais. Com um grande volume de dados, a consulta usando a função é concluída mais rapidamente, e a precisão reduzida pode ser uma compensação aceitável durante a APPROX_COUNT_DISTINCT exploração de dados básicos.