Need help with terminology

Go down

Need help with terminology Empty Need help with terminology

Post  Arvind on Sat Feb 27, 2010 9:58 am

Hello all,

I have tried to depict my current dw schema in the image below. As you can see the transaction table, summary tables are all part of the same physical schema. The transaction table has all the transactions since inception and has all the details of the individual transactions that are captured from the source system. The transactions are summarized and loaded into the fact_monthly_summary table at the end of every month. This is a monthly snapshot of all the transactions. We also have a snapshot of the customer at the close of every month. Besides these I also have product level summary tables.

Although I have simplified in the image, the customer table has several dimension tables that allows the slicing and dicing of customer attributes. The product summary table is joined with four or five additional dimensional tables some of them having almost 200 columns in it. All these tables belong to a single physical Oracle schema. Thats for the background.

Need help with terminology Star11

I would like to prepare an architecture diagram and my confusion is the terminology. Is it alright to show the transaction tables as an ODS and the individual summary tables as Data Marts? ODS, from its definition is volatile. This one is not. We have all the transactions and never gets deleted. Can I still show this as an ODS in the diagram or should I leave is as part of the Data warehouse?

Data Marts: We have separate set of tables for individual products each with its own aggregation and dimensions. Can these be considered as data marts even though they reside in the same physical schema?

Below are my two options to show: (actual diagrams would look much better!)
Need help with terminology Arch11

Which one is the correct way of depicting? or is there another way to show this in the architecture?

Once again, I have two questions: What terminology to use in the diagram and how to show it.

Your help is much appreciated. thank you.

Arvind

Posts : 8
Join date : 2009-11-15

Back to top Go down

Need help with terminology Empty Re: Need help with terminology

Post  BoxesAndLines on Sun Feb 28, 2010 2:48 pm

Unfortunately, there aren't any strict industry standards here. You could go with Inmon DW 2.0 vocabulary if it is that important. Bill decided that everyone hijacked his idea of what is a data warehouse and now has copyrighted his version. Otherwise, anywhere you go one person's ODS is another's staging database. I will add that I don't get caught up what is in one "schema" and what is not. That's a purely physical implementation detail usually based on maintenance of different tables. FWIW, of your two diagrams, diagram 2 depicts more how I would represent your environment. I would add ETL processes between the ODS and datamarts to illustrate the logic required to rearrange the data into dimensional models.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Need help with terminology Empty Re: Need help with terminology

Post  Arvind on Mon Mar 01, 2010 7:41 pm

BoxesandLines: Thank you!

Arvind

Posts : 8
Join date : 2009-11-15

Back to top Go down

Need help with terminology Empty Re: Need help with terminology

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum