understanding progen query

8/13/2019 Understanding ProGen Query

http://slidepdf.com/reader/full/understanding-progen-query 1/16

Understanding ProGen Query

Following topic will be covered in this document

1) The 4 layer of ProGen Query

2) Calculated and Summarized formula

3) Inner View By’s

4) ProGen Standard Trend

5) Prior Query

6) Company Security in ProGen Query

7) Role Security in ProGen Query

8) Fact filter in ProGen Query

9) Time Based formula Query

10) Report passing Query

11) How to debug for data validation



Example ProGen Query

SELECT A_111613 AS A_111613 ,

A_111313 AS A_111313 ,

A_111287 AS A_111287 ,

A_111285 AS A_111285 ,A_111333 AS A_111333

FROM

(SELECT VIEWBY1 AS A_111613 ,

ORDER1,

NVL(1.0*B_16390,0.0) AS A_111285 ,

NVL(1.0*B_16391,0.0) AS A_111287 ,

NVL(1.0*B_16404,0.0) AS A_111313 ,

NVL(1.0*B_16414,0.0) AS A_111333

FROM

(SELECT VIEWBY1 ,

ORDER1 ,

SUM( B_16390) AS B_16390 ,SUM( B_16391) AS B_16391 ,

SUM( B_16404) AS B_16404 ,

SUM( B_16414) AS B_16414

FROM

(SELECT REGION.REGION_DESC AS VIEWBY1 ,

REGION.REGION_DESC AS ORDER1 ,

SUM( SALES.ORDER_UNIT) AS B_16390 ,

SUM( SALES.NET_VALUE) AS B_16391 ,

SUM( SALES.ORDER_VALUE) AS B_16404 ,

SUM( SALES.NET_UNIT) AS B_16414

FROM SALES SALES

INNER JOIN REGION REGION

ON ( SALES.region_id = REGION.region_id )WHERE 1 = 1

AND ( 1 =1 )

AND SALES.SALES_DATE BETWEEN to_date('09/01/2011 00:00:00 ','mm/dd/yyyy hh24:mi:ss ') AND

to_date('09/30/2011 00:00:00 ','mm/dd/yyyy hh24:mi:ss ')

GROUP BY REGION.REGION_DESC

) O7

GROUP BY VIEWBY1 ,

ORDER1

) O7_1

) OT1

ORDER BY ORDER1

Color The last wrapperColor The Second layer

Color The first Layer

Color The Inner most layer



The 4 layer Query

Progen Query will have three inner queries

The Inner most layer

The first Layer

The Second layer

The last wrapper

The Inner most Query is the most important portion. Following thing are achieved here

1) Joining of Fact and dimension

2) Dimension filter

3) ProGen Time table join for trend

4) ProGen time filter

5) Fact filter applied here6) Report passing queries applied here

7) This Section also contains union all queries for current , prior and time based formulas

8) Each fact will have one or more queries

a. One for current

b. One for prior

c. One for each time based formula

9) Calculation for calculated /Single fact/ Conversion/Parameter formulas are done here

10) It

The First Layer

In this layer calculation of change % and summarized formula happen.

Both inner most query and the first layer query have same grouping columns, unless there is an inner

view by used. In case of inner view by inner most query/ies will have more group bys columns



Calculated and Summarized formulas

To simply the things all the formulas on a single fact using (or using dimensions with fact) is calculated

formulas. Usually in single fact also the members in calculation should be of same data type for

calculated formula. All other formula’s like formula over two facts and same fact formula with different

data type and formula come from same fact having different queries in inner most layer are all

summarized formula’s. Calculated formulas are calculated in inner most layer and summarized formulas

are calculated at first layer

All time based formulas are calculated formula.

Example calculated formula

Table Sales – Column: Type having value A,B,C and Column : Sales hold sales Value

Case when sales.type =’A’ then sales.sales else null end

Or

Sales.sales –sales.discount

Or

Case when region.name = ‘South’ then sales.sales else null end

Summarized formula examples

Same Table – Sum(sales.Sales)/count(Distinct sales.sales_order_no)

Different table – sum(sales.sales) – sum(purchase.po_value)

In case of summarized formula the data is summarized in inner query after that the calculation will

happen in outer query .

There are two type of summarized formula are there pre and post. Please find the queries to understand

that



Pre

Select region as region,

Sum(Sales/po_value) as Sales,

From (

Select region.region as region,

Sum(sales.sales) as sales ,

Null as po_value

From sales inner join region (sales.region_id = region.region_id)

Group by region.region

Union all


Null as sales ,

Sum(purchase.po_value) as po_value

From purchase inner join region (purchase.region_id = region.region_id)

Group by region.region) A1

Group by region

Post


Sum(Sales)/Sum(po_value) as Sales,

