A slowly changing fact table?

Question

Background) I went through the process of creating a fact table for our inventory data, which in theory will act as a night snapshot of our warehouse. What is recorded is information such as quantity, weight, location, statuses, etc. The data is very detailed and in many cases is not specifically related to one object (our source database registers inventory data as having three primary keys: aka pallet license mark, product and type of packaging - therefore it has essentially 3 business keys and does not have a surrogate key key).

The goal is to be able to 100% accurately recreate our warehouse management system data, which can be viewed for one day in history. Therefore, I can look and see how many XYZ product pallets were at location 1234 on August 4.

Question 1) Now, I built this fact table to look structurally like a slowly changing dimension, type 2. Is this wrong? I read a little about the accumulation of snapshot fact tables, and I'm starting to doubt my design. What is the best practice in this situation?

Question 2) If my design is fine, how do I configure Analysis Services to recognize my DateStart and DateEnd columns in a FACT table? I found some information on how to set this up for measurements, but it doesn't seem to work / apply to fact tables.

For reference - Fact table structure (with added notes about columns):

CREATE TABLE [dbo].[FactInventory]( [id] [int] IDENTITY(1,1) NOT NULL, (fact table only surrogate key) [DateStart] [datetime] NULL, (record begin date) [DateEnd] [datetime] NULL, (record end date) [CreateDate] [datetime] NULL, (create date of the inventory record in src db) [CreateDateId] [int] NULL, (create date dimension key) [CreateTimeId] [int] NULL, (create time dimension key) [LicensePlateId] [int] NULL, (pallet id dimension key) [SerialNumberId] [int] NULL, (serial number id dimension key) [PackagedId] [int] NULL, (packaging type id dimension key) [LotId] [int] NULL, (inventory lot id dimension key) [MaterialId] [int] NULL, (product id dimension key) [ProjectId] [int] NULL, (customer project id dimension key) [OwnerId] [int] NULL, (customer id dimension key) [WarehouseId] [int] NULL, (warehouse id dimension key) [LocationId] [int] NULL, (location id dimension key) [LPStatusId] [int] NULL, (licenseplate status id dimension key) [LPTypeId] [int] NULL, (licenseplate type id dimension key) [LPLookupCode] [nvarchar](128) NULL, (licenseplate non-system name) [PackagedAmount] [money] NULL, (inventory amount - measure) [netWeight] [money] NULL, (inventory netWeight - measure) [grossWeight] [money] NULL, (inventory grossWeight - measure) [Archived] [bit] NULL, (inventory archived yes/no - dimension) [SCDChangeReason] [nvarchar](128) NULL (auditing data for changes) 

Answer 1

As a rule, there are no changes in the snapshot fact table.

Usually you have a date / time size that is used to drill down to dimensions, not DateStart / DateEnd. Similarly, you do not have SCD information. A snapshot was taken, and dates and times are tied to these facts. If these facts are repeated the same every month, so be it.

Working with determining which facts are valid at a given time is more processing than you really want your DW or your ETL to cope - such a design (effective dates, etc.) is more effectively used in the live OLTP system. such as where the full story is stored in a living system. The DW point needs to be optimized for reporting, not space, and therefore there is a direct measurement of the date / time of the snapshot, which makes it easy to index and potentially split data without years of arithmetic or comparisons.

For your dimensional model, be careful not to succumb to too big a dimension problem. Remember that measurements should not correspond to objects in the real world. The choice of how dimensional attributes are grouped into measurement tables should be informed: 1) by the needs for queries, 2) data binding and behavior changes, 3) business organization. You might want to examine one or more garbage parameters.

Answer 2

Before moving on, is inventory really a slowly changing fact?

Edit: Then why not just reduce every product every day, as you need it.

The problem is that the fact tables are getting large, and you arbitrarily throw ALL in the fact table. Ideally, the fact table will contain nothing more than foreign keys to dimensions and data that relate only to that fact. But some of the columns that you specify look as if they belong to one of the size tables, whereas

For example, license plate information. Status, type and search code. Similarly with netWeight / grossWeight. They must be inferred from the size of the product and PackagedAmount.

 CREATE TABLE [dbo].[FactInventory]( [id] [int] IDENTITY(1,1) NOT NULL, (fact table only surrogate key) [day] [int] NULL, (day dimension key, grain of a day) [CreateDateId] [int] NULL, (create date dimension key) /* I take these are needed? * [CreateTimeId] [int] NULL, (create time dimension key) * [CreateDate] [datetime] NULL, (create date of the inventory record in src db) */ [LicensePlateId] [int] NULL, (pallet id dimension key) /* Now THESE dimension columns...possibly slowly changing dimensions? [LPStatusId] [int] NULL, (licenseplate status id dimension key) [LPTypeId] [int] NULL, (licenseplate type id dimension key) [LPLookupCode] [nvarchar](128) NULL, (licenseplate non-system name) */ [SerialNumberId] [int] NULL, (serial number id dimension key) [PackagedId] [int] NULL, (packaging type id dimension key) [LotId] [int] NULL, (inventory lot id dimension key) [MaterialId] [int] NULL, (product id dimension key) [ProjectId] [int] NULL, (customer project id dimension key) [OwnerId] [int] NULL, (customer id dimension key) [WarehouseId] [int] NULL, (warehouse id dimension key) [LocationId] [int] NULL, (location id dimension key) [PackagedAmount] [money] NULL, (inventory amount - measure) [netWeight] [money] NULL, (inventory netWeight - measure) [grossWeight] [money] NULL, (inventory grossWeight - measure) [Archived] [bit] NULL, (inventory archived yes/no - dimension) [SCDChangeReason] [nvarchar](128) NULL (auditing data for changes)