Hello:
I'm a newbie to Datawarehousing and using of the concepts of Dimensional
modeling. I read about Kimball's methodology too.
I started using Analysis Services, which is really great. I created tables
with the primary index values.
My scenario is like this.
I should be able to drill down to any of these reports and the details
also. Kindly help.
I would like evaluate the sale of tools in different regions during
different times, with different customers, with different tools description
and with different types of purchases(in any fashion), the way users need.
I created 5 different tables with
Tool Description, Region, Time, Purchase Type, Customer.
All tables are linked by Tool number which is unique.
Tool Description has fields such as
Tools Description, Tool Type, Tool Group, Tool Number(The index has
multiple columns to make it unique).
Region - Region, State, Zip Code, Tool_No(Unique Record)
Time - Year,Quarter,Month,Week,Day,Tool_No
Purchase_Type - Purchase_Type(Lease/Sale), Sale_Type, Transaction_Type,
Tool_NO(Index values)
Customer(Customer age, Gender,Tool_No)

I created all these tables and keyed in values manually for my own testing
purposes. I would be populating the values using Stored PRocedures(probably
by DTS) as time goes.
But to create a FACT table with multiple dimensions, the following
questions arose.
Can I create a fact table with the following columns or do I need to add
Tool_No also in the FACT table.
Tool_Description,Year,Purchase_Type,Customer,Total _Sales.

I guess that I need to populate this fact table using T-SQL before
creating a cube .Am I correct? Please do let me know.

In the FACT TAble, do I need to go to the level of granularity of the
Tool_No also.

Once I create a fact table with T-SQL queries, can I go ahead and create a
cube with all 4 - 5 dimensions, the way I would really like to drill it down
to the reports.

Please help

Any help is greatly appreciated.

Thanks,
Bobby