Instalando y configurando TS Gateway y TS Web Access

Muchas veces los usuarios tienen la necesidad de poder conectarse remotamente a sus aplicaciones de trabajo diario o a su PC de la oficina de manera segura y sencilla, existen muchas alternativas como pueden ser conectarse a la VPN de su trabajo, instalar uno de los tantos programas que existen en internet de control remoto etc.

Pero una de las formas más practicas seguras y confiables hasta el momento que en particular yo conocía era por Citrix.

Si bien en mi opinión Citrix lleva ventaja sobre la solución que propone Microsoft con Windows Server 2008 Terminal Services RemoteApp, y más en ambientes de grandes empresas, Microsoft 2008 TS es una buena alternativa para poder montarlo en pymes.

A continuación voy a explicar de la mejor manera posible como instalar, configurar y como publicar aplicaciones con terminal services.

Como primera medida tenemos que tener bien en claro como sería un escenario básico de nuestro ambiente de Terminal:

La conexión del punto 1 al 2 se establece por medio del protocolo RDP encapsulado
en un HTTPS (HTTP sobre SSL). Para recibir esta conexión HTTPS en el perímetro
de red, el servidor TS Gateway debe estar ejecutando Internet Information Services (IIS)
Del servidor web. Después de recibir la conexión, el servidor TS Gateway a continuación, elimina los datos HTTPS y envía los paquetes RDP a los servidores de terminal de destino (punto 3) que se encuentran detrás de un segundo firewall interno.

En este escenario Active Directory Domain Services debe estar instalado en el TS Gateway para validar ó denegar las conexiones entrantes.

Para esta ocasión debido a la falta de recursos en mi computadora personal estaremos utilizando para la práctica 1 DC llamado 8KLABDC01 y otro servidor que será el TS Gateway además de alojar las aplicaciones que vamos a publicar 8KLABAP03.

1.       Bueno primero nos logueamos en el servidor 8KLABAP03 con las credenciales administrator de nuestro dominio.
2.       Vamos a la consola de Server manager y expandimos Roles, en el panel de detalle de la derecha seleccionamos la opción Add Roles.
3.       Nos aparecerá una pantalla de información “Before of begin” le damos Next.
4.       En la pantalla Select Server Roles, tildamos la opción de Terminal Services y hacemos click en Next.

En la pantalla Terminal Services nos hace una pequeña introducción a las propiedades de TS leemos y le damos click en Next.
En Roles Services deberemos elegir que rol de Terminal Services va a tener nuestro servidor, en este caso por ahora vamos a marcar Terminal Server y TS Gateway y le damos Next.

Es posible que al marcar TS Gateway nos aparezca un cuadro de dialogo que nos informa que para instalar este rol en el servidor se requieren tener otros roles instalados, le decimos que si que agregue los roles requeridos (Add required Role Services)

8. En la siguiente pantalla solo le damos Next, nos informa que antes de instalar cualquier aplicación a para publicar debemos tener el Terminal Server instalado.

9. En la siguiente pantalla elegimos la opción do not require Network Level Authentication, para que las computadoras que corran con cualquier versión se puedan conectar a este terminal server.

10. En la siguiente pantalla Licensing mode debemos especificar el tipo de licencia de terminal que poseemos, vamos a elegir en este caso configurar más tarde ya que como no poseemos de una licencia, MS nos deja utilizar el servicio por unos 120 días. Le damos Next.

11. En la pantalla Select usersGroups allowed Access to this Terminal Server, deberemos especificar los grupos de usuarios que van a tener acceso al Servidor terminal Server, por defecto ya se encuentra asignado el grupo Administrators, en mi caso cree un grupo adicional que se llama TS Gateway. Le damos Next.

12. Podemos instalar un certificado ya para que la conexión RDP vaya cifrada con la capa SSL y sea más segura, si tenemos ya un certificado podemos aprovechar e instalarlo ahora, si no, después, marcamos la choose a certifícate for SSL encryption later. “Next”, la instalación de un certificado será obligatoria posteriormente.

13. Creamos las directivas de autorización para la conexión usando TS Gateway, elegimos Now. Next.

  1. Seleccionamos los grupos de usuarios que se podrán conectar a travez del TS Gateway. Por default ya aparece el grupo de Administrators. Una vez que seleccionamos a los usuarios que se podrán conectar a travez del TS Gateway le damos Next.

  1. Creamos el servidor TS CAP, que permitirá a los usuarios que agregamos anteriormente a validarse por password o smart card o ambos. Lo dejamos como esta y le damos Next.

  1. En la siguiente pantalla, podemos indicar a que computadoras se podrán conectar los usuarios anteriormente que seleccionamos, para este caso elegimos la opción Allow users to connect to any compute ron the network.

En la siguiente pantalla solo le damos Next

  1. En la pantalla de select Roles services también dejamos todo como esta y le damos Next.

  1. En la siguiente pantalla Web Server (IIS) también le damos Next.
  2. En la pantalla de Roles services de IIS también dejamos todo como esta y seleccionamos Next.

  1. En la pantalla de confirmación, nos muestra un resumen de lo que instalara, con algunas advertencias que arreglaremos más adelante. Hacemos Click en el Boton Install.
  2. Una vez finalizada la instalación de todos los componentes nos muestra un resumen y nos advierte que debemos reiniciar el servidor. Hacemos click en close y luego reiniciamos el equipo.

  1. Una vez reiniciado el equipo nos vamos a Start, Administrative Tools, Terminal Services, TS Gateway Manager para terminar de configurar nuestro TS Gateway.
  2. En la consola de TS Gateway Manager, nos paramos sobre nuestro servidor 8KLABAP03, en el panel de la derecha nos informarma un status del servidor, nos aparecerá un error de que no se instalo aun el certificado de seguridad, el cual vamos a instalar ahora.

  1. Hacemos click derecho sobre el servidor 8KLABAP03, propiedades. Nos abrirá la siguiente pantalla, en la solapa General, podemos limitar la cantidad de conexiones simultaneas al TS Gateway, en este caso lo dejamos como viene por default y que permita el máximo de conexiones simultaneas.

  1. En la solapa SSL Certificate, vamos a crear el certificado de seguridad, aquí vamos a hacer click en create a self-signed certifícate for SSL-encryption y luego Create Certificate debido a que aun no tenemos uno.

  1. En certifícate name escribimos el nombre del servidor completo, y en certifícate location le indicamos la ruta donde se va a guardar el certificado para distribuirlo a los usuarios mas adelante, en mi caso como es una prueba lo dejo en Documents.

  1. Le damos ok.

  1. Vamos a la solapa Server Farm, y agregamos al servidor TS Gateway como miembro de la granja. Escribimos el nombre del servidor y luego click en add. Para terminar click en apply y si queremo le damos un refresh status para verificar el status ok del servidor como miembro de la granja.

Bueno con esto ya tendríamos nuestro servidor TS Gateway funcionando solo resta distribuir el certificado a los clientes y realizar una prueba de conexión.

Fuente: http://www.grupoitpro.com.ar/instalando-y-configurando-ts-gateway-y-ts-web-access-part1

Postgres ODBC, Linked server, SQl server Reporting Services connection

Connec to Postgres using ODBC connection follow these steps
1) Download Postgres ODBC driver from http://www.postgresql.org/ftp/odbc/versions/msi/
or a 64-bit postgres ODBC driver from http://code.google.com/p/visionmap/downloads/detail?name=setup_psqlODBC_x64_8.3.4.0.exe&can=2&q=
2) Install above ODBC drivers
3) Create system DSN … On windows XP > Control Panel > Administrative Tools > Data Sources (ODBC)

4) Go to System DSN Tab > Add > Select PostgresSQL Unicode sriver > Finish

