Best Practice Database Schema/user layout

[mea0730]

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!

[ngalemmo]

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

[LAndrews]

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)

[mea0730]

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]

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

[ngalemmo]

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.

[Sponsored content]