Construir e Consultar
o Data Warehouse
com SQL Server
Jacques Robin
Alexandre Damasceno
Marcelino Pereira
CIn-UFPE
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
Arquitetura de ferramentas do SQL Server
para Data Warehousing
SQL
Server
Enterprise
Manager
FLAT
Query
Analizer
OLAP
Server
Tabelas
OLAP
Manager
Cubo
DTS
MDX
Sample
Estudo de caso: RoboCup Warehousing
link: ~robocup\aula\
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

RoboCup Warehousing:
exemplo da fonte de dados disponível
RoboCup Warehousing: preparação de dados
Ferramenta de
modelagem E-R
Log do
Soccer
Server
Data Mart
ROLAP
Serviços de
transformação
de dados
Microsoft
SQL Server
Log do
Soccer
Server
SGBD
relacional
Hipercubo
OLAP
derivados
Hipercubo
OLAP
primitivo
Servidor
OLAP
JODI: API
OLE DB for OLAP / Java
API
XSB-SQL
InterProlog
API Java / XDB
Sistema dedutivo
XSB
DOODI: API
OLE DB for OLAP / XSB
Usar Visio para modelagem E-R do
Warehouse ROLAP e criação das tabelas
Modelo estrela de dados primitivos
Modelo estrela de dados derivados I
Modelo estrela de dados derivados II
Modelo constelação do warehouse pronto
para consultas OLAP
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.

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
Pré-processamento do pré-processamento

Teamleft: 11monkeys, Teamright:
FCFoo




Score: 0:0
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
Pré-processamento do pré-processamento

Soluções encontradas:
• Programa Perl, Java, C ...
• DTS
• Com auxílio de consultas SQL
Usar o DTS Wizard

Começar a mexer com a base
• link: ~robocup\aula\
OLAP Manager: funcionalidades

Acessar servidores OLAP
• Registro de servidores

Criar e alterar estrutura multi-dimensional
• Dimensões
• Cubos
• ...

Visualizar dados
• Navegador de consultas simples
Criar cubos a partir do warehouse relacional
com o OLAP Manager Wizard
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++

Padrões da Microsoft
“abertos e orientados a objetos”

UDA (Universal Data Access):
•

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):
•
•
•

conjunto de padrões e API para acesso a vários tipos de dados
transparentemente do seu suporte de armazenamento físico
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
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.

Exemplos simples de uma consulta MDX
WITH [Medidas].[Lucro]
AS ‘[Medidas].[Vendas] * ([Medidas].[PrecoUnitario] - [Medidas].[CustoUnitario])’
SELECT NON EMPTY (CROSSJOIN ({[LojaLoc].[Pais].MEMBERS},
{[Tempo].[Ano].MEMBERS}) ON COLUMNS
NON EMPTY {[ProdCat].[SubCat].MEMBERS} ON ROWS
FROM Vendas
WHERE ([Medidas].[Lucro]).
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





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



