Storing a questionnaire in a Warehouse
<p>I am currently implementing a data warehouse with SQL Server 2014 and I am struggling with the layout of the dimension tables, well, more the extraction of the data. I have an Excel-File with the following format:</p>

<pre><code>+--------+---------+------------+------------+------------+---+-------------+
| Region | Country |  Location  | Question 1 | Question 2 | … | Question 97 |
+--------+---------+------------+------------+------------+---+-------------+
| Africa | Algeria | Location 1 | Yes        | Yes        | … | No          |
| Africa | Algeria | Location 2 | No         | Yes        | … | Yes         |
+--------+---------+------------+------------+------------+---+-------------+
</code></pre>

<p>There are some more attributes about who answered the question when. As indicated with the dots, there are more than 3 Questions, actually there are 97 of them.
Currently I've imported the excel file into my raw table which represents my preparation area(It has the exact same format as the excel file with first row as headers). Now, when I want to put the data into staging I'm struggling a bit with the layout. So I thought about the layout and since my boss told me that the questions could change in future, I came up with the following two tables:</p>

<pre><code>+------------+---------------------+
| DIM_Questionnaire                |
+------------+---------------------+
| PK         | SUR_QuestionnaireID |
| FK         | LocationID          |
| FK         | TimeKey             |
+------------+---------------------+

+--------------+-----------------+
| DIM_Question                   |
+--------------+-----------------+
| PK           | SUR_QuestionID  |
| FK           | QuestionnaireID |
|              | Question        |
|              | Answer          |
|              | Category        |
+--------------+-----------------+
</code></pre>

<p>The Survey is then added to a fact table.  What do you think about this solution in a warehouse? Furthermore, how would i be able to get the column name from the raw-table as a value in in the Question table? </p>