Rails - Apr 2009 - Help with Data Warehouse app -- How to display data?

I have a "data warehouse" application I''ve been writing and I
would like
to solicit some assistance with how to design it.  Basically, I harvest
data from a lot of sources and dump them into my database.

For background on what I''m doing, this "data warehouse" takes
HR/Personnel/Company/Organizational data and associates that with User
Account and Group data from 5 platforms (ie, Active Directory, UNIX,
others).

I began first by scaffolding and designing the Models, and then
importing the data with a Rake task--roughly doing:

1.) Insert/Update nightly data into Table.
2.) Flag data that was not in my nightly source data (maybe it was
deleted?)
3.) Call method to create relationships ("hooks") between these
various
sources.

I wrote this in Perl (with mysql) and I displayed this data in a webpage
by using a set of SQL Views and a meta-table, describing what elements
of these views is displayed.

It was not a good solution for a lot of reasons so I decided to redo my
database and learn Ruby/RoR in the process.


Now I need to interact with this data in a lot of ways.  The web portion
is only one aspect of this database''s use.  As a result, I have a lot
more data here than the user really needs to see.

I want to have the initial page be a search form.  Some columns maps
one-to-one with other columns in the database.  Other search fields act
as a generic filter across all (or most) columns.

When they submit the search, it displays a subset of data / results,
which can then be clicked on and more detail is shown about
particular[/groups of] records.

All told, I have 17 tables (so, 17 nightly dumps).  A majority of the
searches are going to be against 3 to 8 of these tables.


Given all of this, my issues/questions are as follows:

- I''m really trying to avoid hard coding as much as possible.  Ideally,
I''d have some kind of admin interface to make changes to how data is
displayed.  The idea of hard coding each text box for the search fields,
or the displayed columns, etc... isn''t appealing to me.

If this really is the best solution, I suppose I can go that route.  The
nice thing of having my Views in MySQL was that it was easy to map meta
data to what to display.


- I''m trying to figure out how to design this to make it very scalable
and easy to modify.  Particularly, if two tables have a column that have
something in common, I''d like to (perhaps) make those into a link to
each other.


- As above, I''d also like to add extra functionality.  I setup
acts_as_revisable in my database and it works very well.  If a user has
an elevated role, I''d like to have them be able to review the history
of
a record.



These are pretty lofty goals and this will take some baby steps.  I''m
still new to Ruby/RoR, but I think it''s been hard (so far) to find good
documentation that addresses some of these issues.  Or maybe I don''t
know the key words or how to approach this the Ruby Way (probably the
case)?

I''d like to use my experiences to do a write-up/tutorial on how to
address data warehousing problems with RoR (and starting RoR from
scratch).  If I become good enough, I may eventually attempt a plugin
for combined data warehousing, versioning, cross-referencing, etc (if I
still think there''s a use for it).

I really appreciate everyone''s help/advice on the issue.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Apr 17, 9:48 pm, "Kyle N/a"
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> I have a "data warehouse" application I''ve been writing
and I would like
> to solicit some assistance with how to design it.  Basically, I harvest
> data from a lot of sources and dump them into my database.
>
> For background on what I''m doing, this "data warehouse"
takes
> HR/Personnel/Company/Organizational data and associates that with User
> Account and Group data from 5 platforms (ie, Active Directory, UNIX,
> others).
>
> I began first by scaffolding and designing the Models, and then
> importing the data with a Rake task--roughly doing:
>
> 1.) Insert/Update nightly data into Table.
> 2.) Flag data that was not in my nightly source data (maybe it was
> deleted?)
> 3.) Call method to create relationships ("hooks") between these
various
> sources.
(Just so you know...3 isn''t necessary with Rails.)
>
> I wrote this in Perl (with mysql) and I displayed this data in a webpage
> by using a set of SQL Views and a meta-table, describing what elements
> of these views is displayed.
I am now somewhat confused.  How were you using Rake with Perl?
>
> It was not a good solution for a lot of reasons so I decided to redo my
> database and learn Ruby/RoR in the process.
[...]
> All told, I have 17 tables (so, 17 nightly dumps).  A majority of the
> searches are going to be against 3 to 8 of these tables.
>
> Given all of this, my issues/questions are as follows:
>
> - I''m really trying to avoid hard coding as much as possible.
 Ideally,
> I''d have some kind of admin interface to make changes to how data
is
> displayed.  The idea of hard coding each text box for the search fields,
> or the displayed columns, etc... isn''t appealing to me.
Check out ActiveScaffold.
>
> If this really is the best solution, I suppose I can go that route.  The
> nice thing of having my Views in MySQL was that it was easy to map meta
> data to what to display.
Rails will deal with database views.  I don''t know that much about how
to work with them, but there''s been discussion about them on this
list.
>
> - I''m trying to figure out how to design this to make it very
scalable
> and easy to modify.  Particularly, if two tables have a column that have
> something in common, I''d like to (perhaps) make those into a link
to
> each other.
I believe ActiveScaffold will do this easily.
>
> - As above, I''d also like to add extra functionality.  I setup
> acts_as_revisable in my database and it works very well.  If a user has
> an elevated role, I''d like to have them be able to review the
history of
> a record.
>
> These are pretty lofty goals
No, they''re not.
> and this will take some baby steps.  I''m
> still new to Ruby/RoR, but I think it''s been hard (so far) to find
good
> documentation that addresses some of these issues.  Or maybe I
don''t
> know the key words or how to approach this the Ruby Way (probably the
> case)?
I think you''re overthinking it.  Rails makes what you''re
talking about
so basic that I don''t think most people would bother writing tutorials
on the subject.  Have you started writing this application yet?
>
> I''d like to use my experiences to do a write-up/tutorial on how to
> address data warehousing problems with RoR (and starting RoR from
> scratch).  If I become good enough, I may eventually attempt a plugin
> for combined data warehousing, versioning, cross-referencing, etc (if I
> still think there''s a use for it).
That could be interesting, although things like ActiveScaffold cover a
lot of the same ground.
>
> I really appreciate everyone''s help/advice on the issue.
> --
> Posted viahttp://www.ruby-forum.com/.
Best,
--
Marnen Laibow-Koser
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
http://www.marnen.org