Manual de utilização do BJIn OLAP
Contents
1. eva ETECNOLOGIA Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus EE S O PAULO 2010 2011 a Choose the index to be queried amp 127 0 0 1 8080 bjinolap ControllerServlet Ih Ho Lea Choose the index to be queried This index is still under construction Current status Processing CREATE INDEX Choose the index on which the queries will be issued spadawan A Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Luis Lopes Siqueira Undergraduate Research An OLAP tool based on the bitmap join index INSTITUTO FEDERAL DE encho climas e Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus S O PAULO ge 2010 2011 21 Ap s terminada a cria o do ndice ser solicitado os apelidos aliases das colunas para realizar as consultas apenas na primeira vez Please define the aliases for the spadawan columns How is the first time that this index is required please set the column names in the BJIn created for carrying out consultation Data cube definition Dimension Date Hierarchy Year Level Year Dimension Supplier Hierarchy Supplier s region Level Region Dimension Supplier Hierarchy Supplier s nation Level Nation Dimension Customer Hierarchy Customer c region Level Region Dimension Custome2. E DBRS er n S Jos ams my xSchema D xSpadawan Table lineorder 2 Date Hierarchy for Date Dimension Y description o O jallMemberName ee falMemberCapton S jalllevelName p defaultMember po O Table date memberReaderClass pt IprimaryKeyTable Ip E Database spadawan PostgreSQL L primankey _ ddateke 1 C Msbe 15 Siga esses mesmos passos e crie mais duas dimens es A pr xima figura mostra como ficou o schema Lembrando que pelo menos uma das dimens es deve conter um Level sendo um atributo da tabela DATE Schema Workbench File Edit View Options Windows Help SOE QA A xSchema 9 7 Table Meorder description Date Icaption Icache enabled Msbe Table date Supplier AA Supplier TIT Region 448 Nation Table supplie A Customer Aw Customer TIE Region Nation Table custom C IM Cube must contain measures E Database spadawan PostgreSQL Agora precisamos adicionar uma Medida Measure ao cubo Clique com o bot o invertido sobre o cubo e adicione uma medida Schema Workbench File Edit View Options Windows Help a E H de Measure for Spadawan C
3. Manual de utiliza o do BJin OLAP Sum rio Tig INSialango O FOMICALA tet 1 2 Instalando o BJln OLAP no Tomcat 7 3 Config rando o BJin OLAP pnt 5 3 1 Configurando o arquivo 5 3 2 Configurando o arquivo 1094 6 4 Criando um arquivo XML e carregando no BJln 8 5 Criando um ndice com o BJln OLAP 18 6 Executando uma Consulta ad idosa sand aaa 20 77205801152 109 5 26 7 1 Alterando a granularidade da consulta sese 26 Les COS quse amet ie re ert tiae a etsi tni aM a RR DR E 28 7 3 Mudando colunas para linhas e 29 7 4 Filtrando as colunas em 30 8 Demais OP ES EU 32 Bl sigo raia 32 Se Controle d 32 Atualizando ON q 6 o PR ARENA UU TT 35 10 Deletando o ndice nnns 36 11 Considera es FINAS peida 37 1 Instalando o Tomcat 7 1 Primei
4. Al m disso poss vel habilitar o ndice para update marcando a op o Enable Update Se o ndice permitir update ser criada uma tabela tempor ria e uma trigger na base de dados para armazenar as altera es na tabela de fatos Ap s isso crie o ndice ad Building BJIn e 49 a 127 0 0 1 8080 bjinolap Controllerservlet Building The bitmap join indices are currently being built You can close your browser during this operation or check the status of the building operation progress by accessing the Query a Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Luis Lopes Siqueira Undergraduate Research An OLAP tool based on the bitmap join index anos croco Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus S O PAULO o 2010 2011 6 Executando uma Consulta Query Para realizar uma consulta na parte de Query selecione o ndice em que as consultas ser o feitas F Choose the index to be queried 8 127 0 0 1 8080 bjinolap Controllerservlet Create Index Choose the index to be queried Choose the index on which the queries will be issued spadawan Proceed Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Lu s Lopes Siqueira Undergraduate Research An OLAP tool based on the bitmap join index INSTITUTO FEDERA
5. Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Luis Lopes Siqueira INSTITUTO FEDERAL DE Rm EDUCA O CI NCIA E TECNOLOGIA Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus Undergraduate Research An OLAP tool based on the bitmap join index E S O PAULD Campas ote 2010 2011 Caso o ndice permita update sera solicitado o nome de usu rio e senha do banco de dados Isso acontece para excluir tanto a tabela tempor ria quanto a trigger que foram criadas junto com o ndice como falado anteriormente Drop Index Select the index to be dropped Warning this index will be permanently deleted Available Index Name spadawan DBMS User Password Delete Developed by Anderson Chaves Camiel with the supervision of Professor Thiago Luis Lopes Siqueira 22 Undergraduate Research An OLAP tool based on the bitmap join index INSTITUTO FEDERAL DE BER e toos Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus S O fmz Campus bu 2010 2011 36 11 Considera es Finais Para esse manual usou se a distribui o Ubuntu 11 04 do Sistema Operacional Linux com as seguintes vers es de software o PostgreSQL Plus Standard 9 1 o Fastbit 1 2 4 o Schema Workbench 3 3 0 14703 A ferramenta BJln OLAP foi desenvolvida por Anderson Chaves Carniel com
6. 0 1 8080 bjinolap Controllerservlet DO EE ARGENTINA 24728485866 00 24728485866 00 23408923241 00 23408923241 00 1992 CANADA 25767399657 00 25767399657 00 8483573 25139319519 00 251393 i 2 8 SHAE ilii 765135 00 23044765135 00 i MHE CANADA BRAZIL JHHE li 81818118 753625 00 53625 8 CANADA 7901118 00 2 3 Hil 9 818 24187967278 00 24187967278 00 N S 90980 00 24926590980 00 123702156861 00 12370215686 8 25552134176 00 242894 03785 00 7 4 Filtrando as colunas em p ginas Similar se o anterior poss vel filtrar os resultados por p gina Por exemplo podemos ver o resultado de cada ano arrastando a coluna d year conforme a figura 127 0 0 1 8080 bjinolap Controllerservlet v Google XCUULCU IZIII TITS Available roll up from c nation level region Submit Roll Up Available drill down from s region 7 tolevel s nation 2 Submit Drill down Subtotals aggregate function SUM 2 here 395 Total for 1992 Total for 1993 Total for 1994 Total for 1995 region america AMERICA AMERICA 24728485866 00 24728485866 00 25487638226 00 25487638226 00 26336753625 00 25552134176 00 25552134176 00 23408923241 00 23408923241 00 23044765135 00 23044765135 00 23682943860 00 25767399657 00 25767399657 00 25415598482 00 25415598482 0
7. 238784835 25139319519 00 25139319519 00 Total for AMERICA 11856 00 11856 00 TOTAL 11856 00 Show chart Show column totals chart Show row totals chart E Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Lu s Lopes Siqueira Undergraduate Research An OLAP tool based on the bitmap join index ee pane RR Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus 6 S O PAULO 2010 2011 127 0 0 1 8080 bjinolap Controllerservlet v B Google Submit Query Executed 12333 ms Available roll up from c nation 2 to level c region 2 Submit Roll Up Available drill down from s region lt level s nation Submit Drill down Subtotals aggregate function SUM e d 1993 america rore AMERICA CANADA 2544779361 Show chart Show column totals chart Show row totals chart c B Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Luis Lopes Siqueira Undergraduate Research An OLAP tool based on the bitmap join index Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus E S O PAULO 2010 2011 31 8 Demais op es 8 1 Autocompletar A ferramenta fornece a op o de autocompletar uma palavra simplesmente usando o comando CTRL ESPA O A figura a seguir mostr
8. tabela resultado o gr fico tamb m ser alterado Os filtros s o explicados na se o 7 2 2055657130 7a10oiasa zraorzeraaso soa Ecosse 27734054014 Do zosorcoason 255557249900 zersrzananoo 27130732550 00 esee 20002779518 o0 2730875278 00 2521725174500 203571450 716503000 estan 10852000023200 30731171287a 00 110001402228 0005100575620 111232082528 o 009757514470 osso BRAZIL BRAZIL BRAZIL BRAZIL BRAZIL BRAZIL CHINA CHINA CHINA CHINA CHINA CHINA 1992 1993 1994 lu5 1905 1997 1992 1993 1994 1995 1975 lur 24 Hide column totals chart 1992 i M i Lo 994 1995 19706 1997 Hide row totals chart AFRICAAMERICA ASIA EUROPE 25 7 Opera es OLAP 7 1 Alterando a granularidade da consulta poss vel mudar a granularidade da consulta fazendo opera es Roll up e Drill down Para isso basta selecionar os atributos e clicar nos bot es Submit Roll up ou Submit Drill down dependendo do caso As imagens a seguir ilustram como feito 127 0 0 1 8080 bjinolap Controllerservlet Dimension Date Dimension Supplier Dimension Customer 1 Measure lo revenue Available roll up from c nation 5 to level c region gt Submit pannun Subtotals aggregate function SUM A drag paging columns here Total for year sse mem select c nation s nation d_year su
9. xYear Table date E Database spadawan PostgreSQL Agora adiciona se um Level a hierarquia Schema Workbench File Edit View Options Windows Help ES J xSchema i Level for Year Hierarchy El Attribute Value xSpadawan Table lineorder description column q am Year InameColumn E JparentColumn dd nullParentvalue Table Hate jordinalColumn linternalType uniqueMembers evelType lhideMembentf Integer caption JcaptionColumn formatter E Database spadawan PostgreSQL Schema Workbench File Edit View Options Windows Help Schema New Schemal Schemal xml xSchema ge xSpadawan Table lineorder T A xDate AR xYear column 2 InullParentvalue Level for Year Hierarchy Value Attribute New Level O 1 0 0 0 0 00 0 table 3 ordinalColumn O O hme o o juniqueMembers 0 hideMemberit n n EE EO Msbe 24 O E E Database spadawan PostgreSQL Column must be set Observa o N o se esque a de adicionar o tipo de dado de cada Level number integer Agora volte na hierarquia e coloque a Primary Key PK da tabela referente dimensao Schema Workbench File Edit View Options Windows Help Schema New Schemal Schemal xml
10. 0 24567901118 00 24567901118 00 24789403785 00 24789403785 00 E g i g 23878483573 00 23421063568 00 23421063568 00 24187967278 00 24187967278 00 24754364462 00 24754364462 00 25139319519 00 25139319519 00 25447793611 00 25447793611 00 24926590980 00 24377122763 00 24377122763 00 Total for AMERICA 122922611856 00 122922611856 00 122816859022 00 122816859022 00 123702156861 00 123702156861 00 123009629279 00 123009629279 00 TOTAL 122816859022 00 122816859022 00 123702156861 00 123702156861 00 123009629279 00 dE JE Show chart Showcolumntotals chart Showrowtotals chart Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Lu s Lopes Siqueira ER EDUCA O CI NCIA E TECNOLOGIA Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus S O PAULO Campes Salto 2010 2011 Undergraduate Research tool based the bitmap join index 30 e 127 0 0 1 8080 bjinolap Controllerservlet v amp Google e Submit Query Executed 12333 ms Available roll up from c nation tolevel c region Submit Roll Up Available drill down from s region to level s nation Submit Drill down Subtotals aggregate function SUM amp d year 199235 X MEM TOTAL s region 24728485866 00 24728485866 00 i 257 i
11. 0 1 8080 bjinolap Bu HLERA The Bitmap Join Index OLAP Tool This is the Bitmap Join Index OLAP Tool BJIn OLAP Tool aimed at decreasing the response time of analytical queries over data warehouses It maximizes the query processing performance of drill down roll up slice and dice and pivoting OLAP operations In addition it offers data visualization using pivot tables and charts to aid decision making Access to documentation BJIn OLAP Tool in gbd dc ufscar br bjinolap a Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Luis Lopes Siqueira Undergraduate Research An OLAP tool based on the bitmap join index BER oo Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus s o EIE Camper fato 2010 2011 3 Configurando o OLAP 3 1 Configurando o arquivo config properties Para conectar o BJln OLAP ao Banco de Dados no caso PostgreSQL necess rio definir o diret rio do FastBit ardea e ibis e a localiza o de armazenamento dos ndices Para isso configure o arquivo config properties do diret rio e usr share tomcat webapps BJlnolap WEB INF Ele deve ser configurado da seguinte maneira driver DRIVER DE CONEX O JDBC url URL DE CONEX O COM JDBC ardeaPath CAMINHO DO ARDEA DO FASTBIT ibisPath CAMINHO DO IBIS DO FASTBIT bjinolapPath CAMINHO ONDE SER O ARMAZENADOS OS NDICES No caso
12. 24881792219 00 152317517800 1923817517 TD m o E m 127 0 0 1 8080 bjinolap ControllerServlet v W gt Google Submit Query Executed 12333 ms Available roll up from c nation to level c region Submit Roll Up Available drill down from s region 2 level 5 nation gt Submit Drill down Subtotals aggregate function SUM drag paging columns here EE PERA E AMERICA TOM ayear 1992 193 194 3195 19e 9 Ascending 24728485866 00 25552134176 00 25394947066 00 24881792219 00 152381751178 00 152381751178 00 23408923241 00 23044765135 00 24139202535 00 23415922132 00 14122836099600 141228360996 00 E Subtotals prac RN 25767399657 00 24567901118 00 24789403785 00 25437181185 00 25938807561 00 151916291788 00 151916291788 00 All None Reverse 23421063568 00 24187967278 00 24754364462 00 23889977631 00 24128353399 00 144260209911 00 144260209911 00 O AFRICA 25139319519 00 25447793611 00 24377122763 00 25255042602 00 26017651642 00 151163521117 00 151163521117 00 O ASIA O EUROPE 122922611856 00 122816859022 00 123702156861 00 123009629279 00 124116351019 00 740950134990 00 740950 EN MIDDLE EAST 122816859022 00 123702156861 00 123009629279 00 124116351019 00 124382526953 00 740950134990 00 740950 Showchart Show colum
13. 78907473 00 31033660666 00 4523680328 0 5649244920 00 531288970300 5564296746 00 5706397148 00 5911965866 00 5670357260 00 33815151643 00 4860308375 0 b O restante an logo selecione os atributos que se deseja fazer Roll up ou Drill down e clique no bot o Submit Roll up ou Submit Drill down 27 7 2 Filtrando a consulta Tamb m poss vel filtrar os resultados da coluna simplesmente clicando sobre a coluna desejada A figura mostra como isso feito no exemplo 127 0 0 1 8080 bjinolap Controllerservlet v Google 5 Dimension Date select c_region c_nation s_region d_year sum lo_revenue where d_year gt 1992 and i Dimension Supplier Dimension Customer Measure lo revenue Submit Query Executed 12333 ms Available roll up from c nation 2 to level c region 2 Submit Roll Up Available drill down from s region 7 to level s nation Submit Drill down Subtotals aggregate function SUM drag paging columns here Ascending Total fo O Descending Subtotals All None Reverse AFRICA RICA CTASIA Q EUROPE EAST Total for AFRICA Z363 1100218 007 121814206476 00 125227429505 00 124482540377 00 124045085143 00 124115054240 00 743315481959 00 74331548195 24728485866 00 25487638226 00 26336753625 00 25552134176 00 25394947066 00
14. L DE EDUCA O CI NCIA E TECNOLOGIA Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus g Campus Satis 2010 2011 Caso o indice ainda esteja em constru o o BJln OLAP exibir mensagens informando qual o status da cria o do ndice como mostrado nas figuras Choose the index to be queried This index is still under construction Current status Processing the star join and creating a temporary table Choose the index on which the queries will be issued spadawan Proceed m Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Lu s Lopes Siqueira Undergraduate Research An OLAP tool based on the bitmap join index INSTITUTO FEDERAL DE EDUCA O CI NCIA E TECNOLOGIA Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus S O PAULO ae 2010 2011 20 ad Choose the index to be queried 127 0 0 1 8080 bjinolap ControllerServlet Create Index Choose the index to be queried This index is still under construction Current status Processing the data partition creation Choose the index on which the queries will be issued spadawan Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Luis Lopes Siqueira Undergraduate Research An OLAP tool based on the bitmap join index INSTITUTO FEDERAL DE
15. Lopes Siqueira Undergraduate Research An OLAP tool based on the bitmap join index INSTITUTO FEDERAL DE Sosa ETECNOLOGIA Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus S O PAULO B 2010 2011 17 5 Criando um indice com o BJIn OLAP Ap s carregar um arquivo XML vamos criar o ndice Na parte de Create Index selecione o arquivo carregado d um nome para o ndice e forne a o nome do usu rio e senha do banco de dados para que o programa possa acessar o data warehouse a Create Index 127 0 0 1 8080 bjinolap create jsp Create Index Select the XML file containing the description of your data cube and of the attributes to be indexed If your file is not available please upload it Give the index a name without spaces or special characters then enter your database user role and the password If you want to build your index over the materialized view that answer your queries please check the corresponding option and check the attributes to be indexed This can provide even better query processing performance but the index creation will take more time Create Bitmap Join Index Index description exemplo xml Index name spadawan Password Create Index over the Materialized View Enable Update Create Index Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Luis Lopes
16. Siqueira Undergraduate Research An OLAP tool based on the bitmap join index INSTITUTO FEDERAL DE am EDUCA O CI NCIA E TECNOLOGIA Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus S O PAULO Campes Salis 2010 2011 Neste caso poss vel criar o indice sobre uma vis o materializada Para isso selecione a op o conforme a imagem abaixo 18 a Create Index 127 0 0 1 8080 bjinolap create jsp Upload XML Create Index Select the XML file containing the description of your data cube and of the attributes to be indexed If your file is not available please upload it Give the index a name without spaces or special characters then enter your database user role and the password If you want to build your index over the materialized view that answer your queries please check the corresponding option and check the attributes to be indexed This can provide even better query processing performance but the index creation will take more time Create Bitmap Join Index 35 Index description exemplo xml Index name spadawan DBMS User usuario Password vosso Create Index over the Materialized View Enable Update Create Index Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Lu s Lopes Siqueira Undergraduate Research An OLAP tool based on the bitmap join index
17. Submit Roll Up Available drill down from c region gt to level c nation lt Submit Drill down Subtotals aggregate function SUM drag paging columns here C ES E EE EEE a EE asi mos E 2695105552 00 27231720424 00 2 74550215000 162740735600 zezsrsszoo 23524 50074970600 1305152279 o 13700190300 0013061725 0809 35251707 0013573354 o 22955001200 320042738106 o sess b Como se pode perceber as linhas da tabela que eram pa ses foram agrupadas em continentes Um exemplo contr rio fazer um Drill down de c region para c nation clicando no bot o Submit Drill down No caso ele mostra os pa ses c nation mas ainda os agrupa em Continentes c region Dimension Date select c region c nation s nation d year sum lo revenue where d year gt 1992 anc Dimension Supplier Dimension Customer Measure lo revenue Submit Query Executed 13637 ms Available roll up from c nation tolevel c region 2 Submit Roll Up Subtotals aggregate function SUM 3 nom mum 0 as lel elEe es eee ESSEDES ED ED EEE MOZAMBIQUE 75216240 Toi for AFRICA 27275002050 oo 2762659752000 275257590400 zr2ntenasoo zrsseoezaoo cerco oo 350455045 0 2129721005 BRAZIL 5195935654 00 5057594670 00 5171107872 00 5080497855 00 5249617142 00 52
18. a orienta o do professor Thiago Lu s Lopes Siqueira e com suporte do Instituto Federal de Educa o Ci ncia e Tecnologia de S o Paulo Campus Salto Este manual foi escrito por Lucas de Carvalho Scabora com afilia o institucional do Instituto de Ci ncias Matem ticas e de Computa o da Universidade de S o Paulo e a orienta o da Professora Doutora Cristina Dutra de Aguiar Ciferri A revis o do manual foi feita por Anderson Chaves Carniel 37
19. a Jal Table for Spadawan Cube Attribute Value xspadawar CC CL DDT Table lineorder lineorder Database spadawan PostgreSQL Com um cubo de dados criado poss vel adicionar suas dimens es O manual explicar como adicionar a dimens o Date pois o restante an logo Clique com o bot o invertido sobre o cubo e adicione uma nova dimens o 11 Schema Workbench File Edit View Options Windows Help E Schema New Schemal Schemal xml IDISEIDDCIDEE E xSchema m Dimension M Dimension for Spadawan Cube M Dimension for Spadawan Cube Cube Dimension 0 Table lineorder description o m pum z roreignke xNew Dimension D caption visible Hierarchy New Hierarchy O is invalid E Database spadawan PostgreSQL Configure essa dimens o com a Foreign Key FK da tabela de fatos correspondente dimens o criada Schema Workbench File Edit View Options Windows Help F Schema New Schemal Schemal xml ICIBESSELERENCI E xschema Dimension E Lm E E Attribute Value xspadavar n Table lineorder description b mu jforeignKey lo orderdate 1 TimeDimension usagePrefix lapon Msbe Hierarchy New Hierarchy O is invalid Database spadawan PostgreSQL 12 Clicando com o bot o invertido sobre a dimens o c
20. a permitir a cria o de ndices bitmap de jun o e false para negar canUpdatelndex True para dar permiss o de atualiza o de ndices bitmap de jun o nos ndices listados na tag indices e false para negar canDeletelndex True para dar permiss o de remo o de ndices bitmap de jun o nos ndices listados na tag indices e false para negar superUser True para declarar como um superusu rio e false para negar Caso o usu rio seja um superusu rio ele j possui todas as permiss es citadas anteriormente Assim o arquivo que especifica essas permiss es chamado de user xml Como exemplo podemos ter lt Users gt User name user passwd pass canUploadXml true canCreatelndex true superUser false canDeletelndex false canUpdatelndex true User name admin passwd admin superUser true gt lt Users gt Feito isso ao iniciar programa ele pedir login Main Upload XML Create Index Update Index Query Drop Index The Bitmap Join Index OLAP Tool This is the Bitmap Join Index OLAP Tool BJin OLAP Tool aimed at decreasing the response time of analytical queries over data warehouses It maximizes the query processing performance of drill down roll up slice and dice and pivoting OLAP operations In addition it offers data visualization using pivot tables and charts to aid decision making Access to documentation BJIn OLAP Tool in gbd dc ufscar
21. a um exemplo Main Upload XML Create Index Update Index Query Drop Index Edit ERE Show log Senne BJin Query Bitmap Join Index spadawan Available Columns Dimension Date Dimension Supplier 5 nation Dimension Customer Measure lo revenue Submit Query Executed 13637 ms Available roll up from c nation 2 tolevel c region Submit Roll Up Subtotals aggregate function SUM drag paging columns here sum lo revenue ALGERIA 5210203041 00 5544224736 00 5523274487 00 5221222720 00 5414180620 00 5317133224 00 32230238828 00 4748225636 0 5273326268 00 5459532506 00 5423351953 00 5414441673 00 5399920581 00 5206062252 00 32176635233 00 4848465050 0 AFRICA KENYA 5446836318 00 5749700066 00 5667447652 00 5485078952 00 5337243885 00 33234842381 00 476187370901 8 2 Controle de usu rios A ferramenta BJln OLAP tamb m inclui um controle de permiss es para diferentes usu rios Normalmente no caminho default explicado na se o 3 poss vel colocar um arquivo XML user xml para especificar as permiss es de cada usu rio Caso uma permiss o n o for declarada assume se que o usu rio n o tem essa permiss o false Essas permiss es s o Permiss o Descri o canUploadXml True para permitir o carregamento de arquivos XML descrevendo o cubo de dados e false para negar canCreatelndex True par
22. bjinolap ControllerServlet Query Bitmap Join Index spadawan _ Dimension Date select c nation s nation d year sum lo revenue 5 Dimension Supplier where d year 1992 and d year 1997 Dimension Customer 7 Measure a Executed 10937 ms Available roll up from c nation lt to level c region 2 Submit Roll Up mS E smssneroo sarmarasco 55227440720 sewisosmoo 0 ars 00 sasseossmoo sermosenneoo 5202310006 sssznemonoo 2853457200 assssoszoo E seamesoamoo srassrorroo senssarsheo semsonsmroo sezzssezroo sesemsresroo 97657527500 0155506500 soreo Eover ara 5273326268 00 5459532506 00 5423351953 00 5414441673 00 5399920581 00 5206062252 00 32176635233 00 FRANCE 5446021992 00 5447345681 00 5423856797 00 4998791018 00 9703047448 00 5482339378 00 32501402314 00 4479084086 00 gt 23 Tamb m poss vel visualizar o resultado em forma de gr ficos apenas clicando nos bot es Show chart exibe o gr fico geral da tabela Show column totals chart exibe um gr fico com apenas o eixo das colunas da tabela ou Show row totals chart exibe um gr fico com apenas o eixo das linhas da tabela que est o abaixo do resultado da consulta Esses gr ficos s o baseados na tabela resultado ou seja se for aplicado filtros na
23. br bjinolap Login OLAP Tool Login usu rio Password Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Luis Lopes Siqueira Undergraduate Research OLAP tool based on the bitmap join index INSTITUTO FEDERAL DE ma ETECNOLOGIA Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus S O PALLO eU Amps dra 2010 2011 33 Dependendo do usu rio ele poder fazer o que for especificado pelo arquivo Por exemplo o usu rio user poder criar e atualizar ndices mas n o poder remover nenhum desses ndices Toda vez que algum usu rio criar um ndice o arquivo XML ser alterado Por exemplo se criarmos o ndice do manual com o usu rio user teremos lt Users gt User name user passwd pass canUploadXml true canCreatelndex true superUser false canDeletelndex false canUpdatelndex true gt lt IndexName gt spadawan lt IndexName gt lt User gt lt User name admin passwd admin superUser true gt lt Users gt 34 9 Atualizando o indice Na parte Update Index poss vel atualizar os ndices criados Para isso necess rio que o ndice permita update se o 5 Feito isso haver uma trigger no banco de dados que registrar em uma tabela auxiliar todas as inser es feitas na tabela de fatos Para que seja poss vel atualizar o ndice basta inserir uma
24. de executar o programa No caso do Ubuntu executa se o seguinte comando no terminal estando dentro da pasta do Schema Workbench e sh workbench sh Schema Workbench File Edit View Options Windows Help Agora montaremos um cubo de dados para o data warehouse spadawan O primeiro passo conectar se com o banco de dados Para isso em e Options gt Connection e configure a conex o No exemplo Connection Name Ispadawan Connection Type Settings Host Name MS SQL Server MS SQL Server Native localhost MaxDB SAP DB Database Name Ispadawan Port Number 2432 User Name lucas Password Access Native JDBC ODBC NDI Ap s conectar se com a Base de Dados podemos iniciar a constru o do cubo de dados V em e File gt New gt Schema Schema Workbench File Edit View Options Windows Help Schema New Schemal Schemal xml OBESE Jal Attribute E Database spadawan PostgreSQL Clique com o bot o direito do mouse sobre o Schema e adicione um cubo atribuindo um nome a ele Schema Workbench File Edit View Options Windows Help xschema ed E Attribute description Fact name must be set E Database spadawan PostgreSQL 10 Agora adicione uma tabela de fatos a esse cubo tamb m clicando com o bot o invertido sobre ele Schema Workbench File Edit View Options Windows Help OF 2 xschem
25. deral Institute of Education Science and Technology Salto Campus Undergraduate Research An OLAP tool based on the bitmap join index amp a 2010 2011 7 3 Mudando colunas para linhas e vice versa poss vel mudar os eixos das colunas e linhas simplesmente clicando e arrastando como mostram as figuras e E 127 0 0 1 8080 bjinolap Controllerservlet v 198 Google Submit Query Executed 12333 ms Available roll up from c nation to level c_region Submit Roll Up Available drill down from s region gt tolevel s nation gt Submit Drill down Subtotals aggregate function SUM drag paging columns here eT n n n E ii 25139319519 00 25447793611 00 24377122763 00 26017651642 00 151163521117 00 151163521117 00 122922611856 00 122816859022 00 123702156861 00 123009629279 00 124116351019 00 124382526953 00 740950134990 00 740950134990 00 122922611856 00 122816859022 00 124116351019 00 124382526953 00 740950134990 00 740950134990 00 Show column totals chart Show totals chart Undergraduate Research An OLAP tool based on the bitmap join index EDUCA O CI NCIA E TECNOLOGIA Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus sin nau n Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Luis Lopes Siqueira 8 127 0
26. e http diegobenna blogspot com 2011 01 install tomcat 7 in ubuntu 1010 html 2 Instalando o OLAP no Tomcat 7 Para instalar o BJln OLAP no tomcat copie o arquivo BJInolap war dentro da pasta BJln OLAP Tool para o diret rio e usr share tomcat webapps Agora inicie o tomcat e clique em Manager App conforme mostrado na figura 4 Apache Tomcat 7 0 22 gt E 127 0 0 1 8080 e 8 Google e Home Documentation Configuration Examples Wiki Mailing Lists Find Help Apache Tomcat 7 0 22 m Paste Software Foundation http www apache org If you re seeing this you ve successfully installed Tomcat Congratulations Recommended Reading Security Considerations HOW TO Manager Application HOW TO Clusterin n Replication HOW TO Host Manager eme Manager App Developer Quick Start Tomcat Setup Realms amp AAA Servlet Examples Servlet Specifications First Web Application JDBC DataSources JSP Examples Tomcat Versions Managing Tomcat Documentation Getting Help For security access to the manager webapp Tomcat 7 0 Documentation FAQ and Mailing Lists is restricted Users are defined in ARO Tomcat 7 0 Configuration The following mailing lists are available Tomcat Wiki announce tomcat apache org In Tomcat 7 0 access to the manager ae Im announcements releases security application is split between different users Find additional important configuration vulnerability notificati
27. m lo_revenue where d year gt 1952 and d year lt 1997 Submit Query Executed 11359 ms m sszasrssoo ssssrasron semzzzraooe ssmscmesssoo E 235500520 O sesrseaoon struorerzon eoesressco szasox7a200 serusererso sos o 542067450 exesoamoo srasesmrroo SODISTSDADO ssesmecsrce Secamserzroo senemsresroo 97697527500 5381485505 00 5835969140 00 5768239246 00 5499626199 00 5644577269 00 5585025333 00 33714922692 00 4806260460 00 47607 5273326268 00 5459532506 00 5423351953 00 5414441673 00 5399920581 00 5206062252 00 32176635233 00 4848465050 00 44630 5447345681 1 5399920581 00 5423856797 00 4998791018 00 5703047448 00 5482339378 00 32501402314 00 o amam asma m I mm a mms a mas I emma aa emas I Os a maes I EDDA a mmm mm 5446021992 00 Neste caso fazemos um Roll up do atributo nation Pa ses region Continentes pr xima imagem gera o resultado 26 127 0 0 1 8080 bjinolap Controllerservlet TT Dimension Date select c region s nation d year sum lo revenue amp Dimension Supplier where d year 1992 and d year 1997 5 Dimension Customer 3 Measure lo revenue Submit Query Executed 9458 ms Available roll up from s nation 2 to level s_region 2
28. n totals chart Show totals chart cm Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Luis Lopes Siqueira Undergraduate Research An OLAP tool based on the bitmap join index Supported and sponsored by Sao Federal Institute of Education Science and Technology Salto Campus sin nao 127 0 0 1 8080 bjinolap Controllerservlet v B Google Submit Query Executed 12333 ms Available roll up from c nation to level c region gt Submit Roll Up Available drill down from s region to level 5 nation Submit Drill down Subtotals aggregate function SUM gt drag paging columns here eto AMERICA for Tortor AMERICA TOTAL m O Descending 2548 76552877717 00 76552877717 00 Subtotals 00 70136632236 00 70136632236 00 None Reverse 554 24567901118 00 75750899257 00 75750899257 00 992 1993 23421 00 24187967278 00 71487514419 00 71487514419 00 E 1994 25447793611 75513704110 00 75513704110 00 1995 Total for AMERICA 7 00 123702156861 00 369441627739 00 369441627739 00 11997 00 123702156861 00 369441627739 00 369441627739 00 Showchart Show column totals chart Show row totals chart Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Lu s Lopes Siqueira Supported and sponsored by Sao Paulo Fe
29. nstalled Tomcat Congratulations Home Documentation Configuration Examples Wiki Mailing Lists Apache Tomcat 7 0 22 T Recommended Reading Security Considerations HOW TO Manager Application HOW TO Clu R Server Status Manager App Developer Quick Start Tomcat Setup First Web Application Managing Tomcat For security access to the manager webapp is restricted Users are defined in CATALINA HOME conf tomcat users xml In Tomcat 7 0 access to the manager application is split between different users Read more Release Notes Change Migration Guide Realms amp AAA JDBC DataSources HOW TO Serviet Examples JSP Examples Documentation Tomcat 7 0 Documentation Tomcat 7 0 Configuration Tomcat Wiki Find additional important configuration information in CATALINA HOME RUNNING xt Developers may be interested in Tomcat 7 0 Bug Database Tomcat 7 0 JavaDocs Host Manager Serviet Specifications Tomcat Versions Getting Help EAQ and Mailing Lists The following mailing lists are available he announce tomcat apache org Important announcements releases se vulnerability notifications Low volume users tomcat apache org User support and discussion taglibs user tomcat apache org User support and discussion for Apache Taglibs dev tomcat apache org Development mailing list including commit messages Observa o Esse tutorial de instala o do tomcat foi retirado do sit
30. ons Low volume Read more information in CATALINA HOME conf tomcat users xml users tomcat apache Org CATALINA_HOME RUNNING t xt User support and discussion Release Notes T taglibs user tomcat apache org Developers may be interested User support and discussion for Apache Taglibs hangelog Tomcat 7 0 Bug Database dev tomcat apache org Migration Guide Development mailing list including commit Tomcat 7 0 JavaDocs messages Agora selecione o programa conforme a figura Query Index ad manager amp 127 0 0 1 8080 manager html e Apache Software Foundation http www apache org Tomcat Web Application Manager Message List Applications HTML Manager Help Manager Help Server Status Start Stop Reload Undeploy L None specified Welcome to Tomcat Expire sessions with idle gt 30 minutes Start Stop Reload Undeploy bjinolap None specified bjin web Expiresessions with idle gt 30 Start Stop Reload Undeploy docs None specified Tomcat Documentation 0 Expiresessions with idle 2 30 minutes Start Stop Reload Undeploy lexamples None specified Servlet and JSP Examples ED hs TR Expire sessions with idle gt 30 http www apache org __ Start Stop Reload Undeploy Se tudo estiver correto o programa iniciar a Main 4 127 0
31. ou mais tuplas na tabela de fatos de seu data warehouse que j possua um ndice no BJln OLAP Assim ao verificar atualiza es sobre o ndice que sofreu update temos o seguinte Update Index Select the index to be updated then enter your database user and the password You can check if the index has updates available If the index was created over a materialized view this index will be rebuilt The update over a materialized view of the index creation will take more time The index has updates m Update Bitmap Join Index Index Name spadawan Password Materialized View 9 Check for Updates Update Index Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Luis Lopes Siqueira INSTITUTO FEDERAL D aa EDUCA O CI NCIA E TECNOLOGIA Supported and sponsored by Sao Paulo Federal Institute of Education Science and Technology Salto Campus EB Undergraduate Research An OLAP tool based on the bitmap join index 2010 2011 Basta clicar no bot o Update Index para atualizar o ndice com os novos dados inseridos 35 10 Deletando o indice Para remover um ndice v em Drop Index selecione o ndice desejado e clique em Delete e a 127 0 0 1 8080 bjinolap drop jsp Drop Index Select the index to be dropped Warning this index will be permanently deleted Available Index Name spadawan
32. r Hierarchy Customer c nation Level Nation Measure Revenue lo revenue Set Aliases cm Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Lu s Lopes Siqueira Undergraduate Research An OLAP tool based on the bitmap join index Supported and sponsored by Sao Federal Institute of Education Science and Technology Salto Campus S O PAULO Feito isso poss vel executar consultas sobre o ndice A pr xima imagem exemplifica uma consulta Eh 6 8 E O EFA DA DE ER Edit columns Show log Exchange BJin Query Bitmap Join Index spadawan Available Columns Dimension Date select c region s nation d year sum lo revenue where d year gt 1952 and d year lt 199 Dimension Supplier Dimension Customer Measure lo revenue Submit Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Luis Lopes Siqueira Undergraduate Research An OLAP tool based on the bitmap join index 22 Para auxiliar na escrita da consulta existe no lado esquerdo do campo da consulta a hierarquia completa do cubo de dados gerada pelo Schema Workbench Available Columns 8 5 Dimension Date 5 Hierarchy Year B 5 Dimension Customer 5 Hierarchy Customer Assim foi feita a consulta exemplo sobre a base de dados Spadawan A resposta da consulta aparece abaixo EI 127 0 0 1 8080
33. rie uma hierarquia No exemplo usaremos apenas ano Schema Workbench File Edit View Options Windows Help 8 jeje Schema New Schemal Schemal xml aa s vor ex B Hierarchy for Date Dimension gt xspad padawan New Hierarchy O Table lineorder description EE S S S lib A S I q A xDate fhasa p Membe NO all memb xNew Hierarchy 0 lalMemberd e er this hierarchy has an all member lallLevelName efaultMember 6031917 D o vim o 2 115 o 22 oio Oo D w w IS At least one Level must be set for Hierarchy E Database spadawan PostgreSQL Adicione a tabela correspondente a essa hierarquia Schema Workbench File Edit View Options Windows Help s s u Schema New Schemal Schemal xml 4 4 s 5s wr on xSchema Table for Year Hierarchy ar Attribute p schema Table lineorder name Cis falas A xDate Am xYear Table Table Table Table does not exist in database E Database spadawan PostgreSQL 13 No caso como a dimens o o ano ser usado a tabela DATE do spadawan Schema Workbench File Edit View Options Windows Help 5 Schema New Schemal 5 1 xSchema xSpadawan Table lineorder A AR
34. ro baixe o arquivo apache tomcat 7 0 6 tar gz do link e hittp Aomcat apache org download 70 cqi 2 Mova o programa para um diret rio onde deseja instalar No caso usou se o diret rio usr share Para isso o comando usado foi considerando que o terminal estava no diret rio que cont m a pasta do tomcat e sudo mv apache tomcat 7 0 8 usr share tomcat 3 Para configurar os usu rios do tomcat necess rio modificar o arquivo tomcat users xml no caminho e sudo gedit usr share tomcat conf tomcat users xml Neste caso sugere se o seguinte trecho configurando usuario e senha como desejar lt xml version 1 0 encoding utf 8 gt lt tomcat users gt lt role rolename manager gui gt lt role rolename manager script gt role rolename manager gt role rolename admin gui gt role rolename admin script gt role rolename admin gt user username usuario password senha roles manager gui admin gui manager admin manager script admin script gt lt tomcat users gt 4 Agora execute o tomcat e sudo usr share tomcat bin startup sh 5 Como tomcat ativado abra um navegador e digite o endere o e http 127 0 0 1 8080 Se tudo estiver certo a seguinte imagem aparecer a Apache Tomcat 7 0 22 e amp 127 0 0 1 8080 vle Google e E Find Help The Apache Software Foundation http www apache org If you re seeing this you ve successfully i
35. sabilitam a hierarquia das categorias do log true ou false Em azul temos que o n vel do detalhes log definido nas categorias bjinolap BUILD bjinolap QUERY e bjinolap A categoria bjinolap abrange as categorias bjinolap BUILD e bjinolap QUERY sendo assim o n vel de detalhamento e os appenders que forem definidos na categoria bjinolap afetar as outras categorias Para exemplificar se a categoria bjinolap tiver o n vel de detalhamento TRACE as categorias bjinolap BUILD e bjinolap QUERY tamb m ter o esse n vel independente do n vel que for especificado para cada um deles O mesmo acontece com os appenders Assim desabilitando a hierarquia colocando false o n vel de detalhamento e appenders definido para a categoria bjinolap n o interferir nas outras categorias 4 Criando um arquivo XML e carregando no BJln OLAP Para gerar o ndice necess rio um arquivo XML de especifica o do cubo de dados do data warehouse contendo os atributos a serem indexados Para isso aconselha se utilizar o programa Mondrian Schema Workbench Para criar o arquivo ser feito um exemplo passo a passo Observa o Esse manual apenas exemplifica a cria o de um XML para a base de dados usada que no caso chama se spadawan Primeiramente ser necess rio conectar se com o banco de dados Para isso baixe o JDBC de seu banco de dados no caso PostgreSQL e o coloque na pasta Drivers do Schema Workbench Posteriormente po
36. se configurou os atributos como driver org postgresql Driver url jdbc postgresql localhost 5432 spadawan ardeaPath home lucas Downloads fastbit ibis 1 2 4 examples ardea ibisPath home lucas Downloads fastbit ibis1 2 4 examples ibis bjinolapPath detault Observa es e org postgresal Driver o nome do driver que conectar com o banco de dados que no caso o PostgreSQL e spadawan o nome do banco de dados usado exemplo e home lucas Downloads fastbit ibis1 2 4 o caminho onde o FastBit foi instalado recomendado a vers o 1 2 4 ou mais atual do FastBit para que o BJln OLAP funcione corretamente e default a configura o padr o que salva os ndices no diret rio o bjin nome do ndice criado 3 2 Configurando o arquivo log4j properties Esse arquivo presente no mesmo diret rio que o arquivo config properties define a configura o de logs de BJln OLAP Ele possui a seguinte estrutura log4j rootLogger DEBUG stdout file log4j logger bjinolao DEBUG stdout file log4j logger bjinolap BUILD DEBUG file log4j logger bjinolap QUERY DEBUG stdout Disable an inheritance system for additivity log4j additivity bjinolap QUERY false true log4j additivity bjinolap BUILD false true log4j appender stdout org apache log4j ConsoleAppender log4j appender stdout layout org apache log4j PatternLayout Pattern to output the caller s file name and line number log4j appender stdout layo
37. ube Value Spadawan Table lineorder description o 1 Jaggregator sum A Date column sid 9 Am Year dformatsString 8888 Year formatter caption Table date PITE tion A Supplier Supplier asas Region 2444 Nation Table supplie Customer AR Customer TIE Region 435 Nation Table custom Revenue LLULLLI Tj E Database spadawan PostgreSQL 16 Pronto o cubo de dados exemplo est pronto Salve o documento XML e voc ter o arquivo para usar no BJln OLAP Assim que terminar de gerar o arquivo XML apenas carregue o no programa ad Upload the XML file containin 127 0 0 1 8080 bjinolap ControllerServlet cmd uploadXMLstup Upload the XML file containing the data cube description and the indices to be built Upload a valid XML file describing the data cube and the attributes to be indexed To create a valid XML file we recommend the Mondrian Schema Workbench The XML file must precisely describe e The involved schemas fact and dimension tables e All constraints primary keys and foreign keys e Attributes names and data types for both levels and measures File has been successfully uploaded Upload XML file File name Click here to build the index Developed by Anderson Chaves Carniel with the supervision of Professor Thiago Luis
38. ut ConversionPattern d 965p 9 YF L log4j appender file2org apache log4j RollingFileAppender log4j appender file File bjin bjinolap log log4j appender file MaxFileSize 100KB Keep one backup file log4j appender file MaxBackuplndex 1 log4j appender file layout org apache log4j PatternLayout log4j appender file layout ConversionPattern d 5p Yt Y F L YmY n No trecho em azul est especificado como ser o og Primeiramente preciso especificar o n vel de detalhamento explicado na tabela a seguir TRACE Fornece mensagens mais detalhadas que o n vel DEBUG DEBUG Fornece mensagens detalhadas sobre o uso do programa INFO Fornece mensagens menos detalhadas que apenas informam sobre o andamento das opera es WARN Fornece mensagens apenas para situa es potencialmente nocivas ERROR Fornece mensagens dos erros que n o impedem que o programa continue executando FATAL Fornece mensagens apenas dos erros que fazer o programa abortar Os n veis superiores abrangem os n veis inferiores logo se assumirmos o n vel DEBUG temos os n veis DEBUG INFO WARN ERROR e FATAL Ap s isso temos os appenders stdout e file que direcionam a sa da do log para um arquivo file e ou para a sa da padr o stdout O caminho onde estar o arquivo do log foi especificado em verde Basta alterar esse caminho para armazenar o log em outro local Em vermelho temos as configura es que habilitam ou de
Download Pdf Manuals
Related Search