About Local Tables

To simplify data retrieval and improve the performance when building cubes, local tables have been incorporated into O3. This resolves issues resulting from use of complex queries against transactional databases which may result in poor performance.

By defining local tables, you can store data from the build process in memory for use in the calculation of virtual fields. See About Virtual Fields

A local table is a group of records with fields and key fields. You can access a record using the key value. This allows you, for example, to store a table representing a relationship between a code included in a measures source and another one which is not included but is necessary for the cube build because it identifies a model's dimension. This avoids the necessity of making a join in the query of measures with the relationship table.

Use the model's data sources to define local tables. You can use pre-existing data sources or new data sources defined exclusively for use with local tables.

Note
Because local tables are stored in memory, it is recommended to use these tables to store only catalogues or relationships between identities. Unless the amount of data is small, it is not recommended to store the result of sources that provide the measures for building the local table.

How to Use Local Tables

The following is an example of how to use a local table to simplify a SQL query. The example is simplified, but this data retrieval technique is applicable to more complex cases.

The following query must be made for the measures source of a sales analysis cube:

SELECT F.Date, I.ProductCode, R.Representative, C.Area, I.Amount
FROM Invoices F, Items I, Products P, Representative R, Clients C
WHERE F.Number = I.InvoiceNo
AND F.Issued = true
AND F.Type = 2
AND F.ClientCode = C.ClientCode
AND I.ProductCode = P.ProductCode
AND P.ProductType = R.ProductType
AND F.ClientCode = R.ClientCode
AND R.ValidityDate = (SELECT MAX(R2.ValidityDate)
                      FROM Representative R2
                      WHERE R2.ProductType = R.ProductType
                      AND R2.ClientCode = R.ClientCode)

The complexity of the previous query is identified in three main elements:

• The Clients table is included in the query's join to obtain the Client's area
• The Products table is included in the join, as it has the Product Type and the Sales Representative depends on (as well as the Client and Date) the Product type sold
• The Sales Representative assignation, apart from being determined by the Client and the Product Type, changes with time, so it is necessary to determine a valid Representative or the most recent one

The use of local tables can reduce the complexity of the query. First, you must define two local tables:

(K) indicates the key field of the local table.

Table 1

Table 2

SELECT R.ClientCode, R.Representative, P.ProductCode
FROM Representative R, Products P
WHERE P.ProductType = R.ProductType
AND R.ValidityDate = (SELECT MAX(R2.ValidityDate)
                              FROM Representative R2
                              WHERE R2.ClientCode = R.ClientCode
                              ANDR2.ProductType = R.ProductType)

Then, you must define the following virtual fields:

Area = LookUpField ("Clients", "Area", ClientCode)
Represent = LookUpField ("Reps", "Representative", ClientCode, ProductCode)

and substitute the measures source SQL query with the following query:

SELECT F.Date, I.ProductCode, I.Amount, F.ClientCode
FROM Invoices F, Items I
WHERE F.Number = I.InvoiceNo. AND F.Issued = true AND F.Type = 2

Notice that the query is significantly simplified, and because the Area and Represent virtual fields have the ClientCode and ProductCode fields as keys included in the query, they will be calculated for each record resulting from the query and added as additional fields.

Defining Local Tables

Use the Fields tab of the data source's property pane to define a local table.

To define a local table:

1. Select the data sources branch in the Design tree. The property pane for the data source displays.
2. Choose the Fields page of the property pane.
3. Turn on the Local Table checkbox. The property pane updates to include the Table Key and Table Field columns for the Field. In the General tab, the Dimension check box is replaced with the Only for Table checkbox.
4. Enter the table name in the space provided to the right of the checkbox.
5. For each field in the data source that is part of the local table, turn the Table Field checkbox on.
6. For each field in the data source that is a key in the local table, turn the Table Key checkbox on.

It is important to remember that

• The local table is constructed from a single data source.
• You must have at least two fields in a local table.
• You must identify at least one field in a local table as a key field.
• Turn on the Only for Table checkbox to indicate that the data source is used only for creation of the local table. No data is used directly in cube building.