Normalization

No PostgreSQL specific customizations were made for this feature.

The Data Population for Reports VLDB setting allows you to define if and how report data is normalized to save memory resources.

MicroStrategy Engine provides four options:

• Do not normalize the report data - 0
• Normalize the report data in Intelligence Server - 1
• Normalize the report data in the database - 2
• Normalize the report data in the database using relationship tables - 3

The default value is 0. This produces a single result table that retrieves all data into Intelligence server without normalization.

Example Report SQL

select a11.C_NATIONKEY n_nationkey,
max(a12.n_name) n_name,
a11.C_MKTSEGMENT C_MKTSEGMENT,
sum(a11.C_ACCTBAL) WJXBFS1
from CUSTOMER a11
join NATION a12
  on (a11.C_NATIONKEY = a12.n_nationkey)
where a11.C_NATIONKEY = 20
group by a11.C_NATIONKEY,
a11.C_MKTSEGMENT

For example, option 2: Normalize the report data in the database, generates an intermediate table for the result table by inserting only the key attribute IDs and metric values.

Example Report SQL

Pass0: 
select a11.C_NATIONKEY  n_nationkey, 
max(a12.n_name)  n_name, 
a11.C_MKTSEGMENT  C_MKTSEGMENT, 
sum(a11.C_ACCTBAL)  WJXBFS1 
into ##T65VYFLHCMD000 
from CUSTOMER a11 
join NATION a12 
  on  (a11.C_NATIONKEY = a12.n_nationkey) 
where a11.C_NATIONKEY = 20 
group by a11.C_NATIONKEY, 
a11.C_MKTSEGMENT 

Pass1: 
select distinct pa11.n_nationkey  n_nationkey, 
pa11.n_name  n_name 
from ##T65VYFLHCMD000 pa11 

Pass2: 
select pa11.C_MKTSEGMENT  C_MKTSEGMENT, 
pa11.n_nationkey  n_nationkey, 
pa11.WJXBFS1  WJXBFS1 
from ##T65VYFLHCMD000 pa11 

See KB32010: What are the Data Population VLDB properties in MicroStrategy Engine? for more information.