Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Analysis Services (2000)  FACT Table Creation and population

Author  Topic  aryan05 Starting Member 1 Post Posted - 2005-05-12 : 08:31:50 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. 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. Any help is greatly appreciated. Thanks,