R
RD
I have a need to analyze existing sales data against previous performance
periods (model years, fiscal years, quarters, months, weeks, etc.). My
management team has always reviewed historical sales numbers using the Model
Name convention (1700CC) versus Part Number (52017). Prior to the
implementation of the new/present MRP system we had a customized order entry
system that just included a products table. Sales Order's were placed with
the use of Descriptive based drop-down fields, which usually was a model
name. The new system forces us to use a P/N in place of a Model Name,
consequently, reporting has become very complex. I can no longer create a
report that generates the number of units and dollars sold by model name.
Instead, I have to use a p/n. Is there a way to create an association
between the two (P/N and Model Name) for both current and previous models?
Keep in mind that I cannot change the MRP structures without upsetting the
MRP vendor.
Is there somewhere that easily explains the comparison reporting of prior
periods performance in units and dollars against present? I imagine this
would be a lengthy OLAP, Pivot Table, especially since they want it grouped
by organization and sorted by dealer name. In every database class I've
participated in, I have yet to be able to locate an instructor that's
knowledgeable enough to tackle the design of this report. However, my
difficulties are now compounded, I believe, because of the part numbering
issue.
In addition, I would like to create a database whereby the Marketing
Departments could easily manage the features of the various product lines
within one view by using pre-defined criteria. How would someone use the
features example below to allow for synonyms? For example, an end item might
fit in both structures. My best illustration would be how on the CDW.com
website; I can locate an end software item under both SOFTWARE and
NETWORKING category. How do I integrate these different structures?
Finally, I have a requirement to report our sales performance to
miscellaneous market research firms, each of which has their own way of
slicing and dicing our product lines. How would I deal with this given that
some of the companies use similar names for specific entities. For example,
BUC Research uses: Boat Type, Top or Rig, Hull Material, Engine
Manufacturer, Engine Type, Hull Configuration, etc. The US Coast Guard
and/or our industry trade association, NMMA, might categorize our products
in the same area while another entity would not. Would each of these be best
served as separate tables (e.g. USCG Category, NMMA Category, or BUC
Category)? Or, could I use a cross-reference table again?
I have been reviewing your past articles on item hierarchies and found some
of it helpful. However, I was wondering how a cross-reference table could
affect hierarchies? Our MRP system we use is designed with two tables Part
Master and PartXReference as shown at the end of this document.
Below is an outline of the different things I'm trying to string together
using a common thread.
Products > [Product Type]
Products > Boats
Products > Engines
Products > Trailers
Manufacturer > Brand > Series > Model > Trim > Option Package
Godfrey Marine > Hurricane > Sun Deck > 217 I/O >
Continent > Country > State > County > City > Zip code
Region > Territory > MTA
A Marine Trade Area or MTA crosses all geographic boundaries (States,
Counties, City, Zip/Postal Codes)
Engines > Inboard > Specifications
Engines > Sterndrive (I/O) > Specifications
Engines > Outboard > Specifications
[MODEL NAME]
Engine Type
4 cyl.
Displacement
106.2 ci (1741 cc)
RPM Range
5000-6000
Alternator Output @ W.O.T.
25A
Compression Ratio
9.70:1
Induction System
DOHC/EFI
Lubrication
Wet Sump
Ignition System
TCI Micro Computer
Starting System
Electric
Shaft Length
20 in., 25 in.
Gear Ratio
13:28 (2.15)
Weight*
402 lb (183 kg)
Warranty
3 Year Limited Pleasure Boat
1 Year Limited Commercial
Boats > Specifications
English (U.S.)
Metric
Beam
Capacity, Fuel
Capacity, Holding Tank
Capacity, People
Capacity, Total
Capacity, Water
Deadrise, Entry
Deadrise, Transom
Displacement
Dry Weight
Freeboard
Horsepower, Maximum
Horsepower, Minimum
Hull Draft (estimated)
Length, Centerline
Length, Overall
Depending on the specific type of boat, we may [not] wish to publish a
specification type b/c it won't apply or causes the manufacturer too many
headaches. We would like to publish the data internally but not to the
public via websites, literature, etc. Method of handling?
Boats > Features [Categories]
· · Accessories
· Canvas
· Cockpit
· Construction
· Dash
· Electrical
o AC Systems
o DC Systems
· Electronics/Stereo
· Exterior
· Fasteners and Hardware
· Fishability
· Fuel System
· Helm
· Instrumentation
· Interior
· Lighting
· Mechanical
· Marine Hardware
o Deck Hardware
o Hull Hardware
o Underwater Hardware
o Stainless Hardware
· Navigation
· Performance
· Plumbing
· Safety
· Seats
· Steering
· Tops or Rigs
o T-Tops
o Hardtops
· Warranty, Hull
· Warranty, Parts
periods (model years, fiscal years, quarters, months, weeks, etc.). My
management team has always reviewed historical sales numbers using the Model
Name convention (1700CC) versus Part Number (52017). Prior to the
implementation of the new/present MRP system we had a customized order entry
system that just included a products table. Sales Order's were placed with
the use of Descriptive based drop-down fields, which usually was a model
name. The new system forces us to use a P/N in place of a Model Name,
consequently, reporting has become very complex. I can no longer create a
report that generates the number of units and dollars sold by model name.
Instead, I have to use a p/n. Is there a way to create an association
between the two (P/N and Model Name) for both current and previous models?
Keep in mind that I cannot change the MRP structures without upsetting the
MRP vendor.
Is there somewhere that easily explains the comparison reporting of prior
periods performance in units and dollars against present? I imagine this
would be a lengthy OLAP, Pivot Table, especially since they want it grouped
by organization and sorted by dealer name. In every database class I've
participated in, I have yet to be able to locate an instructor that's
knowledgeable enough to tackle the design of this report. However, my
difficulties are now compounded, I believe, because of the part numbering
issue.
In addition, I would like to create a database whereby the Marketing
Departments could easily manage the features of the various product lines
within one view by using pre-defined criteria. How would someone use the
features example below to allow for synonyms? For example, an end item might
fit in both structures. My best illustration would be how on the CDW.com
website; I can locate an end software item under both SOFTWARE and
NETWORKING category. How do I integrate these different structures?
Finally, I have a requirement to report our sales performance to
miscellaneous market research firms, each of which has their own way of
slicing and dicing our product lines. How would I deal with this given that
some of the companies use similar names for specific entities. For example,
BUC Research uses: Boat Type, Top or Rig, Hull Material, Engine
Manufacturer, Engine Type, Hull Configuration, etc. The US Coast Guard
and/or our industry trade association, NMMA, might categorize our products
in the same area while another entity would not. Would each of these be best
served as separate tables (e.g. USCG Category, NMMA Category, or BUC
Category)? Or, could I use a cross-reference table again?
I have been reviewing your past articles on item hierarchies and found some
of it helpful. However, I was wondering how a cross-reference table could
affect hierarchies? Our MRP system we use is designed with two tables Part
Master and PartXReference as shown at the end of this document.
Below is an outline of the different things I'm trying to string together
using a common thread.
Products > [Product Type]
Products > Boats
Products > Engines
Products > Trailers
Manufacturer > Brand > Series > Model > Trim > Option Package
Godfrey Marine > Hurricane > Sun Deck > 217 I/O >
Continent > Country > State > County > City > Zip code
Region > Territory > MTA
A Marine Trade Area or MTA crosses all geographic boundaries (States,
Counties, City, Zip/Postal Codes)
Engines > Inboard > Specifications
Engines > Sterndrive (I/O) > Specifications
Engines > Outboard > Specifications
[MODEL NAME]
Engine Type
4 cyl.
Displacement
106.2 ci (1741 cc)
RPM Range
5000-6000
Alternator Output @ W.O.T.
25A
Compression Ratio
9.70:1
Induction System
DOHC/EFI
Lubrication
Wet Sump
Ignition System
TCI Micro Computer
Starting System
Electric
Shaft Length
20 in., 25 in.
Gear Ratio
13:28 (2.15)
Weight*
402 lb (183 kg)
Warranty
3 Year Limited Pleasure Boat
1 Year Limited Commercial
Boats > Specifications
English (U.S.)
Metric
Beam
Capacity, Fuel
Capacity, Holding Tank
Capacity, People
Capacity, Total
Capacity, Water
Deadrise, Entry
Deadrise, Transom
Displacement
Dry Weight
Freeboard
Horsepower, Maximum
Horsepower, Minimum
Hull Draft (estimated)
Length, Centerline
Length, Overall
Depending on the specific type of boat, we may [not] wish to publish a
specification type b/c it won't apply or causes the manufacturer too many
headaches. We would like to publish the data internally but not to the
public via websites, literature, etc. Method of handling?
Boats > Features [Categories]
· · Accessories
· Canvas
· Cockpit
· Construction
· Dash
· Electrical
o AC Systems
o DC Systems
· Electronics/Stereo
· Exterior
· Fasteners and Hardware
· Fishability
· Fuel System
· Helm
· Instrumentation
· Interior
· Lighting
· Mechanical
· Marine Hardware
o Deck Hardware
o Hull Hardware
o Underwater Hardware
o Stainless Hardware
· Navigation
· Performance
· Plumbing
· Safety
· Seats
· Steering
· Tops or Rigs
o T-Tops
o Hardtops
· Warranty, Hull
· Warranty, Parts