September 12, 2003 at 3:34 pm
Given the following OLTP schema:
CREATE TABLE Store (StoreNo_PK int IDENTITY NOT NULL PRIMARY KEY)
CREATE TABLE Job (JobNo_PK int IDENTITY NOT NULL PRIMARY KEY)
CREATE TABLE StoreJob (StoreJobNo_PK int IDENTITY NOT NULL PRIMARY KEY
, StoreNo_FK int NOT NULL FOREIGN KEY REFERENCES Store (StoreNo_PK)
, JobNo_FK int NOT NULL FOREIGN KEY REFERENCES Job (JobNo_PK))
CREATE TABLE Question (QuestionNo_PK int NOT NULL IDENTITY PRIMARY KEY)
CREATE TABLE JobQuestion (JobQuestionNo_PK int IDENTITY PRIMARY KEY
, JobNo_FK int NOT NULL FOREIGN KEY REFERENCES Job (JobNo_PK)
, QuestionNo_FK int NOT NULL FOREIGN KEY REFERENCES Question (QuestionNo_PK))
CREATE TABLE Answer (AnswerNo_PK int IDENTITY NOT NULL PRIMARY KEY
, JobQuestionNo_FK int NOT NULL FOREIGN KEY REFERENCES JobQuestion (JobQuestionNo_PK)
, StoreJobNo_FK int NOT NULL FOREIGN KEY REFERENCES StoreJob (StoreJobNo_PK))
Is it possible to create a Cube of the Answers for all StoreJobs, even though each Job can have a varying list of questions, and many Questions will be unique to each Job?
Currently we are using separate Excel Pivot Tables for each Job, that query the OLTP database, to overcome this issue.
September 14, 2003 at 10:48 pm
Hi David,
What are your measures (the values you want to see in the middle of the crosstab)? Is it an answer count or something completely different (ie not in table schema).
I personally wouldn't want to make separate dimensions for question, answer & job, because of the 1-to-1 of some of these, the sparcity could be large depending on your data sizes.
HTH,
Steve.
Steve.
September 17, 2003 at 1:29 am
Thanks for the reply Steve,
The measures are also complicated, basicly each Question (text string) can be of a varying type, some are: single Option selections (option button), multiple choice (check box), single Option from a list (combo box), and both free and formatted text (format can be numberic with min / max, etc.).
The easy part for Piviot Table is the fact that the Answer table contains the text represntation of the user's interaction with the UI. The Answer table also has a relationship for the Options that the user selected, which allows for some aggregation in the query that populates the Pivot Table.
So aggregate functions (Min / Max / Mean /Count / etc.) are performed on the Answer, in a survey fashion.
I had begun to create a star schema for this but ran into the problem of varying Questions per Job. So far my only solution is to create a seperate cube per Job.
September 21, 2003 at 7:11 am
We had a similar issue when trying to warehouse and then report on data from Casiosoft's MobileLink software. We basically created a cube per job. In some ways, I think that summary SQL queries run over the questionnaire, job and answer tables could have been a better solution.
Steve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply