Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 17 Oracle Analytics Lounge
- 218 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 81 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
How to model 1 conformed dimension and 2 facts (Facts are kind of snowflake)
Hello All,
We have 3 tables.
Customers
LoanDetails
RepaymentDetails
How i can model this . Suppose i have to generate a report for customer wise loan amount and repayment amount how can i calculate it?
Now for example
Customer Steve got two loans from bank
Home loan - 1000USD
Car loan - 200 USD
in repayment table he has entries for his repayment every month. For example in 2016 he paid 12 times every month.
So now i joined like below
Customer - > Loan Details
Customer ->RepaymentDetails
But when we use sum(loanamount) and sum(repaymentamount) for steve in report it is totalling the 1000USD + 500 USD 12 times ..
Hope my explanation is clear. any help will be highly appreciated.
Thanks
Stv
Answers
-
The proof is in the SQL that obiee is generating. Have a look at the nqquery.log after running your analysis to see what obiee is generating based on your model. Its probably creating some cross join that is why you are seeing the multiplication of records.
regards
Emmanuel
0 -
Hello Emmanuel,
didnt create the join in OBIEE yet. I was executing query only.
I have filters such as payment date , loan date etc in fact tables.
0 -
Can you post the sql query?
0 -
"Facts are kind of snowflake" ... oh no!
"I have filters such as payment date, loan date, etc. in fact tables" ... nope! these go in dimensions ...
Given what you've said in your post your fact star model should look like:
Dim - Date
attributes related to dates: year, month, week, date, fiscal period, same date last year, etc.
Dim - Customers
attributes related to customers: name, phone, address, start date, credit rating etc
Dim - Loans
attributes related to loans: loan number, loan type, etc
Dim - Payments
attributes related to payments: payment number, payment type, tender type, etc
Fact - Customer Loans
GRAIN: customer key, loan key, date key
MEASURE: loan amount
Fact - Customer Loan Payments
GRAIN: customer key, loan key, payment key, date key
MEASURE: payment amount
With a simple structure like the above coupled with logical hierarchy for dates -- you can do all you want and more.
0 -
Thomas Dodds wrote:"Facts are kind of snowflake" ... oh no!"I have filters such as payment date, loan date, etc. in fact tables" ... nope! these go in dimensions ...
That's why I didn't even bother replying ;-)
0 -
.. If i make dimension out of fact it will become non conformed dimensions and will trouble us in reporting layer.
0 -
3295595 wrote:.. If i make dimension out of fact it will become non conformed dimensions and will trouble us in reporting layer.
Care to explain how and why?
0 -
We have to set totals for the non conformed dimension in RPD and when we take sum for the repayment and loan loan value will add up based on number of records in the repayment table for same loan?can it happen?
0 -
If the LTS content setting are correct then the results will be correct.
Attributes in facts are just wrong and only ever acceptable if used to created/calculate facts which don't exist physically.
0 -
If I have to I can create a logical star out of a single physical database table.
A logical star is a must-do for OBIEE ...
0