From (


Sum(sales.sales) as sales ,

Null as po_value


Group by region.region

Union all


Null as sales ,

Sum(purchase.po_value) as po_value

From purchase inner join region (purchase.region_id = region.region_id)

Group by region.region) A1

Group by region

This might not make a difference in some queries, but place where inner view by is used it make lot ofdifference. Also in case of divide it can make difference too.



Pre with inner view

Order having 5 or more lines


Sum(case when total_lines >=5 then 1 else 0 end ) as order_with_5Line,

From (

Select region.region as region,sales.orderNo,

count(distinct sales.order_line) as total_lines ,

Null as po_value


Group by region.region, sales.orderNo,

) A1

Group by region



Inner View

Inner view is used in specific cases when there are some calculations needed at one particular level, and

then data can be rolled up on other levels

Example

1. Total Order with at least 5 line for each region CD’s

2. Total Bookings with repeat more than once at department level

3. Total Cities in a zone where there is least one high school

In all such case you need to first group data first at a level then you can do the calculation and roll it at

upper level

In case of progen query Inner query will have addition group by , when Inner view by is used. Ideally

Inner Query and First layer will have same group by’s. In case of inner view by , Inner query will have

additional group by.

Order having 5 or more lines


Sum(case when total_lines >=5 then 1 else 0 end ) as order_with_5Line,

From (

Select region.region as region,sales.orderNo,

count(distinct sales.order_line) as total_lines ,

Null as po_value

From sales inner join region (sales.region_id = region.region_id)Group by region.region, sales.orderNo,

) A1

Group by region



ProGen Standard Trend

The ProGen Standard Trend will have join with Progen Time. ProGen Time is view on pr_day_denom

table.

When Query uses progen time table; the view by /group by column from this table depend on type of

time region used the options used in time region.

In case the date which is joining to ProGen Time is having date and time component, make sure that

date is truncated while joining to ddate of ProGen time

SELECT TIME AS TIME ,

A_111313 AS A_111313 ,

A_111287 AS A_111287 ,

A_111285 AS A_111285 ,

A_111333 AS A_111333

FROM(SELECT VIEWBY1 AS TIME ,

ORDER1,

NVL(1.0*B_16390,0.0) AS A_111285 ,

NVL(1.0*B_16391,0.0) AS A_111287 ,

NVL(1.0*B_16404,0.0) AS A_111313 ,

NVL(1.0*B_16414,0.0) AS A_111333

FROM

(SELECT VIEWBY1 ,

ORDER1 ,

SUM( B_16390) AS B_16390 ,

SUM( B_16391) AS B_16391 ,SUM( B_16404) AS B_16404 ,

SUM( B_16414) AS B_16414

FROM

(SELECT PROGEN_TIME.CM_CUST_NAME AS VIEWBY1 ,

PROGEN_TIME.CM_ST_DATE AS ORDER1 ,


SUM( SALES.NET_VALUE) AS B_16391 ,



FROM SALES SALES

INNER JOIN

( SELECT *

FROM PR_DAY_DENOM

) PROGEN_TIME

ON ( TRUNC(SALES.SALES_DATE)= PROGEN_TIME.DDATE )

WHERE 1 = 1

AND ( 1 =1 )

AND SALES.SALES_DATE BETWEEN to_date('9/1/2010 00:00:00','mm/dd/yyyy hh24:mi:ss ') AND

to_date('9/30/2011 00:00:00','mm/dd/yyyy hh24:mi:ss ')



AND TO_CHAR(PROGEN_TIME.ddate,'dd') *1 <= 30

GROUP BY PROGEN_TIME.CM_CUST_NAME ,

PROGEN_TIME.CM_ST_DATE

) O7

GROUP BY VIEWBY1 ,

ORDER1

) O7_1

) OT1

ORDER BY ORDER1



Prior Query

When you select a prior measure, a new query per fact added to the inner most query. There is usually

one current and one prior query. The Below example also highlight change % calculations.

SELECT A_111656 AS A_111656 ,

A_111313 AS A_111313 ,

A_112190 AS A_112190 ,

A_112188 AS A_112188 ,

A_112189 AS A_112189 ,

A_111285 AS A_111285

FROM


ORDER1,

NVL(1.0*B_16390,0.0) AS A_111285 ,

NVL(1.0*B_16404,0.0) AS A_111313 ,

NVL(1.0*Prior_B_16404,0.0) AS A_112190 ,NVL(1.0*Change_B_16404,0.0) AS A_112188 ,

NVL(1.0*Changep_B_16404,0.0) AS A_112189

FROM