5) On Prompted page add

Data Source — Whatever you want to name the DSN

Database – Name of postgres database

Server – Name of Postgres server

Port:5432 (or whaever you database port is..)
Username and Password: Enter postgres database username /password you wish to use
Test the System DSN by clicking on Test
6) Create Linked Server on SQL Server 2008/2005 to connect to postgres database
Start SQL server management studio
Connect to SQL server instance where who wish to create linked server
Go to ServerObject > linked Servers > New Linked Server
Fill the Linked server properties
Select Provider: Microsoft OLEDB Provider for ODBC Drivers
Product name: postgres (you can put whatever u like)
Data Source: Name of System DSN created in last step
OK
Right click Linked Server and Test Connection
Now you query postgres from SSMS by something like
select * from linkedserver_name.database_name.schema_name.Table
7) Creating Connection to Postgres from SQL Server Reporting Services (SSRS)
Create a New data Source
Name : Test
Data Source Type: ODBC
Connection String: Driver={PostgreSQL UNICODE};Server=testserver;Port=5432;Database=Test;Schema=public;
Connect using: Credentials stored securily in report server, eneter user_name, password and Test Connection
If it is 64-bit driver then
Connection string is something like
Driver={PostgreSQL 64-Bit ODBC Drivers};Server=testserver;Port=5432;Database=test;Schema=public;
Using above techniques you can also connect from SSIS (SQL server Integration Services)

Fuente: http://postgresblog.blogspot.com/2010/10/postgres-odbc-linked-server-sql-server.html

Set up PostgreSQL as a linked server in Microsoft SQL Server 32-bit on a Windows Server 2003 64-bit

Environment

  • Machine 1
    IP: 10.2.29.183
    OS: Windows Vista Business SP1
    DB: PostgreSQL 8.3, port: 5432
  • Machine 2
    IP: 10.2.29.18
    OS: Windows Server 2003 Enterprise x64 Edition SP2
    DB: Microsoft SQL Server X86 Standard 9.00.1399.06, port: 4900

Procedures

  • Machine 1
    1. Make sure port 5432 is opened in the firewall.
    2. Make sure Machine 2 is granted access to PostgreSQL. The access config file is at C:\Program Files\PostgreSQL\8.3\data\pg_hba.conf in this case. host all all 10.2.29.18/32 md5 is added to the end of the file.
  • Machine 2
    1. Install 64-Bit OLEDB Provider for ODBC (MSDASQL), the installer file is WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe in this case.
    2. Install 32-bit PostgreSQL ODBC driver, choose the appropriate PostgreSQL version matching the setup (in this case psqlodbc_08_03_xxxx.zip).
    3. In SQL Server Management Studio, create a linked server using the following command:

      EXEC sp_addlinkedserver
      @server = '10.2.29.183',
      @srvproduct = 'PostgreSQL',
      @provider = 'MSDASQL',
      @provstr = 'Driver=PostgreSQL ANSI;uid=postgres;Server=10.2.29.183;Port=5432;database=my_db_name;pwd=my_password';
      EXEC sp_addlinkedsrvlogin
      @rmtsrvname = '10.2.29.183',
      @useself = 'false',
      @rmtuser = 'postgres',
      @rmtpassword = 'my_password';
    4. To test the connection:

      SELECT *
      FROM OPENQUERY([10.2.29.183], 'SELECT * FROM information_schema.tables')

Fuente: http://eddiecjc.blogspot.com/2009/05/set-up-postgresql-as-linked-server-in.html

Driver: http://www.postgresql.org/ftp/odbc/versions/msi/

Instalación y configuración de la plataforma BI de Pentaho.

Con la ultima versión estable de la plataforma BI descargada de la web de Pentaho (la 3.5.2), y siguiendo las instrucciones de Prashant Raju para esta versión en la plataforma Windows utilizando MySql, realizamos la instalación y configuración de nuestro sistema realizando los siguientes pasos:

1) Requisitos previos: maquina virtual Java y la base de datos MySQL (u otra de las soportadas).

Para poder ejecutar la plataforma de BI de Pentaho es necesario disponer de una máquina virtual Java instalada en el equipo donde vamos a trabajar. Pentaho recomienda la versión 1.5 de Sun JRE. Con versiones anteriores no funciona y la 1.6 no esta oficialmente soportada (es la que tengo instalada yo), aunque si funciona.

Para ver la versión instalada, ejecutaremos el comando: java  -version. En el caso de no disponer de la máquina, podemos descargarla en la web de Sun.

A continuación comprobaremos que la variable de entorno JAVA_HOME apunte al directorio donde tenemos instalado Java. Igualmente, la variable PATHtambién debera apuntar al directorio de ejecutables de la instalación de Java. En mi caso, el valor de las variables será el siguiente:

JAVA_HOME   c:\Program Files\Java\jdk1.6.0_17
PATH        c:\Program Files\Java\jdk1.6.0_17\bin;.....

Para configurar las variables, lo realizaremos desde Propiedades del Sistema, Variables de Entorno.

Con respecto a MySQL, en el caso de que no lo tengamos instalado en nuestra máquina, lo descargaremos de la web y realizaremos la instalación según las instrucciones que nos proporcionan en su portal de documentación.

2) Descomprimir los ficheros de la plataforma.

Seleccionamos una carpeta (por ejemplo c:\pentaho), y en ella vamos a descomprimir el fichero Zip que nos hemos bajado de la web. Tras el proceso, tendremos dos carpetas diferenciadas, llamadas administration-console y biserver-ce. La primera carpeta alberga los ficheros de la plataforma de administración, que utilizamos para configurar y administrar el servidor BI (utiliza Jetty). La segunda, es la plataforma de BI propiamente dicha (la que utilizarán los usuarios), que utiliza tomcat.

En este momento, ya podriamos arrancar la plataforma desde los correspondientes scripts que se encuentran en la carpeta c:\pentaho\biserver-ce (start-pentaho.bat para iniciar el servidor y stop-pentaho.bat para pararlo). Este Script arranca en primer lugar la base de datos HSQLDB de ejemplo (donde residen las datos necesarios para el funcionamiento de la plataforma, junto con datos de pruebas para los ejemplos precargados). A continuación, arranca la plataforma de BI, a través del tomcat. Como no queremos trabajar con esa base de datos, sino con MySQL, vamos a proceder a realizar una serie de ajustes antes de arrancar la plataforma.

3) Creación de catalogos en base de datos necesarios para la plataforma.

La plataforma Pentaho necesita dos bases de datos para su funcionamiento (además de la base de datos de test para poder trabajar con el set de ejemplos). Las bases de datos y su cometido son las siguientes:

  • hibernate: esta base de datos almacena la autentificación de usuarios y los datos de autorizaciones, el contenido BI (solution repository) y los origenes de datos disponibles en la plataforma.
  • quartz: es el repositorio para el scheduler Quartz, que es uno de los componentes que forma la plataforma, que nos permite la planificación de procesos dentro del servidor BI.
  • sampledate: contiene las tablas para ilustrar y hacer posible la ejecución de todos los ejemplos por defecto que proporciona la plataforma, para poder hacernos una idea de sus funcionalidades y sus posibilidades de análisis.

Por defecto, los catálogos de estas bases de datos estarán creados en la base de datos HSQLDB que se puede arrancar en la configuración del servidor por defecto.  Para crearlos en MySQL, como es nuestro caso, ejecutaremos los scripts que se encuentran en la carpeta c:\pentaho\biserver-ce\data o bien descargarlos de la web de Prashant Raju. Decido utilizar estos últimos, pues ademas de crear todos los catalogos de tablas, también incluye la carga de datos de ejemplo (paso 5), que es una opción que no incluye la instalación estandar. El orden de ejecución será el siguiente:

