I am working with a system, which has 4 databases:
I want to create 4 facts tables, one fact table for each database... for example, I will have an Account Fact table with ClientAccount, Transaction, Provider as its dimension table. I will have 3 similar Fact Tables for other databases.
My Question is: does it make sense to include each corresponding fact table in that database? i.e. Create Accounting Fact and Dimension tables in the Account database? Or is it a better to create a new database for all of our star schema, and include all the dimension and fact tables in their own database?
Unless your data volume is very small, your data warehouse should be housed in a separate database from the transactional data. A DW has a different usage pattern (OLTP vs OLAP) and will generally have a different maintenance window.
I would recommend creating all of your Dims and Facts in a single dedicated DW database. I can't think of any benefit to separating them and it would reduce your DBA overhead by not having extra databases to manage/secure/audit/document.
As for Dimensions vs Facts, data from the OLTP Account table would be used to create a Dim and a Fact. DimAccount at the very least would be a degenerate dimension containing just the account number. You'd have to review your data to determine if any of the other records are generic attributes of the Account specifically. FactAccount would contain references to the other Dimensions (DimAccountType, DimCustomer, DimLocation, etc)
Think of the dimensions as the values from lookup tables/dropdown lists, which exist prior to any events happening. For example, a bank can offer Checking & Savings accounts, even if they do not yet have any accounts.
Facts document an event. When an account is created, the fact record will reference all of the dimensions that describe the event, and record the measurable values associated with the event, if any.