2

I am working with a system, which has 4 databases:

  • Account (Storing bank accounts, transactions, etc)
  • Client (Client related info)
  • Credit (getting rates from 3rd party system)
  • Quality (Further internal calculation)

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?

Hooman Bahreini
  • 14,480
  • 11
  • 70
  • 137

2 Answers2

2

Without knowing too much about the system, I would suggest these are dimension tables rather than fact tables. A dimension table represents an entity or an object that you can use to construct a fact. Accounts and clients seem like a good fit for this. I'm not sure what Credit and Quality are but they may be dimensions as well.

Your fact table should represent transaction-like records. This could be sales, transactions, phone calls, or whatever your data warehouse is reporting on. This fact table would then have foreign keys to each of the dimension tables.

Regarding a single or multiple databases: I would suggest storing it in a single database. It's easier to use that way, and you don't have to worry about database links when querying your data. Your ETL process for populating these fact and dimension tables can extract the data from these four databases and load it into one database, and from there, you can build the cubes in a single database.

bbrumm
  • 1,342
  • 1
  • 8
  • 13
1

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.

Wes H
  • 4,186
  • 2
  • 13
  • 24