Conformed Dimension Processing Multiple Data Marts

Rookie question.

We have 8 conformed dimensions and we are on our 4th data mart using some of these 8 conformed dimensions.

We build the conformed dimensions once every night because the client wants to complete full refresh only.

The client has been building each data mart every night and rebuilding each conformed dimension to only store the rows that exist in the fact table. So, the DimCustomer is built first in staging and contains 500 rows then DM1 is built and DimCustomer contains 100 rows in the dimension only and DM2 contains 250 rows within DimCustomer, and so on.

Should the Conformed dimensions be built once and if that conformed dimension has 500 rows used in 3 Data Marts all 500 rows should stay in that dimension even though each data mart fact may not have all 500 customers?

Thank you for your repsonse,
Scoop

Are the customers that don't have a fact record not important? It would seem that these customers may be the most important for data mining. Your data mart tells nothing of these customers. With the volumes you have it doesn't really matter. Loading a 500 row dimension table is insignificant in database resources. Once your dimension tables contains 10 million rows and your fact table contains 100 million rows, the answers to your questions become readily apparent. You cannot refresh a substantial data warehouse on a nightly basis.

All of our 8 conformed dimensions are important along with every row.

We build these conformed dimensions once every night

We then build 4 data marts every night.

We identify the source for all facts of each 4 data marts. Match natural keys to natural keys and create the 4 data marts.

We want to build some cubes using Data Mart 1 and Data Mart 4 in SSAS but the conformed dimensions in each data mart do not have the same amount of rows and surrogate keys and do not match and we recieve errors.

So, what is the standard with conformed dimensions when they are used in all 12 data marts which we are going to build should these conformed dimensions be build once then every data mart 1 through data mart 12 each conformed dimension should only contain the rows that match the facts of each data mart or should each conformed dimension in each of the 12 data marts have the same amount of rows so, you can build cubes from multiple data marts or not matter what you should build conformed dimensions once and only match the rows in each data mart fact with the conformed dimension so, if 100 out of the 500 exist in the fact only store 100 rows in that conformed dimension.

I really do not get your reply. Maybe I am not making sense but a conformed dimensions are your master files of your business and I thought you build conformed dimensions for many reaason but one was to be able to drill across and combine wtih other data marts fact when you build a cube, etc. Maybe I am wrong here also, but I or we are trying to combine data marts 1 and data marts 4 together in a cube but we are receiving errors because the keys do not match from one data mart to the other due to the data marts conformed dimensions only contain rows that match all facts.

WOW I am out of breath after this. So, what is the best practice for building conformed dimensions and how should they be built and shared like plug and play from data mart to data mart??

Thank you, Scoop

So your conformed dimension, Customer_Dim for example, is a different table in each data mart with different data but same structure?

Yes, they call the dimension the same name, same structure, same attributes, but only contains rows that are found in the source for the associated fact table or tables.

Thank you,
Scoop

OK, got it. Yes, you need to same data in each dimension.

We create all data marts using SQL Server 2005.

We create all Conformed Dimensions first then build the 4 data marts.

We build a cube and data mart 1 and data mart 2 are sources.

The Data source view we pull in all dimensions and facts from Data Mart 1 create DSV then we right click inside DSV and add all dimensions and facts from Data Mart 2 into data source View.

If the conformed dimensions do not have the same amount of rows then we cannot join that conformed dimension of data mart 1 to facts of data mart 2.

So, with conformed dimensions the best practice I would think is to keep all rows in each conformed dimension in all data marts.

Advice and best practices on using conformed dimensions is what we are looking for.

Thank you,
Scoop

I think that's what I said. I don't do what you do though. In my data marts, my conformed dimensions are not copied. There is only one table. The cubes all pull from the same table. The facts all use the same table. All my datamarts live in the same database.

I would rather use one database also at this client site I am at, but they are very hard to convince on some best practices, space, and consolidation.

I recommend that we complete all work in staging_DB then create all conformed and non-conformed facts and dimensions in DW_Prod_DB then we build cubes we pull DW_Prod_DB as source and the DSV we only pull the resuired tables for the new deployed data mart. This is what I would like us to do.

Do you find this process to be the same as what you are doing and that way you can cut down on the redundancy issue and the conformed processes are more inherent?

Thank you for your opinion and help,
Scoop

BoxesAndLines ??? for you..

I agree with the one central database and the direction was suppose to go this way with the BUS architecture yet listening did not occur at this client site.

Your central database do you add your referential constraints in this one database or do you wait to add the referential constraints inside SSAS?

Reason ask is when do you re-build indexes and do you drop constraints in central database before re-building?

Pretty blank ? but I think you get my ??

I would prefer the client site I am at who refreshes data everynight because they are not ready for incremental loads yet and storing history yet to not add RI constraints to dims or fact in the one central database because they would have to drop and re-build these constraints every night which would add more time to completing the rebuilds of all models.

So, looking for input and thank you.

Scoop

Scoop wrote:BoxesAndLines ??? for you..

Your central database do you add your referential constraints in this one database or do you wait to add the referential constraints inside SSAS?

There is no RI defined in the database. There are PK, unique constraints, and not null constraints.

Scoop wrote:Reason ask is when do you re-build indexes and do you drop constraints in central database before re-building?

Depends on the fact table. Sometimes I drop and recreate after the indexes during the nightly load. Other times, I leave the indexes intact. Constraints are insurance against ETL and data errors. They are there to protect you and the users.

BoxesAndLines wrote---

There is no RI defined in the database. There are PK, unique constraints, and not null constraints.

Dims are Pks and Facts are FK (referential constraints) and in order to build a cube in SSAS a relationship has to exist between the dims and the facts. So, I understand not having the referential constraints relationship created in the central database but this must exist in SSAS correct?

I know that PK's contraints and Unique constraints purpose also and I agree with you on this but I am confused on the building cube process.

I'm not a cube person but RI, whether implemented via application code or DBMS constraints, must be enforced.