Design Help

Hi,

I am designing a dataware house for a Survey comp, where am trying to figure out what can be considered a fact.

All i have in my current sql db is inspenction data in the form of Q&A. what are the aspects that needs be taken into consideration while  designing data marts. any suggestions are welcome.

thanks
Jey

You need a detail transaction Fact table.  That table is going to be a fact less fact table. You may need following dims... company, questions, date, and people.
Once you have the fact table you can find out something like... How many male VS female answer a specific question. what was their age range or race...etc. What you show in a report should be your business user request or requirments. I just gave you some examples.

Thanks for the reply Zoom.

Here is a sample tables:
Bathroom-table Gen table    

Isbathroom           GenId    (PK)
Fullcnt               Subusr-Id    
Halfcnt                 Bldgnum
GenId(foreign key) Isfamily
                              Condition
                                Yrbuilt

(FullCnt: Count of Full bathrooms in the house & HalfCnt- Count of half bathrooms in the house)

I have in my current SQL db is information about house inspection. All I could figure out were the dimensions because there is no additive/ measure to perform. How to proceed further.

In a Factless Fact table all you have are Dim Ids. There is no measure in Factless Fact table.

I have few more questions before i proceed with the design part. (Flexibility & Errored records. )

1. Lets say there is a record which is coming from Sql DB has incorrect information, at what stage can i verify this information and throw back an error. How to hold on to that record information, next time it comes thru.
2. lets assume that business is going to increase the number of question, where should this information be added in future.

Good practice is to load data as it is received from source in the staging area. You can also get errors loading that data in staging if that data violates a column data type. For example you have a date column but you received a non-date value. Work with your business user to ask what would happen in that kind of error. There are 2 option for them to choose…You can either change non-date value to a default value (1/1/1700) and load that record or you can reject the whole record. It is also a good practice that a business user get report on the bad data so they can make correction on the source system.
The other kind of errors you can get when you do data transformation. For example converting a gender code to more descriptive value ( converting gender code 1 to male and 2 to Female). If expected gender code values are 1 and 2 but you get a null or other than 1 and 2, then ask business user what would happen for that record… reject it or covert it a default value as “unknown”. Once again, it is also a good practice that a business user get report on the bad data so they can make correction on the source system.

The main point is that IT people work with business user to help them how to handle bad data.

Now come to your 2nd question. You load any addition questions to the same dim where you are currently saving them.