(SELECT VIEWBY1 ,

ORDER1 ,

SUM( B_16390) AS B_16390 ,

SUM( B_16404) AS B_16404 ,

SUM( Prior_B_16404) AS Prior_B_16404 ,

( NVL(1.0 * SUM( B_16404),0) - NVL(SUM( Prior_B_16404),0)) AS Change_B_16404 ,

( ( NVL(SUM( B_16404),0) - NVL(SUM( Prior_B_16404),0)) )*100.0/ (

CASEWHEN SUM( Prior_B_16404) =0

THEN NULL

ELSE SUM( Prior_B_16404)

END ) AS Changep_B_16404

FROM

(SELECT REGION.ZONE_DESC AS VIEWBY1 ,

REGION.ZONE_DESC AS ORDER1 ,



NULL AS Prior_B_16404 ,

NULL*0 AS Change_B_16404 ,

NULL*0 AS Changep_B_16404

FROM SALES SALES


ON ( SALES.ACCOUNT_CODE = REGION.ACCOUNT_CODE

AND SALES.AREA = REGION.AREA

AND SALES.REGION = REGION.REGION

AND SALES.TERRITORY = REGION.TERRITORY

AND SALES.ZONE = REGION.ZONE )



WHERE 1 = 1

AND ( 1 =1 )

AND SALES.SALES_DATE BETWEEN to_date('09/01/2011 00:00:00 ','mm/dd/yyyy hh24:mi:ss ')

AND to_date('09/30/2011 00:00:00 ','mm/dd/yyyy hh24:mi:ss ')

GROUP BY REGION.ZONE_DESC

UNION ALL

SELECT REGION.ZONE_DESC AS VIEWBY1 ,

REGION.ZONE_DESC AS ORDER1 ,

NULL*0 AS B_16390 ,

NULL*0 AS B_16404 ,

SUM( SALES.ORDER_VALUE) AS Prior_B_16404 ,

NULL*0 AS Change_B_16404 ,

NULL*0 AS Changep_B_16404

FROM SALES SALES


ON ( SALES.region_id = REGION.region_id )

WHERE 1 =1

AND ( 1 =1 )AND SALES.SALES_DATE BETWEEN to_date('08/01/2011 00:00:00 ','mm/dd/yyyy hh24:mi:ss ')

AND to_date('08/31/2011 23:59:59 ','mm/dd/yyyy hh24:mi:ss ')

GROUP BY REGION.ZONE_DESC

) O7

GROUP BY VIEWBY1 ,

ORDER1

) O7_1

) OT1

ORDER BY ORDER1



Company Security

When company security is applied, all users need to be associated to one or more company. Fact and

Dimension also need to be secured on company using security ui.

In query there will a clause added to filter the company data from fact like

SALES.COMPANY_ID IN

( SELECT DISTINCT COMPANY_ID

FROM PRG_SEC_USER_COMPANY

WHERE PROGEN_USER_ID = 41

)

If company security is applied and user do not have any company access; then he will not get any data.



Role Security

Role security can be applied from Multi Security UIs in BI manager. Role security clause will come like a

normal filter clause in query.

The main difference between company security and role security is:

1. In case of role security, if you do not apply filter user will have all data access. In company

security he will not have any data access

2. As of now company Security is possible only for one parameter. While role security can be

applied to multiple parameters.

Region.State in (‘MP’,’UP’,’AP’)



Fact Filter

Fact filter are specific to a fact. They are applied in inner most query to the fact for which they are

created. They are applied to both current as well as prior query of the fact.

SELECT A_111613 AS A_111613 ,

A_111313 AS A_111313 ,

A_111287 AS A_111287 ,

A_111285 AS A_111285 ,

A_111333 AS A_111333

FROM


ORDER1,

NVL(1.0*B_16390,0.0) AS A_111285 ,

NVL(1.0*B_16391,0.0) AS A_111287 ,

NVL(1.0*B_16404,0.0) AS A_111313 ,

NVL(1.0*B_16414,0.0) AS A_111333FROM

(SELECT VIEWBY1 ,

ORDER1 ,

SUM( B_16390) AS B_16390 ,

SUM( B_16391) AS B_16391 ,

SUM( B_16404) AS B_16404 ,

SUM( B_16414) AS B_16414

FROM

(SELECT REGION.REGION_DESC AS VIEWBY1 ,

REGION.REGION_DESC AS ORDER1 ,

SUM( SALES.ORDER_UNIT) AS B_16390 ,SUM( SALES.NET_VALUE) AS B_16391 ,



FROM SALES SALES


ON ( SALES.region_id = REGION.region_id )

WHERE 1 = 1

AND ( 1 =1 )

AND SALES.FLAG =1

AND SALES.SALES_DATE BETWEEN to_date('09/01/2011 00:00:00 ','mm/dd/yyyy hh24:mi:ss ') AND

to_date('09/30/2011 00:00:00 ','mm/dd/yyyy hh24:mi:ss ')

GROUP BY REGION.REGION_DESC

) O7

GROUP BY VIEWBY1 ,

ORDER1

) O7_1

) OT1

ORDER BY ORDER1

understanding progen query

Documents