Schemas ... in the final dimensional models (BI database)

[ian.coetzer]

Hi
quick question ...

We are planning to have 2 or three staging databases.

1) Staging
2) Conforming

and then finally the actuall BI database

3) DataWarehouse

My question, in this final final database (3) should we use schemas to 'group' common dimensions, housekeeping administrative dimension, facts for departments etc.? as shown below - or should one rather keep this simple - chuck them all in the dbo. schema?

admin.DimAudit
admin.DimSource
shared.DimDate
shared.DimTime
shared.DimCustomer
credit.DimBatch
credit.DimBranch
credit.FactAffordability
credit.FactVATReceipt
callcentre.FactCallCentreMetrics
etc.

or should i just stick to not use schemas and just create them all in the default .dbo schema?

any ideas? comments?

[ngalemmo]

I prefer to have one schema... it's just simpler to deal with in the modeling tool. I would create additional schema that contain synonyms and/or views of the warehouse schema if I want to create user specific envionments.

[ian.coetzer]

Hi,

Thank you for the reply.
After discussing this with a colleague i'm also now more inclined to keep things simple - one schema.

Interresting thought - creating views in specific schemas for user specific environment / subject matter area scenarios ... might consider this in the future.

Ian.

[hang]

I would have a metadata database, not just separate schema, to hold all the tables, ie. DimAudit and DimSource in your admin schema, and perhaps DimBatch if it refers to ETL batch. The tables in separate metadata area should not be mixed with dimensions and facts in the same database, as they contain information about data or data flows across different data stores, eg. staging, dimensional or ODS.

I agree that one general schema, dbo in case of SQL Server, does simplify the dimensional store, but not quite sure about a separate schema for synonyms and views. It seems to me that synonyms and views should stay wherever they are used in relation to other tables, dimensions or facts. For instance in SQL Server, I would create synonyms in ODS, DDS or staging databases for any objects living in external databases/servers, so that we know they are pointing to foreign objects. Another point in using synonyms this way is to centralise the connecting paths in this special type of objects, so that I can easily manage the change of databases or server environments, avoiding hardcoding the paths in any logic ridden stored procs, views and functions.

I like the idea about grouping subject area related objects, except for conformed dimensions, into schemas, as they look less cluttered and are easier for security roles management.

[Sponsored content]