How to 'restore' a transaction ?

[jcoira]

Hi,

1-Lets take the well known case study of customers,
products, etc. Suppose I sell a product to a customer and
store the sale in my fact table (which grain is the sale).
But after 15 days the customer gives the product back.
What would you advise to do in that case to represent that
event in my fact table to keep aggregated data accurate ?

2-Also, suppose that a product belongs to more than one
category and of course a category holds more than one
product (that is, a many to many relationship). How do I
build my product dimension and moreover how do I link a
product to my dimension table. Must I include two keys
(product and category) in my fact table even when both
products and categories are stored in the same dimension?
(suppose no surrogate keys are used at this time).

Any help would be really appreciated.

Thanks

// jcoira

 

[MDXer]

In respect to issue 1 we have a similar situation but we go out to 60 days. The way I handle it is in my fact table I have a column to show reverse status I flag the record with 1 if it has been reveresed. I build my partitions from a view and not directly from the table and apply the logic &quot;where reversed <> 1&quot; the reversed column is only ever Null or 1. My Cube is partitioned to allow for the least possible amount of processing which in my case is a month. so during a normal process I process the 2 most current partitions and the records are removed from the Cube.

 

[MDXer]

In respect to issue 1 we have a similar situation but we go out to 60 days. The way I handle it is in my fact table I have a column to show reverse status I flag the record with 1 if it has been reveresed. I build my partitions from a view and not directly from the table and apply the logic &quot;where reversed <> 1&quot; the reversed column is only ever Null or 1. My Cube is partitioned to allow for the least possible amount of processing which in my case is a month. so during a normal process I process the 2 most current partitions and the records are removed from the Cube.

Hope this Helps
Paul

 

[jcoira]

Thank you Paul.

Any help with the second issue ?

// jcoira

 

[MDXer]

if you use Surogate keys for your ID Values you should be able to do this
dim table:

Product ProductID Category CategoryID
------- --------- -------- ----------
ProductA 1 CategoryA 1
ProductB 2 CategoryA 1
ProductC 3 CategoryB 2
ProductA 4 CategoryC 3

will result in a dimension such as

CategoryA
ProductA
ProductB
CategoryB
ProductC
CategoryC
ProductA

Carry the Product ID in the Fact Table and use this to join the DIM to the Fact. This will require you to change the allow Duplicate names property on the Dimension to true. The only thing to make sure of is that you if at all possible are unique at your lowest level of the dim. Paul