Design Help

View previous topic View next topic Go down

Design Help

Post  studyref on Sun Feb 07, 2016 4:21 pm

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

studyref

Posts : 3
Join date : 2016-02-06

View user profile

Back to top Go down

Re: Design Help

Post  zoom on Mon Feb 08, 2016 4:48 pm

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.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Design Help

Post  studyref on Tue Feb 09, 2016 5:25 am

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.

studyref

Posts : 3
Join date : 2016-02-06

View user profile

Back to top Go down

Re: Design Help

Post  zoom on Wed Feb 10, 2016 4:29 pm

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

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Design Help

Post  studyref on Tue Feb 16, 2016 10:22 am

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.


studyref

Posts : 3
Join date : 2016-02-06

View user profile

Back to top Go down

Re: Design Help

Post  zoom on Wed Feb 17, 2016 11:10 am

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 chooseYou 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.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Design Help

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum