# Help with Data Warehouse app -- How to display data?

**URL:** <https://www.ruby-forum.com/t/help-with-data-warehouse-app-how-to-display-data/165843>\
**Category:** Rails\
**Created:** [April 18, 2009, 3:48am UTC](https://www.ruby-forum.com/t/help-with-data-warehouse-app-how-to-display-data/165843 "2009-04-18T03:48:22Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![kyle\_n](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/7cd45c/32.png) [@kyle\_n](https://www.ruby-forum.com/u/kyle_n)\
**Post date:** [April 18, 2009, 3:48am UTC](https://www.ruby-forum.com/t/help-with-data-warehouse-app-how-to-display-data/165843/1 "2009-04-18T03:48:22Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![kyle\_n](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/7cd45c/32.png) [@kyle\_n](https://www.ruby-forum.com/u/kyle_n)\
**Post date:** [April 20, 2009, 7:11pm UTC](https://www.ruby-forum.com/t/help-with-data-warehouse-app-how-to-display-data/165843/2 "2009-04-20T19:11:43Z")

</div>

On Apr 17, 9:48 pm, “Kyle N/a” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
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  
removed\_email\_address@domain.invalid  
[http://www.marnen.org](http://www.marnen.org)