Prologika Forums

Hi Teo,

In my survey data warehouse I store data for a variety of different surveys.  The dimensions are conformed more or less accross all surveys ... but I have had to create an extra junk dimension for a few that are specific to them.

What is the best ... or best options design wise?

So far, for every type of survey requiring a dimension specific to it ... I've create a separate fact table just to house that survey's data.  In SSAS, I then create a cube just for it as well.

Any input from anyone would be appreciative.

thanks - Wayde
 

The best design option is the one that works

Without knowing much about your specific situation, it looks like it may be somewhat inefficient to create a fact table per survey. If the survey cardinality differ substantially from one survey to another you can still have one fact table but create multiple measure groups that reference only the applicable dimensions. In this way, you may be able to minimize the number of fact tables and simplify ETL.

As far splitting the measure groups in cubes, again pros and cons. If you have huge data volumes or you need to partition security logically, it may make sense to do this. Otherwise, cross-measure group reporting can be simplified if you have a single cube since you won't need to link measure groups across cubes.

a good and politically correct answer :)

Well as I adjust the schema based on incoming changes, my particular issue now seems to deal more with different many-to-many relationships for each kind of survey.  As an example ...

For Survey A I have a Classification Dimension (stores info about students) that has a M-2-M relationship with the Response Dimension (stores information about survey users) which is then related to the Answers fact table.

For Survey B I have a Demographic Dimension (stores demographic info about employees)  that has a M-2-M relationship with the Response Dimension (stores information about survey users) which is then related to the Answers fact table.

The Classification Dimension is not and will never be related Survey B ... and the Demographic Dimension will never be related to Survey A.

Given that, what would be your suggestion? 

As I noted before. If you think that your design and ETL will be simplified by having a single fact table then keep one fact table but create separate measure groups on top of it linked to the appropriate dimensions. Then, start with a single cube. If performance is not OK (after you''ve done all you can think of, e.g. partitioning, aggregation, etc.), consider splitting UDM in multiple cubes. Read the SQL Server 2005 Analysis Services performance guide for performance optimization tips and considerations.