Need help with terminology

[Arvind]

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.



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!)


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.

[BoxesAndLines]

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.

[Arvind]

BoxesandLines: Thank you!

[Sponsored content]