- Create a Fact_TRADE that has everything that relates to a trade including the fee. Standard fee type amounts will be carried on the trade record. Total fees will also be carried.
- Create Fact_FEE that has all the details for FEE and are this fact table will be connected to the FACT_TRADE, so 1 to many relationship.
- Create a Fact_CASH that will record all the cash movements that relate to a trade. Again a 1:M relationship with a Fact_TRADE record.
- Create a Fact_STOCK just like the Fact_CASH record with a 1:M relationship with the Trade record.
As you can see these are Fact table joins. These can be expensive. But I cannot see another way around since they are all at different grains. Can you think of an alternative? Any suggestion will be appreciated.
Your approach has a heavy dimensional modeling influence. I would recommend seperating your data warehouse into architectural layers that are designed to function in specific roles. The 1st role: is the In-Take layer. This is typically modeled to closely align to you enterprise data model, highly normalized, subject oriented, integrated and contains atomic level of history. The Next Layer is the distribution layer. This layer provides a means to "serve up" data from the intake layer for purposes of delivering data to data marts. The Data Marts serve in the capacity of the access layer. More times than not, this layer will be dimensional, but doesn't always have to be. I would recommend maintaining the transaction details in the intake layer in a normalized form. Then look at your customers' access needs and opportunistically build your dimensional data marts sourcing them from the in-take layer and aggregating them as needed.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Data warehousing and Business Intelligence Web Links: tips, tutorials, scripts, and more.
- Have an DW tip to offer your fellow administrators and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical data warehousing questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Related Q&A from Mike Lampa
When trying to design a data warehouse, we often try to model the database on the operational data model. Are there any guidelines in trying to ... Continue Reading
What is a surrogate key in a table? Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.