Cube design

I have a table including all the info. Now I need to create a cube based on this table. I confuse how to create dimensions and fact based on this table. Do I just query out date, department.... To create tables as dimensions?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Vikas GargAssociate Principal EngineerCommented:

Generally the Fact table contains two things

1. Meausres
2. Dimension Keys

So you can design your fact table such.

For dimension tables they will contain key and detail about dimensions.

For example your table is having transaction date, amount, product name

Then you can create a calendar dimension having tran date and datekey
other product dimension with product name and id

and in the fact table you will have prodid, datekey and amount

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
wasabi3689Author Commented:
How do I generate keys since I have only one flat table containing all info? For dimension tables such as product dim, what I need is to query out all distinct products from this flat table into a new product dim table including the keys, correct?

My second question is
if I have unique keys in the flat table, can I just use these keys to all dimensions, not creating new keys like datekey...
wasabi3689Author Commented:
finally, can I just use this flat table as one single fact, not having dimension to create a cube since this table has include all the info?
Vikas GargAssociate Principal EngineerCommented:
To create Cube you will be needed fact and dimension so in the datasource view you need to create new named query which is similar to the physical table.

If you do not want to create name query then you need to create physical table which you can later on use in your data source view.
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.