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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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 OlsenDBACommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.