fact-less fact table

hai
can you get give me some ideas for constructing a fact table for dataware
housing.And also about the different types of measures in the fact table
like full additive measures,semi-additive measures and fact-less fact table
vidhyaYour fact table should have measures that you would like to report and
analyze. If you wanted to track sales you might have (simplified):
CustomerID
ProductID
StoreID
SalesAmount
Sales Amount is your measure and it can be totaled up so therefore it is
additive.
Semi-Additive measure can be values such as account balances or inventories
(something that is tracked over time). If you have $100 in your account toda
y
and $50 in your account tomorrow, you don't want to state that the total is
$150.
Here is some info on Semi-Additive (it was written under AS 2000 but the
principles still apply):
http://msdn.microsoft.com/library/d...br />
add2.asp
Fact-less Facts are basically used to identify the occurance of something
that may not have a measure associated. Essentially the existence of the fac
t
row counts as 1.
Here is a good Kimball reference for that:
http://www.dbmsmag.com/9609d05.html
-=Steve
"vidhya" wrote:

> hai
> can you get give me some ideas for constructing a fact table for datawar
e
> housing.And also about the different types of measures in the fact table
> like full additive measures,semi-additive measures and fact-less fact tabl
e
>
> vidhya
>
>

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,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,
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,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,
>
>

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,
>
>