mysql> source 1_create_repository_mysql.sql;
 ...output
 mysql> source 2_create_quartz_mysql.sql;
 ...output
 mysql> source 3_create_sample_datasource_mysql.sql;
 ...output
 mysql> source 4_load_sample_users_mysql.sql;
 ...output
 mysql> source 5_sample_data_mysql.sql;
 ...output

La ejecución de los scripts sql la realizaremos desde MySQL Query Browser (la herramienta gráfica para ejecución de sentencias SQL) o bien desde linea de comandos con la utilidad mysql que llevar incluido el servidor MySQL para ejecutar scripts. Podiamos haber utilizado cualquier otro editor sql, comoSQuirreL.

4) Configuracion JDBC, Hibernate and Quartz.

Todas las aplicaciones de Pentaho, incluyendo el Pentaho Server, utilizan la conectividad JDBC (Java Database Connectivity) para la comunicación con las bases de datos. Por tanto, será necesario disponer de los correspondientes conectores según la base de datos que vayamos a utilizar. En nuestro caso, vamos a dejar tanto el conector para MySQL (donde iran las bases de datos de Hibernate y Quartz), como el de Oracle (donde va la base de datos del DW). Las carpetas donde vamos a copiar serán las siguientes:

  • C:\Pentaho\biserver-ce\tomcat\common\lib: ubicación de los drivers JDBC para poder utilizar en el servidor Pentaho la base de datos para la que el conector proporciona conectividad.
  • C:\Pentaho\administration-console\jdbc: es necesario ponerlos aquí también para poder definir correctamente las conexiones a base de datos en la consola de administración.

A continuación, configuraremos los ficheros de parametrización del sistema para que Hibernate y Quartz lean de los catalogos de base de datos en Mysql que hemos creado en el punto 3, en lugar de la base de datos HSQLDB proporcionada por defecto.

  • Configuracion de Hibernate (I): en el fichero applicationContext-spring-security-jdbc.xml (ubicado en la carpeta C:\Pentaho\biserver-ce\pentaho-solutions\system), modificaremos la parte que veis subrayada a continuación, con los valores referidos para utilizar MySQL.
<!--  This is only for Hypersonic. Please  update this section for any other database you are using --> 
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property  name="url"
value="jdbc:mysql://localhost:3306/hibernate"  />
<property  name="username" value="hibuser" />
<property name="password" value="password" />
</bean>
  • Configuracion de Hibernate (II): en el fichero applicationContext-spring-security-hibernate.xml (ubicado en la carpeta C:\Pentaho\biserver-ce\pentaho-solutions\system), modificaremos la parte que veis subrayada a continuación, con los valores referidos para utilizar MySQL.
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.MySQLDialect
  • Configuración de Hibernate (y III): en el fichero hibernate-settings.xml ( ubicado en la carpeta C:\Pentaho\biserver-ce\pentaho-solutions\system\hibernate), modificaremos la parte que veis subrayada a continuación.
<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>

Con la configuración anterior, hemos configurado la seguridad JDBC de la plataforma. Ahora nos falta indicar en los contextos del servidor de aplicación, la ubicación de las bases de datos, para decirle al servidor que lea de las bases de datos en Mysql, utilizando los drivers y la configuración de seguridad realizada anteriormente. Para ello, modificamos el fichero contexts.xml (ubicado en C:\Pentaho\biserver-ce\tomcat\webapps\pentaho\META-INF) de la siguiente manera:

<?xml version="1.0" encoding="UTF-8"?>
 <Context path="/pentaho" docbase="webapps/pentaho/">
 <Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
 factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
 maxWait="10000" username="hibuser" password="password"
 driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
 validationQuery="select 1" />
<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
 factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
 maxWait="10000" username="pentaho_user" password="password"
 driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
 validationQuery="select 1"/>
 </Context>

Con esta configuración ya tendriamos lista la parte de conectividad con la base de datos. Solo en el caso de que no hubieramos utilizado los scripts de Prashant Raju, tendriamos que realizar un último paso, que sería ejecutar la siguiente sentencia SQL para indicarle al servidor que los datos de ejemplo los hemos cambiado de lugar:

UPDATE hibernate.DATASOURCE
 SET DRIVERCLASS = 'com.mysql.jdbc.Driver’,
 URL = 'jdbc:mysql://localhost:3306/sampledata’,
 QUERY = 'SELECT 1’
 WHERE NAME = 'SampleData’
 ;

5) Configuración Servidor Apache-Tomcat.

La plataforma Pentaho utiliza Apache-Tomcat como servidor de aplicaciones para desplegar los servicios que la componen. El servidor lleva una configuración por defecto que podemos modificar (por ejemplo, para variar el puerto donde nos conectamos, para el caso de que haya conflicto con otras aplicaciones instaladas en el servidor), la ubicación html, el lenguaje, etc. Para ello, modificaremos el fichero web.xml que se encuentra en la carpeta C:\Pentaho\biserver-ce\tomcat\webapps\pentaho\WEB-INF. Veamos alguna de la cosas que podemos cambiar.

solution-path

Con este parámetro, le indicamos a la plataforma BI la ubicación de la carpeta pentaho-solutions. Por defecto, tiene el valor c:\biserver-ce\.

En nuestro caso, vamos a cambiar el valor para que apunte a la carpeta donde hemos instalado:

<context-param><param-name>solution-path</param-name>
 <param-value>C:\Pentaho\biserver-ce\pentaho-solutions</param-value>
 </context-param>

base-url

Al instalar, la ruta URL por defecto para acceder a la plataforma será la siguiente: http://localhost:8080/pentaho

Podemos cambiarla si lo desamos modificando el parmetro base_url dentro del mismo fichero. En nuestro caso, como vamos a cambiar el puerto por defecto, modificamos su valor indicando lo siguiente:

<context-param>
 <param-name>base-url</param-name>
 <param-value>http://localhost:9999/pentaho/</param-value>
 </context-param>

Esto nos obligará a cambiar tambien la configuración del fichero server.xml, que veremos mas adelante.

port

En la ruta C:\Pentaho\biserver-ce\tomcat\conf, tenemos el fichero server.xml, donde podemos modificar el puerto por defecto de nuestro servidor BI (que es el 8080).

<!-- Define a non-SSL HTTP/1.1 Connector on port 8080 -->
 <Connector port="9999" maxHttpHeaderSize="8192"
 maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
 enableLookups="false" redirectPort="8443" acceptCount="100"
 connectionTimeout="20000" disableUploadTimeout="true" />

Ahora pararemos el servidor tomcat y al arrancar la nueva URL de acceso será la siguiente: http://localhost:9999/pentaho

6) Otros elementos. Scripts arranque. Configuración de la publicación de contenidos y del correo SMTP.

Antes de continuar, vamos a ajustar el script de arranque de la plataforma BI, omitiendo la parte de arranque de la base de datos HSQLDB, que por defecto se arranca cuando lanzamos el script start-pentaho.bat (de la carpeta c:\pentaho\biserver-ce). Es tan sencillo como comentar la linea donde se arranca la base de datos. El script quedaría como sigue (la linea subrayada se ha comentado para que no se ejecute):

@echo off
setlocal
cscript promptuser.js //nologo //e:jscript
rem errorlevel 0 means user chose "no"
if %errorlevel%==0 goto quit
echo WScript.Quit(1); > promptuser.js

if exist "%~dp0jre" call "%~dp0set-pentaho-java.bat" "%~dp0jre"
if not exist "%~dp0jre" call "%~dp0set-pentaho-java.bat"

