Using informatica for a set of reports, should you try to have only one fact table. I have more than one and I am having trouble doing calculated fields where it uses fields from each table. I am thinking I should have put everyting into one fact table. Has anyone else had this problem?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
More than one fact table
- Thread starter julianbar
- Start date
Hard to tell without an example. Storing data into one facttable means having a equal set of dimensions anyway. What kind of difficulties are you experiencing with doing calculations anyway?
T. Blom
Information analyst
tbl@shimano-eu.com
T. Blom
Information analyst
tbl@shimano-eu.com
- Thread starter
- #3
Here is an example.
There is a fact table(1) with data pertaining to expenditures. In this table there is a column called budgetid which links to a budget table. There is also a column called end_date which links to a calendar.
There is another fact table(2) with data pertaining to budget dollars for certain line items. The key field is budgetid.
There is a calendar dimension table. The key field is end_date.
In a report if I try to incorporate data from all the tables I get the message that the dimensions in the calendar table are not appropriate dimensions for the data in the budget table.
I would like to see in a report these columns:
end_date (comes from fact table(1))
quarter (comes from dimension table)
Days Elapsed (comes from dimension table)
Dollars Expended (comes from fact table(1))
Budget (comes from fact table(2))
I would appreciate anyone's help. Thank you.
There is a fact table(1) with data pertaining to expenditures. In this table there is a column called budgetid which links to a budget table. There is also a column called end_date which links to a calendar.
There is another fact table(2) with data pertaining to budget dollars for certain line items. The key field is budgetid.
There is a calendar dimension table. The key field is end_date.
In a report if I try to incorporate data from all the tables I get the message that the dimensions in the calendar table are not appropriate dimensions for the data in the budget table.
I would like to see in a report these columns:
end_date (comes from fact table(1))
quarter (comes from dimension table)
Days Elapsed (comes from dimension table)
Dollars Expended (comes from fact table(1))
Budget (comes from fact table(2))
I would appreciate anyone's help. Thank you.
Since you are talking about getting a message about incompatible dimensions I digest you are using a certain kind of reporting tool. To tackle this I would suggest posting this problem in the relevant forum, which should give you lots of feedback from specialists. It sounds like a non-specific informatica problem.
From what you are telling there seems little problem in creating one facttable.
My suggestion: Create a view over both tables before altering the table structures and use the view as source. Does this do the trick?
T. Blom
Information analyst
tbl@shimano-eu.com
From what you are telling there seems little problem in creating one facttable.
My suggestion: Create a view over both tables before altering the table structures and use the view as source. Does this do the trick?
T. Blom
Information analyst
tbl@shimano-eu.com
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question