Page Comparison

Wiki Markup

{scrollbar}

----
Before designing the report it is necessary to specify&nbsp;what data you will be working on and which the connection mechanism will be.

This tutorial builds a report using data stored in an O3 cube.

Extraction of these data is performed in 2 steps:&nbsp;&nbsp;
* Define the data source&nbsp;
* Define the data set.&nbsp;&nbsp;
You may have several data sets defined on the same data source.

h3. Defining a Data Source&nbsp;

# Select New Data Source in the Data menu to create a new data source.&nbsp;
!NewDataSource.png|vspace=10,hspace=10!
# Choose the Data Source type for O3 ( O3 Data Source)
This lets you define a data source for O3 cubes and views. These O3 data sources may be of two types: &nbsp;
#* Sources that access views saved in the server.
#* Sources that access cubes saved in the server and use MDX language to specify the query.&nbsp;
# Indicate the Data Source Name.
In this example the data source will be called "O3 Local" &nbsp;
# Enter the Data source parameters.&nbsp; &nbsp;
| *Host Name:* | The name or IP address of the server where O3 is running.&nbsp;&nbsp;&nbsp; \\
In our case:*localhost* |
| *Port:* | Server port indicated in the parameter above where O3 server is run.&nbsp; &nbsp; \\
In our case we will use port 7777 by default. |
| *User Name:* | Name of the user defined in O3 that will be used for connection to the server.&nbsp; \\
In our case, we will use&nbsp;*user* |
| *Password:* | Password of the user defined in the previous field.&nbsp; &nbsp; \\
In our case we will use the&nbsp;*user*'s password. |
| *Query Type:* | It indicates the Data Source Type to be used:&nbsp;MDX Query or&nbsp;O3 View. \\
In our case&nbsp;*O3 View* |
!NewO3DataSource.png|vspace=10,hspace=10!
# Verificar que la conexión se estableció en forma correcta presionando el botón *Test Connection...*

h3. Definiendo un Conjunto de Datos (Data set)

Una vez definida la Fuente de datos, es posible definir los Conjuntos de Datos que se obtendrán de dicha fuente. Cada Conjunto de Datos será una consulta diferente que como se mencinó anteriormente podrá originarse desde una vista o a través de MDX.

Sea cual sea el caso, la creación del Conjunto de Datos (Data Set) es&nbsp;la misma.

Para el caso concreto de este tutorial se utiliza una vista sobre el cubo de vinos que se define como se muestra en la siguiente imagen
!VistaVinosPorVendedor.png|vspace=10,hspace=10!

En esta vista se seleccionan las medidas Litros Vendidos y Venta Neta como columnas y en el eje vertica se colocan las dimensiones de Vendedores y Vinos en sus respectivos niveles Vendedor y Vino.

Una vez definida la vista y almacenada en el servidor con el nombre *VinosPorVendedor* con alcance público se deben seguir los siguientes pasos:
# Seleccione la opción del menú Data->New Data Set
!NewDataSet.png|vspace=10,hspace=10!
# Indique el nombre del Conjunto de Datos (Data Set Name)
# Seleccione la Fuente de datos sobre la que se desea definir el conjunto de datos
En este ejemplo la fuente de datos se llama *"O3 Local"*.
# Seleccionar *O3 Data Set* como tipo de Conjunto de Datos
# En la ventana de definición del nuevo Conjunto de Datos, ingrese la consulta que se muestra a continuación para obtener los datos desplegados en la vista pública VinosPorVendedor. Esta vista debe haber sido creada con aterioridad en el servidor de O3
!Query-O3View.png|vspace=10,hspace=10!
En la consulta es importante destacar la cláusula *FROM* de la clausulta que hace referencia a *'Vinos/VinosPorVendedor#_public'*. Este es el nombre con el que se hace referencia a la vista necesaria para este reporte.
| Vinos | Es el nombre del cubo tal como está publicado en el servidor |
| VinosPorVendedor | Es el nombre de la vista de donde se extraen los datos |
| \#_public | Indica que la vista es una vista pública. |
Estos datos pueden ser sacados de la barra de estado del navegador web, luego de source=Vinos/VinosPorVendedor#_public en la página de marcadores.
# Presionar el botón *Finish* para aceptar los cambios
# La ventana que se muestra a continuación permite modificar parámetros adicionales del Conjunto de Datos, así como obtener una Vista Preliminar (Preview Results) de la consulta
!Query-Preview.png|vspace=10,hspace=10!

h3. Definiendo Conjuntos de Datos a través de MDX

Los pasos anteriores permitieron definir un Conjunto de Datos utilizando una vista previamente almacenada en el servidor.

O3 permite un método alternativo que no requiere salvar ninguna vista, sino que por el contrario permite al diseñador del reporte indicar la consulta directamente en el propio reporte.

Este método alternativo utiliza el lenguaje de consulta MDX, el cual permite especificar consultas multidimensionales sobre los cubos publicados en el servidor de O3
# Crear una nueva fuente de datos (Data Source) llamada O3 Local MDX con los siguientes parámetros
| *Host Name:* | localhost |
| *Port:* | 7777 |
| *User Name:* | user |
| *Password:* | user |
| *Query Type:* | MDX Query |
# Crear un nuevo conjunto de datos (Data Set) llamado Ventas MDX con los siguientes parámetros
| *Name:* | Ventas MDX |
| *Data Source:* | O3 Local MDX |
| *Data Set Type:* | O3 Data Set |
# Ingresar la siguiente consulta MDX para este nuevo conjunto de datos
{code:sql}