cd data
rem start start_hypersonic.bat
cd ..\tomcat\bin
set CATALINA_HOME=%~dp0tomcat
set CATALINA_OPTS=-Xms256m -Xmx768m -XX:MaxPermSize=256m -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000
set JAVA_HOME=%_PENTAHO_JAVA_HOME%
call startup
:quit
endlocal

Ademas de toda la configuración realizada hasta ahora, nos quedan por configurar dos aspectos importantes para el funcionamiento del sistema:

  • Publicación de contenido: por defecto, la publicación de contenido en el servidor BI esta desactivada, por lo que para publicar los informes o análisis que vayamos realizando, lo deberiamos de hacer dejando los ficheros en la correspondientes carpetas del servidor. Pero es mas fácil hacerlo mediante lo que se llama publicación (que veremos en detalle mas adelante). Para habilitar la publicación, modificaremos el ficheropublisher_config.xml, que se encuentra en la carpeta C:\Pentaho\biserver-ce\pentaho-solutions\system. Ahí indicaremos la contraseña de publicación. Por defecto, no tiene ninguna contraseña, y por tanto, no esta habilitada la publicación.
<publisher-config>
 <publisher-password>passpublic</publisher-password>
</publisher-config>
  • Servicio de correo SMTP: como ultimo paso, configuraremos la posibilidad de envio de correo electrónico, a través de un servidor externo (ya que la plataforma no dispone de un servidor de correo electrónico propio). Para ello, configuraremos el fichero email-config.xml en el directorio C:\Pentaho\biserver-ce\pentaho-solutions\system\smtp-email, de la siguiente manera:
<email-smtp>
 <properties>
 <mail.smtp.host>smtp.gmail.com</mail.smtp.host>
 <mail.smtp.port>587</mail.smtp.port>
 <mail.transport.protocol>smtps</mail.transport.protocol>
 <mail.smtp.starttls.enable>true</mail.smtp.starttls.enable>
 <mail.smtp.auth>true</mail.smtp.auth>
 <mail.smtp.ssl>true</mail.smtp.ssl>
 <mail.smtp.quitwait>false</mail.smtp.quitwait>
 </properties>
 <mail.pop3></mail.pop3>
 <mail.from.default>respinosamilla@gmail.com</mail.from.default>
 <mail.userid>respinosamilla@gmail.com</mail.userid>
 <mail.password>password</mail.password>
</email-smtp>

En este caso, estoy utilizando gmail para enviar los correos desde la plataforma. Los valores subrayados son los que yo he indicado. En el caso de estar utilizando otro servidor de correo, tendreís que modificar la configuración de servidor, puertos, tipo de conexión, ect, para que funcione según la configuración de este. Con esta funcionalidad habilitamos la distribución de contenido a través del correo electrónico (por ejemplo, para el envío de la ejecución de informes o análisis).

En este momento, ya podemos arrancar la plataforma. Al iniciarla, y conectarnos en el puerto http://localhost:9999, nos aparece la consola de usuario, con una configuración por defecto. Tendría el siguiente aspecto.

Consola de Usuario por defecto

Ya podemos conectarnos con alguno de los usuarios existentes y trastear con el proyecto de ejemplo Steel Wheels o la colección de muestras y ejemplos que incluye la plataforma. Con ellos nos podemos hacer una idea de las posibilidades de análisis de las que vamos a disponer.

Personalizando la plataforma de usuario.

Como queremos personalizar el portal, vamos a realizar algunos cambios en la consola de usuario (también llamada Mantle). Para ello, vamos a utilizar el blog de Prashant Raju donde nos explica muy bien los pasos a seguir para configurar nuestra plataforma. Esta personalización va a consistir en lo siguiente:

No queremos que aparezcan los usuarios de ejemplo al conectarnos al sistema.

Con la configuración por defecto del sistema, cuando entramos al portal de usuario, nos aparece la siguiente ventana:

Aparecen los usuarios de ejemplo, y al seleccionarlos podemos entrar directamente en la plataforma (sin necesidad de recordad su nombre de usuario o contraseña), ya que el sistema nos lo recuerda. Esto no es operativo para un sistema productivo, y por tanto, vamos a modificarlo. Para ello, modificaremos el fichero loginsettings.properties (ubicando en la carpeta C:\Pentaho\biserver-ce\tomcat\webapps\pentaho\mantleLogin). La configuración por defecto del fichero es la siguiente:

# this file contains settings to configure the login dialog
# flag to turn on/off show users list (overrides pentaho.xml)
#showUsersList=true
# launch PUC in new window (default setting)
openInNewWindow=false
# sample users (be sure that each group has the same # of items as the rest)
userIds=joe, suzy, pat, tiffany
userDisplayNames=Joe (admin), Suzy, Pat, Tiffany
userPasswords=password, password, password, password

Vamos a modificar los valores de la siguiente manera:

# this file contains settings to configure the login dialog
# flag to  turn on/off show users list (overrides pentaho.xml)
showUsersList=false

Reiniciamos el servidor y al entrar en el portal, el aspecto de login habrá variado, apareciendo la siguiente pantalla:

Este login es mas acorde con un sistema donde hay que mantener la seguridad.

Ventana de conexión personalizada para nuestra empresa.

Para modificar el aspecto de la ventana de login, hemos de modificar el fichero PUC_login.jsp que se encuentra en la carpeta C:\Pentaho\biserver-ce\tomcat\webapps\pentaho\jsp. En este fichero hemos modificado textos, alguna de las imagenes que aparecen, hasta conseguir el siguiente aspecto:


Esto es solo un ejemplo sencillo de como podemos ajustar el diseño de la página a las necesidades corporativas de una empresa (logos, infografia, etc). Os dejo el link al fichero PUC_login.jsp modificado.

Configuración de mensajes de login y de mensajes de error.

Para modificar los mensajes de usuario en el momento del login, habrá que modificar el fichero MantleLoginMessages_es.PROPERTIES (para el caso del idioma castellano, o el fichero MantleLoginMessages_en.PROPERTIES en el caso de estar trabajando con el ingles). El fichero se encuentra en dos ubicaciones distintas y habra que modificarlo en ambos casos para que siempre salgan los mismos mensajes. Las ubicaciones son las siguientes:

  • C:\Pentaho\biserver-ce\tomcat\webapps\pentaho\mantleLogin\messages
  • C:\Pentaho\biserver-ce\tomcat\webapps\pentaho\mantle\messages

Cambiaremos los textos de los mensajes, y al grabar el fichero automaticamente seran utilizados por el servidor con los nuevos valores.

Personalización del panel de control y del area de trabajo.

Se pueden personalizar muchisimos aspectos de la consola de usuario (area de trabajo), tal y como nos cuenta Prashant Raju en su blog, desde los logotipos, barras de menu, barra de herramientas, colores, etc. En nuestro ejemplo, vamos a modificar el fichero launch.jsp (ubicado en C:\Pentaho\biserver-ce\tomcat\webapps\pentaho\mantle\launch). En el ejemplo, he modificado el fichero para que en la parte de la derecha aparezca mi blog a los usuarios de la plataforma, en el momento de conectarse. El resultado es el siguiente:

Workspace personalizado

Este es solo un ejemplo sencillo de lo que se puede personalizar, que puede ser casi todo (hasta el código fuente si fuese necesario).

Con todos los elementos que hemos configurado, la plataforma de BI de Pentaho esta preparada y lista para ser utilizada, y ademas personalizada a nuestro gusto o necesidades. A continuación vamos a ir viendo las diferentes herramientas que nos proporciona Pentaho para construir nuestros análisis y la forma de configurar su ejecución dentro de la plataforma BI de Pentaho. Además realizaremos la configuración del metadata y la definición de los cubos Olap que luego nos permitiran realizar los análisis dimensionales.

