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

fact table design question

I have created a factSales table with dimDate, dimCustomer, dimProduct, dimSalesPerson tables. The dimensions are all joined with surrogate integer identity PK fields which serve as the composite key in the fact table.

It is possible for the same customer to place multiple orders for the same product from the same sales person on the same date. When this happens it seems to me that only the last order will be stored in the fact table. I want to have a row for each order. How would one design a fact table to accomplish this. The only truly unique piece of data from the OLTP is the sales order number.

factSales:

PK DateKey, int

PK CustomerKey, int

PK ProductKey, int

PK SalesPersonKey, int

Amount

UnitCost

Weight

ShippingCost

dimDate

PK DateKey, int, identity

SalesDate

dimCustomer

PK CustomerKey, int, identity

CustomerName

Address

dimProduct

PK ProductKey, int, identity

Product#

ProductName

DimSalesPerson

PK SalesPersonKey, int, idnetity

SalesPersonName

Dept

StartDate

EndDate

Hi,

You'll need to stick another key on the fact to make each row unique. Even if it is something simple like a count

e.g.

FK DateKey, int

FK CustomerKey, int

FK ProductKey, int

FK SalesPersonKey, int

FK OrderNumberOfTheDay, int

measures ...

If possible a time stamp might be another way, I am assuming though that the different orders happen at different times of the day. But basically another key in your ETL process will fix that problem.

Hope that helps,

Matt

|||

Thanks Matt. I was going to just add the sales order # as it is unique but I didn't want to violate and conventions that might cause problems down the line.

Thanks again!

|||

Hi John

Matt is right in that you should simply bring through the Sales Order Number then your DSV design will take care of the aggregation

Alternatively, unless you need to analyse the data by the Sales Order Number, (which some might argue you should use your OLTP for) you could aggregate the facts in your fact table ETL load process. This can increase performance both in processing the cube and queries if you have a lot of data

If you want to use any drillthrough functionality then keep the Sales Order Number in.

HTH

Tim

|||

Thanks Tim.

I had also considered that possibility. My actual OLTP/OLAP is more complex than I showed (I tried to keep it simple for this example). I would have to join several dozen records in order to aggregate the data and I may have a sales order record which does not yet have a ship date which will be the key date slicer. I can load my fact table with a pointer to a 0 date key or just skip SO records without a ship date (my next task to figure out); it would be very complex to aggregate them. Plus I was already storing the SO# in my fact table on the likely probability the users will want to drill back to the original table.

Fact table design problem

Hello All,

I have a row for each employee, each month in my fact table.

EmployeeID DateKey Other Dimensions... 1 20060101 2 20060101 1 20060201 2 20060201

I need to include bonus information. The problem is, for each month a person may have received more than one bonus amount and type in a month. In some cases they have gotten the same bonus twice in the same month. For example:
EmployeeID DateKey Bonus Bonus Type
1 20060105 $5,000 Team
1 20060107 $500 GoodJob
1 20060110 $250 Incentive

How can I have multiple bonuses and their amounts in the fact table if I only have a single row for each employee?

Thank you for the help, this has been driving me crazy.

-Gumbatman

p.s. I've tried stuff with many-to-many dimensions and also Factless Fact tables to get this, but it is just not working for me. I read Mark Russo's very good article about many-to-many dimensions but I am not 100% that it applies to what I am doing.

Hello! I am not sure that I understand the problem fully but have you tried to add a bonus type dimension.

With a bonus type key, an employee key and a date key you should be able to do the analysis you are describing.

All these relations would be one-to-many from the dimension tables.

HTH

Thomas Ivarsson

|||

Thomas,

Thank you for responding.

Are you saying keep the Bonus amounts (the facts) in the main Fact table or have a secondary fact table?

If it is in the main fact table, how do I connect the Bonus dimension to the fact table when there are multiple bonuses inside a month (which is the grain of that fact table)?

I tried using a Bonus fact table that connects to the main fact table via a DateKey and EmployeeKey. Then I connect the Bonus Dimension to the Bonus fact table. In order to get the Bonus Dimension to be "seen" by the main fact table, I am using a many-to-many connection.

The problem I am having there is I have a Geography Dimension connected only to the main fact table, not the Bonus fact table. I can't seem to get the Geography Dimension to work properly. What I mean by that is that the Bonus numbers show up for everyone, regardless of Geography.

Please let me know if I am not explaining this clearly. I really appreciate the help (and I really need the help). I've gotten to the point were I am ready to hire a consultant on this issue alone.

-Gumbatman

|||

Perhaps you are talking about something not previously known in your first post.

I do not think that you will need two fact tables for this problem. I am thinking about one fact table with one measure like Amount.

The dimension tables will be time, employee and bonustype. If an employee receive the same type of bonus twice on a single day you can aggregate these recordss into one, with Integration Services.

HTH

Thomas Ivarsson

|||

Sorry if I didn't mention stuff clearly in my first post. There is a lot to this issue that I may have forgotten to mention.

However, I am curious about your IS suggestion. I can certainly aggregate the amount into the single row. But how do I show that a person received multiple bonuses in that month?

For example, the Bonus Dimension looks like this:

BonusID BonusType BonusAmount

1 Thank You 50

2 Applause 100

3 Encore 250

On a single row, the employe received two Thank Yous and an Applause - a total of 200. Should my Dimension table look like it does? And this prompts another question, can I do a calculation from a Dimension table?

Thank you again for your help.

-Gumbatman

|||

Hello. I would move the BonusAmount to the fact table and keep BonusId and BonusType in the dimension table.

You do all calculations in the fact table.

Your single fact table will look like this

EmployeeId

BonusId

BonusDate

BonusAmount

HTH

Thomas Ivarsson

|||

Thomas,

Is the Fact table you are suggesting here, a secondary one to the "main" fact table? If so, what is the best way to link the two fact tables, since the data needs to be summarized by month?

If you are suggesting putting the bonus data in the main table, do I just repeat some of the other Dimensions? For example, here is what I am thinking would be my main fact table with bonus information for a single employee in one month:

Employee Key Date Key Geography Key Salary Bonus Amount Bonus Key 1 20060101 55 50,000 50 1 1 20060101 55 0 250 2 1 20060101 55 0 500 3

Since it is additive by month, I have to zero-out the Salary for the additional rows.(At least that is my guess.)

Thank you.

-Gumbatman

|||

No! Only one fact table.

I see that you have a salary measure as well and that it will repeat for all bonus records and that the granularity is actually on a month level?

If this is the case I suggest that you make each bonus type a separate measure and aggregate them to the same level as the salary(month?)

So if the same bonus appears several times during a month it will be aggregated into one single bonus type measure.

HTH

Regards

Thomas Ivarsson

|||

OK. I think I got it now.

But if I add each bonus as a separate measure in the fact table, what happens if a new bonus type appears?

Thank you.

-Gumbatman

|||

It will be a new measure.

HTH

Thomas Ivarsson

|||

Sorry, I should have been more specific...

What is the best way to handle a new measure coming into the Fact table, through Integration Services? I guess I check and then append new columns when a new bonus measure is created?

Also, if I do it by measures, what happens to the Bonus Dimension? I will need to slice and dice by the Bonus types. I am unclear about how I can still show who got a what bonus type, especially if there was more than one in that monthly row.

Thank you again (and for your patience).

-Gumbatman