Best Practice Database Schema/user layout

View previous topic View next topic Go down

Best Practice Database Schema/user layout

Post  mea0730 on Mon Jun 28, 2010 2:02 pm

Just wanted to check to see how others have structured the typical data warehouse schema layout. I know much of this is based on the individual project requirments, but I just wanted to see how others have done it.

I was thinking about the following layout.

"DW" - Schema that owns the data warehouse reporting tables
"DWSTG" - Schema that owns the staging tables
"DWRPT" - Used by the BI reporting tool. Has only SELECT to the DW schema tables
"DWETL" - Used by the ETL tool Has SELECT,INSERT,UPDATE,DELETE to tables in "both" DW and DWSTG

Thanks!

mea0730

Posts : 21
Join date : 2010-05-15
Location : San Jose, CA

View user profile

Back to top Go down

Re: Best Practice Database Schema/user layout

Post  ngalemmo on Mon Jun 28, 2010 2:30 pm

There are probably dozens of ways to deal with this. It is very much a situational driven scenario. What you have outlined will work.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Best Practice Database Schema/user layout

Post  LAndrews on Mon Jun 28, 2010 2:47 pm

Depending on your situation there may be some other requirements.

Some that I've seen in the past :

DWAudit : Read only access given to auditors (e.g. SOX) - can expose some of the ETL metadata
DWMaint : Read/Write access to certain persistant STG tables that contain data only in the DW environment (e.g. custom hierarchy)

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Best Practice Database Schema/user layout

Post  mea0730 on Tue Jun 29, 2010 2:06 am

Thanks for the replies!!

The bottom line is that I don't want the BI tools to have access to the schemas that own the tables and have drop table, etc.. permissions. I've seen that cause problems in the past!

I just was curious how others have organized the schemas.

Thanks,
Mike

mea0730

Posts : 21
Join date : 2010-05-15
Location : San Jose, CA

View user profile

Back to top Go down

Re: Best Practice Database Schema/user layout

Post  mea0730 on Tue Jun 29, 2010 11:04 am

and what about using a seperate schema/user for the staging tables? Would it be better just to put the staging tables in the main DW schema?

Thanks,
Mike

mea0730

Posts : 21
Join date : 2010-05-15
Location : San Jose, CA

View user profile

Back to top Go down

Re: Best Practice Database Schema/user layout

Post  ngalemmo on Tue Jun 29, 2010 11:37 am

That's usually what I do. I give the table names a unique prefix, such as S_, to segregate them. It is easier for the ETL folk because they only need one connection to the DB.

Either way is fine... no real advantage or disadvantage... it all depends on what the local policy is. Some shops prefer a separate schema for backup purposes, but since staging is relatively small and transient the overall effect on backup is insignificant. And advantage of having it in the same schema is that the backup will have staging content in sync with the DW, which may be advantageous in some restore scenarios.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Best Practice Database Schema/user layout

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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