Conformed Dimensions Schemas and Data Marts in 1 DB w/ diff. SLAs - advice requested

[g8rpal]

I started a position a few months ago and their current data warehouse architecture is as follows:
4 databases, one holds the conformed dimensions, then three other databases that are essentially data marts for 1 or 2 solutions each.

A new proposal which I am involved in suggests that we change this architecture to 1 database (Microsoft SQL Server 2008) that uses different schemas to separate the different data marts. The primary advantage for our data modeler is so that the fact tables can have foreign key constraints on the conformed dimensions. Personally, I like the idea of only applying foreign key constraints periodically to ensure referential integrity but leaving them off during the normal course of the week but that is not the point of this post.

One of the DBAs at the company is concerned about this proposed approach because different data marts have different service level agreements. His argument is that if all of the data marts are in one database and that database goes down, all of the data marts are down.

Now, I am not a DBA and have only a superficial understanding of this area of expertise. I am looking for some good points to make that will put the DBA at ease and hopefully convince him that this approach makes more sense.

Any suggestions, comments, or questions would be greatly appreciated.

Sincerely,

Mike

[BoxesAndLines]

42 was a very tempting answer. After all it is the ultimate answer. To answer your question I will ask you the same question I ask all data modelers and other folks that want to engage the DBA to change their stance. Who answers the call at 2AM when the database fails?

[hang]

Here's the site worthing reading: http://sqlcat.com/top10lists/archive/2008/02/06/top-10-best-practices-for-building-a-large-scale-relational-data-warehouse.aspx . Notice the last point in tip 4 gave the correct answer.

[g8rpal]

Thank you for the replies.

Never mind the FK constraints for now. I guess my question really has more to do with the fact that the conformed dimensions are in their own database currently. Personally, I've never seen that approach taken before. Is there any detriment to having the conformed dimensions in a separate database? I liked the new design better because they were in the same database and all of the projects used schemas to logically separate them. Our modeler liked the new design better because of the ability to have the FK constraints on the conformed dimension tables.

Thanks again,

Mike

[hang]

The dimensionally modelled database in the context of BI/DW environment should always be self-contained, meaning all the relevant dimension and fact data should be ETLed into a dimensional data store in a single database.

Although I am not big fan of applying any FK constraints in a dimensional data store, if FK constraints can only be applied between tables in the same database, there is a strong reason for them to be stored in the same database.

If the conformed dimensions are part of dimensional relationship, why store them in separate database? In case of SQL Server, I think using different schemas in the same database for different subject areas or business processes is good idea, whereas the conformed dimensions should be stored just under dbo as they are shared by different business processes (schemas).

[g8rpal]

Hang,

Thanks for your reply. I found this link today:

http://www.sqlservercentral.com/Forums/Topic940727-263-1.aspx

and I liked this quote:

..." I use it for the logical grouping of database objects.

This started with a Kimball BI recommendation to use schemas instead of prefixes for naming conventions of data warehouse table. Instead of dbo.factCustomerOrder, I have fact.CustomerOrder; instead of dbo.dimTime, I have dim.Time. The "dbo" prefix is totally unneccesary, and I think the shorter names is just cleaner. It's purely asthetic, but I like it."

I also liked the very last post on page 4 which I won't bother reposting here.

I think from a development stand point it just seems to make sense to logically group things together.

I really liked your comment re: the BI/DW environment should always be self-contained. I think that will be our best argument for changing the current structure.

I really appreciate everyone taking the time to respond.

Sincerely,

Mike

[hang]

This started with a Kimball BI recommendation to use schemas instead of prefixes for naming conventions of data warehouse table. Instead of dbo.factCustomerOrder, I have fact.CustomerOrder; instead of dbo.dimTime, I have dim.Time.

I don't recall Kimball ever suggested to use schemas to separate fact tables from dimension tables. My understanding is use schemas and naming conventions (prefix or suffix) wherever appropriate. I have seen many DW books, one coauthored by Kimball himself, using fact and dim as prefix or suffix for fact and dimension table names. Don't get carried away by dbo in SQL Server. It is not a schema per se, instead it indicate objects (including tables) that have generic connotation. So conformed dimensions may well fall into this category.

I also liked the very last post on page 4

Me too! Having read all the posts on the recommended link, I think 'Forum Newbie' made all the good points, and two important points on using schemas are about Relationship and Security. A business process or subject area can be a schema that hold all the relevant dimensions and facts together to form one or more star schemas which make the schema reading less cluttered. Obviously user accesses to different business processes may naturally form different user groups, so schemas makes the security management a lot easier.

[Sponsored content]