Time sensitive information - request for ideas on how to implement

[JCRA]

Hi!

I would appreciate having ideas on how to implement in an efficient way a "time vector" for several tables in a database.

Imagine thousands of stores, hundreds of people in promotion activities, dozens of products, promotions, etc.

Imagine that people in promotion "rotate" among the stores as per management definition, promotions go "on" and "off", new products are included, etc.

I need to find for example, which stores have not been visited by the assigned person in a given week. (and similar reports, like sales of (then active) products in a given week, etc.

OK, you've got the idea. The question is not regarding the SQL code.... What I am looking for is for a good stratagy for handling such a situation (sort of time tracking). Any good (efficient) ideas you could provide?

Thanks in advance

Regards

 

[ThomVF]

This sounds like a data-mart type of application. Are you familiar with the Fact-Dimension model used in Data Warehouses/Data Marts ?

A fact is something you want to report on or calculate against - a quantifiable measurement.

A dimension is used to qualify the fact.

For example, a Fact would be a record of a "Sale". The dimensions could be "Product", "Store", "Date/Time", "SalesPerson", etc.

Another name for this is a "Star Schema". Think of the fact as the nucleus/center of the star and the dimensions are the points that go out from the center. A deviation on this is called the "snowflake" schema, where dimensions have dimensions on themselves.

In your case, I see 2 facts : "Sale" and "Visit"

The "Sale" fact has dimensions: Store, Promotion, Product, Employee and Date/Time. The Promotion dimension references a strucutre that schedules an employee to it for a period of time.

The "Visit" fact has dimensions: Employee, Store, Promotion and Date/Time (these dimensions are shared by the "Sale" fact). The dimensions identify what employee on what date visited which store for what particular promotion.

You can read more about this methodology here:

http://www.dbatoolbox.com/WP2001/dssdw/arch_dw.pdf

http://www.cis.drexel.edu/faculty/song/WESST/frmain12.htm

Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com