Forum: Ruby on Rails Help with Data Warehouse app -- How to display data?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
8b03d6492a44fa7748d897a740fa41ca?d=identicon&s=25 Kyle N/a (smagoo)
on 2009-04-18 03:48
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.
Dd2d775dea75b381edb1bbf0600a0907?d=identicon&s=25 Marnen Laibow-Koser (marnen)
on 2009-04-20 19:11
(Received via mailing list)
On Apr 17, 9:48 pm, "Kyle N/a" <rails-mailing-l...@andreas-s.net>
wrote:
> 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@marnen.org
http://www.marnen.org
This topic is locked and can not be replied to.