...

Before designing the report it is necessary to specify what data you will be working on and which the connection mechanism will be.

This tutorial builds a report using data stored in an O3 cube.

Extraction of these data is performed in 2 steps:

Define the data source
Define the data set.
You may have several data sets defined on the same data source.

Defining a Data Source

Select New Data Source in the Data menu to create a new data source.
Image Added
Choose the Data Source type for O3 (O3 Data Source)
This lets you define a data source for O3 cubes and views. These O3 data sources may be of two types:
- Sources that access views saved in the server.
- Sources that access cubes saved in the server and use MDX language to specify the query.
Indicate the Data Source Name.
In this example the data source will be called "O3 Local"

Enter the Data source parameters.

Host Name:	The name or IP address of the server where O3 is running. In our case:localhost
Port:	Server port indicated in the parameter above where O3 server is run. In our case we will use port 7777 by default.
User Name:	Name of the user defined in O3 that will be used for connection to the server. In our case, we will use user
Password:	Password of the user defined in the previous field. In our case we will use the user's password.
Query Type:	It indicates the Data Source Type to be used: MDX Query or O3 View. In our case O3 View

Image Added

Check the connection was properly by pressing the Test Connection button

Defining a Data set

Once the Data Source has been defined, you may define the Data Sets to be obtained from such source. Each Data Set will be a different query which, as mentioned before, may be generated from a view or through MDX

Whatever the case, the creation of the Data Set is the same.

For the specific case in this tutorial a view on the wines cube is used, as seen in the image below.
Image Added

In this view, measures Litres Sold (Litros Vendidos) and Net Sales (Venta Neta) are selected as columns, and dimensions Salespeople (Vendedores) and Wines(Vinos) in their corresponding levels Salesperson and Wine, are placed on the vertical axis.

Once the view has been defined and saved in the server under the name VinosPorVendedor, as a public view, the following steps must be followed:

Select Data Set from the Data menu.
Image Added
Indicate the Data Set Name
Select the Data Source in which the Data Set needs to be defined.
In this example, the data source is called "O3 Local".
Select O3 Data Set as Data Set Type
In the new Data Set definition window, enter the query shown below to obtain the data displayed in the public view VinosPorVendedor. This view must have been created beforehand in the O3 Server.
Image Added
In the query, it is important to point out the clause FROM in the clause referring to 'Vinos/VinosPorVendedor#_public'. This is the name by which we refer to the necessary view for this report.
Vinos
This is the name of the cube just as it is published in the server.
VinosPorVendedor
The name of the view where data are extracted from.
#_public
It indicates the view is a public one.
These data can be extracted from the web browser status bar, after sourceVinos/VinosPorVendedor#_public in the markers' page.
Press Finish to accept changes.
The window shown below lets you modify additional parameters of the Data Set, as well as obtain a print preview of the query.
Image Added

Defining Data Sets through MDX

The steps above have enabled us to create a Data Set using a view previously saved in the server.

O3 allows for an alternative method which does not involve saving any view, but on the contrary allows the designer of the report to indicate the query directly in the report itself.

Create a new Data Source called llamada O3 Local MDX with the parameters below.
Host Name:
localhost
Port:
7777
User Name:
user
Password:
user
Query Type:
MDX Query
Create a new Data Set called MDX Sales with the same parameters.
Name:
Ventas MDX
Data Source:
O3 Local MDX
Data Set Type:
O3 Data Set

Enter the following MDX query for this new data set.

Code Block

	sql
	sql


SELECT
	{[Measures].[Litros Vendidos], [Measures].[Venta Neta]} ON COLUMNS
	,CROSSJOIN({[Vendedores].[Vendedor].members}, {[Vinos].[Vino].members}) ON ROWS
FROM [Vinos]

...

This query generates a table containing:

One row for each Wine sold by each Distributor

For every combination of Wine and Distributor it will show 2 columns ( Litres sold and Net Sales measures)

Distribuidor	Vino	Litros Vendidos	Venta Neta
Distributor 1	Wine 1	10	1500
Distributor 1	Wine 2	15	2700
Distributor 2	Wine 1	13	1780
...	...	...	...
Distributor 3	Wine 3	12	720

The clause CROSSJOIN in the query is the one that enables the nesting of dimensions Distributors and Wines which also existed in the View.

For more details about syntax and options provided by the MDX language supported by O3 refer to Writing MDXqueries

Displaying when a datamart was last updated

Displaying the update time of a Datamart

...

Wiki Markup
{scrollbar}

Versions Compared

Old Version 2

New Version Current

Key

Defining a Data Source

Defining a Data set

Defining Data Sets through MDX

Displaying when a datamart was last updated

Vinos	This is the name of the cube just as it is published in the server.
VinosPorVendedor	The name of the view where data are extracted from.
#_public	It indicates the view is a public one.

Host Name:	localhost
Port:	7777
User Name:	user
Password:	user
Query Type:	MDX Query

Name:	Ventas MDX
Data Source:	O3 Local MDX
Data Set Type:	O3 Data Set