Pros & cons to have a typical star schema but with only one fact value field and a thousand attribute names in one of the dimension tables?

30 pts.
Tags:
Data Warehouse Planning
Data warehousing
SQL
Want to build a kind of a scientific fact search and analytical engine for a lot of different types of measurements hosted in a lot of source tables and columns.

A star join schema is built and data is copied (ETL) from the sources as usual - but all source column values are stored into a single fact-table value column and each column name is represented by a surrogate foreign key referencing a dimension table where the source table and column names are prestored.

There are several other dimensions as well, as usual. In this design ER-structures in the source database are deliberately abandoned, the only structure that is preserved is repeating groups through a simple serial number dimesion, so that e.g. time series values and depth intervals (stratigraphy) are chained. One might have a second fact value column for alphanumeric codes.

There will be a lot of rows in the fact table, but rather short ones. The star join database can be searched with SQL and simple statistics may be presented or one may take resulting datasets to other analytical tools, e.g. Excel. The SQL in queries may sometimes be tricky in this design. One might create (with SQL-scripts) smaller and specialized data marts on different topics from this data warehouse.

An interesting quality is that space coordinates in the fact table open up for unlimited possibilities to select and present data on digital maps from a GIS tool. 

/RWg, Geological Survey of Sweden.



Software/Hardware used:
Oracle SQL*Plus, ArcView, MapInfo
ASKED: November 25, 2009  12:07 PM
UPDATED: December 22, 2009  10:33 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

The new pivot feature in Oracle 11g SQL will be very handy to operate on that fact table. /RWg

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JennyMack
    Rwg, Wow, this is quite a lot of information! Just to clarify -- what exactly do you need help from the community on? Suggestions on hardware/software? Do you want the performance pros/cons? Usability? Cost? Please provide more information here on the criteria that would be most helpful to you, so the community can answer. Thanks, Jenny Community Manager
    4,280 pointsBadges:
    report
  • Rwg
    Jenny, Was there not a T-shirt to reward participants in this forum ;)? I was hoping for some light shed on the design from experience i.e. from anyone that have built a analytic db on a star join model. So generic usability comes first. Performance I am sure can be dealt with some how. My case is a scientific one, rather than e.g. assessments of sales. The general idea is to re-store data where simplicity is taken to the extreme for OLAP and that contrasts heavily to the complex data models that prevail in the many source databases. This simplicity, I believe, is a must if you want a broad group of users to be able to help themseleves to swiftly analyze any data from own assumptions without any source database knowledge whatsoever. The side-data/meta-data they must know of, and can discriminate from, is put in the dimension tables. Some have been skeptic about the loss of structure that prevails in the source databases and others think SQL will be odd and retrieval be slow. Recently I noticed the new pivot feature in Oracle 11g that eventually can make things easier. We are about upgrade. A T-shirt for this topic. But of course, rather some good view points. Thanks/RWg
    30 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following