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

View previous topic View next topic Go down

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

Post  ian.coetzer on Wed Nov 17, 2010 8:42 am

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?
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

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

Post  ngalemmo on Wed Nov 17, 2010 11:48 am

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

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

Post  ian.coetzer on Wed Nov 17, 2010 1:01 pm

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.
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

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

Post  hang on Wed Nov 17, 2010 1:25 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

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

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