10 Replies Latest reply on May 12, 2015 10:39 AM by Prasanna M

    Reg. Child Fact Table in BMMI Layer

    Prasanna M

      Hi all,

       

      I have a main fact table. 'CUSTOMER ACCOUNT DETAILS' which has account details of all the customers with the current day balance.

      There is another table 'ACCOUNT BALANCE HISTORY' which stores the historical day-wise balances for these accounts.

       

      When I tried to bring in the second table to the BMMI layer, and joined with 'CUSTOMER ACCOUNT DETAILS', the new table 'ACCOUNT BALANCE HISTORY' has become the fact table. and the original fact table 'CUSTOMER ACCOUNT DETAILS' has become dimension table which is actually a fact table and is joined with many other dimensions, like, account_type, branch, currency, etc..,

       

      can anyone please suggest, how to have these two tables as FACT TABLE in order to make my BM consistent without any warning.

        • 1. Re: Reg. Child Fact Table in BMMI Layer
          Raja_gopal

          Hi,

           

          Can you explain physical structure of the model.

           

          1.what are the dimension joined to 'CUSTOMER ACCOUNT DETAILS' and 'ACCOUNT BALANCE HISTORY'?

          2. is 'CUSTOMER ACCOUNT DETAILS' and 'ACCOUNT BALANCE HISTORY'  having physical join.

           

          Regards

          Rajagopal

          • 2. Re: Reg. Child Fact Table in BMMI Layer
            Prasanna M

            Hi Rajagopal,

             

             

            Thanks for the response.

            1) dimension joined to 'CUSTOMER ACCOUNT DETAILS'

              a) BRANCH (branch to which the accounts belong)

              b) CURRENCY (currencies on which the accounts operate)

              c) ACCOUNT CLASS (The product to which the accounts belong)

             

            2) Yes. CUSTOMER ACCOUNT DETAILS' and 'ACCOUNT BALANCE HISTORY have physical join. They both join by ACCOUNT NUMBER.

             

            Please let me know if u need any other clarification.

            • 3. Re: Reg. Child Fact Table in BMMI Layer
              Raja_gopal

              hi,

              let me assume  that 'ACCOUNT BALANCE HISTORY' also having the same dimensions.

               

              1.existing model will keep CUSTOMER ACCOUNT DETAILS as fact with branch,currency,account class dimensions

              2. create an alias of CUSTOMER ACCOUNT DETAILS as CUSTOMER ACCOUNT DETAILS_dim then join with ACCOUNT BALANCE HISTORY_fact.

              ACCOUNT BALANCE HISTORY_fact will have  branch,currency,account class dimensions also CUSTOMER ACCOUNT DETAILS_dim

               

              hope this will help

               

              Regards

              Rajagopal

               

               

              • 4. Re: Reg. Child Fact Table in BMMI Layer
                Prasanna M

                Hi,

                Actually, CUSTOMER ACCOUNT DETAILS will have all the dimension columns. and have approximately 5000 accounts.

                and ACCOUNT BALANCE HISTORY will have only three columns 'ACCOUNT NUMBER', 'DATE', 'BALANCE. Since, for each account, there can be 30 records in a month, I am not intended to increase the columns to accomodate all the dimension columns.

                I would like to have dimension columns in the main table CUSTOMER ACCOUNT DETAILS and have day wise balances in ACCOUNT BALANCE HISTORY.

                 

                can you suggest if this is feasible?

                • 5. Re: Reg. Child Fact Table in BMMI Layer
                  Raja_gopal

                  hi,

                   

                  I think you didn't uderstand properly, here the explanation.

                   

                  1.in Physical layer create alias of CUSTOMER ACCOUNT DETAILS  as CUSTOMER ACCOUNT DETAILS _dim

                  2.join CUSTOMER ACCOUNT DETAILS _dim to ACCOUNT BALANCE HISTORY

                  3.pull both the tables in BMM , here ACCOUNT BALANCE HISTORY act as fact and CUSTOMER ACCOUNT DETAILS _dim  will act as dimension.

                   

                  dont change anything in the exiting model.

                   

                  Regards

                  Rajagopal

                   

                   

                   

                   

                  • 6. Re: Reg. Child Fact Table in BMMI Layer
                    Prasanna M

                    Hi Raja,

                     

                    Below is the BM diagram.

                     

                    BMM_Diagram.jpg

                    also, below is the BM objects. As per below, 'Account Balances' have become the Fact now and 'Accounts' which was earlier a Fact, has become a Dim now.

                     

                    BM_Objects.jpg

                    When I performed the consistency check, I get the below warnings.

                    Consistency_Check.jpg


                    In most of the requests, the table 'ACCOUNT' will act as the fact. only when historical account balances are referred, 'ACCOUNT BALANCES' will be acting as the fact.


                    Please let me know if we can accomodate both the tables as FACT.


                    • 7. Re: Reg. Child Fact Table in BMMI Layer
                      Raja_gopal

                      did you try the above steps?

                      from your current BMM model, remove join between Accounts and Account balance,and follow the above steps.

                      when you do this you will have

                      1.one star model for Account_fact with related dimension.

                      2.one snowflake model Account_Balances_fact with Account_dim (with corresponding dimensions)

                      • 8. Re: Reg. Child Fact Table in BMMI Layer
                        Prasanna M

                        The table 'Account Balances' is linked to this BM only by a join with 'Accounts'. If I remove it, it will be detached from the BM.

                        • 9. Re: Reg. Child Fact Table in BMMI Layer
                          Raja_gopal

                          untill you have basic understanding of obiee you can't do this.sorry

                           

                          you can delete the detached from BMM.

                          • 10. Re: Reg. Child Fact Table in BMMI Layer
                            Prasanna M

                            Ok thanks.

                             

                            Let me try and update the post.