Fuente: http://churriwifi.wordpress.com/2010/06/20/17-1-instalacion-y-configuracion-de-la-plataforma-bi-de-pentaho/

Conexión (ODBC y JDBC) con M$ SQL Server 2000

Aclaración: este tutorial fue publicado inicialmente en el grupo Pentaho.

Buenas.
Debido a preguntas concurrentes acerca de este tema y el de las conexiones en general, conjuntamente con mi colega Ing. Mariano García Mattío hemos realizado un pequeño manual, en el que explicaremos mediante ejemplos gráficos como establecer desde Pentaho Data Integration 3.2.0 stable conexiones ODBC y JDBC con M$ SQL Server 2000.
SQL Server Enterprise Manager:

Administrador de servicios de SQL Server:

Conexión ODBC
1) Establecer orígen de datos ODBC:
* Ir a Panel de control → Herramientas Administrativas → Orígenes de datos (ODBC)
* Seleccionar la pestaña “DNS de Sistema” y presionar el botón “Agregar…”.
* Seleccionar el controlador “SQL Server” y presionar el botón “Finalizar”.
* Completar la ficha para la creación del origen de datos:
** Nombre: datawarehouseODBC
** Descripción: Conexión ODBC a datawarehouse
** Servidor: TGX
* Una vez completada la ficha presionar el botón “Siguiente >”.
* Completar la información de esta pantalla de acuerdo lo crean conveniente y presionar el botón “Siguiente >”.
* Tildar la opción “Establecer la siguiente base de datos como predeterminada” y seleccionar “datawarehouse”. Presionar el botón “Siguiente >”.
* Completar la información de esta pantalla de acuerdo lo crean conveniente y presionar el botón “Finalizar”.
Finalmente deberíamos tener algo como esto:

2) Establecer conexión ODBC en Pentaho Data Integration 3.2.0 stable:
* En la “Transformación” o “Trabajo” que estemos utilzando, ir a la pestaña “View”:

* Presionar el botón derecho sobre “Conexiones a base de datos” y seleccionar “Nuevo”.
* A continuación llenaremos solo los campos necesarios para establecer la conexión, es decir, los campos de la pestaña “General”:
** Connection Name: Conexión ODBC a datawarehouse
** Connection Type: MS SQL Server
** Access: ODBC
** ODBC DNS Source Name: datawarehouseODBC

* Es conveniente presionar el botón “Probar” para asegurarnos de la conexión es correcta.
* Finalmente presionar “OK”.
Conexión JDBC
1) Configurar Driver JDBC:
* Copiar el driver “sqljdbc.jar” en “[data-integration_home]\libext\JDBC”
* Reiniciar Pentaho Data Integration si es que ya estaba ejecutándose.
2) Habilitar la autentificación estándar en M$ SQL Server 2000:
Este paso es llevado a cabo para que cuando intentemos establecer la conexión con la base de datos no nos arroje el siguiente error: Error de inicio de sesión del usuario ‘sa’. Motivo: no está asociado a una conexión de SQL Server de confianza.
* Abrir SQL Server Enterprise Manager.
* Botón derecho sobre el servidor, en nuestro caso: (local)(Windows NT).
* Seleccionar la opción “Propiedades”.
* Ir a la pestaña “Seguridad” y seleccionar la opción “SQL Server y Windows”.
* Presionar el botón “Aceptar” y reiniciar el servicio.
3) Establecer conexión JDBC en Pentaho Data Integration 3.2.0 stable:
*En la “Transformación” o “Trabajo” que estemos utilzando, ir a la pestaña “View”:

* Presionar el botón derecho sobre “Conexiones a base de datos” y seleccionar “Nuevo”.
* A continuación llenaremos solo los campos necesarios para establecer la conexión, es decir, los campos de la pestaña “General”:
** Connection Name: Conexión JDBC a datawarehouse
** Connection Type: Generic database
** Access: Natie (JDBC)
** Custom Connection URL: jdbc:sqlserver://localhost:1433;databasename=datawarehouse;responseBuffering=adaptive
** Custom Driver Class Name: com.microsoft.sqlserver.jdbc.SQLServerDriver
** User Name: sa
** Password: sa

* Es conveniente presionar el botón “Probar” para asegurarnos de la conexión es correcta.
* Finalmente presionar “OK”.
Esperamos les sea útil.
Saludos.

Fuente: http://www.redopenbi.com/group/pentahodataintegration/forum/topics/tutorial-06-conexion-odbc-y

How To Install PgAgent On Windows (PostgreSQL Job Scheduler)

This is a surprise for me that PostgreSQL do not have any build-in job scheduler. However It did pack into pgAdmin source but i wonder why it didnt intsall it by default? Here i provide some steps to show how to install pgAgent(Job Scheduler) on windows as services

1) Go to your PgAdim file path like below, please change to your own pgAdmin file path like “C:\Program Files\PostgreSQL\8.2\pgAdmin III”

pgagent-install-step1

2) Get pgAgent.sql and execute the script, it will create all pgAgent core tables

pgagent-install-step2

3) After executed pgAgent script, user will notice one job icon appear at pgAdmin.

pgagent-install-step3

4) Job scheduler is still not working yet, you have to register it as services in windows. Please issue following command on command prompt

C:\Program Files\PostgreSQL\8.2\bin\pgAgent INSTALL pgAgent -u postgres 
-p secret hostaddr=127.0.0.1 dbname=newdb user=postgres password=secret

please issue C:\Program Files\PostgreSQL\8.2\bin\pgAgent to show usage of pgAgent, where
-u = username
-p = password
“hostaddr=127.0.0.1 dbname=newdb user=postgres password=secret” = connect string

P.S please notice INSTALL is all uppercase, do not type lowercase , it will not work.

5) After registered service on windows, just go windows service to start it or use net start command.

pgagent-install-step4

6) Done, we can start to schedule our job now.

Fuente: http://www.mkyong.com/database/how-to-install-pgagent-on-windows-postgresql-job-scheduler/

OpenI4Pentaho

Estimados,
estuve probando hace un tiempito OpenI, en su versión Plugin de Pentaho. Es muy simple la instalación, solo copiar la carpeta openi-pentaho-plugin-3.0.1.zip/openi en pentaho-solutions/system y la carpeta openi-pentaho-plugin-3.0.1.zip/openi/openi-sample dentro de pentaho-solutions. Iniciar el bi-server y listo. Una buena opción para reemplazar el viejo pero genial JPivot. Lo descargan de aquí.
Les adjunto una screenshots.

Fuente:http://jmagm.blogspot.com/

9.9. Date/Time Functions and Operators

Table 9-26 shows the available functions for date/time value processing, with details appearing in the following subsections. Table 9-25 illustrates the behaviors of the basic arithmetic operators (+, *, etc.). For formatting functions, refer to Section 9.8. You should be familiar with the background information on date/time data types from Section 8.5.

All the functions and operators described below that take time or timestamp inputs actually come in two variants: one that takes time with time zone or timestamp with time zone, and one that takes time without time zone or timestamp without time zone. For brevity, these variants are not shown separately. Also, the + and * operators come in commutative pairs (for example both date + integer and integer + date); we show only one of each such pair.

Table 9-25. Date/Time Operators

Operator
Example
Result

+
date '2001-09-28' + integer '7'
date '2001-10-05'

+
date '2001-09-28' + interval '1 hour'
timestamp '2001-09-28 01:00:00'

+
date '2001-09-28' + time '03:00'
timestamp '2001-09-28 03:00:00'

+
interval '1 day' + interval '1 hour'
interval '1 day 01:00:00'

+
timestamp '2001-09-28 01:00' + interval '23 hours'
timestamp '2001-09-29 00:00:00'

