Construir e Consultar o Data Warehouse com SQL Server

1
Construir e Consultar o Data Warehouse com SQL
Server

• Jacques Robin
• Alexandre Damasceno
• Marcelino Pereira
• CIn-UFPE

2
Roteiro

• 1. Recursos do SQL Server para Data Warehousing e
  OLAP
• 2. Estudo de caso criar e consultar Data
  Warehouse de estatísticas de jogos da RoboCup
  para descobrir insights táticos
• 3. Preparação de dados com o pacote Data
  Transformation Services
• 4. Criação de cubos multidimensionais a partir de
  tabelas relacionais com o OLAP Manager
• 5. Consultar cubos multidimensionais com OLE DB
  for OLAP e MDX

3
Arquitetura de ferramentas do SQL Server para
Data Warehousing
4
Estudo de caso RoboCup Warehousing
link robocup\aula\
5
Estudo de caso RoboCup Warehousing

• Arquivos de log do simulador de partidas
  SoccerServer
• Dados em formato flat
• Sem intencionalidade
• Apenas resultado no campo das ações dos
  agentes/clientes/jogadores
• Sem as ações mandadas para o simulador pelos
  agentes, nem as percepções recebidas em volta
• Sem possibilidade de inferí-las confiavelmente,
  devido ao ruído introduzido pelo simulador
• De granularidade fina demais para descobrir
  insights procurados por mineração direta
• Necessidade de passo(s) de derivação de dados de
  granularidade suficiente na preparação dos dados

6
RoboCup Warehousing exemplo da fonte de dados
disponível
7
RoboCup Warehousing preparação de dados
8
Usar Visio para modelagem E-R do Warehouse ROLAP
e criação das tabelas
9
Modelo estrela de dados primitivos
10
Modelo estrela de dados derivados I
11
Modelo estrela de dados derivados II
12
Modelo constelação do warehouse pronto para
consultas OLAP
13
Data Transformation Services (DTS)
Funcionalidades

• Migrar dados entre diferentes localidades de modo
  automático.
• Tratar esses dados durante o sua migração.
• Permite o uso de scripts para realizar esses
  trabalhos de modo mais elaborado.

14
Formato Flat do DTS

• 1,0,4,"0.0,0.0","-47.0,0.0",101,0,-11,0,0,0,0,0,0,
  0,0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-43.0,0.0",102,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-35.0,25.0",103,0,-36,0,0,0,0,0,0
  ,0,0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-30.0,0.0",104,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-35.0,-25.0",105,0,35,0,0,0,0,0,0
  ,0,0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-3.0,25.0",106,0,-83,0,0,0,0,0,0,
  0,0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-0.6,-0.6",107,1,49,0,0,1,0,0,0,0
  ,0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-15.0,-15.0",108,0,-142,0,0,0,0,0
  ,0,0,0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-3.0,31.0",109,0,-83,0,0,0,0,0,0,
  0,0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-1.0,10.0",110,0,-85,0,0,0,0,0,0,
  0,0,0,0,0,0,1

15
Pré-processamento do pré-processamento

• Teamleft 11monkeys, Teamright FCFoo
• Score 00
• T 0, mode 4 Ball ( 0.0, 0.0), P 1(-48.0,
  0.0, 3), 2(-31.8, 0.0, 0), 3(-27.8, 14.9,
  -8), 4(-28.0,-15.0, 120), 5(-22.0, 0.0, 87), 6(
  -9.0, 22.0, -11), 7( -1.0, 12.0, 20), 8(
  -9.0,-22.0, 6), 9( -1.0, 0.0, -75),10(-10.0,
  0.0, -15),11( -1.0,-12.0, 87),12( 47.0,
  0.0,-179),13( 25.0, 15.0,-178),14( 30.0,
  5.0,-150),15( 24.5, 0.0, 179),16( 30.0, -5.0,
  150),17( 25.0,-15.0, 179),18( 20.0, 20.0,
  178),19( 9.4, 0.0,-179),20( 20.0,-20.0,-176),21(
  4.1, 8.2,-116),22( 1.8, -8.9, 101),

• 1,0,4,"0.0,0.0","-47.0,0.0",101,0,-11,0,0,0,0,0,0,
  0,0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-43.0,0.0",102,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-35.0,25.0",103,0,-36,0,0,0,0,0,0
  ,0,0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-30.0,0.0",104,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-35.0,-25.0",105,0,35,0,0,0,0,0,0
  ,0,0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-3.0,25.0",106,0,-83,0,0,0,0,0,0,
  0,0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-0.6,-0.6",107,1,49,0,0,1,0,0,0,0
  ,0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-15.0,-15.0",108,0,-142,0,0,0,0,0
  ,0,0,0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-3.0,31.0",109,0,-83,0,0,0,0,0,0,
  0,0,0,0,0,0,1
