Fact Table with Dimensions

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,Cust
omer,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,Bobby, i could take a look at your current SQL Server database and OLAp
structures. Send them to me, andI'll see what can I do
Alejandro Leguizamo
SQL Server MVP
Colombia
"Bobbyx2405" <Bobbyx2405@.discussions.microsoft.com> wrote in message
news:6899853C-03EA-490C-9EE1-657E276A85F3@.microsoft.com...
> 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,Cust
omer,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,
>
>