Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Can you advise with it?
Answers
-
Hmm when i write formula in analize just like this: count(distinct("Payment"."ID") by "Client"."ID" ) - all work perfect! None formula (a lot!) in BMM on Payment is not working
So due to your link's & advice i have to check all my work again and again. I will be back Thank a lot about your assistance 0 -
My phisical layer
LINKS
BMM
0 -
Hi,
looking at the physical structure something that does not seem right is; -
Account (Fact) -> Partner Client (Dimension)
Account (Fact) -> Calendar (Dimension)
Partner Client (Dimension) -> Calendar (Dimension)
Dimension to dimension should not be, unless this is some kind of snowflake (?) in which case I would expect the calendar to be copied as a new alias for the purpose such that there is no join.
Looking again and trying to understand what is making up what in the physical is this because you have a single table source for both dimension and fact?
Also, on things I cannot see - you look to have aggregate tables, so have you ensured that content level settings are correct for the two sources for each field in the fact table, and that settings are also correct with the associated dimension where, dependendant on which fields are present the agg table may or may not be used?
You have started in the deep end of BI modelling for sure!
0 -
Also, can you explain the purpose of 'add-ons' - is this snowflaking, if so when you modelled the business model layer did you drag the outer table onto the inner dimension and define the nature of the join that way? (if it is correct you will only see one Logical Table Source on the dimension in question, but when you click into it you will see the relationship with your outer table defined)
Note also that if this is a common requirement then you may also want to have a look at lookup tables, often used for translation purposes but particularly if you have a dense relationship can also work well for adding the odd field to a dimension.
0 -
It's additional attribute for CLIENT.
Dees it impact on SQL generation between?
So i remove it join, but nothing change
0 -
Yes one table is datamart with dimensional data and fact data. I divide at BMM layer on two: fact & dim There is no aggregate data indeed. But for a while i don't need them. I have to understand power of OBIEE. May be there is no any power
One fact&many dimensions - it's not in real world. I try understand how it work on a simple example Client-Account-Payment. 0 -
And what error are you currently getting? (you are my hands and eyes!)
0 -
-------------------- SQL Request, logical request hash:
cd65f190
set variable LOGLEVEL = 7;SELECT
0 s_0,
"Kaspi Business - Платежи"."Календарь"."Месяц (Дата)" s_1,
"Kaspi Business - Платежи"."Партнер"."Активен - 1 мес" s_2,
"Kaspi Business - Платежи"."Партнер"."Активен - 3 мес" s_3,
"Kaspi Business - Платежи"."Партнер"."ИИН" s_4,
"Kaspi Business - Платежи"."Партнер"."Наименование" s_5,
"Kaspi Business - Платежи"."Партнер"."Тип партнера" s_6,
"Kaspi Business - Платежи"."Партнер"."ТОО ИП" s_7,
"Kaspi Business - Платежи"."Платежи"."Количество платежей" s_8, --this is simple sum(1) on Payment_FACT
"Kaspi Business - Платежи"."Счета"."Остаток по счету на конец мес (KZT)" s_9,
"Kaspi Business - Платежи"."Счета"."Средне месячный остаток по счету (KZT)" s_10,
count(distinct("Kaspi Business - Платежи"."Платежи"."ID") by "Kaspi Business - Платежи"."Партнер"."ИИН") s_11,
count(distinct("Kaspi Business - Платежи"."Счета"."Номер счета") by "Kaspi Business - Платежи"."Партнер"."ИИН") s_12
FROM "Kaspi Business - Платежи"
WHERE
("Календарь"."Месяц (Дата)" = date '2018-11-01')
ORDER BY 5 ASC NULLS LAST, 2 ASC NULLS LAST, 6 ASC NULLS LAST, 8 ASC NULLS LAST, 3 ASC NULLS LAST, 4 ASC NULLS LAST, 7 ASC NULLS LAST
FETCH FIRST 6500001 ROWS ONLY
/* QUERY_SRC_CD='rawSQL' */
]]
[2019-01-15T10:48:28.741+06:00] [OBIS] [TRACE:7] [] [] [ecid: 54d5c90a-b481-41da-8e21-8e3d9cfbf877-00008845,0:1:1:3] [sik: ssi] [tid: d1da7700] [messageid: USER-74] [requestid: e3920008] [sessionid: e3920000] [username: sharkov_32744] -------------------- Logical query limits: max query time(user, system, session) = (0, 0, 0); max rows(system, session) = (0, 0)
[2019-01-15T10:48:28.741+06:00] [OBIS] [TRACE:7] [] [] [ecid: 54d5c90a-b481-41da-8e21-8e3d9cfbf877-00008845,0:1:1:3] [sik: ssi] [tid: d1da7700] [messageid: USER-23] [requestid: e3920008] [sessionid: e3920000] [username: sharkov_32744] -------------------- General Query Info: [[
Repository: ssi, Subject Area: Kaspi Business - Платежи, Presentation: Kaspi Business - Платежи
]]
[2019-01-15T10:48:28.744+06:00] [OBIS] [TRACE:7] [] [] [ecid: 54d5c90a-b481-41da-8e21-8e3d9cfbf877-00008845,0:1:1:3] [sik: ssi] [tid: d1da7700] [messageid: USER-2] [requestid: e3920008] [sessionid: e3920000] [username: sharkov_32744] -------------------- Logical Request (before navigation): [[
RqList [1,2,3,4,5,6,7]
0 as c1 GB,
Календарь.Месяц (Дата) as c2 GB,
Партнер.Активен - 1 мес as c3 GB,
Партнер.Активен - 3 мес as c4 GB,
Партнер.ИИН as c5 GB,
Партнер.Наименование as c6 GB,
Партнер.Тип партнера as c7 GB,
Партнер.ТОО ИП as c8 GB,
Количество платежей:[DAggr(Платежи (Факт).Количество платежей by [ Календарь.Месяц (Дата), Партнер.ИИН, Партнер.Активен - 1 мес, Партнер.Активен - 3 мес, Партнер.Наименование, Партнер.ТОО ИП, Партнер.Тип партнера] )] as c9 GB --,
Остаток по счету на конец мес (KZT):[DAggr(Счета (Факт).Остаток по счету на конец мес (KZT) by [ Календарь.Месяц (Дата), Партнер.ИИН, Партнер.Активен - 1 мес, Партнер.Активен - 3 мес, Партнер.Наименование, Партнер.ТОО ИП, Партнер.Тип партнера] )] as c10 GB,
Средне месячный остаток по счету (KZT):[DAggr(Счета (Факт).Средне месячный остаток по счету (KZT) by [ Календарь.Месяц (Дата), Партнер.ИИН, Партнер.Активен - 1 мес, Партнер.Активен - 3 мес, Партнер.Наименование, Партнер.ТОО ИП, Партнер.Тип партнера] )] as c11 GB,
count(distinct Платежи.ID by [ Партнер.ИИН] ) as c12 GB,
count(distinct Счета.Номер счета by [ Партнер.ИИН] ) as c13 GB
DetailFilter: Календарь.Месяц (Дата) = DATE '2018-11-01'
OrderBy: c5 asc NULLS LAST, c2 asc NULLS LAST, c6 asc NULLS LAST, c8 asc NULLS LAST, c3 asc NULLS LAST, c4 asc NULLS LAST, c7 asc NULLS LAST
]]
[2019-01-15T10:48:28.748+06:00] [OBIS] [TRACE:7] [] [] [ecid: 54d5c90a-b481-41da-8e21-8e3d9cfbf877-00008845,0:1:1:5] [sik: ssi] [tid: d1da7700] [messageid: USER-52] [requestid: e3920008] [sessionid: e3920000] [username: sharkov_32744] -------------------- Expression 'Количество платежей:[DAggr(Платежи (Факт).Количество платежей by [ Партнер.ИИН] )]' converted to NULL because [nQSError: 14020] None of the fact tables are compatible with the query request Количество платежей:[DAggr(Платежи (Факт).Количество платежей by [ Партнер.ИИН] )]. [nQSError: 14081] You may be able to evaluate this query if you remove one of the following column references: Партнер.ИИН.
[2019-01-15T10:48:28.749+06:00] [OBIS] [TRACE:7] [] [] [ecid: 54d5c90a-b481-41da-8e21-8e3d9cfbf877-00008845,0:1:1:5] [sik: ssi] [tid: d1da7700] [messageid: USER-53] [requestid: e3920008] [sessionid: e3920000] [username: sharkov_32744] -------------------- List of attributes and their LTS sources: [[
Column count(distinct Платежи.ID by [ Партнер.ИИН] ) rendered via fact LTS [Logical table sources (Priority=0, SystemGenerated=False): Счета (Факт).N_KASPI_BIZ_ACC_D]
]]
[2019-01-15T10:48:28.750+06:00] [OBIS] [TRACE:7] [] [] [ecid: 54d5c90a-b481-41da-8e21-8e3d9cfbf877-00008845,0:1:1:5] [sik: ssi] [tid: d1da7700] [messageid: USER-53] [requestid: e3920008] [sessionid: e3920000] [username: sharkov_32744] -------------------- List of attributes and their LTS sources: [[
Column Средне месячный остаток по счету (KZT):[DAggr(Счета (Факт).Средне месячный остаток по счету (KZT) by [ Календарь.Месяц (Дата), Партнер.ИИН, Партнер.Активен - 1 мес, Партнер.Активен - 3 мес, Партнер.Наименование, Партнер.ТОО ИП, Партнер.Тип партнера] )] rendered via fact LTS [Logical table sources (Priority=0, SystemGenerated=False): Счета (Факт).N_KASPI_BIZ_ACC_D]
]]
[2019-01-15T10:48:28.751+06:00] [OBIS] [TRACE:7] [] [] [ecid: 54d5c90a-b481-41da-8e21-8e3d9cfbf877-00008845,0:1:1:5] [sik: ssi] [tid: d1da7700] [messageid: USER-52] [requestid: e3920008] [sessionid: e3920000] [username: sharkov_32744] --------------------
Expression 'Количество платежей:[DAggr(Платежи (Факт).Количество платежей by [ Календарь.Месяц (Дата), Партнер.ИИН, Партнер.Активен - 1 мес, Партнер.Активен - 3 мес, Партнер.Наименование, Партнер.ТОО ИП, Партнер.Тип партнера] )]' converted to NULL because [nQSError: 14020] None of the fact tables are compatible with the query request Количество платежей:[DAggr(Платежи (Факт).Количество платежей by [ Календарь.Месяц (Дата), Партнер.ИИН, Партнер.Активен - 1 мес, Партнер.Активен - 3 мес, Партнер.Наименование, Партнер.ТОО ИП, Партнер.Тип партнера] )]. [nQSError: 14081] You may be able to evaluate this query if you remove one of the following column references: Календарь.Месяц (Дата), Партнер.Активен - 1 мес, Партнер.Активен - 3 мес, Партнер.Наименование, Партнер.ТОО ИП, Партнер.Тип партнера.
[2019-01-15T10:48:28.752+06:00] [OBIS] [TRACE:7] [] [] [ecid: 54d5c90a-b481-41da-8e21-8e3d9cfbf877-00008845,0:1:1:5] [sik: ssi] [tid: d1da7700] [messageid: USER-48] [requestid: e3920008] [sessionid: e3920000] [username: sharkov_32744] -------------------- The logical query block failed to hit or seed the cache in subrequest level due to [[
only one subrequest
]]
[2019-01-15T10:48:28.752+06:00] [OBIS] [TRACE:7] [] [] [ecid: 54d5c90a-b481-41da-8e21-8e3d9cfbf877-00008845,0:1:1:5] [sik: ssi] [tid: d1da7700] [messageid: USER-53] [requestid: e3920008] [sessionid: e3920000] [username: sharkov_32744] -------------------- List of attributes and their LTS sources: [[
Select: Платежи.ID: Logical table sources (Priority=0, SystemGenerated=False): Платежи.N_KASPI_BIZ_OP_PAY_F
Select: Партнер.ИИН: Logical table sources (Priority=0, SystemGenerated=False): Партнер.N_KASPI_BIZ_PARTNER_BY_ACC_D
Select: Календарь.Месяц (Дата): Logical table sources (Priority=0, SystemGenerated=False): Календарь.N_KASPI_BIZ_CALENDAR_MONTH_D
Where: Календарь.Месяц (Дата): Logical table sources (Priority=0, SystemGenerated=False): Календарь.N_KASPI_BIZ_CALENDAR_MONTH_D
Select: Счета.Номер счета: Logical table sources (Priority=0, SystemGenerated=False): Счета.N_KASPI_BIZ_ACC_D
Select: Партнер.Активен - 1 мес: Logical table sources (Priority=0, SystemGenerated=False): Партнер.N_KASPI_BIZ_PARTNER_BY_ACC_D
Select: Партнер.Активен - 3 мес: Logical table sources (Priority=0, SystemGenerated=False): Партнер.N_KASPI_BIZ_PARTNER_BY_ACC_D
Select: Партнер.Наименование: Logical table sources (Priority=0, SystemGenerated=False): Партнер.N_KASPI_BIZ_PARTNER_BY_ACC_D
Select: Партнер.ТОО ИП: Logical table sources (Priority=0, SystemGenerated=False): Партнер.N_KASPI_BIZ_PARTNER_BY_ACC_D
Select: Партнер.Тип партнера: Logical table sources (Priority=0, SystemGenerated=False): Партнер.N_KASPI_BIZ_PARTNER_BY_ACC_D
]]
[2019-01-15T10:48:28.756+06:00] [OBIS] [TRACE:7] [] [] [ecid: 54d5c90a-b481-41da-8e21-8e3d9cfbf877-00008845,0:1:1:5] [sik: ssi] [tid: d1da7700] [messageid: USER-51] [requestid: e3920008] [sessionid: e3920000] [username: sharkov_32744] -------------------- The logical plan contains non-cacheable table or expression: DM_KASPI_BIZ_ACC AS N_KASPI_BIZ_ACC_D [[
plan
RqStats
RqBreakFilter <<94534889>>[2,3,4,5,6,7,8]
RqList <<94534534>>
0 as c1 GB,
D1.c6 as c2 GB,
D1.c7 as c3 GB,
D1.c8 as c4 GB,
D1.c3 as c5 GB,
D1.c9 as c6 GB,
D1.c10 as c7 GB,
D1.c11 as c8 GB,
cast(NULL as INTEGER ) as c9 GB,
D1.c5 as c10 GB,
D1.c4 as c11 GB,
D1.c1 as c12 GB,
D1.c2 as c13 GB
Child Nodes (RqJoinSpec): <<94534872>>
RqJoinNode <<94534871>> []
(
RqList <<94534541>>
count(distinct N_KASPI_BIZ_OP_PAY_F.ID by [ N_KASPI_BIZ_PARTNER_BY_ACC_D.INN] ) as c1 GB,
count(distinct N_KASPI_BIZ_ACC_D.KBA_ACC_NUMBER by [ N_KASPI_BIZ_PARTNER_BY_ACC_D.INN] ) as c2 GB,
N_KASPI_BIZ_PARTNER_BY_ACC_D.INN as c3 GB,
avg(N_KASPI_BIZ_ACC_D.KBA_ACC_AVG_M_REST_NE by [ N_KASPI_BIZ_CALENDAR_MONTH_D.D_M, N_KASPI_BIZ_PARTNER_BY_ACC_D.INN, N_KASPI_BIZ_PARTNER_BY_ACC_D.IS_ACTIVE_1M, N_KASPI_BIZ_PARTNER_BY_ACC_D.IS_ACTIVE_3M, N_KASPI_BIZ_PARTNER_BY_ACC_D.NAME, N_KASPI_BIZ_PARTNER_BY_ACC_D.TOO_IP, N_KASPI_BIZ_PARTNER_BY_ACC_D.TYPE_PARTNER] ) as c4 GB,
sum(N_KASPI_BIZ_ACC_D.KBA_ACC_REST_NE by [ N_KASPI_BIZ_CALENDAR_MONTH_D.D_M, N_KASPI_BIZ_PARTNER_BY_ACC_D.INN, N_KASPI_BIZ_PARTNER_BY_ACC_D.IS_ACTIVE_1M, N_KASPI_BIZ_PARTNER_BY_ACC_D.IS_ACTIVE_3M, N_KASPI_BIZ_PARTNER_BY_ACC_D.NAME, N_KASPI_BIZ_PARTNER_BY_ACC_D.TOO_IP, N_KASPI_BIZ_PARTNER_BY_ACC_D.TYPE_PARTNER] ) as c5 GB,
N_KASPI_BIZ_CALENDAR_MONTH_D.D_M as c6 GB,
N_KASPI_BIZ_PARTNER_BY_ACC_D.IS_ACTIVE_1M as c7 GB,
N_KASPI_BIZ_PARTNER_BY_ACC_D.IS_ACTIVE_3M as c8 GB,
N_KASPI_BIZ_PARTNER_BY_ACC_D.NAME as c9 GB,
N_KASPI_BIZ_PARTNER_BY_ACC_D.TYPE_PARTNER as c10 GB,
N_KASPI_BIZ_PARTNER_BY_ACC_D.TOO_IP as c11 GB
Child Nodes (RqJoinSpec): <<94534820>>
RqJoinNode <<94534816>> [(InNode:<<94534816>>) (OutNode:<<94534818>>) , (InNode:<<94534816>>) (OutNode:<<94534819>>) ]
(select kbop_id_op id,
kbop_id_client_ibso,
kbop_id_client_dwh,
kbop_id_client_inn,
kbop_type_op_src,
kbop_date_op,
kbop_deb_acc_number,
kbop_deb_acc_bal,
kbop_cre_acc_number,
kbop_cre_acc_bal,
kbop_sum_ne,
kbop_rep_date,
kbop_chg_date,
kbop_audit_id,
kbop_type,
kbop_val,
kbop_profit,
'PAY' type_op,
kbop_knp,
kbop_is_self_pay,
kbop_cre_bank_bic,
kbop_cre_bank_name,
substr(KBOP_CRE_ACC_BAL, 1, 4) CRE_ACC_BAL4,
substr(KBOP_DEB_ACC_BAL, 1, 4) DEB_ACC_BAL4,
kbop_cre_bank_iin,
trunc( kbop_date_op,'mm') m
from dwh_san.DM_KASPI_BIZ_OP_PAY
) as T512961 On N_KASPI_BIZ_CALENDAR_MONTH_D.D_M = N_KASPI_BIZ_OP_PAY_F.M and N_KASPI_BIZ_ACC_D.KBA_ACC_NUMBER = N_KASPI_BIZ_OP_PAY_F.KBOP_DEB_ACC_NUMBER and N_KASPI_BIZ_ACC_D.KBA_REP_DATE = N_KASPI_BIZ_OP_PAY_F.M
RqJoinNode <<94534817>> [(InNode:<<94534817>>) (OutNode:<<94534818>>) , (InNode:<<94534817>>) (OutNode:<<94534819>>) ]
(select kba_rep_date rep_date,
kba_id_client_inn inn,
kba_id_client_name name,
kba_type_partner type_partner,
a.kba_client_too_ip too_ip,
max(a.kba_acc_is_active) is_active_3m,
max(a.kba_acc_is_active_1m) is_active_1m,
max(a.kba_is_in_kb) is_in_kb
from dwh_san.dm_kaspi_biz_acc a
where kba_id_client_name is not null
group by kba_rep_date,
kba_id_client_inn,
kba_id_client_name,
kba_type_partner,
a.kba_client_too_ip
) as T528287 On N_KASPI_BIZ_CALENDAR_MONTH_D.D_M = N_KASPI_BIZ_PARTNER_BY_ACC_D.REP_DATE and N_KASPI_BIZ_ACC_D.KBA_ID_CLIENT_INN = N_KASPI_BIZ_PARTNER_BY_ACC_D.INN and N_KASPI_BIZ_ACC_D.KBA_REP_DATE = N_KASPI_BIZ_PARTNER_BY_ACC_D.REP_DATE
RqJoinNode <<94534818>> [(InNode:<<94534816>>) (OutNode:<<94534818>>) , (InNode:<<94534817>>) (OutNode:<<94534818>>) , (InNode:<<94534818>>) (OutNode:<<94534819>>) ]
(select add_months(trunc(sysdate,'mm'),-level+1) d_m, trunc(add_months(trunc(sysdate,'mm'),-level+1),'yyyy') d_y from dual connect by level<12*4) as T512831 On N_KASPI_BIZ_ACC_D.KBA_REP_DATE = N_KASPI_BIZ_CALENDAR_MONTH_D.D_M
RqJoinNode <<94534819>> [(InNode:<<94534816>>) (OutNode:<<94534819>>) , (InNode:<<94534817>>) (OutNode:<<94534819>>) , (InNode:<<94534818>>) (OutNode:<<94534819>>) ]
DM_KASPI_BIZ_ACC AS N_KASPI_BIZ_ACC_D
DetailFilter: N_KASPI_BIZ_ACC_D.KBA_REP_DATE = DATE '2018-11-01' and N_KASPI_BIZ_CALENDAR_MONTH_D.D_M = DATE '2018-11-01'
) as D1
OrderBy: c5 asc NULLS LAST, c2 asc NULLS LAST, c6 asc NULLS LAST, c8 asc NULLS LAST, c3 asc NULLS LAST, c4 asc NULLS LAST, c7 asc NULLS LAST
]]
[2019-01-15T10:48:28.768+06:00] [OBIS] [TRACE:7] [] [] [ecid: 54d5c90a-b481-41da-8e21-8e3d9cfbf877-00008845,0:1:1:5] [sik: ssi] [tid: d1da7700] [messageid: USER-50] [requestid: e3920008] [sessionid: e3920000] [username: sharkov_32744] -------------------- The logical query disqualifies the plan cache [[
plan
]]
[2019-01-15T10:48:28.769+06:00] [OBIS] [TRACE:7] [] [] [ecid: 54d5c90a-b481-41da-8e21-8e3d9cfbf877-00008845,0:1:1:5] [sik: ssi] [tid: d1da7700] [messageid: USER-16] [requestid: e3920008] [sessionid: e3920000] [username: sharkov_32744] -------------------- Execution plan: [[
RqStats [for database 0:0,0]
RqBreakFilter <<94534889>>[2,3,4,5,6,7,8] [for database 0:0,0] /* FETCH FIRST 6500001 ROWS ONLY */
RqList <<94535048>> [for database 0:0,0]
D1.c1 as c1 [for database 3023:135516:DWH_PROD,74],
D1.c2 as c2 [for database 3023:135516:DWH_PROD,74],
D1.c3 as c3 [for database 3023:135516:DWH_PROD,74],
D1.c4 as c4 [for database 3023:135516:DWH_PROD,74],
D1.c5 as c5 [for database 3023:135516:DWH_PROD,74],
D1.c6 as c6 [for database 3023:135516:DWH_PROD,74],
D1.c7 as c7 [for database 3023:135516:DWH_PROD,74],
D1.c8 as c8 [for database 3023:135516:DWH_PROD,74],
D1.c9 as c9 [for database 3023:135516:DWH_PROD,74],
D1.c10 as c10 [for database 3023:135516:DWH_PROD,74],
D1.c11 as c11 [for database 3023:135516:DWH_PROD,74],
D1.c12 as c12 [for database 3023:135516:DWH_PROD,74],
D1.c13 as c13 [for database 3023:135516:DWH_PROD,74]
Child Nodes (RqJoinSpec): <<94535093>> [for database 3023:135516:DWH_PROD,74]
RqJoinNode <<94535092>> []
(
RqList <<94534534>> [for database 3023:135516:DWH_PROD,74]
0 as c1 GB [for database 3023:135516:DWH_PROD,74],
D1.c6 as c2 GB [for database 3023:135516:DWH_PROD,74],
D1.c7 as c3 GB [for database 3023:135516:DWH_PROD,74],
D1.c8 as c4 GB [for database 3023:135516:DWH_PROD,74],
D1.c3 as c5 GB [for database 3023:135516:DWH_PROD,74],
D1.c9 as c6 GB [for database 3023:135516:DWH_PROD,74],
D1.c10 as c7 GB [for database 3023:135516:DWH_PROD,74],
D1.c11 as c8 GB [for database 3023:135516:DWH_PROD,74],
cast(NULL as INTEGER ) as c9 GB [for database 3023:135516:DWH_PROD,74],
D1.c5 as c10 GB [for database 3023:135516:DWH_PROD,74],
D1.c4 as c11 GB [for database 3023:135516:DWH_PROD,74],
D1.c1 as c12 GB [for database 3023:135516:DWH_PROD,74],
D1.c2 as c13 GB [for database 3023:135516:DWH_PROD,74]
Child Nodes (RqJoinSpec): <<94534872>> [for database 3023:135516:DWH_PROD,74]
RqJoinNode <<94534871>> []
(
RqList <<94534963>> [for database 3023:135516:DWH_PROD,74]
sum_SQL99(D1.c1 by [ D1.c3] ) as c1 [for database 3023:135516:DWH_PROD,74],
sum_SQL99(D1.c2 by [ D1.c3] ) as c2 [for database 3023:135516:DWH_PROD,74],
D1.c3 as c3 [for database 3023:135516:DWH_PROD,74],
sum_SQL99(D1.c13 by [ D1.c6, D1.c3, D1.c7, D1.c8, D1.c9, D1.c11, D1.c10] ) / sum_SQL99(D1.c12 by [ D1.c6, D1.c3, D1.c7, D1.c8, D1.c9, D1.c11, D1.c10] ) as c4 [for database 3023:135516:DWH_PROD,74],
sum_SQL99(D1.c5 by [ D1.c6, D1.c3, D1.c7, D1.c8, D1.c9, D1.c11, D1.c10] ) as c5 [for database 3023:135516:DWH_PROD,74],
D1.c6 as c6 [for database 3023:135516:DWH_PROD,74],
D1.c7 as c7 [for database 3023:135516:DWH_PROD,74],
D1.c8 as c8 [for database 3023:135516:DWH_PROD,74],
D1.c9 as c9 [for database 3023:135516:DWH_PROD,74],
D1.c10 as c10 [for database 3023:135516:DWH_PROD,74],
D1.c11 as c11 [for database 3023:135516:DWH_PROD,74]
Child Nodes (RqJoinSpec): <<94535002>> [for database 3023:135516:DWH_PROD,74]
RqJoinNode <<94535001>> []
(
RqList <<94535122>> [for database 3023:135516:DWH_PROD,74]
count(distinct case D1.c15 when 1 then D1.c14 else NULL end by [ D1.c6, D1.c10, D1.c11, D1.c3, D1.c9, D1.c7, D1.c8] ) as c1 [for database 3023:135516:DWH_PROD,74],
count(distinct case D1.c17 when 1 then D1.c16 else NULL end by [ D1.c6, D1.c10, D1.c11, D1.c3, D1.c9, D1.c7, D1.c8] ) as c2 [for database 3023:135516:DWH_PROD,74],
D1.c3 as c3 [for database 3023:135516:DWH_PROD,74],
sum(D1.c18 by [ D1.c6, D1.c10, D1.c11, D1.c3, D1.c9, D1.c7, D1.c8] ) as c5 [for database 3023:135516:DWH_PROD,74],
D1.c6 as c6 [for database 3023:135516:DWH_PROD,74],
D1.c7 as c7 [for database 3023:135516:DWH_PROD,74],
D1.c8 as c8 [for database 3023:135516:DWH_PROD,74],
D1.c9 as c9 [for database 3023:135516:DWH_PROD,74],
D1.c10 as c10 [for database 3023:135516:DWH_PROD,74],
D1.c11 as c11 [for database 3023:135516:DWH_PROD,74],
count(D1.c19 by [ D1.c6, D1.c10, D1.c11, D1.c3, D1.c9, D1.c7, D1.c8] ) as c12 [for database 3023:135516:DWH_PROD,74],
sum(D1.c19 by [ D1.c6, D1.c10, D1.c11, D1.c3, D1.c9, D1.c7, D1.c8] ) as c13 [for database 3023:135516:DWH_PROD,74]
Child Nodes (RqJoinSpec): <<94535164>> [for database 3023:135516:DWH_PROD,74]
RqJoinNode <<94535163>> []
(
RqList <<94534541>> [for database 3023:135516:DWH_PROD,74]
N_KASPI_BIZ_PARTNER_BY_ACC_D.INN as c3 GB [for database 3023:135516:DWH_PROD,74],
N_KASPI_BIZ_CALENDAR_MONTH_D.D_M as c6 GB [for database 3023:135516:DWH_PROD,74],
N_KASPI_BIZ_PARTNER_BY_ACC_D.IS_ACTIVE_1M as c7 GB [for database 3023:135516:DWH_PROD,74],
N_KASPI_BIZ_PARTNER_BY_ACC_D.IS_ACTIVE_3M as c8 GB [for database 3023:135516:DWH_PROD,74],
N_KASPI_BIZ_PARTNER_BY_ACC_D.NAME as c9 GB [for database 3023:135516:DWH_PROD,74],
N_KASPI_BIZ_PARTNER_BY_ACC_D.TYPE_PARTNER as c10 GB [for database 3023:135516:DWH_PROD,74],
N_KASPI_BIZ_PARTNER_BY_ACC_D.TOO_IP as c11 GB [for database 3023:135516:DWH_PROD,74],
N_KASPI_BIZ_OP_PAY_F.ID as c14 [for database 3023:135516:DWH_PROD,74],
rownum(1 by [ N_KASPI_BIZ_PARTNER_BY_ACC_D.INN, N_KASPI_BIZ_OP_PAY_F.ID] at_distinct [ N_KASPI_BIZ_PARTNER_BY_ACC_D.INN, N_KASPI_BIZ_OP_PAY_F.ID] ) as c15 [for database 3023:135516:DWH_PROD,74],
N_KASPI_BIZ_ACC_D.KBA_ACC_NUMBER as c16 [for database 3023:135516:DWH_PROD,74],
rownum(1 by [ N_KASPI_BIZ_PARTNER_BY_ACC_D.INN, N_KASPI_BIZ_ACC_D.KBA_ACC_NUMBER] at_distinct [ N_KASPI_BIZ_PARTNER_BY_ACC_D.INN, N_KASPI_BIZ_ACC_D.KBA_ACC_NUMBER] ) as c17 [for database 3023:135516:DWH_PROD,74],
N_KASPI_BIZ_ACC_D.KBA_ACC_REST_NE as c18 [for database 3023:135516:DWH_PROD,74],
N_KASPI_BIZ_ACC_D.KBA_ACC_AVG_M_REST_NE as c19 [for database 3023:135516:DWH_PROD,74]
Child Nodes (RqJoinSpec): <<94534820>> [for database 3023:135516:DWH_PROD,74]
RqJoinNode <<94534816>> [(InNode:<<94534816>>) (OutNode:<<94534818>>) , (InNode:<<94534816>>) (OutNode:<<94534819>>) ]
(select kbop_id_op id,
kbop_id_client_ibso,
kbop_id_client_dwh,
kbop_id_client_inn,
kbop_type_op_src,
kbop_date_op,
kbop_deb_acc_number,
kbop_deb_acc_bal,
kbop_cre_acc_number,
kbop_cre_acc_bal,
kbop_sum_ne,
kbop_rep_date,
kbop_chg_date,
kbop_audit_id,
kbop_type,
kbop_val,
kbop_profit,
'PAY' type_op,
kbop_knp,
kbop_is_self_pay,
kbop_cre_bank_bic,
kbop_cre_bank_name,
substr(KBOP_CRE_ACC_BAL, 1, 4) CRE_ACC_BAL4,
substr(KBOP_DEB_ACC_BAL, 1, 4) DEB_ACC_BAL4,
kbop_cre_bank_iin,
trunc( kbop_date_op,'mm') m
from dwh_san.DM_KASPI_BIZ_OP_PAY
) as T512961
RqJoinNode <<94534817>> [(InNode:<<94534817>>) (OutNode:<<94534818>>) , (InNode:<<94534817>>) (OutNode:<<94534819>>) ]
(select kba_rep_date rep_date,
kba_id_client_inn inn,
kba_id_client_name name,
kba_type_partner type_partner,
a.kba_client_too_ip too_ip,
max(a.kba_acc_is_active) is_active_3m,
max(a.kba_acc_is_active_1m) is_active_1m,
max(a.kba_is_in_kb) is_in_kb
from dwh_san.dm_kaspi_biz_acc a
where kba_id_client_name is not null
group by kba_rep_date,
kba_id_client_inn,
kba_id_client_name,
kba_type_partner,
a.kba_client_too_ip
) as T528287
RqJoinNode <<94534818>> [(InNode:<<94534816>>) (OutNode:<<94534818>>) , (InNode:<<94534817>>) (OutNode:<<94534818>>) , (InNode:<<94534818>>) (OutNode:<<94534819>>) ]
(select add_months(trunc(sysdate,'mm'),-level+1) d_m, trunc(add_months(trunc(sysdate,'mm'),-level+1),'yyyy') d_y from dual connect by level<12*4) as T512831
RqJoinNode <<94534819>> [(InNode:<<94534816>>) (OutNode:<<94534819>>) , (InNode:<<94534817>>) (OutNode:<<94534819>>) , (InNode:<<94534818>>) (OutNode:<<94534819>>) ]
DM_KASPI_BIZ_ACC AS N_KASPI_BIZ_ACC_D
DetailFilter: N_KASPI_BIZ_ACC_D.KBA_ACC_NUMBER = N_KASPI_BIZ_OP_PAY_F.KBOP_DEB_ACC_NUMBER and N_KASPI_BIZ_ACC_D.KBA_REP_DATE = N_KASPI_BIZ_OP_PAY_F.M and N_KASPI_BIZ_ACC_D.KBA_REP_DATE = N_KASPI_BIZ_CALENDAR_MONTH_D.D_M and N_KASPI_BIZ_ACC_D.KBA_REP_DATE = DATE '2018-11-01' and N_KASPI_BIZ_CALENDAR_MONTH_D.D_M = N_KASPI_BIZ_OP_PAY_F.M and N_KASPI_BIZ_CALENDAR_MONTH_D.D_M = N_KASPI_BIZ_PARTNER_BY_ACC_D.REP_DATE and N_KASPI_BIZ_ACC_D.KBA_ID_CLIENT_INN = N_KASPI_BIZ_PARTNER_BY_ACC_D.INN and N_KASPI_BIZ_ACC_D.KBA_REP_DATE = N_KASPI_BIZ_PARTNER_BY_ACC_D.REP_DATE and N_KASPI_BIZ_CALENDAR_MONTH_D.D_M = DATE '2018-11-01' [for database 3023:135516:DWH_PROD,74]
) as D1
GroupBy: [ D1.c3, D1.c6, D1.c7, D1.c8, D1.c9, D1.c10, D1.c11] [for database 3023:135516:DWH_PROD,74]
) as D1
) as D1
) as D1
OrderBy: c5 asc NULLS LAST, c2 asc NULLS LAST, c6 asc NULLS LAST, c8 asc NULLS LAST, c3 asc NULLS LAST, c4 asc NULLS LAST, c7 asc NULLS LAST [for database 0:0,0]
]]
[2019-01-15T10:48:28.772+06:00] [OBIS] [TRACE:7] [] [] [ecid: 54d5c90a-b481-41da-8e21-8e3d9cfbf877-00008845,0:1:1:5] [sik: ssi] [tid: d1da7700] [messageid: USER-18] [requestid: e3920008] [sessionid: e3920000] [username: sharkov_32744] -------------------- Sending query to database named DWH_PROD (id: <<94534534>>), connection pool named DWH_BI, logical request hash cd65f190, physical request hash 581f15ee: [[
select distinct 0 as c1,
D1.c6 as c2,
D1.c7 as c3,
D1.c8 as c4,
D1.c3 as c5,
D1.c9 as c6,
D1.c10 as c7,
D1.c11 as c8,
(case when NULL > 2147483647 or NULL < -2147483648 then cast (NULL as number(9,0)) else cast (NULL as integer) end ) as c9,
D1.c5 as c10,
D1.c4 as c11,
D1.c1 as c12,
D1.c2 as c13
from
(select sum(D1.c1) over (partition by D1.c3) as c1,
sum(D1.c2) over (partition by D1.c3) as c2,
D1.c3 as c3,
sum(D1.c13) over (partition by D1.c6, D1.c3, D1.c7, D1.c8, D1.c9, D1.c11, D1.c10) / sum(D1.c12) over (partition by D1.c6, D1.c3, D1.c7, D1.c8, D1.c9, D1.c11, D1.c10) as c4,
sum(D1.c5) over (partition by D1.c6, D1.c3, D1.c7, D1.c8, D1.c9, D1.c11, D1.c10) as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8,
D1.c9 as c9,
D1.c10 as c10,
D1.c11 as c11
from
(select count(distinct case D1.c15 when 1 then D1.c14 else NULL end ) as c1,
count(distinct case D1.c17 when 1 then D1.c16 else NULL end ) as c2,
D1.c3 as c3,
sum(D1.c18) as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8,
D1.c9 as c9,
D1.c10 as c10,
D1.c11 as c11,
count(D1.c19) as c12,
sum(D1.c19) as c13
from
(select T528287.INN as c3,
T512831.D_M as c6,
T528287.IS_ACTIVE_1M as c7,
T528287.IS_ACTIVE_3M as c8,
T528287.NAME as c9,
T528287.TYPE_PARTNER as c10,
T528287.TOO_IP as c11,
T512961.ID as c14,
ROW_NUMBER() OVER (PARTITION BY T528287.INN, T512961.ID ORDER BY T528287.INN DESC, T512961.ID DESC) as c15,
T512619.KBA_ACC_NUMBER as c16,
ROW_NUMBER() OVER (PARTITION BY T528287.INN, T512619.KBA_ACC_NUMBER ORDER BY T528287.INN DESC, T512619.KBA_ACC_NUMBER DESC) as c17,
T512619.KBA_ACC_REST_NE as c18,
T512619.KBA_ACC_AVG_M_REST_NE as c19
from
(select kbop_id_op id,
kbop_id_client_ibso,
kbop_id_client_dwh,
kbop_id_client_inn,
kbop_type_op_src,
kbop_date_op,
kbop_deb_acc_number,
kbop_deb_acc_bal,
kbop_cre_acc_number,
kbop_cre_acc_bal,
kbop_sum_ne,
kbop_rep_date,
kbop_chg_date,
kbop_audit_id,
kbop_type,
kbop_val,
kbop_profit,
'PAY' type_op,
kbop_knp,
kbop_is_self_pay,
kbop_cre_bank_bic,
kbop_cre_bank_name,
substr(KBOP_CRE_ACC_BAL, 1, 4) CRE_ACC_BAL4,
substr(KBOP_DEB_ACC_BAL, 1, 4) DEB_ACC_BAL4,
kbop_cre_bank_iin,
trunc( kbop_date_op,'mm') m
from dwh_san.DM_KASPI_BIZ_OP_PAY
) T512961,
(select kba_rep_date rep_date,
kba_id_client_inn inn,
kba_id_client_name name,
kba_type_partner type_partner,
a.kba_client_too_ip too_ip,
max(a.kba_acc_is_active) is_active_3m,
max(a.kba_acc_is_active_1m) is_active_1m,
max(a.kba_is_in_kb) is_in_kb
from dwh_san.dm_kaspi_biz_acc a
where kba_id_client_name is not null
group by kba_rep_date,
kba_id_client_inn,
kba_id_client_name,
kba_type_partner,
a.kba_client_too_ip
) T528287,
(select add_months(trunc(sysdate,'mm'),-level+1) d_m, trunc(add_months(trunc(sysdate,'mm'),-level+1),'yyyy') d_y from dual connect by level<12*4) T512831,
DWH_SAN.DM_KASPI_BIZ_ACC T512619 /* N_KASPI_BIZ_ACC_D */
where ( T512619.KBA_ACC_NUMBER = T512961.KBOP_DEB_ACC_NUMBER and T512619.KBA_REP_DATE = T512961.M and T512619.KBA_REP_DATE = T512831.D_M and T512619.KBA_REP_DATE = TO_DATE('2018-11-01' , 'YYYY-MM-DD') and T512831.D_M = T512961.M and T512831.D_M = T528287.REP_DATE and T512619.KBA_ID_CLIENT_INN = T528287.INN and T512619.KBA_REP_DATE = T528287.REP_DATE and T512831.D_M = TO_DATE('2018-11-01' , 'YYYY-MM-DD') )
) D1
group by D1.c3, D1.c6, D1.c7, D1.c8, D1.c9, D1.c10, D1.c11
) D1
) D1
0 -
So i make analysis with mix Client, Account, Payments - usually business want everything
When i count payments by formula - all ok when i count payments from measure of Payment _Fact -it's not working. 0 -
Many formulas on Payments is just calculated from Addons - I will remove it. I can include it in DataMart of payment. Is't help me?
0
