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.

Extracting Integer

Hi,
I have on NCHAR column which has value like.. 1:25:234567. I have to
extract last part of value i. e. 234567 which comes after sign ':' .
The no. of digits vary. How can i do that using TSQL .
Please Help !!
Thanks
NJneeju a crit :
> Hi,
> I have on NCHAR column which has value like.. 1:25:234567. I have to
> extract last part of value i. e. 234567 which comes after sign ':' .
> The no. of digits vary. How can i do that using TSQL .
> Please Help !!
> Thanks
> NJ
>
SELECT REVERSE(SUBSTRING(REVERSE('1:25:234567')
, 1, CHARINDEX(':',
REVERSE('1:25:234567'))-1))
More generally :
SELECT REVERSE(SUBSTRING(REVERSE(MyColumn), 1, CHARINDEX(':',
REVERSE(MyColumn))-1))
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Oh Thanks a lot...that worked after small change..
Thanks again
NJ

extracting csv string and using in NOTIN clause

Hi,
I'm trying to pass a stored proc a list of values in a comma seperated strin
g:
("3125,3126,3129...") The values represent an integer column in a table.
Then I want to use the string to exclude the values from a SELECT as in:
CREATE PROCEDURE myProc
@.transactionIDs varchar(255)
AS
SELECT TransactionID FROM SalesTransactions
WHERE TransactionID NOT IN (@.transactionIDs)
GO
SQL Server complains that it can't cast a varchar to an int
Any suggestions on how to do this?
Can someone provide an example?
ThanksWhile someone might suggest you use dynamic SQL for this, I
wouldn't. Instead, consider either this not-so efficient solution:
select TransactionID
from SalesTransactions
where
charindex(
',' + rtrim(TransactionID) + ',',
',' + @.transactionIDs + ','
) = 0
-- be sure @.transactionIDs has no spaces
or, for greater efficiency, the more involved solution
that uses a table-valued function to convert @.transactionIDs
into a table of id values:
http://groups.google.co.uk/groups?q...wierzbicki+kass
You can then write:
select TransactionID
from SalesTransactions
where TransactionID not in (
select Item from ListTable(@.transactionIDs)
)
Steve Kass
Drew University
Opa wrote:

>Hi,
>I'm trying to pass a stored proc a list of values in a comma seperated stri
ng:
>("3125,3126,3129...") The values represent an integer column in a table.
>Then I want to use the string to exclude the values from a SELECT as in:
>CREATE PROCEDURE myProc
> @.transactionIDs varchar(255)
>AS
>SELECT TransactionID FROM SalesTransactions
>WHERE TransactionID NOT IN (@.transactionIDs)
>GO
>SQL Server complains that it can't cast a varchar to an int
>
>Any suggestions on how to do this?
>Can someone provide an example?
>Thanks
>
>
>