• 1,0,4,"0.0,0.0","-1.0,10.0",110,0,-85,0,0,0,0,0,0,
  0,0,0,0,0,0,1

16
Pré-processamento do pré-processamento

• Soluções encontradas
• Programa Perl, Java, C ...
• DTS
• Com auxílio de consultas SQL

17
Usar o DTS Wizard

• Começar a mexer com a base
• link robocup\aula\

18
OLAP Manager funcionalidades

• Acessar servidores OLAP
• Registro de servidores
• Criar e alterar estrutura multi-dimensional
• Dimensões
• Cubos
• ...
• Visualizar dados
• Navegador de consultas simples

19
Criar cubos a partir do warehouse relacional com
o OLAP Manager Wizard
20
OLE DB for OLAP

• Para conexão com Data Warehouse e funções
  administrativas
• usa outros padrões abertos e orientados a
  objetos da Microsoft COM e OLE
• Para consultar Data Mart conectado
• usa uma linguagem com sintaxe parecida com SQL
  MDX
• Independente do modelo físico de dados
• ROLAP, MOLAP ou HOLAP
• Amarrado a plataforma Windows
• Pode ser chamado apenas a partir de C

21
Padrões da Microsoft abertos e orientados a
objetos

• UDA (Universal Data Access)
• conjunto de padrões e API para acesso a vários
  tipos de dados transparentemente do seu suporte
  de armazenamento físico
• COM (Component Object Model)
• padrão de interfaces para vários tipos de
  serviços
• como métodos de (pseudo)-objetos sem ID nem
  atributos
• acesso a métodos via ponteiros, herança simples e
  simulada via includes
• OLE (Object Linking and Embedding)
• conjuntos de interfaces COM fornecendo serviços
  de acesso a dados de vários tipos (planhilha, BD,
  HTML, etc) a partir de C/C
• OLE DB parte de OLE para acesso a BD
• OLE DB for OLAP parte de OLE DB para acesso a
  BDMD
• ADO (ActiveX Data Objects)
• camada de UDA acima de OLE permintindo acesso
  objetos OLE a partir de outras linguagens MS
  como Visual J e Visual Basic
• ADOMD parte de ADO para acesso a BDMD

22
MDX sintaxe das consultas

• WITH
• membros virtuais calculados a partir de membros
  do cubo
• via operadores aritméticos, estatísticos, de
  agregação, de series temporais, de formatação de
  saída
• SELECT
• conjunto de membros de dimensões, hierarquias e
  níveis
• especificado via expressão de operadores
• OLAP (DrillDown, Filter, ...)
• de manipulação de conjuntos (CrossJoin, Union,
  Inter, Member ...)
• agregação (TopSum, TopCount, TopPercent, Order,
  ...)
• ON eixos de visualização planares
• row, column, page, sections, chapters
• FROM cubo
• WHERE lista de membros de dimensões medidas,
  slice e dice.

23
Exemplos simples de uma consulta MDX

• WITH Medidas.Lucro
• AS Medidas.Vendas (Medidas.PrecoUnitario
  - Medidas.CustoUnitario)
• SELECT NON EMPTY (CROSSJOIN (LojaLoc.Pais.MEM
  BERS,
• 
  Tempo.Ano.MEMBERS) ON COLUMNS
• NON EMPTY ProdCat.SubCat.MEMBE
  RS ON ROWS
• FROM Vendas
• WHERE (Medidas.Lucro).

24
Consultas MDX sobre cubo de dados primitivos

• select
• DimGame.Game.AllMembers on columns,
• DimMode.Description.AllMembers on rows
• from PrimitiveCube
• where Measures.Occurrence
• with member Measures.TrueValue as
  '(Measures.Occurrence/22)'
• select
• crossjoin(DimGame.Game.AllMembers,
  DimAgent1.Team.AllMembers) on columns,
• DimMode.Description.AllMembers on rows
• from PrimitiveCube
• where Measures.TrueValue
• Modificar exemplo

25
Consultas MDX sobre cubo de dados derivados

• select
• DimGame.Game.Allmembers on columns,
• DimStatistic.SubType.Allmembers on
  rows
• from DerivedCube
• where Measures.Occurrence
• select
• crossjoin(DimGame.Game.AllMembers,
  DimAgent1.Team.AllMembers) on columns,
• DimStatistic.SubType.AllMembers on
  rows
• from DerivedCube
• where Measures.Occurrence
• Modificar exemplo