Schema Naming

There seems an obvious advantage in giving warehouse dimensions and facts their own schema (dim, fact); but it might be more imagined than real.  I have started using this separation; it allows the dim table prefix to be dropped which makes lists and auto-naming neater later on in the development pipeline; I’ll see how it goes.

I use Rep for reporting objects (views or procedures that drive reports).  That might be too cryptic for good naming, although giving it a description (MS_Description) property helps.  I tried to keep schemas to two or three characters.  I have just come across joethebusinessintelligenceguy who unambiguously uses the complete word report for the reports schema and I warm to the idea.

Joe has some other nice ideas: olap for SSAS objects and etl for SSIS loader objects.

Those Schemas in Full

dim  dimensions
fact fact
etl etl processes
log log tables (although log is a reserved word)
rep or report report sources
olap SSAS sources


SQL Server Collation

The answer is:  Latin1_General_CI_AS.  (the windows collation)

This is Microsoft’s recommendation.  It’s in article 322112 Comparing SQL Collations to Windows collations.