• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

incorporate Star schema architecture

hi experts
I have a requirment where I have a Customer table which will contain 20 columns(denormalized)
a basic snapshot of the table is

and so on

We will have two more tables
Department and Salary

we need to get the following data from the table.
List of all customer_names,department_name who have Cusomer_FaultCount1
List of all customer_names,department_name,salary who have Cusomer_FaultCount2
and so on

We are planning to incorporate Star schema architecture  with fact table and the dimension tables.
Can anyone tell me how i can get started ?
I would greatly appreciate any help.

  • 2
1 Solution
Kent OlsenData Warehouse Architect / DBACommented:
Hi royjayd,

I think that most of us are somewhat surprised by your post.  The decision has been made that your database is going to be converted to a Star Schema, but no one knows anything about them.  That's a recipe for failure.

With that said, what can you tell us about your data?  How much is there (row and/or overall space)?  What's the foundation for your fact tables?

royjaydAuthor Commented:
hi Kdo,

>>The decision has been made that your database is going to be converted to a Star Schema
I am sorry i dint get that.
What do you mean by database is going to be converted to a Star Schema?
I am not familiar with star schema and not familiar with the concept

>>what can you tell us about your data?
The data will be huge in the Customer table, we anticipate 5000 rows to be added daily to it every working day. The data will be cleaned up once every year, so you can imagine
considering 240 working days in a calendar year there will be 240 X 5000 rows to be saved in the table.
The data in this table will be used for analysis and reporting (using java)

Here is the database information which will host the tables
Microsoft SQL Server Enterprise Edition (64-bit)
OS: Microsoft Windows NT 6.1 (7601)
version: 11.0.2383.0
memory: 131037 (MB)
processors: 16
IsClustered: False

Kent OlsenData Warehouse Architect / DBACommented:
From your last post, it sounds like you want to use a star schema design to record transactions.  That's highly unusual and problematic.  A star schema is a particular kind of database model that's used typically in data warehouses.  It's very good at filtering through huge amounts of data for data mining and reporting.  One of the characteristics is that (depending on the DBMS engine) the data in the index(es) can be used without ever reading a fact table.  But that comes at a price.  The reporting and research needs must be designed into the schema, otherwise you're just reading the fact (transaction) table.

Typically, an OLTP will record transactions serially, with the transaction table containing unnormalized data to reduce the demand on system resources.  The transactions are usually written with a single INSERT statement that often reads no other tables.

An OLAP (a data warehouse is a form of OLAP and a star schema architecture is a specific model used in data warehouses) significantly restructures the data to make queries the most efficient, provided that the dimension and fact tables are structured with the specific queries in mind.  Adding several columns to a traditional OLTP is nearly trivial so application changes don't require massive database changes.  Adding those columns to a star schema could require a complete redesign of the database, depending on how they interact with existing dimensions and reporting needs.

You don't need a data warehouse.  5000 transactions per day (1.2M per year) is a very modest database by today's standards.  10 years from now when your data has grown to 12 million rows, it will still fit into a hand-held device.

Record the transactions in a typical OLTP architecture.  If your customer is insistent on having a star schema style OLAP (and I would try to persuade them out of this idea) use the transactions in the OLTP as source data to build the OLAP.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now