Observatório de dados/BI/Exemplo/2.1/Std-BI

Testes realizados:

Especificação formal de produto final contemplando os requisitos do exemplo, expressa com base na linguagem do Modelo de referência.

Dados e objetivos do exemplo rodando na ferramenta Std-BI.

Carga convencional

A carga é automática, mas internamente scripts são gerados e podem ser editados.

Abaixo scripts gerados por datapackage.json:

CREATE EXTENSION file_fdw;
CREATE SERVER pgfiles FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE exemplo2_1_region(
  region text,	--Label of the report describing the project
  wdId	text,	-- Wikidata concept (semantic) identifier
  name	text, --Common name of the region, in portuguese
  fullname	text, -- Complete name, with prefixes and prepositions
  creation	integer,	--Region official creation year
  extinction	integer,	-- Region official creation year (null for in use)
  postalCode_ranges	text, -- Numeric ranges of postal codes
  notes	text,	--Notes about assegments, dates or changes
) SERVER pgfiles 
OPTIONS ( filename 'https://raw.githubusercontent.com/datasets-br/state-codes/master/data/br-region-codes.csv', format 'csv' );

CREATE FOREIGN TABLE exemplo2_1_state(
  subdivision	text,	--State-name 2-letter abbreviation, ISO 3166:BR
  region text,	-- Region name abbreviation with 1 or 2 letters
  name_prefix	text,	-- Name prefix, in portuguese
  name	text, --Common name of the region, in portuguese
  id	integer,	-- Short serial ID for subdivisions, starting from 1 (for base36 subtract 1).
  idIBGE	text,	-- IBGE statdandard identifier
  wdId	text,	-- Wikidata concept (semantic) identifier
  lexLabel	text,	-- Brazilian's LEX URN syntax translation of the official state name
  creation	integer,	--State official creation year
  extinction	integer,	-- State official creation year (null for in use)
  -- ...,
  postalCode_ranges	text, -- Numeric ranges of postal codes
  notes	text	--Notes about assegments, dates or changes
) SERVER pgfiles 
OPTIONS ( filename 'https://raw.githubusercontent.com/datasets-br/state-codes/master/data/br-state-codes.csv', format 'csv' );

Aqui pode-se manter as VIEWS como referência ao stream CSV, ou fazer um CREATE TABLE AS SELECT ...

Carga alternativa

Usando CREATE TABLE gerado a partir de pacotes tais como Pandas... E depois carga com COPY.

Filtragem e junção

CREATE "Regiões" AS -- filtragem
  SELECT region, name "Região", creation "Criação", "wdId"
  FROM exemplo2_1_region
  WHERE extinction IS NULL
; 

CREATE "UFs" AS -- filtragem e junção
  SELECT  s.subdivision "UF", s.region, r.name region_name, s.name "Nome", s."utcOffset", s."idIbge",  s.wdId, km2
  FROM exemplo2_1_state s INNER JOIN "Regiões" r ON r.region=s.region
  WHERE s.extinction IS NULL
;

Sumarização

Sumarizando por simples contagem, e somando áreas a cada grupo, ambos com base na junção.

CREATE cards_data AS -- filtragem
  SELECT count(DISTINCT region) "Total de regiões", count(DISTINCT "UF") "Total de UFs", 
  FROM "UFs"
;
CREATE graph_region_data AS -- filtragem
  SELECT region, sum(km2) km2 
  FROM "UFs" GROUP BY 1 ORDER BY 2 DESC 
;

Relatório

Gerando HTML5 com Mustache e biblioteca vinculada (ver ref1 ref1-source), template TPL:

<div class="region_ufs">
  <span>{{region_name}}</span>
  {{#children}}
    <table><tr> <td>{{Nome}} <td>{{UF}},  <td>{{idIbge}} <td>{{utcOffset}}
  {{/children}}
</div>

let UFs = GET_SQL_VIEW('"UFs"');
let tree = _makeTree({q:UFs, id:"UF", children:"region"});
let output = Mustache.render(TPL, tree);