+
time '01:00' + interval '3 hours'
time '04:00:00'

-
- interval '23 hours'
interval '-23:00:00'

-
date '2001-10-01' - date '2001-09-28'
integer '3'

-
date '2001-10-01' - integer '7'
date '2001-09-24'

-
date '2001-09-28' - interval '1 hour'
timestamp '2001-09-27 23:00:00'

-
time '05:00' - time '03:00'
interval '02:00:00'

-
time '05:00' - interval '2 hours'
time '03:00:00'

-
timestamp '2001-09-28 23:00' - interval '23 hours'
timestamp '2001-09-28 00:00:00'

-
interval '1 day' - interval '1 hour'
interval '1 day -01:00:00'

-
timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'
interval '1 day 15:00:00'

*
900 * interval '1 second'
interval '00:15:00'

*
21 * interval '1 day'
interval '21 days'

*
double precision '3.5' * interval '1 hour'
interval '03:30:00'

/
interval '1 hour' / double precision '1.5'
interval '00:40:00'

Table 9-26. Date/Time Functions

Function
Return Type
Description
Example
Result

age(timestamp,timestamp)
interval
Subtract arguments, producing a”symbolic” result that uses years and months
age(timestamp '2001-04-10', timestamp '1957-06-13')
43 years 9 mons 27 days

age(timestamp)
interval
Subtract from current_date
age(timestamp '1957-06-13')
43 years 8 mons 3 days

current_date
date
Today’s date; see Section 9.9.4

current_time
time with time zone
Time of day; see Section 9.9.4

current_timestamp
timestamp with time zone
Date and time; see Section 9.9.4

date_part(text,timestamp)
double precision
Get subfield (equivalent to extract); seeSection 9.9.1
date_part('hour', timestamp '2001-02-16 20:38:40')
20

date_part(text,interval)
double precision
Get subfield (equivalent to extract); seeSection 9.9.1
date_part('month', interval '2 years 3 months')
3

date_trunc(text,timestamp)
timestamp
Truncate to specified precision; see alsoSection 9.9.2
date_trunc('hour', timestamp '2001-02-16 20:38:40')
2001-02-16 20:00:00

extract(field fromtimestamp)
double precision
Get subfield; see Section 9.9.1
extract(hour from timestamp '2001-02-16 20:38:40')
20

extract(field frominterval)
double precision
Get subfield; see Section 9.9.1
extract(month from interval '2 years 3 months')
3

isfinite(timestamp)
boolean
Test for finite time stamp (not equal to infinity)
isfinite(timestamp '2001-02-16 21:28:30')
true

isfinite(interval)
boolean
Test for finite interval
isfinite(interval '4 hours')
true

justify_hours(interval)
interval
Adjust interval so 24-hour time periods are represented as days
justify_hours(interval '24 hours')
1 day

justify_days(interval)
interval
Adjust interval so 30-day time periods are represented as months
justify_days(interval '30 days')
1 month

localtime
time
Time of day; see Section 9.9.4

localtimestamp
timestamp
Date and time; see Section 9.9.4

now()
timestamp with time zone
Current date and time (equivalent tocurrent_timestamp); see Section 9.9.4

timeofday()
text
Current date and time; see Section 9.9.4

If you are using both justify_hours and justify_days, it is best to use justify_hours first so any additional days will be included in the justify_days calculation.

In addition to these functions, the SQL OVERLAPS operator is supported:

(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)

This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or time stamp followed by an interval.

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: false

When adding an interval value to (or subtracting an interval value from) a timestamp with time zone value, the days component advances (or decrements) the date of the timestamp with time zone by the indicated number of days. Across daylight saving time changes (with the session time zone set to a time zone that recognizes DST), this means interval '1 day' does not necessarily equal interval '24 hours'. For example, with the session time zone set to CST7CDT,timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' will produce timestamp with time zone '2005-04-03 12:00-06', while adding interval '24 hours' to the same initial timestamp with time zone produces timestamp with time zone '2005-04-03 13:00-06', as there is a change in daylight saving time at 2005-04-03 02:00 in time zone CST7CDT.

9.9.1. EXTRACT, date_part

EXTRACT(field FROM source)

The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, time, or interval. (Expressions of type date will be cast to timestamp and can therefore be used as well.) field is an identifier or string that selects what field to extract from the source value. The extract function returns values of type double precision. The following are valid field names:

century

The century

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
Result: 20
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 21

The first century starts at 0001-01-01 00:00:00 AD, although they did not know it at the time. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from -1 to 1. If you disagree with this, please write your complaint to: Pope, Cathedral Saint-Peter of Roma, Vatican.

PostgreSQL releases before 8.0 did not follow the conventional numbering of centuries, but just returned the year field divided by 100.

day

The day (of the month) field (1 – 31)

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 16
decade

The year field divided by 10

SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 200
dow

The day of the week (0 – 6; Sunday is 0) (for timestamp values only)

SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 5

Note that extract‘s day of the week numbering is different from that of the to_char function.

doy

The day of the year (1 – 365/366) (for timestamp values only)

SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 47
epoch

For date and timestamp values, the number of seconds since 1970-01-01 00:00:00-00 (can be negative); for intervalvalues, the total number of seconds in the interval

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');
Result: 982384720

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 442800

Here is how you can convert an epoch value back to a time stamp:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';
hour

The hour field (0 – 23)

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 20
microseconds

The seconds field, including fractional parts, multiplied by 1 000 000. Note that this includes full seconds.

SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
Result: 28500000
millennium

The millennium

SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 3

Years in the 1900s are in the second millennium. The third millennium starts January 1, 2001.

PostgreSQL releases before 8.0 did not follow the conventional numbering of millennia, but just returned the year field divided by 1000.

milliseconds

The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.

SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
Result: 28500
minute

The minutes field (0 – 59)

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 38
month

For timestamp values, the number of the month within the year (1 – 12) ; for interval values the number of months, modulo 12 (0 – 11)

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 2

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
Result: 3

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
Result: 1
quarter

The quarter of the year (1 – 4) that the day is in (for timestamp values only)

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 1
second

The seconds field, including fractional parts (0 – 59[1])

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 40

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
Result: 28.5
timezone

The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC.

timezone_hour

The hour component of the time zone offset

timezone_minute

The minute component of the time zone offset

week

The number of the week of the year that the day is in. By definition (ISO 8601), the first week of a year contains January 4 of that year. (The ISO-8601 week starts on Monday.) In other words, the first Thursday of a year is in week 1 of that year. (for timestamp values only)

Because of this, it is possible for early January dates to be part of the 52nd or 53rd week of the previous year. For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005.

SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 7
year

The year field. Keep in mind there is no 0 AD, so subtracting BC years from AD years should be done with care.

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 2001

The extract function is primarily intended for computational processing. For formatting date/time values for display, seeSection 9.8.

The date_part function is modeled on the traditional Ingres equivalent to the SQL-standard function extract:

date_part('field', source)

Note that here the field parameter needs to be a string value, not a name. The valid field names for date_part are the same as for extract.

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Result: 16

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Result: 4

9.9.2. date_trunc

The function date_trunc is conceptually similar to the trunc function for numbers.

date_trunc('field', source)

source is a value expression of type timestamp or interval. (Values of type date and time are cast automatically, totimestamp or interval respectively.) field selects to which precision to truncate the input value. The return value is of type timestamp or interval with all fields that are less significant than the selected one set to zero (or one, for day and month).

Valid values for field are:

microseconds

milliseconds

second

minute

hour

day

week

month

quarter

year

decade

century

millennium

Examples:

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00

9.9.3. AT TIME ZONE

