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.
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.
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
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?
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.
Below is the BM diagram.
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.
When I performed the consistency check, I get the below warnings.
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.
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)
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.
untill you have basic understanding of obiee you can't do this.sorry
you can delete the detached from BMM.
Let me try and update the post.