Cube Design

Question

I have survey cube that for given filter (time) shows all surveys, question and answers taken for a time period. I have request from the field to show not only taken ones but rather all question and answers for list of surveys (those not taken). How should I design the cube to fulfill this request?

Thank you, very much!

gbrajvo

Answer 1

Hi gbrajvo,

I think this document provides the answer to your question:

http://www.sqlbi.com/articles/many2many/

There is a dedicated chapter about surveys :-)

Hope this helps

Tom

Answer 2

Thank you Tom very much!

Unfortunately, they are not covering scenario where no data is required to be shown.

My problem is this.  I have a survey with 5 question and I filter result for two months (February and March) . For February, I have hits and it’ll show all 5 question and answers. But for the March I don’t have any hits and it doesn’t show questions.  How to force it to show questions with ansers 0 or null ?

Answer 3

Unfortunately I was able to find article how to do it on Oracle platform: http://techblog.aqies.com/2011/08/31/showing-dimension-data-in-absence-of-fact-values-data-densification/#comment-3473 

(this is exactly what I need), and I was able to recreate it up to the last step. I don’t know how to do last step within SSAS platform.

Once again, any help would be greatly appreciated.

Thanks!

Answer 4

Thank you Tom very much!

Unfortunately, they are not covering scenario where no data is required to be shown.

My problem is this.  I have a survey with 5 question and I filter result for two months (February and March) . For February, I have hits and it’ll show all 5 question and answers. But for the March I don’t have any hits and it doesn’t show questions.  How to force it to show questions with ansers 0 or null ?

Most MDX clients will suppress empty rows and columns by default, but they usually have an option to switch this off so that empty rows and columns are displayed. So this could be the cause of your issue here.

---

http://darren.gosbell.com - please mark correct answers

Answer 5

Hi Darren,

Any tips how to do it?

Thanks!

Answer 6

What client tool are you using?

---

http://darren.gosbell.com - please mark correct answers

Answer 7

Excel 2010.

Answer 8

Under "PivotTable Tools" on the ribbon, click on the Options ribbon, then on the Options button on the left hand side.

Then in the display tab of the options dialog there are settings called "show items with no data on rows" and "show items with no data on columns" than you can enable.

---

http://darren.gosbell.com - please mark correct answers

Answer 9

Thank you Darren, very much!

I’m almost there. There is on small problem.  When I enable “show items with no data”

 I’m getting all answers (also those that don’t belong to the survey I’m browsing).

Is it a way to narrow it to display only answers that belong to survey being analyzed?

Answer 10

Sorry, but my last answer may have over simplified things. You could use it to show all the questions, but it could cause issues when drilling down to the answers.

It really depends on how your dimensions are structured. It sounds like the questions and answers are in separate dimensions, which is the most flexible structure, but it also means that all answers belong to all surveys - there is nothing except for the responses tying the two dimensions together.

So one option could be to create a measure group that would just indicate if a question and an answer was valid for a given month. So this measure group would have questionid, answerid and monthid and you would have a row if the combination was a valid one. (Or if your questions and answers never changed from month to month  you could just have questionid and answerid.) This measure group would just have a single row count measure and you could set it to visible = 0.

Then you could either cheat a little and create a calculated measure over your "real" measure and set this new row count as the NON_EMPTY_BEHAVIOR.

Or you could create a measure that explicitly checks this rowcount and if it's not empty you return either the real response measure or a zero.

eg.

IIF( isempty(Measures.[Answer RowCount]), null , iif( isemtpy(measures.[responses]), 0, measures.[responses]))

---

http://darren.gosbell.com - please mark correct answers

Answer 11

Hi Darren,

I implemented your suggested solution and it works (second option). Thank you very, very much!

I have only one problem with this approach. When I use new calculated member I can’t show answer "date and time' even for the ‘real’ records. It shows the list of the all dates. Yes you are correct I have separate Question and Answer dimension and I was wondering if that is the reason for such "date and time" behavior.

Thanks!

Answer 12

Hi Darren,

I went a step further and I’m now inches away from final product. I somewhat resolved issue I wrote you in my previous post, and here is how. I created separate dimension for “answer date time”. I loaded a table with answer date and time plus I added fake date ‘1900-01-01’. Then I added keys from this new dimension to my original fact table (keys generated from real times) and a key of the fake "date and time" value to my second measure group (one you suggested, with Question and Answer keys only). Then I created calculated member like this:

case

when IsEmpty([Measures].[Fact Survey Count]) then

             case when [Measures].[Answer Row Count] <> 0 then '' end

             else [Measures].[Fact Survey Count]

end

MDX statement you sent me was always returning this fake date (whether survey is taken or not).

Now when survey is taken, I’m getting real date and time and if survey is not taken, I’m getting my fake date (wanted).  The only thing is that when I browse taken survey this fake date (one row) is returned together with many correct dates. If you can give me any tip how to get rid of this row that would be a winner. Once again a hundred times thanks for your help. It means a world.

Answer 13

OK, I think we can probably fix the "real" answers, I'm not sure if we can do much for the months where there were no responses.

So the current logic runs for each cell and says "if the survey was valid this month and for the current intersection we have responses return the response measure, otherwise return 0". if we tweak this to say  "if the survey was valid this month and for the current intersection, for the current Month, we have responses return the response measure, otherwise return 0".

IIF( isempty(Measures.[Answer RowCount]), null , iif( isemtpy((measures.[responses],[date].[month].CurrentMember) ), 0, measures.[responses]))

So this code checks if the responses measure has a value in the current month, what this means is that it can return empty cells for dates under the current month, so this should fix your issue with "real" responses. (note - this relies on having attribute relationships configured correctly in your date dimension - and if you have multiple hierarchies we may need to enhance it further).

It's not going to help with the months where you have no responses, but then I'm guessing that it does not make much sense to drill down below the month in these cases.

---

http://darren.gosbell.com - please mark correct answers

Answer 14

Here is design.

Can we move it from month down to day with same characteristics ( If there is answer, show date if not show fake)?  I tried with "MonthNumberOfYear" and then to replace it with “TimeKey” (the format of this key is (int 20050101-20140101) and it is not working. Do you see anything wrong with design in regard to your suggestion?

THANKS!

Goran

Answer 15

Hi Darren,

I removed AnswerDateTimeKey and tried your suggestion: “if your questions and answers never changed from month to month you could just have questionid and answerid” and I’m still chasing my tail. I’d appreciate if you can suggest me if you see anything wrong with my design. Looks like I’m stuck and yet so close.

Thanks!