The AT TIME ZONE construct allows conversions of time stamps to different time zones. Table 9-27 shows its variants.

Table 9-27. AT TIME ZONE Variants

Expression
Return Type
Description

timestamp without time zone AT TIME ZONE zone
timestamp with time zone
Treat given time stamp without time zone as located in the specified time zone

timestamp with time zone AT TIME ZONEzone
timestamp without time zone
Convert given time stamp with time zone to the new time zone

time with time zone AT TIME ZONE zone
time with time zone
Convert given time with time zone to the new time zone

In these expressions, the desired time zone zone can be specified either as a text string (e.g., 'PST') or as an interval (e.g., INTERVAL '-08:00'). In the text case, the available zone names are those shown in either Table B-6 or Table B-4.

Examples (supposing that the local time zone is PST8PDT):

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
Result: 2001-02-16 19:38:40-08

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40

The first example takes a time stamp without time zone and interprets it as MST time (UTC-7), which is then converted to PST (UTC-8) for display. The second example takes a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).

The function timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone.

9.9.4. Current Date/Time

The following functions are available to obtain the current date and/or time:

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME (precision)
CURRENT_TIMESTAMP (precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME (precision)
LOCALTIMESTAMP (precision)

CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone.

CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIMESTAMP can optionally be given a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.

Note: Prior to PostgreSQL 7.2, the precision parameters were unimplemented, and the result was always given in integer seconds.

Some examples:

SELECT CURRENT_TIME;
Result: 14:39:53.662522-05

SELECT CURRENT_DATE;
Result: 2001-12-23

SELECT CURRENT_TIMESTAMP;
Result: 2001-12-23 14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
Result: 2001-12-23 14:39:53.66-05

SELECT LOCALTIMESTAMP;
Result: 2001-12-23 14:39:53.662522

The function now() is the traditional PostgreSQL equivalent to CURRENT_TIMESTAMP.

It is important to know that CURRENT_TIMESTAMP and related functions return the start time of the current transaction; their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp.

Note: Other database systems may advance these values more frequently.

There is also the function timeofday() which returns the wall-clock time and advances during transactions. For historical reasons timeofday() returns a text string rather than a timestamp value:

SELECT timeofday();
Result: Sat Feb 17 19:07:32.000126 2001 EST

All the date/time data types also accept the special literal value now to specify the current date and time. Thus, the following three all return the same result:

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';  -- incorrect for use with DEFAULT

Tip: You do not want to use the third form when specifying a DEFAULT clause while creating a table. The system will convert now to a timestamp as soon as the constant is parsed, so that when the default value is needed, the time of the table creation would be used! The first two forms will not be evaluated until the default value is used, because they are function calls. Thus they will give the desired behavior of defaulting to the time of row insertion.

 

Fuente: http://www.digipedia.pl/pgsql/8.1/functions-datetime.html

This file emulates some common MS SQL Server functions in PostgreSQL

-- maps getdate() to now()
create or replace function getdate() returns timestamptz as '
begin
return now();
end;
' language 'plpgsql';

-- maps isnull() to coalesce()
create or replace function isnull(anyelement, anyelement) returns anyelement as '
begin
return coalesce($1,$2);
end;
' language 'plpgsql' immutable;

-- This allows the use of "+" when joining strings.
create or replace function strcat(text, text) returns text as '
begin
return $1 || $2;
end;
' language 'plpgsql' immutable;
create operator + (procedure = strcat, leftarg = text, rightarg = text);

-- these simulate day, month, and year functions in T-SQL
-- day function for each date/time type.
create or replace function day(timestamptz) returns int as '
begin
return extract(day from $1);
end;
' language 'plpgsql' immutable;

create or replace function day(timestamp) returns int as '
begin
return extract(day from $1);
end;
' language 'plpgsql' immutable;

-- month function for each date/time type.
create or replace function month(timestamptz) returns int as '
begin
return extract(month from $1);
end;
' language 'plpgsql' immutable;

create or replace function month(timestamp) returns int as '
begin
return extract(month from $1);
end;
' language 'plpgsql' immutable;

-- year function for each date/time type.
create or replace function year(timestamptz) returns int as '
begin
return extract(year from $1);
end;
' language 'plpgsql' immutable;

create or replace function year(timestamp) returns int as '
begin
return extract(year from $1);
end;
' language 'plpgsql' immutable;

-- emulate ms sql string functions.
create or replace function space(integer) returns text as '
begin
return repeat('' '', $1);
end;
' language 'plpgsql' immutable;

create or replace function charindex(text, text) returns int as '
begin
return position($1 in $2);
end;
' language 'plpgsql';

create or replace function len(text) returns int as '
begin
return char_length($1);
end;
' language 'plpgsql';

create or replace function left(text, int) returns text as '
begin
return substr($1, 0, $2);
end;
' language 'plpgsql';

-- a function to allow mailing. It is currently a wrapper that allows
-- this functionality to be added later.
-- create or replace function xp_sendmail(tofield text, message text, subject text) returns int as '
-- declare
-- begin
-- return 0;
-- end;
-- ' language 'plpgsql';

-- these functions provide casts from timestamps to ints (# of days). Must be created as super-user.
create or replace function mscomp_int4(interval) returns int4 as '
begin
return extract(day from $1);
end;
' language 'plpgsql' immutable;

create cast (interval as int4) with function mscomp_int4(interval);
create or replace function mscomp_int4(timestamptz) returns int4 as '
begin
return mscomp_int4($1 - ''1/1/1900'');
end;
' language 'plpgsql' immutable;

create cast (timestamptz as int4) with function mscomp_int4
(timestamptz);
create or replace function mscomp_int4(timestamp) returns int4 as '
begin
return mscomp_int4($1 - ''1/1/1900'');
end;
' language 'plpgsql' immutable;

create cast (timestamp as int4) with function mscomp_int4(timestamp);

create or replace function mscomp_float(interval) returns float as '
begin
return (extract(epoch from $1) / 86400);
end;
' language 'plpgsql' immutable;

create cast (interval as float) with function mscomp_float(interval);

create or replace function mscomp_float(timestamptz) returns float as '
begin
return mscomp_float($1 - ''1/1/1900'');
end;
' language 'plpgsql' immutable;

create cast (timestamptz as float) with function mscomp_float(timestamptz);

create or replace function mscomp_float(timestamp) returns float as '
begin
return mscomp_float($1 - ''1/1/1900'');
end;
' language 'plpgsql' immutable;

create cast (timestamp as float) with function mscomp_float(timestamp);
Fuente: http://powergres.sraoss.co.jp/s/ja/tech/plus/experience/vol11/samples/mssqlcomp.sql

Postgres – Habilitar dblink no windows

Para quem já utilizou o linked server no sql server, um dia pode se deparar com a necessidade de criar um link com um servidor remoto e executar uma consulta em um banco remoto.

Pensando nisso, vou demonstrar aqui como criar o linkedserver do postgresql, neste caso chamado de dblink.

O postgres traz nos seus arquivos um chamado dblink.sql, no caso do windows, você pode executar este script que assim então estará liberadas as funções/store procedures do dblink.

O script deve estar em:

  • Versão 9.0 : C:\Program Files\PostgreSQL\9.0\share\contrib

Caso não encontre o script é esse:

-- dblink_connect now restricts non-superusers to password
-- authenticated connections
CREATE OR REPLACE FUNCTION dblink_connect (text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_connect (text, text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT;

-- dblink_connect_u allows non-superusers to use
-- non-password authenticated connections, but initially
-- privileges are revoked from public
CREATE OR REPLACE FUNCTION dblink_connect_u (text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT SECURITY DEFINER;

CREATE OR REPLACE FUNCTION dblink_connect_u (text, text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT SECURITY DEFINER;

REVOKE ALL ON FUNCTION dblink_connect_u (text) FROM public;
REVOKE ALL ON FUNCTION dblink_connect_u (text, text) FROM public;

CREATE OR REPLACE FUNCTION dblink_disconnect ()
RETURNS text
AS '$libdir/dblink','dblink_disconnect'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_disconnect (text)
RETURNS text
AS '$libdir/dblink','dblink_disconnect'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_open (text, text)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_open (text, text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_open (text, text, text)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_open (text, text, text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_fetch (text, int)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_fetch (text, int, boolean)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_fetch (text, text, int)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_fetch (text, text, int, boolean)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_close (text)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_close (text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_close (text, text)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_close (text, text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink (text, text)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink (text, text, boolean)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink (text)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink (text, boolean)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_exec (text, text)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_exec (text, text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_exec (text)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_exec (text,boolean)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE C STRICT;

CREATE TYPE dblink_pkey_results AS (position int, colname text);

CREATE OR REPLACE FUNCTION dblink_get_pkey (text)
RETURNS setof dblink_pkey_results
AS '$libdir/dblink','dblink_get_pkey'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_build_sql_insert (text, int2vector, int, _text, _text)
RETURNS text
AS '$libdir/dblink','dblink_build_sql_insert'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_build_sql_delete (text, int2vector, int, _text)
RETURNS text
AS '$libdir/dblink','dblink_build_sql_delete'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_build_sql_update (text, int2vector, int, _text, _text)
RETURNS text
AS '$libdir/dblink','dblink_build_sql_update'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_current_query ()
RETURNS text
AS '$libdir/dblink','dblink_current_query'
LANGUAGE C;

CREATE OR REPLACE FUNCTION dblink_send_query(text, text)
RETURNS int4
AS '$libdir/dblink', 'dblink_send_query'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_is_busy(text)
RETURNS int4
AS '$libdir/dblink', 'dblink_is_busy'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_get_result(text)
RETURNS SETOF record
AS '$libdir/dblink', 'dblink_get_result'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_get_result(text, bool)
RETURNS SETOF record
AS '$libdir/dblink', 'dblink_get_result'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_get_connections()
RETURNS text[]
AS '$libdir/dblink', 'dblink_get_connections'
LANGUAGE C;

CREATE OR REPLACE FUNCTION dblink_cancel_query(text)
RETURNS text
AS '$libdir/dblink', 'dblink_cancel_query'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_error_message(text)
RETURNS text
AS '$libdir/dblink', 'dblink_error_message'
LANGUAGE C STRICT;

Cole-o no seu editor e execute.

Se executar com sucesso, seu dblink está ativado.

Alguns exemplos de como utilizar:

SELECT tab01.codigo,tab02.codigo
FROM tabela01 tab01
INNER JOIN (SELECT * FROM dblink(‘conexao’,'SELECT codigo FROM tabela02') AS resultado(codigo integer)) tab02 ON tab01.codigo=tab02.codigo;

---------------

select *
from dblink
   (
    'dbname=nomeDoSeuBanco
     hostaddr=10.0.0.11
     user=postgres
     password=postgrepwd
     port=5432',

    'select id
     from suaTabela
    '
   ) as resultado(idRemoto integer); 

--------------

select *
from dblink
   (
    'dbname=nomeBanco
     hostaddr=10.0.0.11
     user=postgres
     password=senha
     port=5432',

    'select *
     from baixa_pv_volume
    '
   ) as t1(idRemoto integer,pvRemoto varchar(10), itemRemoto varchar(10),volumeRemoto integer,
	   data_geracaoRemoto timestamp, usuario_idRemoto integer, deletadoRemoto boolean,
	   empresa_idRemoto varchar(2)); 

-------------

select remota.deletadoRemoto,local.deletado,*
from tabelaLocal local
inner join (select * from dblink('dbname=nomeBanco hostaddr=10.0.0.11 user=postgres password=senha
		port=5432', 'select * from tabela' ) as resultado(idRemoto integer,pvRemoto varchar(10), itemRemoto varchar(10),volumeRemoto integer,
	   data_geracaoRemoto timestamp, usuario_idRemoto integer, deletadoRemoto boolean,
	   empresa_idRemoto varchar(2))) remota on remota.idRemoto = local.id
where remota.deletadoRemoto <> local.deletado

Caso deseje habilitar no línux:

O primeiro passo é instalar o DBLink no banco. Aqui vou considerar que a instalação do PostgreSQL foi realizada de forma compilada e para isso será necessário fazer uso dos arquivos da instalação. Dentro do diretório existe um diretório denominado contrib e dentro deste diretório existe um subdiretório chamado dblink. Para instalar o dblink é necessário realizar a compilação e posteriormente adiciona-lo ao banco desejado.

Para compilar é necessário executar o seguinte comando:

make

make install

Após a execução dos comandos acima será gerado um arquivo chamado dblink.sql. Este arquivo deve ser carregado (importado) no banco desejado. Para demonstrar o seu uso trabalharei com os bancos: banco01 e banco02.

O banco banco01 possui um tabela chamada tabela01 e o banco02 possui uma tabela chamada tabela02. Cada tabela possui um atributo código do tipo inteiro e ambas as tabelas contém 10 registros.

Carregando o arquivo dblink.sql no banco01.

psql banco01 -f dblink.sql

Com o dblink carregado no banco01, o próximo passo é realizar a conexão entre o banco01 e o banco02.

No exemplo, será considerado que estando conectado no banco01 será requisitada uma conexão com o banco02 para ai sim possibilitar a troca de informações entre os dois bancos de dados.

Então vamos a prática:

banco01=# SELECT dblink_connect(‘conexao’,'host=localhost port=9999 user=postgres dbname=banco02′);
dblink_connect
—————-
OK
(1 row)

Alguns parâmetros são informados. O primeiro é um nome para a conexão, e o restante parâmetros normais de uma conexão: hostname, porta, usuário, senha (opcional) e o nome do banco. Como para este exemplo a autenticação esta usando o método trust, o parâmetro password foi omitido.

Com a conexão OK agora é só realizar uma operação qualquer.

Por exemplo, um join entre a tabela01 que pertence ao banco01 e a tabela02 que pertence ao banco02.

banco01=# SELECT tab01.codigo,tab02.codigo FROM tabela01 tab01 INNER JOIN (SELECT * FROM dblink(‘conexao’,'SELECT codigo FROM tabela02′) AS resultado(codigo integer)) tab02 ON tab01.codigo=tab02.codigo;

Um outro exemplo pode ser feito com uma operação de escrita (INSERT, UPDATE OU DELETE).

A partir do banco01 fazendo uma chamada de inserção na tabela02 que está no banco02.

banco01=# SELECT dblink_exec(‘conexao’,'INSERT INTO tabela02 VALUES (generate_series(11,20))’);
dblink_exec
————-
INSERT 0 10
(1 row)

Conferindo:

banco01=# SELECT * FROM dblink(‘conexao’,'SELECT codigo FROM tabela02′) AS resultado(codigo integer);
codigo
——–
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
(20 rows)

Para operações de UPDATE e DELETE o procedimento transcorre da mesma maneira do que do comando INSERT.

Por fim, para encerrar a conexão é necessário executar a seguinte função:

banco01=# SELECT dblink_disconnect(‘conexao’);
dblink_disconnect
——————-
OK
(1 row)

Dicas para a instalação do Línux foi retirada do blog: http://jotacomm.wordpress.com/
acesse caso deseje mais informações.

 

Fuente: http://www.jardelmorais.com/banco-de-dados/postgres/ddl-postgres/postgres-habilitar-dblink-no-windows/