Can you advise with it? - Page 3 — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Can you advise with it?

Received Response
252
Views
47
Comments
135

Answers

  • Alex Sharkov
    Alex Sharkov Rank 5 - Community Champion

    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

  • Alex Sharkov
    Alex Sharkov Rank 5 - Community Champion

    My phisical layer

    pastedImage_0.png

    LINKS

    pastedImage_2.png

    pastedImage_3.png

    pastedImage_4.png

    pastedImage_5.png

    pastedImage_6.png

    BMM

    pastedImage_1.png

    BMM_HIERARCHY.jpgPAYMENT_FACT_CONTENT.jpg

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    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!

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    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.

  • Alex Sharkov
    Alex Sharkov Rank 5 - Community Champion

    It's additional attribute for CLIENT.

    Dees it impact on SQL generation between?

    pastedImage_1.png

    So i remove it join, but nothing change

  • Alex Sharkov
    Alex Sharkov Rank 5 - Community Champion

    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.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    And what error are you currently getting?  (you are my hands and eyes!)

  • Alex Sharkov
    Alex Sharkov Rank 5 - Community Champion

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

  • Alex Sharkov
    Alex Sharkov Rank 5 - Community Champion

    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.

  • Alex Sharkov
    Alex Sharkov Rank 5 - Community Champion

    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?