Construir e Consultar Construir e Consultar o Data Warehouse o Data Warehouse

com SQL Servercom SQL Server

Jacques RobinAlexandre Damasceno

Marcelino PereiraCIn-UFPE

RoteiroRoteiro

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 Arquitetura de ferramentas do SQL Server para Data WarehousingServer para Data Warehousing

Tabelas CuboFLAT

Enterprise Manager

OLAP Manager

SQL Server

DTS

Query Analizer

MDXSample

OLAP Server

Estudo de caso: RoboCup WarehousingEstudo de caso: RoboCup Warehousinglink: ~robocup\aula\

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

Estudo de caso: RoboCup WarehousingEstudo de caso: RoboCup Warehousing

RoboCup Warehousing: RoboCup Warehousing: exemplo da fonte de dados disponívelexemplo da fonte de dados disponível

RoboCup Warehousing: preparação de RoboCup Warehousing: preparação de dadosdados

Data MartROLAP

Ferramenta de modelagem E-R

APIXSB-SQL

SGBDrelacional

Hipercubo OLAP

derivados

JODI: APIOLE DB for OLAP / Java

DOODI: APIOLE DB for OLAP / XSB

Sistema dedutivo XSB

InterPrologAPI Java / XDB

Log doSoccerServer

Log doSoccerServer

Serviços detransformação

de dados

...

MicrosoftSQL Server

Servidor OLAP

Hipercubo OLAP

primitivo

Usar Visio para modelagem E-R do Usar Visio para modelagem E-R do Warehouse ROLAP e criação das tabelasWarehouse ROLAP e criação das tabelas

Modelo estrela de dados primitivosModelo estrela de dados primitivos

Modelo estrela de dados derivados IModelo estrela de dados derivados I

Modelo estrela de dados derivados IIModelo estrela de dados derivados II

Modelo constelação do warehouse pronto Modelo constelação do warehouse pronto para consultas OLAPpara consultas OLAP

Data Transformation Services (DTS): Data Transformation Services (DTS): FuncionalidadesFuncionalidades

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 DTSFormato 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é-Pré-processamento do pré-processamentoprocessamento

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é-Pré-processamento do pré-processamentoprocessamento

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

Usar o DTS WizardUsar o DTS Wizard Começar a mexer com a base

• link: ~robocup\aula\

OLAP Manager: funcionalidadesOLAP 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 Criar cubos a partir do warehouse relacional com o OLAP Manager Wizardrelacional com o OLAP Manager Wizard

OLE DB for OLAPOLE 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 Padrões da Microsoft “abertos e orientados a objetos”“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

MDX: sintaxe das consultasMDX: 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 MDXExemplos simples de uma consulta MDXWITH [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 Consultas MDX sobre cubo de dados primitivosprimitivos

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 Consultas MDX sobre cubo de dados derivadosderivados

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