Dimension Attributes and Fact attributes storing same data in multiple data marts??

Can data marts using BUS architecture store the same data but be called different names like DimChargeToDept and DimDepartment. All have the same attirbutes names different but same exact data and rows. I would rather they create a view for each dimension.

So, what are the best practices for having fact attributes that are not conformed facts storing the same exact data in different facts and dimensions that are conformed storing the same exact data but just changing the name like above.

Conformed should be master data and only exist once and created once yet used by many data marts. My opinion looking for others.

Thank you,
Scoop

Best is to store only as many physical copies of the data as needed (basically one per physical database instance), and to minimize the number of objects affected by changes, and the skill sets needed to make those changes.

So, if all access to the Data Mart is via one reporting tool (or integrated set of tools), set up the multiple aliases for one table (and its elements if that is needed) or "role playing" names within the reporting tool; all the good ones support this, now when modifications occur you have one set of objects to change and one skill set to implement the change, at least as it relates to the subject of "multiple roles played by one table".

If there are multiple tools or the tool set is not integrated, so that there are multiple places where the alias or alternate name has to be defined, using different sets of skills, then database views are the way to go. This will impose a degree of consistency across the multiple access paths, and speed implementation as each tool or non-integrated component can import the view metadata from the database.

One note about using views this way (or synonyms in an Oracle database): using Kimball's design suggestions, there will be a time (date) dimension table that will almost always have multiple roles within each Data Mart and can have hundreds of uses across many Data marts. If a unique view is created for each use of the time dimension, it becomes hard to manage; adding one new column becomes a big task, and keeping track of all the views and what they are for is not trivial. I recommend planning ahead and pre-creating ones that represent common kinds of dates in the enterprise -- DimStartDate, DimEndDate, DimTransactionDate, DimRevisionDate, etc., and some really generic ones that will come in handy like DimDate01, DimDate02, . . .

I see dimensions that play multiple roles in almost every star schema that I work with, but I don't understand what you mean about fact tables that behave that way. Could you share an example?

Regards,
Tim

Depending on the particular query tool you are using, there isn't any reason to be creating views and aliases in the database itself. You have a customer dimension, for example, and have rolenames for the foreign keys (i.e. ship_to_customer_key, sold_to_customer_key, etc).

Most tools (Business Objects, Microstrategy, etc) allow you to define alias references in the tool. It is much simpler to deal with it there rather than going back to a DBA to make a schema change (particularly once things get into production).