Versions Compared

Old Version 3

changes.mady.by.user Nicolás Palombo (Unlicensed)

Saved on

compared with

New Version Current

changes.mady.by.user Nicolás Palombo (Unlicensed)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin
Wiki Markup
{scrollbar}

----
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.
{quote}
(!) 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.
{quote}

h3. 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.

!worddav5fb3aefc87d63298c6829a29022947f3.png|height=283,width=293!

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

...

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.

(warning) 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.

Image Added

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

Code Block
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)
{code}

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

...

Name

Clients

Query

SELECT ClientCode, Area
FROM Clients

Fields

ClientCode (K)
Area

Table 2

Name

Reps

Query

Code Block

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)

...

 Fields 
 ClientCode (K) 
...

 ProductCode 
...
 (K) 
...

 Representative 

 Then, 
...
 you 
...
 must 
...
 define 
...
 the 
...
 following 
...
 virtual 
...
 fields:
...
}
 Code Block
Area = LookUpField ("Clients", "Area", ClientCode)
Represent = LookUpField ("Reps", "Representative", ClientCode, ProductCode)
{code}

and 
...
 substitute 
...
 the 
...
 measures 
...
 source 
...
 SQL 
...
 query 
...
 with 
...
 the 
...
 following 
...
 query:
...
}
 Code Block
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
{code}

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 
...
  1.  the 
...
  1.  data 
...
  1.  sources 
...
  1.  branch 
...
  1.  in 
...
  1.  the 
...
  1.  Design 
...
  1.  tree. 
...
  1.  The 
...
  1.  property 
...
  1.  pane 
...
  1.  for 
...
  1.  the 
...
  1.  data 
...
  1.  source 
...
  1.  displays.
...
  2. Choose 
...
  1.  the 
...
  1.  Fields 
...
  1.  page 
...
  1.  of 
...
  1.  the 
...
  1.  property 
...
  1.  pane.
...
  2. Turn 
...
  1.  on 
...
  1.  the 
...
  1.  Local 
...
  1.  Table 
...
  1.  checkbox. 
...
  1.  The 
...
  1.  property 
...
  1.  pane 
...
  1.  updates 
...
  1.  to 
...
  1.  include 
...
  1.  the 
...
  1.  Table 
...
  1.  Key 
...
  1.  and 
...
  1.  Table 
...
  1.  Field 
...
  1.  columns 
...
  1.  for 
...
  1.  the 
...
  1.  Field. 
...
  1.  In 
...
  1.  the 
...
  1.  General 
...
  1.  tab, 
...
  1.  the 
...
  1.  Dimension 
...
  1.  check 
...
  1.  box 
...
  1.  is 
...
  1.  replaced 
...
  1.  with 
...
  1.  the 
...
  1.  Only 
...
  1.  for 
...
  1.  Table 
...
  1.  checkbox.
...
  2. Enter 
...
  1.  the 
...
  1.  table 
...
  1.  name 
...
  1.  in 
...
  1.  the 
...
  1.  space 
...
  1.  provided 
...
  1.  to 
...
  1.  the 
...
  1.  right 
...
  1.  of 
...
  1.  the 
...
  1.  checkbox.
...
  2. For 
...
  1.  each 
...
  1.  field 
...
  1.  in 
...
  1.  the 
...
  1.  data 
...
  1.  source 
...
  1.  that 
...
  1.  is 
...
  1.  part 
...
  1.  of 
...
  1.  the 
...
  1.  local 
...
  1.  table, 
...
  1.  turn 
...
  1.  the 
...
  1.  Table 
...
  1.  Field 
...
  1.  checkbox 
...
  1.  on.
...
  2. For 
...
  1.  each 
...
  1.  field 
...
  1.  in 
...
  1.  the 
...
  1.  data 
...
  1.  source 
...
  1.  that 
...
  1.  is 
...
  1.  a 
...
  1.  key 
...
  1.  in 
...
  1.  the 
...
  1.  local 
...
  1.  table, 
...
  1.  turn 
...
  1.  the 
...
  1.  Table 
...
  1.  Key 
...
  1.  checkbox 
...
  1. on.
Image Added
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.
...
 Wiki Markup
{scrollbar}
 Child pages (Children Display)