Moving Average In Sql Server 2008 R2

A Bakers Dozen: 13 dicas de energia para usar o SQL Server Reporting Services 2008 R2 Kevin S Goff Kevin S. Goff, um Microsoft SQL Server MVP, está escrevendo a série de produtividade The Bakers Dozen na revista CoDe desde 2004. Kevin hospeda um webcast semanal no SQL Tópicos de Servidor e Business Intelligence, no BDBIRadio. Kevin tem sido um desenvolvedor de aplicativos, arquiteto e mentor de tecnologia desde 1987. Kevin trabalhou em várias linhas de negócios, incluindo seguros, fabricação, cuidados de saúde, bens de consumo, contabilidade e finanças, publicidade e muitos outros. Ao longo do caminho, Kevin ganhou vários prêmios no setor público e privado para o desenvolvimento de aplicativos. Além disso, Kevin ensinou aulas de SQL Server, Business Intelligence e Data Warehousing a centenas de estudantes durante um período de cinco anos e criou inúmeros módulos personalizados de cursos ao longo do caminho. Kevin é autor de um livro sobre o desenvolvimento de relatórios e contribuiu com capítulos sobre a programação MDX para um segundo livro. Atualmente, ele está desenvolvendo um conjunto de cursos de treinamento de vídeo comercial para tópicos de SQL Server e BI. Ele tem sido um MVP do SQL Server desde 2010 e anteriormente era um MVP do. NETC de 2005 a 2009. Kevin é um orador freqüente no Grupo de Usuários e outros eventos da comunidade (SQL Saturday, SharePoint Saturday, Code Camp) na região do Atlântico Médio, E fala ocasionalmente em conferências. Em 2012, Kevin trabalhou com o Microsoft TechNet Radio em uma série de webcast de 13 semanas sobre novos recursos no SQL Server 2012. Para obter mais informações, confira o site principal do Kevins no KevinSGoff. net ou envie-o por email para kgoffkevinsgoff. net. Este artigo foi publicado em: This O artigo foi arquivado em: Com cada nova versão do SQL Server Reporting Services, a Microsoft continua oferecendo novas funcionalidades para relatórios. O SQL Server 2008 R2, lançado para a fabricação em meados de 2010, representa o quarto lançamento da Reporting Services da Microsofts nos últimos 10 anos. Esta quarta versão se concentra na geração de mapas e em vários recursos de visualização de negócios inteligencedata. Neste artigo, Ill demonstra 13 características significativas no SSRS. O que está no menu Recentemente, participei de uma apresentação onde o falante parecia ter demorado para chegar à carne da demonstração. Então eu dou direito aos meus 13 tópicos - no estilo Sergeant Joe Friday (ou seja, os fatos, apenas os fatos). Aqui estão os 13 itens no menu para este artigo: Implantando relatórios para servidores nativos ou SharePoint. Criando mapas SSRS usando o SSRS State Map Gallery. Criando mapas SSRS usando dados geoespaciais. Vinculando os dois mapas SSRS juntos usando uma ação de relatório. Outro exemplo usando dados geoespaciais, mas com um zoom personalizado em uma página da web. Criando um relatório de tabela dinâmica com o controle de relatório Tablix. Criando relatórios com parâmetros em cascata. Criando relatórios contra cubos OLAP. Criando relatórios com indicadores de desempenho e sparklines. Executando relatórios de C e exportando para PDF. SSRS 2008 R2 Potpourri. Bakers Dozen Spotlight: assinaturas baseadas em dados. Subreports. Então, o que você terá no final A Figura 1 mostra um projeto SSRS no Business Intelligence Development Studio (BIDS). O projeto contém mais de uma dúzia de relatórios, com três fontes de dados compartilhadas. As fontes de dados são dsADW2008 (o banco de dados relacional OLTP para AdventureWorks), dsADW2008DW (o banco de dados relacional OLTP data-mart para AdventureWorks) e dsADW2008OLAP (o banco de dados OLAP para AdventureWorks). Dica 1: Implantando relatórios para servidores nativos ou SharePoint Este artigo pressupõe que você conhece os conceitos básicos de criação de relatórios SSRS em BIDS e que você conhece os conceitos básicos de consulta de dados para relatórios. Depois de criar relatórios em BIDS e testá-los com a opção BIDS preview, o próximo passo é implantar relatórios em um servidor. O servidor pode ser um servidor QA, um servidor de desenvolvimento de teste ou um servidor de produção. O Reporting Services suporta dois tipos diferentes de destinos de implantação: modo integrado do SharePoint ou modo SSRS nativo (não SharePoint). Com o modo integrado do SharePoint, você implanta relatórios de SSRS e fontes de dados em uma biblioteca de documentos do SharePoint e você pode aproveitar os recursos do SharePoint. No modo SSRS nativo, você implanta relatórios em um ambiente web que é dedicado exclusivamente ao Reporting Services. Para configurar o SSRS para qualquer modo em um servidor (ou mesmo um ambiente de desenvolvimento), primeiro você precisa definir o tipo de configuração. A Figura 2 mostra a tela de Configuração do SSRS 2008 R2, onde você pode fornecer o banco de dados do Servidor de Relatório para o modo SSRS nativo ou o modo integrado do SharePoint. Além disso, você precisará configurar o URL do Serviço da Web, que geralmente será algo como Serverreportserver. Figura 2: banco de dados do Servidor de Relatório do SSRS 2008 R2 Configuration Manager. Note-se que, no modo SSRS nativo, não é mais necessário (no SSRS 2008 e 2008 R2) executar o IIS - porque o SSRS usa a pilha de rede SQL. Obviamente, se você está implantando relatórios SSRS em um ambiente do SharePoint, o IIS é necessário. No final do SharePoint, você precisará instalar o Suplemento do Reporting Services para SharePoint (disponível como download gratuito para MOSS 2007 e integrado na instalação de pré-requisitos do SharePoint 2010). Você também precisará acessar o Gerenciamento de aplicativos gt do SharePoint Administration Central e, em seguida, configurar a integração do servidor de relatório para usar o mesmo URL do Serviço da Web que seja definido de volta na área de Configuração do SSRS. Depois de configurar o SSRS e sua área de implantação de destino, você pode executar um projeto SSRS e definir as propriedades do projeto. A Figura 3 mostra as propriedades de um projeto SSRS ao implementar em um local do SharePoint. Nota: é assumido que existe uma coleção de site ou site do SharePoint já existente. Então, suponha que eu criei uma localização do site chamada MyServersitesMySiteCollection, uma biblioteca de documentos de relatório no Site Collection chamada MyReportLibrary e um projeto de relatório chamado MyReportProject. Aqui estão as entradas necessárias na página de propriedades do projeto: ao implantar em um servidor de relatório nativo (não-SharePoint), a localização do URL é muito mais simples. No meu caso, eu tenho um servidor local chamado localhost. E uma instância de relatório do banco de dados do SQL2008 R2. Nesse caso, aqui estão as entradas necessárias na página de propriedades do projeto (Figura 4): TargetReportFolder: MyReportProject myserversitesMySiteCollectionMyReportLibraryMyReportProject TargetServerURL: localhostReportServerSQL2008 R2 Uma vez implantado, você pode revisar relatórios no Gerenciador de Relatórios do SSRS (ao usar o modo SSRS nativo, Figura 5) , Ou na coleção de sites do SharePoint (Figura 6). Dica 2: Criando Mapas do SSRS Usando o SSRS State Map Gallery O SSRS 2008 R2 eo Report Builder 3.0 fornecem novas funcionalidades para criar mapas. Isso abre grandes possibilidades para visualizações geográficas. Pode-se escrever um artigo inteiro apenas sobre os novos recursos de mapeamento, mas eu tentarei mostrar alguns recursos aqui. A Figura 7 mostra um mapa de estados dos EUA com estados codificados por cores por vendas usando uma Galeria de Mapas incorporada e um conjunto de dados contendo vendas por estado. O usuário pode passar o mouse sobre um estado e ver uma dica de ferramentas de vendas e ranking de lucro. O relatório também mostra um gráfico de eixo duplo que traça as vendas por estado como um gráfico de colunas e uma porcentagem de margem de lucro por estado como um gráfico de dispersão. O SSRS 2008 R2 eo Report Builder 3.0 oferecem novas funcionalidades para criar mapas. Isso abre grandes possibilidades para visualizações geográficas. Para criar isso, primeiro preciso criar um conjunto de dados para os dados de suporte para o mapa: criou um novo conjunto de dados contra o cubo ADWOLAP (dsSalesByState). Recuperou o valor das vendas do revendedor e a margem de lucro bruto do revendedor da tabela de fatos de vendas do revendedor e também recuperou o atributo Geografia Estado-Província. Defina um filtro interno para visualizar somente as províncias estaduais nos Estados Unidos e criou um filtro de parâmetros para que o usuário selecione um Ano Fiscal. Criou dois membros calculados pelo MDX para classificar cada estado pelas vendas e pela margem de lucro bruto (o projeto de download contém o código MDX). O próximo passo é criar o mapa usando o Assistente de mapas. Este é um processo incrivelmente fácil: arraste um objeto de mapa da caixa de ferramentas para a área principal do relatório. Isso invoca o SSRS Map Wizard. Selecione Map Gallery para o Map Source, selecione EUA por Estado Exploded. E depois selecione Avançar. Verifique Adicionar uma camada de mapas do Bing. Selecione um tipo de telha de híbrido. E depois selecione Avançar. Para a opção Escolher mapa de visualização, selecione Mapa analítico de cores. E depois selecione Avançar. Para a opção Choose Analytic DataSet, selecione o conjunto de dados do primeiro conjunto de etapas (dsSalesByState). Na tela Especificar campos de correspondência, combine a coluna para StateName e, em seguida, selecione a coluna de conjunto de dados StateValue. Selecione um tema (Mahogany), selecione a soma do valor das vendas do revendedor como o campo para visualizar e, em seguida, selecione uma regra de cores de Vermelho-Amarelo Verde. Para adicionar uma dica de ferramenta para que um usuário possa passar o mouse sobre um estado e visualizar as informações de vendas e classificação, clique com o botão direito do mouse no mapa, vá para as camadas do mapa, clique com o botão direito do mouse nas propriedades da camada de polígono e, em seguida, insira a seguinte fórmula A expressão da fórmula de dica: dica 3: Criando mapas SSRS usando dados geoespaciais do mapa na Figura 7. Um usuário pode querer clicar em um estado e iniciar um segundo relatório que mostra os locais do vendedor e as vendas dentro do estado (Figura 8 e Figura 9). Este segundo mapa irá traçar as vendas usando as coordenadas geoespaciais para cada fornecedor. Você pode usar o novo tipo de dados de geografia no SQL Server 2008 (e as localizações de endereço geoespacial encontradas na tabela Person. Address em AdventureWorks). Para realizar isso, eu primeiro (mais uma vez) preciso criar um conjunto de dados de suporte: crie um conjunto de dados (dsVendorSales) que recupera as vendas do fornecedor, a identificação do fornecedor e o nome do fornecedor, o nome da cidade, o ranking do vendedor e os dados geográficos da localização espacial da pessoa. Tabela de endereços. A Figura 8 mostra um procedimento armazenado que captura essas informações para um estado específico como um parâmetro. Posso então arrastar um mapa para a área de layout, mas desta vez no assistente de mapa, posso especificar a opção SQL Server Spatial Query para a fonte dos dados espaciais. Eu também preciso definir o tipo de camada como um ponto, desde que eu forneço pontos de dados geoespaciais. Dica 4: Vinculando os dois mapas SSRS juntos Usando uma ação de relatório Para que um usuário possa clicar em um estado no primeiro mapa e iniciar um mapa mais detalhado, preciso definir uma ação de relatório no primeiro relatório. Eu posso ir às propriedades de polígono do primeiro mapa, vá para a página de ação na caixa de diálogo de propriedades e, em seguida, configure a ação para Ir para Relatório, especifique o nome do relatório filho e especifique os parâmetros de relatório filho que eu quero Preencha o relatório pai principal. Dica 5: Outro exemplo Usando dados geoespaciais, mas com um zoom personalizado em uma página da Web Quando eu estava aprendendo o novo tipo de dados geoespaciais no SQL Server 2008, criei um aplicativo de relatório de mapa de demonstração que traçou meus restaurantes favoritos na área de Atlanta, Geórgia . (Eu morava em Atlanta no início dos anos 90.) O resultado é a Figura 10 e a Figura 11. Que mostram um relatório SSRS dentro de uma página da web, com alguns controles de extensor de controle deslizante muito básicos da biblioteca ASP. NET AJAX para ampliar e deslizar para o norte e para o leste. Figura 10: mapa SSRS usando pontos geoespaciais de restaurantes em Atlanta, Geórgia. Figura 11: mapa SSRS com conjunto de zoom. Além disso, preenchi a tabela de endereços usando um Serviço da Web (Lista 1) e um procedimento armazenado (Listagem 2). Aqui estão os passos que eu passei: criei duas tabelas de banco de dados do SQL Server para armazenar o nome do restaurante e as informações de endereço. Escreveu um pequeno aplicativo em C para chamar um serviço público público gratuito (geocoder. us) que receberá um endereço, cidade e estado e retornará os pontos de latitude e longitude (Listagem 1). Escreveu um pequeno procedimento armazenado (Listagem 2) que recebe os pontos de latitude e longitude do programa C e usa a função SQL 2008 STPointFromText. Essa função converte as coordenadas de latitude e longitude em um ponto de geografia que, em última instância, posso usar em um mapa SSRS. Criou um relatório (rptMapFavoriteRestaurant) com um mapa que aponta para os dados geoespaciais da tabela de endereços na primeira etapa. Eventualmente, eu quero que o usuário final seja capaz de ampliar o mapa inout, e também mover o mapa updown (northsouth) e leftright (eastwest) do centro. Então eu criei três parâmetros inteiros (Zoom. MoveUp. MoveRight) que são gravados no Nível de Zoom. Veja o Centro X. E View Center Y Map Viewport propriedades. Observe que na Figura 10 e na Figura 11. O relatório aparece em uma página da Web do ASP. NET, onde eu posso usar um controle de extensor do controle deslizante básico (da Biblioteca ASP. NET AJAX) para o usuário ajustar as propriedades View Center X e View Center Y. Para acessar o relatório dentro de uma página da Web. NET, criei um novo projeto de site do Visual Studio 2010, descartei uma instância de um visualizador do Reporting Services em um Formulário da Web, juntamente com extensores de controle deslizante para View Center X e View Center Y, e um Botão de comando da Web para executar o relatório. No evento Click do botão de comando, uso o código na Listagem 3. Que define o URL do relatório, cria um objeto de lista de parâmetros ReportParameter, preenche os três parâmetros e, em seguida, define a propriedade visível do visor de relatório como verdadeira. O usuário agora pode visualizar o relatório em um navegador da Web (novamente, Figura 10 e Figura 11). A Figura 11 mostra um exemplo de zoom da área geral de Atlanta para um restaurante específico (Rays on the River) - o nível de zoom é tão alto que você quase pode ler o menu Dica 6: Criando um Relatório de Tabela de Diagrama de Pivô A Figura 12 e a Figura 13 demonstram Um relatório que usa os recursos da tabela dinâmica (matriz) do SSRS. Ambas as capturas de tela mostram grupos de linhas com a hierarquia do produto (categoria-subcategoria-produto) e grupos de colunas com a hierarquia de datas (Ano Fiscal-Fiscal Quarter), com expandcollapse (drilldown) capacidades para mostrar ou suprimir detalhes. O relatório também contém uma opção suspensa para mostrar trimestres expandidos como uma porcentagem do ano ou uma porcentagem do nível do produto principal. Figura 12: Matriz e detalhamento: opção para mostrar cada trimestre (elemento de divulgação) como uma porcentagem do ano. Figura 13: Matriz e detalhamento: opção para mostrar cada trimestre (elemento de espalhamento) como uma porcentagem da linha pai. Para implementar este relatório, fiz o seguinte: criei um novo relatório contra a fonte de dados compartilhada ADWOLAP. No relatório Primary DataSet, usei o designer de consulta OLAP MDX para selecionar a medida de vendas na Internet e os seguintes membros de Dimension: ano fiscal, trimestre fiscal do ano, categoria de produto, subcategoria do produto e produto. Além disso, no designer Query, definei a hierarquia de Geografia do Cliente no Dimension Slicer na parte superior da janela de diálogo e marquei a caixa de seleção Parâmetros para permitir ao usuário fazer uma seleção de geografia em tempo de execução. Além do parâmetro vinculado a dados, também criei um parâmetro não vinculado para o menu suspenso (na Figura 11 e na Figura 12) para permitir ao usuário selecionar a partir das duas opções de lógica de cálculo para as porcentagens (Mostre cada trimestre como um dos Ano ou Mostrar cada trimestre como o pai do produto). No designer do relatório, arrastei um controle Matrix da caixa de ferramentas e, em seguida, arrastou a medida de Quantidade de Vendas na Internet para a seção Dados da Matriz. Em seguida, criei grupos de linhas para membros da categoria de produto, subcategoria e produto. Comecei criando o grupo Categoria de produtos e, em seguida, adicionando grupos filho para subcategoria e produto. Para implementar o recurso de detalhamento para as linhas, carreguei a caixa de diálogo de propriedade do grupo para o grupo Product SubCategory Product e fui para a caixa de diálogo Visibility, configure a opção Report is initial run to Hide e, em seguida, configure o Display pode ser alternada para a Subcategoria E Categoria, respectivamente. Eu criei os grupos de colunas para os membros da dimensão fiscal e do trimestre fiscal da mesma forma que os grupos de linhas e implementou um detalhamento de grupo de colunas ao ocultar inicialmente o grupo de colunas do quarto fiscal e configurar o Display pode ser alternado para o ano fiscal. Com grupos de linhas por categoria de produto, subcategoria e produto, o nível de agregação de linha (ou seja, soma) é por produto. Para implementar totais de linhas para subcategoria, categoria e um total geral, cliquei no menu suspenso do grupo de linhas para cada um dos três grupos e tirei a opção de Adicionar TotalBefore. Se você está se perguntando por que eu selecionei Antes em vez do mais típico Depois, é apenas para simplificar a saída gerada quando o usuário colapsa um nível de grupo de linhas. Eu implementei os totais das colunas para o ano fiscal e um grande total da mesma maneira geral que eu implementei os totais das filas. Cliquei na lista suspensa do grupo de colunas para cada um dos dois grupos de colunas e tirei a opção de Adicionar TotalAnterior. Todas as etapas acima foram possíveis usando o SSRS 2005. No entanto, o próximo recurso (implementando uma porcentagem do total para cada grupo de colunas) só é possível no SSRS 2008. Implementar uma porcentagem do total para cada um dos trimestres fiscais (como distribuídos por O ano fiscal), cliquei corretamente na matriz, no separador de colunas para o trimestre fiscal do ano, e selecione o grupo Insert Columninside. O BIDS abrirá uma nova coluna (dentro do grupo do trimestre fiscal), onde eu posso implementar uma porcentagem do cálculo total. Para realmente implementar a porcentagem do cálculo total para todos os grupos de linhas, posso aproveitar o recurso SSRS para acessar células específicas de caixa de texto do relatório por nome usando a coleção ReportItems. Como o cálculo dependerá da opção de porcentagem do usuário, preciso colocar o seguinte cálculo na expressão da nova célula: Tenho em atenção que preciso repetir isso para cada grupo de linhas. Eu também preciso definir a propriedade de cor de primeiro plano: Dica 7: Criando Relatórios com Parâmetros em Cascata A Figura 14 mostra um exemplo de um relatório contra um banco de dados OLAP que usa parâmetros em cascata. Nessa situação, o relatório gera vendas diárias para uma cidade e produto específico. Como a dimensão OLAP poderia ter milhares de produtos, o relatório solicitará uma cidade e, em seguida, exibirá apenas esses produtos na distribuição (ou seja, apenas os produtos que foram vendidos para o cliente atual). Eu criei o conjunto de dados para o relatório, selecionando o atributo de dimensão da data, a quantidade de vendas da Internet e, em seguida, dimensione os parâmetros do filtro para o atributo da Cidade do Cliente e o atributo da dimensão do produto. O BIDS criará conjuntos de dados ocultos para vincular as listas suspensas da Cidade e do Produto. Posso modificar o MDX gerado para o conjunto de dados escondido do Produto e, em seguida, adicionar o seguinte código MDX: Dica 8: Criando Relatórios contra OLAP A Figura 15 mostra outro exemplo de um relatório SSRS contra uma fonte de dados OLAP. Este relatório contém um gráfico que traça as vendas mensais, juntamente com uma média móvel de 12 meses (ou seja, para qualquer mês único, as vendas médias voltadas nos últimos 12 meses). Muitos analistas usarão um gráfico como a Figura 15 para detectar quaisquer tendências em que as vendas mensais diminuam abaixo da média padrão. O SSRS pode facilmente traçar duas medidas em um gráfico usando diferentes tipos de gráfico (barra e linha de coluna), mas eu preciso fornecer o cálculo da média móvel. Essencialmente, por qualquer mês (usando a declaração MDX CurrentMember), preciso calcular a média voltada nos últimos 12 meses (usando a função MDX LastPeriods). Aqui está o cálculo completo do MDX para a Média Mover: Há outro tópico que eu preciso abranger: uma característica bastante desagradável que a Microsoft introduziu no SSRS 2008. No relatório principal DataSet, criei um parâmetro para a Hierarquia de Geografia. Como resultado, o SSRS gerou automaticamente um segundo conjunto de dados (oculto) com o código MDX para preencher um menu suspenso de parâmetros (e isso é bom). O SSRS (Visual Studio) também permite que você modifique o código MDX para esse segundo conjunto de dados, se você quiser apenas um subconjunto da hierarquia (e isso também é uma coisa boa). No entanto, por padrão, se você voltar e modificar o primeiro conjunto de dados ou modificar o novo parâmetro do relatório, o Visual Studio irá regenerar o código do conjunto de dados MDX. Isso substituirá suas mudanças MDX personalizadas - e isso não é um bom coisa Felizmente, há uma solução alternativa. Nota: Você deve fazer isso ANTES de modificar qualquer código MDX gerado. Feche o designer do relatório, mas mantenha aberto o projeto SSRS geral. No Solution Explorer, clique com o botão direito do mouse no relatório e, em seguida, pegue a opção de código de exibição. Para cada instância de um conjunto de dados oculto, encontre a combinação ltQuerygt. ltDataSetgt e, em seguida, insira o seguinte: Guarde o código, feche a janela do código e, em seguida, recarregue o relatório no designer do Visual Studio. Dica 9: Criando Relatórios com Calibres de Desempenho e Sparklines Um dos principais aprimoramentos no SSRS 2008 foi a nova visualização de dados. Você pode pensar que isso simplesmente significa novos recursos de gráficos. Na verdade, os novos recursos de visualização no SSRS 2008 vão além dos novos recursos de gráficos. O SSRS 2008 oferece novas opções de relatório de calibração que permitem que os desenvolvedores criem relatórios de estilo de painel para renderizar o desempenho visual, de forma semelhante aos tipos de relatórios de estilo KPI encontrados em ferramentas como o PerformancePoint Services. Os novos recursos de visualização no SSRS 2008 e 2008 R2 vão muito além dos novos recursos de gráficos. Antes de começar, o seguinte exemplo se originou no livro excepcional de Teo Lachevs, Applied Microsoft SQL Server 2008 Reporting Services. Este é um dos dois livros do SSRS que eu recomendo no final deste artigo. Neste livro, Lachev mostra como usar um indicador de gráfico de bala para visualizar as vendas como uma porcentagem da cota de vendas. Eu fiz várias melhorias no exemplo: classificação interativa no nível do grupo de funcionários nos diferentes números de vendas. Exibindo o segundo indicador em uma cor diferente com base na porcentagem de vendas. Referenciando a porcentagem de vendas no indicador da coleção do ReportItems Textbox. Exibição de dois tipos de gráfico de linha (um gráfico de linhas com marcadores e uma linha de curva suavizada). Mostrando um grande total para o ano. No entanto, o exemplo originalmente vem do livro Lachevs, que eu recomendo. A Figura 16 mostra um relatório que exibe informações de vendas e cotações por vendedor, juntamente com duas visualizações importantes que são novas no SQL Server 2008 e 2008 R2. O primeiro é o indicador de desempenho (novo em 2008), eo segundo é o sparkline (novo em 2008 R2). Um sparkline é um gráfico altamente comprimido (muitas vezes um gráfico de linhas) com todos os rótulos, marcas de seleção e outros atributos despojados - e se destina a mostrar o direcionamento de tendência geral. Figura 16: Relatório com linhas de faixas e medidores de desempenho. O indicador de desempenho é semelhante a um KPI visual em Servidores PerformancePoint ou SharePoint, mas com o benefício adicional de exibir visualmente quão perto (ou distante) uma métrica é de um objetivo. O SSRS oferece uma série de medidores lineares e radiais diferentes. Eu escolhi um indicador de gráfico de bala linear. O indicador de desempenho consiste nas seguintes propriedades que eu preciso especificar: Propriedades do intervalo: por padrão, o indicador de desempenho contém intervalos horizontais onde eu posso clicar com o botão direito do mouse, ir às propriedades do intervalo e especificar o alcance e a cor do plano de fundo (0-80 luz Vermelho, 80-90 amarelo claro, 90-100 luz verde. Ponteiros lineares: por padrão, existem dois ponteiros lineares para mostrar o desempenho real dentro dos intervalos. Posso fazer o ponteiro linear principal uma barra horizontal que mapeia a porcentagem de empregado de Lucro, e eu posso definir a cor de preenchimento de ponteiros lineares: Dica 10: executando relatórios de C Suponha que você tenha uma situação em que você deseja gerar programaticamente um relatório SSRS para um arquivo PDF - talvez depois de terminar um trabalho de banco de dados. A Listagem 4 mostra como você Pode executar um relatório implantado e, em seguida, criar um fluxo de arquivos para tornar o relatório como um arquivo PDF. Dica 11: SSRS 2008 R2 O Potpourri SSRS 2008 R2 contém uma série de aprimoramentos diversos que Ive achou particularmente valioso. Embora não seja um Lista completa, aqui estão alguns dos que eu usei: agora você pode criar quebras de página no nível do grupo de relatórios. O SSRS 2008 R2 agora possui uma página X do nível de grupo, além de uma página geral X de Y. Se você acessar a janela de propriedades para qualquer grupo, existem novas propriedades do PageBreak (BreakLocation, ResetPageNumber, etc.) que você pode conjunto. Se você estiver produzindo um relatório que precisa ser repaginado no nível do grupo, esse novo recurso elimina a necessidade de calcular a paginação manual. Adicionalmente, você pode agregar agregação a uma página. Esta é uma característica MUITO bem-vinda para quem produz certos relatórios financeiros Criando relatórios contra As listas de SharePoint agora são muito mais fáceis: não há necessidade de fazer referência ao. ASMX por mais tempo. Você pode fazer referência ao formato de exportação de saída em tempo de execução com a nova variável global GlobalsRenderFormat. Os desenvolvedores que desejaram unir valores em relatórios DataSets serão felizes com este: o objeto DataSet agora oferece suporte a várias funções LOOKUP, para simular um JOIN entre DataSets. Ao exportar para o Excel, você pode especificar os nomes das tabelas da planilha. Agora você pode definir uma expressão de relatório como um agregado de um agregado. Dica 12: Bakers Dozen Spotlight: assinaturas baseadas em dados Muitos ambientes da empresa SSRS gerarão relatórios em uma programação (geralmente overnightoff-hours) e entregarão o conteúdo do relatório gerado por e-mail, compartilhamento de arquivos ou uma biblioteca de documentos do SharePoint. Isso se tornou um método muito popular para entregar a saída do SSRS aos usuários finais, especialmente quando há uma natureza preditiva para a forma como as pessoas executam relatórios. Isso também pode melhorar o desempenho geral e a escalabilidade em certos ambientes de inteligência de negócios de data warehouse, onde os usuários, de outra forma, executariam relatórios largecomplicados durante o dia. As inscrições de relatórios podem melhorar o desempenho global e a escalabilidade em certos ambientes de inteligência de negócios de data warehouse, onde os usuários, de outra forma, executariam relatórios largecomplicados durante o dia. Uma assinatura do SSRS é como qualquer assinatura padrão - você se inscreve para conteúdo específico e você possui o conteúdo entregue à sua porta da frente. No contexto do SSRS, significa que você pode fornecer conteúdo SSRS para a caixa de entrada de e-mails de alguém, para uma pasta de rede compartilhada ou para alguns documentos da biblioteca de documentos do SharePoint. O SSRS oferece muitas opções do lado do servidor para definir caches de relatórios, instantâneos e assinaturas. Eu vou me concentrar em um recurso específico (e importante): inscrições baseadas em dados. Eu vou fazer um relatório que pode ser executado para diferentes categorias de produtos, definir um cronograma para executar o relatório para diferentes categorias e, em seguida, especificar um tipo de entrega de uma biblioteca de documentos do SharePoint. Um possível contexto comercial aqui seria gerar um relatório de vendas para cada gerenciador de marca diferente em uma empresa: eu também poderia gerar uma assinatura para gerenciadores de região, ou algum outro fluxo de trabalho comercial. Para começar, eu crie uma tabela em um banco de dados relacional do SQL Server (Listagem 5) que irá definir o conteúdo de uma assinatura baseada em dados. O conceito de dados orientado deve ser óbvio, mas, no caso, estou armazenando em uma tabela as informações de uma assinatura que a Id de outra forma deve fornecer SSRS manualmente. Uma assinatura baseada em dados é ideal quando eu tenho um grande número de definições de assinatura que também podem mudar ao longo do tempo. No caso da Listagem 5. Eu definir o relatório a ser executado (rptSalesByBrand, um relatório de vendas muito simples), o nome do arquivo de saída de destino e da biblioteca de documentos do SharePoint, o formato de renderização (HTML estático, embora possa ser um PDF tão facilmente) e a chave de parâmetro Para a instância do relatório (desde que eu quero uma saída separada para bicicletas, acessórios e roupas). Como este relatório específico é um relatório OLAP, eu preciso expressar as chaves de parâmetros em termos de notação MDXOLAP Dimension Member (Product. Category. amp1). Se eu criasse um relatório contra um banco de dados relacional, o ID provavelmente precisaria fornecer a chave de número inteiro substituto para o parâmetro. Em muitos casos, os desenvolvedores criarão uma UI especial para preencher essas tabelas a partir de seleções orientadas por menu. Observe também a opção para AutoIncrement - isso gerará um arquivo de saída exclusivo para cada relatório de execução da assinatura. O próximo passo é criar um cronograma de execução compartilhada no SharePoint. Na coleção de sites do SharePoint, onde o relatório reside, você deseja acessar as Ações do Site, Configurações do Site e definir um cronograma compartilhado. Esta é uma interface que é executada no topo do SQL Server Agent, para criar um trabalho com uma freqüência de execução específica (por exemplo, todas as noites de segunda-feira à meia-noite, a cada duas semanas, etc.) Você pensaria que o próximo passo seria informar o Reporting Services Mecanismo de execução (via SharePoint) sobre a tabela de inscrição da Listagem 5. Infelizmente, há um problema. Por padrão, as fontes de dados do relatório usam a autenticação do Windows, que o SSRS não aceitará quando um relatório for executado sem supervisão em uma programação. Então eu preciso ir para a pasta de coleção do site que contém as fontes de dados implantadas, abrir a fonte de dados (que é basicamente uma seqüência de conexão) e, em seguida, pegue a opção de armazenar credenciais de forma segura no servidor para execução de relatório autônomo. Essa conta deve ser uma conta de domínio com direitos suficientes para executar o relatório e acessar a biblioteca de documentos de saída do SharePoint - uma conta de serviço geralmente não será forte o suficiente. (Um gerenciador de TI ou DBA pode criar uma conta especial de domínio não humano para essas situações). Uma vez que eu tomei cuidado com a fonte de dados, eu posso ir ao relatório implantado, clicar no menu suspenso do documento do SharePoint e, em seguida, pegar a opção para adicionar Uma assinatura (e, em seguida, especifique uma assinatura baseada em dados). O SharePoint irá me solicitar a fonte de dados que contém a tabela de inscrição (da Listagem 5), juntamente com colunas específicas que o SSRS espera (e como elas mapeiam para as colunas na Listagem 5) e o nome da programação compartilhada que será executada A subscrição. A Figura 17 mostra o resultado na biblioteca de documentos do SharePoint, quando a programação é executada. Às vezes, os DBAs desejam consultar as tabelas de registro do SQL Server Agent para ver o histórico de uma programação. A Listagem 6 mostra uma consulta SQL contra as tabelas do sistema do ReportServer e as tabelas do sistema do SQL Server Agent, para visualizar o histórico de uma programação. Dica 13: Subreportes Ocasionalmente, um relatório exigirá vários níveis de detalhes (ou seja, dados de vários relacionamentos de 1 para muitos). Por exemplo, talvez seja necessário mostrar uma lista de produtos e suas vendas através do canal de varejo (resumido pelo território de vendas), bem como vendas através do canal do revendedor (resumido pelo vendedor). Um produto específico pode ter vendas em um ou em ambos os canais. Além disso, eu quero mostrar cada conjunto de vendas separadamente. A Figura 18 demonstra um exemplo básico de um relatório com vários níveis de detalhes (subrelatórios). O relatório principal é rptProductSubReportExample (que mostra a listagem principal do produto), e os dois sub-relatórios são rptSubProductInternetSales (que mostra as vendas da Internet por território para cada produto) e rptSubProductResellerSales (que mostra as vendas do revendedor por vendedor para cada produto). Eu construí cada um dos dois sub-relatórios criando um relatório SSRS padrão, mas também definindo um parâmetro de relatório para um ID de produto único. Então, qualquer vez que eu executo qualquer um dos sub-relatórios de forma independente, o SSRS me solicita um parâmetro ID de produto. However, in the main report, after I build a basic layout for a product listing, I can add a reference to a subreport from the toolbox. In the subreport properties, SSRS (BIDS) provides a parameters page where I can map the ProductID value from the main report dataset to the subreport parameter. Book Recommendations I highly recommend the following two books. If you work with Reporting Services on a regular basis, these two books should be considered required reading: Applied Microsoft SQL Server 2008 Reporting Services by Teo Lachev (published by Prologika). I also recommend Teo Lachevs blog and his other books. Microsoft SQL Server Reporting Recipes for Designing Expert Reports by Paul Turley and Robert M. Buckner. I also have additional SSRS book recommendations on my website (KevinSGoff. net ), but these are the absolute must-have books. Article Project File You can find the entire source code and project file on my website at KevinSGoff. net. in the download area. SQL Saturday and SharePoint Saturday events If you werent aware of this, there are SQLSaturday and SharePoint Saturday events all over North America, as well as Australia, the U. K. and New Zealand. These are free events that offer much of the same content that youll find at commerical conferences. For more information, check out sqlsaturday and sharepointsaturday. org. I often speak on SQL Server and Business Intelligence topics at these events, mainly in the Mid-Atlantic region. If you work with either SQL Server or SharePoint, I definitely recommend you check out these events Listing 1: Code to call a public Web Service for geospatial data Listing 2: Stored procedure to write address and LatLong data Listing 3: Accessing the map from a. NET web page Listing 4: Generating a report to a PDF Listing 5: Creating a data-driven table for SSRS subscriptions Listing 6: Querying a subscription table Kevin S GoffIntroduction With the release of SQL Server 2016 Service Pack 1 the In-Memory ColumnStore technology is now also available in the Standard, Web and even Express and LocalDB Editions. Besides the benefit of only 1 codebase to maintain, this change in policy will also become a clear disk storage space saver due to its high data de-duplication and compression ratios and, last but not least, its also a serious ad-hoc query performance booster The main difference between the SQL flavors is how much CPU power and memory is allocated to tasks like (re-)building of the Clustered ColumnStore Index. For example: with the Standard Edition a single core (max. 100 Processor time of the sqlservr process) is being used and querying a CCI happens with a maximum of 2 CPUs (MAXDOP2), versus leveraging all available CPUs in Enterprise Edition. Building a Clustered ColumnStore Index (CCI) with SQL Server 2016 Standard Edition: Building a CCI with all 4 available cores with SQL Server 2016 Enterprise Edition: The base timings for loading 7.2 GB 60 Million rows from a single TPCH lineItem files doesnt show much of a difference between the flavors when Bulk inserting the data direct into either a heap table or a table with a CCI the difference become clear when we compare the time needed to build a CCI on a heap table or rebuilding a CCI: To summarize, the absolute fastest way to have data available in a table with a Clustered ColumnStore Index is to: load into heap build the CCI afterwards with SQL 2016 Ent. Ed. Direct load into CCI For tables with a Clustered ColumnStore Index already created make sure that you stream directly into Compressed Row Groups to maximize throughput. In order to do so, the Insert batch size should be equal or larger than 100K Rows (102400 to be precise). Smaller batches will be written into compressed delta store tables first before being tuple moved into its final compressed Row Group segments, which means SQL Server has to touch the data twice: There are various options to load data and we will go over the most frequently used ones, like the Bulk Insert command, BCP and SSIS. Lets see what is needed to get best performance and how to monitor 1) T-SQL Bulk Insert Let us start with a the BULK INSERT command: Checking Data Load Progress To check the Number of Rows that already got loaded into the CCI, even when the Table Lock option is being used, query a new dmv called sys. dmdbcolumnstorerowgroupphysicalstats: This DMV will also reveal the possible Resource Group states in more detail while loading. There are four possible Row Group states while loading data. When you see the state INVISBILE like in the picture below means that data is being compressed into a RowGroup. 0: INVISIBLE ( RowGroup is in the process of being built from data in the delta store) 1: OPEN160160160160160160160 ( RowGroup is accepting new records) 2: CLOSED160160160 ( RowGroup is filled but not yet compressed by the tuple mover process) 3: COMPRESSED160 ( RowGroup is filled and compressed). 4 TOMBSTONE160 ( RowGroup is ready to be garbage collected and removed) By specifying the Batch Size with a value of 102400 or higher you will achieve maximum performance and data will get streamed and directly compressed into its final RG this behavior will show up as COMPRESSED. You can also check a DMV that got introduced with SQL2014 to check on the RowGroup State, which is the sys. columnstorerowgroups DMV: Test Result Bulk inserting data into a table with CCI via the Bulk Insert command can slightly be improved by adding the Batchsize102400 and TABLOCK options. This brings an 8 improvement in throughput. 2) BCP. exe The BCP utility is still being used quite heavily in many production environments so worth to check on it quickly: by default, the BCP sents 1000 rows at the time to SQL Server. The time it takes to load 7.2GB of data via BCP: 530 seconds. or160 113K rowssec The RowGroup state shows NVISIBLE which means that with the default settings the Delta Store is being used. To make sure the BCP command streams the data directly into the compressed RGs you have to add the batchsize b option with a value of at least 102400. I ran various tests with larger batch sizes: up to 1048576, but the 102400 gave best me the result. BCP DB. dbo. LINEITEMCCI in F:TPCHlineitem. tbl S. - c - T - tquotquot - b 102400 h tablock The RowGroup state now shows COMPRESSED which means we bypass the Delta Store and data streams into the compressed RGs: Result: the BCP completed in 457 seconds, or 133K rows per second or During testing I noticed that the default SSIS 2016 settings use memory buffer sizes that can also potentially limit the batch size to become less than 100K Rows. In the example below you see that data landed in delta stores: the RG states are Closed and the deltastorehobtid fields are populated, which means the delta stores are leveraged. This was the moment to reach out and check with to my colleagues who luckily have noticed this to and a solution is already there (see: Data Flow Buffer Auto Sizing capability benefits data loading into CCI ). To fully leverage the CCI streaming capabilities you have to increase the Default memory BufferSize amp MaxRows settings: Change the these into 10x larger values: 8211 DefaultMaxBufferRows from 10000 into 1024000 and the most important one: 8211 DefaultBufferSize from 10485760 into 104857600 . Note: the new AutoAdjustBufferSize setting should be set to True when you load very wide Rows of data. Change also the values for the Destination adapter: 8211 Rows per Batch:160 from none into 102400 8211 Maximum Insert commit size: from 2147483647 into 102400 The feature parity introduced with SQL Server 2016 SP1 opens up a whole new range of possibilities to benefit from Hopefully the walkthroughs above help you to max out Bulk Insert, BCP and SSIS performance when loading data into a Clustered ColumnStore Index What will be the absolute fastest way to load data from a flatfile into a table within SQL Server 2016 A lot has changed since my initial post on this topic many years ago, ike the introduction of In-memory optimized tables and Updateable Columnstore table indexes. Also the list of data transport vehicles to choose from is growing: besides BCP, the T-SQL Bulk Insert command, SSIS as ETL tool and PowerShell there are some new ones added, like PolyBase, External R Script or ADF. In this post I will start with the checking how much faster the new durable amp non-durable In-memory tables are Setting the Baseline For these tests Im using an Azure DS4V2 Standard VM with 8 cores28 GB of RAM and 2 HDD Volumes with host caching RW enabled. (Both Luns provide 275 MBsec RW throughput although the GUI states a limit of 60MBsec). I generated a single 60 Million row7.2 Gigabyte TPCH lineitem flat file as data to load. As baseline to for use for comparison we will use the time it takes to load the file into a Heap table: This regular Bulk Insert command completes within 7 minutes with an average of 143K rowssec. Enabling the test database for Memory optimized tables The (in SQL20142016 Enterprise amp developer Edition ) introduced in-memory tables are designed for very fast OLTP with many small transactions and high concurrency, which is a completely different type of workload as bulk inserting but, just out of curiositylets give it a try There are 2 types of in-memory tables: durable and non-durable tables. The durable ones will persist data on disk, the non-durable ones wont. To enable this option we have to do some housekeeping and assign a fast disk volume for hosting these files. First, alter the database to enable the Contains MEMORYOPTIMIZEDDATA option followed by adding a File location and Filegroup that will contain the Memory-Optimized tables: The third thing to do is to add a separate memory pool to the SQL Server instance so it can keep all the data we will load into in-memory tables separate from its default memory pool: Binding a database to a memory pool The steps to define a separate memory pool and to bind a database to it are listed below: Extra memory pools are managed via the SQL Resource Governor. The 4th and last step is to bind the test database to the new memory Pool with the sys. spxtpbinddbresourcepool command.160 In order for the binding to become effective we have to take the database offline and bring it back online. Once bound we can dynamically change the amount of memory assigned to its pool via the ALTER RESOURCE POOL PoolHk WITH (MAXMEMORYPERCENT 80 ) command. Bulk Insert into Durable In-Memory table Now we are all set with the In-memory option enabled, we can create an in-memory table. Every memory-optimized table must have at least one index (either a Range - or Hash index ) which are completely (re-)composed in memory and are never stored on disk. A durable table must have a declared primary key, which could then be supported by the required index. To support a primary key I added an extra rownumber ROWID1 column to the table: Specifying a batch size of 1 (up to 5) Million rows to the bulk insert command helps to persist data to disk while the bulk insert is ongoing (instead of saving it all at the end) doing so minimizes memory pressure on the memory pool PookHK we created. The data load into the durable In-Memory table completes in 5 minutes 28 seconds, or 183K Rowssec. Thats an okay time but not that much faster than our baseline. Looking at the sys. dmoswaitstats shows that the no.1 waitstat is IMPPROVIOWAIT which occurs when SQL Server waits for a bulk load IO to finish. Looking at the Performance counter Bulk Copy Rowssec and Disk Write Bytessec shows the flushing to disk spikes of 275 MBsec once a batch got in (the green spikes). That is the maximum of what the disk can deliver but doesnt explain it all. Given the minor gain, we will park this one for future investigation. Monitoring the Memory Pool Via the sys. dmresourcegovernorresourcepools dmv can we check if our in-memory table leverages the newly created PoolHK memory Pool: The output shows this is the case the 7.2GB ( some extra for the Rowid) got uncompressed loaded into the memory poolHk pool: If you try to load more data than you have memory available to the pool you will get an proper message like this one: The statement has been terminated. Msg 701, Level 17, State 103, Line 5 There is insufficient system memory in resource pool 8216PookHK to run this query. To look one level deeper at memory space allocation on a per In-memory table basis you can run the following query (taken from the SQL Server In-Memory OLTP Internals for SQL Server 2016 document ): The data we just loaded is stored as a varheap structure with a hash index: So far so good Now lets move on and check out how staging in a non-durable table performs Bulk Insert into Non-Durable In-Memory table For IMND tables we do not need a Primary key so we just add and Non-clustered Hash index and set DURABILITY SCHEMAONLY. The Bulk insert Data loading into the non-durable table completes within 3 minutes with a throughput of 335K rowssec (vs 7 minutes) This is 2.3x faster then inserting into a heap table. For the staging of data this definitely a quick win SSIS Single Bulk Insert into a Non-Durable table Traditionally SSIS is the fastest way to load a file quickly into SQL Server because SSIS will handle all the data pre-processing so the SQL Server engine can spend its CPU ticks on persisting the data to disk. Will this still be the case when inserting the data into a non-durable table Below a summary of the tests I ran with SSIS for this post: the SSIS Fastparse option and160 the DefaultBufferMaxRows and DefaultBufferSize settings are the main performance boosters. Also the Native OLE DB (SQLOLEDB.1) provider performs slightly better than the SQL Native Client (SQLNCLI11.1). When you run SSIS and SQL Server side by side, increasing the network packet size isnt needed.160160 Net result: a basic SSIS package that reads a flat file source and writes the data out directly to the Non-Durable table via an OLE DB destination performs similar as the Bulk Insert command into a IMND table: the 60 Million rows are loaded in 2minutes 59seconds or 335K rowssec, identical to the Bulk insert command. SSIS with Balanced Data Distributor But wait8230160 the in-memory tables are designed to work lock amp latch free so this means that we can load data also via multiple streams That is easy to achieve with SSIS the Balanced Data Distributor will bring just that (the BDD is listed in the Common section of the SSIS Toolbox) Adding the BDD component and inserting the data into the same Non-durable table with 3 streams provides the best throughput: we are now up to 526000 Rowssec Looking at this very flat line, with only 160 of CPU time used by SQLServer, it seems we are hitting some bottleneck: I quickly tried to be creative by leveraging the modulo function and added 2 more data flows within the package (each processing 13 of the data)160 but it that isnt improving much (1 min52sec) so a great topic to investigate for a future post160160 The In-Memory Non-Durable table option brings some serious performance improvement for staging of data loading data 1.5x faster with a regular Bulk inser t and up to 3.6x times faster with SSIS. This option, primarily designed to speed up OLTP, can also make a huge difference to shrink your batch window quickly (To be continued)