Welcome, Guest
Username: Password: Remember me

TOPIC: custom reports and custom pages

Re: custom reports and custom pages 2 years 3 months ago #77686

  • TMSWhite
  • TMSWhite's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 759
  • Thank you received: 82
  • Karma: 36
Mazi wrote:
How about an alternative approach: Convert response data in Limesurvey into a different table structure (one row per response with related data like SID, GID, QID, ...). This way you can get rid of the strange column naming at Limesurvey and can easily query data fro a certain survey, group or question.

Here is how I would recommend doing this. I've used this Entity-Attribute-Value approach for a decade, and performance is excellent (despite running on a low-end Amazon EC2 instance and having a 17 GB MySql database with over 200,000 responses and 70 million rows of data).
The administrator has disabled public write access.

Re: custom reports and custom pages 2 years 3 months ago #77692

  • tacman1123
  • tacman1123's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 125
  • Karma: 1
We used that approach for a massive project several years ago, 11 million documents and about 200 million document attributes. Indeed, performance wasn't a problem.

The other advantage to this approach is that it becomes simple to add repeatable groups. For example, if you have a group called 'child' with 3 questions, name, gender, birthday, and you want this to be repeatable. Currently after a lot of typing and renaming of fields, you end up with a big table with fields like child1_name, child2_name. Reporting on this is a mess.

But with Entity-Attribute-Value, you could have unrestricted repeatable questions, AND when you report on it, you'd be able to treat each "child" as an independent entity, not looping through to some artificial maximum. That is, you could rewrite a report that says "give me a list of all the children in this survey's responses."

Is this a possibility for 2.0? If so, I'd love to participate in the design discussion, we had done some preliminary work for our survey system, and I have some thoughts on integrating timing, survey versions, and the like.

Tac
The administrator has disabled public write access.

Re: custom reports and custom pages 2 years 3 months ago #77694

  • TMSWhite
  • TMSWhite's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 759
  • Thank you received: 82
  • Karma: 36
Tac-

It would be great to have you involved.

2.0's feature list hasn't been locked down yet, so anything goes. Once we pass the lock phase, we'd need to focus on 2.1.

So, perhaps the best place to start would be for you to create a wiki page with a proposed data model so that we can discuss it. I'm especially interested in your ideas for unrestricted repeating groups.

EAV with qcode naming could also help another topic you raised - importing answers from one survey into a new one without having to worry about SGQA naming.
The administrator has disabled public write access.

Re: custom reports and custom pages 2 years 3 months ago #77695

  • tacman1123
  • tacman1123's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 125
  • Karma: 1
I'm also wondering if people would be open to changing the surveys table to a EAV structure. There are 57 fields now, and of course there are more I'd like to to add. As an EAV, admins could add their own survey attributes. For example, I want to add "custom_reports". Instead of altering the data structure of surveys, I'd simply add a new record (not field) to survey_attribute_definition, which would be:

code: custom_reports
type: TEXT

There would probably be things like order, prompt, advanced, and maybe some validation rules, whatever.

But then it's active in the interface, without having to change the underlying code! You'd access it with some sort of method call, e.g. $survey->getAttribute('custom_reports').

Where the attribute is stored would depend on things like access speed, filtering and sorting. So expiration_date and is_public would probably continue to be stored in the surveys table (to make the query faster and easier) but custom_reports would be in the attribute data table (something like survey_attribute_data, or survey_attributes).

Tac
The administrator has disabled public write access.

Re: custom reports and custom pages 2 years 3 months ago #77697

  • TMSWhite
  • TMSWhite's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 759
  • Thank you received: 82
  • Karma: 36
Another excellent idea. We've been using EAV for advanced question_attributes for a while now, and it makes it much easier to add new attributes, or associate existing attributes with new question types -- all without having to specify a data model upgrade. Taking a similar approach for general survey attributes would also be nice - especially if we changed over to use a model-driven GUI generation approach as was done for advanced question attributes. Take a look at the questionAttributes() function within common_function.php to see how it is done for advanced question attributes.
The administrator has disabled public write access.

Re: custom reports and custom pages 2 years 3 months ago #77699

  • tacman1123
  • tacman1123's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 125
  • Karma: 1
So, perhaps the best place to start would be for you to create a wiki page with a proposed data model so that we can discuss it.

Would this be in docs.limesurvey.org/tiki-index.php? Is there a page for proposals like this? I don't want to plop it in the wrong spot!

I'll also write up a proposal for a ReportWriter Module, which I've been thinking about for a while and am almost ready to implement. Would love to propose it for 2.0, but for the client that needs it, I might need to just develop it in my 1.92 branch.

Tac
The administrator has disabled public write access.

Re: custom reports and custom pages 2 years 3 months ago #77701

  • tacman1123
  • tacman1123's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 125
  • Karma: 1
Yep, very much along these lines. One difference is that a site administrator (superuser) could add new survey attributes via the UI, and not need to modify the code. OR maybe just add it to config.php, e.g.

custom_survey_attributes = array(
'custom_reports'=>array('type'=>'TEXT'),
'restrict_by_ip_list'=>array('type'=>'TEXT')
);

Tac
The administrator has disabled public write access.

Re: custom reports and custom pages 2 years 3 months ago #77704

  • TMSWhite
  • TMSWhite's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 759
  • Thank you received: 82
  • Karma: 36
The administrator has disabled public write access.

Aw: Re: custom reports and custom pages 2 years 3 months ago #77714

  • Mazi
  • Mazi's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 5300
  • Thank you received: 291
  • Karma: 247
Tac, did you read about the "Database Storage Engine" (DBSE) which was developed during GSOC 2010 and ported to the Ci framework during GSOC 2011 but wasn't ported to the Yii framework yet?
If the basics of this project make it possible to deal with your preferred layout, it might be a good start and we don't have to do so much of re-coding.

Best regards/Beste Grüße,
Dr. Marcel Minke
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)limesurvey.org'"
The administrator has disabled public write access.

Re: Aw: Re: custom reports and custom pages 2 years 3 months ago #77736

  • tacman1123
  • tacman1123's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 125
  • Karma: 1
That's here, right:

docs.limesurvey.org/tiki-index.php?page=...orage+Engine+Project

The link to the PDF is no longer valid, I'm curious what the database structure was.

Interesting ideas, but I'm not sure I followed it altogether. How far did it get? Is any of the code re-usable?

Tac
The administrator has disabled public write access.
Moderators: ITEd
Time to create page: 0.128 seconds
Donation Image