Transcript presentation

Statistics 2 go
(simplePXsql, PX2go and API)
PC-Axis reference group, Tirana 2013
Lars Pedersen
Question:
How many persons are living in Greenland ?
Look to:
http://bank.stat.gl/beest1
Answer by a pc:
Answer by an iPad :
Answer by an iPhone:
Think off:
• Motorola Storno
• Sony Ericsson
• Nokia
• and px-web 2008
Question:
How many persons are living in Greenland ?
http://bank.stat.gl/beest1
http://bank.stat.gl/m.asp?m=beest1
Answer by a pc:
Answer by an iPad:
Overview
•
•
•
•
•
a little history
from px-files to a database
what is that good for?
px2go
api’s
• cliffhanger
a little history :
the family concept
the family concept
• We need to stay connected
• Meet, party and quarrel
• a road-map
the family concept
Create
procedures
Create
procedures
Create
procedures
Create
procedures
PX-files
?
DB
Nordic
PX-web
PC-Axis
PC-Axis/sql
Denmark
Norway
Ireland
…
from px-files to a database
Database specific settings:
[System]
sql_database=statbank2013
sql_baseowner=..
sql_datestamp=Y-D-M h:m:00
sql_endline=
sql_bulksave=1
from px-files to a database
All can be done batch by a few lines in
PX-JOB
@echo off
: Batch command line:
: PX-Job.exe {job} [in] {out} {err} {def} {log} {-options}
:PX-Job job=sql in=H:\database\test\ out=H:\database\test\ log=H:\database\test\pxjob1.txt err=H:\database\test\ -s -o1 -e1
PX-Job job=sql in=H:\database\test\ out=H:\database\test\ log=H:\database\test\pxjob1a.txt err=H:\database\test\ -s -o3 -e1
input
approximately 650 px files
normal tree structure
output with switch -s -o1 -e1
INSERT INTO statbank2013..PXTables VALUES('en','BEEST1','ANSI','2000','',null,'2010-22-03 09:00:00','2013-22-02
09:00:00','','','Annually','',null,null,0,null,null,'','','BE','Population','','',null,'Population January 1st by residence, gender, age and place of birth 19772013','','Population','Persons','','2013-22-02 09:00:00','','','','','Lars Pedersen, [email protected]',null,null,null,'Statistics
Greenland','','',null,null,null,null,'','','','','','','','','',null,'','','',null,null,null)
INSERT INTO statbank2013..PXVariables VALUES('en','BEEST1','time','1','R','','',null,'','',null,null,null,'','',null,'',null,null,'','')
INSERT INTO statbank2013..PXValues
VALUES('en','BEEST1','time','1','1977','1','',null,null,'','','','','',null,null,null,null,null,'',''),('en','BEEST1','time','1','1978','2','',null,null,'','','','','',null,null,null,null,null,'
',''),('en','BEEST1','time','1','1979','3','',null,null,'','','','','',null,null,null,null,null,'',''),('en','BEEST1','time','1','1980','4','',null,null,'','','','','',null,null,null,null,null,'',''),(
'en','BEEST1','time','1','1981','5','',null,null,'','','','','',null,null,null,null,null,'',''),('en','BEEST1','time','1','1982','6','',null,null,'','','','','',null,null,null,null,null,'',''),('en',
'BEEST1','time','1','1983','7','',null,null,'','','','','',null,null,null,null,null,'',''),('en','BEEST1','time','1','1984','8','',null,null,'','','','','',null,null,null,null,null,'',''),('en','BEE
ST1','time','1','1985','9','',null,null,'','','','','',null,null,null,null,null,'',''),('en','BEEST1','time','1','1986','10','',null,null,'','','','','',null,null,null,null,null,'',''),('en','BEEST
1','time','1','1987','11','',null,null,'','','','','',null,null,null,null,null,'',''),('en','BEEST1','time','1','1988','12','',null,null,'','','','','',null,null,null,null,null,'',''),('en','BEEST1'
,'time','1','1989','13','',null,null,'','','','','',null,null,null,null,null,'',''),('en','BEEST1','time','1','1990','14','',null,null,'','','','','',null,null,null,null,null,'',''),('en','BEEST1','
time','1','1991','15','',null,null,'','','','','',null,null,null,null,null,'',''),
output with switch -s –o3 -e1
Converts our 574 Mb px-files to tab-separated
csv-files
in less than
1 hour
transfer script (load all)
Empty database (drop if exsist)
Create 3 tables (tables, variables and values)
Loop though the px-files
set metafile=objf.opentextfile(f.name)
uttext = metafile.readall
metafile.close
adoConn.execute uttext
set metafile=nothing
Create table for data by use of newly added meta
Create insert statement for data
BULK INSERT statbank2013..PXData_NRE13 FROM 'G:\web\database\Greenland\National
accounts\NRE13_data.sql' WITH (CODEPAGE = 'ACP',FIELDTERMINATOR='\t',ROWTERMINATOR='\n')
simplePXsql (data tables)
simplePXsql (tables)
simplePXsql (variables)
simplePXsql (values)
what is that good for :
• a dirty px-file datacollection
•
•
•
•
Used for cleansing
queries across tables
translation services
bridge to the nordic model ?
connections
Create
PX-Edit
procedures
PX-files
Create
procedures
simplePXsql
Create
procedures
DB
Nordic
Create
procedures
PX-web
PC-Axis
PC-Axis/sql
Denmark
Norway
Ireland
…
nordic db model
px2go
• http://bank.stat.gl/m.asp
• Find table
– a link (http://bank.stat.gl/m.asp?m=beest1)
– category
– search
• Table selected
– known selection
– unknown selection
s… happens
• Greenland Tourism ( www.tourismstat.gl )
• automatic updated from our statbank
• api
http://json-stat.org
•
•
•
•
Graph
Table
Pyramid
Bubles
re-doing px2go for json-stat
matrix
getmeta
generate
Json-stat
showcube
Graph
Table
getdefault
API
Json-stat
csv
explore
cliffhanger
• groupings
• production
Screenshots from live - demo